Hi Vivek,

Yes, you can definitely use formulas to calculate profit/loss on sales of stocks based on FIFO. You don't need VBA for this.

Here's how you can calculate the gain or loss if the Action is Sell:

1. First, you need to calculate the cost of the shares that you are selling. For this, you can use the FIFO method. To do this, you can use the SUMIF function to add up the cost of the shares that were bought first until you reach the number of shares you are selling. Here's an example formula:
   
   =SUMIF($B$2:B2,B2,$F$2:F2)
   
   In this formula, B2 is the cell containing the stock name, and F2 is the cell containing the trade value. You need to copy this formula down for all the rows in your Transactions sheet.
   
2. Once you have calculated the cost of the shares you are selling, you can subtract it from the trade value to get the gain or loss. Here's an example formula:
   
   =IF(C2="Sell",E2-D2,"")
   
   In this formula, C2 is the cell containing the Action (Buy or Sell), E2 is the cell containing the trade value, and D2 is the cell containing the cost of the shares being sold. This formula will only calculate the gain or loss if the Action is Sell.
   
3. Finally, you can use conditional formatting to highlight the cells with a positive gain in green and the cells with a negative gain in red. To do this, select the cells with the gain/loss values, go to Home Conditional Formatting New Rule, select "Format only cells that contain", choose "Less than" for negative gains and "Greater than" for positive gains, and select the appropriate color.