Getting Started

• Introduction
• Download
• Installation & Activation
• Troubleshooting
• Data Refresh/Updates
• Software Updates
• Manage Your Subscription
• Example Spreadsheets

General Formulas

• Black-Scholes Option Pricing
• Instrument Identifier Conversions
• Miscellaneous

Technical Analysis Toolkit

• Introduction
• Simple Moving Average (SMA)
• Exponential Moving Average (EMA)
• Volume Weighted Average Price (VWAP)
• Average True Range (ATR)
• Bollinger Bands
• Aroon

Federal Reserve Economic Data
(FRED)

• Overview
• Formula Reference

Yahoo Finance

• Formula Reference
• • All Formulas
  • Stocks
  • Stocks: Income Statement
  • Stocks: Balance Sheet
  • Stocks: Cash Flow
  • Stocks: Analysis
  • Stocks: ESG Risk Ratings
  • Options Chain
  • Stock Options
  • Mutual Funds
  • ETFs
  • FX Rates
  • Cryptocurrencies
  • Commodities
  • Historical Market Data
  • News
• Historical Array Formulas
• Historical Data Download
• VBA Functions Reference

IG Index / IG Markets

• Account Setup
• Formula Reference
• VBA Functions Reference
• Historical Price Data Download To Excel
• Historical Price Data Download To File
• Historical Client Sentiment Data Download
• Watchlists
• Live Positions, Transactions & Activity History

EODHistoricalData.com

• Overview & Setup
• Historical Price Data
• Stocks Fundamental Data
• Stocks Financial Analysis

Nasdaq Data Link

• Overview & Setup
• Formula Reference

Excel Formula Reference: Options Chain

Excel Price Feed enables easy downloading of options chain data into your Excel spreadsheet.

There are two ways to get options chain data into your spreadsheet:

• On demand using the user interface of the Add-in.
• Automatically using Excel Price Feed Array Formulas.

Both options enable you to download an options chain either in "list" or "straddle" format, very similar to the format you can see on the Yahoo Finance website.

Below is an example of an options chain list from the Yahoo Finance website:

And here is an example of an options chain straddle from the Yahoo Finance website:

Please note that Excel Price Feed also includes formulas that implement the Black-Scholes option pricing model.

Options Chain On Demand Download

To get started with on demand download, first press the "Configuration Pane" button on the ExcelPriceFeed tab in Excel. This will bring up a window on the far right of Excel.

Select the "Option Chains" tab (half way down the screen):

In the image above you can see that I have selected Tesla stock from the search list.

To download the options chain list for the next expiry date (March 5 2021) I first put my cursor in the Excel cell where I want the data to download to then simply press the "Download" button:

A full list of all calls and puts are downloaded to your Excel spreadsheet.

If you wish to download an options chain for a different expiry date, first press the "Refresh Dates" button, this will populate the list box with all possible expiry dates for which Yahoo Finance has data.

Here you can see all the expiry dates for Tesla options:

Now I can download a straddle for a specific expiry date; the example below is the straddle for Tesla 20 January 2023:

A full list of calls and puts, aligned by strike price, are downloaded to your Excel spreadsheet.

Options Chain Excel Array Formulas

Excel Price Feed includes 3 Excel formulas for retrieving Options Chain data:

• Download an Options Chain List: EPF.Yahoo.OptionsChain.List
• Download an Options Chain Straddle: EPF.Yahoo.OptionsChain.Straddle
• Download available option expiry dates: EPF.Yahoo.OptionsChain.ExpiryDates

These formulas are very powerful as you just need to enter them into your spreadsheet once and each time you refresh your spreadsheet the latest data/prices will be downloaded.

You can use a combination of these formulas with other Excel formulas, such as lookups, to build some sophisticated Excel based option models.

Please note: these formulas are "array formulas" and are much easier to use with later versions of Excel. If you have an older version of Excel you will struggle to use them easily. For more information, please see instructions on the Historical Array Formulas page.

EPF.Yahoo.OptionsChain.List

This formula populates your spreadsheet with options chain list data for the specified ticker and optional expiry date:

=EPF.Yahoo.OptionsChain.List(ticker, expiryDate [OPTIONAL])

The input parameters required are:

Parameter	Description
Ticker	The ticker for the instrument, eg "APPL" or "SPY".
Expiry Date	If no expiry date is specified options for the next expiry date are downloaded.

Example

We would like to populate our spreadsheet with the options chain for Tesla stock for the next expiry date. We insert the following formula into cell A1:

=EPF.Yahoo.OptionsChain.List("TSLA")

Where "TSLA" is for Tesla stock

This will output the following to Excel:

EPF.Yahoo.OptionsChain.Straddle

This formula populates your spreadsheet with options chain straddle data for the specified ticker and optional expiry date:

=EPF.Yahoo.OptionsChain.Straddle(ticker, expiryDate [OPTIONAL])

The input parameters required are:

Parameter	Description
Ticker	The ticker for the instrument, eg "APPL" or "SPY".
Expiry Date	If no expiry date is specified options for the next expiry date are downloaded.

Example

We would like to populate our spreadsheet with the options chain straddle for Tesla stock for options expiring on 20 January 2023. We insert the following formula into cell A1:

=EPF.Yahoo.OptionsChain.List("TSLA", "20 Jan 2023")

Where "TSLA" is for Tesla stock; "20 Jan 2023" is the expiry date

This will output the following to Excel:

EPF.Yahoo.OptionsChain.ExpiryDates

This formula populates your spreadsheet with a list of all possible option expiry dates for the specified ticker:

=EPF.Yahoo.OptionsChain.ExpiryDates(ticker)

The input parameter required is:

Parameter	Description
Ticker	The ticker for the instrument, eg "APPL" or "SPY".

Example

We would like to populate our spreadsheet with the list of option expiry dates for Apple stock. We insert the following formula into cell A1:

=EPF.Yahoo.OptionsChain.ExpiryDates("AAPL")

Where "AAPL" is for Apple stock

This will output the following to Excel: