FIFO with python

readme.md

FIFO Tax Calculation Input CSV Format

This script processes a CSV file to calculate the FIFO (First In, First Out) cost basis and gain/loss for buy and sell transactions. The input file must follow the structure below:

CSV Structure

Column Name	Description	Example
Date	The transaction date in YYYY-MM-DD format.	2025-01-01
Asset	The name of the asset being transacted.	Asset A
Type	The transaction type: either Buy or Sell.	Buy or Sell
Quantity	The number of units bought or sold.	10 or -10
Price	The price per unit for the transaction.	100 (price per unit)
Total	The total value of the transaction (Quantity * Price).	1000

Sorting Requirements

• The Date column must be sorted in chronological order + Type (because date resolution and BUY + SELL same day) within each asset group.
• If the input CSV is unsorted, the script will automatically sort it by Asset and Date.

Notes

• For Type, use only Buy for purchases and Sell for sales.
• The Quantity must be positive for both buys and sells.
• Ensure all values are numeric (except Date, Asset, and Type).
• Each Asset should have consistent naming (e.g., "Bitcoin" vs. "BTC").

Example CSV

Date,Asset,Type,Quantity,Price,Total
2025-01-01,Asset A,Buy,10,100,1000
2025-01-02,Asset B,Buy,20,50,1000
2025-01-05,Asset A,Sell,5,120,600
2025-01-06,Asset B,Sell,10,60,600

Trade Republic pyTR csv preprocessing

see prepare_trade_republic_transactions. this is assuming a account_transactions.csv made via the pyTR package. the problem with date resolution is open there as issue.

sample output

🟡 Processing asset 10/12: Solana
🛒 BUY added: {'Shares': 9.607163, 'Rate': -10.408900109220589}
🛒 BUY added: {'Shares': 4.55106, 'Rate': -21.972903016000668}
🛒 BUY added: {'Shares': 4.362183, 'Rate': -22.92430189196556}
🛒 BUY added: {'Shares': 4.457579, 'Rate': -22.433702240610877}
🛒 BUY added: {'Shares': 4.776301, 'Rate': -20.936703947259605}
🛒 BUY added: {'Shares': 5.390051, 'Rate': -18.552700150703583}
🛒 BUY added: {'Shares': 5.217355, 'Rate': -19.166800035650247}
🛒 BUY added: {'Shares': 4.312277, 'Rate': -23.18960493493345}
🛒 BUY added: {'Shares': 4.938149, 'Rate': -20.250502769357507}
🛒 BUY added: {'Shares': 5.127547, 'Rate': -19.50250285370373}
🛒 BUY added: {'Shares': 5.033624, 'Rate': -19.866402417025984}
🔄 SELL of 57.635075 shares at 14.299348096623454 per unit.
FIFO queue before processing: [{'Shares': 9.607163, 'Rate': -10.408900109220589}, {'Shares': 4.55106, 'Rate': -21.972903016000668}, {'Shares': 4.362183, 'Rate': -22.92430189196556}, {'Shares': 4.457579, 'Rate': -22.433702240610877}, {'Shares': 4.776301, 'Rate': -20.936703947259605}, {'Shares': 5.390051, 'Rate': -18.552700150703583}, {'Shares': 5.217355, 'Rate': -19.166800035650247}, {'Shares': 4.312277, 'Rate': -23.18960493493345}, {'Shares': 4.938149, 'Rate': -20.250502769357507}, {'Shares': 5.127547, 'Rate': -19.50250285370373}, {'Shares': 5.033624, 'Rate': -19.866402417025984}]
Total amount before processing: 57.773289
✅ Fully consumed BUY: {'Shares': 9.607163, 'Rate': -10.408900109220589}
✅ Fully consumed BUY: {'Shares': 4.55106, 'Rate': -21.972903016000668}
✅ Fully consumed BUY: {'Shares': 4.362183, 'Rate': -22.92430189196556}
✅ Fully consumed BUY: {'Shares': 4.457579, 'Rate': -22.433702240610877}
✅ Fully consumed BUY: {'Shares': 4.776301, 'Rate': -20.936703947259605}
✅ Fully consumed BUY: {'Shares': 5.390051, 'Rate': -18.552700150703583}
✅ Fully consumed BUY: {'Shares': 5.217355, 'Rate': -19.166800035650247}
✅ Fully consumed BUY: {'Shares': 4.312277, 'Rate': -23.18960493493345}
✅ Fully consumed BUY: {'Shares': 4.938149, 'Rate': -20.250502769357507}
✅ Fully consumed BUY: {'Shares': 5.127547, 'Rate': -19.50250285370373}
🕜 Partially consumed BUY: {'Shares': 0.138214, 'Rate': -19.866402417025984}
Processed SELL. Cost basis: -1097.254185056333, Gain/Loss: -273.11
FIFO queue after processing: [{'Shares': 0.138214, 'Rate': -19.866402417025984}]
Total bought: 57.773289000000005, Total sold: 57.635075
🔄 SELL of 0.138214 shares at 62.8735149840103 per unit.
FIFO queue before processing: [{'Shares': 0.138214, 'Rate': -19.866402417025984}]
Total amount before processing: 0.138214
✅ Fully consumed BUY: {'Shares': 0.138214, 'Rate': -19.866402417025984}
Processed SELL. Cost basis: -2.7458149436668293, Gain/Loss: 5.94
FIFO queue after processing: []
Total bought: 57.773289000000005, Total sold: 57.773289

