Excel integration with TT

Excel integration with TT

RTD: Retrieving instrument IDs and properties

When retrieving instrument data using RTD, you need to specify the ID of the instrument and the properties you want to retrieve.

Retrieving instrument IDs

Before you can access information about an instrument, you must first extract its ID using one of the following methods:

  • By name
  • By fills

By name method

=RTD("tt.rtd",,"Inst","exchangeName","instrName")

Note: The second parameter is the name of the external server running the RTD Server. As the TT RTD Server always runs locally, you must omit a value for the second parameter or supply an empty string (“”). However, you must account for the parameter in the formula.

The instrName argument corresponds to what is commonly called the short name for the instrument.

For example, if you want to retrieve the ID for the Jun16 GE futures contract from the CME exchange, you need only enter the following formula in a cell.

=RTD("tt.rtd",,"Inst","CME","GEM6")

After processing the formula, Excel displays the result in the cell, similar to the following:

You can now use the result shown in the cell to identify the instrument in other formulas in the spreadsheet. Typically, you would reference the cell (B3, in this case) to identify the instrument in other formulas.

By fills method

You can use the following formula to retrieve the IDs for all instruments for which you have traded at least one contract:

=RTD("tt.rtd",,"Inst","FILLS",CELL("Address",cell-reference))

Note: The second parameter is the name of the external server running the RTD Server. As the TT RTD Server always runs locally, you must omit a value for the second parameter or supply an empty string (""). However, you must account for the parameter in the formula.

This formula returns a one dimensional vertical array of the instrument IDs. To display this entire array, you would specify this same formula in a series of cells in Excel where each has a different value for the cell-reference argument. An empty string will be returned if you reference a member of this array that has no value.

The cell-reference values take the form "column,row". For example:

You can now use the result shown in the cell to identify the instrument in other formulas in the spreadsheet. Typically, you would reference the cell to identify the instrument in other formulas.

Retrieving instrument properties

Once you have an instrument ID, you can use the following formula to retrieve properties associated with the instrument:

=RTD("tt.rtd",,inst-id,property,filter1,filterN)

The TT RTD Server supports two types of properties you can retrieve from TT:

Retrieving instrument properties: type 1

Type 1 properties for an instrument are those which return a single value and have no optional filters. They can be extracted with an RTD formula of the form:

=RTD("tt.rtd",,inst-id,property)

Note: The second parameter is the name of the external server running the RTD Server. As the TT RTD Server always runs locally, you must omit a value for the second parameter or supply an empty string (“”). However, you must account for the parameter in the formula.

For example, if you stored the string "Last" in cell A13 and the instrument ID in cell B3, you could extract the last traded price in TT display format as follows:

=RTD("tt.rtd",,B3,A13)

After processing the formula, Excel displays the result in the cell, similar to the following. Note that the "$" in the cell references are Excel mixed reference notations.

Retrieving instrument properties: type 2

Type 2 properties for an instrument are those which return a single value and have optional filters. They can be extracted with an RTD formula of the form:

=RTD("tt.rtd",,inst-id,property,filter1,filter2,filterN)

Note: The second parameter is the name of the external server running the RTD Server. As the TT RTD Server always runs locally, you must omit a value for the second parameter or supply an empty string (“”). However, you must account for the parameter in the formula.

You can use the Account attribute as a filter to limit the fills for which the TT RTD Server calculates values. For example, you might want the quantity of sell orders (SellQty property) of an instrument only if it was submitted through a specific account (Account attribute).

Note: As noted, you must specify at least one Account filter for some type 2 properties.

The format for a filter parameter in the formula is:

<property><operator><value>

For example, if you stored the string "AvgOpenPrice#" in cell A33, the string "Account=ATRSim" in cell A31, and the instrument ID in cell B3, you could extract the average open price in decimal as follows:

=RTD("tt.rtd",,B3,A33,A31)

After processing the formula, Excel displays the result in the cell, similar to the following. Note that the "$" in the cell references are Excel mixed reference notations.