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:

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

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:

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:

This gets the trend, increasing or decreasing:

Average of annual growth rates:

Sum:

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:

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