and a sample result xlsx

Date	Asset	Type	Shares	Sale Price	FIFO Cost Basis	Gain/Loss
2023-06-11 00:00:00	Orchid	SELL	1350.236914	0.05	-100.08	-36.08
2023-06-11 00:00:00	Polygon	SELL	2271.364974	0.59	-2198.28	-850.42
2023-12-09 00:00:00	Polygon	SELL	2.029882	0.36	-1.72	-0.99
2023-06-11 00:00:00	Solana	SELL	57.635075	14.3	-1097.25	-273.11
2023-12-09 00:00:00	Solana	SELL	0.138214	62.87	-2.75	5.94
2023-06-11 00:00:00	The Sandbox	SELL	184.56729	0.34	-100.37	-37.37
2023-06-11 00:00:00	Uniswap	SELL	198.518015	3.76	-1098.29	-350.99
2023-12-09 00:00:00	Uniswap	SELL	0.360853	3.46	-1.71	-0.46

main.py

import dis
from typing import assert_type
from zoneinfo import available_timezones
import pandas as pd
from collections import deque


def prepare_trade_republic_transactions(file_path):
    """
    Prepares and filters transaction data from a custom Trade Republic CSV file via pyTR package.

    Parameters:
        file_path (str): Path to the input CSV file.

    Returns:
        pd.DataFrame: Preprocessed DataFrame ready for FIFO calculations.
    """
    # List of allowed crypto assets
    cryptos = [
        "Algorand",
        "Avalanche",
        "Bitcoin",
        "Chainlink",
        "EOS",
        "Ethereum",
        "Fantom",
        "Orchid",
        "Polygon",
        "Solana",
        "The Sandbox",
        "Uniswap",
    ]

    # Load the CSV file with the specific delimiter ';'
    df = pd.read_csv(file_path, delimiter=";")

    # Rename columns for consistency
    df.rename(
        columns={
            "CSVColumn_Date": "Date",
            "CSVColumn_Type": "Type",
            "CSVColumn_Value": "TotalValue",
            "CSVColumn_Note": "Note",
            "CSVColumn_ISIN": "ISIN",
            "CSVColumn_Shares": "Shares",
            "CSVColumn_Fees": "Fees",
            "CSVColumn_Taxes": "Taxes",
        },
        inplace=True,
    )

    # Convert TotalValue and Shares to numeric (remove commas and convert to float)
    df["TotalValue"] = df["TotalValue"].str.replace(",", "").astype(float)
    df["Shares"] = df["Shares"].str.replace(",", "").astype(float)

    # Convert Date to datetime
    df["Date"] = pd.to_datetime(df["Date"])

    # Filter rows where Note matches allowed cryptos (allow all BUY/SELL transactions)
    df = df[df["Note"].isin(cryptos)]

    # Compute the rate per unit
    df["Rate"] = df["TotalValue"] / df["Shares"]

    # Ensure Shares is positive and TotalValue is correctly signed
    df["Shares"] = df["Shares"].abs()
    df["TotalValue"] = df.apply(
        lambda x: -abs(x["TotalValue"]) if x["Type"] == "BUY" else abs(x["TotalValue"]),
        axis=1,
    )

    # Sort by Date, and prioritize BUY before SELL for same-day transactions
    df.sort_values(by=["Note", "Date", "Type"], inplace=True)

    return df


