Quicken sharing prices across accounts causes problems in reconciling

skeleton567 · September 2019

Apparently Quicken uses a common price history for all accounts. When you enter transactions, it often will ask if you want to change the quantity or change the share price get to the correct transaction total. Quicken itself recommends altering the price, which makes sense to reconcile to totals.

However, I have three Fidelity accounts which all contain individual lots of the same securities. I enter transactions in the first account and take the recommended calculated price which can go to multiple decimal places. Account is reconciled to the statement using the Holdings page.

Then I enter transactions into the second account. Lots may differ resulting in different price calculations to several decimal places. So I take the recommended calculated price which, due to different lot sizes, may be different from the transactions in the first account. The second account is reconciled to its statement using the Holdings page.

Then I return to the first account which no longer matches the statement balance because the prices calculated for the second account have caused the holdings for the first account to be priced differently.

Quicken needs to maintain price history per account per security to avoid this problem that they lead you to create by letting them calculate a security price on a buy.

GeoffG · September 2019

For what you are saying to be true, Fidelity would have different prices for the same securities across your various accounts. This obviously is not the case. You need to enter one price per day and reuse that price for the various accounts that hold the same security. To reconcile investment accounts, you must match the number of shares and the cost, not the market value to what Fidelity states.

Jim_Harman · September 2019

To further complicate matters, the share prices for a given day may change again if you download quotes, because that records the official closing prices. The heirarchy of prices is given here
https://www.quicken.com/support/how-update-security-prices

These discrepancies are very small, right?

Usually for performance and capital gains tracking purposes it is most important to have the number of shares and the total amount paid recorded accurately, and let Quicken calculate the price per share, as the Bought dialog recommends.

When reconciling an investing account, I find it most useful to compare the number of shares for each holding to the statement rather than the total value, which can be off by a little due to the rounding errors you are observing.

Jim_Harman · September 2019

Also if you are buying individual stocks or ETFs where the price varies during the day, it is perfectly OK to have a different share price for each lot. These prices are recorded in the transaction list(s) and in the Portfolio views if you group by account and click on the + next to a security to view the lot info.

What Quicken shows as the market value in the account's Holdings view is based on the price recorded per the hierarchy I referenced above.

skeleton567 · September 2019

OK, Jim, here is concrete example from today reconciling two separate accounts holding different lots of the same mutual fund:

shares price total
Account 1 Statement:
--------------------------------
AQR Managed Futures 221.044 @ 9.09 2009.28
Account 1 Quicken entry:
AQR Managed Futures 221.044 @ 9.08995 2009.28 ***
------------
Account 2 Statement:
--------------------------------
AQR Managed Futures 499.894 @ 9.09 4544.03
Account 2 Quicken entry:
AQR Managed Futures 499.894 @ 9.08998 4544.03
------------
--------------------------------
Returning to Account 1: it now shows
AQR Managed Futures 221.044 @ 9.08998 2009.29 ***
------------

Now, this demonstrates exactly the design problem with sharing price history among accounts. Note that the first account is now out-of-balance. I had to extend prices to more decimal places to match the statement total in each account. Doing so now has thrown the first account off.

The conflict arises because the funds are traded in dollar amounts so there are many, many trades with decimal places in the shares, thus the advice to accept or modify the calculated share price in order to match the share quantity and trade total.

Suppose one of these was your account and one belonged to another investor. You wouldn't want you account being altered by another, would you? Worse, your next statement will probably have an opening balance that has changed from the closing balance on your previous statement.

I'm sure this can be 'pooh-poohed' away as OK, but I developed computer systems for 42 years and this would be entirely unacceptable in any position I ever held, especially in the banking and financial realm where balances must match and history may not change.

skeleton567 · September 2019

You are correct that the share price varies with trades during the day, but portfolios are valued after the market close at the closing price, regardless of whether or not you had any trades. Multiple downloads for mutual funds during the day would only retrieve the previous day closing price. The problem remains that Quicken in going to use the most recent price for a single security across all accounts that hold it.

q_lurker · September 2019

I am missing something here. Are the cited examples transaction entries in the transaction list of the two accounts, or are they presentations for what the holdings or portfolio views show for the two accounts?

I know of no circumstance where entering a transaction in account 2 changes a transaction entry in account 1.

