Upgrade from Eikon -> Workspace. Learn about programming differences.

For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
candrews avatar image
1 ●0 ●0 ●0

How to get final OHLCV on a day after the business date rolls (e.g. after 6pm New York)

I run the following at 6pm NYK for the give date, and then again at 6am NYK the next morning. The RICs are all future outrights, from either CME or equity/rates index futures from European/Asian exchanges. 95% of the data is the same between runs. However, there are still a lot of differences (e.g. a subset below). Note: The latest settlement time for all products included is 4:15pm, with the exchange possibly trading until 5:15pm.

1) Why is this data different? I'm most interested first in the Close; then Volume; then the rest.

2) What can I do so I can run query at 6pm and get a stable result?


Note that end_date will be at least 1 day after start


Value1 is the result from 6pm NYK on the 19th, Value2 from 6am NYK the next morning



eikoneikon-data-apipythonrefinitiv-dataplatform-eikonworkspaceworkspace-data-api
ohlcv-fun.png (50.2 KiB)
result-diffs.png (62.5 KiB)
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.

pimchaya.wongrukun01 avatar image   pimchaya.wongrukun01

Hello @candrews

Thank you for your participation in the forum. Is the reply below satisfactory in resolving your query?

If yes, please click the 'Accept' text next to the reply. This will guide all community members who have a similar question. Otherwise please post again offering further insight into your question.

Thanks,

AHS

7 Answers

· Write an Answer
Upvotes
Accepted
candrews avatar image
1 ●0 ●0 ●0

In chatting with another person about this it seems the most reliable way to get the daily data is via get_date() not get_timeseries().

Ask for the following fields, they will all generally be available by 11:30pm NYK on the same day. 

['TR.SETTLEMENTPRICE.Date', 'TR.SETTLEMENTPRICE', 'TR.OPENPRICE', 'TR.HIGHPRICE', 'TR.LOWPRICE', 'TR.CLOSEPRICE']
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.

Upvotes
chavalit-jintamalit avatar image
18.2k ●21 ●13 ●21

Hi @candrews

To simplified the question.

It is equivalent to call the following code:

ek.get_timeseries("ADZ9", start_date="2019-11-19T00:00:00", end_date="2019-11-19T00:00:00", interval="daily")

At 6 PM 2019-11-19 and 6 AM 2019-11-20 New York time and why you got different values.

In this case, it is 0.6833 vs 0.6831.


I can only assume that there are still some updates on the data.

6 PM 2019-11-19 NY is 11 PM 2019-11-19 UTC.

The input date is in UTC, so there is still 1 more hour before the end of the day.


You can contact the Refinitiv Content helpdesk to clarify the data behavior at https://my.refinitiv.com/


As for NaN result. Can you reproduce it with the simple API call?

If so, please share your code to reproduce the issue. So I can try it as well.

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.

Upvotes
candrews avatar image
1 ●0 ●0 ●0

No, your call is not equivalen. Notice that my code puts end_date as the next date (not same), so this should push the end beyond end of business on the same day. Also note.. I ran this a couple more times hours later today, and there were a few more field that continued to update. Can you please check into the actual database and see when these fields last updated.

You can focus on only the fields that have no NaN values (just different numbers) if that helps you isolate.

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.

Upvotes
candrews avatar image
1 ●0 ●0 ●0

Chavalit, to expand further, thought the the date strings were correctly transformed to UTC with start date have a T00:00:00 appended, and having end be the next day with similar time. If this is not right, can you please give me the date strings that I should use tomorrow at 6pm so that I can capture everything? In terms of values that are continuing to being updated.. I think all exchanges are at least an hour closed and rolled to the next date by then. So, what further updates are coming in relative to this date?

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.

chavalit-jintamalit avatar image chavalit-jintamalit

I use Chart application in Eikon Desktop.

And I can see that there were movement for the whole 48 hours on 2019-11-19 to 2019-11-20. (I used 1 miniute data)

2019-11-19


2019-11-20


ahs.png (73.7 KiB)
ahs2.png (72.3 KiB)
Upvotes
chavalit-jintamalit avatar image
18.2k ●21 ●13 ●21

