Stock trade tracker spreadsheet
It currently works for selling covered calls, selling cash secured puts, and selling naked puts. This is what it looks like: As with my dividend tracker spreadsheet , the orange cells are ones that you manually edit and the green cells are calculated automatically. 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.
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.
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. Premium Premium is the money collected for selling a put or call.
It is also what you pay if buying a put or call.
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 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.
If your broker has different requirements then this formula can be updated to reflect that. It can be used to calculate the annualized rate of return column U.
Trading Journal Spreadsheet | Trade Tracking Software for Traders
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.
Any stock transactions can be performed on a separate spreadsheet. This can changed by editing the formula in column U, if desired. 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 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 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. 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.
My Trading Journal (Excel Spreadsheet) — ditycapylal.web.fc2.com
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. In doing things this way, the entire trade is complete on a single row. And, as similar to my dividend tracker, it also shows a table displaying monthly option income each year.
Preview and use the Options Tracker spreadsheet. Please let me know what you think! This is version 1. Any other features you would like to see? Thank you Scott for taking the time to pull this tracker together. 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. 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. Would you like it added there too?
I am a fan of your dividend tracker and happy to see an options tracker as well. I am now a DGI, selling Puts to collect incomes while I fill out my portfolio.
Free Stock Portfolio Tracking Spreadsheet
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. 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. Sorry to hear about the loss in your IRA, but great job using options to get that amount back up. Let me know how you like it. It does combine both options and dividends into one spreadsheet.
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? 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! 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?
The latest versions of all my investing spreadsheets are available online here. 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. 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. 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! Already love and use your Dividend spreadsheet…now found this today.
Is this still work in progress? 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.
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. 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?
I am definitely open to creating additional versions. I was going to work on iron condors next. Thanks for your comment. Google discontinued their Template gallery and have not yet migrated the user created templates yet. It can be accessed directly here. Love your Options Tracker Spreadsheet.
How can I get a copy of this. Do you sell a program?
Bulkowski's Trading Spreadsheet
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.
Let me know if you have any more questions. Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email. Enter your email address to subscribe to this blog and receive notifications of new posts by email. Opening an Educational Plan. Dividend Stock Portfolio Tracker with Transactions Page.
International Dividend Portfolio Tracker with Transactions Page. Theme by Press Customizr. Open Date Open Date is the date that the option contract was opened.
Strike Price The strike price of the option. Calculating Covered Call Profits — Not As Easy As It Sounds Fees Enter any fees associated with the trade. Preview and use the Options Tracker spreadsheet Please let me know what you think! October 10, at October 10, at 8: October 28, at October 28, at 1: November 23, at 6: November 23, at 1: November 25, at 2: November 30, at December 2, at 5: December 15, at 1: January 17, at 3: February 1, at February 23, at 2: February 18, at 9: February 18, at 4: March 18, at March 19, at March 21, at March 23, at 3: May 19, at May 19, at 3: Leave a Reply Cancel reply Your email address will not be published.
Subscribe to Blog via Email Enter your email address to subscribe to this blog and receive notifications of new posts by email. The Road to One Million. The Dividend Guy Blog. Recent Posts Popular Posts Recent Comments Tags.
Investing 5 financial metrics you should ignore when investing 6 May, Investing Opening an Educational Plan 24 Apr, Investing Dividend Stock Portfolio Tracker with Transactions Page 4 Aug, Investing International Dividend Portfolio Tracker with Transactions Page 10 Nov, Investing Robinhood or Loyal3: Investing Automated Dividend Calendar 4 Feb, May Dividend Income from YOU the Bloggers!
Troy Market History says:. I think Warren Buffett recently sold his IBM after holding on Woah - thanks for the knowledge there. May Investments from the Community Dividend Cashflow says:.