Fixing an investment lot

Scotty Van

I have an account where the total of an individual holdings lot quantities do not add up to the total quantity for that holding in the account. Account Holding Qty = 57.373 the lot qty = 42.735. I have isolated the date when this occurs. it happened on Aug 13, 2020. I do not see any transactions for this holding in this account.

How do I adjust the Lot qty on this date to get it back up to the correct qty of 57.373? The register is correct from when the lot was opened on 11/30/2016 until 8/13/2020.

As a side note the rebuild investment lots routine does not complete. It says 'the resulting value is too big. Please Enter it as more than one transaction".

Jim_Harman

First I would go to Edit > Preferences > Investment transactions and make sure the Show Hidden Transactions box is checked. 

Then go back to the account and look for s Placeholder in the problem date. Resolving the Placeholder may fix your problem.

Scotty Van

Thanks for your response Show hidden transactions is already checked. There is no placeholder transaction that I can see. I wish there was a way to select an investment lot in an account and see all the transactions for some date range.

Jim_Harman

Perhaps there is a transaction in another account that due to some data corruption is affecting this holding.

You might run an investing transaction report for all accounts and all securities near the problem date and see if there are other transactions on the same date. 

If so, maybe deleting and re-entering those transactions will fix it.

As always, back up your data file before changing historical data.

q_lurker

I am concerned that your "Rebuild Lots" on the validation attempt failed.  That might indicate irreparable corruption.  If this discrepancy just recently appeared, you might be best to restore from a backup. 

Is this a security held only in this one account?
This is one lot of correctly 42.735 shares.  How many different lots are there for this security in this account? 
Is this a security with a lot of trading in and out?   
I am trying to get a sense of how complex (or simple) your circumstances are with this security.

Pacoinconn

I have a similar problem and would appreciate some advice.

Background:

On March 23, 2020 I transferred 36,492 shares of a security in 16 lots. The original account (Call it "Account A") shows correctly that 36,492 shares were transferred to another existing account (call it "Account B") and remaining share balance in Account A at 0 shares.

(The Quicken entry in Account A is one entry transferring all 16 lots to Account B. Quicken automatically added 16 separate transactions in account B showing the transfer fro Account A.)

Issue:

In my "current" Quicken file in Account B, the lot in question shows 2,963 shares added from Account A but only 1,503 as the share balance in Account B. (The lot in question was the first of the 16 transfers automatically created by Quicken based on the transfer transaction in Account All other lots are accurate.

Further, in my current Quicken file if I look at the balance in Account B prior to March 23, 2020, it shows a negative share balance of 1,460...precisely the difference between the correct share balance and the one showing in Quicken.

I'm very puzzled by this recent discovery because I am very diligent about reconciliation and checking account balances and can't explain why/how I just noticed this now...but leave that aside.

Resolution Attempts:

I've gone back and found a backup from February 3, 2022 where the transfer shows the correct balance in Account B.

Because I had issues in another account in May, the current file has been super-validated, including rebuilding investment lots.

I have tried showing the "Hidden transactions", however there is no placeholder transactions in either Account A nor Account B.

Questions:

Given that rebuilding investment lots hasn't fixed the error and there is no placeholder transaction, what is the best way to correct this issue and prevent a recurrence?

I don't want to go back to February 3, 2022 and update all banking, credit card, and other investment account transactions to now.

Is it possible to use the corrected, presumably uncorrupt transactions in Accounts A and B? If so, what would be the best approach given that Accounts A and B were created in July 2003 and contains hundreds of investment transactions plus transfers to our checking accounts?

Or am I better off deleting all transactions associated with the corrupt lot in Account B, including the transfers and manually entering entries from March 23, 2020?

Thx in advance for support and input.

Jim_Harman

Just to be clear, let me see if i can restate your situation.

-- You used "Shares transferred between accounts" to move a holding from account A to B on 3/23/20.

-- Account A shows one Removed transaction for all 36,492 shares and account B shows 16 Added transactions, corresponding to the tax lots that were transferred.

-- One of the Added transactions in Account B correctly shows 2,963 shares, but as of today there are only 1,503 shares in that lot.

-- Prior to the transfer date, Account B shows -1460 shares in that tax lot, which accounts for the difference.

If I have that right, I would click on Holdings in account B and set the As of date earlier in time until you first see the -1460 shares. When you have found that date, look in the transactions for Account B and see if something there explains it. There may be a mangled transaction of some sort that you can edit or delete.

Be sure to back up your data file before changing historical data, in case something goes wrong. 

Please let us know what you find.

Pacoinconn

@Jim_Harman ,

Thx for the quick reply. You've summarized my situation accurately.

I followed your advice and found a -1,460 share balance on 8/29/2014. That balance was created when I sold 5,122 shares (in two lots, none of which are the lot in question). I deleted that transaction, however when I tried to create a replacement transaction, I was unable to do so because the share balance showed only 3,662 shares (exactly 1,460 short of the actual balance at the time).

Interestingly, I found a transaction on 2/14/2014 for selling 1,460 shares for 7/1/2010 lot (not the lot in question either). When I looked at the Holdings on 2/13, the 7/1/2010 lot is not listed. However it is listed on 2/13/204 and previous dates however with a share balance of 936. This error occurs despite the fact that the 7/1/2010 lot was transferred from Acccount A to Account B correction on 4/24/2012.

Given this history it looks like many of the lots may be messed up...and I may be better off either:

1. recreating the transactions from day 1? If so, what's the best approach to do so? Do I start two new accounts and enter transactions manually and delete the "parallel" transactions in Accounts A and B? Is there a way for me to build a spreadsheet in Excel and import it? If so, is there a link or guide in Quicken?

or

2. adding the 1,460 shares as a "fake transaction" and moving on. If I take this approach, I'm afraid that there's no way to make sure that errors won't crop again. Is that correct?

Appreciate the assistance.

Jim_Harman

Another alternative would be to back up your data then do a File > Validate and Repair with the Rebuild investing lots option selected. There is no guarantee that this will fix it, but it is worth a try. You would have to review the lots over time to make sure everything is correct after the repair.

Since the problem appears to date to before you transferred the shares between the accounts, you might have to delete and re-do the transfer

Pacoinconn

@Jim_Harman

I had already done a validate and repair with the rebuild investing lots option selected to address a corruption issue in another investment account.

When you say that I might "have to delete and re-do the transfer", are you talking about the original transfers? If yes, won't that create issues because transactions that occurred later in time for the same lot will no longer have shares? Or will that be corrected once I "re-do" the transfer?