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.
- Trading options is all a part of my net worth building regimen. I use this spreadsheet to track net worth and expenses.
- 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.
- For a list of all my credit card and travel hack related posts, click here.
- 1 Why use a spreadsheet to track options trading?
- 2 Options Trading Spreadsheet
- 3 Using the Options Spreadsheet
- 4 Selling Options is a great way to generate income
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 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.
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.
I will go into greater detail about this strategy in a later post so stay tuned!
- The Ultimate Spreadsheet To Track Credit Card Churning
- The Ultimate Net Worth, Budgeting, And FIRE Spreadsheet
- The Ultimate Restaurant Tracker List Spreadsheet
- The Ultimate Travel And Vacation Itinerary Planning Spreadsheet
- Do You Need A Business To Get A Business Credit Card?
- Travel Tips: 10 Things To Double-Check Before Traveling Internationally
- Ultimate Guide For Revolut: Zero Fees To Exchange Money
- Best Ways To Meet The Minimum Credit Card Bonus Spend Requirements
- Chase Sapphire Reserve vs Preferred: How To Get Both Cards
- Ultimate Guide To Churning And Using US Credit Cards As An Expat
- Why Opening And Closing Multiple Credit Cards Doesn’t Kill Your Credit Score
- Credit Card Inventory Update 2019: My Credit Card Strategy For The Year