Bond Holding data for multiple RICs

Giorgio Cozzolino

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!

raksina.samasiri

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

Hope this could help.

jason.ramchandani01

@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

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

Giorgio Cozzolino

@jason.ramchandani01

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!