📋
📋
📋
📋
MarketXLS Tutorials
Search…
Welcome to MarketXLS
Getting Started
Signing Up
Installing MarketXLS
Real-time data feed
Functions: How to Use
Function List
mxls_db_query function
How to use Forex functions in MarketXLS
Function: QM_List()
Core concepts
Watch List - Save Stock Tickers
Getting additional data
Connect Interactive Brokers With MarketXLS
Connect ThinkorSwim With MarketXLS
Growth Functions
Crypto Data
Portfolio Management
Using Templates
Perform Technical Analysis (Template)
Dividends
Custom Beta
Options Pricing
Options Pricing
Options Greeks
Utilities Tab in MarketXLS
Option Pricing with Quotemedia
Options Screener
Real Time Stock Option Pricing In Excel
Historical Pricing
Historical Fundamental Functions
Getting Historical Stock Prices in Excel
Create Charts using MarketXLS
Quick Stock and Currency Charts in Excel with MarketXLS
Custom Charts in Excel with MarketXLS
MarketXLS Charts – Candlestick Charts
FAQs
Contact Us
Powered By GitBook
mxls_db_query function
This function allows you to query MarketXLS databases with formulas database on our servers to get useful insights about the price & volume.
Function Structure:
=Mxls_db_query(“What Database”, “What Symbol”, “What do you want to query” i.e. Volume, Open, High, Low, Close, Dividend, Split, returns “, “How do you want to aggregate” i.e. sum, average, max, min, variance, standard deviation, “How far you want to go in days” 1, 100, 200, etc)
The database can be either “historical” or “fundamental”
So, to get the minimum low a symbol has moved to in the last 100 days, type: =MXLS_DB_Query("historical", "MSFT", "low", "min", "100")
To get the average of daily returns of stock in the last 100 days, type:=mxls_db_query("Historical","msft","returns","average",100)
To get the average of the daily returns of MSFT from a specific date in the past to another date uses the function as shown below. The last argument in this function can accept start and end dates in YYYY-MM-DD format as shown below. You will need to separate the dates by a ^ sign as shown in the example below.
This example below will return the average of the daily returns for MSFT from 2019-01-15 to 2020-06-21: =mxls_db_query("Historical","msft","returns","average","2019-01-15^2020-06-21")
You can also get and calculate the analytical functions on the MarketXLS database and quickly get values like Growth Rate (CAGR), trend, average growth as shown below.
CAGR for 5 years of EPS:=mxls_db_query("Fundamental","MSFT","eps","growth","5y")
This gets the trend, increasing or decreasing:=mxls_db_query("Fundamental","MSFT","revenue","trend","5y") Average of annual growth rates:=mxls_db_query("Fundamental","MSFT","revenue","avggrowth","5y")
Sum: =mxls_db_query("Fundamental","MSFT","revenue","sum","5y")
mxls_db_query function can use the following keys for the items. So, for example, if you want to use this function on Long Term Debt use ltdebt as shown in the example below.
Average growth of the long term debt in last 5 years: =mxls_db_query("Fundamental","MSFT","ltdebt","avggrowth","5y")
item#key Loans#loan Investment securities#invSec Lease financing income#leaseFin Other interest income#otherInt Federal funds sold/purchased#fedFnd Interest bearing deposits#intBearDep Loans held for resale#loanHeldRes Trading account securities#tradAccSec Time deposits placed#timeDepPl Other money market investments#othMMInv Total money market investments#totMMInv Total interest income#totIntInc Deposits#deposits Short term debt#stDebt Long term debt#ltDebt Federal funds purchased/securities sold#fedFndPerSec Capitalized lease obligations#capLeaseOb Other interest expense#othIntExp Total interest expense#totIntExp Net interest income (expense)#netIntInc Provision for loan loss#provLoanLoss Trust fees by commissions#trstFeeComm Service charge on deposit accounts#svcChgDepAcc Other service charges#othSvcChg Security transactions#secTrans Premiums earned#premEarn Net realized capital gains#netRealCapGain Investment banking profit#invBankProf Other non-interest income#othNonIntInc Total non-interest income#totNonIntInc Salaries and employee benefits#salEmpBen Net occupancy expense#netOccExp Promotions and advertising#promoAd Property/liability insurance claims#propLiabInsClm Policy acquisition costs#polAcq Amortization def. policy acquisition cost#amortDefPolAcq Current and future benefits#currFutBene Other non-interest expense#othNonInExp Total non-interest expense#totNonIntExp Premium tax (credit)#premTax Minority interest#minorInt Income taxes#incTax Income, acquired in process R&D#incAcqProRND Income, restructuring and M&A#incResMNA Other special charges#othSpChg Special income/charges#spIncChg Net income from continuing operations#netIncContOp Net income from discontinued operations#netIncDisOp Net income from total operations#netIncTotOp Extraordinary income/losses#extraIncLoss Income from cumulative effect of account chg#incCumEff Income from tax loss carry forward#incTaxLossCf Other gains (losses)#otherGains Total net income#totNetInc Normalized income#normInc Net income available for common#netIncAvailCom Preferred dividends#prefDividends Basic EPS from continuing operations#bEPSContOp Basic EPS from discontinued operations#bEPSDisOp Basic EPS from total operations#bEPSTotOp Basic EPS from extraordinary income#bEPSExtraInc Basic EPS from cumulative effect of accounting chg#bEPSCumEff Basic EPS from tax loss carry forward#bEPSTaxLossCf Basic EPS from other gains (losses)#bEPSOtherGains Basic EPS, total#bEPSTotal Basic normalized net income/share#bNormNetIncpS Diluted EPS from continuing operations#dEPSContOp Diluted EPS from discontinued operations#dEPSDisOp Diluted EPS from total operations#dEPSTotOp Diluted EPS from extraordinary income#dEPSExtraInc Diluted EPS from cumulative effects of accounting chg#dEPSCumEff Diluted EPS from tax loss carry forward#dEPSTaxLossCf Diluted EPS from other gains (losses)#dEPSOtherGains Diluted EPS, total#dEPSTotal Diluted normalized net income/share#dNormNetIncpS Dividends paid per share#divPaidpS Revenues, year to date#ytdRev Total operations income, year to date#ytdIncTotOp Diluted EPS total operations, year to date#ytdEPSTotOp Dividends paid per share, year to date#ytdDivPaidpS Cash and due from banks#cash Restricted cash#resCash Federal funds sold/securities purchased#fedFndSec Interest bearing deposits at other banks#intDepOthBank Investment securities, net#invSecNet Loans#bsLoan Unearned premiums#unearnPrem Allowance for loans and lease losses#allLoanLoss Net loans#netLoan Premises and equipment#premEquip Due from customers' acceptance#dueCustAcc Trading account securities#bsTradAccSec Other receivables#othRecv Accrued interest#accInt Deferred acquisition cost#defAcqCost Accrued investment income#accInvInc Separate account business#sepAccBus Time deposits placed#timDepPlc Intangible assets#intangAss Other assets#othAss Total assets#bsTotAss Non-interest bearing deposits#nonIntBearDep Interest bearing deposits#bsIntBearDep Short term debt#bsStDebt Other liabilities#othLia Bankers' acceptance outstanding#bankerAccOut Federal funds purchased/securities sold#fedPurSecSold Accrued taxes#accTax Accrued interest payables#accIntPay Other payables#othPay Capital lease obligations#bsCapLeaseOb Claims and claim expense#claims Future policy benefits#futPolBene Unearned premiums#bsUnearnPrem Policy holder funds#polHoldFnd Participating policy holder equity#partPolEq Separate accounts business#sepAccBusLia Minority interest#minInt Long term debt#bsLtDebt Preferred stock equity#prefStkEq Common stock equity#comStkEq Common par#comPar Additional paid in capital#addPaidInCap Cumulative translation adjustment#cumTransAdj Retained earnings#retEarn Treasury stock#treasuryStk Other equity adjustments#othEqAdj Foreign currency adjustments#forCurrAdj Net unrealized loss/gain on investments#netUnrealLGInv Net unrealized loss/gain on foreign currencies#newUnrealLGFor Net other unearned losses/gains#netOthUnearnLG Total equity#bsTotEq Total liabilities#bsTotLia Shares outstanding common class only#shOutComCl Preferred shares#bsPrefS Total ordinary shares#totOrdSh Total common shares outstanding#totComShOut Treasury shares#treasSh Basic weighted shares outstanding#basicWghtShOut Diluted weighted share outstanding#dWghtShOut Number of employees#numEmp Number of part-time employees#numPTEmp Net income (earnings)#cfNetInc Provision for loan losses#cfProvLoanLoss Depreciation and amortization#depAmort Deferred income taxes#defIncTax Change in assets (receivables)#chgAss Change in liabilities (payables)#chgLia Investment securities gain#invSecGain Net policy acquisition costs#netPolAcqCost Realized investment gains#realInvGain Net premiums receivables#netPremRecv Change in income taxes#chgIncTax Other non-cash items#othNonCashItem Net cash from operating activities#netCashOpAct Proceeds from sale/mat. inv.#procSaleInv Purchase of investment securities#purInvSec Net increase federal funds sold#netIncFedSold Purchase of property & equipment#purPropEq Acquisitions#acq Other investing changes, net#othInvChgNet Net cash from investing activities#netCashInvAct Net change in deposits#netChgDep Cash dividends paid#cashDivPaid Repayment of long term debt#repayLtDebt Change of short term debt#chgStDebt Issuance of long term debt#issLtDebt Issuance of preferred stock#issPrefStk Issuance of common stock#issComStk Purchase of treasury stock#purTreasStk Other financing activities#othFinAct Net cash from financing activities#netCashFinAct Effect of exchange rate changes#effExchRateChg Net change in cash & equivalents#netChgCashEq Cash at beginning of period#cashAtBegin Cash at end of period#cashAtEnd Total risk-based capital ratio#totRiskCapRat Auditor's name#audName Auditor's report#audRep Operating revenue (revenue/sales)#opRev Total revenues#totRev Adjustments to revenue#adjToRev Cost of sales#costOfSales Cost of sales with depreciation#costOfSalesWd Gross margin#grossMargin Gross operating profit#grossOpProfit Research & development expense#rndExpense Selling, gen & administrative expense#genExpense Advertising#advert Operating income#opInc Operating income before depreciation (EBITDA)#ebitDa Depreciation#depreciation Depreciation unreconciled#depunrecon Amortization#isAMort Amortization of intangibles#isAMortInTang Operating income after depreciation#opIncAfterDep Interest income#interestInc Earnings from equity interest#earnEqInt Other income, net#otherInc Income, acquired in process R&D#incProRnd Income, restructuring and M&A#incRestruct Other special charges#othSpecialChg Special income/charges#specialInc Total Income available for interest expense (EBIT)#ebit Interest expense#interestExp Pre-tax Income (EBT)#ebt Income taxes#incTax Minority interest#minorityInt Preferred securities of subsidiary trust#prefSecSubTrust Income before tax#incbfTax Net income from continuing operations#netIncConTop Net income from discontinued operations#netIncDisop Net income from total operations#netIncToTop Extraordinary income/losses#extraIncLoss Income from cumulative effect of accounting chg#incCumEff Income from tax loss carry forward#incTaxLossCf Other gains (losses)#otherGains Total net income#totNetInc Normalized income#normInc Net income available for common#netIncAvailCom Preferred dividends#prefDividends Excise taxes#exciseTax Basic EPS from continuing operations#bepsConTop Basic EPS from discontinued operations#bepsDisop Basic EPS from total operations#bepsToTop Basic EPS from extraordinary income#bepsExtraInc Basic EPS from cumulative effect of accounting chg#bepsCumEff Basic EPS from tax loss carry forward#bepsTaxLossCf Basic EPS from other gains (losses)#bepsOtherGains Basic EPS, total#bepsTotal Basic normalized net income/share#bNormNetIncPs Diluted EPS from continuing operations#depsConTop Diluted EPS from discontinued operations#depsDisop Diluted EPS from total operations#depsToTop Diluted EPS from extraordinary income#depsExtraInc Diluted EPS from cumulative effect of accounting chg#depsCumEff Diluted EPS from tax loss carry forward#depsTaxLossCf Diluted EPS from other gains (losses)#depsOtherGains Diluted EPS, total#depsTotal Diluted normalized net income/share#dnormNetIncPs Dividends paid per share#divPaidPs Revenues, year-to-date#ytdRev Income from total operations, year-to-date#ytdIncToTop Diluted EPS from total operations, year-to-date#ytdEpsToTop Dividends paid per share, year-to-date#ytdDivPaidPs Cash and equivalents#cash Restricted cash#rescash Marketable securities#securities Accounts receivable#accRecv Loans receivable#loanRecv Other receivable#othRecv Receivables#receivables Raw materials#rawMat Work in progress#workInProg Purchased components#purchasedBits Finished goods#fingoods Other inventories#othInv Inventories adjustments & allowances#invAdj Inventories#inventories Prepaid expenses#prepaidExp Current deferred income taxes#bsacurrdefTax Other current assets#othCurrass Total current assets#totCurrass Land & improvements#land Building & improvements#building Machinery, furniture & equipment#equip Construction in progress#consinProg Other fixed assets#othFixAss Total fixed assets#totFixAss Gross fixed assets (plant, property & equipment)#grossFixAss Accumulated depreciation & depletion#accDep Net fixed assets (net PP&E)#netFixAss Intangibles#intangibles Cost in excess#costinxs Non-current deferred income taxes#nonCurrDefTax Other non-current assets#othNonCurrass Total non-current assets#totNonCurrass Total assets#totAss Inventory valuation method#invValMethod Accounts payable#accPay Notes payable#notesPay Short term debt#stDebt Accrued expenses#accruExp Accrued liabilities#accruLia Deferred revenues#defRev Current deferred income taxes#bslCurrDefTax Other current liabilities#othCurrLia Total current liabilities#totCurrLia Long term debt#ltDebt Capital lease obligations#capLease Deferred income taxes#defIncTax Other non-current liabilities#othNonCurrLia Minority interest#minorInt Preferred securities of subsidiary trust#prefSecSub Preferred equity outside stock equity#prefEqOutStk Total non-current liabilities#totNonCurrLia Total liabilities#totLia Preferred stock equity#prefStockEq Common stock equity#commStockEq Common par#commPar Additional paid in capital#addPaidCap Cumulative translation adjustment#cumTransAdj Retained earnings#retEarn Treasury stock#tresStock Other equity adjustments#othEqAdj Total capitalization#totCap Total equity#totEq Total liabilities & stock equity#totLiaEq Cash flow#cashFlow Working capital#workCap Free cash flow#freeCashFlow Invested capital#investedCap Shares outstanding common class only#commShos Preferred shares#prefsh Total ordinary shares#totorDsh Total common shares outstanding#totCommShos Treasury shares#tressh Basic weighted shares outstanding#bwgtshos Diluted weighted shares outstanding#dwgtshos Number of employees#emp Number of part-time employees#ptemp Net income (loss)#netInc Depreciation#dep Amortization#cfamort Amortization of intangibles#cfamortintang Deferred income taxes#defIncTaxOp Operating (gains) losses#opGain Extraordinary (gains) losses#extraAgain (Increase) Decrease in receivables#decRecv (Increase) Decrease in inventories#decInv (Increase) Decrease in prepaid expenses#decPrepExp (Increase) Decrease in other current assets#decOthCurrass (Increase) Decrease in payables#decPay (Increase) Decrease in other current liabilities#decOthCurrlia (Increase) Decrease in other working capital#decOthWorkCap Other non-cash items#othNonCash Net cash from continuing operations#netCashContop Net cash from discontinued operations#netCashdisop Net cash from operating activities#netCashoPact Sale of property, plant, equipment#saleProp Sale of long term investments#saleLtInv Sale of short term investments#salestInv Purchase of property, plant, equipment#purprop Acquisitions#acquisition Purchase of long term investments#purLtInv Purchase of short term investments#purStInv Other investing changes net#othInvChgs Cash from disc. investing activities#cashDisInv Net cash from investing activities#netCashInvAct Issuance of debt#issueDebt Issuance of capital stock#issueStock Repayment of debt#payDebt Repurchase of capital stock#repStock Payment of cash dividends#payDiv Other financing charges, net#othFinChrgs Cash from disc. financing activities#cashDisFinAct Net cash from financing activities#netCashFinAct Effect of exchange rate changes#exchangeRate Net change in cash & cash equivalents#netChangeInCash Cash at beginning of period#cashBegin Cash at end of period#cashEnd Foreign sales#forSales Domestic sales#domSales Auditor's name (aud. name & aud. op.)#audName Auditor's Report (aud. name & aud. op.)#audRep Close price/earnings ratio#closePe High price/earnings ratio#highPe Low price/earnings ratio#lowPe Gross profit margin (profit margin after CGS)#grossProf Pre-tax profit margin#preTaxProf Post-tax profit margin#postTaxProf Net profit margin (PM from total operations)#netProf Interest coverage from continuing operations#intCovconTop Interest as a percentage of invested capital#intPctInvCap Effective tax rate#effTaxRate Income per employee#incPerEmp Normalized close price/earnings ratio#normClosePe Normalized high price/earnings ratio#normHighPe Normalized low price/earnings ratio#normLowPe Normalized net profit margin#normNetProf Normalized return on stock equity#normRetStock Normalized return on assets#normRetAss Normalized return on invested capital#normInvCap Normalized income per employee#normIncPerEmp Quick ratio#quickRat Current ratio#currRat Payout ratio#payoutRat Total debt to equity ratio#totDebtEqRat Long-term debt to total capital#ltDebtTotCap Leverage ratio#levRat Asset turnover#assTurnOvr Cash as a percentage of revenue#cashPctRev Receivables as a percentage of revenue#recvPctRev SG&A expense as percentage of revenue#sgaExpPctRev R&D Expense as percentage of revenue#revPerCash Revenue per dollar, cash#revPerPlant Revenue per dollar, plant (net)#revPerCommEq Revenue per dollar, common equity#revPerInvCap Revenue per dollar, invested capital#recvTurnovr Receivable turnover#invTrnovr Inventory turnover#recvPerDaySales Sales per dollar, receivables#salePerRecv Sales per dollar, inventory#salePerInven Revenue to assets#revToAss Number of days cost of goods sold in inventory#numDaysCostGood Current assets per share#currAssPs Total assets per share#totAssPs Intangibles as percentage, book value#intangPctBook Inventory as percentage, revenue#invenPctRev Long term debt per share#ltDebtPs Current liabilities per share#currLiaPs Cash per share#cashPs Long-term debt to equity ratio#ltDebtEqRat Long term debt as percentage of invested capital#ltDebtPctInvCap Long term debt as percentage of total liabilities#ltDebtPctDebt Total liabilities as a percentage of total assets#totDebtPctAss Working capital as a percentage of equity#wrkCapPctEq Revenue per share#revPs Book value per share#bookPs Tangible book value per share#tangBookPs Price to revenue ratio#prcRevRat Price to equity ratio (price to book)#prcEqRat Price to tangible book ratio#prcTangBookRat Working capital as percentage of price#wrkCapPctPrc Working capital per share#wrkCapPs Cash flow per share#cashFlowPs Free cash flow per share#freeCashFlowPs Return on stock equity (return on equity)#retStock Return on invested capital#retInvCap Return on assets#retAss Price/cash flow ratio#prcCashFlow Price/free cash flow ratio#prcFreeCashFlow Sales per employee#salesPerEmp Sales indicator#salesInd Earnings indicator#earnInd Earnings per share indicator#epsInd Price indicator#prcInd Price book indicator#prcBookInd Price sales indicator#prcSalesInd Price cash indicator#prcCashInd Price free cash indicator#prcFreeCashInd Debt equity indicator#debteqlnd Currency rate indicator#currRatInd Gross profit indicator#grossProfInd Pre tax profit indicator#preTaxProfInd Post tax profit indicator#postTaxProfInd Net profit indicator#netProfInd Return equity indicator#retEqInd Level rate indicator#levRatInd Asset turn over indicator#assTurnOvrInd Research & development expense as percentage of revenue#rndExpPctRev
Previous
Type of functions
Next - Functions: How to Use
How to use Forex functions in MarketXLS
Last modified 11mo ago
Copy link