Historical Futures Trades Timestamped 1 Minute Early

[Rusty]

This post documents a problem with historical downloaded futures tick data. My data service is Barchart.com. Another user has reported a similar problem with another service on an unrelated thread. [https://www.sierrachart.com/supportb...73#post159873] As I don't want to hijack that thread, I'll report my findings here.

1. What is the problem?

When historical CME GLOBEX contracts are downloaded by Sierra/DDFPlus, the trades are timestamped 1 minute early.

2. Which products are affected?

At least the following: ES, SP, NQ, ND, E6, and ZB.

3. How is the problem demonstrated?

I have verified this by a number of methods. However, the easiest way is to check the last minute of the futures session close (15:14 CDT) and the first minute of the next day's session open (15:30 CDT). The historical data consistently shows no trades in the last minute of the close and shows trades commencing at 15:29 CDT before the next day's open. The ES and NQ contract timestamps clearly stop 1m before EOD and start 1m before the next day's session. The SPH10 contract has timestamps in the maintenance period at 16:58 CDT. The E6H10 contract stops 2m before the maintenance period and restarts as early as 16:59 CDT. And ZBH10 stops 2m before the maintenance period and restarts as early as 16:59 CDT. ND is too thinly traded to tell. The following is a summary of 10-day checks performed for contracts that were downloaded in June. (Times are EDT)
- ESU10 10 days through 6/25/10, timestamps stop at 16:13:59 or 16:14:00, start 16:29:00.
- ESM10 10 days through 3/26/10, timestamps stop at 16:13:59 or 16:14:00, start 16:29:00.
- ESH10 10 days through 3/5/10, timestamps stop at 16:13:59 or 16:14:00, start 16:29:00.
- ESZ09 10 days through 12/15/09, timestamps stop at 16:13:59 or 16:14:00, start 16:29:00.
- ESU09 10 days through 9/09/09, timestamps stop at 16:14:01, start 16:29:00.
- SPH10 10 days through 3/5/10, timestamps in maintenance period at 17:58.
- NQM10 10 days through 3/16/10, timestamps stop at 16:13:58 or 16:13:59, start 16:29:00+.
- NQU10 10 days through 6/25/10, timestamps stop at 16:13:58 or 16:13:59, start 16:29:00+.
- NQH10 10 days through 3/5/10, timestamps stop at 16:13:58 or 16:13:59, start 16:29:00+.
- NDH10 10 days through 3/5/10, hard to tell, thinly traded.
- E6H10 10 days through 3/5/10, no timestamps later than 16:58, as early as 17:59 (maintenance period 17:00 - 18:00).
- ZBH10 10 days through 3/5/10, no timestamps later than 16:58, as early as 18:29 (maintenance period 17:00 - 18:30).
The clearest way to state these results is that the historical data never reports trades in the last minute of today's futures session and always reports trades in the minute before the next day's session begins.

There are other ways to demonstrate this problem, e.g. comparing the times of the HOD and LOD reported by the CME to the historical data, and looking at the Open of the RTH trading session (I have spot checked this and found the volume spike for the historical data starts at 8:29 CDT, a minute before the Open). But this is the easiest way.

4. Do the same contracts, collected in real-time, exhibit the problem?

No. ES and NQ tick data, collected in real-time, doesn't have early timestamps. For example: (Times are EDT)
- ESU0 5 days through 7/9/10, timestamps stop at 16:15:01, start 16:30:01.
- NQU0 5 days through 7/9/10, timestamps stop at 16:15:01, start 16:30:01.
In contrast, the downloaded historical NQU10 contract DOES have early timestamps. In other words, the historical data is 1 minute earlier than the real-time data over the same period. For example:
- NQU10 5 days to 7/9/10, timestamps stop at 16:14:00, start 16:29:00.
The real-time collected data always reports trades in the last minute of the current futures session and never reports trades in the minute before the next day's session begins.

5. How far back does the problem extend?

That depends on when you downloaded the historical data. I first downloaded Barchart historical data on 11/20/08 using SC v357. The ESZ8 and NQZ8 contracts have correct timestamps in September, October, and November of 2008 (I checked 10 days before 9/20/08, 10 days before 10/10/08, and 10 days before 11/20/08). I backfilled ESU9 and NQU9 at EOD on 8/10/09 using SC v406 and got good timestamps (ESU9 ends session at 16:15:01, starts new at 16:30:01; and NQU9 ends session at 16:14:59, starts new at 16:30:01).

But by 1/15/10, when I backfilled ESH0 and NQH0 at EOD via SC v444, the timestamps had gone bad (ESH0 ends session 16:14:00, and NQH0 ends session 16:13:59). Right now, using SC v569, the early timestamps affect all contracts at least as far back as the Barchart Extended Ticks service allows data to be downloaded, i.e. one year back (7/14/09, e.g. the ESU09 contract).

6. Is the problem in the Barchart historical database?

No. I've been in communication with Barchart and described the problem. They sent me ESU10 data files for the 6/24/10 Open and the 6/25/10 Close which were generated by direct queries on their database. This data was not transmitted by Sierra/DDFPlus but was emailed to me. All the trades are correctly timestamped and they exhibit none of the problems I have described. After analyzing the files, I inserted my historical data to show the early timestamps, and my real-time data for comparison. The modified files are attached to this post.

The 6/24/10 Open file is "ESU10 100624 1629-1631 BC+H+RT 100714.csv." The first six columns are labeled "Barchart 'Open' 6/24/10 15:29-15:31" and contain the data extracted by Barchart from their database. The next three columns are labeled "BC Historical 6/24/10 15:29-15:31". This is historical ESM10 data I downloaded, which has the bad timestamps. The last three columns are labeled "BC Real-time 6/25/10 15:13-15:16". This is data I collected in real-time from the Barchart service. The query I used to extract the data from my files was for the time window 15:29 to 15:31, which matched the Barchart query. I matched the rows from all three files by Price and Size, not by Timestamps, so it is obvious where the timestamps differ and by how much. It is clear that the downloaded historical data is timestamped 1m early (to the second). The data collected in real-time lags by 4-5 seconds, as one might expect (but not necessarily like). Where matches exist, the Price and Size data agree perfectly; and there are no gaps in the samples.

The 6/25/10 Close file is "ESU10 100625 1613-1616 BC+H+RT 100714.csv" The first six columns are labeled "Barchart 'Close' 6/25/10 15:13-15:15" and contain data extracted by Barchart from their database. The next three columns are labeled " BC Historical 6/25/10 15:13-15:16". This is historical ESM10 data I downloaded, which has the bad timestamps. The last three columns are labeled " BC Real-time 6/25/10 15:13-15:16". This is data I collected in real-time from the Barchart service. The query I used to extract the data from my files was for the time window 15:13 to 15:16. I have aligned the rows by Price and Size, not by Timestamps, as above. It can be seen that my downloaded historical data starts at 15:14 when the trades actually occurred, but the trades are timestamped starting at 15:13, a minute early (to the second). The data collected in real-time lags by 2-5 seconds, as before. Again, the Price and Size data agree perfectly where matches exist; and there are no gaps.

Barchart is at a loss to explain the results I'm getting. But one thing is clear: when Barchart accesses their historical database (which is the same database Sierra accesses for downloads/backfills), they get the right timestamps. When I download the database though Sierra/DDFPlus, I get bad timestamps. However, when I collect the data in real-time via Sierra/DDFPlus, I get reasonably delayed timestamps.

7. Is the setup correct for the downloads?

I hope so. The downloads/backfills were performed with Sierra v569. Similar results were experienced earlier this year with v444. I performed the downloads over a number of days/times. Several charts were used to cross-check the validity of the settings.

There is more data and additional tests which have been done to substantiate this assertion, should they be desired. There is a related long-standing problem, that I will post here tomorrow. Thank you for considering this matter.

Config: W2K SP4 - .NET Framework 2.0 SP1 - Sierra Chart v569 - VS 2005 (8.0.50727.7600) - VC# 2005 Express - VC++ 2005 Express

[SC_SupportGroup]

Rusty, we don't have time to read through all of this. One or two sentences would have been fine. In the past Barchart.com 1 minute data timestamps were always one minute ahead of time. They had confirmed this. And this is something that we noticed. So we deducted one minute from their timestamps. Apparently their 1 minute timestamps now indicate the starting time of the data. Please use version 617, we removed the adjustment.

[SC_SupportGroup]

After looking into this some more, and your files, we see that Barchart.com historical tick data was also adjusted. We thought originally you were referring to 1 minute data because we knew that was adjusted. Tick data never should have been adjusted. Only 1 minute data. Clearly this was a significant programming error. Probably it went on so long because the effect of it was rather subtle.

[Rusty]

Thank you for following up on this anomaly. As I understand it, the timestamp adjustment for Barchart.com historical tick data has been removed in v617 (Release). That will cure the problem going forward. And the historical futures tick data I have downloaded can be repaired with the Adjust Data feature.

My real concern is for the Barchart real-time data which I have collected that has gaps filled with bad timestamped data. Do you know in which SC version the 1 minute adjustment for tick data was incorporated? I need to go back and sanitize files collected since then.

[Rusty]

Originally Posted by SC_SupportGroup

...Probably it went on so long because the effect of it was rather subtle.

There is another subtle problem when backfilling tick data from Barchart in real-time. I reported it to Barchart in early January, but Richard was out of town, and it received scant attention. (As it turns out, it's probably not their problem.)

The problem occurs when there is a disconnect, connectivity is re-established, and SC subsequently makes a request to backfill the gap. SC formats the request for a block of data that covers the gap by subtracting a minute from the time of the start of the gap and submits it. When the trades are returned, the logic adds a minute to the start of the gap to access the first record for the backfill, then subtracts a minute from its timestamp (as described in your posts above). The net effect is that, at the start of the backfill, the timestamps between the real-time collected data and the backfilled data are continuous, but the data has a one-minute gap. SC continues from there, incrementing the record pointer and subtracting a minute from each timestamp. At the end of the backfill, there is a one-minute jump in timestamps between the backfill and the real-time data, but the data is continuous.

Consider this actual case. On 1/15/10, during Barchart real-time data collection, a series of disconnects started at 11:20:18 EST, with a connection finally being made at 11:31:40, leaving a gap of about 11 minutes. SC began downloading missing data for 7 symbols, the last of which was ESH0, starting at 11:31:44. The log says it requested intraday data for ESH0 starting at 1/15/2010 11:19:19 AM, and that it received records from 11:19:19 AM to 11:30:35 AM. Note that the last timestamp in the backfill is more than a minute behind the actual time, suggesting a 1 minute void at the end. Since the backfill request took less than 1 minute to process, a 1 minute void is unjustifiable. What actually happened is that out of the 6704 records that were retrieved, SC started filling with a trade that was timestamped 11:21:18 (one minute after the start of the gap at 11:20:18) and changed its timestamp to 11:20:18 (subtracted a minute) and ended filling with a trade that was timestamped 11:31:35 (9 seconds before the end of the gap) and changed its timestamp to 11:30:35 (subtracted a minute). So that the timestamps were continuous at the start of the backfill but not at the end; and the data had a 1 minute gap at the beginning of the backfill and was (almost) continuous at the end.

I have attached the relevant portion of the message log and a CSV file which shows the trades from the ESH0.scid-file versus the CME Group DataMine Time & Sales record to verify the timestamps. (Note that message log times are MDT, ESH0 timestamps are EDT, and CME T&S times are CDT. Also, I have excised the middle of the 11 minute backfill to reduce file size. Find the start and end of the backfill by searching for "*****" in column A.)

Other users have identified this problem, believing that it was the failure of the driver to queue real-time data during the backfill that was causing the data gap. There was no resolution reported. [https://www.sierrachart.com/supportb...d.php?t=19063]

Perhaps this has already been corrected in conjunction with backing out the timestamp adjustment. But it wasn't explicitly mentioned, and it really needs to be fixed. Thank your for your consideration.

[SC_SupportGroup]

Do you know in which SC version the 1 minute adjustment for tick data was incorporated?

No. Investigating this is too time-consuming. We know that it was done close to the time we added support for Barchart.com. All of this is confusing, because we added that adjustment and at some point later, barchart.com 1 minute data became timestamped at the beginning of the minute rather than at the end.

The issue described here:
https://www.sierrachart.com/supportb...ad.php?t=19063
about not getting all of the data up to the current moment when historical data is downloaded, was indeed a barchart.com issue having to do with how they queue and send the data. We pointed this out to them more than a year ago, but they offered no solution. Therefore, after the initial download is done, we make a second one. So therefore we indirectly resolved the issue.

[SC_SupportGroup]

This is not the case, how do you come to this conclusion?:

When the trades are returned, the logic adds a minute to the start of the gap to access the first record for the backfill

Edit: after continuing to read what you wrote, you come to this conclusion through an observation. However, this is not how it works and we don't see this.

[SC_SupportGroup]

This is confusing and we cannot see this is how SC is handling data from Barchart.com. Probably your perspective on what's happening is different than the way we see it.

Consider this actual case. On 1/15/10, during Barchart real-time data collection, a series of disconnects started at 11:20:18 EST, with a connection finally being made at 11:31:40, leaving a gap of about 11 minutes. SC began downloading missing data for 7 symbols, the last of which was ESH0, starting at 11:31:44. The log says it requested intraday data for ESH0 starting at 1/15/2010 11:19:19 AM, and that it received records from 11:19:19 AM to 11:30:35 AM. Note that the last timestamp in the backfill is more than a minute behind the actual time, suggesting a 1 minute void at the end. Since the backfill request took less than 1 minute to process, a 1 minute void is unjustifiable. What actually happened is that out of the 6704 records that were retrieved, SC started filling with a trade that was timestamped 11:21:18 (one minute after the start of the gap at 11:20:18) and changed its timestamp to 11:20:18 (subtracted a minute) and ended filling with a trade that was timestamped 11:31:35 (9 seconds before the end of the gap) and changed its timestamp to 11:30:35 (subtracted a minute). So that the timestamps were continuous at the start of the backfill but not at the end; and the data had a 1 minute gap at the beginning of the backfill and was (almost) continuous at the end.

We ran a test and we do not see anything like this. Probably what you are describing was in part related to the timestamp adjustment of 1 minute.

[Rusty]

Originally Posted by SC_SupportGroup

No. Investigating this is too time-consuming...

All right. I have narrowed the start of the problem to sometime between 8/10/09 and 1/15/10 and will investigate further if my collected data is needed for backtesting.

Originally Posted by SC_SupportGroup

The issue ... about not getting all of the data up to the current moment when historical data is downloaded, was indeed a barchart.com issue having to do with how they queue and send the data. We pointed this out to them more than a year ago, but they offered no solution. Therefore, after the initial download is done, we make a second one. So therefore we indirectly resolved the issue.

I verified that SC v618 (Release) "Re-requested most recent data" after a long backfill. Thank you.

[Rusty]

Originally Posted by SC_SupportGroup

Edit: after continuing to read what you wrote, you come to this conclusion through an observation. However, this is not how it works and we don't see this.

It was presumptuous of me to hypothesize how the programming logic works without the code.

Originally Posted by SC_SupportGroup

This is confusing and we cannot see this is how SC is handling data from Barchart.com. Probably your perspective on what's happening is different than the way we see it.

My analysis was flawed. I assumed the gap started when the driver state first changed from connected to disconnecting. In retrospect I understand that it started about a minute before that. Also, the message log showed that the backfill data which was received started a minute before the gap. I concluded the request was bad, but my conclusion was wrong. The request was correct, but message log (erroneously) showed the start and end of the received records AFTER the 1-minute timestamp adjustment had been made. The net effect is that, after the backfill was completed, the file was missing a minute of data at the start of the gap, a minute of timestamps at the end of the gap, and all the timestamps in the gap were offset 1 minute.

Originally Posted by SC_SupportGroup

We ran a test and we do not see anything like this. Probably what you are describing was in part related to the timestamp adjustment of 1 minute.

The 1-minute adjustment sufficiently explains the observations I made. I have verified that SC v618 (Release) solves the tick data timestamp problem for large downloads and for short data gaps (I reported them as two related problems, but they actually stemmed from the same root cause). Thank you for implementing the correction.

[SC_SupportGroup]

Thank you for pointing out the issue.