def rates_us(): name = 'rates_us' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("G0000886,G0000887,G0000891,G8455661,M0000185,G0000898", "2010-06-21", "2021-06-18", usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['美债1年', '美债2年', '美债10年', '美债10-2', '美元兑人民币', 'libor_3m'] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < today_date.date())] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
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 cash_amt_prc(): # 资金现券与成交量 name = 'cash_amt_prc' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb( "M0041652,M0041653,M0041655,M1004511,M1004515,M0220162,M0220163,M0330244,M0041739,M0041740", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['R001','R007','R021','GC001','GC007','DR001','DR007',\ '成交量:R001','成交量:银行间质押式回购','成交量:银行间债券现券'] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < today_date.date())] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def daily_fig_rates(): name = 'fig_rates' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb( 'S0059744,S0059746,S0059747,S0059749,M1004263,M1004265,M1004267,M1004271', last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ "1年国债", "3年国债", "5年国债", "10年国债", "1年国开", "3年国开", "5年国开", "10年国开" ] df = df.dropna(axis=0) df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def pmy_rate_sec(): name = 'primary_rate_sec' last_date = do.get_latest_date(name) df = pd.read_excel('/Users/wdt/Desktop/tpy/Data_Tools/【数据库更新】/专题数据/一级发行数据/利率债一级.xlsx',\ sheet_name = '数据(导入)') df = df[cols] df['date'] = df['发行起始日'] df = df.loc[df.date > last_date] columns_type = [ VARCHAR(30), VARCHAR(30), VARCHAR(30), DateTime(), DateTime(), Float(), Float(), VARCHAR(30), VARCHAR(30), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def policy_rate(): name = 'policy_rate' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb('M0041371,M0041373,M0041377,M0329656,\ M0329543,M0329544,M0329545', last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['逆回购利率:7天', '逆回购利率:14天', '逆回购利率:28天',\ '逆回购利率:63天', 'MLF:3m', 'MLF:6m', 'MLF:1y'] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def fig_industrial_production(): name = 'fig_industrial_production' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("S5704502,S5715680,S5708175,S5715660,S5417017,S5914175", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ "日均产量:粗钢:国内", "日均产量:焦炭:重点企业(旬)", "高炉开工率(163家):全国", "产能利用率:电炉:全国", "PTA产业链负荷率:PTA工厂", "浮法玻璃:产能利用率" ] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < dt.datetime.now().date())] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def fig_midstream(): name = 'fig_midstream' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err,df = w.edb("S5705039,S0247603,S0181750,S5914515,S5907373,S5416650,M0067419,M0066359,\ M0066348,M0066350" , \ last_date, today_date, usedf = True) if df.shape[1] == 1: return [], name, [] df.columns = [ 'Mylpic综合钢价指数', '库存:主要钢材品种:合计', '库存:螺纹钢(含上海全部仓库)', '水泥价格指数:全国', '中国玻璃价格指数', '中国盛泽化纤价格指数', '期货收盘价(活跃合约):PVC', '期货收盘价(活跃合约):天然橡胶', '期货收盘价(活跃合约):黄大豆1号', '期货收盘价(活跃合约):黄玉米' ] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < today_date.date())] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def spreads(): # 息差与杠杆 name = 'spreads' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err,df = w.edb("M0220162,M0220163,M1004515,M0048486,M0048490,M1004007,M1004900,S0059722,S0059724,S0059725,M1004271,M1004300", \ last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['DR001','DR007','GC007','IRS_1y_FR007','IRS_5y_FR007',\ 'IRS_5y_shibor3m','cd_AAA_6m',\ '中短票_AA+_1y','中短票_AA+_3y','中短票_AA+_5y',\ '国开10年','地方债_AAA_3y'] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < today_date.date())] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def daily_fig_credit_premium(): name = 'fig_credit_premium' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("M0048432,M0048434,M0048435, \ M0048422,M0048424,M0048425, \ M0048412,M0048414,M0048415, \ M1004265,S0059746, \ M1010704,M1010706,M1010708, \ M1015080,S0059738", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ "中债城投债到期收益率(AAA):1年", "中债城投债到期收益率(AAA):3年", "中债城投债到期收益率(AAA):5年", "中债城投债到期收益率(AA+):1年", "中债城投债到期收益率(AA+):3年", "中债城投债到期收益率(AA+):5年", "中债城投债到期收益率(AA):1年", "中债城投债到期收益率(AA):3年", "中债城投债到期收益率(AA):5年", "中债国开债到期收益率:3年", "中债国债到期收益率:3年", "中债商业银行二级资本债到期收益率(AAA-):1年", "中债商业银行二级资本债到期收益率(AAA-):3年", "中债商业银行二级资本债到期收益率(AAA-):5年", "中债可续期产业债到期收益率(AAA):3年", "中债中短期票据到期收益率(AAA):3年" ] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [ 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
def download_certain_mail(msg_list,download_io=' ',lastday=' '): try: subject_list=[] date_list=[] for msg in msg_list: # 获取可读的附件名 subject=decode_str(msg.get("subject")) # print(subject)# 附件名 if '现券市场交易情况总结日报' in subject: lastday = do.get_latest_date('Net_buy_bond') download_io="/Users/wdt/Desktop/tpy/raw_data_pool/现券市场交易情况总结/日报/" # 查找邮件名中的八位数字 date= re.search(r"\d{8}",subject)[0] if date > lastday.strftime("%Y%m%d") : subject_list.append(subject) date_list.append(date) get_att(msg,download_io) print("已经下载附件:"+subject) else: pass elif '质押式回购市场交易情况总结日报' in subject: lastday=do.get_latest_date('Repo_amt_prc_for_collateral') download_io = '/Users/wdt/Desktop/tpy/raw_data_pool/质押式回购市场交易情况总结/' date= re.search(r"\d{8}",subject)[0] if date > lastday.strftime("%Y%m%d") : subject_list.append(subject) date_list.append(date) get_att(msg,download_io) print("已经下载附件:"+subject) else: pass print("##########") except BaseException as e: print('fail error:',e)
def interbank_deposit(): name = 'interbank_deposit' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb('M1006645,M0329545', last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['存单_股份行_1y', 'MLF:1y'] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [Float(), Float(), DateTime()] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def monetary_policy_tools(): name = 'monetary_policy_tools' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb('M0061614,M0061615,M0061616,\ M0329540,M0329541,M0329542,M5596597,\ M0041372,M0041374,M0041378,M0329655,\ M0062600,M0060446,M0096197,\ M0134555,M0150207', last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['OMO:净投放', 'OMO:投放', 'OMO:回笼',\ 'MLF_数量_3m','MLF_数量_6m','MLF_数量_1y','MLF_到期',\ '逆回购_数量_7d','逆回购_数量_14d','逆回购_数量_28d','逆回购_数量_63d',\ '逆回购_到期','国库现金:中标量','国库现金:到期量',\ 'SLO_投放','SLO_回笼'] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date <= dt.datetime.now().date())] columns_type = [ 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
def cash_cost(): name = 'cash_cost' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb('M1006336,M1006337,M1004515,M0017142', last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['DR001', 'DR007', 'GC007', 'shibor_3m'] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [Float(2), Float(2), Float(2), Float(2), DateTime()] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def fig_downstream(): name = 'fig_downstream' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("S2707379,S2707380,S2726996,S6126413, \ S0049599,S0000293,S6500614,S6424740, \ S6604459,S6604460,S0000066,S0237842,S0031550", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ '30大中城市:商品房成交套数', '30大中城市:商品房成交面积', '100大中城市:成交土地溢价率:当周值', '当周日均销量:乘用车:厂家零售', '柯桥纺织:价格指数:总类', '义乌中国小商品指数:总价格指数', '中关村电子价格产品指数', '中国公路物流运价指数', '电影票房收入', '电影观影人次', 'CCFI:综合指数', 'CICFI:综合指数', '波罗的海干散货指数(BDI)' ] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < today_date.date())] columns_type = [ 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
def daily_fig_bond_leverage(): name = 'fig_bond_leverage' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb('M0041739,M5639029', last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['成交量:银行间质押式回购', '债券市场托管余额'] # df = df.dropna(axis = 0) df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [Float(4), Float(1), DateTime()] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def fig_cpi_ppi_related(): name = 'fig_cpi_ppi_related' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("S0000241,S0000242,S0000240,S0000236, \ S5065111,S5065112,S0143884,S5042881,S0105896,S0031505,\ S0248945,M6424471", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ "食用农产品价格指数:蛋类:周环比", "食用农产品价格指数:蔬菜类:周环比", "食用农产品价格指数:禽类:周环比", "食用农产品价格指数", '平均批发价:28种重点监测蔬菜', '平均批发价:7种重点监测水果', '平均价:猪肉:全国', '中国大宗商品价格指数:总指数', '南华综合指数', 'CRB现货指数:综合', '农产品批发价格200指数', 'iCPI:总指数:日环比' ] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < dt.datetime.now().date())] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def fig_upstream(): name = 'fig_upstream' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("S5104570,S5125686,S5111905,S5111903,\ S5705040,S5705131,S0031648,S0031645,\ M0066355,M0066356,S0049493,S0049494,S0200868", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ '综合平均价格指数:环渤海动力煤', '炼焦煤库存:六港口合计', '现货价:原油:英国布伦特Dtd', '现货价:原油:美国西德克萨斯中级轻质原油(WTI)', 'Mylpic矿价指数:综合', '国内铁矿石港口库存量', '伦敦现货白银:以美元计价', '伦敦现货黄金:以美元计价', '期货收盘价(活跃合约):阴极铜', '期货收盘价(活跃合约):铝', '库存期货:阴极铜', '库存期货:铝', '南华焦炭指数' ] df['date'] = df.index df = df.loc[(df.date > last_date) & (df.date < today_date.date())] columns_type = [ 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
def industial_premium(): name = 'fig_industries_premium' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df = w.edb("M1008950,M1008953,M1008973,M1008971,M1008964", last_date, today_date, usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ["信用利差_地产","信用利差_钢铁","信用利差_煤炭",\ "信用利差_有色","信用利差_汽车"] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [Float(), Float(), Float(), Float(), Float(), DateTime()] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def main(): # 附件下载路径 download_io = "/Users/wdt/Desktop/tpy/raw_data_pool/现券市场交易情况总结/日报/" # @ 读取mail_config.txt内的邮箱信息 io = os.getcwd() + "/mail_config.txt" with open(io, 'r') as f1: config = f1.readlines() for i in range(0, len(config)): config[i] = config[i].rstrip('\n') host = config[0] # pop.163.com username = config[1] # 用户名 password = config[2] # 密码 # 获取数据库内最后更新时间 last_date = do.get_latest_date('Net_buy_bond') #获取邮件池子 msg_list = get_mail_pool(host, username, password) #下载邮件 download_certain_mail(msg_list, download_io, last_date)
def repo_volume(): name = 'repo_volume' last_date = do.get_latest_date(name) today_date = dt.datetime.now().date() print('表{}的最近更新日期为{}'.format(name, last_date)) err, df=w.edb('M1004529,M0330244,M0330245,M0330246,\ M0330247,M0330248,M0330249,M0330250,\ M0330251,M0330252,M0330253,M0330254,\ M0041739' ,\ last_date,today_date.strftime("%Y-%m-%d"), "Fill=Previous",usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = ['加权利率:R001','成交量:R001','成交量:R007','成交量:R014', '成交量:R021','成交量:R1M', '成交量:R2M','成交量:R3M', '成交量:R4M', '成交量:R6M', '成交量:R9M','成交量:R1Y',\ '成交量:银行间质押式回购'] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [ 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
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 upload_date_list(name): ''' 输出一个待更新日期的列表 最后依照此列表逐个上传 ''' dir_names=[] latest_date = do.get_latest_date(name) if name == 'Net_buy_bond': path = input_path+"/现券市场交易情况总结/日报" elif 'Repo' in name : path = input_path+'/质押式回购市场交易情况总结/日报' for dir in os.listdir(path ): if 'DS' in dir or 'xlsx' in dir : continue # print(dir) attach_time = re.match(".*\\d{8}\\.",dir).group()[-9:-1] attach_datetime = pd.to_datetime(attach_time) if attach_datetime > latest_date: dir_names.append(dir) else: pass return dir_names
def daily_fig_liquidity_premium(): name = 'fig_liquidity_premium' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err,df=w.edb('M0017139,M0041653,M0220163,\ M0017142,M0048486,M1010889,M1010892,M0329545,\ M1011048' , \ last_date,today_date,usedf=True) if df.shape[1] == 1: return [], name, [] df.columns = [ "shibor_7d", "质押回购利率_7天", "存款类质押回购利率_7天", "shibor_3m", "IRS:FR007:1y", "存单_AAA_3m", "存单_AAA_1y", "MLF:1年", "国股银票转贴现收益率_3m" ] df['date'] = df.index df = df.loc[df.date > last_date] columns_type = [ Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), Float(), DateTime() ] dtypelist = dict(zip(df.columns, columns_type)) return df, name, dtypelist
def rates(): name = 'rates' last_date = do.get_latest_date(name) today_date = dt.datetime.now() print('表{}的最近更新日期为{}'.format(name, last_date)) err,df = w.edb("S0059744,S0059746,S0059747,S0059748,S0059749,\ M1004298,M1004300,M1004302,M1004304,M1004306,\ M1004263,M1004265,M1004267,M1004269,M1004271,\ M0048432,M0048434,M0048435,M0048436,\ M0048422,M0048424,M0048425,M0048426,\ M0048412,M0048414,M0048415,M0048416,\ M1002654,M1002656,M1002658,\ M1003631,M1003633,M1003635,\ M1003639,M1003641,M1003643,\ M1003623,M1003625,M1003627" ,\ last_date,today_date,usedf = True) if df.shape[1] == 1: return [], name, [] df.columns=['国债1年','国债3年','国债5年','国债7年','国债10年',\ '地方1年','地方3年','地方5年','地方7年','地方10年',\ '国开1年','国开3年','国开5年','国开7年','国开10年',\ '城投_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 df = df.loc[df.date > last_date] 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