Entering a transaction in account 2 will change the portfolio/holdings view for the account 1 holding within the hierarchy of price entries. (https://www.quicken.com/support/how-update-security-prices)
________

Hierarchy for prices entered

Manual price entry
Current Price (Quote)
Historical Price (Quote)
Price downloaded with broker data through One Step Update or Update Now
Import Price (CSV)
Transaction Price entered when manually entering a transaction in the brokerage account list.

Each entry method overrides the price(s) received by the method just below it. This means that the price displayed after entering a Buy transaction (for example) would be replaced by the price imported from a CSV downloaded from an investing site (such as Yahoo! Finance), which would be replaced by the price transmitted by the broker when performing One Step Update, and so forth.
__________

That says that each time you enter a transaction with a price, Quicken updates the price history information for that security UNLESS one of the other price sources has already been applied to that security for that date.

If that is the circumstance you are complaining about, the solution is to enter 9.09 as a manual price entry for that security for that date (or do a download quotes or a One-Step Update or similar).

Now to your example, 221.044 * 9.09 = 2009.2899. Quicken is going to show that as 2009.29. That your FI shows it as 2009.28 surprises me. Similarly, 499.894 * 9.09 = 4544.0364. Quicken is going to show that as 4544.04. I am again surprised your FI rounds down on that.

The bottom line (for me) is that if 9.09 is the closing price for that security for that date, that is the value that should appear in the price history of that security for that date.

skeleton567 · September 2019

> @q_lurker said:
> I am missing something here. Are the cited examples transaction entries in the transaction list of the two accounts, or are they presentations for what the holdings or portfolio views show for the two accounts?
>
> I know of no circumstance where entering a transaction in account 2 changes a transaction entry in account 1.
>
> Entering a transaction in account 2 will change the portfolio/holdings view for the account 1 holding within the hierarchy of price entries. (https://www.quicken.com/support/how-update-security-prices)
> ________ Hierarchy for prices entered
>
> * Manual price entry
> * Current Price (Quote)
> * Historical Price (Quote)
> * Price downloaded with broker data through One Step Update or Update Now
> * Import Price (CSV)
> * Transaction Price entered when manually entering a transaction in the brokerage account list.
>
> Each entry method overrides the price(s) received by the method just below it. This means that the price displayed after entering a Buy transaction (for example) would be replaced by the price imported from a CSV downloaded from an investing site (such as Yahoo! Finance), which would be replaced by the price transmitted by the broker when performing One Step Update, and so forth.
> __________
>
> That says that each time you enter a transaction with a price, Quicken updates the price history information for that security UNLESS one of the other price sources has already been applied to that security for that date.
>
> If that is the circumstance you are complaining about, the solution is to enter 9.09 as a manual price entry for that security for that date (or do a download quotes or a One-Step Update or similar).
>
> Now to your example, 221.044 * 9.09 = 2009.2899. Quicken is going to show that as 2009.29. That your FI shows it as 2009.28 surprises me. Similarly, 499.894 * 9.09 = 4544.0364. Quicken is going to show that as 4544.04. I am again surprised your FI rounds down on that.
>
> The bottom line (for me) is that if 9.09 is the closing price for that security for that date, that is the value that should appear in the price history of that security for that date.
------------------------------------------------------------------------------------------------------------------
Yes, you are missing the point. This is all about price history. Follow this closely:

I use the Holdings page to enter month-end prices and reconcile FIRST account. In order to reconcile to Fidelity statement TOTAL FOR THAT SECURITY, I have to enter up to the six decimals that are available. Total for that security now matches the Fidelity total.

I use the Holding page to enter current prices and reconcile SECOND account. In order to reconcile to Fidelity statement month-end TOTAL FOR THE SAME SECURITY but a different account and different number of shares, I may have to enter up to the six decimals that are available, but this may have to be a different price in order to match the SECOND fidelity total for the same security with possibly a different number of shares.

You are partially correct that this is due to rounding. That's the reason for entering more decimal places to match the total dollar value for the security.

Now, what happens to the price that I entered to reconcile the first account? Since the date is the same ( end of month usually but not always), Quicken uses the last price entered, the one for the second account. Actually, it probably ALTERS the price from the first account because the date is the same.

Now I return to the Holding page for the first account, and VOILA!, Quicken in it's infinite wisdom uses the second price to calculate for the first account, because it has been changed in price history when the second account was reconciled using the Holdings page.

I'm not saying that any TRANSACTION is changed, never even hinted at that. But the PRICE HISTORY for the security IS DEFINITELY changed, which throws the first account out of balance.

This is not all that difficult to understand because you have two holdings of the same security in different accounts using the same price entry to reconcile to different extensions rounded to 2 decimal places. You have to understand that computer software will round to x decimal places UP or DOWN based on WHAT FOLLOWS in x+1 and further decimal places, starting from the right and working to the left.

This tends to happen multiple time per account, so that the following month your beginning account total no longer matches the beginning balance on the statement.

If each account had its own price for the security/date, this issue would not occur. This is NOT a Fidelity problem but a design problem with price history in Quicken. The same thing will happen in reconciling to ANY investment house data.

Tom Young · September 2019

"I'm not saying that any TRANSACTION is changed, never even hinted at that."

Actually, you did, and that's why I was finding the conversation so confusing.

"When you enter transactions, it often will ask if you want to change the quantity or change the share price get to the correct transaction total."

"Transaction" dollars are COST numbers and subsequent transaction entries for the same security in a different Account don't affect any other transactions' COST figures. Plus the reference to "reconciling" which, for an Investment Account boils down to per share cost and number of shares also requires that you look closely at transactions. So while it was clear to me, later, that you were referring to market prices I was trying to relate that to what seemed to be a "transaction" lead off.

I'm an accountant by training and probably have at least a touch of OCD, so I sometimes do spend time tracking down errant penny differences, on the cost side.. But I recognize that fair market value calculations can frequently result in a penny difference if the presentation of "per share FMV" is to two decimal points but the product (price times shares) is based on more than two decimal points in the per share FMV number.

I live with it because I also have the concept of immateriality.

NotACPA · September 2019

My brokerage is Fidelity Investments. In determining the value of a position, Fido TRUNCATES to the penny amount while Q ROUNDS, using standard 5/4 rounding.

Thus, the value of several of my positions as reported in Q, frequently vary from what Fido reports by a penny ... possibly several pennies in an account if multiple securities have this issue.

I determined a long time ago that, in this situation, the pennies simply weren't worth worrying about.

Jim_Harman · September 2019

Using your example I think you should be reconciling by entering the closing price as $9.09 as reported by Fidelity for both accounts and confirming that Quicken says you have the correct number of shares in each account.

You don't say what share class of this fund you hold, but I think if you look it up on Yahoo Finance or elsewhere you will see that $9.09 is the official month-end price and that is what should be recorded in Quicken's price history.

As @NotACPA notes, Fidelity truncates the total in its statement while Quicken rounds, which accounts for the discrepancy you are seeing.

q_lurker · September 2019

@NotACPA: Curious if you can identify whether Fidelity's penchant to round down also applies on buys and sells. If 221.044 shares were sold at 9.09/share, would the seller get 2209.28 (the round down value) or 2209.29, the 5/4 round pattern value)?

NotACPA · September 2019

I'll do some more research ... but so far (5 or 6 examples) I haven't found any evidence of truncation.

BUT, the research is far from complete, because I usually buy/sell in lots of 100 ... which makes this difficult to determine.

skeleton567 · September 2019

OK, I've made this very simple for y'all. The attached file demonstrates the problem and I'll walk you through it. I have created a brand new Quicken file, created two accounts, Fidelity One and Fidelity Two. There have been no downloads of transactions or prices.

1. Made purchase in Fidelity One of AQR Managed Futures on 8/30/2019.
2. Made purchase in Fidelity Two of AQR Managed Futures on 8/30/2019.

3. Reconciled Fidelity One to SECURITY LEVEL TOTAL on statement 8/31/2019 using Holdings page in Q to adjust price via additional decimal places.
4. Checked Holdings total against Fidelity statement and they match.

5. Reconciled Fidelity Two to SECURITY LEVEL TOTAL on statement 8/31/2019 using Holdings page in Q to adjust price via additional decimal places.
6. Checked Holdings total against Fidelity statement and they match.

7. Returned to Fidelity One.
8. Checked Holdings total against Fidelity statement and they no longer match.

Quicken has modified the reconciled price for AQR used for Fidelity One so that the Holdings for that date no longer match the Fidelity statement. This is not a ROUNDING problem for Fidelity and it is not a ROUNDING problem for Q. This is because the historical price recorded for Fidelity One has been altered arbitrarily by Q.

Now, as a accountant, you know that for a BUY, the COST number is important, and that for a SELL, the PRICE number is important. When Fidelity makes a SELL, I will get the amount shown for the holding in THEIR records, not for the holding in Q.

If I have reconciled an account to its statement, when I open the Holdings page for that account for that date at a later time, it should still match the statement to which it was reconciled. PERIOD. As an accountant, how can you say otherwise?

Tom Young · September 2019

I understand exactly what you're referring to here.

I'd make 3 points:

After your quote price manipulation you're off by a penny in Account 1's Market Value. That's a miss of .0005%. It's immaterial. Live with it.
Your cost basis, they really only "solid" dollar amount is still correctly stated
You'll never get the price recorded in Q when you sell, except by pure dumb luck. The price changes all the time. "Market Value" numbers by their nature - relying as they do on past prices -are simply "estimates" or "approximations" of what you might get when you sell. As such a little "fuzziness" the the Market Value number is acceptable.

skeleton567 · September 2019

I guess the problem is that I, like you, must also suffer a bit - or a lot - from OCD. When I was developing software for years, that was a very good trait because the products were better for it. Much of the software we use now is of very disappointing quality.

My wife constantly reminds me that 'Done Right is good. Done is better'. I never would have gotten away with this during my years in financial institutions.

skeleton567 · September 2019

"You'll never get the price recorded in Q when you sell, except by pure dumb luck. The price changes all the time. "Market Value" numbers by their nature - relying as they do on past prices -are simply "estimates" or "approximations" of what you might get when you sell. As such a little "fuzziness" the the Market Value number is acceptable."

Please, guys, I was trading in the market over 50 years ago, so I think maybe I understand how it works. I don't need that explained. We're trying to talk about a Quicken design anomaly here that causes my accounts to go out-of-balance.

Actually, with these funds, I think the prices are NOT changing all the time. The underlying share prices do change all day as trading occurs. But I believe the funds are priced once a day after closing when all the individual holdings are updated and recorded. This is why the funds then process THEIR trades for customers because the closing price has been established.

Further, I don't agree that Market value is an 'estimate'. I believe it is a statement of the calculated fund share value based on the closing price of shares held by the fund for that day. Obviously, any buy/sell orders entered the next day will be based on the closing price of THAT day. Your statement about never getting the price recorded in Q is correct, but I don't believe it has anything to do with your argument. It's because THAT share price is now history and will likely be different for trades the following day.

If I understand correctly there are some funds whose share price does fluctuate all day, and these are called 'exchange traded funds'. Investopedia says this:

"An exchange-traded fund (ETF) is a basket of securities that trade on an exchange, just like a stock. ETF share prices fluctuate all day as the ETF is bought and sold; this is different from mutual funds that only trade once a day after the market closes."

True, the brokerage house will round TRANSACTION TOTALS to two decimals simply because our currency has no smaller unit. But until an individual lot is traded, it's value should remain unchanged by Q, especially since the trades may involve multiple lots.

I have explained to you in clear simple terms what is happening, and what I get it a glib 'Live with it'. If that is the case, OK. Jut thought I would throw it out there for some discussion and see if anyone has an idea. Shouldn't we try to make software better? I've used Quicken since 1986 so I sort-of know how it works too, even though they try to be very secretive.

q_lurker · September 2019

@skeleton567 : It appears to me that everyone responding to you has a clear picture of the situation.

As I see it, AQR establishes the NAV (Net Asset Value) of its fund, a specific class, as 9.09 on a specific date. They are saying that for trades requested for that date, that is what one share of the fund is worth (excluding loads and commissions that may or may not apply; I have not check out that specific fund). But they - not Fidelity, not you, not me, not Quicken establish the value of one share of their fund. They report that NAV to pretty much everyone and anyone. It is public knowledge. It is in that regard an absolute.

Fidelity knows you have 221.044 shares of that fund in an account. You've agreed with them. You've made sure Quicken agree with that. For that date, that too is effectively an absolute that all agree upon.

Now Fidelity comes out and says 221.044 shares at $9.09/share is worth $2,009.28. Quicken takes the same two values and gets a value of $2,009.29. We've established (I think) that the difference is due to how these two entities treat the fractional cents caused by the fractional shares. How is it that Fidelity is right and Quicken is wrong?

"I never would have gotten away with this during my years in financial institutions."
How is it that Fidelity is getting away with this (truncating rather than rounding) today?

"We're trying to talk about a Quicken design anomaly here that causes my accounts to go out-of-balance."
I would suggest we are talking about a difference in approaches to a basic mathematical problem. I don't see this as a "design anomaly". Quicken appear to be operating in a sound mathematically sensible manner.

I checked the Mutual funds in my portfolio - 39 holdings, 9 accounts, 25 different funds, 3 financial institutions. Not one penny difference between Quicken's presentation of holding values in the account vs the FI's presentation.

You have a Fidelity issue, not a Quicken issue. Quicken is using a sound mathematical approach. Fidelity is not (IMO).

What you are asking Quicken to do - a separate price history for each account holding a security - would not make Quicken a better program. It would make it more confusing for users and more prone to error both within the program and from the user base.

'Nuf said. I'm done with this.

skeleton567 · September 2019

" How is it that Fidelity is right and Quicken is wrong?"

Well, Fidelity is the one who has the money.

NotACPA · September 2019

skeleton567 said:

" How is it that Fidelity is right and Quicken is wrong?"

Well, Fidelity is the one who has the money.

I agree with q_lurker, and I've disagreed with Fidelity for 40+ years about this.

Rounding is much more defensible than truncation.

And rounding gives a much more accurate picture of your holding.

BUT in any event, it's a FRIGGING PENNY. Quitcherbitchin.

Jim_Harman · September 2019

And the penny was in your favor!

NotACPA · September 2019

Out of curiosity, how does Vanguard, and the other fund/brokerage companies handle this?

Because I don't recall this issue EVER coming up with regard to Vanguard.

SO, I suspect that Fidelity is the outiier in this practice and Q is doing the same thing as non-Fidelity fund/brokerage companies.

skeleton567 · September 2019

I don't know that because I've dealt with Fidelity only for years. But I do know that in the past I have used Piper Jaffrey, Waterhouse, Transamerica, etc along with Q which contains now 45 years of my data because I went back and entered it all from paper records of my first job that had a retirement plan, and never had this issue with them either. I don't have a problem with Fidelity or others truncating trade totals to the penny. It's just a business practice that I understand because the penny is the smallest currency unit that we have. At least they are consistent. When I get my statements, the opening balances ALWAYS match their closing balances from the previous statement. Then I find that Q has altered its opening balance because I have reconciled a different account.

This discussion has gone way off the focus of the original issue that Q changes a reconciled account balance because it alters a security price that has been used for one account when recording transactions and/or reconciling Holdings for another account.

As I commented earlier, lets face the fact that Fidelity - or whomever you do business with, is the final authority since they have the money. If I record prices and reconcile to their numbers, Q should NOT be altering that. If I enter a price, Q should not change that in any way. It's OK that they have their hierarchy of pricing records from different sources, but a keyed price should ALWAYS be the final authority, regardless of other sources. If I even want it to be WRONG, that should be my decision. it's MY data.

Incidentally, I just experienced again ANOTHER of their stupid decisions. When I performed a file validation, instead of telling me there was something they didn't like, they arbitrarily generated a SHORT SALE transaction for an account that has been closed for twenty five years. Now the history for that account is incorrect and the balance is no longer 0.00 for the dead account.

Now THAT ain't a Fidelity problem, or anyone else's except Q.

Jim_Harman · September 2019

You should be recording the official closing price for the security in Quicken's price history ($9.09 in your examples), not a computed price based on Fidelity's reported total value of one of your holdings divided by the number of shares or the price recorded by a transaction. This price will be recorded for you if you download quotes after the market has closed and the fund has posted its NAV for the day (typically around 7PM Eastern). The cost basis and proceeds of any transactions will not be affected.

Because Quicken rounds rather than truncating as Fidelity does, the total value of the holding in Quicken may be a penny different from Fidelity's reported value.

Quicken sharing prices across accounts causes problems in reconciling

Comments

Hierarchy for prices entered

Categories