#!/usr/bin/env python
# coding: utf-8

# In[13]:


# [Step 1] Set up the environment
import refinitiv.dataplatform as rdp
import datetime
import pandas as pd
rdp.open_desktop_session('DEFAULT_CODE_BOOK_APP_KEY')


# In[19]:


# [Step 2] Using RDP Search API to retrieve the data
df = rdp.search(
    view = rdp.SearchViews.GovCorpInstruments,
    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 (RCSParentDomicileGenealogy in ('G:53' 'G:3H') and RCSCurrencyLeaf eq 'US Dollar' and RCSCountryGenealogy ne 'M:DQ\G:B6'))",
    select = "RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn"
)
display(df)


# In[20]:


# [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[21]:


# 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[22]:


# 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[23]:


# 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[24]:


# 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[25]:


# 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[26]:


# 3.7 ) Yield
cols = ['RIC','IssuerLegalName','CurrentYield','PricingClosingYield']
y = df[cols]
y = y[y['CurrentYield']>0]


# In[27]:


# 3.7.1 ) Yield top 10
yield_top10 = y.sort_values('CurrentYield', ascending = False).head(10)
yield_top10


# In[28]:


# 3.7.1 ) Yield last 10
yield_last10 = y.sort_values('CurrentYield', ascending = True).head(10)
yield_last10


# In[18]:


rdp.close_session()


# In[ ]: