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

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
Diep.Tran avatar image
18 ●0 ●0 ●4

Retrieve Balance sheet data based on a list of RICs and Data items

Hello,

I would appreciate to receive an urgent support regarding retrieving data. I run into an error when trying to retrieve bank balance sheet data based on a set of RICs and Data items. I have documented a list of RICs and Data items and use pandas to read and print it in Python. However, when I get to the ek.get_data code part, there is an error called back.

The code I attempted:

df3, e = ek.get_data(df['RICs'],df1['Dataitem'],parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})

The invalid syntax I received:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

What should I change in this case to make it work?

Thank you so much in advance for any advices and guidances!

PS: The RICs and Dataitems that I created are as below:

1625522226807.png1625522207228.png


eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikon
1625522207228.png (23.3 KiB)
1625522226807.png (8.6 KiB)
icon clock
10 |1500

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

6 Answers

Upvote
Accepted
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@Diep.Tran The following is definitely working for me:

instruments = ['KAER.VI','TIRO.VI']
data1 = pd.DataFrame()

for i in instruments:
    try:
        df1, e = ek.get_data(i,['TR.F.CashSTDeposDueBanksTot.date','TR.F.CashSTDeposDueBanksTot'],parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})
        if len(data1):
            data1 = data1.append(df1, ignore_index=True)
        else:
            data1 = df1
    except:
        pass
data1

1625661795442.png


1625661795442.png (105.3 KiB)
icon clock
10 |1500

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

Diep.Tran avatar image Diep.Tran
Thank you for the reply. Can I ask what is inside the data frame df1?
Upvote
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@Diep.Tran Please try the following - you need to pass in an array of field names, not a dataframe - but thankfully in python this is simple:

df3, e = ek.get_data(df['RICs'].astype(str).values.tolist(),df1['Dataitem'].astype(str).values.tolist(),parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})

df3

Let me know if this works for you.

icon clock
10 |1500

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

jason.ramchandani01 avatar image jason.ramchandani01

@Diep.Tran 

instruments = df['RICs'].astype(str).values.tolist()
data1 = pd.DataFrame()

for i in instruments:
    try:
        df1, e = ek.get_data(i,df1['Dataitem'].astype(str).values.tolist(),parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FQ0','Frq':'FQ','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})
        if len(data1):
            data1 = data1.append(df1, ignore_index=True)
        else:
            data1 = df1
    except:
        pass
data1
Diep.Tran avatar image Diep.Tran jason.ramchandani01
Thank you again for your super detailed guidance. I will modify my command and come back to you if any problems arise.
Diep.Tran avatar image Diep.Tran Diep.Tran

@jason.ramchandani Dear Jason, I have tried the code, but maybe it is the error from my part that the loop only runs for the first ticker and does not return responses for the rest of the RICs code. This is how it looks:


1625575257908.png


I have an additional problem now that the data item is now lengthened to approx. 400 Data items. I tried to break it down to 4 different parts now and then append them together. However, I would like to ask if it is possible to do it faster in a loop instead of running the code 4 times?

Thank you so much again and sorry for additional concerns.

1625575257908.png (56.0 KiB)
Diep.Tran avatar image Diep.Tran

@jason.ramchandani Thank you so much, I am so grateful for your swift comment. I modified the code as you suggested and it works perfectly. However, the dataset that I obtained lacks of the timeseries dimension (as stated in the parameters, I would like to retrieve the data for a period from 31 Dec 1999 to 31 Mar 2021). How can I better improve the code to get the time dimension?

The result that I received is as below: (lacks of time period dimension)

1625529052046.png


1625529052046.png (66.9 KiB)
jason.ramchandani01 avatar image jason.ramchandani01 Diep.Tran

@Diep.Tran So to add a date field its easy - as you are requesting all company statement examples they should be of the same date - so you could just repeat the first field you have and add .date at the end: TR.F.CashSTDeposDueBanksTot.date


There are many different output types for different fields.You can investigate those in the Data Item Browser App - type DIB into Eikon search bar.

Also I think you will run up against our API limits which is why you are seeing a lot of NAs. You need to be aware of API limits, which for the get_data API are as follows:

  • get_data: The current limit value (10-Oct-2019) is around 10,000 data points.

So you will need to break down the request either by chunking the RICs or by requesting years separately (or both) as 505 companies * 89 fields * (20years *4 quarters) is almost 4.5 million datapoints. This is why you are getting NAs. So limit each call to 10K datapoints => 4.5 Mil / 10,000 so that would be 449 iteratve calls. In this case I would just be tempted to try requesting data for each RIC seperately and then append it to a frame as follows:


Upvotes
Diep.Tran avatar image
18 ●0 ●0 ●4

@jason.ramchandani Hello, I tried the code you suggested but it only works for the first ticker in the RIC list and does not seem to append the remaining tickers in the RIC list in the final output?

How should I fix it in this case?



1625656256585.png



1625656256585.png (66.7 KiB)
icon clock
10 |1500

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

jason.ramchandani01 avatar image jason.ramchandani01

@Diep.Tran Please can you copy and paste the code you are using. Also please on another line just:

print(df['RICs'].astype(str).values.tolist())

thanks

Upvotes
Diep.Tran avatar image
18 ●0 ●0 ●4

@jason.ramchandani Thank you for your swift reply.

Here is the code I modified

instruments = df['RICs'].astype(str).values.tolist()

print(df['RICs'].astype(str).values.tolist())

data1 = pd.DataFrame()


for i in instruments:

try:

df1, e = ek.get_data(i,df1['Assets'].astype(str).values.tolist(),parameters= {'SDate':'1999-12-31','EDate' :'2021-03-01','Period': 'FI0','Frq':'FI','reportingState':'Rsdt', 'curn':'Native', 'Scale':'3'})

if len(data1):

data1 = data1.append(df1, ignore_index=True)

else:

data1 = df1

except:

pass

data1


icon clock
10 |1500

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

jason.ramchandani01 avatar image jason.ramchandani01

@Diep.Tran I don't think you understood me - I want to see the instrument list so please on a new line print(instruments) or print(df['RICs'].astype(str).values.tolist()) - please remove this line from the main routine. Please post the instrument list here so i can check. thx

Diep.Tran avatar image Diep.Tran jason.ramchandani01

@jason.ramchandani Sorry for misunderstanding you. Here is the RIC list I have. The only RIC showed up in the final output is the first ticker

1625657332405.png

Upvotes
Diep.Tran avatar image
18 ●0 ●0 ●4

@jason.ramchandani Sorry for misunderstanding you. Here is the RIC list I have. The only RIC showed up in the final output is the first ticker

1625657332405.png


1625657332405.png (73.6 KiB)
icon clock
10 |1500

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

Upvotes
jason.ramchandani01 avatar image
6.7k ●8 ●6 ●7

@Diep.Tran So df1 is the return from the API (ie the data) for each RIC - which is being refreshed on each iteration of the for loop where i is the next RIC in the list of instruments. We append df1 to data1 as we want to build a dataframe for all the RICs.

icon clock
10 |1500

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

Diep.Tran avatar image Diep.Tran
Oh, thank you, then I see where the problem lies :) it works for me now.
Click below to post an Idea Post Idea