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

Fetching Historical or Fundamental Data with mxls_db_query

The mxls_db_query function helps you quickly retrieve historical price/volume data or fundamental metrics directly from MarketXLS databases. Whether you need average returns over a certain stretch, the highest or lowest price in recent days, or advanced analytics like CAGR and trend detection for company fundamentals, mxls_db_query handles it efficiently within Excel.

By leveraging MarketXLS’ server-based datasets, this function saves you from manual or third-party data wrangling. It computes common aggregations such as sum, average, min, max, variance, and standard deviation, or even advanced metrics like growth and average growth over a specified period. Whether you are analyzing historical price changes or fundamental aspects like EPS and revenue growth, mxls_db_query simplifies your workflow so that you can focus on insights rather than data collection.

Why Use This Function?

  • Streamlined Data Retrieval: Eliminate the need to download, clean, or maintain external datasets.
  • Flexible Aggregations: From simple (sum, average) to advanced (standard deviation, variance, growth).
  • Historic and Fundamental Data: One function to handle both stock price histories (close, open, high, low, volume, dividends, returns) and fundamental fields (revenue, eps, debt, etc.).
  • Directly in Excel: Obtain data within your spreadsheets, no extra tools or programming required.
  • Customizable Date Ranges: Pull historical data by number of days or specific start/end dates.
  • Fundamental Periodicity: Retrieve annual or quarterly data for a specified number of periods or time frames like “5y,” “4q,” and more.
  • Advanced Analytics: Evaluate trends, CAGR, average growth rates, positivity streaks, and other specialized metrics.
  • Quick Decision-Making: Perfect for building dashboards, running scenario analyses, or achieving deeper insights without leaving Excel.

How to Use in Excel

Use this function just like any other Excel formula. Choose between "historical" or "fundamental" databases and adjust the parameters based on your analytic goal.

=mxls_db_query(Database, Symbol, applyOn, doWhat, periods)
  1. Enter the formula in any cell (A1, for example).
  2. Replace Database with either "historical" or "fundamental."
  3. Provide the Symbol (e.g., "MSFT", "AAPL") for your query.
  4. For applyOn, specify what you want to analyze (like "close", "returns", "revenue", "ltdebt").
  5. For doWhat, choose how to summarize or calculate (e.g., "sum", "average", "min", "growth").
  6. Finally, specify the periods parameter:
    • For historical data, enter a number of days (e.g., 30, 100) or a date range (e.g., "2020-01-01^2021-01-01").
    • For fundamental data, include an integer plus a “y” or “q” (e.g., "5y", "4q").

Ensure you have a valid MarketXLS license. If your license is invalid or the ticker is unrecognized, the function returns "NA."

Parameters Explained

Parameter Description Example Values Notes
Database Which MarketXLS database to query. Choose "historical" for price/volume data or "fundamental" for financial statements and ratios. "historical", "fundamental" Must be either "historical" or "fundamental".
Symbol The stock (or similar) ticker symbol to query. "MSFT", "AAPL" Symbol must be valid; otherwise returns "NA".
applyOn The metric or field on which to apply your operation. For historical: close, volume, returns, etc. For fundamentals: eps, revenue, ltdebt, etc. For historical: "close", "returns"
For fundamentals: "revenue", "ltdebt"
Check documentation for key names if using more specific metrics (e.g., “ltdebt” for long-term debt).
doWhat The operation or aggregator to perform. "sum", "max", "min", "average", "variance", "standarddeviation", "growth", "avggrowth", "trend", etc. Some operations (e.g., "growth", "avggrowth", "trend") apply only to fundamental data. Historical data supports sum, min, max, average, variance, standarddeviation, etc.
periods How far back or which exact date range to utilize. "30", "2019-01-01^2020-01-01", "5y", "4q" For historical data, specify days as an integer or a date range (YYYY-MM-DD^YYYY-MM-DD). For fundamental data, include “y” (years) or “q” (quarters).

Example Usage

Below are sample calls that showcase different ways to use this function. Enter them in any cell in Excel and press Enter.

Basic Examples

• Minimum Low Price in Last 100 Days (Historical)
=mxls_db_query("historical", "MSFT", "low", "min", "100")
Returns the lowest daily low for Microsoft over the last 100 trading days.

• Average of Daily Returns in Last 100 Days (Historical)
=mxls_db_query("historical", "MSFT", "returns", "average", 100)
Computes the mean daily return for MSFT over the past 100 days.

• Sum of Daily Trading Volume in the Last 30 Days (Historical)
=mxls_db_query("historical", "AAPL", "volume", "sum", 30)
Summarizes total volume traded for Apple over 30 days.

Advanced Scenarios

• Date Range for Historical Data (Close Prices)
=mxls_db_query("historical","AAPL","close","average","2021-01-01^2021-06-30")
Retrieves the average close price for Apple between January 1, 2021, and June 30, 2021.

• CAGR Over 5 Years for EPS (Fundamental)
=mxls_db_query("fundamental","MSFT","eps","growth","5y")
Fetches the Compound Annual Growth Rate of Microsoft’s EPS over a five-year period.

• Check if Revenue is Increasing or Decreasing (Trend Detection)
=mxls_db_query("fundamental","MSFT","revenue","trend","5y")
Returns "1" if increasing, "-1" if decreasing, or "0" if mixed over the last five annual periods.

• Average Growth of Long-Term Debt in 5 Years (Fundamental)
=mxls_db_query("fundamental","MSFT","ltdebt","avggrowth","5y")
Calculates the mean annual growth rate of MSFT’s long-term debt over five years.

• Returning All Values (Fundamental)
=mxls_db_query("fundamental","MSFT","revenue","allvalues","5y")
Provides a list-like structure of each revenue data point used in the last 5 annual periods.

• Positive Streak Examples (Fundamental)
=mxls_db_query("fundamental","MSFT","revenue","positivestreak","5y")
Checks how many consecutive periods revenue has been positive (based on the data retrieved).

In all cases, if fewer data points exist than required (e.g., standard deviation with only one data point), the function may return “NA” or display an error message explaining insufficient data.

Common Questions and Troubleshooting

  1. “NA” Returns Frequently:

    • Check your market data subscription and license validity.
    • Ensure the symbol is correct (“MSFT” vs. “MSF” by mistake).
    • Verify the last parameter for fundamental data ends with “y” or “q.”
  2. “Invalid Time Period” or “Error: No valid data points…”:

    • For historical queries, ensure your date format is YYYY-MM-DD^YYYY-MM-DD.
    • Verify you’re not requesting more data than exists for the symbol.
  3. “Insufficient Data Points to Calculate Standard Deviation or Variance”:

    • Make sure your date range or period is large enough to have at least 2 data points.
  4. Handling Growth or Trend Results:

    • Growth returns CAGR for fundamental data when “growth” is specified.
    • Trend returns 1 if data is strictly increasing, -1 if strictly decreasing, 0 otherwise.

With mxls_db_query, you can harness robust analytics directly in Excel—no advanced coding needed. From short-term price movements to extended fundamental statistics, it’s a powerful tool for your MarketXLS-enabled workflow. Experiment with different parameters, keep an eye on the date ranges, and you’ll uncover deeper insights into the stocks you track every day.

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Queries MarketXLS Database and Other Financial Formulas
How does MarketXLS work?