Opt_UnusualOptionVolScanEOD: Identify Unusual Option Volume in Excel

The opt_UnusualOptionVolScanEOD Excel function (also known internally as fn_01516) helps you find and analyze unusual option volume activity directly within Excel. This can help you detect significant volume spikes or open interest changes, allowing you to better pinpoint potential trading opportunities. By leveraging this function, you can quickly filter and focus on the most active options, streamline your research, and gain valuable insights into market behaviors.

Why Use This Function?

• Quickly scan for large volume or open interest changes among options without leaving Excel.
• Identify potential trading opportunities, unusual market activity, or changes in sentiment.
• Seamlessly refresh and fetch updated data from MarketXLS’ servers.
• Filter by country (e.g., US or India) and instrument type (e.g., equity or index) to narrow down your results.
• Great for building custom watchlists and integrating these scans with other Excel-based analyses.

How to Use in Excel

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

1. Open Excel and ensure that the MarketXLS add-in is installed and your license is active.
2. In any cell, type the function exactly as shown above, entering the parameters based on your needs.
3. Press Enter to see the result.
4. The function will fetch data from MarketXLS’ servers and display details of unusual option volume based on the parameters you provide.

Parameters Explained

Example Usage

Basic Examples

1. Return 5 “unusual option volume” records for all option types (US default overridden):
   =opt_UnusualOptionVolScanEOD(5, , "US")

   Explanation:
   • NumberOfRecords: 5
   • OptionType: left blank ? “0” (calls and puts)
   • Country: "US"
   • InstrumentType: not specified (defaults to “0”)

2. Return 2 highest-volume put options in India (equities only):
   =opt_UnusualOptionVolScanEOD(2, "P", "IN", "equity")

   Explanation:
   • NumberOfRecords: 2
   • OptionType: "P" (puts)
   • Country: "IN"
   • InstrumentType: "equity"

3. Return 10 records of unusual option volume with no country or instrument type specified:
   =opt_UnusualOptionVolScanEOD(10)

   Explanation:
   • NumberOfRecords: 10
   • All other parameters blank ? defaults to combined calls/puts, Indian market, and all instruments.

Advanced Scenarios

1. Integrating with Other Formulas
   Suppose you want to automatically change NumberOfRecords based on your own thresholds or calculations elsewhere in the sheet. You might reference a cell like A1 for the record count:
   =opt_UnusualOptionVolScanEOD(A1, "C", "US", "equity")

   This allows you to dynamically adjust the number of results displayed.

2. Building a Combined US/India Watchlist
   You can create two separate references—one for US (InstrumentType = "index") and one for India (InstrumentType = "equity")—to compare results side-by-side:

   • =opt_UnusualOptionVolScanEOD(5, , "US", "index")
   • =opt_UnusualOptionVolScanEOD(5, , "IN", "equity")

   This approach makes it easy to keep track of major index options in the US and top equity options in India simultaneously.

3. Trading Strategy Examples

   • Spot large changes in open interest: Identify contracts where volume or open interest is significantly higher than normal.
   • Monitor earnings seasons: Filter by calls or puts around critical market sectors during earning announcements.
   • Follow major index movements: If you suspect index volatility is significant, restrict to "index" for a more targeted analysis.

Common Questions and Troubleshooting

1. “I get ‘Call/Put flag has invalid format. Please check your inputs.’”
   • Make sure you use “C” or “P” (case-insensitive) or leave it blank (which defaults to “0”).
   • Any invalid string will cause this warning.

2. “Why am I only getting 2 results even though I typed -1 for the first parameter?”
   • If NumberOfRecords is invalid or 0 or negative, it resets to 2 by default.

3. “My function returns ‘Refreshing.’ What does that mean?”
   • This can happen if the data is being updated in real-time. The function will auto-refresh once the data is ready.

4. “Can I retrieve data for other countries?”
   • Currently, the function only distinguishes “IN” (India) and “US” for unusual options data. Any entry that’s not recognized as India defaults to US.

5. “I still get ‘NA’ as a result. Why?”
   • If there’s an error on the server side or if your license is invalid, the function may return “NA.” Double-check your MarketXLS license status and inputs.

By combining this function with your technical or fundamental analysis within Excel, you can streamline your research to quickly spot unusual market activity. Play around with the input parameters to expand or narrow your scans based on your specific trading strategy. Enjoy the flexibility and the power of MarketXLS right inside your Excel workbook!