Options Tracker Spreadsheet

If you follow Options Hunting you’ll know that I’ve been working on a spreadsheet to track options. Version 1.0 is now complete. It currently works for selling covered calls, selling cash secured puts, and selling naked puts. It will also work for selling naked calls, but I haven’t worked out the margin cash reserve calculations for that trade yet.

This is what it looks like:
putscalls

As with my dividend tracker spreadsheet, the orange cells are ones that you manually edit and the green cells are calculated automatically. Here’s what each column means:

Stock Symbol
The ticker symbol for the stock underlying the option contract. I use a Google Finance call to look up the stock price so you must use the ticker symbol as recognized by Google Finance.

Open Date
Open Date is the date that the option contract was opened.

Exp Date
Exp Date stands for expiration date and is the date that the contracted is scheduled to end. If you decide to close a contract early, then make sure to update the Close Date (see below).

Call or Put
Type “Call” or “Put” here depending on what kind of contracted you opened.

B/S
This stands for buy or sell and refers to how the option was first started. If you sell a put you are technically “selling to open.” Most people will likely just keep this as an S.

Stock Price DOC
Enter the underlying stock price at the time you opened the contract. This field is used to calculate the annualized rate of return for a margin account and is used in the calculation for determining margin cash reserve.

DTE
Days to expiration. This shows the days left on the option contract. If the option has already expired, then 0 will be displayed rather than a negative number.

Current Stock Price
This field shows the current stock price of the underlying stock.

Break Even Price
This field shows the break even price for the option exclusive of any fees. For example if I sold a put on a stock priced at $100 and received a premium of $1.50, my break even price is $98.50. If I sold a covered call on that same stock and received $1.00, my break even point would be $101.

Strike Price
The strike price of the option.

Premium
Premium is the money collected for selling a put or call. It is also what you pay if buying a put or call. Since contracts are transacted in increments of 100 shares, you’d enter the 2.38 into column if you received $238 in premium.

C
C stands for contracts and indicates how many contracts you either sold or bought.

(Put) Cash Reserve
This field show the amount of money needed on hand in order to sell the option. This will be 100 x the strike price. For a non-margin account, that total amount needs to be in the account before your broker will allow the trade to go through. This is why this is called a cash-secured put. That cash is ear marked for that option trade in case it gets put to you. If you sold a call, then this field is not used.

(Put) Margin Reserve
This field calculates the amount of money needed in the account for a naked put sold in an account with margin. I used Schwab’s calculations for this which is (25% of the underlying stock’s market value + the option ask price – any out-of-the money amount) x 100 (per contract) x the number of contracts. If your broker has different requirements then this formula can be updated to reflect that.

(Call) Cost Basis/Share
This field is for selling covered calls. It can be used to calculate the annualized rate of return (column U). However, this field is not currently used. The annualized rate of return is based solely on the option rate of return as calculated on the strike price. It does not take into account any gains or losses from selling the underlying stock. I did this to keep this spreadsheet dedicated to profit/loss only related to options. Any stock transactions can be performed on a separate spreadsheet. This can changed by editing the formula in column U, if desired. Let me know if you’d be interested in that and I can do the formula for you. Calculating the rate of return can be tricky, as discussed here: Calculating Covered Call Profits – Not As Easy As It Sounds

Fees
Enter any fees associated with the trade. If I sold to open a contract and it expired, then I’d just put the fee of the initial trade. If I bought to close the option then I would add that additional commission to the original value.

Exit Price
This is the price to exit the option. If it expired, then you’d either leave it blank or type in 0. If you bought to close, then type in whatever premium you paid.

Close Date
This is the date you either closed the option or it expired. It is used to calculate the Days Held column and is important for accurately calculating the annualized rate of return.

Profit/Loss
This show the final profit or loss for each finalized option trade. The cell will be dark green for profit and red for a loss.

Annualized ROR for Options
This calculates the annualized rate of return for the option trade. As described above, it does not include any profit or loss from selling the underlying stock in a covered call situation.

Margin Annualized ROR
Calculates the annualized rate of return based on the smaller margin cash reserve. This field is only used at this point for puts. It is not yet set up for calculating the rate of return for naked calls.

