Confusion for get_data() function result

YunaValianta.Aulia

Hi all,

I am using python api to get daily volume and auction volume for instrument. However, I have confusing result as follow:

search_field =

['TR.Volume',

'TR.Volume.date',

'TR.Turnover',

'TR.NUMBEROFTRADES',

'TR.OPENINGAUCTIONVOLUME',

'TR.CLOSINGAUCTIONVOLUME',

'TR.INTRADAYAUCTIONVOLUME',

'TR.OPENINGAUCTIONPRICE',

'TR.CLOSINGAUCTIONPRICE',

'TR.INTRADAYAUCTIONPRICE']

result1 =

ek.get_data(['CFCG.DE'],search_fields,

{'SDate': '2020-03-02','EDate': '2020-03-05','Frq': 'D' })

result1

( Instrument Volume Date Turnover Number of Trades \

0 CFCG.DE 5188 2020-03-02T00:00:00Z 5468.40 4

1 CFCG.DE 10701 2020-03-03T00:00:00Z 11688.65 7

2 CFCG.DE 3400 2020-03-04T00:00:00Z 3830.00 2

3 CFCG.DE 20765 2020-03-05T00:00:00Z 21014.45 8

Opening Auction Volume Closing Auction Volume Intraday Auction Volume \

0 1089.0 NaN NaN

1 1277.0 NaN 40.0

2 NaN NaN 8517.0

3 NaN NaN NaN

Opening Auction Price Closing Auction Price Intraday Auction Price

0 1.05 1.020 NaN

1 1.00 1.070 1.11

2 NaN 1.100 1.01

3 NaN 0.995 NaN , None)

result2 =

ek.get_data(['CFCG.DE'],search_fields,

{'SDate': '2020-03-02','EDate': '2020-03-04','Frq': 'D' })

( Instrument Volume Date Turnover Number of Trades \

0 CFCG.DE 5188 2020-03-02T00:00:00Z 5468.40 4

1 CFCG.DE 10701 2020-03-03T00:00:00Z 11688.65 7

2 CFCG.DE 3400 2020-03-04T00:00:00Z 3830.00 2

Opening Auction Volume Closing Auction Volume Intraday Auction Volume \

0 1089.0 NaN NaN

1 1277.0 NaN 40.0

2 NaN NaN NaN

Opening Auction Price Closing Auction Price Intraday Auction Price

0 1.05 1.02 NaN

1 1.00 1.07 1.11

2 NaN 1.10 NaN , None)

result3 = vol= ek.get_data(['CFCG.DE'],search_fields,

{'SDate': '2020-03-04','EDate': '2020-03-04','Frq': 'D' })

( Instrument Volume Date Turnover Number of Trades \

0 CFCG.DE 3400 2020-03-04T00:00:00Z 3830 2

Opening Auction Volume Closing Auction Volume Intraday Auction Volume \

0 NaN NaN NaN

Opening Auction Price Closing Auction Price Intraday Auction Price

0 NaN 1.1 NaN , None)

Let us focus for date 2020-03-04 for result1,result2 and result3. As it can be seen at the above, the data for this particular date are consistent for result1, result2 and result3 except for Intraday Auction Volume. For result1, the intraday auction volume is 8517 for date 2020-03-04, while for result2 and result3 they are both NaN (no volume). This is inconsistent in my opinion as they are all supposed to be the same regardless how many dates I extract from the get_data() function. Kindly need your help. Thanks.

chavalit-jintamalit

Hi @YunaValianta.Aulia

The 8517 value is for 2020-03-05.

YunaValianta.Aulia

Hi @chavalit.jintamalit

Thank you for your answer.

In this case, does it mean I can't use get_data() function to search for multiple data at once? For example I want to get TR.Volume and TR.INTRADAYAUCTIONVOLUME at once together with their corresponding date TR.Volume.date and TR.INTRADAYAUCTIONVOLUME.date.

I tried:

result1 = ek.get_data(['CFCG.DE'],['TR.Volume.date','TR.Volume','TR.INTRADAYAUCTIONVOLUME.date','TR.INTRADAYAUCTIONVOLUME'],{'SDate':'2020-03-02','EDate': '2020-03-06', 'Frq':'D'})

( Instrument Date Volume Date \

0 CFCG.DE 2020-03-02T00:00:00Z 5188 2020-03-02T00:00:00Z

1 CFCG.DE 2020-03-03T00:00:00Z 10701 2020-03-03T00:00:00Z

2 CFCG.DE 2020-03-04T00:00:00Z 3400 2020-03-05T00:00:00Z

3 CFCG.DE 2020-03-05T00:00:00Z 20765 2020-03-06T00:00:00Z

4 CFCG.DE 2020-03-06T00:00:00Z 12150

Intraday Auction Volume

0 NaN

1 40.0

2 8517.0

3 NaN

4 NaN , None)

But as you can see I will have the same 'Date' column for both fields and python cant differentiate which date belongs to which field. Is there any way to solve this problem? I simply aim to have result like the below data frame:

Instrument Date_Volume Volume Date_Intraday_Auction Intraday_Auction

CFCG.DE 2020-03-02 5188 2020-03-02 NaN

CFCG.DE 2020-03-03 10701 2020-03-03 40

CFCG.DE 2020-03-04 3400 2020-03-04 NaN

CFCG.DE 2020-03-05 20765 2020-03-05 NaN

I am also confused why the function gives result that skips the date of 2020-03-04 (as in your answer) when we search for result between 2020-03-02 to 2020-03-05. Why doesn't it make 2020-03-04 to appear with NaN as result?

Many thanks.

YunaValianta.Aulia

Hi @chavalit.jintamalit

So does it mean I cant search for multiple field with get_data()? For example I want to find at once TR.Volume and TR.INTRADAYTURNOVER? I tried:

result1 = ek.get_data(['CFCG.DE'],['TR.Volume.date','TR.Volume','TR.INTRADAYAUCTIONVOLUME.date','TR.INTRADAYAUCTIONVOLUME'],{'SDate':'2020-03-02','EDate': '2020-03-06', 'Frq':'D'})

But this results in two columns with the same name 'Date' such that python is unable to separate which date belongs to which field.

My second question, in your answer when we search for result between 2020-03-02 to 2020-03-05, why eikon API simply skips 2020-03-04 entirely? Why cant it keep 2020-03-04 in the data frame and gives result as NaN?

Many thanks.

chavalit-jintamalit

Hi @YunaValianta.Aulia

You can use get_data() to request for multiple fields but if the fields are from different databases, the date may not be aligned.

TR.Volume and TR.INTRADAYAUCTIONVOLUME are from different databases.

Please see this post for more information, https://community.developers.refinitiv.com/questions/56148/how-to-sort-an-ekget-data-request-by-instrument-an.html

For second question:

If there is no data point, there will be no record.

For example: