mxls_db_query Function

Query MarketXLS databases with powerful formulas to get useful insights about price, volume, and fundamental data directly from our servers.

Function Structure

Parameters:

  • Database: "historical" or "fundamental"
  • Symbol: The stock symbol (e.g., "MSFT")
  • Query Type: Volume, Open, High, Low, Close, Dividend, Split, Returns, etc.
  • Aggregation: sum, average, max, min, variance, standard deviation
  • Time Period: Number of days (e.g., "100") or date range (e.g., "2019-01-15^2020-06-21")

Historical Database Examples

To get the minimum low a symbol has reached in the last 100 days:

To get the average of daily returns in the last 100 days:

To get average daily returns between specific dates:

Date Format for Ranges

When using date ranges, use the format "YYYY-MM-DD^YYYY-MM-DD" with a caret (^) separating the start and end dates.

Fundamental Database Examples

You can calculate analytical functions on the MarketXLS database and quickly get values like Growth Rate (CAGR), trend, and average growth.

CAGR for 5 years of EPS:

Get the trend (increasing or decreasing) of revenue:

Average of annual growth rates for revenue:

Sum of revenue over 5 years:

Using Item Keys

The mxls_db_query function uses specific keys for fundamental items. For example, to work with Long Term Debt, use the key ltdebt:

Average growth of long term debt in the last 5 years:

Common Item Keys

Income Statement

  • totRev - Total revenues
  • opInc - Operating income
  • netInc - Net income
  • eps - Earnings per share
  • ebit - EBIT
  • ebitDa - EBITDA

Balance Sheet

  • totAss - Total assets
  • ltDebt - Long term debt
  • totEq - Total equity
  • cash - Cash and equivalents
  • retEarn - Retained earnings
  • totLia - Total liabilities

Cash Flow

  • netCashOpAct - Net cash from operations
  • netCashInvAct - Net cash from investing
  • netCashFinAct - Net cash from financing
  • freeCashFlow - Free cash flow
  • cashFlow - Cash flow

Ratios & Metrics

  • retStock - Return on equity
  • retAss - Return on assets
  • currRat - Current ratio
  • quickRat - Quick ratio
  • levRat - Leverage ratio

Powerful Analysis

The mxls_db_query function enables sophisticated financial analysis directly in Excel without needing to download and manipulate large datasets. Perform trend analysis, growth calculations, and statistical operations on historical and fundamental data with simple formulas.
← Previous: QM_List FunctionNext: Forex Functions →