This article introduces a collection of RTD formulas that returns time values instead of market or study values.
CQG provides the time of the highest high and the time of the lowest low from the current session via RTD:
=RTD("cqg.rtd", ,"ContractData", "EP", "HIghTime",, "T") =RTD("cqg.rtd", ,"ContractData", "EP", "LOwTime",, "T")
In addition, you can pull in the current time, referred to as Line Time, which is Chicago time:
=RTD("cqg.rtd",,"SystemInfo","Linetime")
All of the above can be formatted in Excel to display the time as hh:mm:ss or hh:mm:ss AM/PM.
If you want a different time than Chicago time, simply add or subtract the number of hours divided by 24. For example, here is New York time:
=RTD("cqg.rtd",,"SystemInfo","Linetime")+1/24
The RTD call for line time also includes the date. If you format the above RTD formula as a number, you will see something such as: 43178.631875.
Everything to the left of the decimal is the date. Everything to the right of the decimal is time. If you want to use just the date, then use the TRUNC function:
=TRUNC(RTD("cqg.rtd",,"SystemInfo","Linetime"))
If you want to use just the time, then use the MOD function, which returns the remainder after the number is divided by the divisor. In this case, 1 is the divisor:
=MOD(RTD("cqg.rtd",,"SystemInfo","Linetime"),1)
Closing Alert
Next, you can highlight a cell based on the current line time within one minute of the close of the session.
Here is the RTD call for the primary session closing time (which, for example, is 3:15 PM line time for symbol EP):
=RTD("cqg.rtd", , "ContractData","EP","PrimarySessionCloseTime")
To adjust this to be one minute less, use the time function, Time(hours,minutes,seconds), and subtract one minute, which returns 3:14 PM:
=RTD("cqg.rtd", , "ContractData","EP","PrimarySessionCloseTime")-TIME(0,1,0)
Using a combination of “IF THEN” and “AND” functions, we can create a condition that returns a 1 if the line time is equal to and between 3:14 and 3:15:
=IF(AND(MOD(RTD("cqg.rtd",,"SystemInfo","Linetime"),1)>=RTD("cqg.rtd", , "ContractData","EP","PrimarySessionCloseTime")-TIME(0,1,0),MOD(RTD("cqg.rtd",,"SystemInfo","Linetime"),1)=RTD("cqg.rtd", , "ContractData","EP","PrimarySessionCloseTime")),1,0)
In Excel, we can highlight a cell, such as the long description of a futures contract, if the above condition is true.
Here is a link to an Excel help file for more information about using conditional alerts in Excel:
Use formulas with conditional formatting
The downloadable sample spreadsheet includes all of the above RTD examples and a closing alert where the long description is highlighted with a red background.