In this article, we outline how to pull historical market data into Excel while excluding data from days when the market was in a holiday session.
Holiday sessions are trading dates, but not clearing dates. Holiday session data is merged with the next session. For example, July 4 is a US holiday and is a trade date, but the clearing date is July 5.There are RTD formulas for calling open, high, low, and close by date and time. We will use that RTD format here to ignore trading on July 4.
First, pulling daily bar data will always give you clearing dates. Here is the RTD formula for daily dates:
= RTD("cqg.rtd",,"StudyData","EP?", "Bar", "", "Time","D","0","PrimaryOnly",,,"False","T")The cell will show 42936.000000. The number to the left of the decimal is the date and the number to the right is time. The “000000” indicates it is midnight. You have to format the cell to show a date. In this example, the date is 7/20/2017.
To call the open for symbol EP? for the date 7/20/2017 using the Primary Session (8:30 to 15:15), which is 405 minutes, you use this RTD formula:
=RTD("cqg.rtd",,"StudyData","Open(EP?) when (LocalYear(EP?)=2017 and LocalMonth(EP?)=7 and LocalDay(EP?) =20)","Bar","","Close","405",,"PrimaryOnly",,,,"T")The downloadable sample uses a combination of these two RTD formulas to pull in the primary session for clearing dates only. The sample uses linked cell references. For example, the symbol is in cell $D$2.
- Column A: Lookback historical counter
- Column B: Clearing dates
- Column C: Converts column B to a year
- Column D: Converts column B to a month
- Column E: Converts column B to a day
- Columns F through I: O,H,L,C for each date using the Primary Session and 405 minutes.
Make sure to lower your Excel RealTimeData (RTD) throttle to 50 milliseconds or lower. Learn how to do that here.
Requires CQG Integrated Client or CQG QTrader, and Excel 2010 or more recent.