Это видео недоступно.
Сожалеем об этом.
Best Spreadsheet for Tracking Options Trades (NEW VERSION) | Free Download
HTML-код
- Опубликовано: 12 авг 2024
- Just in time for the new year, I have a new way for you to track your options trades! Version 2 of my Ultimate Options Tracking Spreadsheet is greatly improved. I've implemented feedback from viewers and you can now use the spreadsheet to track your option selling AND option buying. So, take a tour and download the ultimate options tracking spreadsheet using the link below. You can use this tracker for all kinds of options trading strategies, including the wheel strategy. You can also track dividends, stocks, interest, and more.
🔗 Links:
Here's a link to the spreadsheet. Be sure to SAVE A COPY to edit it.
The spreadsheet is free, but if you use it, consider buying a "Super Thanks," which directly supports my efforts. The Super Thanks button is located under the video. Thanks for your support!
Google Sheets: docs.google.com/spreadsheets/...
Excel: docs.google.com/spreadsheets/...
Original Options Tracking Spreadsheet Video (v1): • Best Spreadsheet for T...
📌Timestamps:
0:00 Start Here + Intro
01:53 Tour and Features
12:38 Tracking Assignments
15:53 Tracking Rolls
18:15 Customization Tips & Tricks
20:22 How to Download + Outro
#optionstrading #optiontrading #wheelstrategy
⚠️Disclaimer:
The content in this video is not financial advice. It is for informational and entertainment purposes only.
![How To Track Your Option Trades [Episode 46]](http://i.ytimg.com/vi/dEEs2VhRNHM/mqdefault.jpg)
This spreadsheet is available for free. There is no need to request access. When you open the spreadsheet, it will be in View Only mode. Please select File > Make a Copy to edit and use the spreadsheet for your own trades. Thanks!
This is awesome my guy! Thank you for sharing.
Thanks! This spreadsheet will greatly assist me in my new found pastime! (I am already seeing the benefits of observing the better outcomes.)
I’ve been using v.1 of the spreadsheet and it has been the best one out of a ton of other ones I tried. Im really excited to start using V2. Thanks so much for your generosity! This sheet is a gem!
That's so cool to hear. I hope you like this version too!
This is exactly what I needed! Thanks!
Thank you for this spreadsheet. I have been using a paid one that has so many bells and whistles including graphs and charts, that at the end of the day I find it too glitchy and complicated for my needs. Your's covers everything I do, including the wheel. I think it wil be exactly perfect for me. Thanks again.
Awesome spreadsheet thanks
good stuff. Love to see a way to track your cost basis too.
Thanks!
Thank you so much! Just started options trading and I'm starting to lose track. I will use your spreadsheet.
I just found you! Thank you for your content and the spreadsheet!
You’re welcome. Thanks for watching!
Thanks ... I like it better than V-1
Thanks
Awesome
Thank you for this wonderful spreadsheet. Feedback for Version 3, perhaps a way to to have beginning balances. I have a couple of trading accounts to deal with the PDT rule, so it would be helpful if this sheet would calculate my P/L as a whole as my trading accts have different balances.
Nice work on the sheet. Can we add a better to track rolls? The current solution jams it in the original trade and the overall fidelity is lost if you rolled multiple times.
Wondering if simply closing the prior trade and opening a new one (on new row) with the credit difference would be sufficient?
It would be quite useful to include a column denoting the strategy, ie, iron condor, calendar, pcs etc.
Any help you could give for my question a week ago about how to handle the NVIDIA split would be so appreciated! 🙏
Very helpful video and resource. How did you get the oval colored dropdown buttons in the “type” column list?
If you’re using Google sheets there will be colored oval when you select one of the types. If you’re using Excel, the whole cell gets colored in, and there are no ovals.
Hi im doing writing covered calls is there any chance writing in the break even calculations column, as on few occasions i held a one stock for5 months as i did not get excercised, just keep renting them out until it reached the above call price.
Sorry im new to this.
I been doing Fokas beyond course.
Thx Owen
Super nice spreadsheet.
I’ve created a similar one according to my needs.
I would implement a couple of useful adjustments (ides I got from this video).
The data (I’m interested in) that is missing in your report is the amount of money (by currency) I have in my account (used for matching purposes) 😊
Ps. what about the collateral (and p&l) using a margin account?
Thanks! In response to your question, if you're using margin you could enter the buying power used for the trade in the collateral column.
May I download to Libre Office? Thank you
Great spreadsheet and thanks. I do have a question, when you rolled in example, how did you account for the closing cost? Thanks again for your dynamic sheet.
If you mean the fee to close the trade, I dont have that fee at my brokerage. If I did, I would account for it in the fee column. Otherwise, the buy to close price is in the close price column.
I should better explain my question, how do you account for the cost of the BTC portion of a roll as you go? For instance, I have rolled a Call 3 times now at a total cost of $10. If I include this in the close price column before the actual closing it will complete the P/L column. At the moment I am adding costs in the Notes section to add together at actual closing time. I may just not understand a simple concept, I am learning and really appreciate your work. Thanks again for this awesome spreadsheet.
Hello! Great Spreadsheet. I do need help though. My P/L column is not showing me the profit on a put I sold then bought back to close. It is figuring the profit on the collateral column amount. It is showing an amount I do not understand where it came from. Can you help?
How do we move our current cost basis and shares etc from a spreadsheet that we are currently using to this without starting over?
Not sure why after roll 12 column H want to count as money and not quantity.
Great stuffs, would you mind adding scale out features? Thanks
Interesting idea. Not sure how I would implement it... but I'll keep it in mind for a v3.
A workaround for now could probably just be doing multiple lines for the contracts that you plan on closing out early
HOW DO U SAVE THE SHEET WHEN YOU ARE FINISED WORKING
Thanks for this. It's very helpful. Is there a way to split up transactions? I buy multiple contracts but don't sell all at once. Can I split them up somehow?
Just manually. You could create a new row and put those contracts you sell at different times on a different row. Have not figured out a better way than that yet.
wHERE IS THE PERFORMANCE TAB ON VERSION 2
Hi - I don't know if you are currently tracking the comments. I wanted to know if it would be possible to add Adjusted Cost Basis for stocks based on Covered Calls written against them. As you keep writing covered calls, your cost basis for the stock goes down. I have many stocks that I am writing covered calls against. It's very difficult to keep track over a long period of time. This would be an excellent option if possible. I expect that this would be difficult to implement, but was just wondering.
Very good. But I a wondering if it can be made so that the latest entry is at the top rather than on the bottom?
Thank you.
Thanks. You should be able sort by the open date or closed date so that your most recent trades are on the top. I just made a slight change to the sheet in case the sorting feature wasn't working. So you might want to to download it and make a copy again.
My P/L cell does not calculate properly. Can you help please
Does your Sheet support diagonal or calendar spreads? If so, how do you handle the different expiration dates? Thanks for a great program.
Yes, I would put each leg on a separate row with its own expiration date. In those cases, it's important to make good use of the notes column to remember which option is tied with which other option.
Great job. How are commissions accounted for?
I would include commissions in the Fees column.
Just started using your tracker. I like it. What is COLL column? You didn't go over that one.
Actually he did .. it’s the Collateral used to purchase the option/stock … for example:if you buy to open a cash Secured Put you tie up $xx dollars to make the trade .. that amount goes into the Collateral field. It is the Denominator in the fraction that computes the ROI.
@@robertelia2824 For me, I am just trading naked calls/puts, does it work for me to put in how much I paid for the contract? I don't understand the tieing up x amount of dollars.
how do i get this into excel?
Were the free download
How would you handle the 10-for-1 split of NVDA? I had three purchases previous to the split; 6+50+54 for a total of 110 shares. On 06-11-24 there were 990 shares added to my account and the stock was valued at 1/10 the previous per share amount. Should I go back and adjust the previous stock purchases? How will this affect my records when I eventually sell the stock? Thanks for any help! (BTW, Your spreadsheet is amazing!)
Yes, if it were me, I would go back and adjust each of the previous stock purchases in each row where there was a purchase. You should only need to change the cells in columns G, H, and I.
For the cells in columns G and I, divide the purchase price by 10. For example, if you bought 6 shares of NVDA at $1,000, change it to $100 in columns G and I.
For the cells column H, multiple the quantity of each purchase by 10. If you bought 6 shares, change it to 60 shares. The collateral and everything else will stay the same. I would also make a note in each of those rows that you made the change due to the 1/10 stock split.
Any ideas on what I would need to do to track when a rolled trade is profitable enough to close early? Formula suggestion?
Include the premium received from opening the trade along with all premium received from rolls in the Fill column. Then when you close the trade early, type the price you paid to buy to close in the Close column. The P/L should calculate correctly.
I think I said this wrong. I get that the accumulated Fill is the premium I have earned. I also have added a column to see the live stock price. I assume that I have to compare the accumulated fill with the current mark to see if this trade is profitable or not. Is there a way to do this live on the sheet? Thanks@@dukeofearlyretirement
Question regarding the P/L calculation: I notice that the formula takes the fee into consideration, but seems to only take the STO fee and not the BTC fee. So is the best way to handle this by simply doubling the fee once the trade closes? And will that work when Qty is more than 1?
That's true. There's just the one cell for the entire fee. So you would have to double the fee if the brokerage charges the same fee to open and close the trade. Whatever you put in the fee column gets multiplied by the number of contracts (if you select buy, sell, or assigned in the dropdown). So, the P/L subtracts the fee times the quantity of contracts.
Hi Buddy, thx a lot for the awesome spreadsheet, and sharing to other is even more awesome, I've sent requests several time, just a very kind reminder, in case you might missed those, would be very appreciate if you could approve the request, a lot thx!
When you open the the link it's in View Only mode. Select File > Make a Copy so that you can edit and use the sheet. Thanks!
Hi - This is a great spreadsheet. I wanted to ask how do I fill it out to properly handle covered calls so that the profit is handled properly? BTW, I did give a super thanks.
The math is not right in this covered call. The fee should just be subtracted after getting the value of profit or loss. If you see in this example where the fee is quite large, it's clear the result is not correct: Sell 3/18/24 3/22/24 3/22/24 SOUN Covered Call $10.50 10 $0.30 $0.00 $6.57 $293 $234.20 4 $58.55 79.93% 7293.77% 6828
The percentages look off for the "SELL" although now the profit/loss is correct with the above change. Please let me know if you spot anything. Thanks
Is it possible for you to add a computation of all of the options that are open? Meaning the options where the closed column is blank. It would be nice to be able to determine what percentage of my portfolio I have exposed to risk at any given time.
The sheet is set up so that if you're trading options, the fee is multiplied by the quantity of contracts. If you sold 10 contracts and the fee was $1.00 each, you would enter $1.00 in the fee column, and the P/L would be reduced by $10. If your brokerage is charging an odd amount, and the fee for 10 contracts turns out to be $6.57 total, then you would need to divide that by 10 and enter .657 in the fee column for it to calculate correctly.
I would suggest using the filter feature on the close date column to only show blank cells and then select all of the collateral column to get the total collateral for those open options.
Duke....good content as always but I found a glitch and maybe you can help me fix the formula. I did change the ASS to "assigned'. The problem is when I attempt to close out that row, the formula (P/L for column M) is predicated on actually seeing "ass" and not "assigned"....so it doesn't calculate. I changed column A back to "ass" and we are in biz.
Thanks for letting me know! I think I've fixed this now so that you can use ass or assigned and it will calculate. If you don't want to download the updated sheet again from the link in the video description, here is the new formula for M2 (you would also have to copy it down through all of column M):
=IF(J2="","",IF(A2="SELL",(I2-J2-K2/100)*H2*100, IF(A2="BUY",(J2-I2-K2/100)*H2*100, IF(A2="ASS",(J2-I2-K2/100)*H2*100,IF(A2="ASSIGNED",(J2-I2-K2/100)*H2*100, IF(A2="SDI",((J2-I2)*H2)-(K2), IF(A2="","")))))))
@@dukeofearlyretirement thank you! Formula inserted!
How do I properly add the information of combined options, like a put credit spread? whats the best way to add the cost of buy and sell of the diffeereent target pricee
when I add a PCS I am $5 away from the loss
Never mind. I found it.
is there a way to track the profit and loss from assignment of PUT and later on re-assigned via a CALL?
If I'm understanding your question correctly, there is a way. You can have a row for selling the PUT, then a second row for being Assigned, then a third row for selling the CALL agains those assigned shares. When the CALL is exercised (or expires in the money), and your shares are called away, type the price at which the shares were called away in the Close price cell of the Assigned row.
One more question, how do I enter multi-leg options? I trade credit spreads and Iron condors mostly.
Enter the strike price of each leg in the strikes (K’s) column - it will just be there for your reference and won’t affect any calculations. Your open price is still the credit received for the spread. And the collateral is the width of the spread.
Duke. I got assigned 100 shares of stock from 1 Put. When I entered as you showed in your video I got in the qty column a $1.00 auto entry instead of a non currency 1. You show an assigned contact in your video directly under the sold put that resulted in the assignment. Is that a reason for the discrepancy… also.. one would have to inset a row under the Put because several trades follow the Put entered in the sheet before the assignment. Is this something I did or a bug. How to fix if a bug. Thx
Also ..- how to summarize the win/loss column..
There was an error in the formatting of the qty column. I've fixed it in the latest version, if you want to made a new copy of the spreadsheet. Or you can fix it on your own spreadsheet by click at the top of that column, on "H" and then go to Format > Number > Automatic. It should change the formatting for that entire column so that it will be a plain number instead of currency.
To summarize the win loss (P/L) column, click at the top of the column on letter "M" and you'll see the sum appear at the bottom in green.
everyone, can you tell me how to track if I wanting to sell portion of my options and where to put them? and how to collate and track from the original options. Say i have 200 contract and wanting to sell 25.
In this situation, I would suggest splitting them out onto separate rows based on the date that you sell to close them. So if you buy 200 contracts all at once, you would put that in one row. Then when you sell 25 of them, insert a new row under the original row. Put the 25 in that row and then change quantity in the original row to 175. It's more of a manual process, unfortunately, but that way everything will be accounted for and calculate correctly.
@@dukeofearlyretirement Thanks much bud appreciated always
Looks ok I guess. Downloaded an Excel version to use but it lost most of the color formatting. Not real sure why. Also lost ability to edit 1st column list in the column - have to go to data validation to edit the list.
Thanks for trying it and reporting the Excel version issues. The .xlsx version seems to work in Google Sheets, but some of the formatting looks different. I haven't been able to try it in actual Excel. Maybe I can get my hands on Excel and test it out.
@@dukeofearlyretirement I use computer based excel 364. Looks like some things get lost in translation.
TYPO - Excel 365
I like the spreadsheet. I've been messing around with it and added a couple of columns with some formatting for DTE, account (I have a couple of accounts and like knowing which one the trade happened it), ACB (for cost basis) and Source (to track what screener I used in TOS). It works well in Google docs but not so much as stand alone. From my experience with Google Docs/Sheets, it's NOT your spreadsheet but it is the way that Google handles things. @@dukeofearlyretirement
Help me to understand couple details.
1. Strike price column has a header K(s). Does it mean something? Planning to rename it for "Strike".
2. I cannot understand how to implement that scenario.
Sold to Open CSP expiring 2/02/2024. The price is going up, and I want to Buy to Close this option earlier. Do I have to create another row with Buy? Or write the price of BTC in Fill column? Or both.
Help, please
1. Yes, K(s) means strike(s), like in baseball. 2. You do this all on in one row. Select Sell in the Type column. The premium you received to open the trade goes in the Fill column. The price you paid to close the trade goes in the Close column.
Thank you, Duke.
@@dukeofearlyretirementHave a problem. Trying to filter by ticker name, or event and it isn't working. Actually non of the filters are working. Sample is working like charm. Am I the only one with such problem? Help, please.
Update. Disregard. I just right clicked, removed filter, and added filter. Leaving my message so maybe it will help someone in future.
@@alexmarkev Thanks, I had a similar problem where the filter was only seeing the first two lines. Your solution worked a treat. Well done. 😄
Just wondering, why is there a gap between gte open and close, as well as a P/L value, for the stock purchase?
I'm not sure I understand. What do you mean by gap? Do you mean the expiration column between the open date and close date columns?
@@dukeofearlyretirementNo, I mean why is the Open and Close date different for the buying of shares; wouldn't that occur in an instant?
(Your spreadsheet shows the QQQ Stock open on 1/2 and close on 2/28).
Oh, I see. For stock, I'm using the open date as the date you bought the shares and the close date as the date when the shares were sold. That way, once you put in a close date, it will calculate your profit per day and return on capital. I guess Close date is options terminology, but it's like when did you open the trade and when did you close the trade.
@@dukeofearlyretirement Ah, gotcha. That makes sense. Thanks
How does this tracker work with Covered Strangles?
I haven’t done this, because I don’t trade covered strangles myself. But I think it would work best with the shares accounted for in one row and then the strangle part on another row right below it.
Let's say I buy 4 calls at once, then tomorrow I sell 1, the next day another one and then the next day sell 2, and now I'm flat. Can your spreadsheet keep track of all of that?
Then let's say you buy one lot today, and one the next day, and you buy another one the next day. Then, one week later you sell all three at once to go flat. Can you spreadsheet sort that all out as well?
Thanks
In the first example, I would suggest creating a new row on the spreadsheet when you sell 2 of the 4 calls. And change the quantity in the original row from 4 to 2. Basically, each date that you close the trade needs to be on its own row to calculate correctly.
In the second example, I would suggest creating a new row for each lot that you buy because they're going to have different dates and prices. When you sell all at once, you would enter the price and date you sell into each row so that each row will calculate and show as closed out.
Thank you
I downloaded but it did not download to be able to hit file
Try File -> Make a copy
Duke .. When I download the file it is read only. How to activate in Google Sheets. Thanks
You have to go to File - Make a Copy
@@dukeofearlyretirement If I want to use this in Excel on my PC do I need the latest Ver of Excel? Thx
I don't think so... But I don't have Excel to test with. I just use Google Sheets.
Duke. I got assigned 100 shares of stock from 1 Put. When I entered as you showed in your video I got in the qty column a $1.00 auto entry instead of a non currency 1. You show an assigned contact in your video directly under the sold put that resulted in the assignment. Is that a reason for the discrepancy… also.. one would have inset a row under the Put because several trades follow the Put entered in the sheet before the assignment. Is this something I did or a bug. How to fix if a bug. Thx
Also .. How do you get a summary of the Profit/Loss column..Again Thx
Yall making this shit too easy
Disregard my last question. I jumped the gun and had not watched the full video.
Two complaints: Can not find how to put in the year in the date column. No running total column.
The date columns are formatted to only show the month and year. If you click on the cell, you'll see the full date with the year in the formula bar. There's not a running total column (interesting idea though), but if you click on the P/L header row (M), you should see the total shown in the bottom right corner of the spreadsheet. Thanks for checking out the spreadsheet.
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Thanks!
Woohoo! My first Super Thanks. I need to get this one framed :) Thanks for your support!