How can I fix mismatch between ESPP security transactions and total number of holdings?

Radler
Radler Member ✭✭
edited March 2023 in Investing (Windows)
I think my issue may have arisen due to multiple transactions over time where I was assuming that Quicken was entering sales of specific lots to match brokerage transactions.
 
I have a Fidelity Investments account where my company's stock is purchased every two weeks through an ESPP. I sell the stock a couple of times a year. I have also made a couple of transfers to Fidelity Charitable, a donor advised fund account. Last December I transferred 875 shares of company stock to a Merrill Edge account.
 
I was verifying that raw month end numbers of company stock matched my brokerage account, but not reviewing each trade to ensure that Quicken was dealing with the same lot that I had traded or transferred. As of yesterday, Fidelity was showing I had 556.9859 shares of company stock. Quicken's holdings matched that total. But when I started reviewing tax lots, I realized that multiple sales over the last two years were registered in Quicken as applying to different tax lots than I had specified with my broker.
 
I went back through the sales and reallocated tax lots to match my broker's records. But in some cases the lot that should have been referenced in Quicken could not be located. I searched all sales transactions and could not find any disposing of the missing lots. Quicken had assigned multiple incorrect lots to the 875 shares transferred to Merrill. I went through and reallocated lots, eventually locating 817.3927 shares of the 875. I could not find 33.2283 shares purchased on 12-11-20 nor 24.3790 shares purchased on 11-12-21. Quicken now shows holdings as 614.5932, the sum of those two lots over the correct number of 556.9859.
 
I have simply added the missing 57.6073 shares to Merrill, so at least that account is correct.
 
But when I went to Quicken's holdings for the Fidelity account to remove the excess 57.6073 shares, the line by line listings of company stock total 1047.9123. After the transfer of 875 shares to Merrill, my Fidelity account holds no company stock before 9-16-22. But Quicken is showing hundreds of shares purchased from 5-29-20 through 10-29-21.
 
There are no placeholder entries in this account. How do I clear out the entries from 2020 through 2021 that should no longer be there? When I tried to manually remove them, I wound up with a negative share balance.

Answers

  • NotACPA
    NotACPA SuperUser ✭✭✭✭✭
    Unless tell the brokerage WHICH lots  to sell to sell AT THE TIME OF THE TRADE they'll almost certainly use FIFO.
    Do your Q records match this?

    Q user since February, 1990. DOS Version 4
    Now running Quicken Windows Subscription, Business & Personal
    Retired "Certified Information Systems Auditor" & Bank Audit VP

  • Radler
    Radler Member ✭✭
    The brokerage did use specified lots for the trades. When I review the brokerage records I can see that The problem is that Quicken assigned lots to trades that don't match what the brokerage did. When I look at the brokerage's record of closed positions I can see that on October 19, 2022, I sold 149 shares that had been purchased in four separate tax lots in 2020: May 29, September 29, October 13, and November 30. But Quicken shows that I sold four different tax lots, all purchased in 2021: October 8, 15, and 29, and November 12. That's neither FIFO nor LIFO. In the Specify Lots window I can allocate the September 29, 2020, lot to the sale, but the other three tax lots don't even appear.
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    When you sell a portion of your holding of a security, the downloaded transaction does not specify which tax lots were sold. If you instructed the broker to sell particular lots, you must use the Specify Lots button in Quicken to select the corresponding lots. If you do not specify the lots, Quicken will normally use FIFO (First In First Out), but there is an option to have it use LIFO instead.

    If you have entered multiple sales incorrectly in Quicken, you must correct Quicken's data, starting with the oldest incorrect transaction. If you try to select lots and an earlier sale was entered incorrectly, the lots you want to sell may no longer be available.
    QWin Premier subscription
  • Radler
    Radler Member ✭✭
    Thanks. But even correcting the oldest transaction won't work in this case, because the shares disappeared before the first transaction. One tax lot that can't be located was acquired on December 29,2020. The first sale in my account after that date was on May 3, 2021. The December 29, 2020, lot of 31.8201 shares was included in the 334 shares sold on May 3, 2021, per the confirmation from Fidelity. Even though there were no other sales between December 29 and May 3, that lot is not available to be specified as part of the May 3 sale. If I look in Holdings for May 3, the December 29 shares are not to be seen. If I set Holdings to the previous day, May 2, there they are. If there was only one sale on May 3 and the shares were not included in it according to Quicken, why would they vanish from the 2nd to the 3rd?
  • Radler
    Radler Member ✭✭
    I was finally able to correct the problem by deleting two reconciled stock sales to which Quicken had allocated the wrong lots. The holdings on the days in question were still off. I re-entered the sales one day before the actual date, when the holdings were showing correctly. Once everything was fixed in the Fidelity account, the approximately 57 shares that had been missing for the transfer to Merrill were available, so I was able to revise the transfer to include all of the proper tax lots.
This discussion has been closed.