Access Yahoo Finance Options Data in Excel

Overview

The YahooFinanceOptions provider offers access to real-time options data from Yahoo Finance, including calls and puts across multiple expiration dates and strike prices. It supports:

• Pricing Fields
  Bid, ask, last price, change, percent change, mark price

• Volume and Open Interest
  Volume, open interest, bid/ask size

• Contract Details
  Expiration date, strike price, option type (call/put), currency

• Market Metadata
  Quote time, underlying symbol, market state

This provider is ideal for building real-time options chains, volatility surfaces, and custom trading models directly in Excel.

Use the following generic RTD formula:

=RTD("market.rtd",,"YahooFinanceOptions","<Option Symbol>","<Data Field>")

Example:

=RTD("market.rtd",,"YahooFinanceOptions","AAPL260116C00200000","Last")

Here, <Option Symbol> is a full OCC-style option symbol (e.g., AAPL260116C00200000), and <Data Field> is one of the supported fields listed below.

To find option symbols, use Yahoo Finance Options or create them using your own code or tools.

Use the Formula Builder to generate and copy formulas into Excel. It also allows you to customize separators and layout settings.

By default, the refresh interval is six hours. You can modify it using the following formula:

=RTD("market.rtd",,"YahooFinanceOptions","rtd_RefreshInterval",66060)

Note that Market RTD loads one page to get the data for all options of the month.

Option Part Formats

Option code format:

[.]<Option Symbol><Expiration Date><Option Type Char><Strike>

Expiration date format: yymmdd
Option type char: C or P

Strike formats:

• 8 digits with leading zero as strike — 1000 (Yahoo Finance format)
• 7 digits with leading zero as strike — 100
• 6 digits with leading zero as strike — 10
• Strike as is, with or without decimal part (common format)

Examples:

=RTD("market.rtd",,"YahooFinanceOptions","AAPL260116C00200000","OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL260116C00200","OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL260116C200","OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL260116C200.0","OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions",".AAPL260116C200.0","OpenInt")

The last formula uses the thinkorswim format.

To find options, visit Yahoo Finance.

Note: Options data is available for US exchanges and may not be available for other global exchanges.

Using Option Specifications

Excel formula format:

=RTD("market.rtd",,"YahooFinanceOptions","<Symbol>", <Expiration Date>, <Strike>, "<Option Type>", "<Data Field>")

Expiration date formats:

• yymmdd
• yyyy-mm-dd
• mmmdd,yy
• mmmdd'yy

Option type format: C or CALL, P or PUT

Examples:

=RTD("market.rtd",,"YahooFinanceOptions","AAPL", "260116", 200, "CALL", "OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL", "2026-01-16", 200, "C", "OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL", "JAN26,16", 200, "PUT", "OpenInt")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL", "JAN26'16", 200, "P", "OpenInt")

Special Notes

• Symbol, OptionSymbol, Exp, Strike, and Type are parsed automatically from the option code.
• OptionCode follows the standard OCC format.
• OptionSymbol ends with 7 for mini options and with 1 for some pre-split options.
• ChangeInPercent, ChangePercent, and Percent are equivalent and calculated by Market RTD.
• Mark is calculated as (Bid + Ask) / 2.

To assist with filtering and lookup:

• Use Strike~0 to find the ATM strike.
• Use Strike+0 or Strike-0 to find equal or nearest strikes.
• Use Strike+1 and Strike-1 to find strikes just above or below.
• Use ExpDate1 to ExpDate20 to retrieve available expiration dates.

Data Fields and Excel Formulas by Option Codes

Data Fields and Excel Formulas by Option Contract Specifications