Status
Open, Closed, or Exercised. An example of exercised would be that you sold a covered call and it got called away from you.

Account
I included this column in case you have multiple accounts in which you do your option trading.


The Puts/Calls sheet is where you enter all the transactions data.

For my spreadsheet I’ve decided that it is best to “realize” the profit or loss when the option trade is actually completed. While it does feel good to book the premium collected as profit for the month you sold it, it makes more sense to realize it after the option is closed, either through expiration or through buying the option to close. This keeps things simple. If I were to need to roll an option I’d first finalize the old option by buying to close and then opening a new one on a later row.

In doing things this way, the entire trade is complete on a single row. It makes it easy to go to the profit/loss column and see how each of your options performed.


Summary
option trading summary

The Summary sheet automatically shows the total profit/loss that is both realized and unrealized, the cash reserves for the open trades, and total fees. And, as similar to my dividend tracker, it also shows a table displaying monthly option income each year.


Preview and use the Options Tracker spreadsheet

Please let me know what you think! This is version 1.0, but I did enter in all my options transactions in 2015 and 2016 and looks pretty good. Any other features you would like to see?

You may also like...

25 Responses

  1. Thank you Scott for taking the time to pull this tracker together. You know I’m a huge fan of your dividend tracker. I had tried to make my own options tracker but it doesn’t compare to this one you’ve just created. As a beta tester of this tracker, I can honestly tell your readers that it’s pretty awesome. The version I tested didn’t have a summary page. thanks for adding this. Much appreciated.
    Options Hunting recently posted…Busy Week – Here’s All Of My TradesMy Profile

    • scott says:

      You’re welcome! If there’s any other features you and other options traders are looking for, let me know and I’ll see what I can put together.

      Scott

  2. John Harker says:

    Thanks for the tracker tool. Just a question in relation to the( Put) Cash Reserve and (Put) Margin Reserve calculation. Should the formula not contain a check if the option is still open. If the option is closed there is no longer a need for the reserve so the sheet will indicate a higher reserve that required.

    • scott says:

      Hi John, thanks for the comment. There is a check of if the option is open or not on the Cash Reserve section of the Summary sheet. I didn’t include that check on the Puts/Calls sheet. Would you like it added there too?

      Scott

  3. Adam says:

    Thank you Scott! I am a fan of your dividend tracker and happy to see an options tracker as well. Selling Puts is helping me to recover from a terrible investment I made in my Rollover IRA; bought a 1K shares of a stock a couple of years ago at $29 and it dropped to $6 in a span of 1-2 weeks. I am now a DGI, selling Puts to collect incomes while I fill out my portfolio.

    I am going to see if I can incorporate the dividend and options trackers into one. Just wondering if you had thought about doing that.

    BTW, I have enough accounts at Scottrade that they give me free regular trades, I only have to pay for options. And I love their FRIP, F for Flexible. The dividends I collect go into a pool to buy any other stock(s), up to 4, automatically fee-free. Thanks again.

    • scott says:

      You’re welcome, Adam! I actually combined the dividend and option tracker into one spreadsheet for my personal use. If you want to get a copy of that, just send me your email on the Contact Us link.

      Scott

    • scott says:

      Hi Adam,

      Thanks! Sorry to hear about the loss in your IRA, but great job using options to get that amount back up. I just checked for November and options added $443 to my income this month…pretty amazing!

      I just sent you a copy of the spreadsheet that I’m using. Let me know how you like it. It does combine both options and dividends into one spreadsheet. I didn’t release it publicly because it could get confusing and/or have extraneous information for those that don’t trade options.

      Scottrade’s FRIP sounds amazing. Wish Schwab did something similar.

      Scott

  4. Austin Roberts says:

    Hey Scott,

    I had some individual option return calculation spreadsheets I had been using but having everything on one spreadsheet is awesome, so thank you! I have a couple questions, 1. Do you have your options spreadsheet in Excel format by chance? When I tried copying it from Google Drive the formulas didn’t translate. 2. Could you send me your most updated version of your options spreadsheet and any other ones you use to track your investments? That would be amazing! And 3. Did you figure out how to include a formula or another couple columns to calculate your gains on a covered call position on a stock you already own? Thanks so much!

    • scott says:

      Hi Austin,

      I’m glad you like the spreadsheets! I’ll address each of your questions here.

      1. I don’t have the spreadsheet available in Excel. I’m sure it could be translated over but as it is now it uses some formulas that only work on Google Sheets (mostly those that rely on looking data up online).
      2. The latest versions of all my investing spreadsheets are available online here.
      3. I could definitely include a formula to calculate gains on a covered call position. I left that out but will get back to you soon with this feature.

      Scott

  5. Colin says:

    Thanks, Scott. I’m new to options and was looking for something like this–your sheet exceeded my expectations though! Gonna check out the dividend tracker next. Thanks again, really appreciate the time you must have put into this and the generosity in sharing it.

    Cheers!

    Colin

  6. scott says:

    Hey options spreadsheet users,

    I just released a minor update that now correctly calculates the annualized returns. It was off slightly on the initial version. Also, this new version now also tracks annualized returns for buying calls and puts as well as selling them. (Thanks for finding these, Ryan!)

    In the next update I will be including a formula to calculate gains on a covered call.

    Scott

  7. Jeff says:

    Hello Scott,

    FANTASTIC work! Already love and use your Dividend spreadsheet…now found this today. I did see under “hidden sheets” there are placeholders for “spreads” and “iron condors”. They are empty? Is this still work in progress? 98% of what I do are spreads…so if it is work in progress then I will happily wait till that portion arrives!

    • scott says:

      Hi Jeff,
      Glad you are enjoying the spreadsheets. Yep, spreads and iron condors are two things that I will be working on. I first wanted to make sure that simple calls and puts worked well first. I don’t have a timeframe for spreads yet, but hopefully soon-ish.

      Scott

  8. Jeff says:

    Also…as another user mentioned about having everything in one large spreadsheet (dividends tracker, stocks/options tracker, etc) I would love that as a choice too. Maybe 2 different downloads/versions?

    • scott says:

      I’ll see what I can do. The problem comes that if I make a change on one spreadsheet, then I also have to make that change on every version of it as well. I get enough questions about simple things on the dividend spreadsheet that I feel that adding more functionality with options may make it too complex. I’ll see if there’s a simple way I can combine the two.

      Scott

  9. Angelo says:

    Thank you for creating your deceptively simple yet elegant spreadsheet for tracking options trades; specifically covered calls. Have you any plans in the future for adding an additional level of security to your covered call spreadsheets by offering a married put or collar version? This is something I’d ‘jump on to’ very quickly as I am in my sixties very conservative and fanatically anxious to preserve my trading principle; and without any proof to what I am about to say, I am almost certain there is a larger market out there for Collar or married put spreadsheet as (I believe) that the majority of CC investors are in or near retirement age and are the largest segment of American CC investors; Your thoughts?

    • scott says:

      Hi Angelo,

      I am definitely open to creating additional versions. I was going to work on iron condors next. I’ll look into married puts and collars as well. Thanks for your comment. And, I’m glad the put/covered call tracker is working well for you.

      Scott

  10. Buddy says:

    Where is the file? I cannot find it.

    Thanks

    • scott says:

      Google discontinued their Template gallery and have not yet migrated the user created templates yet. It can be accessed directly here. Just click “File” and then “Make a Copy”

      Scott

  11. Mark says:

    Thank you for sharing your work.

  12. Bayram Dayanikli says:

    Thanks a lot for your work, will start working on it.

  13. Alan Adler says:

    Hi Scott,

    Love your Options Tracker Spreadsheet. How can I get a copy of this. Do you sell a program? I’m a active trader, but knew to options. Everyone advises me to keep detailed records and your spreadsheet is the best one I’ve seen.

    Please advise and thanks for your time.

    • scott says:

      Hi Alan,

      I’m glad you like it! The options tracker spreadsheet is free to use. I just created it in my free time. To use, just follow the link at the bottom of this page. Make sure you are signed into Google before clicking the link. Then click the “Use Template” button at the top right. Let me know if you have any more questions.

      Scott

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge