Investment - 401(k) download - a sold share (for rebalancing) reduces my cash iso increasing my cash

rryan

Periodically I have to go in and Update my Cash Balance from a negative number which can be up to $10k, to zero. I also get a raft of Placeholder transactions which I diligently accept as the lesser work of two unattractive options.

Today I decided to look to find out why. It appears that during a periodic rebalancing (and another reason too intricate to describe here), when shares are sold the cash in my account decreases iso increases.

I read a few help articles and found a user with the reverse problem when he bought shares, his cash increased and the inferred remedy to me is to set the Action to 'remove shares' from my account iso using the defaulted the Sold Action which reduces my cash.

I have several thousand sold transactions so I'm not about to go in and edit every one, so my questions are, is my diagnosis correct? is there a setting I can make to change the imported transaction from being a Sold and reduce cash to either Sold and Increase cash or convert Sold to Remove Shares en masse? & lastly why on earth is this happening? When you sell something you get cash (or a promise of cash), you don't sell something and give cash away.

[Deleted User]

@rryan - you should never need to artificially adjust your cash balance in a 401(k) account.  Your cash balance (positive or negative) should always be offset by investment transactions (purchases from contributions, sales due to withdrawals).    

The fact that you get placeholder transactions is indicative of an issue.  These placeholder transactions are fluid meaning their share value changes whenever you change the security holding share value.  They are simply what their name implies - a placeholder which is the share difference between what your financial institution reports and the shares you have on Quicken.  Each placeholder represents missing investment transactions in Quicken.

Investment realignments or transfers should never raise or use net cash if the investments are all contained in one investment account, which most are.  The transfer out of funds (sale of shares) is offset by transfers into funds (purchase of shares).  Bottom line transfers or realignments should never affect your net cash balance.

The concept of security buys use cash, and security sells raise cash is fundamental and is not or should not be changeable.  There is no setting in Quicken that can change this.  I am not sure why you are getting opposite results for your sales, but it could have something to do with the way you have been adjusting cash, or the unresolved placeholder transactions.

I think your main issue is the placeholder transactions which are not resolved or resolved incorrectly.  These unresolved placeholder transactions are the reason why you also have unreconciled cash sitting in your account.

Anytime you get a placeholder transaction that should tell you that there are investment transactions you need to add to your account.  Once you correctly add these investment transactions, your cash balance should go to zero and your placeholder transactions to go to zero shares and can be deleted.

You don't mention how often you update and reconcile your 401(k) account, but I am assuming you are able to download information from your financial institution. but to stop the bleeding, "Draw a line in the sand" and choose a starting date with a good, reconciled balance and going forward I would recommend reconciling frequently even daily.

After that is done, you can go back and fix placeholder transactions (restore the correct investment transactions) and remove the cash adjustments.

Please let me know if you have any questions or if am not fully understanding your issue.

rryan

Mahalo Damian for your answer - I will read it later in more detail. Logically I agree with what you explain and I have always only downloaded and have tried accepting and not accepting placeholders several times (this has been happening for several years) to see which is the right course of events. I don't know how my specific placeholders get generated, but I suspect it's related to the downloaded transactions not doing what they are meant to do eg. I more often have placeholders to correct more shares reported by Transamerica than are in Quicken.

I still think something is not right though with the continual negative cash balance. Because I can't send a screenshot - here is an example of every Sold and Bought Transaction

Date Action Security Desc Share-Bal, Inv-Amt, Cash-Amt, Cash-Bal
xyz Sold ABC .2 of ABC 2,000, -$5 , -$5, -$5,000 ( line above is eg -$4,999)
xyz Bought DEF .2 of DEF 1,000, $5 , -$5, -$2,999 (line above is eg, -$,3000)

What I am trying to show is that the cash required (Cash-Amt) is always negative for both every sold and every bought transaction - when it should be negative cash for a buy and positive cash for a sell. So my cash balance just keeps drifting lower for every sale. I do reconcile pretty frequently in fact I just looked and I have it to the penny (the share balances equal to 6 decimal places) after accepting placeholders and zeroing out the cash. I have likely used Quicken 5 days a week for about 30 years. I am actually our 401(k) administrator for our company so I am also writing to Transamerica as well to see if they know why.

RR

[Deleted User]

@rryan - you are welcome.  I think the first thing you should try is a validate or super validate, if you haven't already.  The other thing to try is a "file Copy", or a validate and file copy in conjunction with each other.  The process is described in this Quicken Support article - 

