Unusual Options Volume and Open Interest Scanning in Excel

The fn_01517 function, implemented in MarketXLS as opt_UnusualOptionVolOIScanEOD, allows you to quickly scan for unusual options volume and open interest directly in Excel. Whether you’re focusing on the US or Indian markets, calls, puts, or both, this function helps traders and investors spot unique volume/interest spikes and take advantage of potential entry and exit points.

Why Use This Function?

• Quickly identify the top unusual options by volume or open interest within specified markets.
• Filter calls, puts, or both to tailor your analysis.
• Adapt scanning for different geographies: analyze either the US or Indian market in one place.
• Warmly integrates within Excel, so you can combine its results with other Excel functions and formulas.
• Saves time by automating repetitive data lookups and sorting.

How to Use in Excel

Simply type the function into your Excel cell as follows:

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

1. Make sure you have a valid MarketXLS license activated.
2. Enter NumberOfRecords (for instance, 5, 10, or 20) to retrieve that many “top” results.
3. Optionally specify the OptionType (calls, puts, or both).
4. Indicate the Country (“IN” for India or “US” for the U.S.) to filter the market.
5. Optionally define the InstrumentType (e.g., “equity” or “index”) to narrow your retrieval.

When you press Enter, MarketXLS makes a data call to retrieve the appropriate unusual option activity. The data is then printed in Excel for you to analyze or integrate into your trading models.

Parameters Explained

Example Usage

Basic Examples

1. Retrieve the top 5 unusual option records (both calls and puts) for the Indian market without specifying instrument type:

   =opt_UnusualOptionVolOIScanEOD(5)

   • NumberOfRecords = 5
   • OptionType (omitted) ? defaults to “0” (both calls and puts)
   • Country (omitted) ? defaults to “IN”
   • InstrumentType (omitted) ? defaults to “0” (no specific filter)

2. Get the top 10 put options with unusual volume or OI in the US:

   =opt_UnusualOptionVolOIScanEOD(10,"P","US")

   • NumberOfRecords = 10
   • OptionType = “P” (puts only)
   • Country = “US”
   • InstrumentType (omitted) ? defaults to “0”

3. Pull the top 8 call options for the Indian index market:

   =opt_UnusualOptionVolOIScanEOD(8,"C","IN","index")

   • NumberOfRecords = 8
   • OptionType = “C” (calls only)
   • Country = “IN”
   • InstrumentType = “index”

Advanced Scenarios

• Combine with other Excel analysis: You can track open interest changes, volume spikes, and then use additional Excel formulas (like LOOKUP, VLOOKUP, or advanced charting) to form comprehensive trading strategies.

• Portfolio overlay: If you have a list of stocks in your portfolio, you can cross-reference the results from fn_01517 with your holdings to see if any underlying symbols match. This helps you quickly gauge option interest in your own portfolio’s securities.

• Market watch expansion: By using multiple function calls with different parameter combinations (e.g., put vs. call, India vs. US), you can track unusual options activity across global markets and categories.

Common Questions and Troubleshooting

1. “I got ‘Call/Put flag has invalid format. Please check your inputs.’”

   • Ensure OptionType is either “C”, “P”, or “0”. Any other entry (like “CALL” or “PUT” spelled out) is invalid for this function’s usage.

2. Why am I getting only 2 results even though I typed “0” for NumberOfRecords?

   • If NumberOfRecords is 0 or negative, the function defaults to 2. Make sure you specify a positive integer.

3. The function returns “NA” or “Refreshing.”

   • “NA” indicates an issue with the data retrieval or an unexpected error on the backend. “Refreshing” likely means MarketXLS is momentarily fetching or refreshing data. Allow time and try again if necessary.

4. Why does my data keep returning from India’s market even though I typed in some other country?

   • Acceptable inputs for Country are “IN”, “US”, “india”, or “INDIA.” Any other value auto-resets to US or defaults to IN if left blank.

5. Do I need to install anything extra for this function to work?

   • This function is part of the MarketXLS Excel Add-in. Make sure your license is valid, and you have the latest version of MarketXLS installed.

By integrating fn_01517 (opt_UnusualOptionVolOIScanEOD) into your Excel workflow, you gain an efficient tool for spotting unusual options activity in both Indian and US markets—all from the comfort of your spreadsheet. Take advantage of this function to uncover trading opportunities driven by significant volume and open interest changes in your favorite equities or indexes.