For a deeper look into our Elektron API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
43 1 3 8

Determining UTC datetime from QUOTE_DATE/TRADE_DATE and QUOTIM/SALTIM

We use Refinitiv RTO data for global strategies, so we need last trade and quote times in UTC. The SALTIM and QUOTIM fields are in UTC, but unfortunately, the TRADE_DATE and QUOTE_DATE fields are not UTC. They represent the "trading date," so for exchanges that are open across UTC midnight, these date fields roll over before or after UTC midnight. This means that if I try to build a UTC timestamp using TRADE_DATE and SALTIM, I get the incorrect date for a period of time each day.

For example, consider the CME RIC ESZ2 (CONTEXT_ID 1118, XMIC_CODE XCME) on 2022-10-18. The exchange is open 23 hours a day with a 1-hour trading closure from 4 to 5 PM Central (21:00 to 22:00 UTC during DST). Quote reporting starts at 4:45 and the exchange reopens at 5 PM. QUOTE_DATE and TRADE_DATE roll over to 2022-10-19 on the first quote and trade after the closure at 4:00. However, the correct UTC date is still 2022-10-18. So, for example, the first trade with TRADE_DATE=2022-10-19 has SALTIM_NS=22:00:00, but this trade actually happened on 2022-10-18.

To compute the correct UTC timestamp it appears that I need to know the close time of every exchange for every day. Then I subtract one day from TRADE_DATE and QUOTE_DATE for trades and quotes with TIME between market open and UTC midnight. This is extremely cumbersome and error prone. There must be a better solution. How are other Refinitiv RTO users dealing with this problem?

Some options I have considered:

* Use ACTIV_DATE instead of TRADE_DATE. This works for trades on many exchanges, but does not work for quotes. ACTIV_DATE rolls over on the first trade after UTC midnight, so gives the wrong date for all quotes that arrive after market close and before UTC midnight. This is what I'm doing currently, but now I need the quote data
* Ignore date fields in the message and guess it using the system clock. This is heuristic and error-prone. You can't reprocess archived messages for old dates and you are not guaranteed to get the correct date for the last trade/quote dates computed from the refresh message on connect
* Store market hours and adjust TRADE/QUOTE dates. Maintaining market hours for all exchanges is a huge undertaking. Does Refinitiv provide this data queryable by CONTEXT_ID so I can look it up?
* Other date fields suggested here. None of these roll at UTC midnight for the CME case I mentioned above


websocketstradedatequoteupdate-message
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Upvote
Accepted
17.2k 47 13 19

Hi @aschmied,

You seem to have investigated the time options quite well. Unfortunately, we won't be able to provide that level of details on this developers forum. It is best to talk to a data content specialist by raising a ticket at my.refinitiv.com. Thanks.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for replying. I opened a ticket with them as well.


This seems like a critical issue and I am surprised no one has raised it before. For an exchange that is open on the days before and after a daylight saving time change you could have two different quotes that happen on different days map to the same QUOTE_DATE/QUOTIM and there will be no way to distinguish them. The same can happen for trades.


For example, CME is closed 4-5 Central. QUOTE_DATE rolls over when quotes start rolling in again at 4:45. So on the day of a fall daylight saving time change you get:


2022-11-05

Saturday 1600 CDT = 2100 UTC

Saturday 1700 CDT = 2200 UTC

Quote date rolls to next day with the first quote after 1645 CDT

Quote at 1650 CDT gets QUOTE_DATE=2022-11-06, QUOTIM=2150 UTC


2022-11-06

Sunday 1600 CST = 2200 UTC

Sunday 1700 CST = 2300 UTC

Quote date rolls to next day with the first quote after 1645 CST

Quote at 1550 CDT gets QUOTE_DATE=2022-11-05, QUOTIM=2150 UTC


The quotes at 2022-11-05 2150 UTC and 2022-11-06 2150 UTC are indistinguishable using QUOTE_DATE/QUOTIM. CME is closed Saturday, and I think this only happens when an exchange is open on the day before and after the DST change and the closure is less than an hour. However, I still think this is a big problem because it makes the data extremely difficult to interpret.

It's not the answer I want, I'll accept this because it is an answer. I would still very much like to know how other users are dealing with this issue. I heard back from support and my request has been forwarded to the content enhancement team. I expect it is unlikely to be prioritized unless lots of users are complaining about this.