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

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
13 3 5 8

Bond Holding data for multiple RICs

I am currently using the following snippet of code to retrieve bond holding information via EMAXX for specific bonds RICs:


    for key in unique_bonds_dict.keys():
        try:
            df, err = ek.get_data([key], ['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD', 'TR.H.REPORTDATE'])
        except:
            time.sleep(5)
            df, err = ek.get_data([key], ['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD', 'TR.H.REPORTDATE'])


The issue is that a firm may have many bonds outstanding, and looping through the RICs is:

1) Very taxing on performance, as API calls need to be spaced via a time sleep of 5 seconds in case of errors

2) Increases chance of failed API responses (which unfortunately escape the try and except mechanism


These are the RICs I am for example trying to source, which relate to only ten firms' outstanding bonds:

['084670BF4=', 'US154879285=', '084670BJ6=', '084670BR8=', 'US120067095=', 'US215001784=', 'US138033414=', 'US204933138=', 'US213305611=', 'US215001865=', '084670BS6=', 'US232901462=', 'US204935041=', 'US120067907=', 'US215002004=', 'US138033422=', 'US204940436=', 'US215002098=', 'US232901497=', 'US204940975=', 'US120067966=', 'US204941009=', 'US215002195=', 'US228078077=', 'US232901535=', '084670BK3=', 'US204941025=', 'US215002217=', '084670CS5=1M', '03755LAC8=', 'US182760072=', '163851AE8=', '163851AF5=', 'US225210918=', 'US237609433=', '163851AH1=', '16677JWD4=', '16677JWE2=', '16677JWF9=', '16677JWG7=', '16677JWH5=', '16677JWL6=', '16677JWM4=', '16677JWN2=', '16677JWP7=', '16677JWT9=', '16677JWU6=', '16677JWV4=', '16677JWW2=', '16677JX50=', '16677JX84=', '16677JXC5=', '16677JXE1=', '16677JXF8=', '16677JXJ0=', '16677JXK7=', '16677JXL5=', '16677JXM3=', '16677JXN1=', '16677JXS0=', '16677JXU5=', '16677JXV3=', '16677JY91=', '16677JYA8=', '166764AP5=', '166764AU4=', '166764AT7=', '166764BN9=', '166764BM1=', '166764AB6=', '166764BV1=', '166764BU3=', '166764BK5=', '166764AH3=', '166764BT6=', '166764BW9=', '166764BD1=', '166764BL3=', '166764BX7=', '166764BY5=', '166764BZ2=', '166764CA6=', '26082MW86=', '26082MWD5=', '26082MWF0=', '26082MWG8=', '26078JAB6=', '26078JAH3=', '26078JAC4=', '26078JAD2=', '26078JAE0=', '26078JAF7=', '278865AY6=', '278865AU4=', 'US152985932=', 'US125543375=', '278865AV2=', '278865BD1=', '278865BE9=', '278865BF6=', '278865AM2=', '278865AW0=', '278865BA7=', '278865BG4=', '278865BH2=', 'US236779084=', 'JP00105020=', 'JP01015020=', 'JP00035012=', 'JP00045020=', 'JP00065020=', 'JP03015020=', 'JP00095020=', 'JP00075012=', 'JP00115020=', 'JP01025020=', 'JP00135020=', 'JP02015020=', 'JP02025020=', 'JP00145020=', 'JP01035020=', 'JP03025020=', 'JP02035020=', '30229AW88=', '30229AWA3=', '30229AWD7=', '30229AWE5=', '30229AWG0=', '30229AWH8=', '30229AWM7=', '30229AWN5=', '30229AWP0=', '30229AWQ8=', '30229AWT2=', '30229AWU9=', '30229AWV7=', '30229AWW5=', '30233WNQ4=', '30229AX12=', '30229AX46=', '30229AX53=', '30229AX79=', '30229AX87=', '30229AXC8=', '30229AXE4=', '30229AXJ3=', '30229AXK0=', '30229AXL8=', '30229AXM6=', '30229AXN4=', '30229AXR5=', '30229AXS3=', '30229AXV6=', '30229AY11=', '30229AY29=', '30229AY37=', '30229AY52=', '30229AY86=', '30229AYA1=', '30229AYC7=', '30229AYF0=', '30229AYG8=', '30229AYH6=', '30229AYJ2=', '30229AYN3=', '30229AYP8=', '30229AYQ6=', '30229AYS2=', '30229AYV5=', '30229AYW3=', '30229AZ10=', '30229AZ28=', '30229AZ36=', '30229AZ69=', '30229AZ77=', '30229AZA0=', '30229AZD4=', '30229AZE2=', '30229AZG7=', '30229AZM4=', '30229AZN2=', '30229AZP7=', '30229AZW2=', '30229AZX0=', '30229BA31=', '30229BAB3=', '30229BAD9=', '30229BAJ6=', '30229BAK3=', '30229BAM9=', '30229BAQ0=', '30229BAU1=', '30229BAX5=', '30229BB14=', '30229BB71=', '30229BBB2=', '30229BBE6=', '30229BBP1=', '30229BC13=', '30231GAJ1=', '30231GAK8=', '30229BCE5=', '30229BD12=', '30229BD87=', '30229BDC8=', '30229BDJ3=', '30229BDN4=', '30231GBB7=', '30231GBA9=', '30231GAR3=', '30231GBL5=', '30231GAC6=', 'US219632215=', '30231GBC5=', '30231GAF9=', '30231GBH4=', '30231GAT9=', '30231GBD3=', '30231GBJ0=', 'US219632231=', '30231GBE1=', '30231GBK7=', '30231GBN1=', 'US219632240=', 'US219632401=', '30231GAY8=', '30231GBF8=', '30231GAN2=', '30231GAW2=', '30231GAZ5=', '30231GBG6=', '30231GBM3=', '60706HAJ2=FINR', 'IVL19PA=TS', 'THIVL21OA=', 'THIVL21OB=', 'THIVL21OC=', 'THIVL224A=', 'THIVL224B=', 'THIVL225A=', 'IVL22OA=TS', 'THIVL22DA=', 'THIVL236B=', 'THIVL236A=', 'THIVL243A=', 'THIVL245A=', 'IVL24NA=TS', 'THIVL24DA=', 'IVL256A=TS', 'IVL25OA=TS', 'THIVL25NA=', 'IVL25DA=TS', 'THIVL26OA=', 'THIVL275A=', 'IVL27NA=TS', 'IVL27NB=TS', 'THIVL286A=', 'THIVL28OA=', 'THIVL295A=', 'THIVL306A=', 'THIVL30NA=', 'THIVL31OA=', 'IVL32NA=TS', 'THIVL336A=', '552081AH4=', 'NL063342025=', '552081AK7=', '552081AM3=', '64016AAA3=', 'NL022628569=', 'US022662805=', '64016AAC9=', 'DE051333683=', 'DE051333535=', 'DE051333250=', 'US102894197=', '58933YAQ8=', '589331AT4=', '58933YAF2=', '58933YAU9=', 'US151305555=', '58933YAR6=', '589331AC1=', '58933YAY1=', 'US102894111=', '589331AD9=', '589331AE7=', '58933YAX3=', '58933YAZ8=', '806605AG6=', 'US102894189=', 'US151306241=', '589331AM9=', '806605AH4=', '58933YAV7=', '589331AQ0=', '58933YBA2=', '589331AS6=', '58933YAJ4=', '58933YAT2=', '58933YAW5=', '58933YBB0=', '718546AA2=', 'US063342629=', '718546AC8=', '718546AU8=', '718546AY0=', '718546AX2=', '718546AV6=', '718546AZ7=', '718546AR5=', '718546AW4=', '718546AK0=', '718546AF1=', 'US075780966=', '718546AH7=', '718546AL8=', '960413AF9=', '960413AX0=', '960413AH5=', 'US147164203=', '960413AT9=', 'US202810403=', '960413AW2=', '960413AY8=', '960413AS1=', '960413AU6=', '960413AZ5=', '960413BA9=', 'FR0127007252=', 'FR0127007260=RRPS', 'FR0011651389=', 'FR0013425170=', 'FR0012452191=', 'FR00140005T0=', 'FR0013252277=', 'FR0013464815=', 'FR0013478252=', '05526LW86=', '05526LWG8=', '05526LWL7=', '05526LWN3=', '05526LWP8=', '05526LWQ6=', '05526LWT0=', '05526LWU7=', '05526LWV5=', '05526LWW3=', '05526LX10=', '05526LX44=', '05526LX51=', '05526LX69=', '05526LX85=', '05526LXF9=', '05526LXS1=', '05526LY27=', 'DE155100176=', 'DE184174561=', 'DE086068311=', 'DEA2BPEV=', 'DEA2BPEU=', 'DE218231888=', 'DE150033799=', 'DE101783324=', 'DE154842284=', 'DE182350265=', 'DE093207971=', 'DE218231934=', 'DE171841810=', 'DE153910979=', 'DE196027793=', 'DE182350257=', 'DE137499428=', 'DE149769196=', 'DE150888565=', 'DE154838759=', 'DE088539958=', 'DE093230710=', 'DE186407458=', 'DE171841771=', 'DE088866720=', 'XS1864163925=LU', 'DE137774593=', 'DE218880568=', 'DE218880584=', '166754AQ4=', 'US173567839=', '166754AW1=', 'US215105423=', 'US152602723=', '166754AP6=', '166754AS0=', 'US173567774=', '50000DW85=', '50000DW93=', '50000DWA0=', '50000DWD4=', '50000DWE2=', '50000DWF9=', '50000DWG7=', '50000DWM4=', '50000DWN2=', '50000DWP7=', '50000DWQ5=', '50000DWT9=', '50000DWU6=', '50000DWV4=', '50000DWW2=', '50000DX19=', '50000DX43=', '50000DX50=', '50000DX68=', '50000DX76=', '50000DX84=', '50000DXD3=', '50000DXE1=', '50000DXJ0=', '50000DXM3=', '50000DXN1=', '50000DXR2=', '50000DXU5=', '50000DXV3=', '50000DY18=', '50000DY83=', '50000DYW0=', '50000DZ17=', '50000DZ25=', '50000DZ33=', 'SA198211826=', '80414L2A2=', '80414L2G9=', 'SA226285270=', 'SA198211281=', '80414L2C8=', '80414L2H7=', 'SA226285300=', 'SA198211320=', '80414L2D6=', '80414L2K0=', 'SA226285326=', 'SA198211346=', '80414L2E4=', 'SA198211613=', '80414L2F1=', '80414L2L8=', 'SA226285334=', '80414L2M6=', 'SA226285342=']

My question is whether there is a way to perform and aggregated search for these all at the same time? Or whether there is a better way to do this? I am particularly keen to understand how I can avoid the failed API responses.

Thanks!

eikoneikon-data-apipythonworkspaceworkspace-data-apirefinitiv-dataplatform-eikonbonds
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
Accepted
5.9k 10 5 8

hi @Giorgio Cozzolino ,

It seems like the limit of get_data() function is reached. For more detail about the Eikon Data API limit, you can check this page.

Could you try put sleep in the loop as an example below

 time.sleep(0.01)

start_exec and end_exec are declared to measure the time used for the code run, it is shown that this takes about 11 mins (I took the full_list from RIC list in comment above)

import pandas as pd
import time
from datetime import datetime

# full_list was declared
start_exec = datetime.now()
data2 = pd.DataFrame()
for ric in fulllist:
    time.sleep(0.01)
    df, err = ek.get_data(ric,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
    if len(data2):
        data2 = pd.concat([data2,df],axis=0,ignore_index=True)
    else:
        data2 = df
end_exec = datetime.now()

print (f'This code took {end_exec - start_exec} to run')
data2

1631824716879.png


Hope this could help.


1631824716879.png (75.1 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.

Upvote
7.5k 10 6 8

@Giorgio Cozzolino So unfortunately this information only seems to be available one RIC at a time - I have tested it. So this is the best I come up with:

fulllist = ['084670BF4=', 'US154879285=', '084670BJ6=', '084670BR8=', 'US120067095=', 'US215001784=', 'US138033414=', 'US204933138=', 'US213305611=', 'US215001865=', '084670BS6=', 'US232901462=', 'US204935041=', 'US120067907=', 'US215002004=', 'US138033422=', 'US204940436=', 'US215002098=', 'US232901497=', 'US204940975=', 'US120067966=', 'US204941009=', 'US215002195=', 'US228078077=', 'US232901535=', '084670BK3=', 'US204941025=', 'US215002217=', '084670CS5=1M', '03755LAC8=', 'US182760072=', '163851AE8=', '163851AF5=', 'US225210918=', 'US237609433=', '163851AH1=', '16677JWD4=', '16677JWE2=', '16677JWF9=', '16677JWG7=', '16677JWH5=', '16677JWL6=', '16677JWM4=', '16677JWN2=', '16677JWP7=', '16677JWT9=', '16677JWU6=', '16677JWV4=', '16677JWW2=', '16677JX50=', '16677JX84=', '16677JXC5=', '16677JXE1=', '16677JXF8=', '16677JXJ0=', '16677JXK7=', '16677JXL5=', '16677JXM3=', '16677JXN1=', '16677JXS0=', '16677JXU5=', '16677JXV3=', '16677JY91=', '16677JYA8=', '166764AP5=', '166764AU4=', '166764AT7=', '166764BN9=', '166764BM1=', '166764AB6=', '166764BV1=', '166764BU3=', '166764BK5=', '166764AH3=', '166764BT6=', '166764BW9=', '166764BD1=', '166764BL3=', '166764BX7=', '166764BY5=', '166764BZ2=', '166764CA6=', '26082MW86=', '26082MWD5=', '26082MWF0=', '26082MWG8=', '26078JAB6=', '26078JAH3=', '26078JAC4=', '26078JAD2=', '26078JAE0=', '26078JAF7=', '278865AY6=', '278865AU4=', 'US152985932=', 'US125543375=', '278865AV2=', '278865BD1=', '278865BE9=', '278865BF6=', '278865AM2=', '278865AW0=', '278865BA7=', '278865BG4=', '278865BH2=', 'US236779084=', 'JP00105020=', 'JP01015020=', 'JP00035012=', 'JP00045020=', 'JP00065020=', 'JP03015020=', 'JP00095020=', 'JP00075012=', 'JP00115020=', 'JP01025020=', 'JP00135020=', 'JP02015020=', 'JP02025020=', 'JP00145020=', 'JP01035020=', 'JP03025020=', 'JP02035020=', '30229AW88=', '30229AWA3=', '30229AWD7=', '30229AWE5=', '30229AWG0=', '30229AWH8=', '30229AWM7=', '30229AWN5=', '30229AWP0=', '30229AWQ8=', '30229AWT2=', '30229AWU9=', '30229AWV7=', '30229AWW5=', '30233WNQ4=', '30229AX12=', '30229AX46=', '30229AX53=', '30229AX79=', '30229AX87=', '30229AXC8=', '30229AXE4=', '30229AXJ3=', '30229AXK0=', '30229AXL8=', '30229AXM6=', '30229AXN4=', '30229AXR5=', '30229AXS3=', '30229AXV6=', '30229AY11=', '30229AY29=', '30229AY37=', '30229AY52=', '30229AY86=', '30229AYA1=', '30229AYC7=', '30229AYF0=', '30229AYG8=', '30229AYH6=', '30229AYJ2=', '30229AYN3=', '30229AYP8=', '30229AYQ6=', '30229AYS2=', '30229AYV5=', '30229AYW3=', '30229AZ10=', '30229AZ28=', '30229AZ36=', '30229AZ69=', '30229AZ77=', '30229AZA0=', '30229AZD4=', '30229AZE2=', '30229AZG7=', '30229AZM4=', '30229AZN2=', '30229AZP7=', '30229AZW2=', '30229AZX0=', '30229BA31=', '30229BAB3=', '30229BAD9=', '30229BAJ6=', '30229BAK3=', '30229BAM9=', '30229BAQ0=', '30229BAU1=', '30229BAX5=', '30229BB14=', '30229BB71=', '30229BBB2=', '30229BBE6=', '30229BBP1=', '30229BC13=', '30231GAJ1=', '30231GAK8=', '30229BCE5=', '30229BD12=', '30229BD87=', '30229BDC8=', '30229BDJ3=', '30229BDN4=', '30231GBB7=', '30231GBA9=', '30231GAR3=', '30231GBL5=', '30231GAC6=', 'US219632215=', '30231GBC5=', '30231GAF9=', '30231GBH4=', '30231GAT9=', '30231GBD3=', '30231GBJ0=', 'US219632231=', '30231GBE1=', '30231GBK7=', '30231GBN1=', 'US219632240=', 'US219632401=', '30231GAY8=', '30231GBF8=', '30231GAN2=', '30231GAW2=', '30231GAZ5=', '30231GBG6=', '30231GBM3=', '60706HAJ2=FINR', 'IVL19PA=TS', 'THIVL21OA=', 'THIVL21OB=', 'THIVL21OC=', 'THIVL224A=', 'THIVL224B=', 'THIVL225A=', 'IVL22OA=TS', 'THIVL22DA=', 'THIVL236B=', 'THIVL236A=', 'THIVL243A=', 'THIVL245A=', 'IVL24NA=TS', 'THIVL24DA=', 'IVL256A=TS', 'IVL25OA=TS', 'THIVL25NA=', 'IVL25DA=TS', 'THIVL26OA=', 'THIVL275A=', 'IVL27NA=TS', 'IVL27NB=TS', 'THIVL286A=', 'THIVL28OA=', 'THIVL295A=', 'THIVL306A=', 'THIVL30NA=', 'THIVL31OA=', 'IVL32NA=TS', 'THIVL336A=', '552081AH4=', 'NL063342025=', '552081AK7=', '552081AM3=', '64016AAA3=', 'NL022628569=', 'US022662805=', '64016AAC9=', 'DE051333683=', 'DE051333535=', 'DE051333250=', 'US102894197=', '58933YAQ8=', '589331AT4=', '58933YAF2=', '58933YAU9=', 'US151305555=', '58933YAR6=', '589331AC1=', '58933YAY1=', 'US102894111=', '589331AD9=', '589331AE7=', '58933YAX3=', '58933YAZ8=', '806605AG6=', 'US102894189=', 'US151306241=', '589331AM9=', '806605AH4=', '58933YAV7=', '589331AQ0=', '58933YBA2=', '589331AS6=', '58933YAJ4=', '58933YAT2=', '58933YAW5=', '58933YBB0=', '718546AA2=', 'US063342629=', '718546AC8=', '718546AU8=', '718546AY0=', '718546AX2=', '718546AV6=', '718546AZ7=', '718546AR5=', '718546AW4=', '718546AK0=', '718546AF1=', 'US075780966=', '718546AH7=', '718546AL8=', '960413AF9=', '960413AX0=', '960413AH5=', 'US147164203=', '960413AT9=', 'US202810403=', '960413AW2=', '960413AY8=', '960413AS1=', '960413AU6=', '960413AZ5=', '960413BA9=', 'FR0127007252=', 'FR0127007260=RRPS', 'FR0011651389=', 'FR0013425170=', 'FR0012452191=', 'FR00140005T0=', 'FR0013252277=', 'FR0013464815=', 'FR0013478252=', '05526LW86=', '05526LWG8=', '05526LWL7=', '05526LWN3=', '05526LWP8=', '05526LWQ6=', '05526LWT0=', '05526LWU7=', '05526LWV5=', '05526LWW3=', '05526LX10=', '05526LX44=', '05526LX51=', '05526LX69=', '05526LX85=', '05526LXF9=', '05526LXS1=', '05526LY27=', 'DE155100176=', 'DE184174561=', 'DE086068311=', 'DEA2BPEV=', 'DEA2BPEU=', 'DE218231888=', 'DE150033799=', 'DE101783324=', 'DE154842284=', 'DE182350265=', 'DE093207971=', 'DE218231934=', 'DE171841810=', 'DE153910979=', 'DE196027793=', 'DE182350257=', 'DE137499428=', 'DE149769196=', 'DE150888565=', 'DE154838759=', 'DE088539958=', 'DE093230710=', 'DE186407458=', 'DE171841771=', 'DE088866720=', 'XS1864163925=LU', 'DE137774593=', 'DE218880568=', 'DE218880584=', '166754AQ4=', 'US173567839=', '166754AW1=', 'US215105423=', 'US152602723=', '166754AP6=', '166754AS0=', 'US173567774=', '50000DW85=', '50000DW93=', '50000DWA0=', '50000DWD4=', '50000DWE2=', '50000DWF9=', '50000DWG7=', '50000DWM4=', '50000DWN2=', '50000DWP7=', '50000DWQ5=', '50000DWT9=', '50000DWU6=', '50000DWV4=', '50000DWW2=', '50000DX19=', '50000DX43=', '50000DX50=', '50000DX68=', '50000DX76=', '50000DX84=', '50000DXD3=', '50000DXE1=', '50000DXJ0=', '50000DXM3=', '50000DXN1=', '50000DXR2=', '50000DXU5=', '50000DXV3=', '50000DY18=', '50000DY83=', '50000DYW0=', '50000DZ17=', '50000DZ25=', '50000DZ33=', 'SA198211826=', '80414L2A2=', '80414L2G9=', 'SA226285270=', 'SA198211281=', '80414L2C8=', '80414L2H7=', 'SA226285300=', 'SA198211320=', '80414L2D6=', '80414L2K0=', 'SA226285326=', 'SA198211346=', '80414L2E4=', 'SA198211613=', '80414L2F1=', '80414L2L8=', 'SA226285334=', '80414L2M6=', 'SA226285342=']
data = pd.DataFrame()
for ric in fulllist:
    df, err = ek.get_data(ric,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
    if len(data):
        data = pd.concat([data,df],axis=0,ignore_index=True)
    else:
        data = df
        
data

1631102188949.png

This returned for me in a few minutes without error. So that was 448 API calls. I hope this can help.



1631102188949.png (293.2 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.

Upvotes
13 3 5 8

@jason.ramchandani

Thanks for this. I tried the solution but as I expected I encounter an error, likely given by the number of API calls ran. Specifically for the following line:


df, err = ek.get_data(key,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])


The error is the following:

  File "C:\ProgramData\Anaconda3\lib\site-packages\eikon\data_grid.py", line 192, in get_data
    if result.get('responses'):
AttributeError: 'NoneType' object has no attribute 'get'

Can you please advise on how to best factor this issue? I usually employ a try/except as per below:

    data = pd.DataFrame()
    for key in unique_bonds_dict.keys():
        try:
            df, err = ek.get_data(key,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
        except:
            time.sleep(5)
            df, err = ek.get_data(key,['TR.H.HoldingCompanyName.HoldingCompanyName','TR.H.PARHELD','TR.H.REPORTDATE'])
            
        if len(data):
            data = pd.concat([data,df],axis=0,ignore_index=True)
        else:
            data = df


Unfortunately, even a try and except method results in a Backend error, 400 Bad Request, leading to the same exception. it seems that the API is not able to handle these requests. Can you please suggest a workaround? 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.