Calculating the Profit and loss for FIFO

Question

Trades of the stock KPITTECH.NS

I am new to Python and data analysis actually. In the above dataframe, I want to use the FIFO method to find the profit/loss made. I want to take each transaction where the stock was bought, calculate its returns till the selling date using FIFO.

I tried iterating through the values, added the quantities sold till the sold quantity >= quantity bought. But from here I am unable to take it forward.

Answer 1

To calculate the profit/loss using the FIFO (First-In, First-Out) method for stock trades, you need to keep track of the purchase history and match each selling transaction with the earliest available buying transaction. Here's an example of how you can implement this in Python:

import pandas as pd

# Assuming you have a DataFrame containing the trades data
trades_df = pd.DataFrame({
    'Date': ['2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01'],
    'Action': ['Buy', 'Buy', 'Sell', 'Sell'],
    'Quantity': [100, 50, 80, 70],
    'Price': [10, 12, 15, 18]
})

# Sort the trades by date in ascending order
trades_df['Date'] = pd.to_datetime(trades_df['Date'])
trades_df = trades_df.sort_values('Date')

# Create a new column for calculating the profit/loss
trades_df['Profit/Loss'] = 0.0

# Initialize variables for tracking purchases
purchased_quantity = 0
purchased_price = 0

# Iterate over each trade
for index, trade in trades_df.iterrows():
    if trade['Action'] == 'Buy':
        purchased_quantity += trade['Quantity']
        purchased_price = trade['Price']
    elif trade['Action'] == 'Sell':
        # Calculate profit/loss for the selling transaction
        sold_quantity = trade['Quantity']
        sold_price = trade['Price']
        remaining_quantity = sold_quantity

        while remaining_quantity > 0:
            if purchased_quantity >= remaining_quantity:
                # The selling quantity is entirely covered by the earliest buying transaction
                trades_df.at[index, 'Profit/Loss'] += (sold_price - purchased_price) * remaining_quantity
                purchased_quantity -= remaining_quantity
                remaining_quantity = 0
            else:
                # The selling quantity exceeds the earliest buying transaction
                trades_df.at[index, 'Profit/Loss'] += (sold_price - purchased_price) * purchased_quantity
                remaining_quantity -= purchased_quantity

                # Update the purchased_quantity and purchased_price variables for the next iteration
                purchased_quantity = 0
                purchased_price = 0

# Display the updated DataFrame with profit/loss information
print(trades_df)

This code assumes you have a DataFrame named trades_df that contains the stock trade data, including columns for the date, action (buy or sell), quantity, and price. The code first sorts the trades by date in ascending order to ensure the FIFO order. Then, it iterates over each trade, keeping track of the purchased quantity and price. When encountering a selling transaction, it matches the earliest buying transaction(s) to calculate the profit/loss and updates the Profit/Loss column accordingly. After running this code, you will have an updated trades_df DataFrame with the profit/loss calculated for each trade.