#!/usr/bin/env python # coding: utf-8 # ### Searching for KungFu Bonds (Chinese-issued U.S. dollars bonds) with the Data Library - Search function # In[1]: # [Step 1] Set up the environment import lseg.data as ld from lseg.data.content import search import datetime import pandas as pd ld.open_session() # In[2]: # [Step 2] Using search function to retrieve the data df = ld.discovery.search( view = ld.discovery.Views.GOV_CORP_INSTRUMENTS, top = 10000, filter = "((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))", select = "RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn,RCSRiskOrganisationCountry", ) display(df) # In[3]: # [Step 2] Using search function to retrieve the data df = ld.discovery.search( view = ld.discovery.Views.GOV_CORP_INSTRUMENTS, top = 10000, filter = "((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))", select = "RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn", ) display(df) # In[5]: response = search.Definition( view = search.Views.GOV_CORP_INSTRUMENTS, filter = "((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))", top = 0, navigators = "RCSRiskOrganisationCountry" ).get_data() response.data.raw["Navigators"]["RCSRiskOrganisationCountry"]["Buckets"] # In[6]: filter1 = "(RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3I')" filter2 = "(RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U')" def bond_search(filter): response = search.Definition( view = search.Views.GOV_CORP_INSTRUMENTS, filter = f"((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ({filter} and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))", top = 10000, select = "RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn" ).get_data() df = response.data.df return df df1 = bond_search(filter1) df2 = bond_search(filter2) # In[7]: df = pd.concat([df1, df2], ignore_index=True) df # In[8]: # [Step 3] Visualize the Kungfu bonds # 3.1 ) TRBC Pie import plotly.express as px rt = df.groupby("RCSTRBC2012Leaf",as_index=False).agg('count') rt = rt.sort_values('RIC', ascending = False).head(10) fig = px.pie(rt, values='RIC', names='RCSTRBC2012Leaf', title='TRBC Pie',color_discrete_sequence=px.colors.sequential.RdBu) fig.show() # In[9]: # 3.2 ) Bond Grade Pie df['RCSBondGradeLeaf'].fillna("No Grade", inplace=True) grad = df.groupby("RCSBondGradeLeaf",as_index=False).agg('count') fig = px.pie(grad, values='RIC', names='RCSBondGradeLeaf', title='Bond Grade Pie',color_discrete_sequence=px.colors.sequential.RdBu) fig.show() # In[10]: # 3.3 ) Issued amount last 12 months from datetime import date from dateutil.relativedelta import relativedelta decb = date.today() + relativedelta(months=-12) start = datetime.datetime(decb.year, decb.month, 1).strftime("%Y-%m-%d %H:%M:%S")[0:10] end = date.today().strftime("%Y-%m-%d %H:%M:%S")[0:10] cols = ['RIC','IssueDate','FaceIssuedTotal'] c = df[cols].copy() c.loc[:,"IssueDate"] = pd.to_datetime(c["IssueDate"]) c = c.set_index("IssueDate").sort_index(ascending=False) c_month = c.resample('BM').sum() c_monthb = c_month[start:end] c_monthb.index.name = None import plotly.express as px fig = px.bar(c_monthb, y='FaceIssuedTotal', title = 'Issued amount last 12 months', color_discrete_sequence=px.colors.sequential.RdBu) fig.show() # In[11]: # 3.4 ) Redem amount in 10 years yearsf = date.today() + relativedelta(years=+10) end = datetime.datetime(yearsf.year, yearsf.month, 1).strftime("%Y-%m-%d %H:%M:%S")[0:4] start = date.today().strftime("%Y-%m-%d %H:%M:%S")[0:4] cols = ['RIC','PricingRedemDate','FaceIssuedTotal'] d = df[cols].copy() d.loc[:,"PricingRedemDate"] = pd.to_datetime(d["PricingRedemDate"], errors = 'coerce') d = d.set_index("PricingRedemDate").sort_index(ascending=True) d_month = d.resample('BY').sum() d_monthf = d_month[start:end] d_monthf.index.name = None import plotly.express as px fig = px.bar(d_monthf, y='FaceIssuedTotal',title = 'Redem amount in 10 years', color_discrete_sequence=px.colors.sequential.RdBu) fig.show() # In[12]: # 3.5 ) Newly issued bonds last 3 months monb3 = date.today() + relativedelta(months=-3) start = datetime.datetime(monb3.year, monb3.month, 1).strftime("%Y-%m-%d %H:%M:%S")[0:10] end = date.today().strftime("%Y-%m-%d %H:%M:%S")[0:10] print(start, end) cols = ['RIC','IssueDate','FaceIssuedTotal','RCSTRBC2012Leaf'] f = df[cols].copy() f.loc[:,"IssueDate"] = pd.to_datetime(f["IssueDate"]) f = f.set_index("IssueDate").sort_index(ascending=True) f_3m = f[start:end] rt = f_3m.groupby("RCSTRBC2012Leaf",as_index=False).agg('count') rt = rt.sort_values('RIC', ascending = False).head(10) fig = px.pie(rt, values='RIC', names='RCSTRBC2012Leaf', title='newly issued bonds last 3 months',color_discrete_sequence=px.colors.sequential.RdBu) fig.show() # In[13]: # 3.6 ) Price top 10 cols = ['RIC','IssuerLegalName','EOMPriceReturn','EOMPriceChange'] price = df[cols].copy() price_top10 = price.sort_values('EOMPriceReturn', ascending = False).head(10) price_top10 # In[14]: # 3.7 ) Yield cols = ['RIC','IssuerLegalName','CurrentYield','PricingClosingYield'] y = df[cols] y = y[y['CurrentYield']>0] # In[15]: # 3.7.1 ) Yield top 10 yield_top10 = y.sort_values('CurrentYield', ascending = False).head(10) yield_top10 # In[16]: # 3.7.2 ) Yield last 10 yield_last10 = y.sort_values('CurrentYield', ascending = True).head(10) yield_last10 # In[17]: ld.close_session()