Skip to content

Instantly share code, notes, and snippets.

@johnukfr

johnukfr/DevRefinitiv Q&A Python RDP search new eg for GovCorpInstruments Secret

Last active August 5, 2022 08:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save johnukfr/241c2b360a30f96371f430005c8d7738 to your computer and use it in GitHub Desktop.
Save johnukfr/241c2b360a30f96371f430005c8d7738 to your computer and use it in GitHub Desktop.
Download ZIP
This is a Python function I made for an article that is (at this point in time) not written yet. It is an unfinished funtion exemplifying how one may use the RDP Python search function to export GovCorp Instrument data. Kudos to Nick and his article: https://developers.refinitiv.com/en/article-catalog/article/building-search-into-your-applicatio…
Raw
DevRefinitiv Q&A Python RDP search new eg for GovCorpInstruments
def GovCorpInstrumentsSearchToExcel(
configurationPath,
session = "desktop.workspace",
orgIDSearch = ["105579", "13709"],
beforeMaturityDate = None, # "2023-03-01"
defaultCurrency = 'EUR',
fields = ['IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName', 'ParentIssuerOrganization',
'ParentLongName', 'RIC', 'PermID', 'IssuerName',
'Description', 'IsMediumTermNote', 'SeniorityType', 'RCSSeniorityType',
'RCSSeniorityTypeName', 'SeniorityTypeDescription', 'MaturityDate',
'IssueDate', 'IsGuaranteed', 'ISIN', 'OriginalIssueCurrency',
'InstrumentType', 'InstrumentTypeDescription', 'HierarchyOrgIDs'],
definedInstrumentTypeDescription = ["Commercial Paper", "FR NEW CP", "FR NEW MTN", "CD"],
filterCurrencies = ['GBP', 'USD', 'EUR'], # can be `None`
removeSubSet = ['Unsecured'], # can be `None`
searchTop = 20, # 10000 max
supersedingOptionalSearchFilter = None,
maturityTableSplitDays = 397,
maturityTableSplitYears = 2,
summaryForSeniorityTypeDescription = False,
toExcel = True,
toCSVs = False,
dateToTablesReturned = True,
audit = True):
"""GovCorpInstrumentsSearchToExcel Pythion Function Version 2.0
One needs a LSEG Refinitiv Workspace or Eikon license and an App Key to use this function.
This Python function returns an Excel Workbook (.xlsx) file with information on Governemnt & Corporate Financial Instruments.
It works by searching for `fields` based on a specified `orgIDSearch` using the LSEG Refinitiv Search API.
It then applies rudimentary manipulations to return data in a convenient way, in a set of tables.
One of these (`summary`) includes simple summs of Bonds' Amount Outstanding.
More on the Refinitiv API here: https://developers.refinitiv.com/en/article-catalog/article/building-search-into-your-application-workflow
Dependencies
----------
Python library 'pandas' version 1.2.4
Python library 'dateutil' version 2.8.1
Python library 'numpy' version 1.21.2
Python library 'refinitiv' version 1.0.0b10 or above (!pip install refinitiv-data)
Parameters
----------
configurationPath: str
This function uses the Refinitiv Data Library in Python. More about it can be found here: https://github.com/Refinitiv-API-Samples/Example.DataLibrary.Python
This library allows you to authenticate yourself to the LSEG Refinitiv data service to retreaive data.
It requieres a configuration file saved in a location that you must specify in this argument `configurationPath`.
You may find an example of the configuration file in question here: https://github.com/Refinitiv-API-Samples/Example.DataLibrary.Python/tree/main/Configuration
You require platform session to acess news data through this api.
E.g.: configurationPath = "C:\\Example.DataLibrary.Python-main\\Example.DataLibrary.Python-main\\Configuration"
session: str
This function uses the Refinitiv Data Library in Python. More about it can be found in the definition of the argument `configurationPath` in this docstring.
There are usually only two types of sessions that you can define in the configuration file needed to use the Refinitiv Data Library, "desktop.workspace" and "platform.rdp"
Default: session="desktop.workspace"
orgIDSearch: list of str
The list of OrgIDs (Organisation IDs) from whichm bond data is asked.
Default: orgIDSearch=["105579", "13709"] # This is just there as an example.
beforeMaturityDate: str
String for a date; only data for bonds maturing before that date would be collected.
If `beforeMaturityDate` is None (i.e.: perpetual), the date '9999-12-31'.
Default: beforeMaturityDate = None # It can be something like `(datetime.now() + relativedelta(days=397)).strftime("%Y-%m-%d")`, `"2023-03-01"`, or `"2023-03-01"` for example.
defaultCurrency: str
String of the 3-lettre-long code for the default currency for which manipulations (e.g.: summs) need to be made.
Default: defaultCurrency = 'EUR'
fields: list of str
List of the fields wished to be requested.
Default: fields = ['IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName', 'ParentIssuerOrganization', 'ParentLongName', 'RIC', 'PermID', 'IssuerName', 'Description', 'IsMediumTermNote', 'SeniorityType', 'RCSSeniorityType', 'RCSSeniorityTypeName', 'SeniorityTypeDescription', 'MaturityDate', 'IssueDate', 'IsGuaranteed', 'ISIN', 'OriginalIssueCurrency', 'InstrumentType', 'InstrumentTypeDescription', 'HierarchyOrgIDs']
definedInstrumentTypeDescription: list of str
List of the InstrumentTypeDescription that is wished to be put in a seperate set in the returned workbook.
Default: definedInstrumentTypeDescription = ["Commercial Paper", "FR NEW CP", "FR NEW MTN", "CD"]
filterCurrencies: list of str or None
List of strings of the 3-lettre-long code of currencies. Only bonds with those default currencies will be requested.
Default: filterCurrencies = ['GBP', 'USD', 'EUR'], # can be `None`
searchTop: int
The Refinitiv search API mimicks the search bar at the top of Workspace/eikon, but allows you to use it programatically.
It can only return data for a certain number of instruments maximum (10000 max).
But you may not want to go to the max, as you may hit your licensed data-limit, so you can set it in `searchTop`.
Default: searchTop = 20, # 10000 max
supersedingOptionalSearchFilter: str or None
Several arguments in this function are there to setup the Search API request call, but not all permutations of what may be asked will be archivable to construct via such a set of arguments.
`supersedingOptionalSearchFilter` is thus there to allow users to write their own search filter with as many parts as they wish.
For more on how to construct a search, please see this page (you may have to go on it twice because of a known ID bug): https://apidocs.refinitiv.com/Apps/ApiDocs#/details/L2Rpc2NvdmVyeS9zZWFyY2gvdjE=/Lw==/POST/README
This filter su[perseeds others built according to other arguments in this function.
Default: supersedingOptionalSearchFilter = None
maturityTableSplitDays: int
This function returns a (.xlsx) that splits data into several sheets, one for bonds maturing before x days, and one maturing at a date after x days and y years.
`maturityTableSplitDays` is that 'x' number of days.
Default: maturityTableSplitDays = 397
maturityTableSplitYears: int
This function returns a (.xlsx) that splits data into several sheets, one for bonds maturing before x days, and one maturing at a date after x days and y years.
`maturityTableSplitYears` is that 'y' number of years.
Default: maturityTableSplitYears = 2
summaryForSeniorityTypeDescription: Boolean
If `True`, then extra sheets are returned for each SeniorityTypeDescription.
Default: summaryForSeniorityTypeDescription = False
toExcel: Boolean:
If `True`, then the data retrieved will be put in a xlsx Workbook.
If `False`, then the data in question is not outputed in an xlsx Workbook, but a tuple of 4 elements (described below in 'Returns' part of this docstring) is still returned in memory to be used in Python.
Default: toExcel = True
toCSVs: Boolean
If `True`, then the data retrieved will be put in a several .csv files.
If `False`, then the data in question is not outputed in an .csv files, but a tuple of 4 elements (described below in 'Returns' part of this docstring) is still returned in memory to be used in Python.
Defalut: toCSVs = False
dateToTablesReturned: Boolean
The outputed tables will be named generically, and might overight pas ones created byt his `GovCorpInstrumentsSearchToExcel` Python function.
Default: dateToTablesReturned = True
audit: Boolean
If `audit` is True, then several tables with the breakdown of the collected data will be outputed.
If False, then only a few tables are shown ('summary, the search filter used and the exchange rates used).
Default: audit = True
Returns
-------
Tuple of 4 elements named currdf, df0, dfPerInstrument and dfSumsPerInstrument respectively:
currdf: pandas fataframe
df0: pandas fataframe
dfPerInstrument: Python dictionary
dfSumsPerInstrument: pandas fataframe
"""
# The next two lineas are not needed in CodeBook. If you would like to run this code outside CodeBook, you will need to point to the configuration file (an example of which you can find here: https://github.com/Refinitiv-API-Samples/Example.DataLibrary.Python/tree/main/Configuration)
import os
os.environ["RD_LIB_CONFIG_PATH"] = configurationPath
import pandas as pd
import numpy as np
from datetime import datetime
from dateutil.relativedelta import relativedelta
import refinitiv.data as rd
from refinitiv.data.content import search
rd.open_session(session) # We have to open our session with LSEG's Refinitiv API from which to call and receive data.
# Certain fields are nessesary for this Python function to work
for i in ['OriginalIssueCurrency' # , 'IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName', 'ParentIssuerOrganization'
]:
if i not in fields: fields = fields + [i]
# Users can choose to use complex filters if wished, elsehow only active debts under a certain maturity date are selected.
if supersedingOptionalSearchFilter == None:
if beforeMaturityDate == None:
appliedFilter = f"IsActive eq true"
else:
appliedFilter = f"IsActive eq true and MaturityDate le {beforeMaturityDate}"
if filterCurrencies != None:
appliedFilter = appliedFilter + " and ("
for j, i in enumerate(filterCurrencies):
appliedFilter = appliedFilter + f"OriginalIssueCurrency eq '{i}'"
if j < len(filterCurrencies) - 1:
appliedFilter = appliedFilter + " or "
appliedFilter = appliedFilter + ")"
else:
appliedFilter = supersedingOptionalSearchFilter
dfAppliedFilter = pd.DataFrame({"Search filter applied": [appliedFilter]})
# Creating a list to be appended in loop below
dfSearchList = [
search.Definition(
view = search.SearchViews.GOV_CORP_INSTRUMENTS,
query = i,
filter = appliedFilter, # Can add `and OriginalIssueCurrency eq 'GBP'`. ge (greater than or equal to), gt (greater than), lt (less than) and le (less than or equal to). These can only be applied to numeric and date properties.
select = ', '.join(fields),
top = searchTop).get_data().data.df
for i in orgIDSearch] # looping through all the OrgIds chosen.
# Now we can concatinate all dataframes in `dfSearchList` into one called `df0`
df0 = pd.concat(dfSearchList, ignore_index=True)
# We ought to keep track of the currencies at play
currList = list(df0.OriginalIssueCurrency.unique())# testCurrL
if defaultCurrency in currList: currList.remove(defaultCurrency)
# Apply currency exchange rates to default currency asked for
if defaultCurrency != "USD": # RICs for exchange rates from USD are slightly differnt to others (RIC: Refinitiv Identifier Codes)
currdf = rd.get_data(
[f"USD{defaultCurrency}=R" if i=="USD" else f"{i}{defaultCurrency}=R" for i in currList], # This line is important, as we are gathering information about currency pairs who's quotes are sometimes reciprical/inverse to what they should be when compared to USD. You can find out which with instruments such as 'GBP/BKGDINFO' and field item 'ROW80_7'.
['CF_CLOSE', 'TR.MIDPRICE.date'])
elif defaultCurrency == "USD":
currdf = rd.get_data(
[f"USD{i}=R" for i in currList],
['CF_CLOSE', 'TR.MIDPRICE.date'])
# Sometimes, no value is returned for 1$ = x of one curency pair. The bellow accounts for this
for i in range(len(currdf)):
if pd.isnull(currdf.CF_CLOSE.iloc[i]): # If no value is returned for this currency pair
iThCurr = rd.get_data(
[currList[i] + "="],
['CF_CLOSE', 'TR.MIDPRICE.date'])
currdf.loc[i] = iThCurr.loc[0]
currdf.index = currList
# Some instruments returned by the search API have no ISINs; that's because they're brand new and not priced or indexed yet.
# E.g.: https://go.refinitiv.com/?u=Y3B1cmw6Ly9hcHBzLmNwLi9BcHBzL0dvdkNvcnAvP3M9MHgwMDEwMmMzNDZhNzEyYTM5JnN0PUVKVkFzc2V0SUQmZ3J2aWV3c3VybD1FVnpCT05EekdDUHhERVNDUnpCQVNJQw%3D%3D&title=GovCorp&key=Y3qr6a%2BWf8j8XoOE30Kd6uZM%2FLDpKO4RDJSWf6fG63Y%3D
df0 = df0[pd.notna(df0.ISIN)]
df0.reset_index(inplace = True) # The `notna` above removes some rows without reseting the index, so we need to do that here.
# Some bonds do not have a maturity date, so we put the maximum date manageable by the Python library `datetime`
df0['MaturityDate'] = df0.MaturityDate.fillna(datetime.max.strftime('%Y-%m-%d'))
# Not all search results come up with `EOMAmountOutstanding` values. We would get more with `TR.CA.AmtOutstanding`. Same with Maturity dates.
try: # Often there's too many instruments in one call, so we need to account for that with thie try loop
dfAmtOutstanding = rd.get_data(
list(df0.ISIN),
['TR.CA.AmtOutstanding.date', 'TR.CA.AmtOutstanding']) # , 'TR.FIMaturityDate'
except:
dfAmtOutstandingList, f = [], ['TR.CA.AmtOutstanding.date', 'TR.CA.AmtOutstanding']
for i in range(0, len(list(df0.ISIN)), 50):
u = list(df0.ISIN)[i:i+50]
try: # Often we can get a 400 error, making many calls back to back increases the chances that we'll hit one, this try loop should make sure the code doesnt stop in its tracks and calls for the data missing again.
amtOutstandingDf = rd.get_data(u[i:i+50], f)
except:
try: amtOutstandingDf = rd.get_data(u[i:i+50], f)
except:
try: amtOutstandingDf = rd.get_data(u[i:i+50], f)
except: amtOutstandingDf = rd.get_data(u[i:i+50], f)
dfAmtOutstandingList.append(amtOutstandingDf)
dfAmtOutstanding = pd.concat(dfAmtOutstandingList, ignore_index=True)
df0['AmountOutstandingDate'] = dfAmtOutstanding['Date']
df0['AmountOutstanding'] = dfAmtOutstanding['Amount Outstanding']
# return df0
AmountOutstandingInDefaultCurrencyList = []
for i in df0.index:
currencyi = df0.iloc[i].OriginalIssueCurrency
if currencyi != defaultCurrency:
currRate = currdf.loc[currencyi][1]
# Sometimes, `AmountOutstanding` is empty, so we have to deal with that
try:
if df0.iloc[i].AmountOutstanding == '':
appendItem = ''
else:
appendItem = float(df0.iloc[i].AmountOutstanding) * float(currRate)
except:
display(df0.iloc[i].AmountOutstanding)
AmountOutstandingInDefaultCurrencyList.append(appendItem)
else:
AmountOutstandingInDefaultCurrencyList.append(
df0.iloc[i].AmountOutstanding)
df0[f"AmountOutstandingIn{defaultCurrency}"] = AmountOutstandingInDefaultCurrencyList
# In order to manipulate our dataframe `df0` date-wise, we need a date column recognised by Python as such
# display(df0[['RIC', 'MaturityDate']]) # TR.FIMaturityDate
# df0[['RIC', 'MaturityDate']].to_csv("test.csv")
df0['MaturityDateInDatetime'] = [datetime.strptime(i[:10], '%Y-%m-%d') for i in df0.MaturityDate]
def SumConvertedFloats(df, columnName):
if len(df[columnName].values) == 0:
return 0
else:
for i in range(len(df[columnName])):
try:
df[columnName].iloc[i] = float(df[columnName].iloc[i])
except:
df[columnName].iloc[i] = np.nan
return df[columnName].sum()
# Now we can split our dataframe in terms of maturities, `SeniorityTypeDescription` and more to get new aggregated insights
dfPerInstrument, sumsPerInstrument = {}, []
for loopIndex, dfiTuple in enumerate(df0.groupby('IssuerOrganization')): # `dfiTuple` is a tuple with the dataframe in 1st place (as opposed to 0th place)
dfi = dfiTuple[1] # We let `dfi` be the ith dataframe through the loop of `df0` for each instrument.
# First we need to compile data per instrument
dfPerInstrument[f"{dfiTuple[0]}"] = dfi
# Second, let's collect the dataframes split per maturities now and add them to the `dfPerInstrument` dictionary for storage outside the ``dfiTuple` loop
dfLessThanXD = dfi[
dfi.MaturityDateInDatetime < datetime.now() + relativedelta(days=maturityTableSplitDays)]
dfPerInstrument[f"{dfiTuple[0]}LessThan{maturityTableSplitDays}D"] = dfLessThanXD
dfMoreThanXD = dfi[
dfi.MaturityDateInDatetime >= datetime.now() + relativedelta(days=maturityTableSplitDays)]
dfBetweenXDAndXY = dfMoreThanXD[
dfMoreThanXD.MaturityDateInDatetime < datetime.now() + relativedelta(years=maturityTableSplitYears)]
dfPerInstrument[
f"{dfiTuple[0]}From{maturityTableSplitDays}DTo{maturityTableSplitYears}Y"] = dfBetweenXDAndXY
# Third, collect insights on the split dataframes, such as summs, and put such info in `summarydf`
dfLessThanXDSum = SumConvertedFloats(df = dfLessThanXD, columnName = f"AmountOutstandingIn{defaultCurrency}")
dfMoreThanXDSum = SumConvertedFloats(df = dfMoreThanXD, columnName = f"AmountOutstandingIn{defaultCurrency}")
dfBetweenXDAndXYSum = SumConvertedFloats(df = dfBetweenXDAndXY, columnName = f"AmountOutstandingIn{defaultCurrency}")
summarydf = pd.DataFrame(
{j: [dfi[j][dfi.index[0]]] for j in [
'IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName',
'ParentIssuerOrganization', 'ParentLongName']})
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Less Than In {maturityTableSplitDays}D"] = [dfLessThanXDSum]
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Between {maturityTableSplitDays}D And {maturityTableSplitYears}Y"] = [dfBetweenXDAndXYSum]
# If a subset was chosen, removing certain maturities from sumations, we ought to include those:
if removeSubSet is not None:
_dfLessThanXD, _dfBetweenXDAndXY = dfLessThanXD, dfBetweenXDAndXY
for i in removeSubSet:
_dfLessThanXD = _dfLessThanXD[_dfLessThanXD.SeniorityTypeDescription != i]
_dfBetweenXDAndXY = _dfBetweenXDAndXY[_dfBetweenXDAndXY.SeniorityTypeDescription != i]
dfPerInstrument[f"{dfiTuple[0]}LessThan{maturityTableSplitDays}DSS"] = _dfLessThanXD
dfPerInstrument[f"{dfiTuple[0]}From{maturityTableSplitDays}DTo{maturityTableSplitYears}YSS"] = _dfLessThanXD
_dfLessThanXDSum = SumConvertedFloats(df = _dfLessThanXD, columnName = f"AmountOutstandingIn{defaultCurrency}")
_dfBetweenXDAndXYSum = SumConvertedFloats(df = _dfBetweenXDAndXY, columnName = f"AmountOutstandingIn{defaultCurrency}")
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Less Than In" +
f" {maturityTableSplitDays}D SS (SubSet) (defined as without Seniorities {str(removeSubSet)})"] = [_dfLessThanXDSum]
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Between {maturityTableSplitDays}D And " +
f"{maturityTableSplitYears}Y SS (SubSet) (defined as without Seniorities {str(removeSubSet)})"] = [_dfBetweenXDAndXYSum]
# Fourth, collect insights per seniority type and keep such info in `sumsPerInstrument` (to then compile in `sumsPerInstrument`)
if summaryForSeniorityTypeDescription == True:
for i in dfi.groupby('SeniorityTypeDescription'):
_sum = SumConvertedFloats(df = i[1], columnName = f"AmountOutstandingIn{defaultCurrency}")
summarydf[f"AmountOutstanding Sum Of SeniorityTypeDescription '{i[0]}' In {defaultCurrency}"] = [_sum]
# Fifth, we can add insights about `definedInstrumentTypeDescription` split of our dataframe as defined in the arguments
dfPerDefinedInstrumentTypeDescription = [
dfi[dfi.InstrumentTypeDescription == i] for i in definedInstrumentTypeDescription]
_df = pd.concat(dfPerDefinedInstrumentTypeDescription)
dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDesc"] = _df
# Sixth, we can filter through the information sived through this far by removing certain types of seniorities
if removeSubSet is not None:
for i in removeSubSet: _df = _df[_df.SeniorityTypeDescription != i]
dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDescSS"] = _df
summaryLongName = f"AmountOutstanding Sum Of InstrumentTypeDescription (defined as {str(definedInstrumentTypeDescription)}) In {defaultCurrency}"
summarydf[summaryLongName] = [SumConvertedFloats(dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDesc"], f"AmountOutstandingIn{defaultCurrency}")]
if removeSubSet is not None:
summarydf[
summaryLongName + f" SS (SubSet) (defined as without Seniorities {str(removeSubSet)})"] = [
SumConvertedFloats(df = dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDescSS"], columnName = f"AmountOutstandingIn{defaultCurrency}")]
sumsPerInstrument.append(summarydf)
dfSumsPerInstrument = pd.concat(sumsPerInstrument)
if dateToTablesReturned == True:
rightNow = ' ' + str(datetime.now()).replace('-', '.').replace(' ', '.').replace(':', '.')
else:
rightNow = ''
# Lastly, let's output everything to an excel workbook if asked int he arguments
if toExcel == True:
with pd.ExcelWriter(f"GovCorpInstruments Search{rightNow}.xlsx") as writer:
dfAppliedFilter.to_excel(writer, sheet_name="searchFilter")
dfSumsPerInstrument.to_excel(writer, sheet_name="summary")
currdf.to_excel(writer, sheet_name="FXRatesApplied")
if audit == True:
df0.to_excel(writer, sheet_name=f"allData")
for i in dfPerInstrument.keys():
dfPerInstrument[i].to_excel(writer, sheet_name=i)
if toCSVs == True: # If you would like the output in several CSV (comma-separated values) files, the below is needed.
dfAppliedFilter.to_csv(f"searchFilter{rightNow}")
dfSumsPerInstrument.to_csv(f"dfSumsPerInstrument{rightNow}.csv")
currdf.to_csv(f"currdf{rightNow}.csv")
if audit == True:
df0.to_csv(f"allData{rightNow}.csv")
for i in dfPerInstrument.keys():
dfPerInstrument[i].to_csv(f"{i}{rightNow}.csv")
rd.close_session() # We have to close our session with LSEG's Refinitiv API from which to call and receive data.
return currdf, df0, dfPerInstrument, dfSumsPerInstrument
from dateutil.relativedelta import relativedelta
testdf = GovCorpInstrumentsSearchToExcel(
configurationPath = "C:\\Example.DataLibrary.Python-main\\Example.DataLibrary.Python-main\\Configuration",
orgIDSearch = ["93444", "68520", "14362", "145052", "16758", "16740", "17983", "12598", "53928", "16880", "18215", "97608", "17092", "62254", "32036", "134625", "106379", "78053", "38025", "28035", "10991", "100112902"],
beforeMaturityDate = None, # (datetime.now() + relativedelta(days=697)).strftime("%Y-%m-%d"), # "2023-03-01", # "2023-03-01"
defaultCurrency = 'USD',
fields = ['IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName', 'ParentIssuerOrganization',
'ParentLongName', 'RIC', 'PermID', 'IssuerName',
'Description', 'IsMediumTermNote', 'SeniorityType', 'RCSSeniorityType',
'RCSSeniorityTypeName', 'SeniorityTypeDescription', 'MaturityDate',
'IssueDate', 'IsGuaranteed', 'ISIN', 'OriginalIssueCurrency',
'InstrumentType', 'InstrumentTypeDescription', 'HierarchyOrgIDs'],
definedInstrumentTypeDescription = ["Commercial Paper", "FR NEW CP", "FR NEW MTN", "CD"],
removeSubSet = None, # can be `None` or a list (e.g.: ['Unsecured'])
filterCurrencies = ["GBP","JPY","EUR"],
searchTop = 100, # 10000 max
supersedingOptionalSearchFilter = None,
maturityTableSplitDays = 397,
maturityTableSplitYears = 6,
summaryForSeniorityTypeDescription = True,
toExcel = True,
toCSVs = False,
dateToTablesReturned=True,
audit = True)
testdf[2]['100112902']
Raw
DevRefinitiv Q&A Python RDP search old eg for GovCorpInstruments
def GovCorpInstrumentsSearchToExcelOldFunction(
RDPAPIKey, # str(api_key.read())
orgIDSearch = ["105579", "13709"],
beforeMaturityDate = None, # "2023-03-01"
defaultCurrency = 'EUR',
fields = ['IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName', 'ParentIssuerOrganization',
'ParentLongName', 'RIC', 'PermID', 'IssuerName',
'Description', 'IsMediumTermNote', 'SeniorityType', 'RCSSeniorityType',
'RCSSeniorityTypeName', 'SeniorityTypeDescription', 'MaturityDate',
'IssueDate', 'IsGuaranteed', 'ISIN', 'OriginalIssueCurrency',
'InstrumentType', 'InstrumentTypeDescription', 'HierarchyOrgIDs'],
definedInstrumentTypeDescription = ["Commercial Paper", "FR NEW CP", "FR NEW MTN", "CD"],
filterCurrencies = ['GBP', 'USD', 'EUR'], # can be `None`
removeSubSet = ['Unsecured'], # can be `None`
searchTop = 20, # 10000 max
supersedingOptionalSearchFilter = None,
maturityTableSplitDays = 397,
maturityTableSplitYears = 2,
summaryForSeniorityTypeDescription = False,
toExcel = True,
toCSVs = False,
dateToTablesReturned = True,
audit = True):
"""GovCorpInstrumentsSearchToExcel Pythion Function Version 1.0
One needs a LSEG Refinitiv Workspace or Eikon license and an App Key to use this function.
    This Python function returns an Excel Workbook (.xlsx) file with information on Governemnt & Corporate Financial Instruments.
It works by searching for `fields` based on a specified `orgIDSearch` using the LSEG Refinitiv Search API.
It then applies rudimentary manipulations to return data in a convenient way, in a set of tables.
One of these (`summary`) includes simple summs of Bonds' Amount Outstanding.
More on the Refinitiv API here: https://developers.refinitiv.com/en/article-catalog/article/building-search-into-your-application-workflow
    Dependencies
    ----------
    Python library 'pandas' version 1.2.4
Python library 'dateutil' version 2.8.1
Python library 'numpy' version 1.21.2
Python library 'refinitiv' version 1.0.0a11.post1
   
    Parameters
    ----------
 
    RDPAPIKey: str
        The App Key as provided in Eikon/Workspace in 'App Key Generator'.
 
    orgIDSearch: list of str
        The list of OrgIDs (Organisation IDs) from whichm bond data is asked.
        Default: orgIDSearch=["105579", "13709"] # This is just there as an example.
 
    beforeMaturityDate: str
        String for a date; only data for bonds maturing before that date would be collected.
If `beforeMaturityDate` is None (i.e.: perpetual), the date '9999-12-31'.
        Default: beforeMaturityDate = None # It can be something like `(datetime.now() + relativedelta(days=397)).strftime("%Y-%m-%d")`, `"2023-03-01"`, or `"2023-03-01"` for example.
defaultCurrency: str
String of the 3-lettre-long code for the default currency for which manipulations (e.g.: summs) need to be made.
Default: defaultCurrency = 'EUR'
fields: list of str
List of the fields wished to be requested.
Default: fields = ['IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName', 'ParentIssuerOrganization', 'ParentLongName', 'RIC', 'PermID', 'IssuerName', 'Description', 'IsMediumTermNote', 'SeniorityType', 'RCSSeniorityType', 'RCSSeniorityTypeName', 'SeniorityTypeDescription', 'MaturityDate', 'IssueDate', 'IsGuaranteed', 'ISIN', 'OriginalIssueCurrency', 'InstrumentType', 'InstrumentTypeDescription', 'HierarchyOrgIDs']
definedInstrumentTypeDescription: list of str
List of the InstrumentTypeDescription that is wished to be put in a seperate set in the returned workbook.
Default: definedInstrumentTypeDescription = ["Commercial Paper", "FR NEW CP", "FR NEW MTN", "CD"]
filterCurrencies: list of str or None
List of strings of the 3-lettre-long code of currencies. Only bonds with those default currencies will be requested.
Default: filterCurrencies = ['GBP', 'USD', 'EUR'], # can be `None`
searchTop: int
The Refinitiv search API mimicks the search bar at the top of Workspace/eikon, but allows you to use it programatically.
It can only return data for a certain number of instruments maximum (10000 max).
But you may not want to go to the max, as you may hit your licensed data-limit, so you can set it in `searchTop`.
Default: searchTop = 20, # 10000 max
supersedingOptionalSearchFilter: str or None
Several arguments in this function are there to setup the Search API request call, but not all permutations of what may be asked will be archivable to construct via such a set of arguments.
`supersedingOptionalSearchFilter` is thus there to allow users to write their own search filter with as many parts as they wish.
For more on how to construct a search, please see this page (you may have to go on it twice because of a known ID bug): https://apidocs.refinitiv.com/Apps/ApiDocs#/details/L2Rpc2NvdmVyeS9zZWFyY2gvdjE=/Lw==/POST/README
This filter su[perseeds others built according to other arguments in this function.
Default: supersedingOptionalSearchFilter = None
maturityTableSplitDays: int
This function returns a (.xlsx) that splits data into several sheets, one for bonds maturing before x days, and one maturing at a date after x days and y years.
`maturityTableSplitDays` is that 'x' number of days.
Default: maturityTableSplitDays = 397
maturityTableSplitYears: int
This function returns a (.xlsx) that splits data into several sheets, one for bonds maturing before x days, and one maturing at a date after x days and y years.
`maturityTableSplitYears` is that 'y' number of years.
Default: maturityTableSplitYears = 2
summaryForSeniorityTypeDescription: Boolean
If `True`, then extra sheets are returned for each SeniorityTypeDescription.
Default: summaryForSeniorityTypeDescription = False
toExcel: Boolean:
If `True`, then the data retrieved will be put in a xlsx Workbook.
If `False`, then the data in question is not outputed in an xlsx Workbook, but a tuple of 4 elements (described below in 'Returns' part of this docstring) is still returned in memory to be used in Python.
Default: toExcel = True
toCSVs: Boolean
If `True`, then the data retrieved will be put in a several .csv files.
If `False`, then the data in question is not outputed in an .csv files, but a tuple of 4 elements (described below in 'Returns' part of this docstring) is still returned in memory to be used in Python.
Defalut: toCSVs = False
dateToTablesReturned: Boolean
The outputed tables will be named generically, and might overight pas ones created byt his `GovCorpInstrumentsSearchToExcel` Python function.
Default: dateToTablesReturned = True
audit: Boolean
If `audit` is True, then several tables with the breakdown of the collected data will be outputed.
If False, then only a few tables are shown ('summary, the search filter used and the exchange rates used).
Default: audit = True
    Returns
    -------
 
    Tuple of 4 elements named currdf, df0, dfPerInstrument and dfSumsPerInstrument respectively:
currdf: pandas fataframe
df0: pandas fataframe
dfPerInstrument: Python dictionary
dfSumsPerInstrument: pandas fataframe
    """
import pandas as pd
from datetime import datetime
from dateutil.relativedelta import relativedelta
import refinitiv.dataplatform as rdp
rdp.open_desktop_session(RDPAPIKey)
# Certain fields are nessesary for this Python function to work
for i in ['OriginalIssueCurrency'
# , 'IssuerOrgid', 'IssuerOrganization',
# 'ParentIssuerName', 'ParentIssuerOrganization'
]:
if i not in fields: fields = fields + [i]
# Users can choose to use complex filters if wished, elsehow only active debts under a certain maturity date are selected.
if supersedingOptionalSearchFilter == None:
if beforeMaturityDate == None:
appliedFilter = f"IsActive eq true"
else:
appliedFilter = f"IsActive eq true and MaturityDate le {beforeMaturityDate}"
if filterCurrencies != None:
appliedFilter = appliedFilter + " and ("
for j, i in enumerate(filterCurrencies):
appliedFilter = appliedFilter + f"OriginalIssueCurrency eq '{i}'"
if j < len(filterCurrencies) - 1:
appliedFilter = appliedFilter + " or "
appliedFilter = appliedFilter + ")"
else:
appliedFilter = supersedingOptionalSearchFilter
dfAppliedFilter = pd.DataFrame({"Search filter applied": [appliedFilter]})
# Creating a list to be appended in loop below
dfSearchList = [
rdp.search(
view = rdp.SearchViews.GovCorpInstruments,
query = i,
filter = appliedFilter, # Can add `and OriginalIssueCurrency eq 'GBP'`. ge (greater than or equal to), gt (greater than), lt (less than) and le (less than or equal to). These can only be applied to numeric and date properties.
select = ', '.join(fields),
top = searchTop)
for i in orgIDSearch] # looping through all the OrgIds chosen.
# Now we can concatinate all dataframes in `dfSearchList` into one called `df0`
df0 = pd.concat(dfSearchList, ignore_index=True)
# We ought to keep track of the currencies at play
currList = list(df0.OriginalIssueCurrency.unique())
if defaultCurrency in currList: currList.remove(defaultCurrency)
# Apply currency exchange rates to default currency asked for
import refinitiv.dataplatform.eikon as ek
ek.set_app_key(RDPAPIKey)
if defaultCurrency != "USD": # RICs for exchange rates from USD are slightly differnt to others (RIC: Refinitiv Identifier Codes)
currdf = ek.get_data(
[f"{defaultCurrency}=" if i=="USD" else f"{i}{defaultCurrency}=R" for i in currList],
['CF_CLOSE', 'TR.MIDPRICE.date'])[0]
elif defaultCurrency == "USD":
currdf = ek.get_data(
[f"USD{i}=R" for i in currList],
['CF_CLOSE', 'TR.MIDPRICE.date'])[0]
# Some instruments returned by the search API have no ISINs; that's because they're brand new and not priced or indexed yet.
# E.g.: https://go.refinitiv.com/?u=Y3B1cmw6Ly9hcHBzLmNwLi9BcHBzL0dvdkNvcnAvP3M9MHgwMDEwMmMzNDZhNzEyYTM5JnN0PUVKVkFzc2V0SUQmZ3J2aWV3c3VybD1FVnpCT05EekdDUHhERVNDUnpCQVNJQw%3D%3D&title=GovCorp&key=Y3qr6a%2BWf8j8XoOE30Kd6uZM%2FLDpKO4RDJSWf6fG63Y%3D
df0 = df0[pd.notna(df0.ISIN)]
df0.reset_index(inplace = True) # The `notna` above removes some rows without reseting the index, so we need to do that here.
# Some bonds do not have a maturity date, so we put the maximum date manageable by the Python library `datetime`
df0['MaturityDate'] = df0.MaturityDate.fillna(datetime.max.strftime('%Y-%m-%d'))
# Not all search results come up with `EOMAmountOutstanding` values. We would get more with `TR.CA.AmtOutstanding`. Same with Maturity dates.
dfAmtOutstanding = ek.get_data(
list(df0.ISIN),
['TR.CA.AmtOutstanding.date', 'TR.CA.AmtOutstanding'])[0] # , 'TR.FIMaturityDate'
df0['AmountOutstandingDate'] = dfAmtOutstanding['Date']
df0['AmountOutstanding'] = dfAmtOutstanding['Amount Outstanding']
currdf = ek.get_data(
[f"{defaultCurrency}=" if i=="USD" else f"{i}{defaultCurrency}=R" for i in currList],
['CF_CLOSE', 'TR.MIDPRICE.date'])[0]
currdf.index = currList
# return df0, currdf, currList
AmountOutstandingInDefaultCurrencyList = []
for i in df0.index:
currencyi = df0.iloc[i].OriginalIssueCurrency
if currencyi != defaultCurrency:
currRate = currdf.loc[currencyi][1]
AmountOutstandingInDefaultCurrencyList.append(
df0.iloc[i].AmountOutstanding * currRate)
else:
AmountOutstandingInDefaultCurrencyList.append(
df0.iloc[i].AmountOutstanding)
df0[f"AmountOutstandingIn{defaultCurrency}"] = AmountOutstandingInDefaultCurrencyList
# In order to manipulate our dataframe `df0` date-wise, we need a date column recognised by Python as such
# display(df0[['RIC', 'MaturityDate']]) # TR.FIMaturityDate
# df0[['RIC', 'MaturityDate']].to_csv("test.csv")
df0['MaturityDateInDatetime'] = [datetime.strptime(i[:10], '%Y-%m-%d') for i in df0.MaturityDate]
# Now we can split our dataframe in terms of maturities, `SeniorityTypeDescription` and more to get new aggregated insights
dfPerInstrument, sumsPerInstrument = {}, []
for dfiTuple in df0.groupby('IssuerOrganization'): # `dfiTuple` is a tuple with the dataframe in 1st place (as opposed to 0th place)
dfi = dfiTuple[1] # We let `dfi` be the ith dataframe through the loop of `df0` for each instrument.
# First we need to compile data per instrument
dfPerInstrument[f"{dfiTuple[0]}"] = dfi
# Second, let's collect the dataframes split per maturities now and add them to the `dfPerInstrument` dictionary for storage outside the ``dfiTuple` loop
dfLessThanXD = dfi[
dfi.MaturityDateInDatetime < datetime.now() + relativedelta(days=maturityTableSplitDays)]
dfPerInstrument[f"{dfiTuple[0]}LessThan{maturityTableSplitDays}D"] = dfLessThanXD
dfMoreThanXD = dfi[
dfi.MaturityDateInDatetime >= datetime.now() + relativedelta(days=maturityTableSplitDays)]
dfBetweenXDAndXY = dfMoreThanXD[
dfMoreThanXD.MaturityDateInDatetime < datetime.now() + relativedelta(years=maturityTableSplitYears)]
dfPerInstrument[
f"{dfiTuple[0]}From{maturityTableSplitDays}DTo{maturityTableSplitYears}Y"] = dfBetweenXDAndXY
# Third, collect insights on the split dataframes, such as summs, and put such info in `summarydf`
dfLessThanXDSum = dfLessThanXD[f"AmountOutstandingIn{defaultCurrency}"].sum()
dfMoreThanXDSum = dfMoreThanXD[f"AmountOutstandingIn{defaultCurrency}"].sum()
dfBetweenXDAndXYSum = dfBetweenXDAndXY[f"AmountOutstandingIn{defaultCurrency}"].sum()
summarydf = pd.DataFrame(
{j: [dfi[j][dfi.index[0]]] for j in [
'IssuerOrgid', 'IssuerOrganization', 'ParentIssuerName',
'ParentIssuerOrganization', 'ParentLongName']})
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Less Than In {maturityTableSplitDays}D"] = [dfLessThanXDSum]
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Between {maturityTableSplitDays}D And {maturityTableSplitYears}Y"] = [dfBetweenXDAndXYSum]
# If a subset was chosen, removing certain maturities from sumations, we ought to include those:
if removeSubSet is not None:
_dfLessThanXD, _dfBetweenXDAndXY = dfLessThanXD, dfBetweenXDAndXY
for i in removeSubSet:
_dfLessThanXD = _dfLessThanXD[_dfLessThanXD.SeniorityTypeDescription != i]
_dfBetweenXDAndXY = _dfBetweenXDAndXY[_dfBetweenXDAndXY.SeniorityTypeDescription != i]
dfPerInstrument[f"{dfiTuple[0]}LessThan{maturityTableSplitDays}DSS"] = _dfLessThanXD
dfPerInstrument[f"{dfiTuple[0]}From{maturityTableSplitDays}DTo{maturityTableSplitYears}YSS"] = _dfLessThanXD
_dfLessThanXDSum = _dfLessThanXD[f"AmountOutstandingIn{defaultCurrency}"].sum()
_dfBetweenXDAndXYSum = _dfBetweenXDAndXY[f"AmountOutstandingIn{defaultCurrency}"].sum()
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Less Than In" +
f" {maturityTableSplitDays}D SS (SubSet) (defined as without Seniorities {str(removeSubSet)})"] = [_dfLessThanXDSum]
summarydf[
f"AmountOutstandingIn{defaultCurrency} Sum Maturing Between {maturityTableSplitDays}D And " +
f"{maturityTableSplitYears}Y SS (SubSet) (defined as without Seniorities {str(removeSubSet)})"] = [_dfBetweenXDAndXYSum]
# Fourth, collect insights per seniority type and keep such info in `sumsPerInstrument` (to then compile in `sumsPerInstrument`)
if summaryForSeniorityTypeDescription == True:
for i in dfi.groupby('SeniorityTypeDescription'):
_sum = i[1][f"AmountOutstandingIn{defaultCurrency}"].sum()
summarydf[f"AmountOutstanding Sum Of SeniorityTypeDescription '{i[0]}' In {defaultCurrency}"] = [_sum]
# Fifth, we can add insights about `definedInstrumentTypeDescription` split of our dataframe as defined in the arguments
dfPerDefinedInstrumentTypeDescription = [
dfi[dfi.InstrumentTypeDescription == i] for i in definedInstrumentTypeDescription]
_df = pd.concat(dfPerDefinedInstrumentTypeDescription)
dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDesc"] = _df
# Sixth, we can filter through the information sived through this far by removing certain types of seniorities
if removeSubSet is not None:
for i in removeSubSet: _df = _df[_df.SeniorityTypeDescription != i]
dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDescSS"] = _df
summaryLongName = f"AmountOutstanding Sum Of InstrumentTypeDescription (defined as {str(definedInstrumentTypeDescription)}) In {defaultCurrency}"
summarydf[summaryLongName] = [dfPerInstrument[
f"{dfiTuple[0]}PerDefInstTypDesc"][f"AmountOutstandingIn{defaultCurrency}"].sum()]
if removeSubSet is not None:
summarydf[
summaryLongName + f" SS (SubSet) (defined as without Seniorities {str(removeSubSet)})"] = [
dfPerInstrument[f"{dfiTuple[0]}PerDefInstTypDescSS"][f"AmountOutstandingIn{defaultCurrency}"].sum()]
sumsPerInstrument.append(summarydf)
dfSumsPerInstrument = pd.concat(sumsPerInstrument)
if dateToTablesReturned == True:
rightNow = ' ' + str(datetime.now()).replace('-', '.').replace(' ', '.').replace(':', '.')
else:
rightNow = ''
# Lastly, let's output everything to an excel workbook if asked int he arguments
if toExcel == True:
with pd.ExcelWriter(f"GovCorpInstruments Search{rightNow}.xlsx") as writer:
dfAppliedFilter.to_excel(writer, sheet_name="searchFilter")
dfSumsPerInstrument.to_excel(writer, sheet_name="summary")
currdf.to_excel(writer, sheet_name="FXRatesApplied")
if audit == True:
df0.to_excel(writer, sheet_name=f"allData")
for i in dfPerInstrument.keys():
dfPerInstrument[i].to_excel(writer, sheet_name=i)
if toCSVs == True:
dfAppliedFilter.to_csv(f"searchFilter{rightNow}")
dfSumsPerInstrument.to_csv(f"dfSumsPerInstrument{rightNow}.csv")
currdf.to_csv(f"currdf{rightNow}.csv")
if audit == True:
df0.to_csv(f"allData{rightNow}.csv")
for i in dfPerInstrument.keys():
dfPerInstrument[i].to_csv(f"{i}{rightNow}.csv")
return currdf, df0, dfPerInstrument, dfSumsPerInstrument
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment