Reconciling investment account

SomebodyInGNV

This is about bank reconciliation, but the problem is in an investment account, so I'm posting it in investments.

I've had an IRA for years. It doesn't have a separate cash account. I just tried to reconcile it and it's off by few bucks. The amount of the error matches exactly the amount of a cash dividend in the previous month. That previous month balanced to $0, including the transaction in question.

The problem transaction is a dividend for a money-market fund which reports as cash in Quicken. I record the transaction as dividends, not interest, if that matters.

The problem transaction is in August. I ran a backup, exported all transaction from the account as QIF, as of the beginning of the last month that reconciled, then deleted them. That resulted in the problem transaction (if that's the root cause) being deleted.

After running validate, I reimported the transactions, then tried to reconcile again. The problem remains.

Does anyone have a suggestion (other than an adjustment) to resolve this? Is there a different way to clean up problem transactions?

Thanks

Frankx

Hi @SomebodyInGNV

I am having some difficulty is following your story above, but - in short - it seems like you are stating that the IRA account does not balance and it relates to a dividend/interest income amount that was received in August. I think that you are focused on the "fact" that the account balanced in that prior month, but doesn't balance now.  You apparently ran the file validation and did the export/import activity to see if you could find what went wrong. 

The good news is that your file validation didn't show any errors. But it doesn't surprise me that you are in the same position now as you were before you did the backup > export of QIF >validate file > import the QIF.  Because that process you used (with the exception of the file validation) did nothing.  Think of it this way - first, you exported data that was giving you an error,  and then you simply imported that same data.  So if you expected the export/import function to fix it, those expectations are incorrect.

Moving forward - from what you said above - I believe that you've already identified the problem.  It is that one dividend that - depending on whether the Q account balance is more than, or less than the financial institution's statement balance - was either not recorded, or posted twice in Quicken.  So you just need to find a month when the dividend was not posted in Quicken, or a month when there are two postings.

Let me know if you have any followup questions and/or let me know how the above goes.

Frankx

q_lurker

SomebodyInGNV said:

This is about bank reconciliation, but the problem is in an investment account, so I'm posting it in investments.

I've had an IRA for years. It doesn't have a separate cash account. I just tried to reconcile it and it's off by few bucks. The amount of the error matches exactly the amount of a cash dividend in the previous month. That previous month balanced to $0, including the transaction in question.

The problem transaction is a dividend for a money-market fund which reports as cash in Quicken. I record the transaction as dividends, not interest, if that matters.

The problem transaction is in August. I ran a backup, exported all transaction from the account as QIF, as of the beginning of the last month that reconciled, then deleted them. That resulted in the problem transaction (if that's the root cause) being deleted.

After running validate, I reimported the transactions, then tried to reconcile again. The problem remains.

Does anyone have a suggestion (other than an adjustment) to resolve this? Is there a different way to clean up problem transactions?

Thanks

So the prior month (August), you had a transactions for a dividend from a MM fund for $X.  That transaction presented an increase of $X to the sash in your account.  At the end of that month, your cash balance in the account was $0 ("That previous month balanced to $0.")  Where did the cash go?  Likely to buy more shares of the MM fund.

When you then try to reconcile September's cash, something is off by $X.  Is Quicken's cash too high or too low by $X?  (also Frankx's question.)

I think your steps exporting and importing all the account's prior transactions were unnecessary.  

Are you downloading transactions?  There has recently been a spate of issues with some FIs where the Opening Balance of an account got changed by a download.  That is the very first (oldest) transaction in the transaction list.  (I make sure mine are always $0, FWIW).  Did you look at that transaction?

Did the cash balance change correctly for the dividend transaction in August at the time you attempted the September reconciliation?  Did it also change correctly with whatever subsequent transaction used that cash?

If I was correct that your August cash balance was $0 when you completed the August reconciliation, was the August balance still $0 when you started the September reconciliation?

Did you look for placeholders in the account?  Do you have the Preferences / Investments set to Show hidden transactions?  It should be to see all placeholders.

Hope these question help.

SomebodyInGNV

When I say the account balanced to $0, I meant $0 difference. It simply balanced.

The export, validate, reimport process was actually export, delete exported records, validate, reimport. If the dividend transaction that matches exactly the amount of the imbalance, any vestige of that transaction should have been cleaned up. Since the account balanced in the month ending before the deleted transactions and there should have been no vestiges left of any transaction after that month, importing the QIF should have resulted in the imported month balancing. It did not.

Edit: Placeholder transactions are displayed in the account. There are none.

Frankx

HI again @SomebodyInGNV,

I don't think that you are understanding what I tried to explain above, so let me try again.

In short - the steps that you described above - with the exception of the "file validation" step, did nothing to change things.  You exported data > then you deleted that same data from the current file > then you validated the file >then you imported the same data you had exported back into the account.  Your problem is in that data (that you exported and then imported).  If you don't change (or to state it better) if you don't "correct" that data, you will not fix this account.  You have either missed posting one monthly entry for the "cash dividend", or you posted it twice. 

Let me know if there is something about the above that you don't understand.

Frankx