Formatting Price Data using RTD and Excel

When writing formulas for calling market prices into Microsoft Excel® using RTD, you can choose between four different formats for the values returned. Here, I will review those formats and offer a custom view of fixed income market data that includes a dash in the price.

Below are four Excel formulas to call for the day's last trade with data formats used (T, B, F, and D) shown at the end of the formula.

=RTD("cqg.rtd",,"ContractData","TYA?","LastTradeToday",,"T") = 121.734375

=RTD("cqg.rtd",,"ContractData","TYA?","LastTradeToday",,"B") = 12123+

=RTD("cqg.rtd",,"ContractData","TYA?","LastTradeToday",,"F") = 12123+^

=RTD("cqg.rtd",,"ContractData","TYA?","LastTradeToday",,"D") = 121235

Using "T" returns the price in a decimal format. Using "B" returns the value in 32nds and is formatted as a text cell. If using "F," you must format the Excel cell font with CQG Swiss font. You will see "+" and "^" for an uptick and the down arrow for a down tick. This cell is also set to text format. Finally, if you use "D," the default format (if you do not enter anything in the formula) will return the native number.

A CQG customer showed me another cell formatting method using the Dollar Fraction function, (DOLLARFR(price, fraction)).

Example:

=DOLLARFR(RTD("cqg.rtd",,"ContractData","TYA?","LastTradeToday",,"T"),32) = 121.235

Here, Excel sees the price as a decimal and converts the price to 32nds.

If you would like to see the classic view with a dash in the bond price, you can use "&" to merge cells. The Excel sample in this blog post takes in price as a decimal, converts it, separates the decimal price into the handle and 32nds, and then merges it back together. Cell F10 adds an extra zero if the price is less than ten 32nds, such as 121-02.5.

Here are some examples using the LastTradeToday function:

Cell B10: =RTD("cqg.rtd",,"ContractData", "TYA", "LastTradeToday")

Cell C10: =TRUNC(B10)

Cell D10: =B10-C10

Cell E10: =D10*32

Cell F10: =IF(E10<10,IF(E10<1,0,0),"")

Cell G10: =C10&"-"&F10&E10

These same formatting techniques apply to other markets with fractions in their prices, such as the grain markets.

Download the sample spreadsheet ‌‍‍‍‍