Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Master the QM_Value Function for Real-Time or Delayed Stock Metrics

The QM_Value function provides a straightforward way to request fundamental or market data (such as price, volume, P/E ratio, etc.) directly in Excel from QuoteMedia via MarketXLS. By simply entering the desired metric (e.g., LAST, MARKETCAP) and the stock symbol (e.g., MSFT, AAPL, or BNS:CA), you can pull live or cached data without leaving your spreadsheet. This flexibility makes it a go-to function for investors, analysts, and traders alike.

Why Use This Function?

  • Effortless Access to Market Data: Retrieve real-time or delayed quotes, fundamental figures (like EPS, market cap), and more with minimal setup.
  • Wide Symbol Coverage: Supports US stocks, Canadian stocks (with :CA suffix), options, and even identifier searches (CUSIP, ISIN, etc.) subject to data entitlements.
  • Time-Saving Workflow: Eliminates the need to switch between external terminals and Excel; all of your data is directly in the spreadsheet.
  • Dynamic Updates: Data can refresh on demand, aiding in building live dashboards or robust analysis models.
  • Error Handling & Caching: Built-in checks help return “NA,” “Refreshing,” or “Please Refresh” when certain conditions are met, ensuring clarity when data is missing or being updated.

How to Use in Excel

=QM_Value(Metric, Symbol)
  1. In any Excel cell, type “=QM_Value(”
  2. For the first parameter, enter the Metric as a string (e.g., "LAST", "HIGH", "MARKETCAP").
  3. For the second parameter, enter the Symbol as a string (e.g., "MSFT", "AAPL", or "BNS:CA").
  4. Press Enter to retrieve the data instantly or see a relevant status message.

When you open or refresh the spreadsheet, MarketXLS attempts to populate the data from the QuoteMedia feed. If the data is not in the local cache, you might see "Please Refresh" until new data is fetched.

Parameters Explained

Parameter Description Example Values Notes
Metric The fundamental or market metric you want to retrieve. "LAST", "HIGH", "EPS" Metrics must be supported by QuoteMedia. The string is automatically converted to uppercase.
Symbol The stock or security identifier (required). "MSFT", "AAPL", "BNS:CA" Supports US/CA suffixes, options (prefix “@”), CUSIP/ISIN, or openFIGI. Must be a valid symbol to avoid “NA.”

Example Usage

Basic Examples

  1. Last Trade Price for Microsoft
    • In cell A1: =QM_Value("LAST","MSFT")
    • Returns the most recent trade price for Microsoft (MSFT) if your license and data entitlements allow it.

  2. Market Capitalization for Apple
    • In cell A1: =QM_Value("MARKETCAP","AAPL")
    • Displays Apple’s market cap. If the data isn’t in cache, you may see "Please Refresh" until the system retrieves it.

  3. 52-Week High for Bank of Nova Scotia on Toronto Stock Exchange
    • In cell A1: =QM_Value("WEEK52HIGH","BNS:CA")
    • Provides the 52-week high for this Canadian symbol. If “Symbol” is valid but data is missing, you might see “NA.”

Advanced Scenarios

• Custom Dashboards: Combine multiple QM_Value calls (e.g., LAST, VOLUME, MARKETCAP) into a single Excel dashboard for quick market overviews.
• Conditional Analysis: Use Excel’s IF statements to track changes (e.g., "Refreshing" vs. actual numeric values) to trigger alerts or color-coded highlights.
• Data for Options: Retrieve fundamentals for option symbols when prefixed with “@” (e.g., "@AAPL230120C150").
• Bulk Symbol Lookups: Input multiple QM_Value formulas for faster data analysis when scanning a watchlist of up to 100 symbols (each with its own cell).

Common Questions and Troubleshooting

  1. Why am I getting "NA"?
    • The symbol might be invalid or missing fundamental data. Check spelling, suffixes, or ensure the company publicly files that data.

  2. What does "Please Refresh" mean?
    • Data is yet to be retrieved from the QuoteMedia feed. Refresh your workbook or wait for the system to update its cache.

  3. Why do I see "Refreshing"?
    • MarketXLS is actively fetching data. Once complete, the displayed cell value should update automatically.

  4. What if my license is invalid?
    • You’ll receive an error message indicating the license is not valid. Verify your subscription or license settings in MarketXLS.

  5. Symbol Suffixes and Identifiers
    • Canadian stocks typically need “:CA,” while US stocks often have “:US” by default (though you can usually omit “:US”).
    • For CUSIP, ISIN, or other identifiers, prepend an asterisk (*). Check your entitlements before using these.

By leveraging QM_Value in Excel, you can craft powerful real-time analytics, track performance, and automate data-driven insights — all seamlessly within MarketXLS.