Changing delimiter in CSV data from TRTH Tick history
Hi,
Did something change in the TickHistoryTimeAndSalesExtractionRequest CSV delimiter recently?
When pulling the following query from 2018-12-05
{'method': 'POST',
'url': 'https://hosted.datascopeapi.reuters.com/RestApi/v1/Extractions/ExtractRaw',
'json': {'ExtractionRequest': {'@odata.type': '#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.TickHistoryTimeAndSalesExtractionRequest', 'ContentFieldNames': ['Trade - Price','Trade - Volume', 'Trade - Qualifiers','Trade - Sequence Number'], 'IdentifierList': {'@odata.type': '#ThomsonReuters.Dss.Api.Extractions.ExtractionRequests.InstrumentIdentifierList', 'InstrumentIdentifiers': [{'Identifier': 'HSIZ8', 'IdentifierType': 'Ric'}], 'ValidationOptions': {'AllowHistoricalInstruments': True}}, 'Condition': {'ReportDateRangeType': 'Range', 'QueryStartDate': '2018-12-04T00:00:00', 'QueryEndDate': '2018-12-05T00:00:00'}}}, 'stream': None}}
I get the following data (unzipped), note `Seq. No.` now has a colon `;` delimiter.
#RIC,Domain,Date-Time,Type,Price,Volume,Qualifiers,Seq. No.
HSIZ8,Market Price,2018-12-05T09:14:00.148335969+08,Trade,26788,8,3[ACT_FLAG1];707327933366141519[TRADE_ID],
HSIZ8,Market Price,2018-12-05T09:14:00.148335969+08,Trade,26788,1,2[ACT_FLAG1];707327933366141519[TRADE_ID],
If I pull this data for 2018-11-01, `Seq No.` seems to have a regular comma `,` delimiter
#RIC,Domain,Date-Time,Type,Price,Volume,Qualifiers,Seq. No.
HSIX8,Market Price,2018-11-01T09:14:00.147380832+08,Trade,25080,3,3[ACT_FLAG1],705982406011650308
HSIX8,Market Price,2018-11-01T09:14:00.147380832+08,Trade,25080,1,2[ACT_FLAG1],705982406011650308
Best Answer
-
Has the separator changed ? (hint: no)
The separator is always a comma. The semicolon is an integral part of the Qualifiers field.
I made tests based on your queries. Taking December 2018 data as example:
#RIC,Alias Underlying RIC,Domain,Date-Time,GMT Offset,Type,Price,Volume,Qualifiers,Seq. No.
HSIZ8,,Market Price,2018-12-04T09:15:00.131944328Z,+8,Trade,27266,1,1[ACT_FLAG1];707288350947813826[TRADE_ID],The Qualifiers field contains: 1[ACT_FLAG1];707288350947813826[TRADE_ID]
The Seq. No. contains nothing (hence the final comma, which is the separator).
Just to double check, if I do not requested the Sequence Number field then the result is:
#RIC,Alias Underlying RIC,Domain,Date-Time,GMT Offset,Type,Price,Volume,Qualifiers
HSIZ8,,Market Price,2018-12-04T09:15:00.131944328Z,+8,Trade,27266,1,1[ACT_FLAG1];707288350947813826[TRADE_ID]This confirms the separator is the comma.
As a side comment, there are other cases where a semicolon is used inside the qualifiers field, like in this example:
HSIZ8,,Market Price,2018-11-01T01:15:23.137752529Z,+8,Trade,25085,1,1[ACT_FLAG1];Combo Trade[USER],705982406011651801,
Where is the trade ID ?
For November 2018 the data (including the sequence number) effectively looks different:
#RIC,Alias Underlying RIC,Domain,Date-Time,GMT Offset,Type,Price,Volume,Qualifiers,Seq. No.
HSIZ8,,Market Price,2018-11-01T01:15:16.929679422Z,+8,Trade,25085,1,1[ACT_FLAG1],705982406011651553What you suggest in your answer is correct: the comma is the separator, and the content of fields Qualifiers and Seq. No. has changed. It looks like the Hang Seng was initially publishing trade IDs in the sequence number, and then changed that.
I searched where it could be, making various tests, using fields "Trade - Qualifiers", "Trade - Sequence Number", "Trade - Unique Trade Identification".
Using:
"ContentFieldNames": [ "Trade - Price", "Trade - Volume", "Trade - Qualifiers", "Trade - Sequence Number", "Trade - Unique Trade Identification" ],
I get in November:
#RIC,Alias Underlying RIC,Domain,Date-Time,GMT Offset,Type,Price,Volume,Qualifiers,Seq. No.,Unique Trade Identification
HSIZ8,,Market Price,2018-11-01T01:15:16.929679422Z,+8,Trade,25085,1,1[ACT_FLAG1],705982406011651553,I get in December:
#RIC,Alias Underlying RIC,Domain,Date-Time,GMT Offset,Type,Price,Volume,Qualifiers,Seq. No.,Unique Trade Identification
HSIZ8,,Market Price,2018-12-04T09:15:00.131944328Z,+8,Trade,27266,1,1[ACT_FLAG1];707288350947813826[TRADE_ID],,707288350947813826I also tried field "Trade - Trade Sequence Number" but it delivered no data.
So in conclusion:
- November: trade ID is in field "Trade - Sequence Number"
- December: trade ID is in field "Trade - Unique Trade Identification" (and it is also concatenated with the qualifiers, using a semicolon as separator between the qualifier and trade ID).
Conclusion
Refinitiv publishes the data it receives from the exchanges, as it receives it. If the exchange modifies the way it publishes data, then the data format will change. That seems to be the case here, and there is not much one can do about it except manage it in the code.
If you want to understand precisely why this has changed, as it is a data query the best and speediest way to receive an answer is to open a content-related enquiry via MyRefinitiv or to call the Refinitiv Help Desk directly.
0
Answers
-
Further investigation leads me to believe that what I believed to be the `Seq Id` is now just being appended to the Qualifier?
Dropping `Seq. Id.` returns the following (still)
#RIC,Domain,Date-Time,Type,Price,Volume,Qualifiers
HSIZ8,Market Price,2018-12-05T09:14:00.148335969+08,Trade,26788,8,3[ACT_FLAG1];707327933366141519[TRADE_ID],Could someone confirm that the `Qualifier` field now includes a `[TRADE_ID]`?
0 -
Thanks Christiaan! A very detailed and satisfactory response, appreciate the time taken. We will fix this on our end.
0
Categories
- All Categories
- 6 AHS
- 39 Alpha
- 162 App Studio
- 4 Block Chain
- 5 Bot Platform
- 17 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 370 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 60 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