Extraction of fixed income data via Eikon API

ab0

1. I am writing a Python script that should take the ISINs of bonds from a CSV file located in the same directory. The task is to load X bonds and plot their YTMs as points on a graph. Bonds with YTMs below certain criteria should be highlighted with a different color. However, the program throws an error because it cannot retrieve data using ISINs and requires RICs, but not every bond has a RIC. How can I solve this issue?
2. In addition, the bond portfolio is already set up in Refinitiv using the PAL function. Can I access this portfolio directly and extract the data from there, so I don’t need to keep a separate file with ISINs in the program’s directory?

import eikon as ek

import pandas as pd

import plotly.express as px

from datetime import datetime, timedelta
ek.set_app_key("API_KEY")

1. Read the list of bonds from a file.
It is assumed that the file contains a column 'ISIN'.
If your file is named 'bonds.scv', make sure that the name and extension are correct.
bonds_file = 'bonds.csv'bonds_df = pd.read_csv(bonds_file)

isin_list = bonds_df['ISIN'].tolist()

2. Request bond data.
Define the necessary fields. If the field names differ, replace them with the correct ones.
fields = ["TR.YieldToMaturity", "TR.Maturity", "InstrumentName"]
data, err = ek.get_data(isin_list, fields=fields)
if err:

print("Error retrieving data:", err)

else:

df = data.copy()

# 3. Convert the maturity date to datetime format.

df['TR.Maturity'] = pd.to_datetime(df['TR.Maturity'])


# Determine the date of the previous trading session.

# For simplicity, we use yesterday's date here.

prev_date = datetime.now() - timedelta(days=1)


# Calculate the time to maturity in years.

df['TimeToMaturity'] = (df['TR.Maturity'] - prev_date).dt.days / 365.25


# Rename columns for convenience.

df = df.rename(columns={"TR.YieldToMaturity": "YTM",

                        "InstrumentName": "BondName"})


# Filter bonds with positive time to maturity and available YTM values.

df = df[df['TimeToMaturity'] > 0].dropna(subset=['YTM'])


# 4. Define categories for color-coding the points.

def categorize_ytm(ytm):

    if ytm < 4.80:

        return "Ниже 4.80%"

    elif 4.80 <= ytm <= 5.0:

        return "От 4.80 до 5%"

    else:

        return "Выше 5%"


df['Category'] = df['YTM'].apply(categorize_ytm)


# Define a color map for the categories.

color_map = {

    "Ниже 4.80%": "red",

    "От 4.80 до 5%": "yellow",

    "Выше 5%": "green"

}


# 5. Build an interactive chart using Plotly.

fig = px.scatter(df,

                 x="TimeToMaturity",

                 y="YTM",

                 color="Category",

                 hover_data=["BondName"],

                 color_discrete_map=color_map,

                 labels={"TimeToMaturity": "Tenor (years)",

                         "YTM": "YTM (%)"},

                 title="YTM on a bond")


fig.show()

Jirapongse

@ab0

Thank you for reaching out to us.

The Eikon Data API provides the get_symbology method that can convert symbologies. You can use this to convert ISINs to RICs. If it can't convert ISINs, to RICS, please provide sample ISINs that don't have RICs.

Regarding the second question, you can use the Portfolio function in Eikon Data API to access a portfolio, as mentioned in the Eikon Data API - Cheat Sheet article.

ab0

https://community.developers.refinitiv.com/discussion/comment/123993#Comment_123993

I used this function, but I still get the same error. In fact, I was training on the simplest type of bonds – US Treasuries.

List of ISINs:

• US912810EW46
• US912810FG86
• US912810UD80

Error:

Error converting ISIN to RIC. Details:
['No best match available', 'No best match available', 'No best match available']

KeyError                                  Traceback (most recent call last)

Cell In[35], line 16

14     target_column = 'Output'

15 else:

---> 16     raise KeyError(f"RIC column not found in the returned result. Available columns: {ric_mapping.columns}")

18 ric_list = ric_mapping[target_column].tolist()

20 # 6. Retrieving bond data via the Refinitiv API

21 # Fields requested: yield to maturity, maturity date, and bond name
KeyError: "RIC column not found in the returned result. Available columns: Index(['error'], dtype='object')"

Code:

1. Import required libraries
import eikon as ek

import pandas as pd

import plotly.express as px

