Calculate price improvement and fees from a TOS exported file

tos_caculate_price_improv_n_fees.py

#=======================================================================================================================
# This file reads the exported file from TOS and calculates the price improvement and fees for each day.
# It then outputs a summary of the price improvement and fees for each day.
# To generate the exported file, follow these steps:
# 1. Go to "Monitor" -> "Account Statement" tab in TOS
# 2. Set the date range
# 3. In the "Order History" section, click on the gear icon (Customize...) on the right side and make sure "Price Improvement" column is selected
# 4. Click on the hamburger button ("Show Actions Menu") near the top right of the "Account Statement" tab
# 2. Click on Export to File
# 3. Save the file to your computer
# 4. Update the `tos_exported_file` variable below with the path to the saved file
# 5. Run this script
#=======================================================================================================================



from io import StringIO
import os
import pandas as pd

#=======================================================================================================================

homedir = os.path.expanduser('~')
data_dir = os.path.join(homedir, "temp-data")
# tos_exported_file = os.path.join(data_dir, "2025-09-26-AccountStatement.csv")
tos_exported_file = os.path.join(data_dir, "2025-09-26-AccountStatement-fromJan01.csv")
out_result_file = os.path.join(data_dir, "daily_price_improvement_and_fees.csv")

#=======================================================================================================================
def calculate_price_improvement(filepath):
    # Read the file as raw text
    with open(filepath, "r", encoding="utf-8") as f:
        lines = f.readlines()

    # Find start and end of "Account Trade History" section
    start_idx = None
    end_idx = None
    for i, line in enumerate(lines):
        if "Account Trade History" in line:
            start_idx = i + 1  # data starts right after this line
        elif "Profits and Losses" in line and start_idx is not None:
            end_idx = i
            break

    if start_idx is None or end_idx is None:
        raise ValueError("Could not locate 'Account Trade History' section.")

    # Extract only the trade history lines
    trade_history_lines = lines[start_idx:end_idx]

    # Write this chunk into a temporary DataFrame
    chunk = "".join(trade_history_lines)
    df = pd.read_csv(StringIO(chunk))

    # Clean and convert
    df["Exec Time"] = pd.to_datetime(df["Exec Time"], format="%m/%d/%y %H:%M:%S", errors="coerce")
    df = df.dropna(subset=["Exec Time"])  # keep only valid rows

    # Coerce Price Improvement column
    df["Price Improvement"] = pd.to_numeric(df["Price Improvement"], errors="coerce").fillna(0)

    # Group by date and sum
    daily_pi = df.groupby(df["Exec Time"].dt.date)["Price Improvement"].sum().reset_index()
    daily_pi.columns = ["Date", "Total Price Improvement"]

    return daily_pi

#=======================================================================================================================
def calculate_fee(filepath):
    # Read the file as raw text
    with open(filepath, "r", encoding="utf-8") as f:
        lines = f.readlines()

    # Find start and end of "Account Trade History" section
    start_idx = None
    end_idx = None
    for i, line in enumerate(lines):
        if "Cash Balance" in line:
            start_idx = i + 1  # data starts right after this line
        elif "Futures Statements" in line and start_idx is not None:
            end_idx = i
            break

    if start_idx is None or end_idx is None:
        raise ValueError("Could not locate 'Cash Balance' section.")

    # Extract only the trade history lines
    cash_balance_lines = lines[start_idx:end_idx]

    # Write this chunk into a temporary DataFrame
    chunk = "".join(cash_balance_lines)
    df = pd.read_csv(StringIO(chunk))

    # Clean and convert
    df["DATE"] = pd.to_datetime(df["DATE"], format="%m/%d/%y", errors="coerce")
    df = df.dropna(subset=["DATE"])  # keep only valid rows

    # Coerce Price Improvement column
    df["Misc Fees"] = pd.to_numeric(df["Misc Fees"], errors="coerce").fillna(0)

    # Group by date and sum
    daily_pi = df.groupby(df["DATE"].dt.date)["Misc Fees"].sum().reset_index()
    daily_pi.columns = ["Date", "Total Fees"]

    return daily_pi

#=======================================================================================================================
if __name__ == "__main__":
    daily_price_improvement = calculate_price_improvement(tos_exported_file)
    #print(daily_price_improvement)

    daily_fee = calculate_fee(tos_exported_file)
    # Flip sign of Total Fees
    daily_fee["Total Fees"] = -daily_fee["Total Fees"]
    #print(daily_fee)

    # Merge the two dataframes on Date
    merged_df = pd.merge(daily_price_improvement, daily_fee, on="Date", how="outer").fillna(0)
    # By default, pandas will truncate long DataFrames when printing. To see the full DataFrame, you can adjust the display settings:
    pd.set_option("display.max_rows", None)
    pd.set_option("display.max_columns", None)
    pd.set_option("display.max_colwidth", None)
    pd.set_option("display.width", None)
    print(merged_df)
    # Save to CSV if needed:
    merged_df.to_csv(out_result_file, index=False)