Most of the popular brokers (such as TD Ameritrade and Robinhood) don’t make it easy to track your covered calls. Popular metrics for covered call traders such as cost basis reduction and daily theta are not available through most brokers. And to make it worse, each time you roll a call option or sell a new one, the broker considers it a new position, instead of a modification of your existing covered call position. Most covered call sellers use a spreadsheet to keep track of all their trades. And with Market Data’s Google Sheets Add-On, it’s easier than ever to track covered calls in a spreadsheet.
See A Covered Call Tracker In Action
The Market Data Add-On will let you see the value of any covered call position, whether its active or historical. This means you can track all your current covered calls, previous covered call trades, or even backtest a covered call idea to see if it’s a valid strategy.
Covered Call Spreadsheet Formula Cheat Sheet
Use this cheat sheet of formulas to quickly build out your own covered call spreadsheet. These formulas are all prepared with real-life covered call examples for Apple, so you can try this yourself in Google Sheets after installing the Add-On. Just replace the option symbol with your own and you’ll be ready to track your calls.
Real Time Quotes
- Real-Time Call Quote
=OPTIONDATA("AAPL240621C00200000")
- Real-Time Level 1 Call Quote With Complete Bid/Ask Data
=OPTIONDATA("AAPL240621C00200000","all")
- Current Value Of An Open Covered Call Position (100 Shares In AAPL – Closing Value Of Your Call)
=100*STOCKDATA("AAPL")-OPTIONDATA("AAPL240621C00200000")
Historical Quotes
For the historical quotes, just change the dates to the dates that you need. The first date is the starting date and the second date is the end date. When there is just a single date, that will give you the quote for a single specific day in the past.
- Historical Call Quote From A Single Day
=OPTIONDATA("AAPL240621C00200000","price","5/25/2022")
- Historical Call Quote From Multiple Days
=OPTIONDATA("AAPL240621C00200000","date,price","5/01/2022", "5/31/2022")
- Historical Full Level 1 Call Quote With Complete Bid/Ask Data Over Multiple Dates
=OPTIONDATA("AAPL240621C00200000","all","5/01/2022", "5/31/2022")
- Historical Value Of A Covered Call Position (100 Shares In AAPL – Closing Value Of Your Call)
=100*STOCKDATA("AAPL","close","5/05/2022")-OPTIONDATA("AAPL240621C00200000","price","5/05/2022")
