Multiple Equity Stocks Trading FIFO Gain Excel Calculator

Description
How to input data
1. If you want to use the Carry Over option, please use the macro-enabled version of the tool and enable the macro while opening the file.
2. You will just have to put your trading details i.e. Transaction Type, Date/Time of Transaction, Stock Name/Symbol, Quantity, and Amount, etc. in columns A:F in the Trade sheet. Prepare transaction data for your trades in a similar format/layout as shown in the Raw Data Tab. Data must be in sorted order by (Stock Name -> Date -> Transaction Type).
3. Copy the raw data and paste it in columns A:F in the Trade Sheet without any formatting using the Paste Special (Values) option. Columns G:AO in the Trade sheet contains formulas, please do not edit or delete them.
4. To carry over the balance quantity in hand as next year's opening balance, then click on the Carry Over Balance Qty button in cell AP1 in the Trade sheet. It will run a macro to carry over the balance quantity in hand to the Carry Over sheet. You can use this balance quantity/data as your opening balance for the next year.


Raw Data sheet
• The Raw data sheet has no formulas. It is just to show the format/layout in which transaction data needs to be prepared for processing.
• Quantity and Prices for all the transactions should be written as positive numbers.
• Market Price is just for your reference. The Trade sheet only uses Quantity and Amount (Columns D and F) for calculations.


Trade sheet
• When you input raw data in the Trade sheet, formulas will automatically calculate FIFO gains and other important data for every transaction in separate rows. You will see Gain/Loss after every sell transaction on FIFO basis in column AA. Besides this, the spreadsheet will also show you other important data e.g. Cumulative Cost of Stock Purchased, Cumulative Proceeds from Sell of Stock, and Number of Stocks Purchased/ Sold /In Hand after every transaction. This sheet is ready to process 3000 transactions (which can be easily extended as per your requirement).
• To help you identify some data entry mistakes, Trade Sheet highlights relevant cells in RED in the following cases:
1. If a cell in Column D (Qty) has a negative value.
2. If a cell in Column F (Amount) has a negative value.
3. If a sell transaction for any stock has a higher value of Sell quantity (column D) than its Quantity in hand (column AB for its previous transaction).
4. If a date value for a buy/sell transaction for any stock is earlier than the date entered in its previous row for the same stock in column B.


Summary sheet
It displays a stock-wise summary of all the trades for up to 100 different stocks (which can be easily extended as per your requirement). It shows the Number of Stocks Purchased/Sold/In Hand, Cost of Stocks Purchased/ Sold/In Hand, Proceeds from the Sale of Stock, FIFO Gain, for every stock in separate rows, and the Total of all trades above the table headings.


Carry Over sheet
• Carry Over sheet is auto-populated by running a macro from the Trade sheet. It shows the stock in hand after all Sell transactions in the Trade sheet. This data can be used as opening stock quantity for the next year.
• It shows Subtotals for each equity stock. If you want to hide these Subtotals, you can do it by going to Data > Subtotal > Remove All option in the Excel toolbar.


Please let me know if you have any question or suggestion to improve this template.