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:

Find more posts tagged with

eikon

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

jason.ramchandani01

@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

All comments

jason.ramchandani01

@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.

Diep.Tran

@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)

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

@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?

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

Diep.Tran

@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

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

@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

jason.ramchandani01

@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

Diep.Tran

Thank you for the reply. Can I ask what is inside the data frame df1?

jason.ramchandani01

@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.

Diep.Tran

Oh, thank you, then I see where the problem lies

it works for me now.

EXPLORE OUR SITES