I'm processing CSVS for order book data from the RawMarketByPrice view that look like this:
#RIC,Domain,Date-Time,GMT Offset,Type,MsgClass/FID number,UpdateType/Action,FID Name,FID Value,FID Enum String,PE Code,Template Number,Key/Msg Sequence Number,Alias Underlying RIC,Number of FIDs
QCOM.ARC,Market By Price,2025-02-19T09:30:00.000000000Z,-5,Raw,REFRESH,,,,,5100,0,0,,0
,,,,Summary,,,,,,,,,,36
,,,,FID,1,,PROD_PERM,5100,
,,,,FID,3,,DSPLY_NAME,QUALCOMM INC,
,,,,FID,15,,CURRENCY,840,USD
,,,,FID,17,,ACTIV_DATE,2025-02-19,
,,,,FID,198,,LOT_SIZE_A,100,
,,,,FID,259,,RECORDTYPE,113,
,,,,FID,1021,,SEQNUM,1050432,
,,,,FID,1709,,RDN_EXCHD2,461,ARC
,,,,FID,3183,,LIST_MKT,Q,
,,,,FID,3422,,PROV_SYMB,QCOM,
,,,,FID,3423,,PR_RNK_RUL,1,NOR
,,,,FID,3425,,OR_RNK_RUL,2,PTS
,,,,FID,3694,,MNEMONIC,QCOM,
,,,,FID,3915,,MKT_STATUS,,
,,,,FID,3984,,TRD_TYPE,B,
,,,,FID,4148,,TIMACT_MS,34195738,
,,,,FID,5357,,CONTEXT_ID,5444,
,,,,FID,6213,,TRD_TYPE2,,
,,,,FID,6401,,DDS_DSO_ID,8411,
,,,,FID,6480,,SPS_SP_RIC,.[SPSNYSARC03L2,
,,,,FID,6516,,BOOK_STATE,1,N
,,,,FID,6517,,HALT_REASN,~,
,,,,FID,6518,,ORD_ENT_ST,,
,,,,FID,6519,,MKT_OR_RUL,0,
,,,,FID,6577,,SH_SAL_RES,,
,,,,FID,6614,,TRD_STATUS,,
,,,,FID,6615,,HALT_RSN,1,NH
,,,,FID,6835,,DELBY_DT,,
,,,,FID,8300,,SEE_RIC,,
,,,,FID,8647,,PERIOD_CD2,,
,,,,FID,8927,,INST_PHASE,,
,,,,FID,8950,,MK_STATUS,,
,,,,FID,9050,,L2_REPR,1,MBP-D
,,,,FID,12843,,OR_PRC_BAS,1,PRC
,,,,FID,13490,,ORDBK_DEPH,1,FB
,,,,FID,14269,,TIMACT_NS,09:29:55.738754070,
,,,,MapEntry,,ADD,,,,,,147.510000B,,6
,,,,FID,6529,,LV_DATE,2025-02-19,
,,,,FID,3427,,ORDER_PRC,147.51,
,,,,FID,6527,,LV_TIM_MS,32405021,
,,,,FID,3428,,ORDER_SIDE,1,BID
,,,,FID,4356,,ACC_SIZE,6,
,,,,FID,3430,,NO_ORD,2,
,,,,MapEntry,,ADD,,,,,,154.700000B,,6
,,,,FID,6529,,LV_DATE,2025-02-19,
,,,,FID,3427,,ORDER_PRC,154.7,
,,,,FID,6527,,LV_TIM_MS,32400016,
,,,,FID,3428,,ORDER_SIDE,1,BID
,,,,FID,4356,,ACC_SIZE,50,
,,,,FID,3430,,NO_ORD,1,
,,,,MapEntry,,ADD,,,,,,160.000000B,,6
…
QCOM.ARC,Market By Price,2025-02-19T09:30:02.270879034Z,-5,Raw,UPDATE,UNSPECIFIED,,,,5100,,60944,,0
,,,,Summary,,,,,,,,,,5
,,,,FID,1021,,SEQNUM,1053292,
,,,,FID,4148,,TIMACT_MS,34202256,
,,,,FID,14269,,TIMACT_NS,09:30:02.256742218,
,,,,FID,17,,ACTIV_DATE,2025-02-19,
,,,,FID,13490,,ORDBK_DEPH,1,FB
,,,,MapEntry,,DELETE,,,,,,173.930000A,,0
QCOM.ARC,Market By Price,2025-02-19T09:30:02.275825437Z,-5,Raw,UPDATE,UNSPECIFIED,,,,5100,,60960,,0
Notice that the data above has a few different timestamp fields.
The UPDATE lines contain a timestamp in the Date-Time field and there is also a TIMACT_MS and a TIMACT_NS field associated with the summary data. In addition, I see ADD values that are associated with LV_TIM_MS. In the data above… when does the order book reflect the DELETE? What field should I use as the timestamp? Same question for the ADD fields.
An ADD field might look like this:
,,,,Summary,,,,,,,,,,5
,,,,FID,4148,,TIMACT_MS,34238351,
,,,,FID,14269,,TIMACT_NS,09:30:38.351090834,
,,,,FID,17,,ACTIV_DATE,2025-02-19,
,,,,FID,1021,,SEQNUM,1068166,
,,,,FID,13490,,ORDBK_DEPH,1,FB
,,,,MapEntry,,ADD,,,,,,173.440000B,,6
,,,,FID,6529,,LV_DATE,2025-02-19,
,,,,FID,3427,,ORDER_PRC,173.44,
,,,,FID,6527,,LV_TIM_MS,34238351,
,,,,FID,3428,,ORDER_SIDE,1,BID
,,,,FID,4356,,ACC_SIZE,9,
,,,,FID,3430,,NO_ORD,1,
QCOM.ARC,Market By Price,2025-02-19T09:30:41.090465363Z,-5,Raw,UPDATE,UNSPECIFIED,,,,5100,,61680,,0
My question: when updating the order book, what timestamp should we use? In the ADD above… is the order 173.44:9 added to the order book on the exchange at 09:30:38.351090834 (the TIMACT_NS or at 2025-02-19T09:30:41.090465363Z which is associated with the UPDATE line under it.
The reason why I ask is because sometimes I notice the exchange NTV having an order book that crosses the order book from ARC and NY2. The order book within the exchange isn't crossed but the order books across exchanges is.
For reference, this is my API call:
json_blob = {
"ExtractionRequest": {
"@odata.type": "#DataScope.Select.Api.Extractions.ExtractionRequests.TickHistoryMarketDepthExtractionRequest",
"IdentifierList": {
"@odata.type": "#DataScope.Select.Api.Extractions.ExtractionRequests.InstrumentIdentifierList",
"InstrumentIdentifiers": [{
"Identifier": f"{ticker}",
"IdentifierType": "Ric",
}],
"ValidationOptions": {
"AllowHistoricalInstruments": "true"
},
"UseUserPreferencesForValidationOptions": "false",
},
"Condition": {
"View": "RawMarketByPrice",
"MessageTimeStampIn": "GmtUtc",
"ReportDateRangeType": "Range",
"DisplaySourceRIC": "true",
"DateRangeTimeZone": "UTC",
"QueryStartDate": query_start_date,
"QueryEndDate": query_end_date,
},
}
}
post_url = URL_BASE + "/Extractions/ExtractRaw"
request_response = post_request_with_auth(post_url, REFINITIV_AUTH_TOKEN, json_blob)
job_id = request_response.json()["JobId"]
logger.info("Starting CSV download...")
data_response = requests.get(URL_BASE + f"/Extractions/RawExtractionResults('{job_id}')/$value", headers = {
"Authorization": f"Token {REFINITIV_AUTH_TOKEN}"
}, stream = True)
data_response.raise_for_status()
return data_response
