def main():
"""
db_path = "/Users/wdt/Desktop/tpy/db.txt"
l = [daily_fig_SRDI(),
daily_fig_liquidity_premium(),
daily_fig_bond_leverage(),
daily_fig_rates(),
daily_fig_credit_premium()
]
# DONE 宏观数据上传数据库
l = [fig_industrial_production(),fig_cpi_ppi_related(),
fig_upstream(),fig_midstream(),fig_downstream(),
]
"""
l = [fig_downstream()]
conn, engine = do.get_db_conn()
l = [hs300()]
for a, b, c in l:
# for i in range(len(a)):
# try:
a.to_sql(name=b,
con=engine,
schema='finance',
if_exists='replace',
index=False,
dtype=c)
print(b, '写入完成')
def main():
w.start()
# @ 读取db.txt内的邮箱信息
conn, engine = do.get_db_conn()
'''
l = [
daily_fig_bond_leverage(),
daily_fig_credit_premium(),
daily_fig_liquidity_premium(),
daily_fig_rates(),industial_premium()
]
# 宏观周报数据
l2 = [
fig_industrial_production(),
fig_cpi_ppi_related(),
fig_upstream(),fig_downstream(),fig_midstream()
]
'''
# 流动性
l = [cash_cost(),policy_rate(),monetary_policy_tools(),\
repo_volume(),interbank_deposit(),rates()]
l = [monetary_policy_tools()]
for a, b, c in l:
if len(np.array(a)) == 0:
print(b, '已是最新，无需更新')
continue
a.to_sql(name=b,
con=engine,
schema='finance',
if_exists='append',
index=False,
dtype=c)
print('成功更新表', b)
def main():
w.start()
# @ 读取db.txt内的邮箱信息
conn, engine = do.get_db_conn()
l = [
daily_fig_bond_leverage(),
daily_fig_credit_premium(),
daily_fig_liquidity_premium(),
daily_fig_rates(),industial_premium(),
cash_cost(),policy_rate(),monetary_policy_tools(),\
repo_volume(),interbank_deposit(),rates(),
cash_amt_prc(),spreads()]
for a, b, c in l:
if len(np.array(a)) == 0:
print(b, '已是最新，无需更新')
continue
a.to_sql(name=b,
con=engine,
schema='finance',
if_exists='append',
index=False,
dtype=c)
print('成功更新表', b, '至', do.get_latest_date(b))
def main():
input_path = '/Users/wdt/Desktop/tpy/raw_data_pool'
# * 读取db.txt内的数据库信息
conn,engine = do.get_db_conn()
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+"的本地数据")
class Inter_Graph:
#定义基本属性
conn, engine = do.get_db_conn()
df = pd.read_sql('select * from Net_buy_bond', conn)
bond_type_OL = df.columns[3:-1].tolist()
bond_duration_OL = df["期限"].unique().tolist()
bond_buyer_OL = df["机构名称"].unique().tolist()[:-1]
dff = pd.read_sql('select * from Repo_amt_prc_for_terms', conn)
repo_terms_OL = dff["期限品种"].unique().tolist()
repo_loaner_OL = dff["机构类型"].unique().tolist()
def get_ir_diff():
conn, engine = do.get_db_conn()
# 城投债数据
data = pd.read_sql('select * from Credit_Premium', conn)
# 基准利率数据
sql_cmd = 'SELECT * FROM interest_rate_day ORDER BY interest_rate_day.index desc LIMIT 1'
ir = pd.read_sql(sql_cmd, conn)
conn.close()
# 城投债数据筛选非PPN
data = data[data["证券简称"].str.contains("PPN") == False]
columns_to_use = [
'证券代码', '证券简称', '主体名称', '是否城投债', '上市日期', '债券余额\n[日期] 最新\n[单位] 亿',
'剩余期限(天)\n[日期] 最新\n[单位] 天', '估价收益率(%)(中债)\n[日期] 最新收盘日\n[估值类型] 推荐',
'含权债行权期限', '债券估值(YY)\n[单位] %', '是否次级债', '区域', '城市', '是否存在担保'
]
df = data[columns_to_use]
# 确定城投债的可比期限
df.loc[:, "含权债行权期限"] = df.loc[:, "含权债行权期限"].fillna(10) * 365
df.loc[:,
"期限"] = ((df[["含权债行权期限", "剩余期限(天)\n[日期] 最新\n[单位] 天"]].min(axis=1)) /
365).round(2)
df.loc[:, "期限_匹配"] = (
(df[["含权债行权期限", "剩余期限(天)\n[日期] 最新\n[单位] 天"]].min(axis=1)) /
365).round(0)
# 选择国开利率作为基准利率
GK_yield_base = ir[[
'中债国开债到期收益率:1年', '中债国开债到期收益率:2年', '中债国开债到期收益率:3年', '中债国开债到期收益率:4年',
'中债国开债到期收益率:5年'
]].T
GK_yield_base.columns = ["GK_yield"]
GK_yield_base["期限"] = [1, 2, 3, 4, 5]
GK_yield_base = GK_yield_base.reset_index(drop=True)
# 合并城投债数据和基准利率
dff_VS_GK = pd.merge(df[df["期限"] < 5],
GK_yield_base,
left_on=["期限_匹配"],
right_on=["期限"],
how="left")
# 计算利差
dff_VS_GK["券种利差"] = (dff_VS_GK["债券估值(YY)\n[单位] %"] -
dff_VS_GK["GK_yield"]) * 100
dff_VS_GK = dff_VS_GK[dff_VS_GK["券种利差"].isna() == False]
return dff_VS_GK
def main():
conn, engine = do.get_db_conn()
w.start()
# 宏观周报数据
l = [
fig_industrial_production(),
fig_cpi_ppi_related(),
fig_upstream(),
fig_downstream(),
fig_midstream()
]
for a, b, c in l:
if len(np.array(a)) == 0:
print(b, '已是最新，无需更新')
continue
a.to_sql(name=b,
con=engine,
schema='finance',
if_exists='append',
index=False,
dtype=c)
print('成功更新表', b, '至', do.get_latest_date(b))
def main():
# * Step1:获取数据
d = pd.DataFrame([])
for dir in os.listdir('./tmp_data'):
if ('~$' in dir) | ('成交统计' not in dir):
continue
print(dir)
x = int(re.findall(r'\d+', dir)[0])
y = int(re.findall(r'\d+', dir)[1])
z = int(re.findall(r'\d+', dir)[2])
date = dt.datetime(int(x), int(y), int(z))
dirr = pd.read_excel('./tmp_data'+'/'+dir,\
sheet_name='信用债成交',header=1)
dirr['时间'] = date
dirr['估值时间'] = dirr['时间'] - dt.timedelta(days=1)
d = d.append(dirr[['方向', '代码', '价格', '时间', '估值时间']])
df = d.reset_index(drop=True)
for idx in df.index:
# q
if type(df.loc[idx, '价格']) == str:
print(idx)
df.drop(idx, axis=0, inplace=True)
# * Step2:添加windapi指标
w.start()
for idx in df.index:
# if idx < 2327:
# continue
print(idx)
code = df.loc[idx, '代码']
net_price = df.loc[idx, '价格']
net_date = df.loc[idx, '时间'].strftime("%Y%m%d")
last_date = df.loc[idx, '估值时间'].strftime("%Y%m%d")
# 修正非交易日
# 方法：寻找一个银行间债券标的作为基准（xxxx国债），然后检查在当天的市场最近交易日是否与其相符
df.loc[idx ,'估值时间'] = w.wss('010001.IB', \
"last_trade_day","tradeDate={}".format(last_date)).Data[0][0]
last_date = df.loc[idx, '估值时间'].strftime("%Y%m%d")
# 基本信息
df.loc[idx,['名字','type','rating','债券期限','城投','发行人','发行方式']] = w.wsd(code,\
"sec_name,windl1type,latestissurercreditrating,\
term2,municipalbond,issuer,issue_issuemethod" ,\
last_date,last_date,'TradingCalendar=NIB',usedf=True)[1].values[0]
# 【到期估值】
df.loc[idx, '到期估值'] = w.wsd(
code, "yield_cnbd", last_date, last_date,
"credibility=4;PriceAdj=YTM;TradingCalendar=NIB").Data[0][0]
if df.loc[idx, '到期估值'] == None:
df.loc[idx, '到期估值'] = 0
try:
if np.isnan(df.loc[idx, '到期估值']):
df.loc[idx, '到期估值'] = 0
except:
pass
# 【行权估值】
df.loc[idx, '行权估值'] = w.wsd(
code, "yield_cnbd", last_date, last_date,
"credibility=3;PriceAdj=YTM;TradingCalendar=NIB").Data[0][0]
if df.loc[idx, '行权估值'] == None:
df.loc[idx, '行权估值'] = 0
try:
if np.isnan(df.loc[idx, '行权估值']):
df.loc[idx, '行权估值'] = 0
except:
pass
# 【部分债无行权日】
df.loc[idx, '行权日'] = w.wsd(code, "nxoptiondate", net_date, net_date,
"type=All").Data[0][0]
if df.loc[idx, '行权日'] == None:
df.loc[idx, '行权日'] = 0
# 【】
df.loc[idx,
'行业'] = w.wsd(code, "industry_csrc12_n", last_date, last_date,
"industryType=3;TradingCalendar=NIB").Data[0][0]
df.loc[idx, '企业性质'] = w.wsd(code, "nature1", last_date, last_date,
";TradingCalendar=NIB").Data[0][0]
df.loc[idx, '隐含评级'] = w.wsd(code, "rate_latestMIR_cnbd", net_date,
net_date, "").Data[0][0]
# 【price】
if df.loc[idx, '价格'] < 20:
price = df.loc[idx, '价格']
else:
x = w.wss(code, "calc_yield","","balanceDate={};bondPrice={};\
bondPriceType=1;maturityDate={}" .\
format(int(net_date),net_price,\
str2int(df.loc[idx,'行权日'])) ).Data[0][0]
if not x:
print('price超过20的且无值：', idx) #
continue
if x < 0.3:
price = w.wss(code, "calc_yield","","balanceDate={};bondPrice={};\
bondPriceType=1" .\
format(int(net_date),net_price)).Data[0][0]
# test
print(code, net_date, 'bc1小于0.3用了不含行权日的到期收益率')
price = x
df.loc[idx, 'price'] = price
# 【估值偏离】
## 用当前成交价price和到期/行权估值的最小距离
if df.loc[idx, '到期估值'] == 0:
x = (w.wsd(code, "couponrate2", last_date).Data[0][0] -
df.loc[idx, 'price']) * 100
else:
if abs(df.loc[idx, 'price'] -
df.loc[idx, '到期估值']) > abs(df.loc[idx, 'price'] -
df.loc[idx, '行权估值']):
x = (df.loc[idx, 'price'] - df.loc[idx, '行权估值']) * 100
else:
x = (df.loc[idx, 'price'] - df.loc[idx, '到期估值']) * 100
df.loc[idx, '估值偏离'] = x
# 【剩余期限】
if abs(df.loc[idx, 'price'] -
df.loc[idx, '到期估值']) > abs(df.loc[idx, 'price'] -
df.loc[idx, '行权估值']):
x = w.wsd(code, "termifexercise", net_date, net_date,
"").Data[0][0]
else:
x = w.wsd(code, "ptmyear", net_date, net_date, "").Data[0][0]
df.loc[idx, '剩余期限'] = x
# * Step3:生成统计数据
df = df[['方向','代码','价格','时间','估值时间',\
'名字','到期估值','行权估值','行权日','price','type','估值偏离',\
'剩余期限','rating','债券期限','城投','行业',\
'发行人','企业性质','隐含评级','发行方式']]
df = organize(df)
stat_table = get_stat_table(df)
# * Step4:原始数据与统计数据写进数据库
conn, engine = do.get_db_conn()
l = [upload2(stat_table), upload1(df)]
l = [upload2(stat_table)]
# l = [upload1(df)]
for a, b, c in l:
a.to_sql(name=b,
con=engine,
schema='finance',
if_exists='append',
index=False,
dtype=c)
'城投_AAA_1y','城投_AAA_3y','城投_AAA_5y','城投_AAA_7y',\
'城投_AA+_1y','城投_AA+_3y','城投_AA+_5y','城投_AA+_7y',\
'城投_AA_1y','城投_AA_3y','城投_AA_5y','城投_AA_7y',\
'中票_AAA_1y','中票_AAA_3y','中票_AAA_5y',\
'中票_AA+_1y','中票_AA+_3y','中票_AA+_5y',\
'中票_AA_1y','中票_AA_3y','中票_AA_5y',\
'中票_AA-_1y','中票_AA-_3y','中票_AA-_5y']
df['date'] = df.index
name = 'rates1'
columns_type=[Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),Float(),
Float(),Float(),Float(),Float(),
DateTime()]
dtypelist = dict(zip(df.columns,columns_type))
return df, name , dtypelist
conn,engine = do.get_db_conn()
for a,b,c in [rates()]:
a.to_sql(name=b,con = engine,schema='finance',if_exists = 'replace',index=False,dtype=c)
print(b, '写入完成')