Using Market Data’s Google Workspace Add-on, you can get access to the OPTIONCHAIN formula. This formula outputs a complete option chain in Google Sheets with a level 1 quote for each option in the chain, full greeks, implied volatility, and other helpful data about each option in the chain. Unlike traditional option chains, with calls on the left and puts on the right, the OPTIONCHAIN formula merges the entire chain’s output into a single table that is pre-sorted by side and strike. This allows for easy VLOOKUP formulas to be used in your sheet when you want to select a subset of options from the output. Power users, however, will find that VLOOKUP is often completely unnecessary because OPTIONCHAIN includes many optional filters that make restricting the output extremely easy.
Video Tutorial: How To View and Filter an Options Chain in Google Sheets
Basic Usage: Select A Single Date From The Chain
The basic usage of the OPTIONCHAIN formula is very simple. Here’s an example: =OPTIONCHAIN("AAPL","all","1/17/2025")
- Use the underlying ticker in the first parameter.
- Select the columns to include in the output
- Select the expiration date to use.
In the above example, we’ve output all data from the 1/17/2025 expiration date for AAPL. The result is a huge table of data that can be a little intimidating at first. There are more than two dozen columns of data and on popular tickers like AAPL, there can be hundreds of rows as well! For a smaller more manageable output, you can specify exactly what columns you’d like in the output like this: =OPTIONCHAIN("AAPL","symbol,side,strike,bid,ask","1/17/2025")
Intermediate Use: Filter The Option Chain By Strike
To avoid an output with hundreds of rows, it oftentimes makes sense to filter the output of the formula by strike. For example: =OPTIONCHAIN("AAPL","symbol,side,strike,bid,ask","1/17/2025","150-175") In this example, I’ve restricted the columns to just 5 and limited the strikes to values between 150 and 175. Note how the output is now much more manageable.
The OPTIONCHAIN formula is extremely flexible. You can use a range like “150-175”, but you can also use individual strikes such as “160,180,200” and that will create an output with the exact strikes specified. Logical operators such as “<150” or “>=160” can also be used. This gives you enormous flexibility to select only the strikes you need from the formula and avoid wasting time filtering the output later. The filters are already built-in to the formula.
Filter By Side and By Moneyness
Add a 5th parameter to filter by side or by moneyness. Let’s say you only wanted to get calls, the formula would be constructed as follows: =OPTIONCHAIN("AAPL","symbol,side,strike,bid,ask","1/17/2025","150-175","calls"). What about if you only wanted in the money options? Just use “itm” as the 5th parameter and leave the 4th parameter blank. =OPTIONCHAIN("AAPL","symbol,side,strike,bid,ask","1/17/2025",,"itm") Use any of these keywords in the 5th parameter to filter by side or by moneyness:
- Calls – Limit to calls only
- Puts – Limit to puts only
- ITM – Limit to in the money options only
- OTM – Limit to out of the money options only
Advanced Use: Filter by Days To Expiration (DTE) and Delta
Sometimes you have potential trades you want to watch every day. You can use the DTE and DELTA keywords in the date and strike parameters to make the formula more dynamic.
If you insert 30 DTE, for example, the formula will select an expiration date closest to 30 days in the future. Remember, options do not expire every day, so the result may not be exact, but the formula will pick the closest match. Use DTE in the 3rd parameter, where you’d normally include a date.
The DELTA keyword can also be used in the 4th parameter in place of the strike. For example, if you put “DELTA 30,60” that will return exactly two options that most closely match the delta indicated. You can also use delta ranges and logical operators, exactly the same way you do with strikes. So “DELTA 50-60” would return all deltas between .50 and .60 and “DELTA <30” would return all deltas lower than .30. The absolute value of delta is always computed. So if you use DELTA 20-30 and you are not filtering for PUTS or CALLS it will return all puts with deltas between -.20 and -.30 and all calls with deltas between .20 and .30. There is no need to use negative deltas when filtering.
By using DTE and DELTA it is possible to evaluate potential future trades and build an option screener that maintains itself perpetually updated, with no need to update the formula’s expiration date or strike parameters. Thanks to the OPTIONCHAIN formula, it has never been easier to get a real-time option chain in Google Sheets. Once you master it, the possibilities to track your options ideas are pretty much limitless.
