Google Sheets users have been using the =GOOGLEFINANCE formula to get automatically updating stock and forex prices for years. However, options pricing is not available through Google Finance. With Market Data, you can use a simple formula in your spreadsheet to easily get options prices and begin to work with them.
Get Live Midpoint Options Quotes In Google Sheets
If you know the option symbol for the contract you are looking for, just use the =OPTIONDATA formula the same way you use the Google Finance formula. Let’s assume we want to get the price on the $400 CALL for January 20, 2023 on SPY. The option symbol is for that call is SPY230120C00400000. Just use =OPTIONDATA("SPY230120C00400000"). When using the formula in this manner, we will provide you with our most current midpoint (between the bid and ask) price.
For live prices you must authorize a broker connection. Market Data does not provide real-time options prices without a connection to your broker.
Get Current Bid/AskOptions Quotes
As you’ve seen, the OPTIONDATA function will normally return the midpoint or mark price of the option: (bid + ask) ÷ 2. In options that are thinly traded or with less liquidity, the mid price may not be appropriate. Sometimes you will want to get the bid or ask price. This can be done easily by adding a second parameter in the function. For example: =OPTIONDATA("SPY230120C00400000", "bid") or =OPTIONDATA("SPY230120C00400000", "ask"). You can also use “all” to get all available information on a single option contract. This will include all three price points, plus other useful information such as the volume, open interest, etc.
If the option has expired and is no longer traded the function will return a No Data error when using either 1 or 2 parameters.
Historical Options Prices In Google Sheets
When passing just one or two parameters to the formula as shown in this example, you will always get the most current option price. However, you can also get historical options prices for contracts that are no longer traded by using additional parameters. You can get historic prices for a single options symbol by passing a third parameter to the function: date. This works the same way as Google Finance. For example, =OPTIONDATA("SPY230120C00400000", "price", "1/25/2021"). You can use “price”, “mark”, or “mid” as a parameter, all three will return the midpoint price for the option. “Bid” and “ask” can also be used if you would like historic bid/ask prices. And again, you can use “all” to get all contract information.
When returning historic data, OPTIONDATA works slightly different from GOOGLEFINANCE and it will only return the data with descriptive headers if more than 1 value is requested. Do you just need the price information for a specific day without the date and headers? Just request a single price. For example, =OPTIONDATA("SPY230120C00400000", "price", "1/25/2021") will return just the midpoint price of this option contract for the day in question in a single cell with no headers.
You can add a 4th parameter after the first date to fetch historical data from multiple dates. For example, =OPTIONDATA("SPY230120C00400000", "price", "1/01/2021", "1/31/2021") will return all prices for January 2021. You can also use the 4th parameter as a number of dates instead of a specific date. So =OPTIONDATA("SPY230120C00400000", "price", "1/01/2021", 31)will return the same thing, 31 days of price data for the option in question.
When using the 4th parameter to specify the number of days, calendar days are used, not trading days. To retrieve 1 year of data from the starting date, use 365 as the second parameter, not 252. However, prices are only returned on trading days. So if you request 365 days of data, the function will only return 252 values. Weekends and holidays when the market was not open will not be included in the results.
As you can see, the OPTIONDATA data function is powerful, versatile, and easy to use. If you are familiar with the GOOGLEFINANCE formula, you don’t need to learn anything new. And even for beginners, you’ll be fetching data very quick.
