def cd6M():
df = do.get_data('spreads')
cash = do.get_data('cash_cost')
cash.index = cash.date
df.index = df.date
df = df['2015':]
df['cd:6M-R007'] = df['cd_AAA_6m'] - cash['R007']
df['R007'] = cash['R007']
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date,0,df['cd:6M-R007'],\
label='cd:6M-R007',color='lightgrey',alpha=1)
ax_ = ax.twinx()
df[['R007', 'cd_AAA_6m']].plot(ax=ax_)
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(-0.05, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(ncol=2,
loc=3,
bbox_to_anchor=(0.35, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def cd_r007():
cash = do.get_data('cash_cost')
cash.index = cash.date
df = do.get_data('rates')
df.index = df.date
df = df['2015':]
df['存单-DR007'] = (df['cd_3m_aaa+'] - cash['DR007']) * 100
df['DR007'] = cash['DR007']
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date,0,df['存单-DR007'],\
label='存单-DR007',color='lightgrey',alpha=1)
ax_ = ax.twinx()
df[['cd_3m_aaa+', 'DR007']].plot(ax=ax_)
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.05, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(['存单:3个月:AAA+','DR007'],\
ncol=1,loc=3, bbox_to_anchor=(0.45,-0.7),borderaxespad = 0.,fontsize=10,frameon=False)
return fig
def gk(year=1):
df = do.get_data('rates')
df.index = df.date
df = df['2007':]
a = df[['国开' + str(year) + '年']]
q25 = np.percentile(a.values, 25, interpolation='linear')
q75 = np.percentile(a.values, 75, interpolation='linear')
med = np.median(a.values)
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
a.iloc[:, 0].plot(ax=ax, label='')
ax.axhline(y=q25, ls='--', color='lightgrey')
ax.axhline(y=q75, ls='--', color='lightgrey')
ax.axhline(y=med, ls='-', color='orange')
ax.legend(['国开' + str(year) + '年'],
ncol=1,
loc=3,
bbox_to_anchor=(0.3, -0.65),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax.set_xlabel('')
return fig
def rate_7D():
df = do.get_data('cash_amt_prc')
df.index = df.date
df = df['2019':]
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['R007', 'GC007']].dropna().plot(ax=ax)
ax_ = ax.twinx()
df[['DR007']].plot(ax=ax_, color='grey')
ax.set_xlabel('')
ax.legend(ncol=2,
loc=3,
bbox_to_anchor=(0.05, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.7, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax.set_xlabel('')
return fig
def r_dr():
df = do.get_data('cash_cost')
df.index = df.date
df = df['2015':]
df['R007-DR007'] = df['R007'] - df['DR007']
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date, 0, df['R007-DR007'], \
label = 'R007-DR007',color='lightgrey',alpha=1)
ax_ = ax.twinx()
df[['R007', 'DR007']].plot(ax=ax_)
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.0, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(ncol=2,
loc=3,
bbox_to_anchor=(0.4, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def msP():
df = do.get_data('rates')
df.index = df.date
df = df['2010':]
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['中票_AAA_1y', '中票_AAA_5y', '中票_AA+_1y', '中票_AA+_5y']].plot(ax=ax)
ax.axhline(y=np.median(df['中票_AAA_5y']),
ls='--',
color='grey',
label='5Y:AAA:中位数')
ax.axhline(y=np.median(df['中票_AA+_5y']),
ls='--',
color='black',
label='5Y:AA+:中位数')
ax.set_xlabel('')
ax.set_title('中票收益率')
ax.legend(ncol=2,
loc=3,
bbox_to_anchor=(0.05, -1),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def vol_1D():
df = do.get_data('cash_amt_prc')
df.index = df.date
df = df['2019':].dropna()
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date, 0, df['成交量:银行间质押式回购'], \
label = '成交量:银行间质押式回购',color='lightgrey',alpha=1)
plt.xticks(rotation=30)
ax_ = ax.twinx()
df[['R001']].plot(ax=ax_)
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.05, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.7, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def gk_local():
cash = do.get_data('cash_cost')
cash.index = cash.date
df = do.get_data('spreads')
df.index = df.date
df = df['2016':]
df['国开债:10年-R007'] = df['国开10年'] - cash['R007']
df['地方债:3年:AAA-R007'] = df['地方债_AAA_3y'] - cash['R007']
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['国开债:10年-R007', '地方债:3年:AAA-R007']].plot(ax=ax, ylim=(-3, 3))
ax.set_xlabel('')
ax.legend(ncol=3,
loc=3,
bbox_to_anchor=(-0.05, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def msPaper():
cash = do.get_data('cash_cost')
cash.index = cash.date
df = do.get_data('spreads')
df.index = df.date
df = df['2016':]
df['1年'] = df['中短票_AA+_1y'] - cash['R007']
df['3年'] = df['中短票_AA+_3y'] - cash['R007']
df['5年'] = df['中短票_AA+_5y'] - cash['R007']
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['1年', '3年', '5年']].plot(ax=ax)
ax.set_xlabel('')
ax.legend(ncol=3,
loc=3,
bbox_to_anchor=(0.10, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax.set_title('中短票:AA+-R007')
return fig
def irs():
df = do.get_data('spreads')
df.index = df.date
df = df['2009':]
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['IRS_1y_FR007', 'IRS_5y_FR007', 'IRS_5y_shibor3m']].plot(ax=ax)
ax.set_xlabel('')
ax.legend(ncol=3,
loc=3,
bbox_to_anchor=(-0.15, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def RepoRate():
df = do.get_data('cash_amt_prc')
df.index = df.date
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['R001','R007','R021']].dropna()['2019':].plot(ax=ax,\
color=['#3778bf','#f0833a','grey'])
ax.legend(ncol=3,
loc=3,
bbox_to_anchor=(0.05, -0.8),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax.set_xlabel('')
return fig
def R_GC_7D():
df = do.get_data('cash_cost')
df.index = df.date
df = df['2016-01':]
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
df[['R007', 'GC007']].plot(ax=ax)
ax.set_xlabel('')
ax.legend(ncol=2,
loc=3,
bbox_to_anchor=(0.15, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def yieldCurve(bond='gz'):
df = do.get_data('rates')
df.index = df.date
df = df['2007':]
today = df.index[-1]
base = df['2020':'2020'].index[-1]
if bond == 'gz':
name = '国债'
elif bond == 'gk':
name = '国开'
else:
print('error')
return
m = ['3月', '6月', '1年', '3年', '5年', \
'7年', '10年', '20年', '30年']
l = [name + x for x in m]
d = pd.DataFrame(columns=[0, 1, 2, 3, 6, 9, 12, 24, 36])
d.loc[today.date()] = df.loc[today, l].tolist()
d.loc[base.date()] = df.loc[base, l].tolist()
d.loc['25分位数'] = [np.quantile(df[x], 0.25) for x in l]
d.loc['75分位数'] = [np.quantile(df[x], 0.75) for x in l]
d.loc['中位数'] = [np.quantile(df[x], 0.5) for x in l]
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
d.loc[today.date()].plot(ax=ax, label='现值')
d.loc[base.date()].plot(ax=ax, color='red', label='2020年底')
d.loc['25分位数'].plot(ax=ax, ls='--', color='lightgrey', alpha=1)
d.loc['75分位数'].plot(ax=ax, ls='--', color='lightgrey', alpha=1)
d.loc['中位数'].plot(ax=ax, color='orange', alpha=0.3)
ax.set_xticks([0, 3, 6, 9, 12, 24, 36])
ax.set_xticklabels(['3M', '3Y', '5Y', '7Y', '10Y', '20Y', '30Y'])
ax.legend(ncol=3,
loc=3,
bbox_to_anchor=(-0.02, -0.65),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax.set_title(name + '到期收益率曲线')
return fig
def CurveChange(bond='gz'):
df = do.get_data('rates')
df.index = df.date
df = df['2021':]
base = df.index[0]
end = df.index[-1]
if bond == 'gz':
name = '国债'
elif bond == 'gk':
name = '国开'
else:
print('error')
return
m = ['6月', '1年', '3年', '5年', '7年', '10年']
l = [name + x for x in m]
d = pd.DataFrame([], columns=range(6))
d.loc[base] = df.loc[base, l].tolist()
d.loc[end] = df.loc[end, l].tolist()
d.loc['期间变动(BP)'] = (d.loc[end] - d.loc[base]) * 100
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
d.loc['期间变动(BP)'].plot(ax=ax, kind='bar', color='lightgrey')
ax_ = ax.twinx()
d.loc[end].plot(ax=ax_)
d.loc[base].plot(ax=ax_)
ax.set_xticklabels(['6M', '1Y', '3Y', '5Y', '7Y', '10Y'], rotation=0)
ax.set_title(name + '收益率变动')
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.05, -0.5),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend([base.date(), end.date()],
ncol=1,
loc=3,
bbox_to_anchor=(0.6, -0.65),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
def new_gk_old():
secondary_rate_sec = do.get_data('secondary_rate_sec')
#筛选200205
secondary_rate_sec1 = secondary_rate_sec.loc[secondary_rate_sec['代码'] ==
'200205.IB']
a = secondary_rate_sec1.groupby(['date'])['价格'].mean()
#筛选200210
secondary_rate_sec1 = secondary_rate_sec.loc[secondary_rate_sec['代码'] ==
'200210.IB']
b = secondary_rate_sec1.groupby(['date'])['价格'].mean()
df = pd.concat([a, b], axis=1)
df.columns = ['200205', '200210']
df['200205-200210'] = (df['200205'] - df['200210']) * 100
#国开债新老券利差
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
plt.plot(df.index, df['200205'], '#3778bf', label="200205")
plt.plot(df.index, df['200210'], '#f0833a', label='200210')
plt.legend(ncol=3,
loc=3,
bbox_to_anchor=(0, -0.9),
borderaxespad=0.,
fontsize=10,
frameon=False)
plt.xticks(fontsize=10, rotation=45)
plt.yticks(fontsize=10, rotation=0)
plt.twinx()
plt.bar(df.index,
df['200205-200210'],
width=1,
color='gray',
alpha=0.2,
label='200205-200210')
plt.legend(ncol=3,
loc=3,
bbox_to_anchor=(0.6, -0.9),
borderaxespad=0.,
fontsize=10,
frameon=False)
plt.ylabel('（BP）', fontsize=10)
plt.title('国开债新老券利差', fontsize=12)
return fig
def gk_gz():
df = do.get_data('rates')
df.index = df.date
df = df['2009':]
df['国开债-国债'] = (df['国开10年'] - df['国债10年']) * 100
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date,0,df['国开债-国债'],\
label='国开债-国债',color='lightgrey',alpha=1)
ax_ = ax.twinx()
df[['国开10年', '国债10年']].plot(ax=ax_)
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.05, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(['国开债:10年','国债:10年'],\
ncol=1,loc=3, bbox_to_anchor=(0.45,-0.7),borderaxespad = 0.,fontsize=10,frameon=False)
return fig
def local_gz():
df = do.get_data('rates')
df.index = df.date
df = df['2013':]
df['地方债-国债'] = (df['地方5年'] - df['国债5年']) * 100
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date,0,df['地方债-国债'],\
label='地方债-国债',color='lightgrey',alpha=1)
ax_ = ax.twinx()
df[['地方5年', '国债5年']].plot(ax=ax_)
ax_.set_yticks(np.arange(1, 6))
ax.set_yticks(range(0, 100, 20))
ax.legend(ncol=1,
loc=3,
bbox_to_anchor=(0.05, -0.6),
borderaxespad=0.,
fontsize=10,
frameon=False)
ax_.legend(['地方债:5年:AAA','国债:5年'],\
ncol=1,loc=3, bbox_to_anchor=(0.45,-0.7),borderaxespad = 0.,fontsize=10,frameon=False)
return fig
def volRatio():
df = do.get_data('repo_volume')
df.index = df.date
df = df['2015':]
# df['tmp_sum'] =
df[['隔夜回购占比','七天回购占比']]=\
df[['成交量:R001','成交量:R007']].div(df[['成交量:R001','成交量:R007']].sum(axis=1),axis=0)
plt.style.use({'font.size': 10})
fig, ax = plt.subplots(figsize=(4.15, 1.42), dpi=300)
ax.fill_between(df.date, 0, df['隔夜回购占比'], \
label = '隔夜回购占比',color='lightgrey',alpha=1)
ax.fill_between(df.date, 0, df['七天回购占比'], \
label = '七天回购占比',color='orange',alpha=1)
ax.legend(ncol=2,
loc=3,
bbox_to_anchor=(0.05, -0.45),
borderaxespad=0.,
fontsize=10,
frameon=False)
return fig
import data_organize as do
data = do.get_data('secondary_rate_sec')
data = do.get_data('primary_isu')
rates = do.get_data('rates')
rates.index = rates.date
from matplotlib import pyplot as plt
rates.loc[rates.date > '2020-03-01', '国开10年'].plot()
date_list = data.date.unique()
date_list.sort()
for date in date_list:
df = data.loc[data.date == date]
print(date, end=',')
print(df.loc[df['代码'] == '200215.IB'].shape[0], end=':')
print(df.loc[df['代码'] == '210205.IB'].shape[0])
d
import datetime as dt #准备工作，配置环境。
import seaborn as sns
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
import datetime
from pylab import mpl
plt.rcParams['font.family'] = ['STKaiti']
mpl.rcParams['axes.unicode_minus'] = False
import data_organize as do
global rates
cash_cost = do.get_data('cash_cost')
cash_cost.index = cash_cost.date
rates = do.get_data('rates')
rates.index = rates.date
rates_us = do.get_data('rates_us')
rates_us.index = rates_us.date
rates0 = rates.loc[rates['date'] >= '2019-01-01']
#计算利差
margin1 = rates0['国债7年'] - rates0['国债10年']
margin2 = rates0['国开7年'] - rates0['国开10年']
margin3 = rates0['国债30年'] - rates0['国债10年']
margin4 = rates0['国债10年'] - rates0['国债1年']
margin5 = rates0['国债3年'] - rates0['国债1年']
margin6 = rates0['国开10年'] - rates0['农发10年']
margin7 = rates0['国开10年'] - rates0['口行10年']
import pandas as pd
import data_organize as do
from sqlalchemy.types import String, Float, Integer,VARCHAR
from sqlalchemy import DateTime
df = pd.read_excel('./cd.xlsx')
name = 'interbank_dps_vol_weekly'
data = do.get_data(name)
dff = df[['截止日期','总发行量(亿元)', '总偿还量(亿元)', '净融资额(亿元)']]
dff.columns = data.columns
dff
d = dff.iloc[:3,:].append(\
data.iloc[2:,:]\
)
columns_type=[DateTime(),Float(),Float(),Float()]
dtypelist = dict(zip(d.columns,columns_type))
conn,engine = do.get_db_conn()
for a,b,c in [(d,name,dtypelist)]:
a.to_sql(name=b,con = engine,schema='finance',if_exists = 'replace',index=False,dtype=c)
print(b, '写入完成')
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
import datetime as dt
from matplotlib.backends.backend_pdf import PdfPages
import pymysql
from sqlalchemy.types import String, Float, Integer, VARCHAR
from sqlalchemy import DateTime
from sqlalchemy import create_engine
from sqlalchemy import exc
import os
import re
import data_organize as do
from WindPy import w
data = do.get_data('primary_market_llz')
# 证券代码写入
for idx in data.index:
print(idx)
dealcode = data.loc[idx, '交易代码']
if '.SZ' in dealcode and 'Z' not in dealcode[:-3] and 'X' not in dealcode[:-3]:
sec_code = dealcode
df.loc[idx,'证券代码']=sec_code
continue
if 'X' in dealcode:
z_idx = dealcode.index('X')
elif 'Z' in dealcode:
z_idx = dealcode.index('Z')
else:
from matplotlib.backends.backend_pdf import PdfPages
from plotly.offline import download_plotlyjs, init_notebook_mode, plot, iplot
import cufflinks as cf
init_notebook_mode(connected=True)
cf.go_offline()
cf.set_config_file(offline=True, world_readable=True)
import data_organize as do
# plt.rcParams['font.family']=['Kaiti SC']
plt.rcParams['font.family']=['STKaiti']
plt.rcParams['axes.unicode_minus'] = False
# * 一级综收
df = do.get_data('primary_rate_sec')
# df = pd.read_excel('/Users/wdt/Desktop/tpy/Data_Tools/【数据库更新】/专题数据/一级发行数据/利率债一级_新.xlsx',sheet_name = '数据（导入）')
# 这步是用来去除异常值的，之后有啥异常值还可以在这边改。
df = df[df['综收较估值'] < 40]
df = df.loc[df['发行起始日'] >= '2020-01-01']
def GK(maturity=[1,3,5,7,10,20]):
df_gk = df[df['发行人全称']=='国家开发银行'][['发行起始日','发行期限(年)','发行人全称','全场倍数','综收较估值','综收较二级']]
qcbs_quantile_gk = df_gk.groupby('发行期限(年)').apply(lambda df:np.nanquantile(df['全场倍数'],[0.25,0.5,0.75]))
# maturity = [1,3,5,7,10,20]
n = len(maturity)
plt.style.use({'font.size' : 12})
fig,ax = plt.subplots(nrows=n,ncols=1,figsize=(4.15*2,1.42*2*n),dpi = 300)
for i in range(n):
dir_list = upload_date_list('Net_buy_bond')
dir_list.sort()
for dir in dir_list:
if 'xlsx' in dir :
print(dir)# 0522.xlsx
continue
l = [daily_Net_buy_bond(dir)]
for a,b,c in l:
a.to_sql(name=b,con = engine,schema='finance',if_exists = 'append',index=False,dtype=c)
print("成功上传"+dir+"的本地数据")
dir_list = upload_date_list('Repo_price_for_investors')
dir_list.sort()
for dir in dir_list:
if 'xlsx' in dir :
print(dir)# 0522.xlsx
continue
l = [
daily_Repo_price_for_investors(dir),
daily_Repo_amt_prc_for_terms(dir),
daily_Repo_amt_prc_for_collateral(dir),
]
for a,b,c in l:
a.to_sql(name=b,con = engine,schema='finance',if_exists = 'append',index=False,dtype=c)
print("成功上传"+dir+"的本地数据")
#main()
d = do.get_data('Repo_amt_prc_for_terms','2020-06-01','2020-06-30')
d = do.get_data('Repo_amt_prc_for_collateral','2020-06-01','2020-06-30')
