Introduction

Day formulae allow you to work the most recent quote information, which depending on the source can be updated in real-time

These formulae all have the same syntax, i.e. =xlqFunctionName("Symbol", "Source") e.g. =xlqPrice("msft","tradier").

Sources
  • CoinMarketCap - X minute updates for many cryptocurrencies
  • EODHD - EODHistoricalData provide delayed snapshot data for many exchanges, including for forex, crypto, commodities etc. (requires xlq2)
  • FMP - Financial Modeling Prep provides realtime snapshot data for numerous worldwide markets including forex, crypto, commodities etc. (requires xlq2)
  • IB - Interactivebrokers provide streaming real-time data for the many worldwide exchanges and products they cover, including for equities, indexes, options, futures, forex etc. etc.
  • IQFeed - provide streaming real-time millisecond precision data for the many exchanges and products they cover, including for equities, indexes, options, futures, forex etc. etc.
  • Schwab - Schwab provide real-time snaphsot data for US equities, indexes, currencies, futures and futures options.
  • Tiingo - provide real-time snapshot data for US and Chinese equities, funds and crypto currencies.
  • Tradier - provide streaming real-time and snapshot data for US equities, options and indexes via 2 sources (tradier and tradier2)

List

xlq2Lite and xlq2
xlq2

EODHD, FMP and Norgate also provide some of the below formuale via their historic data, often with additional detail. Please consult xlq2demo.xlsx for more info.

xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier
Name Name / Description
Exchange The name or location of the symbol's exchange.
Price The last price traded
Bid The bid price
Ask The ask price
Price2 History and trend of price
Price2x with additional settings on price change
Bid2 History and trend of bid
Bid2x with additional settings on price change
Ask2 History and trend of ask
Ask2x with additional settings on price change
Midpoint The midpoint between bid and ask
LastSize Size of last trade
BidSize Size of last bid
AskSize Size of last ask
Change Days change in price
ChangePercent Days change in price as %
Open Days opening price
PreviousClose Previous days closing price
High High for the day
Low Low for the day
Volume Number of shares bought and sold during the day
AverageVolume Average number of shares bought and sold
TypicalPrice Typical price (High + Low + Last)/3
13WeekLow Lowest price for the stock over the last 13 weeks
13WeekHigh Highest price for the stock over the last 13 weeks
26WeekLow Lowest price for the stock over the last 26 weeks
26WeekHigh Highest price for the stock over the last 26 weeks
52WeekLow Lowest price for the stock over the last 52 weeks
52WeekHigh Highest price for the stock over the last 52 weeks
TradeTime Date / time of last trade
LastUpdate Date / time of last update for symbol
SymbolCount the number of symbols that are currently active for a day source
UninitializedSymbolCount the number of symbols for the source that have not yet received any data from the feed. When a connection with a streaming feed is reset the count will again include all the symbols until re-initialized
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier
PricePerEarnings Price / Earnings ratio
PricePerEarningsMkt Price / Earnings ratio on market price
Yield Current percentage dividend yield based on the present cash dividend rate.
MarketCapVal Market Capitalization (multiplying the current Price by the current number of Shares Outstanding).
SharesOutstanding Number of shares of common stock currently outstanding.
PricePerBook Current Price divided by the latest quarterly book value per share
EarningsPerShare Earnings / share as per source (trailing)
EPSEstCurrentYear Current Year analyst consensus EPS estimate or forward eps as provided by the data source.
VWAP Volume Weighted Average Price
Range Days Range, difference between high and low
Spread Spread - difference between ask and bid
PcntOffAveVolume Volume % off Average Volume
ChangeFromOpen Change From Open
ExchangeOfLastTrade Exchange Of Last Trade
MarketHoursChange Market Hours change, i.e. change only during trading hours
ExtTradingDifference Extended Trading Difference i.e. difference between market hours price and last price
IsMarketOpen 1 if market is open if not 0
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier
NoOfTradesToday No. of trades today
VolumeRate Volume / minute
TradeRate Trades / minute
MarketHoursLast Market hours last price
BidChange Last change in bid price
AskChange last change in ask price
BidTime Time of bid change
AskTime Time of ask change
BidExchange Exchange of bid
AskExchange Exchange of ask
Volatility Volatility
PreviousDaysVolume Previous Days Volume
CalendarYearHigh Calendar year high
CalendarYearLow Calendar year low
CalendarYearHighDate Calendar year high date
CalendarYearLowDate Calendar year low date
52WeekHighDate 52 week high date
52WeekLowDate 52 week low date
SecurityType Security Type e.g. stock /index / option
DividendAmount Dividend amount paid
DividendRate Dividend rate
DividendPayDate Dividend pay date
ExDividendDate Ex Dividend date
5YearGrowthPcnt 5 Year Growth %
FiscalYearEnd Fiscal Year End (month)
InstitutionalPcnt Institutional %
Beta Beta
CurrentAssets Current Assets
CurrentLiabilities Current Liabilities
BalanceSheetDate Balance Sheet Date
LTDebt Long term debt
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier
HistoricVolatility Historic Volatility
ImpliedVolatility Implied Volatility
BidImpliedVolatility Bid Implied Volatility
MidImpliedVolatility Mid Implied Volatility
AskImpliedVolatility Ask Implied Volatility
ORATSFinalImpliedVolatility ORATS Final Implied Volatility
Split1Ratio Split Ratio (last)
Split1Date Date of last split
Split2Ratio Split Ratio (prior)
Split2Date Date of prior split
SIC SIC (Standard Industrial Classification)
Underlying Underlying Symbols of options
RootOptionSymbols Root Option Symbols (separated by space)
LeapSymbols LeapSymbols
NetAssetValue Net Asset Value (Mutual funds)
OpenInterest Open Interest (options / futures)
Strike Strike
Settle Settle
Delay Delay
AverageMaturity Average Maturity
7DayYield 7 Day Yield
ExpirationDate Expiration Date
DaysToExpiration Days To Expiration
TickID unique tick identification no. (IQFeed)
Currency Currency
OptionCallOpenInterest Option Call Open Interest
OptionPutOpenInterest Option Put Open Interest
OptionCallVolume Option Call Volume
OptionPutVolume Option Put Volume
Delta Delta
Gamma Gamma
Vega Vega
Theta Theta
Rho Rho
Phi Phi
GreeksUpdateTime Greeks Update Time
ContractSize ContractSize
OptionExpirationType Option Expiration Type
OptionType Option Type
Average Option Volume AveOptionVolume
ShortableShares Shortable Shares
IsShortable Will return the following values:
0 is not shortable, .
1 (Schwab) will return 1 if it is shortable, .
2 (IB) The contract will be available for short sale if shares can be located .
3 (IB) There are at least 1000 shares available for a short sale
IsHalted Is Halted
AuctionPrice Auction Price
AuctionVolume Auction Volume
AuctionImbalance Auction Imbalance
PriceNonQualifying It is possible to include / exclude non qualifying (odd lot) trades with IQFeed, and still view the last N Q trade here
SizeNonQualifying Size of the last non qualifying trade
TimeNonQualifying Time of the last non qualifying trade
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier
MarketHoursChangePercent Market Hours change %
MarketHoursLastSize Market Hours Last Size
MarketHoursTradeTime Market Hours trade time
DividendDeclaredDate Dividend Declared Date
DividendFrequency Dividend Frequency
DividendNextExDate Next Ex Dividend Date
DividendNextPayDate Next Dividend Pay Date
LastEarnings Last Earnings Date
MoneyIntrinsicValue Intrinsic Value
Multiplier Multiplier
TheoreticalOptionValue Theoretical Option Value
ImpliedYield Implied Yield
Underlying Underlying
Option Type Option Type
Mark Mark Price
Mark Change Mark Price Change
MarkChangePercent Mark Change %
Cusip Cusip
AverageVolume10 Average Volume 10 day
AverageVolume3M Average Volume 3 Month
PRRatio Price Revenue Ratio
PCFRatio Price Cash Flow Ratio
QuickRatio Quick Ratio
CurrentRatio Current Ratio
PEG Ratio Price Earings to Growth Ratio
ReturnOnEquity Return On Equity
ReturnOnAssets Return On Assets
GrossMarginTTM Gross Margin TTM
GrossMarginMRQ Gross Margin MRQ
OperatingMarginTTM Operating Margin TTM
OperatingMarginMRQ Operating Margin MRQ
ReturnOnInvestment Return On Investment
InterestCoverage Interest Coverage
TotalDebtToCapital Total Debt To Capital
LTDebtToEquity LTDebt To Equity
TotalDebtToEquity Total Debt To Equity
EPSChangePercentTTM EPS Change Percent TTM
EPSChangeYear EPS Change Year
EPSChange EPS Change
RevenueChangeYear Revenue Change Year
RevenueChangeTTM Revenue Change TTM
RevenueChangeIn Revenue Change In
MarketcapFloat Marketcap Float
RevenueChangeTTM Revenue Change TTM
BookValuePerShare Book Value Per Share
ShortInterestToFloat Short Interest To Float
ShortInterestDaysToCover Short Interest Days To Cover
DividendGrowthrate3Year Dividend Growth rate 3 Year
ttmEPS ttmEPS
NextEarningsDate Next Earnings Date
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier
Delete Allows for a symbol to be removed from the xlq data engine programmatically.

