Worksheet formulae

Simple formula

The simplest formula =RTD("quotein.rtd",, <symbol>, <field>, <feed>) and is used to return level 1 quotes. This formula is compatible with older versions.

Generic formula format

To support more complex requests QuoteLink uses a system based on passing a string of <key>= <field> pairs. For example:

"SYMBOL=IBM | TYPE=QUOTES | FIELD=LAST| FEED=IQ"

A text string like this one fully, specifies all necessary fields for a quotes request. Generally the text string is not written in a single part as above but it is built by concatenating the different pairs <key>= <field> using the RTD formula as I'll now explain:

The generic Excel RTD formulae uses the format: = RTD("quotein.rtd",, argument1, argument2, argument3, ...)

When processing this formula Quotelink will concatenate the arguments: argument1, argument2, ... into a single text string as above. It also adds a separator '|' after each argument.
Thus both the formulae:

=RTD("quotein.rtd",, "SYMBOL=IBM | TYPE=QUOTES | FIELD=LAST| FEED=IQ")

=RTD("quotein.rtd",, "SYMBOL=", "IBM", "QUOTES=", "LAST", "FEED=", "IQ")

result in the same string request

"SYMBOL=IBM | TYPE=QUOTES | FIELD=LAST | FEED=IQ"

Since all necessary fields for a QUOTES request have been specified, it will actually get the quote into the cell.

If the terms necessary for a specific request have not been all specified the request will return the concatenation of the terms.

The resulting string can then be the input of other formulae where other fields may be appended completing the request.

This chain system allows to group common terms and then use them repeatedly in multiple quotes.

As an example suppose I'm only interested in the LAST field for a large number of symbols. Using this system I can write in a corner the common parameters to all requests in an auxiliary formula:

=RTD("quotein.com",,"TYPE=QUOTES|FIELD=LAST|FEED=IQ|SYMBOL=")

assuming that the cell containing the above formula is A1 I can now repeat the must simpler formula:

=RTD("quotein.com",,$A$1, <symbol> ) this formula will then be repeated and will concatenate A1 contents with the <symbol>

The <key>= <field> pairs necessary fields for each of the requests are the following:

LEVEL I QUOTES

TYPE=QUOTES

SYMBOL=<symbol> (optional defaults to IBM)

FIELD=<field> examples: BID, ASK, LAST, ... (optional defaults to LAST)

FEED=<feed> (optional defaults to the current feed)

SERIES (HISTORICAL and INTRADAY)

TYPE=SERIES

SYMBOL=<symbol> (optional defaults to LAST)

PERIOD=in minutes if nothing said 1, 2 ...; or in seconds if 1 s, 5 s (optional defaults to 1 minute)

FIELD=OPEN, HIGH, LOW, CLOSE, ... (optional defaults to CLOSE)

START= optional aligns the bar to the given time. (useful to start any period at any time; also used in futures only to specify the exact start of the day bar - the value should be Excel time which is the fraction of the day)

FEED=<feed> (optional defaults to current feed)

DAYS=<number of days to request>;

STREAM=<1 for on 0 for off>;

DEPTH QUOTES (futures and Level 2)

TYPE=DEPTH

SYMBOL=<symbol> (optional defaults to @ES#)

FIELD=NAME, BID, BID SIZE, ASK, ASK SIZE, (optional defaults to CLOSE)

MMID= < MMID order > may be -1,-2,-3,-4, ... for best bids ans 1,2,3, ... for best asks

FEED=<feed> (optional defaults to current feed (IQ or IB atm))

Stock options Chains

TYPE=LIST

WHAT=SYMBOLS

WANT=EXPIRATIONS

CLASS=IEOPTION

SYMBOL=<symbol> underlying stock

INDEX=0,1,2 ... N peeks a value in the resulting list>

FEED=<feed> (optional defaults to current feed)

Future options FOPs

TYPE=LIST

WHAT=SYMBOLS

WANT=EXPIRATIONS

CLASS=FOPTION

SYMBOL=<symbol> underlying future

INDEX=0,1,2 ... N peeks a value in the resulting list

FEED= < feed name > (optional defaults to current feed) then for each expiration it is possible to get the valid strikes:

TYPE=LIST

WHAT=SYMBOLS

WANT=STRIKES

EXPIRATION=<:format YYMMDD>:

CLASS= IEOPTION or FOPTION

SYMBOL=<:symbol>

INDEX=0,1,2 ... N peeks a value in the resulting list>

FEED=<feed name> (optional defaults to current feed)

Future Chains

TYPE=LIST

WHAT=SYMBOLS

CLASS=FUTURE;

SYMBOL=<symbol>

INDEX=0,1,2 ... N peeks a value in the resulting list>

FEED=<feed> (optional defaults to current feed)

List of all fields available for a feed for instruments of the same type as _symbol_

TYPE=LIST

WHAT=FIELDS

SYMBOL=<symbol>> the symbol is used to represent its class of instrument

INDEX=0,1,2 ... N peeks a value in the resulting list>

FEED=<feed> (optional defaults to current feed)

IQFeed stats

Same information available is in IQConnect icon.

TYPE=LIST

WHAT=SYSTEM

FIELD= one of: ServerIP, ServerPort, MaxSymbols, NumberOfSymbols,ClientsConnected, SecondsSinceLastUpdate, Reconnections, AttemptedReconnections,StartTime, MarketTime, Status, IQFeedVersion, LoginID, TotalKBsRecv, KBsPerSecRecv, AvgKBsPerSecRecv, TotalKBsSent, BsPerSecSent, AvgKBsPerSecSent

FEED=<feed > (only IQ works for now)

Portfolio

FEED=feed optional (only OX or IB based Note:you can use multiple accounts from OX, IB working at the same time)

TYPE=LIST

WHAT=POSITIONS

ACCOUNT=<account> this optional ; if omitted the default account is considered

INDEX=0,1,2 ... N peeks a value in the resulting list>

FIELD=for OX one of SecurityType, PositionID, AccountID, Symbol, OXSymbol, UnderlyingSecurity, SymbolDescr, Quantity, optionmultiplier, InsertDate,PutOrCall, StrikePrice, ExpMonth, ExpYear, AccntNum, CusipNumber, CostBasis, Bid, Ask, Last, Price, DisplayInTick, DisplayDenominator, GainLoss, Value, QuoteHasData, Close, MarketId, PositionDelta

Publishing data

TYPE=PUT

SYMBOL=<symbol>

FEED=<custom feed>

FIELD=<the field to publish to >

VALUE=<value to publish>

The published value becomes available locally for all QuoteLink applications and across the LAN in LAN mode