Getting Started
- Introduction
- Download
- Installation & Activation
- Troubleshooting
- Data Refresh/Updates
- Software Updates
- Manage Your Subscription
- Example Spreadsheets
General Formulas
Technical Analysis Toolkit
- Introduction
- Simple Moving Average (SMA)
- Exponential Moving Average (EMA)
- Volume Weighted Average Price (VWAP)
- Average True Range (ATR)
- Bollinger Bands
- Aroon
Yahoo Finance
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
Nasdaq Data Link
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:
