Retrieve Market and Options Data with QM_List

The QM_List function is a powerful MarketXLS feature that allows you to request market data—such as historical quotes, intraday intervals, and even options data—directly into Excel. This function automatically outputs data in a neat, tabular format below the cell where you enter the formula, so you can analyze stock or options chains without having to leave Excel.

By leveraging QM_List, you can seamlessly import up to thousands of lines of data for stocks or options, filter precisely which strikes or expirations you need, and integrate everything into your custom Excel workflows.

Why Use This Function?

• Efficient Data Retrieval: Instantly fetch historical prices, intraday intervals, or option chain data in Excel without copy/pasting from external sources.
• Flexible Filtering: Narrow down your search to specific date ranges, intervals, or options criteria (e.g., minimum strikes, calls only).
• Tabular Output: QM_List neatly places returned data in your spreadsheet, saving you time from manual formatting.
• Powerful Combinations: Combine your requested data with other Excel formulas to build trading models, analytics dashboards, or simple watchlists.
• Real-World Utility: Ideal when you need quick customizable insights—for instance, analyzing volatility patterns, exploring short-term price history, or investigating option greeks.

How to Use in Excel

=QM_List(Method, [Arg1], [ArgVal1], [Arg2], [ArgVal2], ..., [Arg15], [ArgVal15])

• You must specify the “Method” (e.g., "getHistory", "getIntervals", "getOptionchain"), which determines the data type or endpoint.
• Optionally, you can pass up to 15 pairs of parameter names and their values. For example:

• "symbol","MSFT"
• "start","2020-01-01"
• "end","2020-03-31"
• "interval","5"

These parameters will further refine what data is returned.

When you press Enter, QM_List automatically returns data. Be sure that cells below your input cell are clear, because the function writes multiple rows of results.

Parameters Explained

Note: If you pass certain keywords like "structured", "onlyStrikes", or "onlyExpirations" as Arg1 (or Arg2, etc.), QM_List may return specialized data formats for options.

Example Usage

Basic Examples

1. Retrieve brief historical quotes for AAL:
   =QM_List("getHistory", "symbol", "AAL")

   This fetches end-of-day history for American Airlines (AAL) over a default date range.

2. Retrieve 5-minute intraday intervals for MSFT starting March 18, 2020:
   =QM_List("getIntervals", "symbol","MSFT", "interval","5", "start","2020-03-18")

   Results in all 5-minute bars from the specified date forward (until data is available).

3. Retrieve all near-term options for AAL with a minimum strike of 12 and maximum strike of 14:
   =QM_List("getOptionchain", "symbol","AAL", "expireMin","2020-07-19", "expireMax","2020-09-18", "strikeMin",12, "strikeMax",14)

Advanced Scenarios

1. Filtering out zero-volume and expired options:
   =QM_List("getOptionchain","symbol","AAL","expireMin","2020-07-19","expireMax","2020-09-18","strikeMin",12,"strikeMax",14,"inclExpired","false","inclZeroVol","false")
   This only returns unexpired contracts with volume above zero.

2. Retrieve structured option data:
   =QM_List("getOptionchain","symbol","AAL","structured")
   Specifying “structured” may yield a reorganized table, useful for advanced analysis like calls and puts side-by-side.

3. Combining with other Excel features:
   • Pair your output cell references with VLOOKUP or INDEX-MATCH for deeper analytics.
   • Use Excel’s built-in charting tools on the returned data to visualize trends quickly.

Common Questions and Troubleshooting

• “Method ‘XYZ’ is not valid.”

• Double-check that your Method matches a supported Quotemedia method.
• Ensure you have the correct data package from Quotemedia for specialized requests (e.g., options data).

• “No Data Retrieved.”

• Verify that your chosen symbol is valid, and the date range or filters are correct.
• If you’re pulling options data, be sure the option symbol or expiration date is valid and not fully expired (unless “inclExpired” is set to "true").

• “Data overwrote part of my worksheet.”

• The QM_List function pastes results below your active cell. Make sure that space is clear or insert a new sheet.

• “The function returned partial data or blank cells.”

• This might happen if certain filters exclude nearly all the results (e.g., narrow strike ranges). Broaden your filters and try again.

Remember:

• Always ensure you have the appropriate Quotemedia subscription so that the requested data method is available.
• The date format should be “YYYY-MM-DD.”
• You can use up to 15 argument/value pairs to finely control your queries.
• Review your Excel for enough space to display full tables.

By using QM_List, you can efficiently pull quotes, intervals, and options data directly into Excel. Whether you’re a portfolio manager wanting quick EOD downloads or an active trader building an options strategy, QM_List empowers you to shape your data the way you need it—without leaving Excel.