StrikeNext: Retrieve the Next Nearest Option Strike for Smarter Trading

The StrikeNext function in MarketXLS is designed to help you conveniently find “next” or “offset” option strikes directly in Excel. With this function, you can quickly pinpoint option strikes around the at-the-money (ATM) price or a chosen target strike, drastically simplifying the process of setting up and fine-tuning trading strategies.

By specifying a symbol (e.g., AAPL, MSFT, etc.), an offset value for the strike (positive or negative), and an optional expiration date, you can generate immediate and precise strike data. This is an invaluable tool for traders looking to initiate or adjust their positions using a straightforward Excel-based workflow.

Why Use This Function?

• Automate Strike Selection: Eliminate guesswork and manual strike checks by automating the query to retrieve relevant strike levels around the current price or a targeted price.
• Flexible Expiration Date Handling: Optionally specify an expiration date to refine your query to that specific options chain. If no expiration is provided, StrikeNext automatically uses the nearest available expiration date.
• Seamless Excel Integration: Execute your option strike lookups within Excel’s familiar environment — no need to toggle between multiple systems.
• Time Efficiency: Ideal for quick analysis of multiple symbols, enabling you to focus on strategy rather than manually retrieving strike data.
• Adaptable Offsets: Get unique strikes above or below the ATM by providing positive, negative, or special offset parameters (e.g., minus2, near_185).

How to Use in Excel

=StrikeNext(Symbol, NumberOfStrike, [ExpirationDate])

• Symbol (required): Ticker symbol of the underlying (e.g., "AAPL").
• NumberOfStrike (required): The offset or directive for which strike to fetch. You can pass a number (e.g., 1, -2), or a textual variant (e.g., "minus1", "near_185").
• ExpirationDate (optional): If you provide a valid date, the function will use that date’s options chain. Otherwise, it defaults to the nearest available expiry.

To implement, simply type “=StrikeNext(…” in any Excel cell, fill in the parameters, and press Enter. You’ll receive the requested strike directly in your worksheet.

Parameters Explained

Example Usage

Basic Examples

1. Retrieve the next strike above the ATM for AAPL:
   • Formula in Excel:
   =StrikeNext("AAPL", 1)
   • Explanation: This fetches the first strike above the at-the-money price for Apple’s nearest expiration.

2. Retrieve the second strike below the ATM for MSFT:
   • Formula in Excel:
   =StrikeNext("MSFT", -2)
   (Or equivalently, =StrikeNext("MSFT", "minus2"))
   • Explanation: This returns the second strike lower than the at-the-money price for Microsoft’s nearest expiry.

3. Use a specific expiration date for SPX:
   • Formula in Excel:
   =StrikeNext("^SPX", 1, "03/15/2024")
   • Explanation: Retrieves the strike one level above the ATM specifically for the March 15, 2024, expiration of SPX options.

Advanced Scenarios

1. Searching for “near” a specific raw price:
   • Formula in Excel:
   =StrikeNext("AAPL", "near_185")
   • Explanation: Locates the strike closest to 185 for Apple’s nearest listed option expiration.

2. Larger Offsets
   • You can create arrays of strikes. For instance, if you want to retrieve three consecutive strikes around the ATM, you can place these formulas in adjacent cells:
   • =StrikeNext("MSFT", -1) 'One strike below ATM
   • =StrikeNext("MSFT", 0) 'Returns ATM or nearest
   • =StrikeNext("MSFT", 1) 'One strike above ATM

3. Integration With Other Excel Functions:
   • Combine With IF or conditional logic to dynamically switch symbols or offsets based on other cell values.
   • For instance:
   IF(A1="Bullish", StrikeNext("TSLA",1), StrikeNext("TSLA","minus1"))

Common Questions and Troubleshooting

• Why am I getting “NA”?

• Ensure the ticker is valid and recognized by MarketXLS. Also confirm your license is active.
• Check you have a valid internet connection, as the function relies on real-time web requests.

• Can I use fractional offsets?

• Numerically, you could pass decimal values, but practical option strikes typically use whole-number offsets.

• Do I have to supply an Expiration Date manually?

• No. If you omit it, the function uses the nearest available expiry date automatically.

• What if my offset points to a strike that doesn’t exist?

• Extremely large offsets can result in “NA” if the system cannot return a valid strike at that level.

By leveraging StrikeNext in Excel, you streamline options analysis and quickly locate the optimal strike for your strategy. Keep these parameters and examples in mind to stay flexible and expedite your trading workflow with MarketXLS.