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, '写入完成')