How to Import Options Data to Excel and Google Sheets (Including Templates)
Last updated May 6, 2023(Originally published on May 7, 2022)
By Leav Graves
Table of Contents
Most traders have spreadsheets they enter data in. This helps them analyze data and improve their workflow. However, importing data to the spreadsheet is a chore, and it wastes time. This problem intensifies when dealing with options as there are many options for each stock, and they expire and change all the time. In this post, you'll learn how to import options data into Excel and Google Sheets to create formulas, analyze, and improve your workflow faster.
This article will cover:
- Importing options data to Google Sheets and Excel
- Why & What you can do with it
- 'Floating' options integration.
- How to use this template - Excel
- How to use this template - Google Sheets
Why use Excel/Sheets, and What can you do with it?
Importing options data to Excel and Google Sheets automatically is a great way to save time and keep your spreadsheets up to date. It's especially useful if you're trading multiple symbols with different systems, and it helps you keep your unique edge in the market. Excel offers a lot of customization, so even if there are no tools available to help you do exactly what you are trying to do - you can often build it in Excel. With Options Samruai automation - you'll be able to automate your process.
Importing options data to Google Sheets and Excel
You can use the Option Samurai plugin to import options data directly to Google Sheets. We support Excel Desktop, Excel Web (office 365), and Google Sheets. You can download the plugin from the Google sheets store, and we have detailed instructions on how to install it on Excel here. The plugin concept is very simple: We have two main commands: One to import Option data and the other to import stock data. After calling the function, specify what data to import and what instrument. Both can be explicit (i.e., "AAPL" or "name") or can refer to another cell (i.e., B2). This allows you to build tools that you can use for different stocks by simply changing the ticker (see below for some examples).See here the detailed article about the Excel and Google Sheet integration and go to Option Samurai Settings to get your API key (you need to have an account and be logged in - we have a free trial)
What can you build with the options data in Excel and Google Sheets?
You can build almost anything you can imagine with the plugin. We have 100s of data points and over 1m options that you can import and use to find what you are looking for. Here are some examples I'm using it for:
Stocks + Options Watchlist
You can build a watchlist that integrates both stock data and options data to track your portfolio or opportunities in the market. We've written an article about Covered Calls on Dividend Aristocrats stocks that you can check as an example. The benefit here is that data is updated automatically, so you build it once and use it all the time.
Stock Valuation
I'm using a very simple and quick stock valuation model that helps me get an idea of what would be a stock's fair price. I can then compare that fair price to the market price and trade accordingly. I use this often after earnings or other major news events to help me understand how I should adjust my thesis. I've presented this Excel in a Webinar. Here is the clip: [embed]https://www.youtube.com/watch?v=w7LdjLDrn1Q[/embed]
Optimizing Option strike and expiration to sell
Option Samurai is the best tool to use to find the optimal option to sell, according to my criteria. However, I often find myself checking and adjusting positions, so I've built a Google Sheet template that imports several options chains and allows me to compare different metrics to verify what is the best option to sell (let me know if you want to see it, and I'll share it).
Compare stocks with Custom formulas.
I've built a watchlist with custom formulas that compare my tracked stocks with formulas that change according to the market mechanic. For example, I've created a 'profitability score' that I give each stock and multiplied it by the analyst target. This makes a unique metric where stocks that are both very profitable and analysts are very bullish get a high rank, and I check them first. Conversely, less profitable stocks get a lower priority, even if analysts are very bullish on them. This is a very flexible sheet that I can adjust according to the market narrative.
'Floating' Options integration
One of the critical features of our options integration is the 'floating' options formula. Options values derive from the underlying stock, and they constantly change and expire. Therefore, if we were explicit about what option to import, we would need to adjust the sheet continually. The floating formula solves this: Imagine telling Excel to import ATM call instead of Call strike 100 (which is ATM right now but might not be in the future). The usage is straightforward:
- You can control the Expiration date by giving a specific date or Days to the expiration variable.
- You can control the strike with a specific input, Moneyness, or N strikes away (for example, 1st OTM or 3rd ITM).
For example: =OPTIONSAMURAI.OPTION("AAPL", "CALL", ">20", "-5%", "name", "stock_last", "strike") (Excel) The above formula will return the call for ticker AAPL (Apple Inc.) with the expiration closest and above 20 days. The strike will be 5% below the current stock price (Moneyness filter), and the formula will return the name of the stock, the stock's last price, and the strike for the option. See the documentation for a detailed explanation and more examples.
How to use this Template - Excel
We've created a simple template to help you get hands-on experience with the formulas and help you get started. With that, you'd be able to import options data to Excel/Sheets in minutes. To start, edit the orange cells to see the effect of the template and understand how it works. After that, add the side panel and start adjusting the template. This can help you get started in building your own tool. Download the template here.
How to use this Template - Google Sheets
The Google Sheets template is identical to the Excel template. Please download a copy to your account and make sure it is the same account you install the plugin on.Please don't ask for permission to edit this template, as it has to be the base for everyone. Go to the template here.
Summary
Excel and Google sheets are very flexible tools. They allow traders and investors to create customized tools to help them in their process. Our plugin will enable you to import options data automagically to Google Sheets and Excel so you can automate your process and save time. This allows you to build custom workflows, formulas, and tools to increase your edge. Go to Option Samurai for more.