Holdings window is truncating rather than rounding fractional pennies
Comments
-
The forum is mangling Code sections. He's a screenshot of what the last part of my comment should have looked like:
0 -
You provided direct evidence the quote used was provided by the third-party quote service provider in the video you posted. You were just unable to display the actual quote imported without rounding. Note: Only quotes imported from the third-party service provider include the daily range and volume and only a manual entry may override the quote data imported from the third-party service provider.John Ellis said:"It is not a guess." "My WAG is the VWSUX price imported from the third party quote provider for 9/30 was approximately $15.93999999."
While it's a reasonable WAG, there isn't any direct evidence that 15.39999... came from the quote provider, and there is reasonable evidence that it did not. You can see the quote file that gets downloaded, e.g.
C:\ProgramData\Quicken\Inet\jre.001\quotes.var
All the prices contained in it have precision of just four digits (hundredths of a penny):<p>$ egrep '\.open|\.low|\.bid|\.ask|\.hi|\.last|\.lo' quotes.var .last 7 59.8800 .open 7 60.2800 .high 7 60.3850 .low 7 59.7901 .bid 7 59.6500 .ask 7 60.1900 .last_div 6 0.2026 .hi 7 60.3850 .last 7 59.8800 .lo 7 59.7901 .open 7 60.2800 .hi 7 60.3799 .last 7 60.1200 ...</p><i></i>
A difference of 0.0001 in the quoted price would be an approximate market-value difference of $13 on 130K shares, not $0.01.
It's certainly possible that a bug in the quote provider inserted 15.399999... in the quote file for just that one day. Another hypothesis at least as likely is that somewhere in the legacy code paths in Quicken, a 32-bit floating-point number is used for the quotes, rather than a 64-bit, which could produce errors of the right magnitude:<div>int main () {</div><div> float f1 = 15.94;</div><div> double f2 = 134130.373;</div><div> double f3 = f1 * f2;</div><div> printf ("%0.14lf %0.6lf\n", f1, f3);</div><div> double f1d = 15.94;</div><div> double f3d = f1d * f2;</div><div> printf ("%0.14lf %0.6lf\n", f1d, f3d);}</div><div></div><div>$ ./test</div><div>15.93999958038330 2138038.089337</div><div>15.94000000000000 2138038.145620</div>
0 -
"You provided direct evidence the quote used was provided by the third-party quote service provider in the video you posted."
The video indicates only that Quicken was storing a value slightly less than 15.94 in the price history for the given date. There are two plausible ways that value of 15.93999... could have gotten there:
1. It could have arrived in the downloaded quote file from the quote provider. As explained above, normally all quotes have four digits of precision, e.g. 15.9400, but it's certainly possible a bug in the provider provided 15.93999... for just that one day.
2. The quote file from the provider contained "15.9400" but Quicken used a 32-bit float, rather than a 64-bit double, to manipulate or store that value at some point. I posted a sample C program above demonstrating this possibility, where 15.94 gets represented internally as 15.9399995803833.
We're lacking any further evidence that would make one of these more likely.
0 -
There aren't two plausible ways the value could have gotten there. Only one.John Ellis said:"You provided direct evidence the quote used was provided by the third-party quote service provider in the video you posted."
The video indicates only that Quicken was storing a value slightly less than 15.94 in the price history for the given date. There are two plausible ways that value of 15.93999... could have gotten there:
1. It could have arrived in the downloaded quote file from the quote provider. As explained above, normally all quotes have four digits of precision, e.g. 15.9400, but it's certainly possible a bug in the provider provided 15.93999... for just that one day.
2. The quote file from the provider contained "15.9400" but Quicken used a 32-bit float, rather than a 64-bit double, to manipulate or store that value at some point. I posted a sample C program above demonstrating this possibility, where 15.94 gets represented internally as 15.9399995803833.
We're lacking any further evidence that would make one of these more likely.
Besides the fact the code you provided doesn't reproduce the issue, an obvious flaw in your theory is such a coding error would apply to all imported quotes.
0 -
"Besides the fact the code you provided doesn't reproduce the issue, an obvious flaw in your theory is such a coding error would apply to all imported quotes."
Wrong and wrong. By that same logic, the problem can't be with the quote provider either, since the bug, whatever it is, would apply to all provided quotes (which it doesn't). As I stated above, my sample code demonstrates using 32-bit floats could in principle get the same order of magnitude error as that observed. Just because it doesn't reproduce the bug exactly doesn't mean that's not the core cause of the problem -- we'd have to know the exact computational steps used by the code.
Regardless, I'll show conclusively that the bug is in Quicken, not the quote provider. And then I'll explain how using 32-bit representations of quotes can cause the bug to occur with just some quotes and larger numbers of shares.
Here's a video that shows how to reproduce the bug with a new, empty Quicken file, adding a brokerage account, adding 134,130.373 shares of VWSUX, downloading historical prices for the past year, and then looking at the market value of the VWSUX holdings on 10/2/2020:
https://www.dropbox.com/s/h7gnis8wkf1fvep/reproduce-quicken-value-bug.2020.11.11.mov?dl=0
I captured the downloaded historical prices by removing the Delete permission from the folder C:\Users\john\AppData\Local\Temp, then downloaded the historical prices in Quicken, then copied the two .tmp files that were created. You can see these two files here:
https://www.dropbox.com/s/m6b35l9nzxsegl0/reproduce-quicken-value-bug.2020.11.11.zip?dl=0
The downloaded price of VWSUX on 10/2/2020 was "15.9400." This definitely rules out the quote provider.
It's been 40 years since my last course in numerical analysis, but I'll try to explain as clearly as I can why using a 32-bit representation for quotes would only affect some quotes and mostly only with larger numbers of shares.
In general, a binary number can only approximate decimal numbers such as 15.94. The more bits in the binary number, the closer the approximation and the smaller the error.
We can estimate the minimum error involved in the market-value computation for VWSUX. The displayed value was $2,138,038.14, for 134,130.373 shares. Quicken is rounding the displayed value to two decimal digits, so the largest actual value that would round to the displayed value would be $2,138,038.14499999. (64-bit floating numbers represent about 15 decimal digits). That would correspond to a maximum internally represented quote of $2,138,038.14499999 / 134,130.373 shares = $15.939999995377600000, or an absolute error of $0.00000000028999003198748600.
That absolute error is suspiciously close to 1 part in 2^32 (0.00000000023283064365387000). That clearly suggests that Quicken is using a 32-bit representation for quotes.
The value Quicken displays for market value is given by this formula:
displayed-market-value = (true-quote + error) * number-of-shares
And that can be rewritten as:
displayed-market-value = (true-quote * number-of-shares) +
(error * number-of-shares)Thus, the larger the number of shares, the larger the absolute error in market value.
Rounding the market value to two decimal digits magnifies the effect of small errors. A total error of 0.000001 can reduce the computed market value from 2,000,000.005 (which rounds to 2,000,000.01) down to 2,000,000.004999 (which rounds to 2,000,000.00).
But very small errors only affect the rounding of market values whose fractional parts are very, very close to 0.005. As the number of shares increase, however, the errors get larger, and more market values will be affected by rounding errors.
This is why we don't see the error that often and why it's more likely to occur with larger market values.
0 -
John Ellis said:
"Besides the fact the code you provided doesn't reproduce the issue, an obvious flaw in your theory is such a coding error would apply to all imported quotes."
Wrong and wrong. By that same logic, the problem can't be with the quote provider either, since the bug, whatever it is, would apply to all provided quotes (which it doesn't). As I stated above, my sample code demonstrates using 32-bit floats could in principle get the same order of magnitude error as that observed. Just because it doesn't reproduce the bug exactly doesn't mean that's not the core cause of the problem -- we'd have to know the exact computational steps used by the code.
Regardless, I'll show conclusively that the bug is in Quicken, not the quote provider. And then I'll explain how using 32-bit representations of quotes can cause the bug to occur with just some quotes and larger numbers of shares.
Here's a video that shows how to reproduce the bug with a new, empty Quicken file, adding a brokerage account, adding 134,130.373 shares of VWSUX, downloading historical prices for the past year, and then looking at the market value of the VWSUX holdings on 10/2/2020:
https://www.dropbox.com/s/h7gnis8wkf1fvep/reproduce-quicken-value-bug.2020.11.11.mov?dl=0
I captured the downloaded historical prices by removing the Delete permission from the folder C:\Users\john\AppData\Local\Temp, then downloaded the historical prices in Quicken, then copied the two .tmp files that were created. You can see these two files here:
https://www.dropbox.com/s/m6b35l9nzxsegl0/reproduce-quicken-value-bug.2020.11.11.zip?dl=0
The downloaded price of VWSUX on 10/2/2020 was "15.9400." This definitely rules out the quote provider.
It's been 40 years since my last course in numerical analysis, but I'll try to explain as clearly as I can why using a 32-bit representation for quotes would only affect some quotes and mostly only with larger numbers of shares.
In general, a binary number can only approximate decimal numbers such as 15.94. The more bits in the binary number, the closer the approximation and the smaller the error.
We can estimate the minimum error involved in the market-value computation for VWSUX. The displayed value was $2,138,038.14, for 134,130.373 shares. Quicken is rounding the displayed value to two decimal digits, so the largest actual value that would round to the displayed value would be $2,138,038.14499999. (64-bit floating numbers represent about 15 decimal digits). That would correspond to a maximum internally represented quote of $2,138,038.14499999 / 134,130.373 shares = $15.939999995377600000, or an absolute error of $0.00000000028999003198748600.
That absolute error is suspiciously close to 1 part in 2^32 (0.00000000023283064365387000). That clearly suggests that Quicken is using a 32-bit representation for quotes.
The value Quicken displays for market value is given by this formula:
displayed-market-value = (true-quote + error) * number-of-shares
And that can be rewritten as:
displayed-market-value = (true-quote * number-of-shares) +
(error * number-of-shares)Thus, the larger the number of shares, the larger the absolute error in market value.
Rounding the market value to two decimal digits magnifies the effect of small errors. A total error of 0.000001 can reduce the computed market value from 2,000,000.005 (which rounds to 2,000,000.01) down to 2,000,000.004999 (which rounds to 2,000,000.00).
But very small errors only affect the rounding of market values whose fractional parts are very, very close to 0.005. As the number of shares increase, however, the errors get larger, and more market values will be affected by rounding errors.
This is why we don't see the error that often and why it's more likely to occur with larger market values.
First, it doesn't matter what you or I think the issue is. We will not be correcting the issue but, thank you for being obstinate. I no longer believe the issue to be limited to a spurious quote imported into Quicken.If we set the number of shares by 10,000,000 for a variety of securities and import a range of the security price quotes, the market values displayed imply the imported prices are truncated at the eighth decimal place. The error always appears to be -0.00000001. This result appears consistent with converting a price from a character string to a double-precision floating point and then back to a character string truncated at the 8th decimal place.A single-precision floating point number simply does not provide sufficient precision to produce the results we see.1 -
a) I hope we have reached tha stage that we see this not as a "truncating rather than rounding" issue, but rather as a storing a number to a different precision than it is displayed (supported by @Jim_Harman 10/30 exercise).
b) My experiment for the day -- I downloaded a csv file from Yahoo finance and opened said file in Notepad (rather than Excel). 28 days of prices, Open, Hi, Lo, Close, Adjusted Close makes for 140 prices. 'Randomly' chose XLU - a SPDR Utility ETF.
56 of those prices were xx.xx999z where the z was 7 to 9. Examples = 59.529999 and 64.879997, 48 prices were high in the form of xx.xx000z. Thus 104 of 140 prices (74%) were off of the 'exact' penny by up to 3e-4 of a cent (3e-6 of a dollar). That applies to 19 of the 28 closing prices. Yahoo displayed all those prices to the penny but downloaded differently.
When I work the file through Excel to get the columns and data right without mathematical manipulation and then import into Quicken, those same 'inaccuracies' seem to come through.
This discrepancy appears to only be noticeable if one has more than 100,000 shares (or thereabouts).
Since it comes from a third-party vendor (Yahoo in this case) and passes through into Quicken through a csv file import, it seems reasonable that it could come from their vendor that way as well, and that it would flow in unchanged via a quotes or historical price download.
I’ll also note the same exercise with VWSUX did not show such inaccuracies.Such a problem involving only holdings of 100,000 shares and differences of a penny are not worth this intensity of discussion (IMO). Time to move on.5 -
"Such a problem involving only holdings of 100,000 shares and differences of a penny are not worth this intensity of discussion (IMO)."
Certainly not worth it to you. I explained above why the incorrect market values were a mild inconvenience for me.
0
