How to show LOT info of a past investment sale? -- in register or transaction report

DT1
DT1 Quicken Windows Other Member ✭✭
edited January 4 in Investing (Windows)

After identifying lots of a sale, in the register and transaction reports, Quicken only shows the total cost and share of the sale, but not the individual lots sold. How do I show the individual lots sold in transactions report or in the register? I do know how to do that with the capital gains report. I also know how to show the current number of lots in portfolio view, using expand.

-DT

Comments

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    And the portfolio views can be set to show closed positions. What is deficient about those sources for what you are after - particularly the cap gains report?

  • DT1
    DT1 Quicken Windows Other Member ✭✭

    q_lurker: »And the portfolio views can be set to show closed positions. What is
    deficient about those sources for what you are after - particularly the
    cap gains report?«

    Multiple reasons:

    (1) I am in a similar position to many who have also posted with questions about LOT identification: Like them, I entered years of data from statements without regard to which LOTs were used in sales. Like them, I did not realize that failure to include the correct LOT data would having increasingly more complex cost basis problems as the years of data was entered. So now, like them, I must correct all those sales.

    It is not easy to correct the problems, because Quicken doesn't show you which lots were used in the sale in the register or in the transaction report. If it showed in either, I would have identified the problem long ago and corrected it.

    (2) Version control and the ability to track changes of my Quicken data files.

    Because Quicken data files are stored in binary, it is not possible to compare them directly to see what has changed between two saved versions.

    In order to be able to compare two different Quicken data files to investigate, locate changes and problems, the easiest way I have found is to store every transaction using the transaction report. I make a new one corresponding to every major backup of a Quicken data file, so I know exactly what is in the data file. I can compare visually side by side, or use a program like Unix "diff."

    The transaction report has nearly all the key data that is stored in a Quicken file, such that the report data file could be used to reconstruct all the accounts and their transactions.

    However, the LOT data is not available in the transaction report, and hence it is impossible to see any changes in a sale related to LOTs or to even have any idea what LOTs were used or to be able to correctly calculate cost-basis.

    Another similar method for reviewing and comparing transaction data is to store the data as a QIF file. However, the LOT data is not available in the QIF file.

    -DT




  • Jim_Harman
    Jim_Harman Quicken Windows Subscription SuperUser ✭✭✭✭✭

    You can print or export the Portfolio views, which should provide the needed data. Group by account and select the Show closed lots option.

    QWin Premier subscription
  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    @DT1 Thanks for the expansive explanation.

    The first thing to recognize is that if the user does not specify lot assignments, Quicken defaults to a FIFO selection. But this non-specified FIFO is dynamic. By that I mean, when Quicken needs the information for what lots were sold with a particular sale transaction and does not find a user specification, it looks at currently available lots and treats them on a FIFO basis. If you later come back in and for some reason add in a new lot before that sale, the lots of that sale will be recomputed. In contrast, if you access the lot specification for the sale, choose the FIFO button and thereby specify the sold lots for that sale, those will not be dynamically by other changes to other transactions. (Hope that is not too confusing.)

    But also be aware that there is an Edit / Preferences / Downloaded Transactions setting for "Lot assignment after Investment Download" on an account basis that can come into play.

    If I were trying to correct historical lot assignments:

    • I would sort the Account transaction list by Security; they should then show with a secondary sort by date from oldest to newest.
    • Starting with the last sale of that security, I would edit the Sold transaction, click the Specify Lots button, and (likely) use the reset button to make sure the default FIFO selection was in effect. If you are or become convinced no Specify Lots was ever done, you might be able to skip this step.
    • Having made sure all the sales were default FIFO, I would then work the sales from oldest to newest in the same fashion (edit transaction, specify lots) to set the lot specification as you now desire. While you may be able to use the Minimum Gain button for that correction, your broker may also have considered long vs short term gains which the Quicken Min/Max selection does not consider.

    While that might seem to be a daunting task, it would be the only reliable way I could see of being sure to get where you want to be. The daunting aspect is mostly a matter of how many securities, sales, and accounts are involved in these corrections.

    Hope this helps.

  • DT1
    DT1 Quicken Windows Other Member ✭✭

    Thanks for the further replies from both of you. I seem to place the bulk of the challenges re: cost basis and lot assignment that I and other users have encountered. However, on further reflection, I should have said that one of the biggest challenges is that the original statements from Fidelity do not say which lots were sold.

    I see q_lurker mentioned the download settings re: lot assignment—I was not familiar with that setting. Thank you. I just assumed Quicken had downloaded correctly exactly what Fidelity is doing. I haven't even checked if that is the case or not. I am still working on statements going back numerous years before I got Quicken automatic download.

    That Fidelity does not report on each statement how much stock of each lot is sold has been the hardest part of all of this. (I can't remember if it is shown in the tax documents, which I do not have for years going way back.) A number of respondents re: lots and costs basis say that 90% of brokerages use FIFO (but may use Min., Max., or Average cost basis) I honestly don't know what method Fidelity is using for the statements I have been working on. I had to use algebra to figure out how many shares of which lots were used in order to calculate the number of shares.

    One other problem I have encountered is that the cost basis calculation and lot assignment is additionally complicated by the original transaction cost. I would like to give an example of one where there is only one lot and neither Fidelity's, Quicken's nor my calculations give the same numbers for new cost basis nor for the capital gains. Should I post that question here or make a new post?

    The numerical differences are small, but I feel I need to go through this exercise to know EXACTLY how the transactions and cost bases are calculated so I can go about identifying, accounting for, and removing any discrepancies. I originally ignored the small discrepancies, wrongly believing, they would be easily resolved and would not create any major complication. But, in fact, I have spent hours trying to figure out what caused them. It's a good exercise regardless.

    Thank you both for your help. Again, should I post the challenging one lot example here or make a new post?

    -DT

  • DT1
    DT1 Quicken Windows Other Member ✭✭

    Correction: "I seem to place the bulk of the challenges re: cost basis and lot assignment that I and other users have encountered…" "on Quicken." -DT

  • q_lurker
    q_lurker Quicken Windows Subscription SuperUser ✭✭✭✭✭

    I just assumed Quicken had downloaded correctly exactly what Fidelity is doing. I haven't even checked if that is the case or not.

    Certainly under the Direct Connect and QFX download prototcols (I am not informed on the EWC+ protocol), there was no format for downloading the associated lot being sold.

    That Fidelity does not report on each statement how much stock of each lot is sold has been the hardest part of all of this. (I can't remember if it is shown in the tax documents, which I do not have for years going way back.) 

    In my limited experience, financial institutions have taken a varied approaches in the tax documents. Some might well report on a lot-by-lot basis. Others might report multiple lots of long-term gains used in one sale as one line - with an acquired date something like 'BEFORE mm/dd/yy' and the aggregate shares, basis, and gain of those lots. So tax documents might be more specific, but might not.

    A number of respondents re: lots and costs basis say that 90% of brokerages use FIFO (but may use Min., Max., or Average cost basis) I honestly don't know what method Fidelity is using for the statements I have been working on. I had to use algebra to figure out how many shares of which lots were used in order to calculate the number of shares.

    Vanguard (as a competitor) allows the investor to choose a default method and shows what that default method is for each sale. But that does not dictate or describe what was used for past sales. Also, if these are longer term holdings, you can run into the question of covered and non-covered securities and lots. Prior to 2011 (or so, depending on the type of security), the user was responsible for defining the the basis and associated gains of the sale. After that date, the IRS required the financial institution to report that level of detail on the 1099-B forms.

    One other problem I have encountered is that the cost basis calculation and lot assignment is additionally complicated by the original transaction cost. I would like to give an example of one where there is only one lot and neither Fidelity's, Quicken's nor my calculations give the same numbers for new cost basis nor for the capital gains. Should I post that question here or make a new post?

    As that sounds to be outside the scope of asking about lots and reporting capabilities, I'd suggest a new post. Please be as explicit as possible about the security and scope of the problem. There are a variety of real world happenings that might be involved.

  • Jim_Harman
    Jim_Harman Quicken Windows Subscription SuperUser ✭✭✭✭✭

    Fidelity should be able to provide the lot details for your current holdings, either from their website or if you contact them directly. Unless there are hundreds of lots to deal with, you could then correct your current holdings in Quicken by Removing and Adding shares to make Quicken match Fidelity's records. Then everything should be clean going forward.

    They should also be able to provide your lot-level details for other dates, since for example executors need this information as of the date of death. But I don't see much benefit from reconstructing all the lot-level transaction history, since any taxes due have presumably already been paid using the data from Fidelity's 1099 forms.

    BTW Vanguard's brokerage 1099 forms provide the lot level details of all the sales each year. I don't think the same level of detail was provided for their legacy mutual fund only accounts.

    QWin Premier subscription
  • DT1
    DT1 Quicken Windows Other Member ✭✭

    q_lurker & Jim: Thanks again for your responses. Quite helpful. I will post the other question soon and will leave a reference here when I do.

    -DT

This discussion has been closed.