xlq formulae are exactly the same as standard Excel formulae, i.e. the same rules apply

To indicate you are entering a formulae = is used followed by the name of the formula with the list of parameters enclosed in brackets
e.g. =xlqPrice("msft") or =xlqPrice("aapl","xlq2") - if you do not provide all parameters, default values are used, i..e the first will use the default source as set in xlq preferences.

Parameters may be entered directly into the formula or referenced in another cell

When entering parameters directly in a formulae string values are entered in quotes, and number directly. Dates are also entered in quotes (if not they are treated as a calculation "1/1/22" = 1 January 2022, 1/1/22 = 1 divided by 1 divided by 22). A formulae may also be entered as a parameter in which case the results of that value are passed.

When reference in another cell, the result of the cell is used and can be string, number or another formula

To find a formula name you can use the demo spreadsheets as a reference, or the resource menu on the website

You may also use autocomplete, i.e. as you type the formula into an excel cell it will propose a list that match (see image in 'formulae and parameters' above.

You may also use excels Fx button (or press shift-F3) and browse to the formulae you want. Once selected required parameters are shown, along with descriptions

if the formula is already entered in the cell using Fx will take you directly to parameter help

How the results of a formulae are shown will depend on the formatting of the cell.

Basic formatting will show thousand separators, number of decimals, date layout, and can also cover color, fonts, and text orientation

There are multiple ways to format a cell, from clicking on the tool bar, to using the formatting dialog (shortcut ctrl-1)

The format painter may also be used to quickly format cells based on an existing one.

It is also possible to create custom formatting when needed.

When a cell is referenced in a formula, e.g. =xlqprice(A1), it is considered relative, i.e. if the cell is copied down 1 row the reference will automatically refer to B1, or copied to the right. will refer to A2

To make the reference absolute $ is used. Wherever =xlqprice($A$1) is copied to it will always refer to A1

It is also possible to make only the column absolute ($A1), or the row (A$1).

To toggle absolute / relative you may press F4

Correctly specifying absolute / relative references when a formula is entered allows for faster editing of a sheet afterwards by copy / pasting or dragging cells.

Using Copy / Paste

You may also Use ctrl-C to copy ctrl-V to paste, and ctrl-H to show the replace dialog

Dragging cells

All xlq formulae start with =xlq.

Formulae from a day source, i.e. real-time or delayed will be simply xlqPrice, xlqBid, xlqName etc.

For historic data the formulae will begin with xlqh, i.e. xlqhClose, xlqhDate, xlqhOpen etc. for daily data

Most historic formulae will also have weekly (W) or monthly (M) equivalents, i.e. xlqhwClose, xlqhmDate etc.

Many calculations are also applied as data is retrieved based on predefined parameters set in xlq preferences, and are called in the same way e.g. xlqhADX, xlqhwRSI etc.

Historic calculations also have an xlqXh variant that allow you to specify all additional parameters to calculate over different time frames etc. on the fly with (also with Weekly and Monthly variants) i.e. xlqxhADX, xlqxhwRSI etc.

Range formulae (xlqRh xlqrhw, xlqrhm)are also available returning the results between 2 dates, e.g. xlqrhHighestClose etc.

All historic calculation and range formulae are also available for x minute intraday analysis via the historic intraday formulae xlqxhi.. and range xlqrhi.
These formulae work with additional parameters to specify the date, time, interval and whether to work with market hours data or also include extended hours.

Other data may also be returned from an historic source, such as statistics, upcoming events etc. These will also begin with xlqh and the sample spreadsheets should be consulted for full lists.

Parameters

The order of parameters are also standard, with the most basic being symbol, source, if parameters are not supplied, the defaults are used

e.g. =xlqprice("msft") will use the default source, and in this case the xlq2 source is specified =xlqNextEarningsDate("tsla", "xlq2")

For xlqh, xlqhw, xlqhm formulae the parameter order is symbol, date, source, i.e. xlqhclose("msft",-1) using the default source and prior close, or xlqhRSI("aapl","2/2/22","tda") using TDAmeritrade for 2nd February 2022

For xlqxh formulae etc the order is symbol, data, extra parameters..., source e.g. =xlqxhBollingerBandsMiddle("nflx",-1,20,2,0,"norgate-us") will return the middle Bollinger Band for last close for Netflix over 20 periods and a standard deviation of 2, based on close from Norgate.

For Range (xlqrh) it is symbol. from date, to date, source, i.e. =xlqrhHighestCloseDate("ZM","1/1/22",-1,"yahoo") will return the date of the highest close since 1 January 2022 and last close.

If using the historic intraday the above examples could be expanded
=xlqxhiBollingerBandsMiddle("nflx",-1,1130,3, 0, 20,2,0,"IB") would return the middle  BB for prior trading day at 11am for a 3 minute interval calculated on market hours data only.
=xlqrhiHighestCloseDate("ZM","2/21/23",930,"2/23/23",1500,1,1,"IB") would return the date / time for the highest close between 21 Feb 9:30 and 23 Feb 15:00 based on a 1 minute interval and included extended hours.

An exception applies to the wrapper functions (xlqFx and xlqFxStatic) which allow all xlq formulae to be passed as a parameter. The order of parameters will always be Formula name, symbol, source, extra parameters so the same cell references could be used for example for =xlqFx("xlqname","meli","iqfeed") requiring no extra parameters and =xlqFx("xlqhclose","meli","iqfeed",-1) requiring a date, and =xlqFx("xlqxhRSI","meli","iqfeed",-1,14) also requiring the number of periods.

In excel dates are simply a number representing the number of days since 1 January 1900 (date limit) so 1 = 1 Jan 1900, 2 = 2 Jan 1900 and 44562 = 1 Jan 2022.

Time is the fractional part of a number, i.e. midday on January 1 2022 would be 44562.5. Likewise 1 hour would be 1/24 or 0.041667, so 2am would be 44562.08333

The actual date / time shown in a cell would depend on the cell formatting, regional settings etc.

If A1 contained 1 Jan 2022, =xlqhclose("msft",A1) would be the same as =xlqhclose("msft",44562) or =xlqhclose("msft,"1/1/2022") When a date is entered directly as a parameter it needs to be in quotes to avoid it being treated as a calculation

When an exact date is passed as a parameter xlq will return the value for that date, or the prior date if it falls on a holiday or weekend so =xlqhclose("msft,"1/1/2022") would actually return the data for 31 December 2021.

To avoid needing to know if the market was open you can work with relative dates instead, where -1 is last close, -2 the trading day before etc.

There are also helper functions to determine the relative date from an actual date to work with a series in the past. See xlqdemo.xlsx for examples and full list.

When working with relative dates and basic historic functions or calculations (xlqxh formulae etc.) it is also possible to specify a date of 0 to use data from the day source and continue the calculation up to last price, i.e. =xlqhclose("msft,0) is the same as =xlqprice("msft") and =xlqxhCCI("msft",0,20,"ib") will calculate the 20 day commodity channel index up until last trade compared to -1 being last close.

For weekly formula any day within the week may be used as reference, and -1 refers to the current partial week. xlqhwdate will return the Monday data for the week referenced.

Same for monthly with xlqhmdate returning the fist of the month referenced and -1 being the current partial month

The same concept applies for data containing future dates, such as EODHD earnings or options, where -1 will be the end date of series, i.e. the most in the future, -2 the one before etc.

There are also 'valid date or next' formulae which will return the following valid date in a series if the date is not valid, rather than the prior.

It is possible to use xlq with VBA either calling the formulae in VBA functions and procedures, or running a vba routine controlling results in cells via xlq functions.

When using xlq formulae with VBA the xlqfxstatic wrapper should be used as it bypasses cell updating optimizations and allows for updating to be controlled by VBA.

e.g. a basic custom function MyxlqPrice calling xlqPrice with the supplied parameter would look like this:

  
Public Function MyxlqPrice(symbol As String)
    MyxlqPrice = Run([xlqFxStatic], "xlqPrice", symbol, "iqfeed")
End Function
  

The next example is a little more complex. It is not a recommended use but covers different topics

  
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Sub KeepLooping()
    Sheets("Sheet1").Select
    Range("A1") = "=xlqFxStatic(""xlqprice"",""BTC.USD[PAXOS,CRYPTO,USD]"",""ib"")"
    Dim Price As Double
    Do While True
        Application.CalculateFull
        Price = Range("A1")
        If (Price < 31000) Then
            Range("B1") = "Below 31000"
        Else
            Range("B1") = "Above 31000"
        End If
        If (Price < 30000) Then
            MsgBox "Alert Low Reached"
            Exit Do
        ElseIf (Price > 32000) Then
            MsgBox "Alert High Reached"
            Exit Do

        End If
        Sleep 10000 'i.e. 10 seconds in milliseconds
    Loop
End Sub

It shows a procedure that once started will write an xlq formulae to A1 then continuously monitoring the value in cell A1, writing results based on the cell value to B1, and ending with a message box if certain criteria are reached.

For the example A1 contains =xlqFxStatic("xlqprice","BTC.USD[PAXOS,CRYPTO,USD]","ib"), i.e. the streaming real-time value for bitcoin. As xlqFxStatic is used the value will be updated every time at the start of the loop by CalculateFull

if =xlqPrice("BTC.USD[PAXOS,CRYPTO,USD]","ib") had been used the value would be updated in real-time while the macro was not running, but would not have updated while VBA was looping.

The macro loops again after a delay of 10 seconds. Sleep is used as it allows other excel processing to be performed. To exit the macro keep the escape key pressed until the Visual Basic 'Code execution has been interrupted' dialog is shown

The next example does the same as the above but allows for real-time updating from xlq (without xlqFxStatic) in the cells as well using the results in a process running on a timer

  
Dim Timer
Sub StartTimer()
    Sheets("Sheet1").Select
    Range("A1") = "=xlqPrice(""BTC.USD[PAXOS,CRYPTO,USD]"",""ib"")"
    Timer = DateAdd("s", 10, Time)
    Application.OnTime Timer, "CalledOnTime"
End Sub

Sub CalledOnTime()
    Dim Price As Double
    Price = Range("A1")
    If (Price < 31000) Then
        Range("B1") = "Below 31000"
    Else
        Range("B1") = "Above 31000"
    End If
    If (Price < 30000) Then
        MsgBox "Alert Low Reached"
    ElseIf (Price > 32000) Then
        MsgBox "Alert High Reached"
    Else
        Timer = DateAdd("s", 10, Time)
        Application.OnTime Timer, "CalledOnTime"

    End If
End Sub