Thinkorswim Realized Gains

The purpose of this post is to measure the realized gain/loss from each Thinkorswim options trade¶

Measure the realized gain/loss and determine methods to improve the upside and manage the downside¶

Log into your TD Ameritrade web account >> My Account >> Cost Basis >> Realized Gain/Loss then export data and save Excel file into a working directory¶

The following script will import the realized gains file and visualize the outcome¶

In [1]:

# Packages required to import, filter, and analyze data
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import config

# Import Thinkorswim baseline data
# path and filenames are passed from the config file
# path = r'C:/.../Documents/Python/Stock Options/'
# filname = r'RC_*******_20190101_20190817.xlsx'
measurement_data = pd.read_excel(config.path + config.filename)

In [2]:

#Look at the data file top five rows
measurement_data.head()

Out[2]:

	Security	Trans type	Qty	Open date	Adj cost	Close date	Adj proceeds	Gain adj	Adj gain($)	Adj gain(%)	Term
0	BMY Apr 18 2019 46.0 Put	Sell to Close.FIFO	100.0	2019-02-26	83.27	2019-03-04	39.73	NaN	-43.54	-52.29	Short-term
1	BMY Apr 18 2019 47.0 Put	Buy to Close.FIFO	100.0	2019-02-26	57.27	2019-03-04	106.73	NaN	49.46	86.36	Short-term
2	BMY Mar 15 2019 55.0 Call	Buy to Close.FIFO	100.0	2019-02-05	72.27	2019-02-20	116.73	NaN	44.46	61.52	Short-term
3	BMY Mar 15 2019 57.5 Call	Sell to Close.FIFO	100.0	2019-02-05	75.27	2019-02-20	47.73	NaN	-27.54	-36.59	Short-term
4	GENERAL ELECTRIC CO (GE)	Assign Sell.FIFO	73.0	2012-01-05	1354.15	2019-01-18	602.78	NaN	-751.37	-55.49	Long-term

The data contains information about the transactions¶

Convert columns into numbers and calculate the adjusted gains and days open¶

In [3]:

#Clean and organize data set

#Convert strings to_numeric
measurement_data['Adj cost'] = pd.to_numeric(measurement_data['Adj cost'], errors='coerce')
measurement_data['Adj proceeds'] = pd.to_numeric(measurement_data['Adj proceeds'], errors='coerce')

#Remove NaN (Not a Number) from the dataframe
measurement_data['Adj proceeds'] = measurement_data['Adj proceeds'].fillna(0)

#Calculate adjusted gain
measurement_data['Adj gain'] = measurement_data['Adj proceeds'] - measurement_data['Adj cost']

#Convert string to datetime
measurement_data['Close date'] = pd.to_datetime(measurement_data['Close date'])
measurement_data['Open date'] = pd.to_datetime(measurement_data['Open date'])

#Calculate days open
measurement_data['Days open'] = measurement_data['Close date'] - measurement_data['Open date']

#Remove unneeded rows and columns
measurement_data = measurement_data[:-1]
measurement_data = measurement_data.drop(columns=['Gain adj', 'Adj gain($)','Adj gain(%)'])

Parse the stock symbol, strike price, and expiration date from the option chain¶

In [4]:

#Parse the security field
measurement_data['Symbol'] = measurement_data['Security'].str.split(' ').str[0]
measurement_data['Month'] = measurement_data['Security'].str.split(' ').str[1]
measurement_data['Day'] = measurement_data['Security'].str.split(' ').str[2]
measurement_data['Year'] = measurement_data['Security'].str.split(' ').str[3]
measurement_data['Strike'] = measurement_data['Security'].str.split(' ').str[4]
measurement_data['Strike'] = pd.to_numeric(measurement_data['Strike'], errors='coerce')
measurement_data['Option Type'] = measurement_data['Security'].str.split(' ').str[5]

Group data by close date and stock symbol¶

Plot a profit and loss bar chart¶

In [5]:

#Group by symbol and date to understand the profit and loss
trade_group = pd.DataFrame()
trade_group['Adj gain'] = measurement_data.groupby(['Close date','Symbol'])['Adj gain'].agg('sum')

In [6]:

#Plot data
import seaborn as sns
sns.set_style("whitegrid")

#Color palette
blue, = sns.color_palette("muted", 1)

#Color based on profit or loss
my_color = np.where(trade_group['Adj gain']>=0, 'b', 'r')

#Set grid size
fig = plt.figure(figsize=(8,6))
ax = fig.add_subplot(111) 

#Plot and label chart
trade_group['Adj gain'].plot(kind='bar',color=my_color);
ax.set_title('Profit/Loss Plot')
ax.set_xlabel('Close Date & Symbol')
ax.set_ylabel('Dollars')
plt.legend()
plt.show()

Percent of profitable trades¶

In [7]:

#Calculate the percentage of profitable trades
defects_opp = trade_group[trade_group['Adj gain'] <= 0].count()
pass_opp = trade_group[trade_group['Adj gain'] > 0].count()
pass_rate = pass_opp/(pass_opp + defects_opp)
print ('%.2f%%' % (pass_rate[0]*100))

59.09%

Average profitable trade¶

In [8]:

#Calculate the average of a winning trade
win_average = trade_group[trade_group['Adj gain'] > 0].mean()
print ('${:,.2f}'.format(win_average[0]))

$25.03

Average losing trade¶

In [9]:

#Calculate the average of a losing trade
loss_average = trade_group[trade_group['Adj gain'] <= 0].mean()
print ('${:,.2f}'.format(loss_average[0]))

$-175.62