With special parameters xlqDelete("RESETCONNECTION","IQFEED") or xlqDelete("RESETCONNECTION","TDA") it is also possible to reset the streaming feed for those sources.
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier

Rank
24hVolume
AvailableSupply
TotalSupply
MaxSupply
PercentChange1h
PercentChange24h
PercentChange7day
PercentChange30day
PercentChange60day
PercentChange90day
MarketCapitalizationDiluted
MarketCapDominance
xlqSelfReportedCirculatingSupply
xlqSelfReportedMarketcap
xlqVolumeChange24h
xlqTotalValueLocked
xlqTotalValueLockedRatio
Extra formulae supported by CoinMarketCap
In addition to Name, LastPrice, MarketCapVal, TradeTime, LastUpdate
 xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier

xlq2OptionSymbolConvert
xlq2OptionSymbolBuildCall
xlq2OptionSymbolBuildPut
Special formulae allowing for an option symbol to be converted from the syntax of one source to another as well as to construct the symbol per source based on underlying values (date, strike etc.)
xlq...  Description  EODHD  FMP  IB  IQFeed  Schwab  Tiingo  Tradier

      

XLQ view

It is also possible to view the day data directly in xlq

Right click the Q on the task bar (or press alt-ctrl-shift-Q) and choose View data / Day View (streaming) or Day List

Examples

For additional information press excel's insert function button 'fx'

 

=xlqPrice("gs","tradier2") will return the last streaming realtime price for GS from tradier

=xlqPrice("EUR.USD[IDEALPRO,CASH,USD]","ib") will return the last eur / usd rate from InteractiveBrokers.

=xlqBid("AAPL 241115C00100000","schwab") will return the last bid for 15 November 2024 $100.00 option call for Apple from Schwab.

=xlqNoOfTradesToday("aapl","iqfeed") will return the number of trades for today for Apple from IQFeed

=xlqChangePercent(O$1) will return the change as a % for the symbol in O1 from the default source.

=xlqFx("xlqPrice","gs","tradier") alternative way of entering formula using xlqfx

=xlqFxStatic("xlqPrice","gs","tiingo") same as above but will only update when alt-ctrl-f9 is pressed.

Additional examples of all formulae in xlqDemo.xlsx included with the download.
New Tips and Tricks Page
  • A quick reference guide to using xlq2 with excel including shortcuts, tips etc.

  • Examples and explanations for all user levels.
Also available via the Resource menu above, along with references for formulae, symbols etc.

LATEST NEWS:

12 September 2024

XLQ2 24.41 released

  • xlqPrice2x, xlqBid2x and xlqAsk2x

    Specialized versions based on price differences to return

    • up to 20 prior values
    • the trend
    • as positive or negative depending on movement.
  • Schwab

    includes all the latest updates.

    Option Chain functionality now supported and Schwab view added

  • New option date formulae

    To calcualte a trend and target price for a given expiry date based on volume and open interest

  • More. >>

Current Offers

1 year of upgrades included
Purchase xlq2Lite for $159
Purchase xlq2 for $299

Pleased with the service and support
or feel extra has been earned?

Your acknowledgement is appreciated.
Thank you

User Comments

It is so uncommon in this day and age to receive top-level, human support and I appreciate that immensely. The quality, breadth and utility of your product is incredible, but even more impressive is the level of support you provided to me, just one individual customer. You may quote me on that. Thank you.

- C.B.

Wow, you did a lot of work on this version. New interface and other things as well. I must give you credit for being the most responsive software engineer that I've ever encountered. Every question that I've sent you over the many years (since 2003!!!) has been answered quickly and professionally. I just wanted to let you know that it is appreciated.