def calculate_fifo_aggregates(df):
    """
    Perform FIFO tax calculation with enhanced debugging for inventory tracking.

    Parameters:
        df (pd.DataFrame): Preprocessed transaction data containing BUYs and SELLs.

    Returns:
        pd.DataFrame: Results including FIFO cost basis and gain/loss for each SELL transaction.
    """
    results = []  # Store the final results
    assets = df["Note"].unique()  # Unique assets to process
    precision = 6  # Decimal precision for rounding
    tolerance = 1e-6  # Allowable tolerance for floating-point errors

    for asset_index, asset in enumerate(assets, start=1):
        # Filter transactions for the current asset
        asset_data = df[df["Note"] == asset].sort_values(by=["Date", "Type"])
        fifo_queue = deque()  # Queue to track BUY transactions (FIFO)
        total_bought = 0  # Total shares bought
        total_sold = 0  # Total shares sold

        print(f"🟡 Processing asset {asset_index}/{len(assets)}: {asset}")

        for index, row in asset_data.iterrows():
            if row["Type"] == "BUY":
                # Add the BUY transaction to the FIFO queue
                fifo_queue.append(
                    {"Shares": round(row["Shares"], precision), "Rate": row["Rate"]}
                )
                total_bought += round(row["Shares"], precision)
                print(f"🛒 BUY added: {fifo_queue[-1]}")

            elif row["Type"] == "SELL":
                sell_shares = round(row["Shares"], precision)
                fifo_cost_basis = 0.0  # Accumulate cost basis for this SELL

                print(f"🔄 SELL of {sell_shares} shares at {row['Rate']} per unit.")
                print(f"FIFO queue before processing: {list(fifo_queue)}")
                print(
                    f"Total amount before processing: {sum(f.get('Shares') for f in fifo_queue)}"
                )

                # Match the sell shares to the FIFO queue
                while sell_shares > tolerance:  # Allow small differences
                    if not fifo_queue:
                        # Debug remaining inventory at failure point
                        print(f"❌ Remaining FIFO queue: {list(fifo_queue)}")
                        print(
                            f"❌ Total bought: {total_bought}, Total sold: {total_sold}"
                        )
                        raise AssertionError(
                            f"Insufficient inventory for {asset}. "
                            f"Trying to sell {sell_shares} but no inventory left. "
                            f"Total bought: {total_bought}, Total sold: {total_sold}."
                        )

                    # Get the oldest BUY in the queue
                    buy = fifo_queue[0]
                    buy_shares = round(buy["Shares"], precision)

                    if buy_shares <= sell_shares + tolerance:
                        # Fully consume this BUY
                        fifo_cost_basis += buy_shares * buy["Rate"]
                        sell_shares -= buy_shares
                        fifo_queue.popleft()  # Remove the consumed BUY
                        print(f"✅ Fully consumed BUY: {buy}")
                    else:
                        # Partially consume this BUY
                        fifo_cost_basis += sell_shares * buy["Rate"]
                        fifo_queue[0]["Shares"] = round(
                            buy_shares - sell_shares, precision
                        )  # Update the queue
                        sell_shares = 0
                        print(f"⚡ Partially consumed BUY: {fifo_queue[0]}")

                # Calculate the gain/loss (use addition because cost basis is negative)
                sale_proceeds = round(row["Shares"] * row["Rate"], precision)
                gain_loss = round(sale_proceeds + fifo_cost_basis, 2)
                total_sold += round(row["Shares"], precision)  # Update total sold

                print(
                    f"Processed SELL. Cost basis: {fifo_cost_basis}, Gain/Loss: {gain_loss}"
                )
                print(f"FIFO queue after processing: {list(fifo_queue)}")
                print(f"Total bought: {total_bought}, Total sold: {total_sold}")

                # Record the result
                results.append(
                    {
                        "Date": row["Date"],
                        "Asset": asset,
                        "Type": row["Type"],
                        "Shares": round(row["Shares"], precision),
                        "Sale Price": round(row["Rate"], 2),
                        "FIFO Cost Basis": round(fifo_cost_basis, 2),
                        "Gain/Loss": gain_loss,
                    }
                )

        # Validate inventory consistency after processing all transactions for the asset
        remaining_inventory = round(total_bought - total_sold, precision)
        fifo_remaining = round(sum(buy["Shares"] for buy in fifo_queue), precision)
        if abs(remaining_inventory - fifo_remaining) > tolerance:
            print(f"❌ Remaining inventory mismatch for {asset}.")
            print(f"❌ Total bought: {total_bought}, Total sold: {total_sold}")
            print(f"❌ Remaining FIFO queue: {list(fifo_queue)}")
            raise AssertionError(
                f"Mismatch in remaining inventory for {asset}. "
                f"Calculated: {remaining_inventory}, Remaining in FIFO queue: {fifo_remaining}."
            )

    # Convert results to a DataFrame
    return pd.DataFrame(results)


if __name__ == "__main__":
    # Preprocess the data
    input_file = "account_transactions.csv"
    prepped_data = prepare_trade_republic_transactions(input_file)

    # Perform FIFO calculations
    fifo_results = calculate_fifo_aggregates(prepped_data)

    # Save results to an Excel file
    fifo_results.to_excel("fifo_results.xlsx", index=False)
    print("FIFO calculation completed. Results saved to fifo_results.xlsx.")