Preopening Excel(R) RTD Calls

After the current trading session ends, many exchanges clear out today’s market data to set up for the preopening posting of orders to the exchange's order book. This includes today’s open, high, low, and close. In effect, the exchange moved today’s data to be yesterday’s data. Consequently, if you are using Excel and RTD calls to displays today’s open, high, low, and close, and the market moves into the preopening state, you will see blank values in Excel until the evening session opens.

In Excel, using a simple “If Then” with RTD formulas to check for a price versus a blank cell will solve this problem. For example, this Excel formula checks for today’s open and, if there is no opening price (a blank cell), then the formula calls for yesterday’s opening price. When the market does open then Excel displays current opening price.

=IF(RTD("cqg.rtd",,"ContractData",A2,"Open",,"T")="", RTD("cqg.rtd",,"ContractData",A2,"Y_Open",,"T"), RTD("cqg.rtd",,"ContractData",A2,"Open",,"T"))

Substituting High, Low, and Last Trade Today for Open in the above RTD formula solves the problem. One issue to note is there is not an RTD formula for yesterday’s net change. To pull that information yesterday’s net change is replaced with pulling the difference between the two previous daily bar’s closing prices:

=IF(RTD("cqg.rtd", ,"ContractData",A2, "NetLastTradeToday",, "T")="",RTD("cqg.rtd",,"StudyData",A2, "Bar", "", "Close","D","-1",,,,,"T")- RTD("cqg.rtd",,"StudyData",A2, "Bar", "", "Close","D","-2",,,,,"T"),RTD("cqg.rtd", ,"ContractData",A2, "NetLastQuoteToday",, "T"))

The downloadable Excel sample has a collection of Excel formulas for dealing with the preopening state. The symbols are in column A.

Does not require a particular CQG version or Excel version.




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.