Holdings window is truncating rather than rounding fractional pennies

John Ellis

The Holdings window for a Schwab brokerage account is truncating fractional pennies rather than rounding, as Schwab does in its reports.  For example:



15.94 * 134,130.373 = 2,138,038.14562

The pennies should be rounded to 0.15 rather than truncated to 0.14.

Quicken_Tyka

Hello @John Ellis

Thank you for taking the time to visit the Community to report this issue, although I apologize that you have not received a response.

I am currently running R29.22 and I was unable to recreate this issue. Using the numbers provided I see the number is rounded.

I would start by running the Validate & Repair tool on the data file from the File menu > File Operations > Validate & Repair option.

In the Validate File window that opens, click the top box to "validate file" and then click OK.

When the validation completes, a data log will open in Notepad, please let us know if any errors or issues are found/repaired, and when ready close and re-open Quicken.

-Quicken Tyka

Jim_Harman

One possibility is that you have several tax lots of this security and Quicken's total is based on adding up the rounded market values of the individual lots. 

Click on the + sign next to the security name to view the tax lots and see if this is the case.

John Ellis

Validate File did repair a number of inconsistencies in my R29.22 (see the attached .log) -- I think it's been a number of years since I last ran it.  But after restarting Quicken I see the same results.

Rocket J Squirrel

John Ellis said:

15.94 * 134,130.373 = 2,138,038.14562

The pennies should be rounded to 0.15 rather than truncated to 0.14.

I doubt Quicken is multiplying. It's more likely it's dividing. How else would it get that average value per share?

2,138,038.14 / 134,130.373 = ‭15.93999995810047‬
which rounds to $15.94.

The sum of the Market Value column is more likely calculated by adding the dollar values of each lot. But since you've hidden the individual values, we can't say.

I can tell you how to make this (non) issue vanish. Under the gear icon at the top right of the Holdings window, un-check "Show cents".

John Ellis

"I doubt Quicken is multiplying. It's more likely it's dividing. How else would it get that average value per share?"

The Quote/Price for the date selected in the Holdings window comes from the security's Price History, not from computing an average. You can prove that to yourself by right-clicking the security in the Holdings window and doing Price History. Then right-click the date in Price History, do Edit, and change the Price to something else. Click OK and observe that new price in the Holdings window.

For the security in question here, the Price History shows its value on the given date as 15.94, which is what is shown in the Holdings window.

Rocket J Squirrel

OK. I'm not sure what I'm looking at in your original image because so much is obscured. My Holdings window doesn't look like yours. Mine shows the totals for each security at the top, not at the bottom. What version of Q are you running?

John Ellis

Version given above. The screenshot is the Holdings window which appears when you click the Holdings button at the top left of the register for a brokerage account. Here's a screenshot showing the full width of the window:

Sherlock

Do you experience the same anomaly using the Portfolio Value report?  Note: We may set the number of decimal places displayed for the prices and shares in the reports up to 6 which may expose a round-up: select Edit > Preferences... and Reports only

Sherlock

John Ellis said:

Version given above. The screenshot is the Holdings window which appears when you click the Holdings button at the top left of the register for a brokerage account. Here's a screenshot showing the full width of the window:

And the market value is now correct?

John Ellis

Tyka,

 Building on Jim's suggestion, I exported the 48 individual lots for the security to a CSV using this recipe, and then imported that into an Excel sheet:

https://www.dropbox.com/s/uk2dby73th135zh/quicken-error-lots.2020.10.29.xlsx?dl=0 

The shares of the individual lots sum up to the total shown in Holdings window. 

The price for 9/30 is shown as 15.939957, not 15.94, but using that price results in a $6 discrepancy. I also tried summing the individual market values of the lots, using both prices, and optionally rounding the values to the nearest cent before summing.  None of those account for the truncation.  (See the spreadsheet.)

If anyone else has a hypothesis, feel free to download the spreadsheet and try it out.

John Ellis

Sherlock: "And the market value is now correct?"

Good catch. That points to the source of the problem: While testing Rocket's idea, I had manually edited the price for 9/30 to 16.00 and then changed it back to 15.94, which then caused the correct market value to show.

This suggests the problem is related to the last price shown for the lot on 9/30:

(Also see the spreadsheet of all the lots.)

I've restored my Quicken files from backup prior to my edit, and it's again showing the incorrect truncated market value.   

To summarize:  

- The Holdings window for 9/30 shows the Quote/Price as 15.94.

- The lot for the security for 9/30 shows the Quote/Price as 15.939957.

- The Price History for the security shows the Quote/Price as 15.94.

- Manually changing the Quote/Price for 9/30 in the Price History to 15.94 eliminates the truncation.

- The spreadsheet shows that trying to use 15.939957 to compute market value doesn't yield the 1-cent discrepancy.

