One of the most mundane yet vital tasks for any trader is keeping accurate records of your trades. Though maintaining a stock trading journal can be tedious and a bit of a drag, it becomes invaluable when used as a learning tool.
Most trading platforms offer a way to extract your trading activity. For example, ThinkorSwim desktop allows you to export trading information for the past 370 days. Unfortunately, the export format can be challenging to work with.
Given my limited budget, I can't afford sophisticated ETL tools like Oracle ODI or Informatica PWC to clean up the data and send it to a cloud database for analysis with Tableau. Instead, I rely on .csv files and Python with PyCharm Community Edition on my budget-friendly laptop, using Dropbox for backup to avoid any data loss.
Later, I plan to use Microsoft Power BI—since it's practically free—to derive the KPIs I need.
Downloading and Cleaning Up the .csv
The first step is to download the .csv export. A .csv file, or 'comma separated values' file, contains rows of data where each column is separated by a comma. It’s not the most readable format, but it’s great for exchanging data without the complications of proprietary formats.
To extract the data from ThinkorSwim, navigate to Monitor -> Account Statement and adjust the period to cover up to 370 days. The option is straightforward, as shown in the screenshot below.
In this first part of the Stock Trading Journal series, I’ll focus specifically on the 'Account Trade History' section of the .csv file. After downloading the file, I manually removed all other sections, unnecessary titles, and empty columns to streamline the data.
In this first part of the Stock Trading Journal series, I'll focus specifically in the 'Account Trade History' part of the .csv file. Once I downloaded the file I manually removed all the other sections, unnecessary titles, empty columns so that the file looks like in the screenshot below. The data is fictitious, so the prices won't match the symbol, but you can get the idea of what I'll use for the Python code.
Creating a New .csv
There are two important sections for the first few articles: 'Order History' and 'Trade History'. The latter is the focus of this first part.
- Order History: Shows all orders captured, whether filled or not. For example, setting a stop order for AAPL below a certain price.
- Trade History: Records actual trades that impact your trading account. For instance, a programmed order to buy AAPL if it goes above $230 would appear in this section if filled.
For my journal, I need both sections, but this article focuses on extracting the Trade History. This section helps me track:
- When trades were opened and closed.
- When positions were pyramided or reduced.
- Where and when stops were placed.
Python Code
The following Python code extracts and processes the Trade History from the .csv file. This is an initial version, with plans to add more features as my Python and Pandas skills improve:
import pandas as pd
# Specify input and output file paths
input_file_path = r'C:\Users\X\X\trades.csv'
output_file_path = r'C:\Users\X\X\new_trades.csv'
# Read the CSV file from disk
df = pd.read_csv(input_file_path)
df['Exec Time'] = pd.to_datetime(df['Exec Time']) # Convert Exec Time to datetime for sorting
# Initialize a dictionary to keep track of stock quantities and first buy dates
stock_quantities = {}
first_buy_date = {}
# Function to determine the Action value
def determine_action(row):
symbol = row['Symbol']
side = row['Side']
qty = row['Qty']
exec_time = row['Exec Time']
if symbol not in stock_quantities:
stock_quantities[symbol] = 0
first_buy_date[symbol] = None
if side == 'BUY':
if stock_quantities[symbol] == 0 and (first_buy_date[symbol] is None or exec_time < first_buy_date[symbol]):
action = 'Open'
first_buy_date[symbol] = exec_time
else:
action = 'Pyramid'
stock_quantities[symbol] += qty
elif side == 'SELL':
if abs(qty) >= stock_quantities[symbol]:
action = 'Close'
stock_quantities[symbol] = 0
else:
action = 'Reduce Position'
stock_quantities[symbol] += qty
return action
# Sort the DataFrame by Exec Time to ensure chronological processing
df = df.sort_values(by='Exec Time')
# Apply the function to create the Action column
df['Action'] = df.apply(determine_action, axis=1)
# Create the 'Action Date' and 'Action Time' columns
df['Action Date'] = df['Exec Time'].dt.strftime('%m/%d/%Y')
df['Action Time'] = df['Exec Time'].dt.strftime('%H:%M') # Extract time part only, in HH:MM format
# Create a new DataFrame with the required columns
new_df = pd.DataFrame()
new_df['Line Type'] = 'Trade' # Changed header to 'Line Type'
new_df['Ticker'] = df['Symbol']
new_df['Strategy'] = 'SW' # Hardcoded 'SW'
new_df['Direction'] = 'L' # Hardcoded 'L'
new_df['Action Date'] = df['Action Date']
new_df['Action Time'] = df['Action Time'] # New 'Action Time' column
new_df['Action'] = df['Action']
new_df['Exec Price'] = df['Price']
new_df['Trans # Stock'] = df['Qty']
# Ensure the 'Line Type' column is correctly populated with the hardcoded value
new_df['Line Type'] = 'Trade'
# Convert 'Action Date' back to datetime for accurate sorting
new_df['Action Date'] = pd.to_datetime(new_df['Action Date'], format='%m/%d/%Y')
# Sort the new DataFrame by 'Action Date', 'Ticker', and 'Action Time'
new_df = new_df.sort_values(by=['Action Date', 'Ticker', 'Action Time'])
# Convert 'Action Date' back to string format for saving
new_df['Action Date'] = new_df['Action Date'].dt.strftime('%m/%d/%Y')
# Save the new DataFrame to a new CSV file
new_df.to_csv(output_file_path, index=False)
print(f'New CSV file has been saved to {output_file_path}')Summary
This article documents my journey toward creating an effective Stock Trading Journal, automating transaction capture to minimize human error. My journal will need essential information, including actual trades and stops, to identify areas for improvement. On a limited budget, I'm using free tools like Python, PyCharm Community Edition, and Dropbox for backups. Later, I'll integrate Power BI to analyze the data.
Without the expensive tools available to larger corporations, fully automating the journal generation on a personal laptop is challenging. However, the goal is to accurately log my trade history and significantly reduce manual work. Since I'm not a day-trader, this approach should be manageable.