Knowledge Base | How to Load Option Chains from Yahoo Finance to Excel

How to Load Option Chains from Yahoo Finance to Excel

Contents

Overview

Yahoo Finance publishes US stock option chains at their website and has no download links.

However, Yahoo Finance has an undocumented web service that returns option chains in JSON using URLs like these:

https://query1.finance.yahoo.com/v7/finance/options/YHOO

https://query1.finance.yahoo.com/v7/finance/options/YHOO?date=1510876800

Our product, MARKET.RTD, loads option data using these URLs and allows consuming data in Microsoft Excel using RTD formulas.

Loading Option Chains from Yahoo Finance into Microsoft Excel using MARKET.RTD

You can load option data using simple formulas like

=RTD("market.rtd",,"YahooFinanceOptions","AAPL180119C00100000","Ask")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL180119C00100000","OpenInterest")

Options from Yahoo Finance in Microsoft Excel by contact names

Also, you can load option data using contract parts:

=RTD("market.rtd",,"YahooFinanceOptions","AAPL","180119","C",100,"Ask")
=RTD("market.rtd",,"YahooFinanceOptions","AAPL","180119","C",100,"OpenInterest")

Options from Yahoo Finance in Microsoft Excel by contacts

Moreover, you can get last underlying stock prices, find ATM strikes, and the next ITM or OTM strikes one by one.

So, you can easily build complex refreshable models.

Options from Yahoo Finance in Microsoft Excel

You can find this example in the Examples folder of the downloaded package.

Also, you can use Formula Builder to get all available formulas for any data provider.

MARKET.RTD Formula Builder - Providers

You can select how to organize formulas, by rows or by columns, and paste them into Microsoft Excel spreadsheets.

Downloads

Products for Option Traders
MARKET.RTD
The app loads data from Yahoo Finance and MSN Money into Microsoft Excel
Version: 5.35 | 04/29/2024 | 4.6MB | Getting Started | What's New | Edition Comparison
Download

Disclaimer

Yahoo Finance is a logo and registered trademark of Yahoo! Inc.

Microsoft Excel is a logo and registered trademark of Microsoft Corporation.