q_lurker

As a sidebar comment, I wonder if John has a Single Mutual Fund account, thus the quote value shown at the bottom of that column. Not applicable for a multi-security account. 

Sherlock

q_lurker said:

As a sidebar comment, I wonder if John has a Single Mutual Fund account, thus the quote value shown at the bottom of that column. Not applicable for a multi-security account. 

The recent image provided indicates otherwise.  There appear to be at least 11 securities in the account.

Rocket J Squirrel

Now that @John Ellis revealed the + signs in the window we can see each line is 1 security.

Anyway, I'm out. I can't get excited about going after a penny. Have fun.

John Ellis

"Do you experience the same anomaly using the Portfolio Value report?"

- The Portfolio Value report shows the incorrect (truncated) value.
- The Investing tab (Show Value, Group By Accounts) shows the incorrect (truncated) value.

"We may set the number of decimal places displayed for the prices and shares in the reports"

It was set to 3. But setting it to 2, 4, or 6 has no effect on the market value shown in the  Portfolio Value report, the Investing tab, or the Holdings window -- the market value still shows the incorrect (truncated) value.

The Quote/Price in the Portfolio Value report shows 15.94, 15.940, 15.9400, and 15.940000 for settings of 2, 3, 4, and 6.  The Price History Lots always shows 15.93997.

As described above, it's pretty clear the bug is related to the price shown in Price History Lots (15.939957).  Vanguard only quotes its funds in cents (two digits).

John Ellis

Correction above: Price History => Lots.

Jim_Harman

Something is amiss here. The market values for the individual lots in column D of your spreadsheet are based on a share price of $15.93, which does not match either the share price of the last transaction or the correct closing price of $15.94.

[edited]
It sounds like the original price in the price history before you set it to 15.94 was something other than 15.939957. What was the total dollar amount of the last purchase? If you divide that by the number of shares purchased, you will get the original 9/30 price per share. Maybe that will be so close to $15.94 that Quicken rounds it to $15.94 for display purposes in the price history. 

When you have 134,000 shares, even a very small difference in the share price can cause a $0.01 difference in the total value.

Note that when comparing to a statement, you must have the correct closing quote in the price history and not the computed price per share of the last purchase. 

John Ellis

"The market values for the individual lots in column D of your spreadsheet are based on a share price of $15.93"

Sorry for the confusion, ignore column D (I did in all my analysis above) -- that sheet was exported from the Investing tab with today's date, which has a price of 15.93. (I just wanted the number of shares per lot.) 

Alternatively, here's a fresh CSV exported from the Investing tab for 9/30 (15.94):
https://www.dropbox.com/s/8ok0ity0pjv3sen/quicken-error-lots.2020.10.29.csv?dl=0 

Column D sums to the rounded value of the 9/30 total market value, not the truncated value.

"It sounds like the original price in the price history before you set it to 15.94 was something other than 15.939957."

As described above, the Holdings window and the Price History for the security showed 15.94, which is what the Schwab statement shows.  But manually editing it to 15.94 (sic) changed the truncated value to the correct rounded value.  (I just repeated this on the file recovered from backup and observed the same behavior.)

"What was the total dollar amount of the last purchase?"

Schwab says Lot 9/30 was 116.117 shares at $15.94 with a total amount of $1850.90. Quicken shows the same number of shares and total amount for the Lot.  But it shows the Quote/Price for the lot as 15.939957, which is the total amount (1850.90) / shares (116.117). 

But note that Quicken is not using 15.939957 for computing total market value in the Holdings window, Investing, or the Portfolio Value report.  15.939957 * 134,130.373 shares = $2,138,032.38 (rounded), whereas the actual market value is $2,138,038.15 (a difference of $5.77).

Jim_Harman

This is an interesting academic exercise, but I can't help you further.

It probably has something to do with rounding in the 6th decimal place, or rounding the number displayed in the price history, or as I said before rounding the individual lots before adding.

But if entering the correct closing price in the price history fixes the problem, why worry further?

John Ellis

Quicken_Tyka,

Here's a screen recording that concisely demonstrates the bug:
https://www.dropbox.com/s/5nqijy8tcwrjb5q/quicken-rounding-bug.2020.10.29.mov?dl=0 

Manually editing the Price History's 9/30 price for a security from 15.94 to 15.94 (sic) changes the total market value by a penny, from an incorrectly truncated amount to a correctly rounded amount.  If your developers want to debug this, I've saved away the files.

This is a mild inconvenience. I reconcile my brokerage accounts against the printed statements, long ago losing trust that Quicken would correctly download transactions and quotes. About every two or three months I find a discrepancy -- sometimes the total value is off by a few cents, sometimes a lot more due to a transaction that never got downloaded.  I also learned long ago that these little errors sometimes mask a bigger problem and I shouldn't let them slide.

