journal for option trading

When I get to be a CEO?

Even though I trade part time, I want to “treat my trading as a business“. My trading business is simpler than most businesses as no physical location, no employees, no inventory only capital and the sky is the limit to growth. Because trading is easy to start, there is probability of getting sloppy in managing trading business and eventually to an early closing (aka wiping out entire account).

When I treat my trading as a business, then I got to be a CEO. What does an (or any) CEO want? Better results! The CEO of the company does not look at only sales figures but lots of metrics are crunched to know the health of the business. What is the most important objective of any CEO? To increase revenues and thus profits. I have my trading mission, goals and objective in my trade plan. I want to track my profits and losses and grow my business. I use my trade journal to analyze, measure and monitor my trading performance. I hope that I will be able to make better trading decisions by taking into account statistical evidence of what works.

The journal will become a perfect trading history logbook for reviewing strategies and past mistakes and successes. I can journal my trades and my data are backed up and secured. This ‘journalized data’ can be analyzed to identify patterns in my trading and report to me. I can use these patterns to my advantage and thus help me determine what is working for me, and what to tweak. I would be interested to know what “set of trading mistakes” is causing the biggest trading losses?

And finally, it is possible to boost my trading performance, to work on patterns that are working against my performance, by taking corrective action and eliminating or minimizing trading mistakes are making you lose money. I can you can focus on making sure to not to repeat trading mistakes.

The tedious work of entering my trade records to integrate with my portfolio is prompting me to write this post. Additionally, I want to brainstorm ideas to build a portfolio tracker.

Options trading terms: The data columns described further down need some options trading terms defined and understood in options dashboard spreadsheet application. Let us familiarize with most common terms s below:

• Trader ID: create and select different trader IDs to track different trading accounts.
• Date and time data: date format is DD-MM-YYYY to force date formatting at application level, I have implemented a function at open the google sheet.
  1. Date Initial Open: is the actual date that the option contract was opened or the new position was established.
  2. Transaction date: when the position was (is) opened. transaction date (or log date) which can be different from the initial open date when the transaction is an adjustment to the option contract (e.g., rolling out in time or adjusting the strikes)
  3. Expiration Date: is the expiration date meaning the date that the contract is scheduled to end.
  4. Initial days till Expiration (Init DTE), the number of days left on the option contract, when the position was opened (on the transaction date).
  5. Days to expiration (DTE): the number of days left on the option contract with respect to current date,
  6. Close Date: The day you either exited your option play or it expired.
• Stock symbol (or Ticker): The ticker symbol for the stock underlying the option contract, used to identify the stock on the stock exchanges and relevant websites such as Yahoo finance.
• Underlying Price: is the underlying stock price at the moment the position is opened on the transaction date.
• Transaction Type Open/close: B/S Buy or sell the option? The type of Transaction to open a position is either Sell to Open (STO) or Buy to Open (BTO).
• Strategy: dropdown choice of the selected the strategy of the trade, such as put, call, strangle, iron condor, iron fly, call spread, put spread, jade lizard and big lizard.
• Strategy Description: I use this field to make a brief description of the trade is that I can quickly identify the trade.
• Strike price: is the set price of the option contract at which the underlying stock can be bought or sold when it is exercised.
• Implied Volatility Rank Open: IVR (Implied Volatility Rank) of the stock at the moment the position is opened on the transaction date.
• Probability of Profit (POP): Probability of Profit and it gives expressed in percentage the expected chance that the trade will have a positive result, will be a winner.
• Quantity (Qty): stand for the quantity of contracts for the newly opened position (default value is one).
• Premium Open: Premium received (credit when selling to open), is the money collected for selling the global position. Since contracts are transacted in increments of 100 shares, when I enter the option premium of $1.23 in this field, I actually have received $123 in premium.
• Total Fee Commissions: Total Fee and Commissions amount that has to be paid to brokerage for executing the transaction. From the settings section, I should be able to control commission rates and Contract Time as at either Exit Time or Entry Time. I could define different rates per broker, as fixed rate, however with IBKR I have settings section of the trade journal where I enter different commissions for each row manually.
• Cash-Secured Put (CSP Cash Reserve): the amount of money needed to be on hand in order to sell the option. For a non-margin account, the total amount that needs to be in the account before your broker allows the trade to go through will be 100 x the strike price. This is why this is called a cash-secured put.
• Put Margin Reserve: the amount of money needed in the account for a naked put sold in margin account. Most brokers calculate this as (25% of the underlying stock’s market value + the option ask price – any out-of-the money amount) x 100 (per contract) x the number of contracts.
• Call Cost Basis/Share: Calculating the rate of return (ROR) is tricky. When selling covered calls, the annualized rate of return is based on the strike price. It does not take into account any gains or losses from selling the underlying stock. I did this to keep this spreadsheet dedicated to profit/loss only related to options. Stock transactions are performed in a similar way. This can be changed by editing the formula in the relevant column, if required.
• Buying Power Requirement (BPR-Margin Requirement): Buying Power requirement – Margin Requirement, is the actual cost of the investment, also a determining factor for the return on investment.
• BPR % of Net Liquidity: Automatic calculated control field that checks if the % of the position is not higher than the set percentage in the portfolio goals.
• Implied Volatility Rank (IVR) @ Open: Implied Volatility Rank of the option at opening – interesting parameter to follow up the evolution of the volatility of the position.
• SPY Beta Weighted Delta @ Open: The delta (SPY Beta weighted) of the option at opening – interesting parameter to follow up the evolution of the total portfolio delta.
• Call: or put: What type of option?
• PRICE DATA:
  1. Stock price DOC: Underlying stock price at the time you opened the contract.
  2. Current stock price: The stock price of the underlier today.
  3. Break-even price: The price the underlier has to reach for the option play to break even.
  4. Total Credit or Debit: calculated field of the opening transaction
  5. Strike price: The strike price of the option.
  6. Premium: Option price.
  7. Fees: All transaction costs for each trade including commissions.
  8. Exit Price: The price you exited your option play.