Hi @candrews

I was assuming that you passed in start_date and end_date as "2019-11-19" when you made the function call.

But if you omit the end_date, it will create a new unexpected behavior.


Case1:
NY 18:00:00 2019-11-19 = UTC 23:00:00 2019-11-19
assuming that get_ohlvc start_date = 2019-11-19, end_date is omitted
start_date = 2019-11-19T00:00:00
end_date = 2019-11-20T00:00:00
Please note that NOW-Today(as of the time the API was called) is 2019-11-19 in UTC.

ek.get_timeseries("ADZ9", start_date="2019-11-19T00:00:00", end_date="2019-11-20T00:00:00", interval="daily")

So you will get only 2019-11-19 data back since 2019-11-20 is in the future and no data available at the time the API call is made.


Case2:
NY 06:00:00 2019-11-20 = UTC 11:00:00 2019-11-20
assuming that get_ohlvc start_date = 2019-11-19, end_date is omitted
start_date = 2019-11-19T00:00:00
end_date = 2019-11-20T00:00:00
Please note that NOW-Today(as of the time the API was called) is 2019-11-20 in UTC.

ek.get_timeseries("ADZ9", start_date="2019-11-19T00:00:00", end_date="2019-11-20T00:00:00", interval="daily")

So you will get data from 2019-11-19 and 2019-11-20 back.

Which means you have 2 rows for each instrument.

This would also explain if you tried to fit in 3000 data point in a single call but did not expect it to be 6000 data point, so you would receive NaN on some instruments.


Maybe this has an effect on your dataframe?

Please confirm. 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.

Upvotes
candrews avatar image
1 ●0 ●0 ●0

@chavalit.jintamalit Sorry, you're wrong on both fronts. In both cases I get back data for the 19th and 20th. I simply throw away the data for the 20th. (In fact this seems to be a "feature" of Eikon. And similar in behavior when I as for data in excel with RHistory; i.e. the next biz date (or even 1 beyond that) is delivered and I must ignore.) And I'm getting back data in chunks, so I'm not hitting the limit on any calls. If you ran the call you'll see you get NaN's for some Open/High/Low values, but probably not for any Close values. I would expect some non-front month and non-active contracts to actually get no real bid/offer through the day, and only get a settlement price that is inerpolated from the front month and the next leap.

Do you have the ability to look at the underlying data and see the time of entry?

We can take this to email and I can give you the results of the queries that I do tonight at 6pm and tmrw at 6am.

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.

chavalit-jintamalit avatar image chavalit-jintamalit

Hi @candrews

I do not think you will get the data from tomorrow in the "daily" interval.

For example, if I ran the query now:

If you make this API call now or at 6 PM today New York time (11 PM UTC time same day).

If you run the API call after 7 PM New York time(already consider daylight saving is off now), UTC is already the next day, so in this case, you would see the next day data point too (providing that if any data point is available).


Anyway, I can confirm that your API call is correct.

But I do not have a tool or ability to see the timestamp on the timeseries database.

This forum is focusing on technical issues such as how to use the API, sample codes, etc...

Moderators may have limited experience with content being delivered by the platform

I would suggest you to contact Refinitiv Helpdesk to get support on the content at https://my.refinitiv.com/

ahs.png (30.9 KiB)
chavalit-jintamalit avatar image chavalit-jintamalit

Hi @candrews

You are right that there are 2 records.

I just tried to capture the data at 6.xx PM New York time.

And I can see that all the data after 6.00PM New York Time (23.00 UTC) are rolled over to the next day.

For example, you can see from the number of data in this picture.

I will try to capture the data again at 6AM New York time on 2019-11-22.

ahs.png (172.5 KiB)
Upvotes
candrews avatar image
1 ●0 ●0 ●0

@chavalit.jintamalit I wasn't able to capture last night before leaving. On your side, please do the second capture, regardless if much later (as the data gets more stable, this in fact may be better). If they match this time through, could you potentially try once more with an expanded instrument list? Thanks for your help on this

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.

chavalit-jintamalit avatar image chavalit-jintamalit

Hi @candrews

I got the same OHLCV data.

ahs1.png (67.1 KiB)

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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