nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Unable To Reconcile Investment Account

Chris Mead

Hello All,

I'm having a sudden issue with reconciling several of my investment accounts.

:: Backstory: I have about a dozen mutual funds with T.R. Price. As I move funds in and out, I'm careful to transfer funds in and then buy securities. In reverse, I sell and then transfer out. From the transaction perspective, this process has always enabled balancing to zero cash and perfect reconciliation.

:: Recently, T.R. Price has upgraded several of my accounts to Class I. For those that delve into this, the difference between class I and class II is the management fee or load.

:: See the attached image of an example of an actual account. Seen is the initial transfer in on 3/31/21 followed by several investment and a transfer out. On 2/23/22, T.R. Price purchased 161k of class one shares. This was actually the conversion, sell of class II and purchase of class I or perhaps a straight conversion. This transaction was downloaded from T.R. Price.

:: Now, Quicken is reporting an issue with reconciliation and as can be seen, the balance is -161k. From my POV and processes, there should have been a commensurate sale of security, in this case class II for the same amount. When I entered this transaction manually, I.E. a sale of 4,512.189 shares, the account balanced and was happy! Zero dollars cash was reported within Quicken as it should. HOWEVER, the overall value of the account was no DOUBLED resulting in an incorrect reporting of overall assets (left hand side) and an improper rollup reporting too high of a balance. So I deleted that manual transaction and the balance now agrees with the on-line amount. But of course Quicken is constantly barking a reconciliation error.

:: I also attempted to delete the downloaded transaction on 2/23/22. The account went to balance but then the overall account value was doubled again.

:: Any ideas on what is happening and how to correct it?

Thank you!

Find more posts tagged with

Investments

Reconcile

Quicken Mac

Accepted answers

John_in_NC

Howdy, Chris:

Ahh, mutual fund conversions. They often require a little manual intervention to get them to show correctly in Quicken, but this shouldn't be too hard nor convoluted.

The downloaded transactions are often Buy/Sell transx which isn't what you want here:

Backup your datafile (just in case) or make sure TimeMachine has backed up recently.
Make sure the ticker for the new security is setup correctly in Windows:Securities.
For 2/23, enter a "Remove Shares" transaction to remove all shares of the old fund. (Appears to be around 4513.45-check your portfolio for this account)
Also for 2/23, enter an "Add Shares" transaction for 4512.189 of the new fund class. In that transaction, you can use $161,446.11 as the cost so that your basis on the new security is correct.
Get rid of any buys/sell transx of the said securities for this conversion, and your cash balance should be zero (add/remove doesn't affect cash, but the Add does allow you to enter cost basis) and your share balances should be correct. Your PV for this account will be correct as you got rid of the old holding, and entered in shares of the new.

I hope this helps!

All comments

John_in_NC

Backup your datafile (just in case) or make sure TimeMachine has backed up recently.
Make sure the ticker for the new security is setup correctly in Windows:Securities.
For 2/23, enter a "Remove Shares" transaction to remove all shares of the old fund. (Appears to be around 4513.45-check your portfolio for this account)
Also for 2/23, enter an "Add Shares" transaction for 4512.189 of the new fund class. In that transaction, you can use $161,446.11 as the cost so that your basis on the new security is correct.
Get rid of any buys/sell transx of the said securities for this conversion, and your cash balance should be zero (add/remove doesn't affect cash, but the Add does allow you to enter cost basis) and your share balances should be correct. Your PV for this account will be correct as you got rid of the old holding, and entered in shares of the new.

I hope this helps!

Chris Mead

John,

Still in work. I did as you said and I "thought" I had the answer but when I looked back, the balance again doubled. I do see that the ticker is different (one letter). I may have to sell the old. I'll fiddle a bit and write back. I'm sure there is a solution.

1. Entered the Remove Shares transaction per the activity reported on the website. No affect to balance.
2. Entered the Add Shares transaction per the activity reported on the website. Invest Amount = $335.797.16 which matched the original Buy that was downloaded. The Balance was not changed (still -335,797.16) and the Current Account value remained unchanged.
3. Deleted the Buy transaction. Balance went to zero. However, the Current Account value is now nearly doubled. It would be nice to have that amount on my account but it's not real.

Chris Mead

Update:

I figured it out but I'm not particularly enamored with the results. I did one account but will sleep on it before I do the rest. Here's what I did.

Background: The Class I has a different sticker than the Class II.

1. Created a new retirement account in Quicken as a simple IRA. Did not link it.
2. In the Class II account, I deleted the Buy transaction that was downloaded. This set the balance back to zero.
3. In the Class II, I entered a Sell Transaction for the full amount of the security. This created a positive balance for that amount.
4. In the Class II, I entered a Payment/Deposit transaction as a transfer to the new Class I account. I entered as a negative. This took the balance in the Class II to zero and the Class I up to the correct amount.
5. In the Class I, I entered a Buy for the number of shares and dollars according to Price. This took the balance of that account to zero.
6. Linked the new Class I account in Quicken. Delinked the Class II. Tested and correct.

So all set but that's a lot of work. But!, I can't remove the Class II from reports as the portfolio history will be screwed up. I also don't like the fact that the Class I history starts on 2/23 when looked at individually. I'll also have to remap my Excel report so I pick it up. Excel is where I'll connect the two together. But thinking of this, it is a different security.

I tried to buy and sell within the same Quicken account. I should be able to do that. But each time I did that, although the balance was correct at zero, Quicken incorrectly doubled the total amount. Market value - and that's the key I think.

I looked at another account and what I am noticing when looking at the portfolio view is that there are two entries of the mutual funds (the class I and class I) balanced out by one negative cash position. What I should be able to do is to generate cash by selling the Class II which should set the balance to zero - which it does. But when I look at the Portfolio. I still see two funds, the class I and class II which is doubling (about) the market value resulting in the improper rollup.

I don't know if you are following all of this but it seems like Quicken will only work if a brand new account is created and the proper Payment/Deposit transaction is used to transfer the dollars. If you are an expert at these things, perhaps we can Zoom.

Chris

John_in_NC

Hi again, Chris.

I think the problem is easier to solve than adding the new account.

What I want you to check (and where I suspect your problem is) is at the beginning of an offending register. I suspect that you have a placeholder transaction (in light gray). These act as opening balances of sorts for cash balances/positions. I am not a betting man, but I have a feeling you will have a placeholder auto-adjusting (offsetting) your Remove Shares transaction. Thus, you got a near doubling of your holdings as both the new/old holdings are still getting figured.

Look for placeholder transactions for the cash balances/securities. I suspect between any/those, and examining the portfolio, you will figure out what is going on.

Note: Placeholders have a legitimate purpose, so don't blindly delete one. Sometimes you have to, depending on timing of what your brokerage reports things.

Additional note: I am less familiar with Single Mutual Funds where each holding has its own account as none of my investment accounts behave this way. (Yours may not be marked as SMF.) Your mileage may vary. But, take a look for the placeholders first.

Chris Mead

I know those place holders. It's not that (those.) The issue is that Quicken downloads by account number but when it finds two securities, to adds them both. So Quicken isn't receiving the account properly despite "selling shares". Creating a separate account worked because the new account only had one instance of the share record to report against. So I think there is an issue due to the fact that Quicken is downloading by account and not reporting by share (like a stock may do.)