• P&L DATA with account data:
  1. Account: The account the option trade is executed in if you have multiple accounts like one for options, one for stocks, IRA, or Roth IRA and trade option plays in each
  2. Contracts (lot): Quantity of contracts held or sold in the option play.
  3. Profit/Loss: How much you made or lost on a trade after it is over. Dark green for profit and red for a loss.
  4. Annualized ROR for Options: Calculates the annualized rate of return on the option play.
  5. Margin Annualized ROR: Calculates the annualized rate of return based on the smaller margin cash reserve.
• Status: Whether a current option play is open, closed, or exercised.

My options trading journal

High level view of my option trading dashboard:

• Option Tools like Greek calculation, expected move (EM), Std deviation calculation (local) and trade simulations
• Dashboards and Reports with Charts
• Automate Jobs with GAS (Google App script)
• Solver Add-in & Statistical Analysis
• Data Entry and Lists
• Portfolio management

Raw Data Input: This is where I can import raw data from my broker IBKR. Usually, I enter trade data manually for each trade.

Auto calculation: This is where raw data is calculated, filtered, and displayed per transaction. This section is being populated from Raw Data Input.

For my options trading dashbord spreadsheet app, the realized profit or loss is calculated when the option trade is actually status changes to ‘CLOSE’ i.e., completed. For example, when selling a premium, the premium collected as profit is only realized (calculated) after the option position is either goes through assignment, expiration or closed through buy to close (BTC). This keeps things simple. If I roll an option position, first finalize(close) the existing position with buy-to-close (BTC) and then open a new position with tag ‘ROLL’. The entire trade is logged in a single row.

Daily, Weekly, and Monthly Dashboard:
Here the Option Dashboard displays the weekly/monthly performance of my trade transactions.

Metadata Analytics:
This section is populated from the data you input on the right side of the Trade Log. (You can edit these metadata drop-downs in the Settings section)

The Summary sheet automatically calculates the total profit/loss that is both realized (and unrealized trades are not reflected in p/l as unrealized), the cash reserves for the open trades, and total fees. In future updates I am planning to connect my Forex trades and dividend tracker to a table displaying daily, weekly monthly income.

Technology: Google spreadsheet
Analysis type:

• Quantitative analysis
• Qualitative analysis
• Statistical analysis
• Descriptive analysis

Expertise

• Math
• Statistics

Application model: Server-side Model View Controller (MVC) pattern. Many of its components and concepts will seem familiar to those of us with experience in other web frameworks

Business Rules of option dashboard:

Rule 1. bought and sold quantity/ amounts should belong be the same for a ticker.

Rule 2. The contract’s exit date of a trade is the date when a position gets closed, regardless of when this position was initialized.

• If I leave it exit date empty, the option dashboard will treat these transactions ‘open’ trades due to open position.

Rule 3. When calculating ‘Put Cash Reserve’ and ‘Put Margin Reserve’ check the status:

• if the option status is still ‘OPEN’, then cash reserve is ‘locked’ for trading.
• If the option position status is ‘CLOSED’, meaning no need for the reserve, that capital is available for trading. The accounts section should indicate a lower reserve.

You can still update the dashboards. Just know that this trade will be excluded while calculating profits and other metrics. Note that you can continue buying and selling the same ticket/symbol. Position will get closed once balance reaches zero.

