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.

Giacomo_C

https://community.developers.refinitiv.com/discussion/comment/125480#Comment_125480

Dear Jirapongse,
I am not searching for convertible bonds, but non-convertible bonds, so your results do not apply.

Moreover, by searching the RIC "MY8664PC" in the workspace it seems that the currency of the issue is MYR and not USD.

The amount outstanding in MYR appears to be the same value under the "IssueSizeUSD" of your search divided by 1'000.

Can you help me understanding?

Best

Giacomo

Jirapongse

It could be other fields.

df = ld.discovery.search(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    filter = "RIC eq 'MY8664PC='",
    top = 100,
    select = "RIC,DocumentTitle,IssueSizeUSD,IssueSizeEUR,ISIN,FaceIssuedUSD,FaceOutstandingUSD,IssueDate,FaceIssuedTotal,FaceOutstanding,AmountOutstandingHistory")
df

Please contact the helpdesk team directly via MyAccount to confirm which field can be used.

Giacomo_C

https://community.developers.refinitiv.com/discussion/comment/125482#Comment_125482

Dear Jirapongse,

thank you for your reply.

I'm still puzzled by the fact that the Amount Outstanding in the original currency (MYR) equals the IssueSzieUSD (same digits, scaled by 1'000).

Furthermore, I am not looking for convertible bonds as you did in your example.

Thanks

Giacomo

Jirapongse

https://community.developers.refinitiv.com/discussion/comment/125484#Comment_125484

@Giacomo_C

Please contact the helpdesk team via MyAccount to verify the value of IssueSzieUSD.

You can change the filter query according to your requirement. Please refer to this Building Search into your Application Workflow article.