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:

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.

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.

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

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.

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.

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

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.

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!





You may also like...

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


  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?


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


  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.


  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.



  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.


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


  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.


  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.


  10. Buddy says:

    Where is the file? I cannot find it.


    • 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”


  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.


  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.


      • michael says:

        is there an answer to how to do this?

        • Mark says:


          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.

  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:


      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.


  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:


    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:


    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:


      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

  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?



  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

  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 […]

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.