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. 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!

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.

 

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:  Financial Independence (FIRE): 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:  The Ultimate Stock And Investments Portfolio Tracking Spreadsheet

 

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:  The Ultimate Budgeting Spreadsheet

 

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!

Continue Reading:

Showing 28 comments
  • Fax Ayres
    Reply

    Hey Johnny, this is great stuff. I’m going to test your option wheel strategy on BTC and ETH. Thanks for all the info.

    • Johnny
      Reply

      They have options on btc and eth?

  • D A
    Reply

    Recieved an email update you responded Johnny, but the website is not showing up the comment for today.

  • D A
    Reply

    Hi!

    I was wondering if you could please explain your profit stop column on the spreadsheet. I’ve looked at the formula and see that there is a 85% profit target, what is that 85% though? Thanks!

    • Johnny
      Reply

      It’s just an arbitrary number I set for myself for my target goals. So if the price hits this level I’ve made 85% profit. I also just this chilling to set take profit levels in my brokerage

  • Ran
    Reply

    Hi there, may I know, on the “divi yield” column, why is it that certain rows are empty, certain rows have values?

    • Johnny
      Reply

      Some stocks pay divis some stocks dont.

  • Bailey
    Reply

    Where do I enter my closing price of the option on the spreadsheet?

  • Sami
    Reply

    Hi Johnny

    Wow congratulations for all the priceless information.
    My question is : How do you select the stocks to sell the put against to start your wheel (they are so many stocks available) ….What makes you choose ARKK, NIO, etc?

    Thank you

    • Johnny
      Reply

      I think I just look at a company that has good growth prospects and then look at the iv levels and just jump in. Not everything is profitable though and it’s only a matter of time before I’m bag holding!

  • Sean
    Reply

    Hey man just getting started on this sheet. Seems pretty cool. Wondering about the PremiumBreakdowns tab. Is that supposed to auto populate? I have entered a couple trades and only the first one I entered showed up there. Wondering if I accidentally deleted something when I deleted all your trades to use it for mine? Can you help?

    • Johnny
      Reply

      Hi Sean, you ight have to expand the “filters” part in the pivottable and select all the stocks you have on the sheet. I had to add a filter because I wanted to remove the “total” row otherwise the grand total at the bottom of the table would be double counted.

    • Sean
      Reply

      So been messing around a bit more, it seems it only shows up if it’s already been done by you? The reason my first one shows up and updates is because it’s ARKK. If I try and do other tickers that you didn’t already have, it does not show up or update. Does the premiumbreakdowns tab have some sort of pre approved list it checks against?

      • Johnny

        You need to manually select the new stocks you’ve added in the filter box, otherwise it won’t show.

  • Matt
    Reply

    You say wheel, but there are some stocks in your spreadsheet for which you never sell puts even after a call is assigned. ARKK is one of them, you only sold calls even if many of them were assigned. Care to explain? Thanks

    • Johnny
      Reply

      Yes you’re right. I should just say ARKK is a CC but just labeled it as a wheel. I wouldn’t read too much into my own trades!

  • Konstantin
    Reply

    Hey, thank you for this really beautiful spreadsheet!

    However, I think there is a mistake in formulas. Closing cost value (Q column) is not being being used later, for example it’s not divided from the total Profit. Please fix it.

  • Mike C
    Reply

    Just found you by googling: option put income spreadsheet

    After discovering this strategy a few years ago I’ve been generating enough income for our family of 6. The strategy termed as The Wheel was new to me and I was stoked to find other like minded options traders doing essentially the same sell put, if assigned sell covered calls, wash-rinse-repeat strategy. Just downloaded the google doc which is an improvement over what I created back when. Are you part of an options trading group? I’ve tried a few but looking for something free where traders meet, no commercial flavor.

    • Johnny
      Reply

      Hey Mike, not part of any options group. I dabbled a bit in the reddit thetagang subreddit but it was too much for me. Let me know if you find something good!

      • Mike C

        I’m in the same subreddit. Where do you trade your options? I’m with Schwab and they hit me with $.50 per contract sold. If I could avoid that I will.

      • Johnny

        Robinhood for sure. No fee options

    • Mike C
      Reply

      Johnny, In the “strategy” column does “CC” represent covered calls? I’ve adopted your spreadsheet to track my wheel strategy so want to fully understand how you use your tracker. So question. Looks like you own NIO shares are now selling covered calls, in fact a sale today 10/25, but you listed the strategy as Wheel, not CC?

  • Matt
    Reply

    Hi Johnny, I designed and build a website to replace spreadsheets when trading the wheel. You can get notifications, track changes in the greeks, automatically group different strategies. Can you take a look at https://optiontracker.io/ and let me know your thoughts? There is a 90 day trial.

  • Shay Johnson
    Reply

    Hi Johnny,

    Thank you for the great information.

    I’ve downloaded your options Wheel Strategy spreadsheet. Are “closing costs,” Q6 the brokerage commission fees? And, in the Stock Assignment chart, there’s no column for the brokerage fee. Do you calculate those numbers in your P&L?

    And, can you tell me what the “profit yield” is?

    Thank you for sharing your knowledge so freely. I truly appreciate it.

    • Johnny
      Reply

      Hi shay, closing costs is if you close the option before expiry, you will have to sell it at a price. Often times I will close a position when it has hita profit goal. I use only no commission brokerages for trading so I’d recommend you do the same especially in these modern times. Profit yield is the premium collected divided by the strike price. It’s a measure of how much profit I’m getting with how much money I’m risking. I try to target around 1%

      • Shay Johnson

        Thanks a million!

        Happy travels to you, where ever you are!

  • 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 OR ASK A QUESTION. BLOGGING IS NOT THE SAME WITHOUT YOU, THE READERS!

Start typing and press Enter to search

Meteora Greece
18 Shares
Tweet
Pin18
Share
Reddit