Retrieve a List of Option Strikes with the Strikes Function

The Strikes function in MarketXLS lets you easily pull all available option strikes for a chosen underlying symbol directly into Excel. It gives you a flexible way to filter by expiration date, option moneyness, and call/put type—all directly in your spreadsheet. This is especially useful for traders and analysts who need quick access to specific strike prices without manually searching through multiple data sources.

Why Use This Function?

• Quickly populate a spreadsheet with valid strike prices for calls, puts, or both.
• Filter strikes by specific expiration dates or retrieve them for all available dates.
• Tailor results to show strikes that are in-the-money (ITM), out-of-the-money (OTM), or any combination (e.g., all).
• Seamlessly integrate with other Excel-based workflows to automate, compare, or further analyze retrieved data.
• Save time and reduce errors by eliminating the need to copy/paste or manually enter strike information.

Use this function when you need to:

• Build or update an option chain spreadsheet, focusing only on relevant strikes.
• Quickly compare call vs. put strikes for a specific date.
• Filter option contract lists for specialized trading strategies (like vertical spreads or covered calls).
• Dynamically fetch valid strikes whenever new data is needed.

How to Use in Excel

=Strikes(Symbol, [Expire], [Money], [CallPutFlag])

1. Enter the function into any cell in Excel.
2. Provide the Symbol (e.g., "AAPL", "TSLA", etc.).
3. Optionally enter an expiration date (Expire). If left blank or set to "all", you retrieve strikes for all available expirations.
4. Optionally enter a moneyness filter (Money). If left blank or "all", all strikes are returned.
5. Optionally specify whether to retrieve "call", "put", or "all" (CallPutFlag).

MarketXLS will print each matching strike below the cell containing your formula, overwriting existing cells. Make sure you have adequate space below your formula.

Parameters Explained

Example Usage

Basic Examples

1. Retrieve All Strikes for Apple (AAPL)
   =Strikes("AAPL")
   Explanation: Returns a comprehensive list of every available strike for AAPL across all expiration dates, both calls and puts.

2. Filter by Expiration for Tesla (TSLA)
   =Strikes("TSLA","2023-10-20")
   Explanation: Returns all strikes for TSLA that expire on October 20, 2023, combining calls and puts, in ascending order.

Advanced Scenarios

1. In-the-Money (ITM) Calls for a Specific Date
   =Strikes("MSFT","2023-12-15","ITM","call")
   Explanation: Generates a filtered list of only in-the-money call option strikes for Microsoft expiring on December 15, 2023.

2. Custom Option Chain Building
   • First, place =Strikes("NFLX","2023-11-17","all","put") in a cell to retrieve a list of potential put strikes.
   • Then, in other cells or worksheets, reference these strikes to track premiums, implied volatility, or to further filter for specific deltas.

3. Generating Strikes for Spread Analysis
   =Strikes("AMZN","2024-01-19","OTM","call")
   Explanation: Focus on out-of-the-money calls for a longer-dated expiration, often useful for spread trading setups.

Common Questions and Troubleshooting

• "What if I don't see any results?"
  • Verify the expiration date is valid and not in the past.
  • Check that your MarketXLS license is active—invalid or expired licenses will return an error message.
  • Ensure the symbol you entered is accurate and listed with available options.

• "Why did it overwrite some existing cells?"
  • The function prints each strike in a new row below the formula cell. Place the formula in an area with enough free space below it.

• "Can I use partial parameters?"
  • Yes. You can leave any optional parameter blank or set it to "all". Only specify the ones you need to narrow down your results.

With Strikes, you can greatly simplify your option research process in Excel. Use it to pull an up-to-date list of valid option strikes, tailor them to your trading needs, and integrate them with other advanced MarketXLS or Excel functions for a streamlined workflow.