from datetime import datetime, timedelta
2. Set your Refinitiv Eikon API key (replace 'APP_KEY' with your actual key)
ek.set_app_key('YOUR_APP_KEY')
3. Read the bonds.csv file and extract unique ISINs
It is assumed that the CSV file contains a column named "ISIN"
df_bonds = pd.read_csv('bonds.csv')

unique_isins = df_bonds['ISIN'].unique().tolist()
4. Test API connection with a sample query for one ISIN
try:

# Perform a test transformation for one ISIN:

# Input type is ISIN, target type is RIC

test_symbology = ek.get_symbology(unique_isins[:1], from_symbol_type='ISIN', to_symbol_type='RIC')

print("API connection successful.")

except Exception as e:

print("Error connecting to API:", e)

exit(1)
5. Transform ISINs to RICs for all bonds
ric_mapping = ek.get_symbology(unique_isins, from_symbol_type='ISIN', to_symbol_type='RIC')
If the API returns an error, it will be in the 'error' column
if 'error' in ric_mapping.columns:

print("Error in transforming ISIN to RIC. Details:")

print(ric_mapping['error'].tolist())

exit(1)
Determine the column name containing the RICs: try 'RIC' or 'Output'
if 'RIC' in ric_mapping.columns:

target_column = 'RIC'

elif 'Output' in ric_mapping.columns:

target_column = 'Output'

else:

raise KeyError(f"Could not find the RIC column in the returned result. Available columns: {ric_mapping.columns}")
ric_list = ric_mapping[target_column].tolist()
6. Retrieve bond data from Refinitiv API
Request the following fields: Yield to Maturity, Maturity date, and Bond description
fields = ['TR.YieldToMaturity', 'TR.Maturity', 'TR.SecurityDescription']

bond_data, err = ek.get_data(ric_list, fields)
Check if any errors occurred while fetching data
if err is not None and not err.empty:

print("Error fetching bond data:", err)

exit(1)
7. Data transformation and calculation of time to maturity
Convert the maturity date column to datetime format
bond_data['TR.Maturity'] = pd.to_datetime(bond_data['TR.Maturity'], errors='coerce')
Define the reference date (previous trading session, e.g., yesterday's date)
reference_date = datetime.now() - timedelta(days=1)
Calculate time to maturity in years (divide the number of days until maturity by 365.25)
bond_data['TimeToMaturity'] = (bond_data['TR.Maturity'] - reference_date).dt.days / 365.25
Convert yield to maturity to a numeric format (assuming the data is in percentage)
bond_data['YTM'] = pd.to_numeric(bond_data['TR.YieldToMaturity'], errors='coerce')
8. Define color based on the yield to maturity level for each bond
def assign_color(ytm):

if ytm < 4.80:

return 'red'

elif 4.80 <= ytm <= 5.0:

return 'yellow'

else:  # ytm > 5.0

return 'green'
bond_data['Color'] = bond_data['YTM'].apply(assign_color)
9. Build the scatter plot using Plotly
When hovering over a point, the bond description (TR.SecurityDescription) is displayed
color_map = {'red': 'red', 'yellow': 'yellow', 'green': 'green'}
fig = px.scatter(

bond_data,

x='TimeToMaturity',

y='YTM',

color='Color',

color_discrete_map=color_map,

hover_data=['TR.SecurityDescription'],

labels={

'TimeToMaturity': 'Time to Maturity (years)',

'YTM': 'Yield to Maturity (%)'

},

title='Bond Yield vs Time to Maturity'

)
fig.show()

Jirapongse

@ab0

There are three options to convert those ISINs to RICs.

1. Set the best_match parameter to false in the get_symbology method

data = ek.get_symbology(["US912810EW46","US912810FG86","US912810UD80"], 
                        from_symbol_type="ISIN", 
                        to_symbol_type="RIC", 
                        best_match=False)
data

It will return a list of matched RICs.

2. Use the get_data method

data, err = ek.get_data(
    ["US912810EW46","US912810FG86","US912810UD80"],
    ["TR.RIC"])
data

3. Use the symbol_conversion in the LSEG Data Library for Python. The example is on GitHub.

ld.open_session()
response = symbol_conversion.Definition(
  symbols=["US912810EW46","US912810FG86","US912810UD80"],
  from_symbol_type=symbol_conversion.SymbolTypes.ISIN,
  to_symbol_types=[
    symbol_conversion.SymbolTypes.RIC]).get_data()
response.data.df

If you are unable to get the data for those coverted RICs or ISINs, please contact the helpdesk team directly via MyAccount to verify the fields and data's availability.