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

Click here to access the Options Tracker Spreadsheet on Google Sheets

You’ll have to click File > Make a copy… in order to start using it yourself.

 

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.


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?

The spreadsheet is free and will always be available for free. However, if you find this spreadsheet useful, please consider donating to support my coffee fund and hosting costs. Thanks!

 

 

151xbHsMKpNBtHPb1tsacGWzoaCKYG9QH6   

 

You may also like...

144 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

  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

    • Stephen says:

      I think I have the latest but it doesn’t seem to calculate a covered call. Can you let me know the steps for a covered call.

  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

  14. Mayer Liebman says:

    Dear Scott,

    Thank you for a great spreadsheet. However, I have a question. If I sell a Call on a stock with a Strike Price of $100.00 and I receive a Premium of $1.00 for the Option, shouldn’t my breakeven point be $99.00 and not $101.00 as stated in the instructions?

    Also, for some reason, I do not see any action (so to speak) on the Summary Section. Is there something I have to do to get it to update?

  15. Mayer Liebman says:

    Sorry, I was confused. You are correct and I am wrong. Your break even number is right.

  16. Brian says:

    The Monthly and annual tables are not working.

  17. Osuri says:

    Great spreadsheet, thanks for all the hard work you put into it, to make our lives that much simpler.

    Some comments/questions/issues/suggestions, when we work with our own copy:
    a) Do NOT delete any row, particularly row 2 (since I didn’t have any Buy contracts to start with), or else some formulae in the Summary tab get whacked out, and have to be manually edited for correctness.
    b) Do NOT manually append a row at the end. Instead, copy, say the previous last row, and modify it. Otherwise all the green rows for that row would end up blank as well.
    c) A suggestion: Since the Status column (W) needs to be manually edited, maybe it should be in orange as well.

    I wish there was a way to adjust the profit/loss smartly, when I get “put”, on a Put contract, and end up with a loss (at least on day one). Oh well, I will try to tweak it myself. There is no end to making this great product even greater.

  18. Mark Danner says:

    Hi Scott, highly interested in your Options Tracker spreadsheet and I am hoping you’re still working on it as your time permits. I previously used a similar sheet in Excel which, appears to have been submarined by the latest fiasco on Yahoo. I do have a few questions whenever you have the time.

    1) So, first and foremost your sheet appears unaffected by the mess at Yahoo. Is that true?

    2) like some others I’d love to have a copy of your combined dividend and options tracker in one sheet.

    3) As you’re able to update new versions will there be a way to port over ones transactions into the new sheet without having to re-enter all of one’s positions?

    4) Is there an easy way to duplicate the put / call sheet and thereby put calls on one sheet and puts on the other?

    5) Any plans for a synthetic long, i.e. a purchased LEAP call and a sold LEAP put on one sheet.

    6) is there a way to include the current of the option into the sheet so that the price would be automatically updated as the sheet is loaded? (Just answer as your time permits, sorry don’t mean to overload you.)
    Mark Danner recently posted…October 2017 IncomeMy Profile

  19. D. Long says:

    Am I correct that I cannot use the program for buying calls and buying puts.
    How do I save it?
    I appreciate your efforts on your putting this together
    D. Long recently posted…Replacing Yahoo Finance with the IEX API in Google SheetsMy Profile

  20. Stephen says:

    How do I enter a covered call and calculate the return if called away, if sold, if expired

    • scott says:

      Hi Stephen, Thanks for the comment. I’ll get back to you in the next day or two. At work now.

      Scott

      • michael says:

        is there an answer to how to do this?

        • Mark says:

          Michael,

          Quite obviously I am not Scott. But, you certainly can calculate the return of a covered call whether it expired worthless, was assigned or, was bought back prior to expiration. Have you downloaded Scott’s spreadsheet? I’m pretty sure it calculates it automatically.

          By no means am I a mathematical genius but, for the Current Profit I use the following formula on the covered call page of my spreadsheet: =IF(L2,IF(C2<0,V2+X2,V2-I2),) with L2 = the stock price, C2 = the number of sold calls, V2 = the current value of the sold calls, X2 = the max profit or loss and I2 = the fees and or commissions.

          From that point I have formulas for Max % Return, Annualized Return, as well as Actual % Return and Annualized (Actual) % Return. Does that help?

          It's probably a lot easier to see if you download Scott's option sheet or, if you prefer, send me a private email (address below) and I can send you my sheet.

      • Jason Edelman says:

        was there ever an answer to this?

  21. Steve says:

    Use your two trackers on a weekly basis, will be daily soon I am sure. Love the simplicity of use and the details it shows. It is helping me track on what strategies are working and which are not so much by having them as different accounts.
    One question thou. the Summary page and the Monthly Option Income each Year by month is calculated using columns we cannot see. My questions comes about as it is showing a value in November (that is the only month I have data other than two entries in October that have yet to expire in December) that does not make sense to me. It is greater than the Open profit/loss and the Closed profit/loss. So I am confused how it calculates it and if it is correct or not. If you could help with that it would be awesome. Many Thanks as always. Look forward to your updates and you have inspired me to keep track of everything. Steve

  22. Vash says:

    Scott, is there any way to pull the live quote for each position into the spreadsheet?

    • Mark Danner says:

      Vash,

      Scott is certainly the guru here but, for the short term at least I’ve been pulling quotes from Yahoo with the following script:

      function onOpen() {}

      //get midprice
      function getMidPrice(ticker) {
      ticker = encodeURI(ticker);
      var response = UrlFetchApp.fetch(“https://query2.finance.yahoo.com/v7/finance/options/” + ticker);
      var chain = JSON.parse(response.getContentText());
      var ask = parseFloat(chain.optionChain.result[0].quote.ask);
      var bid = parseFloat(chain.optionChain.result[0].quote.bid);
      var mid = (bid + ask) / 2;
      return mid;

      }

      Obviously, this is a “try it at your own risk” thing especially considering Yahoo closed down their API a few months ago and thereby causing disruption to quite a few users who need/want to pull option quotes into their spreadsheets. Then, you’ll need a column for the mid price which you can pull with the following formula: =getMidPrice(AG3) with AG3 being the column that contains the symbol for the option.

      I also know that Interactive Brokers has their own API but I haven’t even begun to figure out how to use it. Good luck and be sure to save a copy of your sheet if you decide to try any of this.
      Mark Danner recently posted…Added multiple currency support to dividend portfolio spreadsheetMy Profile

      • Vash says:

        Can you walk me through how to use this? I went to the script editor in Sheets and entered that in and it keeps throwing an “Illegal character” error on Line 6.

        • Mark Danner says:

          With this websites permission I don’t mind giving it a shot. Mind you I’m not near the “brain” that Scott is when it comes to writing spreadsheets but, I’ve learned to copy / paste real well.

          The “code” that I posted appears to be the same that’s in my script. For instance, my line 6 says

          “var response = UrlFetchApp.fetch(“https://query2.finance.yahoo.com/v7/finance/options/” + ticker);”

          without the quotes. But, it does end with the semi colon.

          Do you have a column in each individual sheet that you’re trying to pull quotes into for the option symbol? Then, another column which references the cells in the symbol column? For instance, my quotes are pulled into column N with the command =GetMidPrice(AG4) with AG4 referencing the AG column which contains the specific option symbol for cell N4..

          Does this make sense?
          Mark Danner recently posted…Added multiple currency support to dividend portfolio spreadsheetMy Profile

          • scott says:

            Thanks, Mark! The spreadsheets are free to update however anyone likes. I’ll take a look at incorporating some of your suggestions into a future version.

            Scott

  23. Sterling says:

    Do you have a way to track vertical spreads or butterflies?

  24. Chris says:

    Scott,, thank you for building this. I am having an issue and I cant seem to get passed it. I enter all the info for the orange cells (Options Tracker). Call, B, 14.92, Strike 15, prem 0.44, If I enter that I closed out at 15.50 I have a return of $1498 or 28,242%. this is for ONE option contract. where am I going wrong?

    Thanks again!
    Chris recently posted…March & April IncomeMy Profile

  25. Carol says:

    Hi Scott,
    I just downloaded your options spreadsheet and filled it in with recent trades. The thought struck me that it would be helpful to have a column linked to current premium prices (B/A) for a call/put to have easy access to potential trades to close a position. Is that possible? Although I’ve just begun to use it, it looks like a terrific tool. Thank you!!

  26. Mark Danner says:

    Scott, if you can find a better solution my hat will certainly be off to you. Yahoo certainly could have monetized their service but, instead decided to destroy the value which was an utterly ridiculous business decision. I’ve heard some investors have figured out how to download from their brokerage. Unfortunately, I’m not one of them. Good Luck!
    Mark Danner recently posted…Cryptocurrency Investing Part 1 – My Mining ExperienceMy Profile

  27. Mayer D Liebman says:

    Scott,

    What am doing wrong? The main sheet works fine, but nothing but zeros appear on the summary sheet.

    Please let me know.

  28. Frank Simms says:

    I am really looking forward to using the Options Tracker spreadsheet. I made a practice copy and entered a $ARNC put filling out the pink columns and leaving the green blank.. Is the Tracker supposed to populate the green columns automatically? If so it is not working. I use another Google Sheet tracker on High Dividend Opportunities (HDO) service. On the HDO portfolio tracker I must hit F5 key to reload. I tried this with your tracker and nothing happens. Any ideas how I should proceed? Thanks, Frank

  29. Eran says:

    great Spread sheet! thank you very much
    can you please update me if you have also sheet for Spreads and Iron Condors?
    i tried to put the information for naked puts and calls but for some reason its doesnt work for me

    thanks Again Eran

  30. Mark Danner says:

    Eran and all,

    Once again, I’d like to say thanks again to Scott for his spreadsheet. To that end, I’d like to make available my expanded version of Scott’s spreadsheet (link below). First, a couple of caveats:

    First off, any “credit”, (perceived or otherwise), should totally go to Scott. Without his initial versions of his option spreadsheet as well as his dividend stock spreadsheet mine would not exist.

    Secondly, I’m not anywhere close to the spreadsheet guru that Scott is. So, while I believe all the formulas are accurate this sheet continues to be a work in progress. So, it’s always possible that some of the formulas need to be fine tuned.

    Next, Scott is still working for a living and I’m retired. So, I’ve had a lot of time to build the spreadsheet that I wanted. (And, I can assure you, at times I’ve spent hours upon hours trying to figure out why a single formula doesn’t work.) I’ve also received help from the Google Community for some of the harder formulas.

    Lastly, my sheet uses a script to download quotes from Yahoo. While it’s not the ideal solution it’s the only semi-reliable one I’ve found for downloading option quotes. Most, quotes are pretty accurate. But some are, unfortunately, not reliable.

    For several years I, and many others used an Excel spreadsheet developed by Mr. John Pajak who was and always will be a Master of all thing Excel. As the years went by I lost track with John and, when Yahoo closed down it’s API I was at a loss of where to turn as I typically trade / invest in several hundred options a year.

    Then, I found this website with Scott’s spreadsheets. For me, it was a massive undertaking converting everything over. As the months, and years have went by I have steadily been expanding Scott’s OptionsTracker into about 98% of what I had before Yahoo dumped us into the ditch. To that end, I have a different sheet for each type of position all incorporated into a combination of Scott’s OptionTracker as well as his dividend and stock sheet. So, Puts, Calls, Iron Condors, Bull Spreads, Bear Spreads, Synthetic Longs, Leaps and Stocks all have their respective “sheet”.

    So, as long as Scott doesn’t object, I’m going to post my link here. https://docs.google.com/spreadsheets/d/1wN40bXCkLBleKMm8Z8S4CbFiTbEi0-HKUR13QDOrzZs/edit?usp=sharing If anyone has questions about how to use the sheet I can provide my email.

  31. Mark says:

    For those that might have questions about my sheet my email is [email protected].

  32. Gino says:

    Can somebody help with data not updating? The green cells are not calculating ( those are the ones to be left blank according to the instructions). Thanks.

  33. Mark says:

    Gino,

    Can you clarify your question? Is this a continuing issue or has it resolved itself? What data provider are you using?

  34. Gino says:

    Mark, I meant to say symbols, DTE, breakeven, etc. are not updating. In other words, the cells in green.
    P.S. I haven’t yet delved into your spreadsheet which you kindly provided so this in regards o Scott’s version.Thanks.

    • Mark Danner says:

      Gino,

      I just pulled up my original copy of Scotts sheet, entered a couple of positions that would be current and the green columns updated as they should. Scott’s sheet uses Google to update prices. So, the issues you may somehow be on your end. Check the various columns. Is all the information correct? Could you have mistakingly entered the wrong data into one of the cells? (I only ask this because this describes the majority of issues that I have when I find mistakes on my sheets.) Hope this helps.

  35. Data Seller says:

    I am very happy to see this post because it really a nice post. Thanks

  36. Thiago says:

    Hi Scott, this is a great starter sheet for options traders, but if you trade lots of options, it can get time consuming to maintain. I’d add a “strategy” column to the sheet as well. For those looking for an online options trading journal, https://www.trademetria.com is one way to go.

    • Mark says:

      Each to his own but, I’ve traded hundreds of options every year for the past 10 years and have always found it to be pretty easy to keep my own spreadsheet. Besides that, it’s very doubtful that I’d be willing to pay for something I can easily do, and have done for several years. Nonetheless, I’m not afraid of trying something new. So, I signed up for trademetria.com and using the instructions on the trademetria website, tried a couple of imports both of which failed. The second one was the easiest simpliest import available but to no avail. So, my only option was manual entry. And, I’m certainly not willing to do manual entry and pay for the privilege. Just my 2 cents.

  37. Lee says:

    I am new to trading options. And i have found your spread sheet tracker to be an invaluable tool!
    Of my first dozen or so put trades, all expired or i bought to close successfully. I like that the Summary page shows the realized gains/losses. Question: how can i record when i am put the contract? Just got put for the first time. do i leave the original trade w/premium on the sheet, but track the put stock transaction on a separate commodities spread sheet? Thank you for all your time and effort!

    • Mark says:

      Lee, I’m not Scott, (obviously) but, I’m going to guess different folks do it different ways. Personally, for my 2 cents worth. I like being able to track my trades from a historical perspective as I have different sheets/tables that provide me with how much income I’m receiving on a monthly, quarterly basis. Plus, I like keeping a history of what trades are working and what aren’t. So, what I do, is the top half of my sheet is for open, or active trades and the bottom half is for closed/expired/assigned trades with the exit prices and closed dates columns filled in.

      Since the put premium reduces your cost on an assigned trade I put the closing premium price the same as the premium that I was initially paid as well as refunding the commissions. I then transfer those amounts over to my stock page so that it reflects the lower cost basis as well as the commissions. Hope this helps.

  38. Hamid says:

    Hi Scott,
    Thank you so so much , for your incredible job.
    There is one bug in program, If open and close date are the same day, then in column U , you will see The ” Divd by Zero”. Thank you for take care of

    • Ken says:

      Awesome spreadsheet. Wish I found out about this months ago as I’m going crazy adding my options trades in for the past few months.
      Is there a way to fix this “Divide by Zero” error when closing a trade in the same day?
      I’m not to good a nested If statements but I tried to add an extra one in the formula to just make the total days held = 1 when it came back as 0.
      Below cell T21 = total days held so I thought by replacing it with 1 there would be no error

      =if(isblank(A21),,(if((T21=0),(if(AND(D21=”Put”,E21=”S”),(S21/M21)/365,if(AND(D21=”Call”,E21=”S”),(S21/(100*L21*J21))/365,(if(AND(D21=”Put”,E21=”S”),(S21/M21)/T21*365,if(AND(D21=”Call”,E21=”S”),((S21/(100*L21*J21))/T21*365), (S21/(K21*L21*100)/T21*365))))))))))

  39. Eugene Huffman says:

    Great spreadsheet. Please tell me about the Monthly Option Income per Year array formula. I am not sure it is calculating correctly, but don’t know about how the ARRAYFORMULA is constructed.

  40. Scott Schaack says:

    I am new to options and currently paper trading a particular buy call strategy. Does this spreadsheet work with simple buy calls? Does it calculate current profit/loss on open calls?

  41. Ya says:

    Great spreadsheet for options. I’m gonna tinker with it a bit but I was wondering if you could add a P/L% column to trades that are closed out?

  42. Matt says:

    Great spreadsheet! One question – if you buy a call and then exercise it upon expiration, is it possible to track the profits/losses you incur when you eventually sell the underlying security? Otherwise, it looks like the call was a “loss” or a “gain” but that doesn’t really tell the whole story.

  43. I am really like to check your spreadsheet again and again. Looks like real and actual informative article for reader. Thanks for sharing your openion.
    George Papazov recently posted…How Does After-Hours Stock Trading WorkMy Profile

  44. James Anderson says:

    Hi and thanks for this awsome spreadsheet.

    I would love to have your spreads spreadsheet you mentioned earlier. Is that complete yet?
    Also my put margin cash reserve is showing a negative number, how could that be?

    Thanks

    James

  45. Manfred Maul says:

    Hello, I really like your OPTION TRACKER SPREAD SHEET. It is just what I was looking for. What do I do when I get past row #100 all fields are plain white and the system won’t do calculation?

    Thanks, Fred

  46. Ryan Malham says:

    I’m wondering if you can help me. I have been doing some options trading with the Money Press Method. I would like to create a google sheet to act as a dashboard that will give me a realtime view of the productivity of each money press trade (one sold put and one one bought put for each symbol). Each week I roll over the sold put and collect the premium for next week. The other bought put acts as my long term protection. It’s unclear to me how well each money press is doing unless I go to my trading platform and select rollover and see what the next week’s premium will be. I’ve seen some methods of getting the options pricing and importing that into Sheets, but I’m not skilled enough to know exactly what infor would be needed. Can you help?

  47. Mark Danner says:

    Howdy Ryan, just another commenter like yourself but I have expanded Scott’s spreadsheet to where it tracks a wide variety of options trades. And, while my sheet does download option quotes, the quotes can be times be unreliable unless you’re willing to pay for them through someone’s API. (And, I’m not so I won’t go down that rabbit trail as I understand it can get expensive.) But, nothing can track your trades as good as your brokers platform. For instance, Interactive Brokers as well as Tastyworks both pair your trades where you can instantly see your daily profit / loss as well as total profit / loss.

    My sheet also tracks closed out trades which is probably one of it’s greatest attributes. Don’t know anything about the “money press” trades so I’ll have to check that out. But, if you would like to email me (posted upthread) we can collaborate and I’m happy to send you a copy of my sheet for whatever it’s worth.

  48. Mayer says:

    Hi, Could you explain the math used in the Profit/Loss Column. The reason I am asking is that I had a stock that was $54.08 on DOC. The premium was $2.48 and the stock was assigned for $57.50. But the Profit /Loss Column shows a loss of $55,106.68. I made money on the Stock, Premium and have a Loss????? Please help. I have been using this sheet since 2017 and love it. Please let me know.

    • Reyam Nambeil says:

      I agree with you. When you close out the trade, either sale of assigned, the profit you made (Sale minus value on DOC) should be taken into account. This is annoying.

  49. Michael V says:

    I’ve been using the Dividend Portfolio Tracker for a while now. Getting ready to start basic options, selling covered calls and puts, and it looks like the Option Tracker will be more than sufficient for that. My question is regarding incorporating the results of options trades into the main Dividend Portfolio to best represent the cost basis offsets. If calls/puts are exercised I guess I could simply enter the transaction into Dividend Tracker using the purchase price (adjusted by premium received less fee) so the price per share cost basis of that particular stock (transaction and overall total) is properly portrayed on the Portfolio page. then future Buy/Sell formulas for Gain/Loss will take into account actual options affected cost basis. Sounds right?

    But if calls/puts aren’t exercised and there is just premiums collected the best way to enter those amounts as a transaction in Dividend Tracker practically speaking. Ultimately you’d like to to reduce Cumulative Cost so Cost Basis goes down so just treating it as a dividend wouldn’t suffice. Looking at the Transaction Tab on Dividend Portfolio Track I’m thinking that with enough trial and error I might be able to muddle through adding a “Opts” as a transaction Type then messing with columns M-O so that Cumulative Cost is reduced by receiving an options premium, which in theory would reduce Cost Basis on Portfolio page?

    • Michael V says:

      That looks awfully long winded. Ultimately I’m not talking about combining Dividend and Option tackers, just manually entering results from Option once options are closed/exercised into Dividend in a way that cost basis and overall gains/losses are reflected in Dividend Porfolio. Dont know if Scott or Mark are already doing something similar with theirs.

  50. Mark says:

    Good afternoon Michael,

    If I understand correctly you’re wanting to track both your options income as well as your dividend income. And then, of course, there are stock gains and or losses. There’s probably several ways to do so but, my sheet does all three through a “Monthly Income” page. This page breaks down the gains and losses by strategy, i.e., Calls, Puts, Leaps, Spreads etc. In a different section on the same page it adds up all of the option income so I can see how much is made by both month and year. And, it also calculates dividends by the month. But, I also have a Dividends History page which calculates dividends by stock (for current holdings) as well as all dividends by month, quarter and year. So, all of it works together in one Google Spreadsheet which has multiple pages. If that sounds like what you’re wanting then take a look at my sheet as Scott as provided a download link just shortly up thread. You may like it the way it is or, at a minimum it may give you ideas on how you can customize your sheet for yourself.

    One word of warning is sometimes, while editing, Google sheets will return a REF! error on the Monthly income page. When this occurs it changes your formulas on the page inserting the REF! into the cells which can be extremely aggravating. So, save versions frequently when editing. If something isn’t clear just ask, hope this helps.

  51. Michael V says:

    Hey Mark,

    I downloaded a copy of your sheet and will play with it some when I actually begin trading options, see how it works or what I might to use piecemeal. For now I’m pretty invested with the original Dividend Portfolio Tracker, with 40 some holdings and thousands of transactions entered.

    What I worked on this afternoon was some “tweeks” to the Transaction tab of the Dividend Portfolio Tracker. I added an “Opt” to Transaction to transaction type list and some modifications to some of the columns on that page. Say I’ve got an options premium I want to record. I select Opt from the drop down, select company name, enter premium amount, enter the brokerage fee. The formulas will post the Transaction Value as the premium minus the fee then subtract that from Previous Cost to fill in the new total in Cumulative Cost. So now the stocks Cost Basis and Cost Per Share will be reflected as this adjusted amount in relevant places in the worksheet.

    I’ve run some trials on it and it seems to work the way intended. Cost Basis and Unrealized Gain/Loss for the stock changes on the Portfolio tab, Cost Per Share for the stock changes on the Reference Data tab.

    The idea was to make it so premiums received for covered calls can be applied through the Dividend Tracker Spreadsheet to reflect a lower cost per share for the stocks. Not sure yet how to credit premiums on secured puts for stock I don’t own any of, hard to reduce the cost basis when the shares are zero.

    I’m actually pretty new to this and might have the theory completely wrong though, let alone tinkering with formulas

    • Mark says:

      Sounds good Michael. As we’ve said, there’s several different ways to calculate profits. The way I do it keeps the option income separate from stock gains. That way I get an accurate count of just how much income I’m generating from options. But, when it comes right down to it there probably is no wrong way as long as there’s no double counting. Good luck!

  52. pja says:

    Great speadsheet…………the one question i have is how to enter a put that was assigned to me….do i leave the exit price at zero or enter a loss somehow.

    • Mark says:

      pja,

      I think that’s probably personal preference. From a taxable standpoint, your cost basis for the stock is reduced by the premium you received for the assigned put. So, if you want to use that method just enter the exit price of the put as the exact price you initially received thereby documenting your profit / loss on the put as zero. Then, enter the cost basis of the stock as the strike price minus the premium received. Or, for the sale of the put you can always enter a big zero on the exit price of the put, (thereby taking the gain on the sold puts page) and your stock basis would be the strike price for the sold put. Either way, works. You just can’t take both off the same trade.

      • pja says:

        Thanks for the quick reply………….it helped …Im going to use the method where i put the exit price at zero………it works for me……..thanks again

  53. Rasik Goyal says:

    This is the best tracking sheet I have come across and using from a month. Thanks so much Scott!!

    Is it possible to provide formula for covered call return considering the gains on underline and premium received. Also add a cell for the protection provided for in the money covered call.

    Another suggestion is to have a separate summary tab by stock symbol so that one can see which stocks are consistent performing and which ones aren’t.
    Is it possible to pull the current price of an option contract similar to the stock price for an open position?

    These suggestions are to take it further but I am as such very thankful for the sheet and find it useful.

  54. paul alaimo says:

    HI AGAIN ……..GREAT SPREADSHEET………JUST DID MY FIRST VERTICAL SPREAD…..ENTERED STO ON ONE LINE AND BTO ON THE NEXT LINE…..IS THIS HOW TO ENTER A VERTICAL SPREAD….I’VE NEVER SOLD A PUT AND BOUGHT A PUT AT THE SAME TIME……………..THANKS PAUL

  55. Ohad Osterreicher says:

    Hey,
    Thank you so much for this spreadsheet! I just got into option selling and this file makes my life so much easier. Thank you!

  56. Jonathan Karas says:

    Thank you for this spreadsheet. I’m new to options and have been going bananas trying to find something adequate, yet not overwhelming, to record my options trades. Thank you! P.S. Is the “Summary” page working correctly?

    • Brandon says:

      Make sure you change the years on the summary page. Also, make sure all of the required dates are filled in on the Profit/Loss page. Also, Change the name of your account on the Summary page using the same account name you used on the Profit/Loss page. That should cover it! GL!

      • . Jonathan Karas says:

        Great! Thank you!

        I noticed that on the profit/loss page, there is a column for P/L$ but not for P/L%. Would be wonderful if we could see a percentage return on each trade. Thanks again.

        • Brandon says:

          There is. It’s the Annualized ROR for Options column. However, the way it was setup didn’t fit my needs. I modified it as follows:

          =((Q86-K86)/K86)*100

          Worked much better.

          • Jonathan Karas says:

            More good news! So can I just delete the existing formula and replace it with yours? I don’t know if matters, but I plan to take the gains and losses on my stock buys and my options separately. In other words, I won’t just zero out the gain on the option. I’m a complete amateur with options. Thanks for your help. I made a small donation through PayPal.

  57. Brandon says:

    Really appreciate this spreadsheet! I’m wondering if there’s a way to include deposited funds to gain a better overall profit/loss?

  58. Adam Gorgoni says:

    This spreadsheet is great and I am enjoying it. Just started using it in 2021. I’m not much of a database person and I have one question: I can’t seem to figure out how to get the summary page to reflect trades in 2021. Could anyone explain how to accomplish that? Thanks so much.

  59. Alex says:

    Fantastic blog! Do you have any tips for aspiring writers?
    I’m hoping to start my own blog soon but I’m a little lost on everything.
    Would you suggest starting with a free platform like
    Wordpress or go for a paid option? There are so many options out there that I’m completely overwhelmed ..
    Any suggestions? Kudos!
    Alex recently posted…AlexMy Profile

  60. Dave Loomis says:

    Scott, thanks for the spread sheet. I love the options page, but the data is not appearing on the summary page. I added my brokerage names (Fidelity and Schwab) by the lines…wondering if I wiped out a formula? Thanks again…even if I can just get the main call/put page working it is still a keeper:-)

    • Dave Loomis says:

      I figured it out..of course. You can only input to Brokers into the summary page, and I wrote fidelity below the 2 in the template. Thanks again.

  61. Mike says:

    Thank you for the great spreadsheet!! But if you still have it or are working on it I too would like either a separate column for gains/losses on the stock or have it tied into the current one somehow. Thank you and please let me know if you are still able to take donations.

  62. Mark says:

    Thanks for your sheet. Just one bug, if i hold for 0 days, the “Annualized ROR for Options” & “Margin Annualized ROR” through an error.

  63. krishna says:

    Thanks for sharing the valuable information, this will be very much helpful
    krishna recently posted…How To Trade Fibonacci Trading Strategy With Massive Profit?My Profile

  64. Matt says:

    Hi Scott, 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.

  65. Very helpful. Thanks for sharing. To know more about trading visit OptionsXtra website.

  66. Joe says:

    Thanks for sharing this spreadsheet! Can someone post instructions on how to expand the number of entries? I’d like to increase the limit as I’ve already hit the limit of 89 trades.

  67. Lee says:

    What a great tracker! Thanks for setting it up. I’ve been using for a year (started when I opened my first trading account) and I love it

  68. Rose says:

    Best tracker I found after searching everywhere! I’ll be donating via PayPal. Thank you for sharing this! I had no problems until I tried to sort by open/closed when updating my trades then re-sorted the dated entered column and then for the “Monthly Income Per Year” and Annual Option Income” boxes i get the #VALUE! error. How can I fix? It also somehow moved my header row down separating my Closed from Open on that sort. Which is actually fine but just weird it did that. Is there a particular way to sort other than A-Z?

  69. Dennis D Weaver says:

    The Dates stop working because of year 2022. Microsoft strikes again

  70. Sophia David says:

    Great Post. Thank you for sharing. If you are searching for options trading services you can visit OptionsXtra website.

    https://optionsxtra.com/

  71. Mike says:

    The spreadsheet to track options looks great especially when it could be deplored for selling several calls and puts.
    Mike recently posted…Ways On How To Build A Good Mutual Fund PortfolioMy Profile

  72. Hansen James says:

    This is so awesome, Scott. Thanks so much for taking the time out to pull this tracker together. This will be very much helpful.
    Hansen James recently posted…How to Reduce Risk in Your Investment PortfolioMy Profile

  73. Mike Peter says:

    Big thumbs up for making the effort in putting up this tracker. It also comes with great clarity. You did a great job.
    Mike Peter recently posted…The Strategic Ways To Reduce Portfolio Risk When InvestingMy Profile

  74. Anar says:

    Hi, I was just searching if it is possible to track simple BUY Call and Put options, because P/L is not calculating BUY options. Maybe someone has right formula for this. Thanks in Advance.

    • Mark says:

      Anar

      Yes, it’s certainly possible to the profit / loss of purchased options. The Google Sheets formula I use is =IF(J2,IF(C2<0,O2+U2,O2-N2),) where
      J2 = Option price,
      C2 = Number of Contracts
      O2 = Current Market Value
      U2 = Commissions / Fees
      N2 = Risk Capital

  75. Emi says:

    Awesome spreadsheet!

    Quick question: how do you track taking partial profits on a position? I like selling positions early to capture profits and protect the principal, before expiration. Curious how you track that.

    Thanks,
    Emi

  76. rabi verma says:

    The Options strategy builder represents the payoff chart for different options spreads. Every newcomer will comprehend how to begin using selections quite well. I really like this blog post. keep posting such kind of important posts. Also you can try intradayscreener website Options strategy builder which represents the payoff chart for different options spreads.

  77. joe says:

    How do you hand a roll in the spreadsheet?

  78. Joe says:

    How do I update the years on the sheet? They are not calculating years not on the sheet

  79. Richard says:

    Stock Price DOC – you can use a formula to calculate – Google Finance can look up the stock price at a certain date
    =INDEX(GOOGLEFINANCE(A2,”price”,B2),2,2)

    • Joe says:

      I hate filling out the field manually so thanks for the tip. It’s not the exact price at time of the trade but it should be close enough and is better than nothing. However I’m getting a formula error. GoogleFinance doesn’t seem to like the format of the date argument, B2 in your example. As a sanity check, I set the B column to Format>Number>Date and tried again but still failed. Any ideas?

      • Joe says:

        UPDATE: Please ignore my reply above. The problem was I copy and pasted your formula into my spreadsheet but the double quotes around price from the copied test were different than the ones on my Windows 10 system, I deleted them and typed in the quotes in and it works now!

        As a test, here’s the copied quote: ”price” (DOESN’T WORK). Here’s when I type it out “price”(WORKS). The copied quotes looks different than quotes I typed into in this site’s text box as the quotes slant to the upper right-hand side where as my typed quotes are vertical. If both look the same to you, this website formatting must be the one changing it.

        • Joe says:

          Okay my test confirms that this website is changing the working double quotes in the non-working version. Make sure you delete the quotes and type them in if you get an error like I did.

          • Joe says:

            I updated the formula to check if the stock symbol is blank to avoid NA symbols (remember to delete the double quotes and type them out, also replace the 63 in A63 and B63 with your current row number):
            =if(isblank(A63),,(INDEX(GOOGLEFINANCE(A63,”price”,B63),2,2)))

            To save you guys time as it took me awhile to figure this out as I’m not familiar with working on spreadsheets, once you see the Stock Price DOC formula works, to copy the formula down the rest of that column, click on the cell with the working formula you just added, cell F63 in my example, then press and hold CONTROL+SHIFT+DOWN until you see all the cells of that column are selected and release the keys, then press CONTROL+D to copy the formula to the rest of the cells, Google Sheets will correctly modify the formula for each number.

  1. November 14, 2018

    […] Options Tracker Spreadsheet […]

  2. February 25, 2020

    […] Download Image More @ https://www.twoinvesting.com […]

  3. March 2, 2020

    […] Download Image More @ https://www.twoinvesting.com […]

  4. July 29, 2020

    […] Created at https://www.twoinvesting.com […]

  5. October 6, 2021

    […] this blogger who created the spreadsheet you see in my screenshot – check out the link to his options tracker […]

  6. May 7, 2022

    […] Option Tracker Spreadsheet– Scott at Options Tracker Spreadsheet – Two Investing […]

Leave a Reply

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

CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.