Convert Bonds Issued Amounts in EUR using exchange rate at Issue Date

Giacomo_C

Hi all, I would like to API request to retrieve a list of bonds, with the outstanding amounts converted to EUR based on the exchange rate as of the issue date.

Currently, the GOVSRCH function in Workspace performs conversions using the exchange rate as of the previous close.

Can you help me?

Best

Giacomo

Jirapongse

@Giacomo_C

Thank you for reaching out to us.

It may relate to the search API. Please refer to this Building Search into your Application Workflow article.

I found the IssueSizeEUR field in the GOV_CORP_INSTRUMENTS view.

df = ld.discovery.search(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    filter = "IsConvertible eq true",
    top = 100,
    select = "RIC,DocumentTitle,IssueSizeUSD,IssueSizeEUR,ISIN,IsConvertible,IssueDate")
df

However, not all RICs has the IssueSizeEUR field. Therefore, we need to retrieve the exchange rates on the issue dates to calculate the outstanding amounts converted to EUR based on the exchange rate as of the issue date.

The code should look like this:

df = ld.discovery.search(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    filter = "IsConvertible eq true",
    top = 100,
    select = "RIC,DocumentTitle,IssueSizeUSD,IssueSizeEUR,ISIN,IsConvertible,IssueDate")

#create a list of fields to retrieve TR.BIDPrice on the issue dates. 
bid_price_fields = df['IssueDate'].apply(lambda x: f"TR.BIDPrice(SDate={x.strftime('%Y-%m-%d')})")

#retrieve bid prices of EUR=
bid_prices = ld.get_data(
    "EUR=", 
    bid_price_fields.tolist(),    
    header_type=ld.HeaderType.NAME)

#Add a new column (EUR) that contain BID prices on the issue dates
bid_column = df['IssueDate'].apply(lambda x: bid_prices[f"TR.BIDPRICE(SDATE={x.strftime('%Y-%m-%d')})"][0])
df["EUR"]=bid_column

#Calculate the AmountEUR column with IssueSizeUSD * (1 / EUR)
df["AmountEUR"] =  df['IssueSizeUSD']*(1/df['EUR'])

df

Please contact the helpdesk team via MyAccount to verify if this is a valid method.