Spreadsheet to record the opening and closing of individual options transactions.
*** Digital Download: Google Sheets Spreadsheet ***
Are you trying to track the profit and loss of individual options transactions to match your brokerage statements? This spreadsheet is designed for the input of options trades as individual transactions which is the same way that brokerages record transactions.
Both complex and single leg options trades can easily be recorded. The sheet allows input of the purchase or sale of stocks, ETFs, puts, calls and dividends. It allows for both selling (short) and buying (long) and requires the input of the opening and closing of each transaction including commission fees. The profit (or loss) is then calculated and displayed on a per transaction basis.
Example: an out of the money put credit spread is often entered as a single order but is comprised of both a long and short option contract and opened for a net credit. This sheet would have a row entry for the long option and a second row for the entry of the short option. In the event that the trade is successful and both options expire worthless, the sheet shows the long put as a smaller loss than the short put which expires for a profit. The sheet would show a 50% win rate based on those 2 trades.
The Options Journal contains 3 sheets:
• Option Journal Sheet: Easily record all transactions for easy entry. Enter the buying or selling of shares, options, dividends all in one place with an easy to follow colour coded entry which guides you through the entry.
• Per Stock Sheet: Single cell entry allows you to quickly filter all the transactions based on the underlying. This displays combined performance and displays transactions by type to easily distinguish between open and closed options positions, as well as grouping dividends and stock transactions.
• Annual Tracking: Weekly summary showing total sum of trades opened or closed, total profit/loss, average profit/loss per trade, percentage of winning trades and any dividends received. Chart of the combined P/L over the year.
Option Journal sheet has simple entry in that all transactions are entered into a single sheet in date order, the columns come pre-programmed with the ability to sort and filter any column quickly with a click of the header.
Per Stock sheet (that can be copied as many times as you like) allows for the data to be displayed on a per stock basis. Single cell data entry allows for quick analysis without moving to multiple tabs. Entering the symbol KO would instantly show me a filtered summary of all of my Coca-cola entries and is colour coded for easy visibility.
The Options Journal sheet is full featured and is designed for serious traders to track a massive amount of data and a year's worth of trading is easily recorded. With that in mind, I've included an annual summary sheet that tallies all the transactions on a week by week basis and shows the win/loss rates as well as a chart of the years returns over time.
This Google Sheet as conditional formatting that uses an advanced colour coding system to help users enter data in an natural way and help identify potential data entry issues in variety of ways. There are built in warnings to highlight expired options that have not be recorded as closed and other helpful tips for common input errors.
The formulas and programming are also not locked so if you know what you're doing it's pretty easy to make modifications to the sheets for extra data entry or to customize the display.
Download includes 3x sheets in a Google Sheets workbook:
• 1x Option Journal: record up to 4100 Trades but can be expanded by adding rows
• 1x Per Stock Sheet: Filter by stock
• 1x Annual Tracking: Weekly summary
The pdf has links to google drive where the spreadsheets can be copied.
*** Enter Values into cells that have a WHITE background colour only***
This chart is ideal for investors who want to quickly enter all trades in a simple to use, powerful spreadsheet that tracks trades the same way the brokerages do. Check out my other best-selling spreadsheets by visiting www.drawbridgefinance.ca