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)
