How can I fix an incorrect cash balance in an investment account when it isn't affecting the value?

aalexorr

I have an investment account that shows a Cash Balance of $40,198.33 in the lower right of the register. When I click on that, the Update Cash Balance window pops up. This states that my current cash balance is $0.00 (which is correct). The Total Market Value shown at the lower right of the register is correct. The Securities Value is too low by the amount of the Cash Balance.

If I go to Investing >> Portfolio and show Value by Accounts, this account has no cash row and the total market value is correct.

This is very strange and doesn't really affect anything, but it annoys me that it is wrong.

As for troubleshooting, I have validated and super validated the file. I have checked the Show Hidden Transactions and looked for placeholders. There were many that all had 0 shares associated with them. I deleted them all; this didn't affect anything.

Any advice will be greatly appreciated. I can't think of any way to adjust this without affecting the market value.

Thanks

aalexorr

Problem fixed, and I sort of understand the issue. I discovered many Bought (not Boughtx) transactions that had N/A for the cash amount. Accompanying each of these was a balancing cash transaction. I don't know if these were all done by me or automatically as part of an import. They were from the time period 1992 - 2007. I edited each Bought transaction, changing the transaction type to an Add Shares transaction. I made sure to update the date acquired in the dialog box (it changed for some weird reason when the window opened). I then deleted the accompanying cash balancing transaction.

This worked! The mystery is how a Bought transaction gets into Quicken where it says to remove cash from the account, says N/A in the register and yet still seemed to remove cash. Changing it to an Add Shares transaction definitely made it a non-cash transaction. Then deleting the cash balancing transactions slowly lowered the cash balance back to zero. Now the register and the holdings match.

Thanks to everyone that offered help.

Tom Young

My instinct is that your database is messed up in a way that Validate and SuperValidate can't handle.  When you've done your validation have you first done File > File Operations > Copy... and then validated the copied file? 

One thing you might try, after making a backup, is to transfer all the securities and cash to a newly created Investment Account in Quicken and see if that Account presents correct information.  If it does you might abandon the old Account, keeping it around for its transaction history only, and start "fresh" in the new Account.

aalexorr

Hi Tom:

Thanks for your help. I did do all the validation on a copied file. I really don't want to lose all the history in one place, but I could transfer all the transactions for the current holdings only, run some reports and see what I think -- good idea!

Tom Young

Doing a transfer of shares and cash between Accounts should not loose any history - it's all still there in your original Account and you can refer to that Account if necessary, for historical purposes.  (I'm assuming all the transactions in that Account are correct and there's just something hinkey in whatever pointers are pulling up the numbers in the lower right corner of your transaction list.)

Jim_Harman

Could this be an issue of whether the FI shows the cash in a money market settlement fund vs as "Cash" in the account? That would explain why the securities value in Quicken is low by the amount of the account's Cash.

If you click on the gear at the top right of the account and pick Reconcile Shares, does it show a discrepancy equal to the cash amount?

aalexorr

Hi Jim:

Thanks for the reply. The securities value is correct in the Investing >> Portfolio view. It does sweep everything into a money market fund, but I treat that as a mutual fund and not cash. Its balance is also not close to the $40,198.33 figure. The problem happened years ago when the FI switched their clearinghouse. There are probably a slew of transactions that somehow caused this, but it seems like one should be able to adjust the cash balance. The problem is that Quicken thinks the cash balance is 0.00 on a couple of screens and not on the register. To me, that seems like a bug.

I did follow Tom's advice on the "copy" file and transferred all the securities to a new account. The new account was fine, but the old account register now shows negative 40,198.33 for the securities value, 40,198.33 for the cash balance and 0 for the total market value.

It does also affect the investment performance reports slightly. Unless Quicken has a solution, I will probably just live with this as it only seems to affect the display on the register; everything else seems accurate... weird.

Jim_Harman

If you are inclined to track this down, you can click on Holdings then set the As of date back in time until the cash is correct. When you have found the date when it first goes wrong, look for transactions near that date that may be causing the problem. It might be shares Added rather than Bought for example.

Always back up before changing historical data.

aalexorr

Thanks again for sticking with this.

