def update_fund_list(df, fname):
df[['sec_name', 'wind_code']].to_excel(fname, index=False)
df = pd.read_excel(fname)
# 基金二级分类
data = w.wss(df['wind_code'].tolist(), "fund_investtype")
temp = utils.wind2df(data)
df['investtype'] = temp['fund_investtype']
# 发行时间
data = w.wss(df['wind_code'].tolist(), "issue_date")
temp = utils.wind2df(data)
df['issue_date'] = temp['issue_date']
# 资产净值
data = w.wss(df['wind_code'].tolist(), "prt_netasset",
"rptDate=%s" % (rptDate))
temp = utils.wind2df(data)
df['netasset'] = temp['prt_netasset']
# 基金经理
data = w.wss(df['wind_code'].tolist(), "fund_fundmanager")
temp = utils.wind2df(data)
df['fundmanager'] = temp['fund_fundmanager']
# 当前申购赎回状态
today = datetime.datetime.today().strftime('%Y-%m-%d')
data = w.wss(df['wind_code'].tolist(), "fund_dq_status",
"tradeDate=%s" % (today))
temp = utils.wind2df(data)
df['fund_status'] = temp['fund_dq_status']
df.to_excel(fname, index=False)
def download_data(start_date, end_date):
w.start()
data = w.wsd('881001.WI', 'pe_ttm', start_date, end_date, 'year=2018')
pe = utils.wind2df(data)
data = w.wsd('CBA00101.CS', 'ytm_b', start_date, end_date, 'returnType=1')
ytm = utils.wind2df(data)
data = w.wsd('881001.WI', 'close', start_date, end_date)
close = utils.wind2df(data)
return pe, ytm, close
def get_wind_data(code, start_date, end_date):
w.start()
fields = "mkt_freeshares,vwap,amt,close,dealnum,free_turn,volume,mfd_buyamt_a,mfd_sellamt_a,high,low"
# data = w.wsd(code, fields, beginTime=start_date, endTime=end_date, "PriceAdj=F")
# data = w.wsd("000402.SZ", "mkt_freeshares,vwap,amt,close", "2017-03-15", "2017-03-16", "PriceAdj=F")
data = w.wsd(code, fields, start_date, end_date, "traderType=1;PriceAdj=F")
return utils.wind2df(data)
def update_mixed_list():
w.start()
today = datetime.datetime.today().strftime('%Y-%m-%d')
data = w.wset("sectorconstituent", "date=%s;windcode=885013.WI" % (today))
df = utils.wind2df(data)
fname = u'%s/混合型基金列表.xlsx' % (const.DATA_DIR)
update_fund_list(df, fname)
def add_old_data(ticker):
w.start()
fname = '%s/history/%s.xlsx' % (const.DATA_DIR, ticker)
if os.path.exists(fname):
df = pd.read_excel(fname)
if 'outmessage' in df.columns:
del df['outmessage']
df = df[['nav_adj']]
end_date = pd.to_datetime(df.index[0]) - datetime.timedelta(1)
data = w.wss(ticker, 'issue_date')
start_date = data.Data[0][0]
if start_date < end_date:
# print start_date, end_date
days = (pd.to_datetime(end_date, format='%Y-%m-%d') -
start_date).days
data = w.wsd(ticker, "NAV_adj", "ED-%dD" % (days), end_date, "")
old_df = utils.wind2df(data)
df = old_df.append(df)
# df = utils.get_historical_return(df)
df.index = pd.to_datetime(df.index)
df = df.loc[~df.index.duplicated(keep='first')]
df.to_excel(fname)
else:
df = df[['nav_adj']]
df = df.loc[~df.index.duplicated(keep='first')]
df.to_excel(fname)
def update_nav(ticker):
w.start()
if datetime.datetime.now().hour < 15:
today = datetime.datetime.today() - datetime.timedelta(1)
else:
today = datetime.datetime.today()
fname = '%s/history/%s.xlsx' % (const.DATA_DIR, ticker)
if not os.path.exists(fname):
utils.down_historical_nav(ticker)
df = pd.read_excel(fname, index_col=0)
df = utils.get_historical_return(df)
df.to_excel(fname)
return
old_df = pd.read_excel(fname, index_col=0)
last_day = old_df.index[-1]
days = (today - last_day).days - 1
if days < 0:
return
data = w.wsd(ticker, "NAV_adj", "ED-%dD" % (days), today, "")
df = utils.wind2df(data)
for col in old_df.columns:
if col not in df.columns:
df[col] = 0
df = old_df.append(df)
df = utils.get_historical_return(df)
df.to_excel(fname)
def get_PE(start_date, end_date):
"""
return pandas series
"""
WIND_CODE = "881001.WI"
data = w.wsd(WIND_CODE, 'pe_ttm', beginTime=start_date, endTime=end_date)
pe_df = utils.wind2df(data)
return pe_df['pe_ttm']
def download_future_information(future_code, start_date, end_date):
fname = '%s/%s.xlsx'%(const.FUTURE_DIR, future_code)
'''
if os.path.exists(fname):
return
'''
data = w.wsd(future_code, "oi,close", start_date, end_date)
df = utils.wind2df(data)
df.to_excel(fname)
def download_option_information(option_code, start_date, end_date):
fname = '%s/%s.xlsx'%(const.OPTION_DIR, option_code)
'''
if os.path.exists(fname):
return
'''
data = w.wsd(option_code, "oi,close", start_date, end_date)
df = utils.wind2df(data)
df.to_excel(fname)
def append_option_column(option_code, col_name):
fname = '%s/%s.xlsx'%(const.OPTION_DIR, option_code)
if os.path.exists(fname):
df = pd.read_excel(fname, index_col=0)
if col_name.lower() in df.columns:
return
start_date, end_date = df.index[0].strftime('%Y-%m-%d'), df.index[-1].strftime('%Y-%m-%d')
data = w.wsd(option_code, col_name, start_date, end_date)
temp = utils.wind2df(data)
df[col_name.lower()] = temp[col_name]
df.to_excel(fname)
def new_df(df):
start_date = pd.to_datetime(df.index[-1]) + datetime.timedelta(1)
end_date = datetime.datetime.today() - datetime.timedelta(1)
if start_date > end_date:
return df
col2edb, edb2col = get_col2edb()
codes = [col2edb[col] for col in df.columns]
# print df.tail()
# print '\n'
data = w.edb(codes, start_date.strftime('%Y-%m-%d'), end_date.strftime('%Y-%m-%d'), "Fill=Previous")
app_df = utils.wind2df(data)
# print app_df
app_df.columns = [edb2col[x] for x in app_df.columns]
app_df = app_df[df.columns]
app_df.index = app_df.index.map(lambda x: x - datetime.timedelta(seconds=0.005))
# print app_df
df = df.append(app_df)
return df
def get_data(symbol):
if not symbol.endswith('.OF'):
fname = '%s/%s.xlsx'%(const.FOF_DIR, symbol)
if os.path.exists(fname):
df = pd.read_excel(fname, index_col=0)
else:
today = datetime.datetime.today().strftime('%Y-%m-%d')
w.start()
data = w.wsd(symbol, 'close', '2015-01-01', today)
df = utils.wind2df(data)
df['return'] = df['close'].pct_change()
df.to_excel(fname)
return df[['return']]
else:
fname = "%s/history/%s.xlsx"%(const.DATA_DIR, symbol)
if os.path.exists(fname):
df = pd.read_excel(fname, index_col=0)
df['return'] = df['nav_adj'].pct_change()
return df[['return']]
else:
print fname
return pd.DataFrame()
def update_nav(ticker):
w.start()
today = datetime.datetime.today() - datetime.timedelta(1)
fname = '%s/history/%s.xlsx' % (const.DATA_DIR, ticker)
if not os.path.exists(fname):
utils.down_historical_nav(ticker)
df = pd.read_excel(fname, index_col=0)
df = utils.get_historical_return(df)
df.to_excel(fname)
return
old_df = pd.read_excel(fname, index_col=0)
# old_df = old_df[['nav_adj']]
# old_df.to_excel(fname)
# old_df = old_df.drop(old_df.index[-4:])
# old_df.index = old_df.index.map(lambda x: x.strftime('%Y-%m-%d'))
# old_df.index = pd.to_datetime(old_df.index)
if 'outmessage' in old_df.columns:
del old_df['outmessage']
last_day = old_df.index[-1]
days = (today - last_day).days - 1
if days < 0:
return
data = w.wsd(ticker, "NAV_adj", "ED-%dD" % (days), today, "")
df = utils.wind2df(data)
if 'outmessage' in df.columns:
old_df.to_excel(fname)
return
for col in old_df.columns:
if col not in df.columns:
df[col] = 0
df = old_df.append(df)
df.index = df.index.map(lambda x: x.strftime('%Y-%m-%d'))
df.index = pd.to_datetime(df.index)
# df = utils.get_historical_return(df)
df = df.loc[~df.index.duplicated(keep='first')]
df.to_excel(fname)
def download_margin(wind_code, start_date, end_date):
fields = "mrg_long_bal,mrg_long_amt,mrg_short_bal,margin_saletradingamount"
data = w.wsd(wind_code, fields, start_date, end_date)
df = utils.wind2df(data)
return df
def download_index_close(wind_code, start_date, end_date):
w.start()
data = w.wsd(wind_code, 'close', start_date, end_date)
return utils.wind2df(data)
def get_data(asset, start_date, end_date):
data = w.wsd(asset, 'close', beginTime=start_date, endTime=end_date)
df = utils.wind2df(data)
return df
def get_ytm(start_date, end_date):
BOND_CODE = "065.CS"
data = w.wsd(BOND_CODE, 'ytm_b', start_date, end_date, "returnType=1")
ytm_df = utils.wind2df(data)
return (1 / ytm_df['ytm_b'])
def get_jpy(start_date, end_date):
JPYCNY_CODE = "JPYCNY.EX"
data = w.wsd(JPYCNY_CODE, 'close', beginTime=start_date, endTime=end_date)
jpy_df = utils.wind2df(data)
return jpy_df['close']
def get_vix(start_date, end_date):
VIX_CODE = "000188.SH"
data = w.wsd(VIX_CODE, 'close', beginTime=start_date, endTime=end_date)
vix_df = utils.wind2df(data)
return vix_df['close']
def get_pe(asset_code, start_date, end_date):
data = w.wsd(asset_code, 'pe_ttm', start_date, end_date)
df = utils.wind2df(data)
return df['pe_ttm']
def get_wind_data(code, start_date, end_date):
w.start()
fields = "mkt_freeshares,vwap,amt,close,dealnum,volume,mfd_buyamt_a,mfd_sellamt_a,high,low,pe_ttm"
data = w.wsd(code, fields, start_date, end_date, "traderType=1;PriceAdj=F")
return utils.wind2df(data)
def get_pb(asset_code, start_date, end_date):
data = w.wsd(asset_code, 'pb_lf', start_date, end_date)
df = utils.wind2df(data)
return df['pb_lf']
def get_wind_data(code, start_date, end_date):
w.start()
fields = "amt,close,high,low,mkt_freeshares,pe_ttm,volume,vwap"
data = w.wsd(code, fields, start_date, end_date, "traderType=1;PriceAdj=F")
return utils.wind2df(data)
def get_data(asset_code, start_date, end_date):
data = w.wsd(asset_code, 'close', start_date, end_date)
df = utils.wind2df(data)
df.index.name = 'date'
return df