What's a penny, you say? If Quicken can't get simple arithmetic right, why should customers trust it to get the bigger things right? But Quicken's brand is obviously not my concern.

What's a penny?  Office Space has a funny plot line about that...

Thanks,

John

Sherlock

My WAG is the VWSUX price imported from the third party quote provider for 9/30 was approximately $15.93999999.  

Jim_Harman

Here is an exercise that is left to the reader, confirming @Sherlock's WAG:

Enter bought transactions for 134,130.373 shares at prices per share of 
15.939999
15.9399999
15.93999999 and
15.94

Observe what is shown in the Bought dialog as the price per share and the total cost.

Quicken's rounding rules appear to be slightly different from the Help you get when searching for "decimal places"

When there are 5 or 6 9s for a total of 7 or 8 decimal places in the share price, it rounds the display of the share price to 15.94 but stores the un-rounded number and uses it for the calculation. 

q_lurker

@Jim_Harman Interesting set of results.  Conventional Wisdom has always said Enter the Shares and total and let Quicken compute the price for any buys, sells or similar transactions.  Those results speak to why.  

Another tweak to ruminate on:
Buy 1341.30373 shares for the total price of 2,138,038.14 or 2,138,038.15 and note the prices Quicken determines.

Jim_Harman

Absolutely correct that you want to let Quicken compute the price per share.

It appears that Quicken is performing its calculations at some high level of precision. It stores the high precision result but rounds the share prices and quantities to 6 decimal places for display purposes. Dollar amounts are rounded to 2 decimal places.

Data entry is truncated to 11 digits and the maximum amount of a transaction is $99,999,999.99

Jim_Harman

And for those who like big numbers, the internal precision is at least 16 digits:

If you buy 99,999,999 shares at $1.00
and the share price goes to 99,999,998
Quicken shows the value of the holding as 
9,999,999,700,000,002.00
which is correct.
  

John Ellis

"Absolutely correct that you want to let Quicken compute the price per share."

And in the case of the bug reported here, Quicken is indeed computing the price per share. The transactions are all downloaded from Schwab (not manually entered), and the market quotes are downloaded by Quicken from wherever Quicken gets its quotes. 

Note that Vanguard quotes all its funds to two decimal digits (pennies), so whenever Quicken is displaying the total market value of a fund, it should be using a two-decimal-digit price quote that it has downloaded, not a 6-digit computed share price from a transaction.

Sherlock

John Ellis said:

"Absolutely correct that you want to let Quicken compute the price per share."

And in the case of the bug reported here, Quicken is indeed computing the price per share. The transactions are all downloaded from Schwab (not manually entered), and the market quotes are downloaded by Quicken from wherever Quicken gets its quotes. 

Note that Vanguard quotes all its funds to two decimal digits (pennies), so whenever Quicken is displaying the total market value of a fund, it should be using a two-decimal-digit price quote that it has downloaded, not a 6-digit computed share price from a transaction.

In this case, as I previously stated, Quicken imported the VWSUX price from the third party quote provider. 

John Ellis

"Quicken imported the VWSUX price from the third party quote provider."

That's not an unreasonable guess, though regardless of the cause, it's Quicken's responsibility to provide its customers with accurate quotes in the precision used by the individual funds (two digits in the case of VWSUX) and to use those accurate quotes to compute market values. If the problem is with the partner provider, then Quicken should work with the provider to fix the issue.

Sherlock

John Ellis said:

"Quicken imported the VWSUX price from the third party quote provider."

That's not an unreasonable guess, though regardless of the cause, it's Quicken's responsibility to provide its customers with accurate quotes in the precision used by the individual funds (two digits in the case of VWSUX) and to use those accurate quotes to compute market values. If the problem is with the partner provider, then Quicken should work with the provider to fix the issue.

It is not a guess.  It's based on the information you provided.  It's only the actual price obtained that is a guess since we're not able to display it without the round up.  The third-party quote provider is responsible for the quotes they provide:  https://www.quicken.com/terms-of-use

Of course, it in everyone's interest that the quotes be accurate.  In this case, we're talking about a very small error.  There have been significantly worse errors at times with the prices pulled from the quote service provider.  I doubt there will be a real fix anytime soon. 

John Ellis

"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>&nbsp; &nbsp; float f1 = 15.94;</div><div>&nbsp; &nbsp; double f2 = 134130.373;</div><div>&nbsp; &nbsp; double f3 = f1 * f2;</div><div>&nbsp; &nbsp; printf ("%0.14lf %0.6lf\n", f1, f3);</div><div>&nbsp; &nbsp; double f1d = 15.94;</div><div>&nbsp; &nbsp; double f3d = f1d * f2;</div><div>&nbsp; &nbsp; 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>