Cash Balance on Register doesn't agree with Cash Balance In "Holdings"

sahurd

As I understand it, the "Holdings" information is calculated from within Quicken (not just a parroting of what my brokerage company reports). In this case, there's a significant discrepancy between the value when I click on "Holdings" vs. the latest Cash Balance in the register. Am I misunderstanding this? It's in agreement for all accounts except one. What might cause the discrepancy?

Jim_Harman

To reduce the deleting and re-adding, you may be able to do the following. This assumes you are using a current version of Quicken and you have not set the Use Average Cost option for this fund.

-- back up first

-- delete the remaining Placeholder if it is for zero shares. If not, replace it with a shares Added or Removed transaction for the required adjustment, noting the date so you can get rid of it later, after you have corrected any transactions that required the adjustment in the first place.

-- delete the Remove and Adds for the name and symbol change.

-- correct the transactions prior to the change, so that the pre-change share balance is correct.

-- enter a Mutual Fund Conversion for the name/symbol change. This will Remove all the old shares and replace them with the correct tax lots of new shares.

-- If you have partial sales after the conversion, you may have to correct the tax lot assignments.

Good luck! 

Jim_Harman

The holdings including cash shown on the Account Overview page that is displayed when you click on Holdings reflect your Quicken data and not what was downloaded from your brokerage. (To see the brokerage data, you can look in Tools > Online Center)

However the Holdings data defaults to today's date while the transaction list (register) shows your ending balance, including any future-dated transactions. You can change the date in the Holdings view by changing the As of date.

To further confuse matters, you may have hidden Placeholder transactions in the transaction list that affect your cash balance. To see these, go to  Edit > Preferences > Investment transactions and make sure the Show hidden transactions box is checked. 

sahurd

So given that, if I'm looking at the holdings data from today & the last transaction list entry, and there are no future-dated transaction, those two numbers *should* agree. I'll look for placeholder transactions, as those are the most likely culprits. Thanks!

Tom Young

Well cash discrepancies certainly are possible if you're making manual entries into your Account and not simply relying on downloads to do your accounting for you.  So if you know that a security is going to pay a dividend on, say 12/17/2019, and you enter that dividend manually, you're probably going to have a cash discrepancy during 12/17/2019 vis a vis what the broker is reporting online since brokers typically don't show that dividend online until the following day.

Jim_Harman

Yes but @Tom Young the discrepancy here is between the cash in the Holdings (Account Overview) view and the Transaction list. I don't think these should be out of sync unless the dates are different or there are Placeholders or other hidden trnasactions.

sahurd

So I found what appears to be the problem. In the days before I started using Quicken 17 years ago, I had some previous transactions. Virtually all of them were of the form: transaction 1 was a purchase, with a cash amount of N/A. transaction 2 was a balancing cash adjustment (that was automatically generated by Quicken). Except that there were a few transactions where that 2nd transaction appeared to be manually entered. Those few transactions added up to the exact difference between holdings & transaction list. Once I deleted them, I got the transaction list to the correct amount, but holdings were corresponding lower. I then deleted all the placeholder entries (which weren't needed, as my share balances were all correct).

Now, all but those few transactions have the actual amount in the cash amount as do their corresponding balancing cash adjustment entries. But I'm left with those few transactions that were problematic, where the purchase still has N/A for the cash amount. How do I make those the same as the others (e.g. no N/A)?

Jim_Harman

The N/A purchase with a balancing cash adjustment is what you get when you try to fix a placeholder by entering a transaction that affects the cash balance on a date prior to the placeholder. This happens even if the Placeholder is for zero shares. 

If you still have N/As, you probably still have placeholders lurking somewhere. Look at the Placeholder Entries tab at the bottom right of your transaction list. Does it a nonzero number next to it? Click on it and it will show you the Placeholders.

If you are sure all the placeholders are gone, you can try deleting and re-entering the N/A transactions

Be sure to back up your data file before messing with historical transactions.

markus1957

Cash is further complicated by the inconsistent manner in which investment firms download to Quicken, the value of money market funds and sweep accounts.

1. Some don't show MMFs as holdings but report them as cash.
2. Some (Morgan Stanley) don't show MMFs and also don't show them as cash; their value is just ignored completely. Cash is reported correctly.
3. Some don't report cash or money market funds.
4. Some compound daily interest and report that value; but then also report a lump interest payment transaction at the end of the month.

A while back I read the OFX standard for handling cash/MMFs and learned there are multiple ways of reporting them that comply with the standard.  The old way was to report MMFs as cash.  Firms have tried to update and get it right but most of mine do not. The Vanguard traditional mutual fund account is the only one I know of that reports MMFs and cash correctly in Quicken.

For a long time I have had to turn OFF the compare to register after download feature because it always reports errors for MMF holdings.

sahurd

So I again have narrowed things down further...once I found a few more pesky placeholder transactions. Now it's down to transactions from 1 security. That one had a later merger event & there was a placeholder for the successor security. Those are the only transactions I still have where the cash amount of the purchase was "N/A". Any ideas?

sahurd

> @Jim_Harman said:
> The N/A purchase with a balancing cash adjustment is what you get when you try to fix a placeholder by entering a transaction that affects the cash balance on a date prior to the placeholder. This happens even if the Placeholder is for zero shares. 
>
> If you still have N/As, you probably still have placeholders lurking somewhere. Look at the Placeholder Entries tab at the bottom right of your transaction list. Does it a nonzero number next to it? Click on it and it will show you the Placeholders.
>
> If you are sure all the placeholders are gone, you can try deleting and re-entering the N/A transactions
>
> Be sure to back up your data file before messing with historical transactions.

Jim-This is exactly right. I think the big problem is that there's a later event where the shares are removed & readded (mutual fund name/symbol change). So I'm guessing that I'll need to delete the final sale of the successor fund, delete the merger like event, and delete the individual transactions, before readding all of the above. Does that make sense, or is there a better/easier way?

Jim_Harman

To reduce the deleting and re-adding, you may be able to do the following. This assumes you are using a current version of Quicken and you have not set the Use Average Cost option for this fund.

-- back up first

-- delete the remaining Placeholder if it is for zero shares. If not, replace it with a shares Added or Removed transaction for the required adjustment, noting the date so you can get rid of it later, after you have corrected any transactions that required the adjustment in the first place.

-- delete the Remove and Adds for the name and symbol change.

-- correct the transactions prior to the change, so that the pre-change share balance is correct.

-- enter a Mutual Fund Conversion for the name/symbol change. This will Remove all the old shares and replace them with the correct tax lots of new shares.

-- If you have partial sales after the conversion, you may have to correct the tax lot assignments.

Good luck!