Multiple Equity Stocks Trading FIFO Gain Google Sheet

Very handy Google Sheet Calculator to calculate trading profits for multiple stocks in single sheet using FIFO method.

 

How to Use:

  1. Prepare raw data for your trades in similar format/layout as shown in Raw Data Tab.

  2. Copy/Paste Special (Values) the raw data in columns A:H in Trade Sheet.

Formulas in columns I:AB in Trade sheet will calculate and show important data after every transaction and stock-wise summary of all trades will be displayed in Summary sheet.

You will just have to put your trading details i.e. Transaction Type (buy/sale), Date & Time of Transaction, Stock Ticker, Quantity, Price etc. In columns A:H in Trade sheet. If you trade in non-USD currency please put USD Conversion Rate for that transaction in column H. For USD transaction this value in this column could be either 0 or 1.

Excel formulas will automatically calculate FIFO gains and other important data for each equity and every row. You will see Gain/Loss after every transaction on FIFO basis in column AA. Besides this, spreadsheet will also show you other important data e.g. Cumulative Cost of Stock Purchased, Cumulative Proceeds from Sell of Stock, Number of Stocks Purchased/ Sold /In Hand after every transaction.

Summary sheet displays stock-wise summary of all trades. It shows Number of Stocks Purchased/Sold/In Hand, Cost of Stocks Purchased/ Sold/In Hand, Proceeds from Sale of Stock, FIFO Gain, Applicable Tax (@33%), and Percentage Return for every stock. It also shows Total of all trades in 1st row.

Chart sheet displays 4 different charts for graphical representations of important statistics:
1. Top Gainers: To display top 10 stocks with the best returns in descending order.
2. Top Losers: To display top 10 stocks with the worst returns in ascending order.
3. Ratio of Balance Stock: To display cost of balance stock (in percentage) for the top 10 most weighted stocks in user’s portfolio.
4. Cost of Balance Stock: To display cost of balance stock for the top 10 most weighted stocks in user’s portfolio.

Other things to note:

  1. Columns T:X contain formulas for calculating intermediate results. You may show or hide them as per your choice.

  2. Column AC shows Error message if it finds that Sell quantity for any stock is greater than Buy quantity.

  3. Trade Sheet uses Conditional Formatting to highlight Sell transactions in Orange and Buy Transaction in Green. Similarly Summary highlights Stocks with Loss in Orange and Stocks with Profit in Green.

Multiple Equity Stocks Trading FIFO Gain

Acquire business license from Eloquens marketplace (Use coupon code FIFOLIFO to get discount)

OR

Please send equivalent amount ($19) to the addresses given below and email your transaction detail to fifolifocalc@gmail.com. You will get the tool in your mailbox after payment confirmation.

BTC : 1NYqYEUGY7h1cbKVkW12wmFfcZMnv6DEa7

ETH : 0x3Cbd9480E480f1d755E5b2389b0E57cABE41e982

LTC : LKFcSetKJY2xufP7UUNZdA8EbPwegKAxg3