Rule 4: Covered call rules. Whether CC is expired worthless, assigned or bought back prior to expiration. spreadsheet should calculate it automatically. For the Current Profit following formula can be used (with required data fields):

S = the stock price,

C = the Lot size (number of sold calls)

V2 = the current value of the sold calls,

X2 = the max P/L (max profit or loss)

fee = the fees and or commissions.

=IF (Stock Price, IF (LOTSIZE CALL < 0, Current_Value_Call + MAX_P&L, Current_Value_Call – fee),)

Rule 5: Vertical trades Bullish and bearish spreads rules:

How the Vertical Trade call or put Works: The Spread option trade strategy is employed when the trader thinks that the price of the Underlying asset will go up or go down moderately in the near term.

Bullish spreads implemented by buying an at-the-money option, and selling out-of-the-money option of the same underlying security and the same expiration month. I have decided to implement spread business logic by dividing spreads into two categories, credit and debit spread. Following Strategies Used Based on Stock Movement:

1. Bullish Strategies: When Stock is expected to move upside, following strategies are traded:
   • Long call +C: Debit
   • Bull Call Spread +C-C: Debit Spread
   • Bull Calendar Spread +C -C: Debit Spread
   • Bull Put Spread -P+P: Credit Spread
2. Bearish Strategy: When Stock is expected to move down side, following strategies are traded:
   • Long put -P – Debit
   • Bear put Spread +P-P: Debit Spread
   • Bear Calendar Spread +P-P: Debit Spread
   • Bear Call Spread -C+C: Credit Spread

Spreadsheet tabs in the spreadsheet: the logbook or tracker of all transaction data, the results page with the very useful possibility to filter by period, trader and account, the third tab is the portfolio overview and the last tab is the instructions tab. Let’s take a look.

Spreadsheet fields to track with my options trading journal for data entry and calculation fields.

Do not delete row 2 as formulae in row 2 would be impacted and render sheet useless.

Create dynamic charts

Using Pivot Table to aggregate data.

I can create a Pivot Table from the dataset (table of Option trade data), all of the columns from the dataset are available to use in my pivot tables.

Adding pivot tables to create eight dynamic charts, one for each chart or value we want to display on the dashboard. Below is the configuration used for each charts Pivot table:

1. Daily Profit and Loss (pnL)
   • pivot table data field for rows data: trade exit date
   • column
   • values: sum of total gains
2. Winning trades %
   • pivot table data field for rows data: trade exit date
   • column:
   • values: sum of “account profit”
3. Average gain / trade
   • pivot table data field for rows data: trade_id
   • column:
   • values: sum of “win” (count win)
4. Average gain in %
   • pivot table data field for rows data: (proceeds aka gain_amount)
   • column:
   • values average of gain amount
5. Total profit amount
   • pivot table data field for rows data: gain amount
   • column:
   • values: sum of gain amount
6. Total trades
   • pivot table data field for rows data: trade id
   • column:
   • values: sum of trade id
7. Long and short trades
   • pivot table data field for rows data: Trade id,
   • column:
   • values: sum of trade id by trade type

Adding slicers
Select any of the pivot tables to show the field list.

Right-click on Months and select Add as slicer
Right-click on Years and select Add as slicer

Creating the Charts

Add a Line Chart for the accumulated profit (different chart type and styling possible)

Add a chart using the same method for Daily PnL, for example a Line Chart or a Bar Chart

Add a chart using the same method for Long/Short, for example a Pie Chart

When done you should have 2 slicers and a few charts on the Pivots sheet.

Creating the Dashboard:

After the charts and slicers, next step is to create the dashboard on a new sheet or existing and name “Dashboard”

Start with an overview, about what needed to be displayed on our dashboard day to day. I have included Total P&L, realized P&L and win-rate. I can expand later to include other metrics and parameters that I am going need for further record keeping like, expiration or being assigned, total risk etc.

Data is derived from the pivots sheet, copy and paste the slicers and the charts (not any data from the sheet) on following fields:

• Total PnL
• Wins
• Avg gain / trade
• Avg gain / trade %
• Total trades

Features of portfolio tracker:

Important /must features:

1. Watch lists
2. Managing Portfolios
3. Setting Automatic Commissions and fees
4. Track Trades
5. Add Setups, Mistakes and set Risk Reward Ratio
6. Understanding Trade Results
7. Managing Trades Table
8. Bulk Actions
9. Understand Tendency Performance
10. Journal Trading Days
11. Use Filter
12. Find Profitable Trading Pattern
13. Review Your Monthly Performance
14. Setup Automatic Targets and Stop Losses
15. Build a Trading Plan
16. Tracks all financial assets held
17. Tracks portfolio diversity
18. Tracks dividend income
19. Tracks option premiums amounts
20. Includes trading calculator (with Projections)
21. Captures P/L from all sales
22. Computes account cash/margin balance
23. Displays monthly income/expenses
24. Compares year over year performance
25. Alert parameter on field DTE: Days until Expiration and is the number of days left on the option contract, at current date (today). This parameter will trigger alerts to manage the position (e.g., IF; DTE < 21 days THEN; “manage trade” alert).
26. Recommended Action: field trigger to “wait” or to “manage” position based on target variables:
    if DTE < 21 days then, “manage”
    if NetProfit > 50% then, “manage”
    else “wait”
