def localize_shibor_data(): shibor_df = ts.shibor_data(2006) for y in range(2007, 2018): df = ts.shibor_data(y) shibor_df = shibor_df.append(df) shibor_daily_change_rate = \ (np.array(shibor_df['ON'])[1:] - np.array(shibor_df['ON'])[:-1]) / np.array(shibor_df['ON'])[:-1] shibor_daily_change_rate = np.append(0.0, shibor_daily_change_rate) shibor_df['daily_change_rate'] = shibor_daily_change_rate shibor_df.to_csv(path_or_buf='data/shibor.csv', header=True, index=False)
def update_shibor_daily(): df = ts.shibor_data() columns = df.columns.tolist() df['datadate'] = [x.strftime('%Y%m%d') for x in df['date']] df['time_stp'] = [dt.datetime.now() for x in df['datadate']] df = df[['datadate'] + columns[1:] + ['time_stp']] date_list = get_sql_dates() update_list = [] for date in df['datadate']: if date not in date_list: update_list.append(date) df = df[df['datadate'].isin(update_list)] df.to_sql('shibor', engine, schema='money_market', if_exists='append') df.to_excel( 'C:\\Users\\client\\Desktop\\python_work\\work\\market_data\\daily_update_shibor.xls' ) send_mail_via_com('hello Will', 'daily_update', '*****@*****.**', select_file='daily_update_shibor.xls') print('Finished, following dates updated: ' + str(update_list)) return
def getShiborDate(self): file_name = str(self.date)+'_shibor'+'.csv' path = self.index + self.index_shibor_data + file_name data = ts.shibor_data(year = self.date) data.sort('date', ascending=False).head(10) data.to_csv(path, encoding='utf-8') print(file_name)
def call_shibor(): ''' 获取银行间同业拆放利率数据,目前只提供2006年以来的数据。 参数说明: year:年份(YYYY),默认为当前年份 返回值说明: date:日期 ON:隔夜拆放利率 1W:1周拆放利率 2W:2周拆放利率 1M:1个月拆放利率 3M:3个月拆放利率 6M:6个月拆放利率 9M:9个月拆放利率 1Y:1年拆放利率 ''' df = ts.shibor_data() #追加数据到现有表 if type(df).__name__ != 'NoneType': df.to_sql('shibor_data', make_engine(), if_exists='append') return df
def get_today_shibor_ON(self): """ 获取今天的银行间拆借利率 隔夜(O/N) """ d = ts.shibor_data() #取当前年份的数据 #print d.sort('date', ascending=False).head(10) return d['ON'][len(d['ON']) - 1]
def get_shibor_rate(year=None): """上海银行间同业拆放利率(Shanghai Interbank Offered Rate,简称Shibor) Args: year: 年份(YYYY),默认为当前年份 """ if not year: year = util.get_year() logger.info('Begin get ShiborRate, year is: %s.' % year) try: data_df = ts.shibor_data(year) except Exception as e: logger.exception('Error get ShiborRate. year is: %s.' % year) return None else: data_dicts = [] if data_df is None or data_df.empty: logger.warn('Empty get ShiborRate. year is: %s.' % year) else: data_df['date'] = data_df['date'].astype(str) data_dicts = [{ 'date': row[0], 'ON': row[1], 'W1': row[2], 'W2': row[3], 'M1': row[4], 'M3': row[5], 'M6': row[6], 'M9': row[7], 'Y1': row[8], 'year': year } for row in data_df.values] logger.info('Success get ShiborRate. year is: %s.' % year) return data_dicts
def __call__(self, conns): self.base = Base() self.financial_data = conns['financial_data'] year = self.base.gettoday()[:4] #Shibor拆放利率 shibor_data = ts.shibor_data(year) print(shibor_data) self.base.batchwri(shibor_data, 'shibor_data', self.financial_data) #银行报价数据 shibor_quote_date = ts.shibor_quote_data(year) self.base.batchwri(shibor_quote_date, 'shibor_quote_data', self.financial_data) #Shibor均值数据 shibor_ma_data = ts.shibor_ma_data(year) self.base.batchwri(shibor_ma_data, 'shibor_ma_data', self.financial_data) #贷款基础利率(LPR) lpr_data = ts.lpr_data(year) self.base.batchwri(lpr_data, 'lpr_data', self.financial_data) #LPR均值数据 lpr_ma_data = ts.lpr_ma_data(year) self.base.batchwri(lpr_ma_data, 'lpr_ma_data', self.financial_data)
def job_8(): try: print("I'm working......银行间同业拆放利率") # Shibor拆放利率 shibor_data = ts.shibor_data() data = pd.DataFrame(shibor_data) data.to_sql('shibor_data',engine,index=True,if_exists='replace') print("银行间同业拆放利率......done") # 银行报价数据 shibor_quote_data = ts.shibor_quote_data() data = pd.DataFrame(shibor_quote_data) data.to_sql('shibor_quote_data',engine,index=True,if_exists='replace') print("银行报价数据......done") # Shibor均值数据 shibor_ma_data = ts.shibor_ma_data() data = pd.DataFrame(shibor_ma_data) data.to_sql('shibor_ma_data',engine,index=True,if_exists='replace') print("Shibor均值数据......done") # 贷款基础利率(LPR) lpr_data = ts.lpr_data() data = pd.DataFrame(lpr_data) data.to_sql('lpr_data',engine,index=True,if_exists='replace') print("贷款基础利率......done") # LPR均值数据 lpr_ma_data = ts.lpr_ma_data() data = pd.DataFrame(lpr_ma_data) data.to_sql('lpr_ma_data',engine,index=True,if_exists='replace') print("LPR均值数据......done") except Exception as e: print(e)
def main(): df=ts.shibor_data() df['date']=df.date.apply(lambda s:s.strftime('%Y%m%d')) engine.connect().execute(f'delete from shiborprices where trade_dt=\'{date}\'') df1=df[df['date']==date] df1.apply(lambda s:convertFormat(s),axis=1)
def override_shibor(cls): start = 2006 shibor = pd.DataFrame() while True: table = ts.shibor_data(start) if table is None or table.empty: break shibor = pd.concat([shibor, table]) start += 1 DMGR.save(shibor, 'macro', 'shibor', if_object2str=True)
def fetch_shibor_data(): df = ts.shibor_data() if df is None: return None df.sort_values('date', ascending=False, inplace=True) df.set_index('date', inplace=True) return df
def add_shibor_page(canvas_para, year_start='2006', year_end=str(datetime.datetime.now().year + 1)): """ 函数功能:增加银行间拆借利率页 :param canvas_para: :return: """ c = canvas_para date_list = pd.date_range(start=year_start, end=year_end, freq='12M') year_list = [str(x)[0:4] for x in date_list] df_shibor_list = [] for year in year_list: shibor_this = ts.shibor_data(year) df_shibor_list.append(shibor_this) df_shibor = pd.concat(df_shibor_list,axis=0).sort_values(by='date', ascending=True) ON = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='ON', timeAxis='datetime') W1 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='1W', timeAxis='datetime') W2 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='2W', timeAxis='datetime') M1 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='1M', timeAxis='datetime') M3 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='3M', timeAxis='datetime') M6 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='6M', timeAxis='datetime') M9 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='9M', timeAxis='datetime') Y1 = extract_point_from_df_date_x(df_origin=df_shibor, date_col='date', y_col='1Y', timeAxis='datetime') shibor_drawing = gen_lp_drawing([tuple(ON)], data_note=['隔夜拆放利率'], time_axis='day', height=letter[1] * 0.1) renderPDF.draw(drawing=shibor_drawing, canvas=c, x=10, y=letter[1] * 0.85) shibor_drawing = gen_lp_drawing([tuple(W1)], data_note=['1周拆放利率'], time_axis='day', height=letter[1] * 0.1) renderPDF.draw(drawing=shibor_drawing, canvas=c, x=10, y=letter[1] * 0.7) shibor_drawing = gen_lp_drawing([tuple(W2)], data_note=['2周拆放利率'], time_axis='day', height=letter[1] * 0.1) renderPDF.draw(drawing=shibor_drawing, canvas=c, x=10, y=letter[1] * 0.55) shibor_drawing = gen_lp_drawing([tuple(M1)], data_note=['1月拆放利率'], time_axis='day', height=letter[1] * 0.1) renderPDF.draw(drawing=shibor_drawing, canvas=c, x=10, y=letter[1] * 0.4) shibor_drawing = gen_lp_drawing([tuple(M3), tuple(M6), tuple(M9), tuple(Y1)], data_note=['3月拆放利率', '6月拆放利率', '9月拆放利率', '1年拆放利率'], time_axis='day', height=letter[1] * 0.25) renderPDF.draw(drawing=shibor_drawing, canvas=c, x=10, y=letter[1] * 0.1) c.showPage() return c
def setShiborData(self, year=None, number=1, isSave=False, tableName=CALLLOANS_SHIBOR_DATA): yearList = getYearList(year, number) for yearItem in yearList: df = ts.shibor_data(yearItem) if isSave is True: df.to_sql(tableName, self.engine_sql, if_exists='append') return df
def shibor(engine, year, sdate=None): tbl = "shibor" tsl.log(tbl + " start...") try: df = ts.shibor_data(year) if sdate is not None: df = df[df.date >= sdate] df.to_sql(tbl, engine, if_exists='append') tsl.log(tbl + " done") except BaseException, e: print e tsl.log(tbl + " error")
def GetShiborData(item = None,year = 2017):# item = 'ON', '1W','2W','1M','6M','9M', '1Y' if item == None: return None df = ts.shibor_data(year) data = {} data['date'] = df['date'] if isinstance(item, list): for i in item: data[i] = df[i] else: data[item] = df[item] return data
def get_shibor_data(year=None): """ Shibor拆放利率 获取银行间同业拆放利率数据,目前只提供2006年以来的数据。 """ df = ts.shibor_data(year) print(df) if df is not None: res = df.to_sql(shibor_data, engine, if_exists='replace') msg = 'ok' if res is None else res print('获取银行间同业拆放利率数据: 年份 {0} {1}'.format(year, msg) + '\n') else: print('获取银行间同业拆放利率数据: 年份 {0} {1}'.format(year, 'None') + '\n')
def get_macro(): Macro={} Macro['Depo']=ts.get_deposit_rate() Macro['Loan']=ts.get_loan_rate() Macro['RRR']=ts.get_rrr() Macro['MoneySupply']=ts.get_money_supply() Macro['MoneyBalance']=ts.get_money_supply_bal() Macro['GDPYOY']=ts.get_gdp_year() Macro['GDPQOQ']=ts.get_gdp_quarter() Macro['GDPFOR']=ts.get_gdp_for() Macro['GDPPULL']=ts.get_gdp_pull() Macro['GDPCON']=ts.get_gdp_contrib() Macro['CPI']=ts.get_cpi() Macro['PPI']=ts.get_ppi() Macro['SHIBO']=ts.shibor_data() return Macro
def get_shibor_data((shibor_type,yearlist)): if shibor_type == 'data': DTS=[] for year in yearlist: preDTS = ts.shibor_data(year) DTS.append(preDTS) DTS=pd.concat(DTS) DTS.to_csv('D:\\ts\\shibor\\shibor_%s.csv'%shibor_type,encoding='gbk') elif shibor_type == 'quote_data': DTS = [] for year in yearlist: preDTS = ts.shibor_quote_data(year) DTS.append(preDTS) DTS = pd.concat(DTS) DTS.to_csv('D:\\ts\\shibor\\shibor_%s.csv' % shibor_type, encoding='gbk') elif shibor_type == 'ma_data': DTS = [] for year in yearlist: preDTS = ts.shibor_ma_data(year) DTS.append(preDTS) DTS = pd.concat(DTS) DTS.to_csv('D:\\ts\\shibor\\shibor_%s.csv' % shibor_type, encoding='gbk') elif shibor_type == 'lpr_data': DTS = [] for year in yearlist: preDTS = ts.lpr_data(year) DTS.append(preDTS) DTS = pd.concat(DTS) DTS.to_csv('D:\\ts\\shibor\\shibor_%s.csv' % shibor_type, encoding='gbk') elif shibor_type == 'lpr_ma_data': DTS = [] for year in yearlist: preDTS = ts.lpr_ma_data(year) DTS.append(preDTS) DTS = pd.concat(DTS) DTS.to_csv('D:\\ts\\shibor\\shibor_%s.csv' % shibor_type, encoding='gbk')
def call_shibor(): ''' 获取银行间同业拆放利率数据,目前只提供2006年以来的数据。 参数说明: year:年份(YYYY),默认为当前年份 返回值说明: date:日期 ON:隔夜拆放利率 1W:1周拆放利率 2W:2周拆放利率 1M:1个月拆放利率 3M:3个月拆放利率 6M:6个月拆放利率 9M:9个月拆放利率 1Y:1年拆放利率 ''' df = ts.shibor_data() return df
# -*- coding: utf-8 -*- import tushare as ts import pandas as pd df = ts.shibor_data() df = df.set_index('date') df_recent = df[df.index > '2017-09-01'] df_recent['ON'].plot()
"pool": pool, "amount": amount, "security": security, "addup": addup }, index=[data.index]) #结果汇总 ########################################################## #####################策略回测结果报告###################### annual_return = (result.ix[-1, 'addup'] / result.ix[0, 'addup'] - 1) / len(result) * 252 daily_return = ((-result.ix[:, 'addup'] + result.ix[:, 'addup'].shift(-1)) / result.ix[:, 'addup']).shift(1) valatility = daily_return.var() risk_free = ts.shibor_data(2015).ix[:, '1Y'].mean() / 100 #shibor一年平均值作无风险利率 sharp_ratio = (annual_return - risk_free) / valatility drawdown = (result['addup'] / pd.expanding_max(result['addup']) - 1).sort_values() max_drawdown = pd.DataFrame(drawdown.ix[0, 0], index=[drawdown.index[0]], columns=['max_drawdown']) #最大回撤 dr = ((-data.ix[:, 'close'] + data.ix[:, 'close'].shift(-1)) / data.ix[:, 'close']).shift(1) tr_r = (1 + daily_return.fillna(0)).cumprod() #策略累计收益 au_r = (1 + dr.fillna(0)).cumprod() #股票累计收益 ###绘图 au_graph = plt.plot(au_r) tr_graph = plt.plot(tr_r) ###说明
factor0.index = pandas.to_datetime(factor0.index) - pandas.to_timedelta(1) pmi = factor0["PMI"].dropna() tong = factor1.resample("m").mean() ## 四个指标 bond = (bond.resample("m").mean()).ix[:,0] lanzi= lanzi.resample("m").mean() pmi = pmi.resample("m").mean() tong_cr = (tong.diff(1)/(tong.shift(1))).ix[:,0] ###导入shibor 数据 import tushare as ts df = ts.shibor_data(2017) for year in range(2006,2017): df = df.append( ts.shibor_data(year)) df.index = df.date.apply(pandas.to_datetime) df= df.sort_index() df.columns shibor = df def reshape(mk,base,freq,shift_n=1,plot=True): """ :param mk: 基于的市场 或组合 :param base: 信号基于的指标 :param freq: 频率, 不低于 mk和base里面频率较低的 m:mouth d:day y:year q:quarter
#print(ts.get_money_supply()) #print(ts.get_loan_rate()) #print(ts.get_today_all()) sh000001 = ts.get_k_data("000001",index=True) print(sh000001.head().as_matrix()) print(sh000001[:20]) print (sh000001.index.size) soho = sh000001.iloc[:4,1:6] print(soho) print(soho.as_matrix()) df = ts.shibor_data() #取当前年份的数据 #df = ts.shibor_data(2014) #取2014年的数据 #df.sort('date', ascending=False).head(10) #df = ts.profit_data(top='all') #df.sort('shares',ascending=False) #print(df[:10]) #all = ts.get_today_all(); #print(all[:20]) #print(sh000001.index.size) #print(sh000001.columns.size) df = ts.get_tick_data('600848',date='2014-12-22',src='sn') print(df.head()) #collection_sh.insert(json.loads(df.to_json(orient='records'))) ''' date = sh000001["date"] openv = sh000001["open"]
import pymongo import json import pandas as pd # ts.get_cash_flow : get specialed Code Cash_flow in the History . # 600547:山东黄金 # 600362:江西铜业 # 600312:平高电气 # 600499:科达洁能 # 603993:洛阳钼业 db = "Shibor" coll = "ShiborData" year = 2012 conn = pymongo.MongoClient('127.0.0.1', port=27017) df = ts.shibor_data(year) # index data columns(X columns) dicIndex = json.loads(df.to_json(orient='split')) for i, ind in enumerate(dicIndex['index']): # Note: pandas has some transformation between date,timestampe and string. d = pd.to_datetime(dicIndex['data'][i][0], unit='ms').strftime('%Y-%m-%d') jsonstr = { '_id': d, 'year': year, dicIndex['columns'][0]: d, dicIndex['columns'][1]: dicIndex['data'][i][1], dicIndex['columns'][2]: dicIndex['data'][i][2], dicIndex['columns'][3]: dicIndex['data'][i][3], dicIndex['columns'][4]: dicIndex['data'][i][4], dicIndex['columns'][5]: dicIndex['data'][i][5], dicIndex['columns'][6]: dicIndex['data'][i][6],
# -*- coding: utf-8 -*- import tushare as ts ''' 获取银行间同业拆放利率数据,目前只提供2006年以来的数据。 参数说明: year:年份(YYYY),默认为当前年份 返回值说明: date:日期 ON:隔夜拆放利率 1W:1周拆放利率 2W:2周拆放利率 1M:1个月拆放利率 3M:3个月拆放利率 6M:6个月拆放利率 9M:9个月拆放利率 1Y:1年拆放利率 ''' df = ts.shibor_data(2018) #直接保存 #df = ts.shibor_data(2014) #取2014年的数据 #df.sort('date', ascending=False).head(10) print(df)
#三大需求对GDP贡献 gdp_for = ts.get_gdp_for() #三大产业对GDP拉动 gdp_pull = ts.get_gdp_pull() #三大产业贡献率 gdp_contrib = ts.get_gdp_contrib() cpi = ts.get_cpi() ppi = ts.get_ppi() df = ts.shibor_data() #取当前年份的数据 #df = ts.shibor_data(2014) #取2014年的数据 df.sort('date', ascending=False).head(10) df = ts.shibor_quote_data() #取当前年份的数据 #df = ts.shibor_quote_data(2014) #取2014年的数据 df.sort('date', ascending=False).head(10) #shibo均值 df = ts.shibor_ma_data() #取当前年份的数据 #df = ts.shibor_ma_data(2014) #取2014年的数据 df.sort('date', ascending=False).head(10) #贷款基础利率 lpr = ts.lpr_data() #取当前年份的数据 #df = ts.lpr_data(2014) #取2014年的数据
import tushare as ts import pandas as pd shibor2014 = ts.shibor_data(2014) #取当前年份的数据 shibor2015 = ts.shibor_data(2015) shibor2016 = ts.shibor_data(2016) shibor2017 = ts.shibor_data(2017) shibor = pd.concat([ shibor2014[['date', 'ON']], shibor2015[['date', 'ON']], shibor2016[['date', 'ON']], shibor2017[['date', 'ON']] ])
def shibor(year=""): jsonFile = os.path.join(config.listsRootPath, "shibor.json") data = ts.shibor_data() # 默认取当前年份的数据 if str(type(data)) =="<class 'pandas.core.frame.DataFrame'>": data.sort('date', ascending=False) writeFile(jsonFile, data, 'records', False)
def fetchByYear(self, mongo, func): years = range(self.begin_year, self.end_year_notinclude) for year in years: print(year) print(type(year)) # year = int(year) #print (type(year)) if (func == 'shibor_data'): if (year < 2006): print("[Wrong]没有" + str(year) + "年数据") continue else: print("有" + str(year) + "年数据") #df = ts.shibor_data() # 取当前年份的数据 print("shibor") print(type(ts.shibor_data(year))) df = ts.shibor_data(year) #取2014年的数据 #df.sort('date', ascending=False) elif (func == 'shibor_quote_data'): if (year < 2006): print("[Wrong]没有" + str(year) + "年数据") continue else: print("有" + str(year) + "年数据") #df = ts.shibor_quote_data() # 取当前年份的数据 df = ts.shibor_quote_data(year) #取2014年的数据 #df.sort('date', ascending=False) elif (func == 'shibor_ma_data'): if (year < 2006): print("[Wrong]没有" + str(year) + "年数据") continue else: print("有" + str(year) + "年数据") #df = ts.shibor_ma_data() # 取当前年份的数据 df = ts.shibor_ma_data(year) #取2014年的数据 print("4444444444444") print(df) #df.sort('date', ascending=False) elif (func == 'lpr_data'): if (year < 2013): print("[Wrong]没有" + str(year) + "年数据") continue else: print("有" + str(year) + "年数据") #df = ts.lpr_data() # 取当前年份的数据 df = ts.lpr_data(year) #取2014年的数据 #df.sort('date', ascending=False) # df = fd.lpr_data(self.year) elif (func == 'lpr_ma_data'): if (year < 2013): print("[Wrong]没有" + str(year) + "年数据") continue else: print("有" + str(year) + "年数据") # df = ts.lpr_ma_data() # 取当前年份的数据 df = ts.lpr_ma_data(year) #取2014年的数据 #df.sort('date', ascending=False) else: print("error" + func) df = {"err": True} year = str(year) print("###################") print(df) print(func) tmpJson = json.loads(df.to_json(orient='records')) # tmpJson = json.dumps(df) print("_________") print(tmpJson) print(type(tmpJson)) import time for i in range(len(tmpJson)): tmpJson[i][u'year'] = int(year) d = time.localtime(tmpJson[i][u'date'] / 1000) tmpJson[i][u'date'] = time.strftime('%Y-%m-%d', d) coll = mongo.shibor[func] coll2 = mongo.shibor[func + '_' + str(year)] coll2.insert(tmpJson) coll.insert(tmpJson)
# -*- coding: utf-8 -*- """ Created on Mon Feb 6 13:47:45 2017 @author: AB053658 """ import tushare as ts df = ts.realtime_boxoffice() print(df) ds = ts.day_boxoffice() print(ds) dv = ts.shibor_data(2013) print(dv.sort_values('ON', ascending=False).head(10)) du = ts.get_realtime_quotes('000581') #Single stock symbol du[['code', 'name', 'price', 'bid', 'ask', 'volume', 'amount', 'time']] print(du) dw = ts.get_index() print(dw)