How To Get Historical Options Prices For Your Spreadsheet · Market Data (2024)

Once you know the option symbol that you need data for, use the OPTIONDATA formula to get a historical option quote. OPTIONDATA makes it possible to get historical options prices going back decades, allowing you to easily backtest any options strategy or simulate any past would-be options trade. Best yet, OPTIONDATA works almost exactly the same way as the GOOGLEFINANCE formula, so it is very easy to get started.

Make sure you install the Market Data Google Sheets Add-on to enable the OPTIONDATA formula in your spreadsheet.

Get a Single Historical Options Price Quote

The OPTIONDATA formula takes three parameters when returning a historical options quote from a specific date:

1. Option Symbol
2. Attribute(s) to return
3. Date of the quote

In the example shown here we’re looking up the price of the AAPL Jul. 21, 2023 $160 call on January 3, 2023, the first trading day of 2023. The option symbol for this call is AAPL230721C00160000 and the full formula to get this historical quote is =OPTIONDATA("AAPL230721C00160000","date,bid,mid,ask","1/3/2023"). This will output a quote with the date of the quote, the bid, mid, and ask prices.

To learn more, take a look at the OPTIONDATA documentation if you’d like a complete list of all the attributes available.

Get A Series of Historical Option Prices

Suppose we wanted to see how the price of this call changed throughout the month of January. And let’s say we wanted the full level 1 quote, with the bid size, ask size, open interest, etc. Just add a 4th parameter to OPTIONDATA and it will output quotes for every day between the two dates. This time we’ll change the second parameter to "all" so that the formula will return all data it has available for these quotes. So our new formula would be: =OPTIONDATA("AAPL230721C00160000","all","1/1/2023","2/1/2023")

Note that the formula now returns much more information:

1. Top of Book Bid / Ask Prices
2. Bid / Ask Size
3. Midpoint, Last Prices
4. Open Interest & Volume
5. In The Money / Out Of The Money
6. Intrinsic / Extrinsic Value of the option at the time of the quote
7. The underlying security’s price at the time of the option quote

Start Getting Historical Option Data For Free

You don’t need to pay to start using historical options prices. Market Data’s Free Forever plan will let you use up to 100 formulas per day for free in your Google Sheets spreadsheets. Power users who need access to lots of data can demo our service with a 30 day free trial and get up to 50,000 daily prices. Historical options prices are available for all U.S. stocks, ETFs, and indices, so there’s no longer any challenge in getting the data you need.