Today's Synthetic Spread Time of High and Low Using RTD

CQG Integrated Client and CQG QTrader customers can pull the time the high and low occurred during today’s session into a Microsoft Excel® spreadsheet using these RTD formulas:

=RTD("CQG.RTD", ,"ContractData", "EP", "HighTime",, "T")

=RTD("CQG.RTD", ,"ContractData", "EP", "LowTime",, "T")

The RTD formulas only work for exchange-traded symbols, both outrights and exchange-traded spreads. You cannot use synthetic spreads.

As a workaround, you can pull today’s open, high, low, and close data of a synthetic spread into Excel using 5-minute bars. You can then use Excel's VLOOKUP function to find the bar time of the high and low.

Here is an example of how to do that. We begin with the required symbol of the synthetic spread. Here is the crack spread using the CQG Bar function:


The Bar function is the CQG Spread Bar chart. The 1 at end is the interval CQG uses to build the open, high, low, and close bars. This example is a 1-minute snapshot of the spread value, which is used to build 5-minute bars. You enter the symbol in cell T2.

Some markets open the previous night, such as crude oil (symbol CLE), which opens at 17:00 line time. Other markets open today, such as the Euro Bund (symbol DB), which opens at 1:00 line time.

The sample spreadsheet pulls 5-minute bar data up to the first bar of the session. You need to enter "True" in cell S5 if you are following a market that opens during the previous day’s night session. Otherwise, enter "False."

In cell R6, enter the symbol you want to use for the opening time of the session.

Excel will display the data up to and including the opening of the session. Then, Excel will find the highest high and lowest low and display those values in cells I2 and J2. Next, Excel scans columns K and L using VLOOKUP and returns the date and time of the high and low in cells N2 through P2 below the high (cell N1) and low (cell P1).

Requires CQG Integrated Client or CQG QTrader, data enablements for all symbols used in the spreadsheet, and Excel 2010 or higher.



Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.