The Ultimate Spreadsheet For Options Trading And Tracking

Spreadsheets are life and I am a fan of using them for everything. I religiously use a spreadsheet to track my net worth and expenses as well as when I collect credit cards for their lucrative rewards.  I am an avid credit card and points collector as you can read from my comprehensive guide to travel hacking and credit cards which explains everything you ever needed to know about how to use credit cards to maximize traveling.

In recent years, I’ve started to trade options in a conservative manner to generate more income in FIRE (FInancial Independence Retire Early) lifestyle. I couldn’t find many good spreadsheet options to track the list of all my options positions which turned out to be ever increasing.

 

 

Why use a spreadsheet to track options trading?


I think the majority of people trading stocks and such will just leave the journal making to the brokerage. I know most brokerages nowadays do a respectful job of keeping a detailed transaction history and tax lot information for your trades on their websites.

However, I firmly believe you just can’t substitute for a nice spreadsheet which puts all the information that I need in one window that I can easily access and analyze.

This post is certainly not about how to trade options and how to profit off them. They are incredibly complex and super risky financial instruments. In recent years, they’ve been the source of all the YOLO trades on Reddit and Robinhood but they will completely wreck your account if you don’t know what you’re doing. This post is not to provide any sort of financial advice but rather sole to provide a spreadsheet that tracks your trades.

Read Also On Johnny Africa:  How I Retired Early At 34 With Over $1m

 

What information to keep track of for options?

Options are much more complex than just trading traditional stocks. They are derivative after all which derive their value from the change in value of another asset. There is very complex mathematical formulas and market forces that contribute to the price of an option. Therefore, depending on your needs, it’s good to keep as much information as possible. Here are some of the things I like to keep track of:

  • Entry date: Without a doubt, you’ll want to keep track of the day you entered a trade
  • Stock ticker: Self explanatory
  • Expiration Date: When the option expires. This is important especially if you are selling options like myself
  • Strike Price: Self explanatory
  • Premium Paid/Collected: Depending on if you are buying or selling the option, it’s good to keep track of how much money you are paying or receiving
  • Greeks: I like to keep track of the option Greeks when I enter. Normally, I find tracking Delta and Implied Volatility as the most useful when it comes to record keeping
  • Date Closed: If you’re trading options, keep track of the date you closed the trade and the price you closed it at
  • Strategy: Which strategy you are implementing: Covered call, Cash covered put, Options Wheel, Iron Condor, YOLO long call etc.

 

Options Trading Spreadsheet


I think a lot of old school traders keep a detailed journal for trading. I wouldn’t say I am old school because I’m not that experienced of a trader at all. However, I have been using a few strategies to generate consistent income while I was working and after I retired early.

 

Options Wheel Strategy

For the majority, I largely use this spreadsheet to track my options wheel strategy. To summarize, the options wheel strategy is simply to sell cash covered puts and collect the premiums until you get assigned. After you get assigned with 100 shares, start selling covered calls until you are once against assigned. Rinse and repeat.

This is perhaps the most boring options strategy of all time, but it is of the least risky and least complicated to understand. Here is a great and simple diagram explaining the process:

How to CONSISTENTLY Outperform the S&P500 using Theta Gang Strategy. A Comprehensive Guide to Wheeling ETFs. : wallstreetbets

Because I run the options wheel strategy consistently, I end up trading A LOT of options every week. I trade about 10 different companies to diversify my risk and mostly sell options one or two weeks out meaning I rinse and repeat constantly. This generates a lot of trade records and looking at the transaction history of my brokerage just won’t cut it. This is why the spreadsheet is great.

Of course, if you are doing straddles, iron condors, or something more complex, you can easily use this spreadsheet and customize it to your own methods.

Read Also On Johnny Africa:  How I Retired Early At 34 With Over $1m

 

Download spreadsheet

The spreadsheet is simple, yet effective. Just make sure you update it on a regular basis. I would recommend spending 5-10 minutes updating the spreadsheet once a month with your figures. I don’t always do the exact amount for different expenses because it takes a lot of time but that is also up to you. You can use this spreadsheet if you’re a single person as well as if you’re a couple (just add everything up!).

Keep in mind that this spreadsheet is also what I use to track my own expenses. I will be making updates live and will add additional features to this spreadsheet as time goes on so make sure to check in for updates!

 

Download Options Trading Spreadsheet

 

 

The spreadsheet is in Google Sheets because if I update it in the future, it will be easy to access from anywhere with an internet connection. I prefer this to Microsoft Excel. It’s easy to download it and use with Microsoft Excel as well.

To download offline, click the red button above, then click file > download as > Microsoft Excel

To use it with your own Google profile, just click file > make a copy

 

 

Using the Options Spreadsheet


As someone that works with spreadsheets regularly, I’ve included some functionality like conditional formatting and formulas that an Excel novice might not understand. I will explain everything necessary for those wanting to get the full use out of the spreadsheet.

For the most part, anyone with any excel experience should just follow my spreadsheet and populate accordingly. Do not touch cells with formulas because everything is intricately linked. All cells that you should update have been color coded with gray cells.

 

The shaded cells

All shaded cells on this spreadsheet are formulas. They are not mandatory but I find them helpful for my own trading purposes. You may or may not depending on your style.

Of course, you can modify them or add your own formulas to your use.

  • Moneyness: This column is tracking how close to the money I am. This works for calls and puts and is a formula that takes the difference between your strike price and current stock price to calculate how much room you have before the option is in the money. For the options wheel strategy, being in the money will mean you will be assigned upon expiration
  • Stock Price: This uses a Google finance formula to grab the most stock price data
  • Status: This column is one you’ll want to edit depending on where you are with the trade. I like to keep track of whether the trade is open, closed, expired, assigned etc.
Read Also On Johnny Africa:  How I Retired Early At 34 With Over $1m

 

Adding new trades

The spreadsheet is not super fancy unfortunately. If you are adding a new trade, simply copy and paste the previous row and update the ticker and option information accordingly.

Let the formulas auto-populate accordingly.

 

Close the trades and keep track of them

When buying or selling options, I rarely wait until the option expires, even if I am in the money. Often times, I will close the trade after I’ve hit a certain profit threshold because there is always a chance for the price to move against you.

Populate the Closing Date and Closing price columns accordingly when you are selling your options early to keep track of your net profit.

When you sell options, you receive the full premium up front but you can “buy back” the option once you’ve made enough money to close out the trade.

 

Selling Options is a great way to generate income


Selling covered calls and cash secured puts (or the options wheel) is a great way to generate “relatively” safe income during retirement.

It is one of the primary methods I use to generate a little extra income during retirement. While not necessary for me since the Trinity principle already states I have enough of a portfolio to live off the interest, I am okay with a little more risk and find selling options to be quite interesting.

I will go into greater detail about this strategy in a later post so stay tuned!

Continue Reading:

Comments
  • tony manzi
    Reply

    Wondering how you handled the wheel strategy in March 2020 when the market tumbled. A put assignment followed by a covered call buy out would seem to lock in a good bit of loss in many cases.

Leave a Comment

Start typing and press Enter to search

Meteora Greece
0 Shares
Tweet
Pin
Share
Reddit