Unusual Option Open Interest End-of-Day Scan

Gain an edge in your trading or investing decisions by scanning for unusual option open interest (OI) activity directly within Excel. The opt_UnusualOptionOIScanEOD function (internally identified as fn_01515) helps you identify potential market opportunities by highlighting contracts that stand out with significant volume, open interest changes, or other relevant factors. With a few simple inputs, you can tailor the scan results to target specific markets (US or India), instrument types (equities or indexes), and option types (calls or puts).

Why Use This Function?

• Pinpoint Unusual Activity: Quickly detect spikes or unusual changes in options OI that might hint at big moves.
• Flexible Market Coverage: Focus on the US or Indian markets to keep your search relevant.
• Filter by Option Type: Limit your search to calls, puts, or grab them all in a single scan.
• Simple Excel Integration: Results are placed directly on your worksheet, letting you easily slice, filter, or combine them with further Excel analysis.
• Take Swift Action: With a concise list of standout contracts, you can make more informed trading or hedging decisions.

How to Use in Excel

=opt_UnusualOptionOIScanEOD(NumberOfRecords, [OptionType], [Country], [InstrumentType])

1. Open Excel and ensure the MarketXLS add-in is active.
2. In any cell, enter the function name followed by the desired parameters in parentheses.
3. Upon pressing Enter, the function fetches the requested data and prints it in Excel. Depending on your add-in settings, it may print the data in a new sheet, in your current selection, or prompt you to choose a location.

Parameters Explained

Example Usage

Basic Examples

1. Retrieve 5 unusual option OI records for Calls in the US (equities and indexes combined): » =opt_UnusualOptionOIScanEOD(5, "CALL", "US", "")

   • This fetches 5 call options from the U.S. market with unusual open interest data.
   • Results are printed onto a new sheet, your selection, or as prompted.

2. Pull 2 unusual options for Puts from India without specifying an instrument type (defaults to both equity & index): » =opt_UnusualOptionOIScanEOD(2, "PUT", "India", "0")

   • Focuses on Put options in the Indian market.
   • Since you used "0" for InstrumentType, it will include any underlying (equities or indexes).

Advanced Scenarios

• Combining with Other Excel Functions

• After retrieving the data, apply Excel’s built-in filtering, sorting, or pivot tables to quickly identify the highest open interest, largest changes, or most significant volume.
• Cross-check other fundamental or technical data using MarketXLS functions or external references to refine your watchlist.

• Narrowing by Instrument Type

• =opt_UnusualOptionOIScanEOD(10, "", "US", "index")
  • Targets the top 10 unusual OI option contracts specifically on major US indexes.

• Building a Trading Strategy

• Use this function daily to watch for unusual open interest activity.
• Combine repeated calls for calls and puts to see if there’s a directional bias in the market.
• Overlay potential changes in open interest with volume or price movements for a deeper perspective on potential upcoming volatility.

Common Questions and Troubleshooting

• “Why am I getting an error about invalid format for calls/puts?”

  • Make sure you use "CALL" or "PUT" (or leave the parameter blank for both). Any spelling errors will trigger an invalid input error.

• “The function returns ‘NA’.”

  • This typically happens if there’s a temporary data retrieval issue or an internal error. Wait and try again. If the issue persists, check your MarketXLS license status.

• “My data isn’t showing up—but the cell says the function name.”

  • The function inserts the results directly into your Excel sheet. If you see the function name in the cell, scroll or check the newly created sheet or your selected region. Depending on your MarketXLS settings, data might be placed in a new worksheet, in the current selection, or after a prompt.

• “I entered ‘INDIA’ for Country. Did it default properly?”

  • Yes. Entering “India” or “IN” in any case (lower/upper) fetches the Indian market. Any other value defaults to the U.S. market.

Remember to adjust the function’s optional parameters based on your region and instrument type preferences. By leveraging unusual option OI data, you can identify potentially significant opportunities in the market and stay ahead of the crowd.