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:
Best Answer
-
@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
0
Answers
-
@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'})
df3Let me know if this works for you.
0 -
@jason.ramchandani01 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)
0 -
@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:
0 -
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
data10 -
Thank you again for your super detailed guidance. I will modify my command and come back to you if any problems arise.0
-
@jason.ramchandani01 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:
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.
0 -
@jason.ramchandani01 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?
0 -
@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
0 -
@jason.ramchandani01 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
0 -
@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
0 -
@jason.ramchandani01 Sorry for misunderstanding you. Here is the RIC list I have. The only RIC showed up in the final output is the first ticker
0 -
@jason.ramchandani01 Sorry for misunderstanding you. Here is the RIC list I have. The only RIC showed up in the final output is the first ticker
0 -
Thank you for the reply. Can I ask what is inside the data frame df1?0
-
@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.
0 -
Oh, thank you, then I see where the problem lies
it works for me now. 0
Categories
- All Categories
- 3 Polls
- 6 AHS
- 36 Alpha
- 166 App Studio
- 6 Block Chain
- 4 Bot Platform
- 18 Connected Risk APIs
- 47 Data Fusion
- 34 Data Model Discovery
- 685 Datastream
- 1.4K DSS
- 615 Eikon COM
- 5.2K Eikon Data APIs
- 10 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- 3 Trading API
- 2.9K Elektron
- 1.4K EMA
- 252 ETA
- 556 WebSocket API
- 38 FX Venues
- 14 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 23 Messenger Bot
- 3 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 60 Open Calais
- 275 Open PermID
- 44 Entity Search
- 2 Org ID
- 1 PAM
- PAM - Logging
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 22 RDMS
- 1.9K Refinitiv Data Platform
- 652 Refinitiv Data Platform Libraries
- 4 LSEG Due Diligence
- LSEG Due Diligence Portal API
- 4 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.2K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 12 World-Check Customer Risk Screener
- 1K World-Check One
- 46 World-Check One Zero Footprint
- 45 Side by Side Integration API
- 2 Test Space
- 3 Thomson One Smart
- 10 TR Knowledge Graph
- 151 Transactions
- 143 REDI API
- 1.8K TREP APIs
- 4 CAT
- 27 DACS Station
- 121 Open DACS
- 1.1K RFA
- 104 UPA
- 193 TREP Infrastructure
- 228 TRKD
- 917 TRTH
- 5 Velocity Analytics
- 9 Wealth Management Web Services
- 90 Workspace SDK
- 11 Element Framework
- 5 Grid
- 18 World-Check Data File
- 1 Yield Book Analytics
- 46 中文论坛