https://www.quicken.com/support/advanced-data-file-troubleshooting-correct-problems-quicken-windows

The one caveat of a "File Copy" is that it will disconnect online services for all your accounts.  They will need to be reactivated in the copied file once the process is completed and it is the copied file you want to keep going forward because that is the "cleaned" file.

[Deleted User]

@rryan - one other thing I forgot to mention.  Please do a backup before attempting any troubleshooting steps or making any major changes to your data file, just in case.

rryan

Also - I pulled the .Qfx file and looked inside - here is what I have for a buy and sell transactions

<INVBUY>
<INVTRAN>
<FITID>2022-07-08200202207089224MV989061467124350057775236363833500</FITID>
<DTTRADE>20220708</DTTRADE>
<DTSETTLE>20220708</DTSETTLE>
</INVTRAN>
<SECID>
<UNIQUEID>9890614671243500</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<UNITS>1.752190</UNITS>
<UNITPRICE>12.099142</UNITPRICE>
<TOTAL>21.20</TOTAL>
<SUBACCTSEC>CASH</SUBACCTSEC>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVBUY>
</BUYOTHER>
<SELLOTHER>
<INVSELL>
<INVTRAN>
<FITID>2022-07-18162020220718926FKJ3408983467124350025760436363833500</FITID>
<DTTRADE>20220718</DTTRADE>
<DTSETTLE>20220718</DTSETTLE>
</INVTRAN>
<SECID>
<UNIQUEID>34089834671243500</UNIQUEID>
<UNIQUEIDTYPE>OTHER</UNIQUEIDTYPE>
</SECID>
<UNITS>-0.063143</UNITS>
<UNITPRICE>11.402695</UNITPRICE>
<TOTAL>-0.72</TOTAL>
<SUBACCTSEC>CASH</SUBACCTSEC>
<SUBACCTFUND>CASH</SUBACCTFUND>
</INVSELL>

Is the -0.72 in the Sell transaction the issue because it shows as negative cash when it should end up on the register as positive cash.

Should there also be other Transaction 'actions' to address the share imbalance which leads to placeholders?

[Deleted User]

@rryan - I see what you are saying now.  The cash portion of Buys and Sells have the wrong sign.  The file should also contain share position information for share reconciliation.  

I am at a loss to explain why or how this happened. Or why other participants in your plan haven't complained.

I think the QFX file needs to be reviewed for script corrections or updates.  I would start by sending an error report via Help-> "Report a problem".  I would then follow up by contacting Quicken Customer Support.

https://www.quicken.com/support/quicken-support-options

Also, please let them know that you sent an error report.  

And, please report back what they find and the solution to this issue.

q_lurker

@rryan
My OFX log SELL (and SELLMF) transactions show a negative share amount but a positive TOTAL amount, so I suspect that is the source of your problems.  Identifying the brokerage might help.

If you do edit the generated Sold Shares transaction, does it show the negative dollar amount?  Is there some other entry that compensates, like a negative price?  The normal Sold Shares transaction should be positive for Shares, Price, and Total.  It could possibly have a positive or negative Commission value, I suppose (positive the common one).
  
Does editing the transaction (simply opening it and saving it), fix the problem?  (Yes I took note of your "I have several thousand sold transactions so I'm not about to go in and edit every one," comment.  Just want to conform if that is the fix.)

Conversely, my OFX file BUYs have a negative TOTAL value.

So it does appear your brokerage is sending the data badly.   

[Deleted User]

@q_lurker - the FI is Transamerica, per one of @rryan 's comments above.

@rryan - that is the other reason why this issue is perplexing.  Transamerica is a large 401(k) plan provider with many plans.  I am hoping this is an isolated issue, otherwise, I would think they have bigger issues.  When you contact Quicken Customer Support, ask them what the proper protocol is to get Transamerica involved in the correction process.  They might tell you that you would need to contact Transamerica directly to get them involved.  Bottom line - Transamerica and Quicken need to come together and fix this issue. 

rryan

Thanks for the confirmation - I can't believe I've waited several years to finally look under the hood and figure out the problem.

It is amazing to me that Transamerica has not had anyone point this out before. I will clip and copy this to my Transamerica rep, as I think I mentioned I am our 401(k) Sponsor person, so they have to work with me.

I see quicken support is chat and call only, so hopefully I'll some have time soon to get this reported to them.