is there an error importing .csv investment file into a brokerage account

benjamin123 · November 13

If I import a dividend with the value 26.18 Quicken enters it as 26.17. If I import the value as 26.1800000001 Quicken enters it correctly as 26.18. Is there a roundoff/truncation problem in Quicken. This happened with about half of the last 60 investment transactions I imported with the .csv "file, file import, investment transactions, from (.csv) file…". The .csv file was created in EXCEL and the "amount" was set to "number" with 2, 4, or 10 decimal places with the same result. Is this a problem with EXCEL's representation of a "number" or within Quicken's import.

Tom Young · November 13

A computer using standard binary floating-point arithmetic cannot represent the number 26.18 exactly.

I'd guess the Excel program has a lot going on behind the scenes to try and massage numbers to ones that puny humans will understand. That not be the case with Quicken, particularly with the import aspect in mind.

Chris_QPW · November 13

Bad programming on the Quicken Inc developer's part.

Yes, the reason for it changing between 26.18 and 26.17 is because you can't represent 26.18 in binary exactly, but a programmer should understand this limitation and properly round the number. BTW is why you have settings like saying what to round to. Given that the most digits Quicken supports is 8 after the decimal point, they should have rounded to that (or 6 if selected), instead they truncated the value.

Note that importing with QIF doesn't have this problem.

First one in the register is QIF, the second is the one with the Quicken QIF import.

Jim_Harman · November 14

This is apparently a case where Quicken is truncating a 64 bit floating point number to 2 decimal places rather than rounding. Take a look at this online calculator:

https://baseconvert.com/ieee-754-floating-point

If you enter 26.18, the 64 bit binary equivalent when converted back to decimal is 26.17 followed by a long string of 9s. If you enter 26.1800000001, the result is 26.18 followed by a long string of zeros.

Since Quicken added the CSV import recently, the code should be "fresh" and thus this should be an easy fix.

Chris_QPW · November 14

For reference:

Jim_Harman · November 28

@K123 and others experiencing this problem please use the link at Help > Report a Problem to report this issue and reference this discussion. Please attach a copy of your CSV file and a screen shot of the result of the import. Because there is a limit of 10 attachments, you may need to un-check the log files with "Old" in their names.

The more reports they get the better.

Tom Young · November 13

A computer using standard binary floating-point arithmetic cannot represent the number 26.18 exactly.

I'd guess the Excel program has a lot going on behind the scenes to try and massage numbers to ones that puny humans will understand. That not be the case with Quicken, particularly with the import aspect in mind.

BK · November 13

I was struggling how to explain this phenomenon in simple terms, knowing that this happens. You took the words right out of my mouth @Tom Young (not). A very succinct explanation and well done.👏

Chris_QPW · November 13

Bad programming on the Quicken Inc developer's part.

Yes, the reason for it changing between 26.18 and 26.17 is because you can't represent 26.18 in binary exactly, but a programmer should understand this limitation and properly round the number. BTW is why you have settings like saying what to round to. Given that the most digits Quicken supports is 8 after the decimal point, they should have rounded to that (or 6 if selected), instead they truncated the value.

Note that importing with QIF doesn't have this problem.

First one in the register is QIF, the second is the one with the Quicken QIF import.

Jim_Harman · November 14

This is apparently a case where Quicken is truncating a 64 bit floating point number to 2 decimal places rather than rounding. Take a look at this online calculator:

https://baseconvert.com/ieee-754-floating-point

If you enter 26.18, the 64 bit binary equivalent when converted back to decimal is 26.17 followed by a long string of 9s. If you enter 26.1800000001, the result is 26.18 followed by a long string of zeros.

Since Quicken added the CSV import recently, the code should be "fresh" and thus this should be an easy fix.

Chris_QPW · November 14

For reference:

K123 · November 28

I'm seeing the same issues. I am excited to see Quicken finally add this functionality, but it's unusable in its current state since almost all transactions wind up being imported as $0.01 off. I've also noticed on some transactions it will bring the share quantity over as the total amount (so it looked like I had sold 1 share of Microsoft stock for $1.00). They are moving in the right direction, but more work to do until this is actually helpful. Had to go manually adjust/verify every transaction which defeats the purpose.

Jim_Harman · November 28

@K123 and others experiencing this problem please use the link at Help > Report a Problem to report this issue and reference this discussion. Please attach a copy of your CSV file and a screen shot of the result of the import. Because there is a limit of 10 attachments, you may need to un-check the log files with "Old" in their names.

The more reports they get the better.

benjamin123 · 5:28AM

here is a response from microsoft that MAY solve the problem until quicken gets it's act together. i haven't explored it yet, but i'm hopeful

In Excel, numbers are represented using a format called binary floating-point notation, which is based on the IEEE 754 specification. This means that numbers are stored in a binary format that can sometimes lead to precision issues due to the way certain decimal numbers are converted into binary.

For example, the decimal fraction 0.1 cannot be represented exactly in binary, leading to a small rounding error when it is stored. This is because 0.1 in binary becomes a repeating binary fraction, which cannot be stored in a finite amount of space. As a result, when you perform calculations involving these numbers, the inaccuracies can propagate, leading to results that may differ slightly from what you expect.

When you import numbers from Excel into another application like Quicken, these small inaccuracies can manifest as truncation or rounding errors, which might explain why you are seeing numbers that are off by a penny. The specific representation of a number in Excel can sometimes be slightly different from what is displayed in the user interface, as seen in the case where 0.049 is stored as 0.049000000000000002 in the underlying data.

To mitigate these issues, Excel provides an option called "Precision as displayed," which forces Excel to store numbers as they are displayed in the worksheet. However, using this option can lead to loss of precision, so it should be used with caution.

Understanding these nuances in how Excel stores and calculates numbers can help you better manage the data when importing into other software like Quicken.

Chris_QPW · 11:43AM

@benjamin123 a CSV file isn't an "Excel file". It is a text-based file. You can open it in Notepad and see the numbers.

What they describe to you is happening, but it is happening in Quicken and for the same reason. But the point is that any first-year programmer should understand that floating point numbers stored in a computer might not be exact and therefore they need to round the numbers, not truncate them.

is there an error importing .csv investment file into a brokerage account

Best Answers

Answers

Categories