27. consolidation trades from underlying transactions and calculates trading performance metrics and various monthly statistics to follow up on.
28. If you want to use it with excel you can export the resulting trade information.
29. Microsoft Excel trading journal using pivot tables can also be used as a dynamic dashboard. Trade data can be imported.
30. “Trade consolidation from underlying transactions“: It takes “options trades” and automatically group into spreads, calendars, long shorts, etc.,
31. Expire and assign options contracts: auto expire and assign your contract.
32. Rolls: manual, by closing a position and re-opening with the new options contract.
33. performance metrics measurement: such as win % and gain based on spreads instead of individual trades.
34. Options on futures contracts: minimum support for the following contracts: ES, GC,SI,CL,NQ,MES,MNQ,RTY,M2K,MYM,YM,KC,KE,LE,SB,ZW,ZC,ZB,ZN,SF,OJ,ZM,XW,XK,ZC,QQQ, Forex/currencies future contracts 6E, 6B, 6E*
35. Analysis: What assets or asset classes are most profitable
36. Track pnl on longs vs shorts
37. Track performance based on timeframe and trading style
38. Closing an option position

Trading metrics

Overview of monthly results

Monthly numbers in dashboard:

MONTH MM YYYY:  +/- $ Currency e.g., September 2022 +$ 500

1. Qty of positions closed
2. Qty of closed with net profit
3. Profit Success Rate: __ %
4. Total Net profit for positions closed with a profit: $
5. Total Net loss for positions closed with a loss: – $
6. Biggest Net Profit: +$
7. Biggest Net Loss: –$
8. Average number of days till expiration on opening: __. __ days
9. Average number of days held for the closed positions: __days

Win rate vs Expectancy:
The win rate is x%, which means the percentage of trades that net gained. The win rate doesn’t give a complete picture of trading performance.

Expectancy: takes into account the average size of gains and losses, giving a better picture of portfolio performance.

Past performance
The trade tracked by:

• Date (e.g., all trades from YYYY to YYYY).
• Instruments (e.g., all trades on AAPL, ESZ16, EURUSD).
• Strategies (e.g., all breakout trades, verticals, condors).
• Swing trade, day trade, instrument rankings over time and more.

Modules: Options Tracker → Data

setup for access to the application’s data layer to interacts with the database,
to enable the SQL database tests asynchronously

Module: Account→ Position→ Attributes
This module defines variables for creating entities via the Options Tracker → Accounts context.

D_Close : is the transaction date (or log date) which can be different from the closing date when the transaction is an adjustment to the option contract (eg rolling out in time or adjusting the strikes).
TrType Close: Transaction Close Type and is the type of Transaction to Close a position is either Buy to Close (BTC) or Sell to Close (STC) or ROLL when rolling the position.
UL Price @ Trans: is the underlying stock price at the moment the position is closed on the transaction date.
IVR @ Close: is the Implied Volatility Rank of the stock at the moment the position is closed on the transaction date.
Quantity: the quantity of contracts for the closed position (in my case practically always just one). If a partial close is made, a new position must be opened with the remaining quantity.
Prem Close: premium paid (debit for buying back to close) or received to close the position.
Trans Fee Comm: Total Fee and Commissions, and is the amount that has to be paid to your brokerage for executing the closing transaction.
Total Credit – Debit: calculated field of the opening transaction
Days Held: is the calculated field of the number of days between the opening and closing of the position.
Comments: field used for any useful comment for later review.
Net P/L: a calculated filed of the realized net profit or loss
ROC %: a calculated filed of the realized return on investment expressed in percentage for the net profit made on the investment cost in the field margin requirement.
Ann ROC %: a calculated field of the realized, non-compounded annualized return on investment expressed in percentage for the net profit made on the investment cost in the field margin requirement, taken into account the number of days the position was held.

References:

1. https://www.twoinvesting.com/2016/10/options-tracker-spreadsheet/
   • This is my starting point to pick up basic idea, Scott @twoInvesting decided to share his Google sheet stock and options trading journal. There is lot of discussion on his page about trading journal.
2. Reddit Journal wiki https://www.reddit.com/r/options/wiki/toolbox/links/#wiki_trading_journals_.26amp.3B_record_keeping