When I click on holdings, there is no cash shown. That is correct. The only place that seems to show a cash balance is the register -- the transaction table and the bottom right link. Unfortunately, the cash balance is all over the place in the past (the account may have changed from a cash sweep one to the way I treat it now). It has ben steadily off by the 40,198.33 since 11/18/2008 (at least that's what the register shows). I don't think it actually showed that balance until much later when something happened. For me the bottom line is this - various screens supposed to display the same information don't agree with each other. Net worth, investment balances reports, etc. all act as if the cash is $0.00 which is correct. But the register doesn't say this. Should be able to somehow fix this. Not sure if Quicken is listening to this...

Tom Young

I'm not a data base pro and you can't look at exactly how Quicken was programmed, but you're right that in all places where a certain accounting number is shown, that number should be the same in all places.  If you go into the Account and make a phony cash entry - a deposit or a transfer - does that $40,198.33 number change accordingly, e.g., the Holdings show some amount as the amount of this entry and the $40,198.33 has become #40,198.33 +/- new entry?

aalexorr

You two are very nice to dig in on this. Tom - yes, whenever you make a transaction, the cash balance changes correctly and the holding reflect this.

The account history is very messy as it goes back to the early nineties and incorporated history from more than one account. Either they didn't support transferring shares and transaction history (lot-based cost basis stuff) when I did this, or I didn't know about that. Even so, it doesn't seem possible to have two screens showing the cash balance of an account where one screen says x and the other say y. This means that these screens are not displaying the same field from the same database table. If Quicken got involved, they could explain what the screens are really showing which might help to understand how to fix the issue. The values displayed are probably calculated values in both places using different calculations. It would be nice to know what those calculations are.

q_lurker

That is all very strange ... 
A few directions I would look (to really go after it) --- 
Tools / Online Center

• For the applicable FI and account, what does the Balances Tab say for cash.  That seems to be a FI supplied value.  I have one FI that includes MM value in that number, another that doesn't.  Just might be an interesting checkpoint for you, but I am not sure it would really confirm anything for you.   

Moving Transactions

• Back up your file or work on a copy of your file.  Create a new account, as an off-line manual account.  Depending on the total complexity and scope of your real account, systematically move groups of transactions from the real account to the new account.  As you do so, monitor the change in that Cash Balance figure.  That might lead you to some transaction, security, that most directly relates to the $40,000.  Right-click on any transaction in the account to get the Move options.

Deleting Transactions

• A variation of the move process would be to systematically delete transactions going back a month, a quarter, a year at a time.  This would give you an indication of really when the $40,000 number built up or appeared. 

aalexorr

I appreciate the help. Not sure what you mean by the Balances tab. Both Quicken and the FI say there is no cash. Quicken says this in every place except in the register. I might be able to look at a report of cash transactions (balances) on the account and compare that to the register. Perhaps it will tell me when the two diverged.

aalexorr

Problem fixed, and I sort of understand the issue. I discovered many Bought (not Boughtx) transactions that had N/A for the cash amount. Accompanying each of these was a balancing cash transaction. I don't know if these were all done by me or automatically as part of an import. They were from the time period 1992 - 2007. I edited each Bought transaction, changing the transaction type to an Add Shares transaction. I made sure to update the date acquired in the dialog box (it changed for some weird reason when the window opened). I then deleted the accompanying cash balancing transaction.

This worked! The mystery is how a Bought transaction gets into Quicken where it says to remove cash from the account, says N/A in the register and yet still seemed to remove cash. Changing it to an Add Shares transaction definitely made it a non-cash transaction. Then deleting the cash balancing transactions slowly lowered the cash balance back to zero. Now the register and the holdings match.

Thanks to everyone that offered help.

Jim_Harman

The balancing cash transactions with N/A are created when there is a Placeholder and you enter Buys at an earlier date.

To make things even more confusing, the balancing transactions are hidden when Placeholders are hidden.

Deleting the Placeholder should let you enter a Buy that will reduce the cash balance.

aalexorr

Still a little confused on this. I did delete all the placeholders (they all had zero shares in them, and when you clicked on them the share balances and costs shown all matched). The balancing transactions didn’t have N/A, the Bought ones did. I first deleted both and added a buy, but that didn’t work. Doing what I did seemed to be the best solution.

Jim_Harman

It sounds like you still have placeholders, or Quicken thinks you do.

At the bottom right of the transaction list (register) there is a tab that says Placeholders. Does that have a number next to it? If you click on the tab, does it show any placeholders?

Jim_Harman

Or it may be that the balance adjustments got in there when you tried to fix things earlier, before you deleted the placeholders, but then I would not expect to see the N/As.

aalexorr

My guess is that I did something in the past to make everything work, and then some upgrade changed something which introduced the problem. There are no placeholders anymore. I deleted them all before I did any of the recent fixes. They were all for zero shares as I said earlier, with share counts and costs all matching in the window that pops up when you click on the placeholder.

The issue seems to be transactions that showed N/A for the cash amount but then actually did remove cash.

Thanks again for your feedback help on this.