- M.N.

I have to say Leo that the last two versions have made some tremendous improvements to what has been for years an outstanding application. Thank you for your continued efforts.

- C.A.

Just to let you know that, the new version works much better & faster than the older one. I like it a lot, fantastic job you did!!!

- R.S.

Many thanks indeed for your clear and very rapid reply. I am not used to this type of quality service - I thought it went out of fashion years ago !!

- P.G.

In only 40 some days XLQ has helped me with a number of research projects I couldn't possibly have done without it and I'm now using it to help manage and track current trades and to alert me to potential new trades.

- D.P.

I just wanted to take a moment and thank you for creating XLQ. I use it regularly for scanning/monitoring securities in Excel 2007.  I have programmed in trailing stops for securities I own and numerous techniques using indicators for securities on my watch list. The combination of Excel 2007 and XLQ has opened up nearly an unlimited capability that is only constrained by my imagination.

- E.H.

Thank you for the amazing package -- I use it daily and it's ridiculously useful! Others (.....) have added some of the features you have, but none of them even comes close to matching what you've built.

- R.B.

Your product is fantastic, amazing, and really strong. It has real enabling capability. Thank you for putting this into the market!

- R.G.

You have an exceptional customer service, you have always answered my questions, I thank you for that, made it really easy to use this software, will be your long term customer. Thanks.

- A.D.

I was halfway resigned to have to program something like this myself. Only it would have taken me forever, and I couldn't have done it half as well. Thanks!

- M.S.

I also want to thank you for making the XLQ program. It is something that is very useful and incredible helpful. Like it was made for my needs.

- J.T.

I have already modified your demo sheet to fit my needs.  Your program allows me to turn my excel spreadsheet into a total information center that updates automatically. It's elegant.

- W.H.

First, I want to say that your program is probably the finest piece of work I have ever seen. The depth is simply amazing. Congratulations and thanks.

- D.B.

Thanx, Leo.  You're a great sport.  By the way, partially because of the application of xlq in my screen, I have passed 30% up in the market this year!!!!

- B.H.

This is a wonderful product -- simple to use and just plain useful!

- C.B.

I want to say you have created THE ULTIMATE data interface software for traders like me who prefer to use Excel for everything. Thanks a bunch.

- M.Y.

I just wanted to say, that I very much like your xlq package. I have tried many similar products and this is by far the best I have come across. Simple, fuss-free setup, integrates well with Excel, and has its own stand alone interface to track your portfolio throughout the day. Since I am from Europe, and trade US, European and Japanese securities, the ability to use a variety of feeds simultaneously and transparently is particularly valuable to me.

- H.S.

I really appreciate your quick responses. And your program is terrific. The Plus version adds a lot with it‘s AAII interface. I’ll definitely register it since in the last week it has become an indispensable part of my stock analysis.

- R.S.

I found XLQ yesterday afternoon – and I am blown away by how useful it is. You really need to market this thing because it is AWESOME!!!

- D.B.

Your explanation was very good. I understand now and find it very useful. If you always reply so quickly to questions, I will have to believe that you supply the best and fastest support of any software company.

- D.H.

I have “dabbled” for several years creating many of the things you present in this great program. I first began using the “*.iqy” external query (update times horrendous), then Excel VBA and lately attempting to learn “C” or “C++” in order to have smoother, faster updates as well as a great appearing “front end”. Your program certainly is, by far, the leader and has the greater edge over any I have seen, or tried to accomplish. Thank you for such a great program.

- R.H.

You are a very special person to reply so quickly and I want to thank you again. I enjoy your program. It gives me everything I need, and I know there is so much more functionality that I'm not even exploiting.

- F.D.

I love your program. XLQ combined with the power of Excel brings limitless possibilities to manipulate data for better trading. I recommend it to everyone I know interested in trading.

- P.S.

Thanks for such a great program. I have no programming experience and was trying to find a way to do just what you have done. Only you did much more. It’s fantastic and reasonably priced too!

- D.T.

Thank you so much for your help and your phenomenal programming. XLQ is awesome.

- T.K.

I use your XLQ product all the time. Some of the best money I've ever spent on software.

- L.S.

