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. I mainly use the spreadsheet to track my strategy trading the Options Wheel which I’ve written about in detail or trading covered calls straight up as well as cash secured put! Finally, I also dabble in various call and put spread options to minimize my risks when trading around earnings.
Trading options has helped me reach Financial Independence in 2020 which was quite the milestone for me. In the last year, I’ve traveled the world for a year and my net worth actually increased.
- Trading options is all a part of my net worth building regimen. I use this spreadsheet to track net worth and expenses.
- If you are looking for a similar spreadsheet to track vanilla stocks, here is my stock portfolio spreadsheet
- The ultimate spreadsheet to track all your credit cards, sign on bonuses, and annual fees.
- Also, check out my travel itinerary planning spreadsheet which is perfect for organizing and planning your trip!
- As well as my Restaurant List Tracker spreadsheet for keeping track of all the restaurants you have been to in your hometown or abroad.
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.
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 are risky
Options are inherently just way riskier than buying standard stocks or bonds. The Wheel strategy is perhaps one of the safest options trading strategies but that’s still not saying much. If you option wheel a meme style growth stock, you are bound to get destroyed when the markets crash.
Make sure to understand the risks of options trading before engaging in it! Otherwise, an alternative you can consider is buying I Bonds (Inflation bonds) which are paying a risk free 9.62% as of 2022 and 6.5% in 2023!
Update 2023: Many of you have emailed me asking about how to trade the option wheel during bear markets. As 2022 was the worst bear market since the financial crisis, it is totally understandable! Again, the option wheel strategy is not without its risks and if you wheeled a crappy stock in 2021/2022, it’s likely you are bag holding already. One of my readers, James, recently wrote a great article detailing how he was able to option wheel during the bear market of 2022.
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:
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.
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!
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.
Some of the spreadsheet is created with Google Finance. Google Finance is an incredibly powerful function and I’ve explained in detail about this function in my Google Finance spreadsheet post.
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.
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.
Running the options wheel strategy
One of my favorite strategies for trading options is running the options wheel. This strategy involves consistently selling puts and calls to generate a consistent income. It’s a very popular strategy and the crux of this strategy relies on theta decay.
It’s probably the easiest and the “safest” option strategies to run out there. Of course, this is options we are talking about so nothing is safe but compared to naked calls for example, it is much more conservative.
I’ve been option wheeling for quite some time now as a way to supplement by retirement income by generating weekly premiums.
To learn more about the option wheel strategy, make sure to read my option wheel strategy post!
- The Ultimate Stock And Investments Portfolio Tracking Spreadsheet
- Generating Retirement Income From Selling Covered Calls
- The Ultimate Google Finance Spreadsheet
- Ultimate Covered Call Strategy Guide: Passive Income Generator
- Ultimate Call Spread And Put Spread Option Strategy Guide
- The Ultimate Options Wheel Strategy Guide: Passive Income Generator
- The Best Passive Income Trading Strategies: Option Wheel, Dividends, REITs, and More
- How To Trade The Option Wheel During A Bear Market
- Ultimate Guide To Selling Puts (Cash Secured Puts)
- The Perfect Mortgage Spreadsheet With Amortization Schedule and Profit Calculator
- The Ultimate Monthly Expenses And Budgeting Spreadsheet
- The Ultimate Personal Finance And Budgeting Spreadsheet