Create A Thinkorswim Trade Summary With Python

Sharing is caring!

Last Updated on July 14, 2022 by Jay

It’s tax season so I made a Python script to help create a trade summary for my thinkorswim (ToS) trading account. If you also use the thinkorswim platform for trading stocks and options, this tutorial might help you save time to create a trading summary.

Those who trade regularly, you know the pain when filing your taxes – going through trade by trade to figure out a Profit & Loss (P&L) for the year. Of course, we can use an accountant to file taxes; however, they charge by the number of trades, so it can be expensive – one year I got charged $500 just for filing taxes on the trades I made.

Get Thinkorswim Trade Summary Into Python

Download Trade Summary from ToS

First of all, we need to download the trade history from the thinkorswim platform. Under Monitor -> Account Statement -> set date range to be the entire 2021 year. Select “Export to file” to download as a csv file.

The CSV file contains lots of information, we only need data from the “Account Trade History” section, which contains trades that have been executed. The CSV file looks like the below:

For your convenience, I’ve uploaded a sample trade history to Github and you can read it directly using pandas – see the below code.

Note this is sample data created using my own trade history mixed with randomized data points. Please do not consider this financial advice!

Load Data & Cleanup

We’ll load the data into Python and perform some cleanup. As shown below, a combo order is grouped together, and only the first line of the order contains information on the Execution Time. We’ll want to fill the missing rows with the same date, and we can drop the time.

import pandas as pd
import numpy as np
import datetime as dt


df = pd.read_excel('https://github.com/pythoninoffice/pythonio_examples/raw/main/thinkorswim_summary/2021_input.xlsx', parse_dates = ['Exec Time', 'Exp'])
df['Exec Time'].fillna(method='ffill',inplace = True)
 #fill missing dates
df['Exec Time'] = df['Exec Time'].dt.date #keep date, and drop time
df['Pos Effect'].replace(['TO CLOSE', 'TO OPEN'],['CLOSE','OPEN'],inplace=True) #rename values
df.rename(columns={'Exec Time': 'Trade Date'}, inplace=True) #rename column

Trade Types And Prices

In the thinkorswim trade history, we can use the Qty column for stocks as is. E.g. 100 means 100 shares. For options, we need to multiple Qty by 100, since 1 option controls 100 shares.

There are basically two types of trades: Open order and Close order. We need both trades to calculate a P&L. In tax filing language when we open a trade (e.g. buy an asset), it’s a “Cost“, and when we close a trade (e.g. sell), it’s “Proceeds“. The net of Cost and Proceeds is Profit & Loss.

Let’s calculate both Cost and Proceeds using the Quantity (number of shares) and Price.

df.loc[(df['Type'] == 'STOCK') | (df['Type'] == 'ETF'),'Quantity'] = df['Qty']
df.loc[df['Type'].isin(['CALL', 'PUT']),'Quantity'] = df['Qty'] * 100

df.loc[df['Pos Effect'] == 'OPEN','Cost'] = -df['Quantity'] * df['Price']
df.loc[df['Pos Effect'] == 'CLOSE','Proceeds'] = -df['Quantity'] * df['Price']

Identify Unique Options and Stocks

We can identify a unique option with the following parameters:

• Stock symbol
• Strike price
• Call/Put
• Expiration Date

For example, AMD_100_CALL_2021-01-21 means AMD 100 strike call that expires on 2021-01-21.

Let’s write a function called optionid to create the unique option IDs. We also handle Stocks and ETF by just using the stock symbol followed by either “_STOCK” or “_ETF” since these instruments don’t have a strike price or expiration date.

Then we can use df.apply() to apply this data transformation to each row of the dataframe.

def optionid():
    if (df['Type'] == 'STOCK'):
        return df['Symbol'] + '_STOCK'
    elif (df['Type'] == 'ETF'):
        return df['Symbol'] + '_ETF'
    else:
        return df['Symbol'] + '_' + str(df['Strike']) + '_' + df['Type'] + '_' + df['Exp'].strftime('%Y-%m-%d')

df['Option ID'] = df.apply(optionid,axis = 1)


#re-organize the columns
df = df[['Symbol','Option ID', 'Pos Effect','Trade Date', 'Cost','Type', 'Proceeds','Qty','Price']]

#sort by stock symbole and date
df.sort_values(by = ['Symbol','Trade Date'],inplace = True)

Up to this point, we have a nicely formatted trade history summary, and we can calculate the P&L easily from it.

Create A Trade Summary By Instruments

Let’s take it a step further by grouping the same options, i.e. the open and closing trades, together. This way makes it easier to see how much profit we made from each trade.

Although this is not a perfect summary and still requires some manual work (e.g. if I opened a trade outside of the history time window). I found this thinkorswim trade summary still helpful when filing taxes and thanks to Python for making the process easy!

summary = df.groupby(['Symbol','Option ID','Pos Effect', 'Trade Date']).agg({'Cost':'sum','Proceeds':'sum','Qty':'sum','Price':'mean'})

summary['Cost'].sum() + summary['Proceeds'].sum()

Additional Resources

How to Use PandaHow To Use Pandas Groupby To Summarize Data

How to Filter A Pandas Dataframe By A List of Values