First, a thank you for the excellent and useful adjunct to EXCEL. I seem to discover a new use every day – and I’m sure I haven’t even scratched the surface to utilize XLQ’s full potential. The new version is really great.

- W.H.

First let me say that I feel XLQ is terrific. I really appreciate the versatility you have made possible with this package. I have set up a few worksheets and find it easy to learn and use with the aid of the included demo sheets. This application has the potential in my opinion to be unmatched in power, efficiency and productivity.

- C.A.

Your software is on its way to becoming the only window that I need to the market’s numbers. With the ability to arrange thing EXACTLY the way I want them, and the use of conditional formatting to trigger alarms and display changes to suit my needs, I am gradually replacing all of the other market tools I’ve been using. I can’t be a more enthusiastic supporter of what you are doing. Thanks.

- W.G.

I'm wowed by your software!! I used to enter data in my spreadsheet for two hours for each update, and therefore only did it once or twice a week. Now I do it with the close each day - in about 30 seconds!!! And I've added some data points to help with my analysis that I just could not manage to maintain before. Thanks again for a great program, a great deal, and great support!!!!!

- R.K.

Your software is great. I'm hooked on the info I can generate for stock purchasing and selling using your program. In two weeks I've become an XLQ junkie.

- V.D.

Please let me tell you how much I have come to depend on XLQ in a short period. This is a great product!! You have made my job significantly more streamlined, and my tasks simpler and more elegant. Many, many thanks!

- L.D.

I have been evaluating XLQ for the past few weeks and am thoroughly impressed with the product. It is versatile, easy to use, accurate and very intuitive. It is a real bargain compared to competitive products plus it runs right from within Excel. Based on this I just entered my purchase order.

- M.M.

I ADORE your program. It has really helped me to learn about how the markets work, and building my own system indicator by indicator is the best way to learn.

- B.K.

Leo: count me as a satisfied customer. I gave it a good workout for a month, decided to buy it when it saved me 5 hours of administration the first week, and helped me do some studies I had been postponing for months due to slow data retrieval. Your product is smooth, fast and elegant with a very clean presentation. Well done!

- K.L.

The more I work with XLQ, the more I like it. It is fast! And so rich... I like it so much I have decided to adopt it as my #1 tool for the long term.

- S.C.

Thanks very much for writing XLQ - I like it very much as it gives me the opportunity to manipulate data as I want without a very steep learning curve.

- T.B.

I just downloaded a trial of XLQ and have been "playing" with it for hours. I am so impressed. I wish I would have known about your program years ago.

- D.F.

I just purchased your software and think it is the next best thing since the spreadsheet.

- R.D.

Thanks for the best (and least complicated) investing software out there!!

- F.B.

Your software is simply brilliant, for the last week that I have been trying it, I have been impressed no end.

- G.R.

Thanks, Xlq is a great piece of software, its actually saving me a heap of time and time is the precious commodity in these hectic days.

- J.P.

The XLQ interface with Excel is excellent. I have been waiting for this capability for years.

- H.A.

Please accept my thanks for your wonderful product - it is a pleasure to use.

- D.L.

Your software is just what I have needed. It provides me with the capability to customize data and construct charts which give me the views I like - not just what someone thinks I would like.

- D.E.

Firstly, let me say that the philosophy and idea behind XLQ is exactly what I am looking for.

- S.P.

The more I use XLQ the more I appreciate what can be done with it.

- S.M.

Just like to say, excellent work. A potentially good bit of software for me.

- M.C.

I love your product so far. ... Thank you for making such product available.

- A.T.

Thanks for the Tip. You know, your program is really super.

- P.B.

Great software! Simple, elegant, well-thought-out and fast.

- R.M.

I am always finding new ways to use your program in my investment / speculative Market endeavors.

- S.S

Thanks for such a wonderful product. It certainly does handle my most precious need which is to monitor the market realtime..

- W.B

It is a pleasure doing business with such a responsive person who obviously understands the value of good customer service. I have just registered and intend to make good use of this program.

- E.D.

Your fast and thorough answer is more than what I expected. Your service is awesome and your product is very stable. Keep up with your good work. Sincerely appreciated :)

- V.P.

Just a note to let you know that I really like you software. It's a wonderful interface that has been of great utility to me.

- J.B.

Join the XLQ2 Users Group

Books

xlq2 has been recommended in the following:

Definitive Guide to Position Sizing

Van Tharp

Paul King