def QA_fetch_get_stock_list(date):
try:
from WindPy import w
except:
QA_util_log_info('No WindPY Module!')
w.start()
if(QA_util_date_valid(date) == False):
QA_util_log_info("wrong date")
else:
awgs = 'date=' + date + ';sectorid=a001010100000000'
data = w.wset("sectorconstituent", awgs)
return pd.DataFrame(np.asarray(data.Data).T, columns=data.Fields, index=data.Times)
def getoptionset(self, underlying):
dataset=None
try:
today=time.strftime('%Y%m%d',time.localtime())
# print('loading %s' %(underlying))
options = w.wset("OptionChain","date="+today+";us_code="+underlying+";option_var=;month=全部;call_put=全部;field=option_code,option_name,strike_price,call_put,first_tradedate,last_tradedate,expiredate")
dataset=options.Data
#期权类型需要转换：认购=c，认沽=p
dataset[3]=[s.replace('认购','c').replace('认沽','p') for s in dataset[3]]
except:
print('查找期权时异常')
return dataset
def _get_industry_code_list(code, date, mode):
w.start()
INDEXCODE = {'sw1': ['indexcode_sw', 'industryType=1'], # 申万一级行业
'sw2': ['indexcode_sw', 'industryType=2'], # 申万二级行业
'sw3': ['indexcode_sw', 'industryType=3'], # 申万三级行业
'sw4': ['indexcode_sw', 'industryType=4'], # 申万明细行业
'wind1': ['indexcode_wind', 'industryType=1'], # 万得一级行业
'wind2': ['indexcode_wind', 'industryType=2'], # 万得二级行业
'wind3': ['indexcode_wind', 'industryType=3'], # 万得三级行业
'wind4': ['indexcode_wind', 'industryType=4'], # 万得明细行业
'citic1': ['indexcode_citic', 'industryType=1'], # 中信一级
'citic2': ['indexcode_citic', 'industryType=2'], # 中信二级
'citic3': ['indexcode_citic', 'industryType=3'], # 中信三级
'amac': ['indexcode_AMAC', ''], # 基金业协会行业
}
if mode == 'all': # 提取全部A股
industry_code_list = w.wset("sectorconstituent", "date=" + date + ";windcode=881001.WI").Data[1]
industry_name = '全部A股'
else: # 按照行业分类提取本行业A股
industry_code = w.wss(code, INDEXCODE[mode][0], 'tradeDate=' + date + ';' + INDEXCODE[mode][1]).Data[0][0]
industry_code_list = w.wset("sectorconstituent", "date=" + date + ";windcode=" + industry_code).Data[1]
industry_name = w.wss(industry_code, "sec_name").Data[0][0]
return industry_name, industry_code_list
def QA_fetch_get_stock_list_special(date, id):
try:
from WindPy import w
except:
QA_util_log_info('No WindPY Module!')
w.start()
if(QA_util_date_valid(date) == False):
QA_util_log_info("wrong date")
else:
if id in ['big', 'small', 'cixin', 'yujing', 'rzrq', 'rq', 'yj', 'st', 'sst']:
awgs = 'date=' + date + ';sectorid=' + \
data_list.wind_stock_list_special_id[id]
data = w.wset("sectorconstituent", awgs)
return pd.DataFrame(np.asarray(data.Data).T, columns=data.Fields, index=data.Times)
def cnt_list_download(end_date):
start_date = "1999-01-01"
cmt_list = pd.read_csv("../cmt_list/cmt_list.csv",
index_col=0).index.tolist()
for cmt in cmt_list:
cnt_df = w.wset(
"futurecc", "startdate=" + start_date + ";enddate=" + end_date +
";wind_code=" + cmt + ";\
field=wind_code,contract_issue_date,last_trade_date")
cnt_df = pd.DataFrame(cnt_df.Data[1:],
index=cnt_df.Fields[1:],
columns=cnt_df.Data[0]).T
cnt_df.to_csv("../cnt_list/" + cmt[:-4] + ".csv")
print "全部合约列表更新完毕"
def fetch_get_stock_list(date):
try:
from WindPy import w
except:
util_log_info('No WindPY Module!')
w.start()
if (util_date_valid(date) == False):
util_log_info("wrong date")
else:
awgs = 'date=' + date + ';sectorid=a001010100000000'
data = w.wset("sectorconstituent", awgs)
return pd.DataFrame(np.asarray(data.Data).T,
columns=data.Fields,
index=data.Times)
def QA_fetch_get_stock_list_special(date,id):
from WindPy import w
#w.start()
w.start()
w.isconnected()
if(is_valid_date(date)==False):
print ("wrong date")
else :
#
#
if id in ['big','small','cixin','yujing','rzrq','rq','yj','st','sst']:
awgs='date='+date+';sectorid='+data_list.wind_stock_list_special_id[id]
data=w.wset("sectorconstituent",awgs)
return data.Data
def data_download(cmt, start_date, end_date):
tmp_cnt_list = w.wset(
"futurecc", "startdate=" + start_date + ";enddate=" + end_date +
";wind_code=" + cmt + ";field=wind_code")
tmp_cnt_list = tmp_cnt_list.Data[0]
tmp_download_cl_list = []
tmp_download_open_list = []
tmp_download_vol_list = []
tmp_download_oi_list = []
tmp_download_high_list = []
tmp_download_low_list = []
for cnt in tmp_cnt_list:
tmp_download_data = w.wsd(cnt, "close,open,volume,oi,high,low",
start_date, end_date, "")
time_index = [
datetime.strptime(str(x), "%Y-%m-%d")
for x in tmp_download_data.Times
]
tmp_cl = pd.Series(tmp_download_data.Data[0],
index=time_index,
name=cnt)
tmp_open = pd.Series(tmp_download_data.Data[1],
index=time_index,
name=cnt)
tmp_vol = pd.Series(tmp_download_data.Data[2],
index=time_index,
name=cnt)
tmp_oi = pd.Series(tmp_download_data.Data[3],
index=time_index,
name=cnt)
tmp_high = pd.Series(tmp_download_data.Data[4],
index=time_index,
name=cnt)
tmp_low = pd.Series(tmp_download_data.Data[5],
index=time_index,
name=cnt)
tmp_download_cl_list.append(tmp_cl)
tmp_download_open_list.append(tmp_open)
tmp_download_vol_list.append(tmp_vol)
tmp_download_oi_list.append(tmp_oi)
tmp_download_high_list.append(tmp_high)
tmp_download_low_list.append(tmp_low)
tmp_update_cl = pd.concat(tmp_download_cl_list, axis=1)
tmp_update_open = pd.concat(tmp_download_open_list, axis=1)
tmp_update_vol = pd.concat(tmp_download_vol_list, axis=1)
tmp_update_oi = pd.concat(tmp_download_oi_list, axis=1)
tmp_update_high = pd.concat(tmp_download_high_list, axis=1)
tmp_update_low = pd.concat(tmp_download_low_list, axis=1)
return tmp_update_cl, tmp_update_open, tmp_update_vol, tmp_update_oi, tmp_update_high, tmp_update_low
def CTDInfoData(self, symbols):
"""
逐个债券代码查询基础数据
wss代码可以借助 WindNavigator自动生成copy即可使用;
"""
print(self.getCurrentTime(), ": Download CF Starting:")
for i in range(len(symbols[0])):
symbol = symbols[0][i]
print(symbol)
w.start()
CF = w.wset("conversionfactor", "windcode=" + symbol)
print(CF.Data[0])
LASTDELIVERY_DATE = symbols[1][i].strftime("%Y%m%d")
print(LASTDELIVERY_DATE)
CTD = w.wss(
list(filter(lambda s: s.endswith("IB"), CF.Data[0])),
"ptmyear,anal_precupn,nxcupn,couponrate,interestfrequency",
"tradeDate=" + LASTDELIVERY_DATE)
BondBaseInfo_data = pd.DataFrame()
print(CTD)
if CTD.Data:
BondBaseInfo_data['BOND_CD'] = CTD.Codes
BondBaseInfo_data['PTMYEAR'] = CTD.Data[0]
BondBaseInfo_data['ANAL_PRECUPN'] = CTD.Data[1]
BondBaseInfo_data['NXCUPN'] = CTD.Data[2]
BondBaseInfo_data['COUPONRATE'] = CTD.Data[3]
BondBaseInfo_data['INTERESTFREQUENCY'] = CTD.Data[4]
rownum = len(CTD.Data[1])
for num in range(rownum):
writer.writerow([
symbol, LASTDELIVERY_DATE, BondBaseInfo_data.iloc[num,
0],
BondBaseInfo_data.iloc[num, 1],
BondBaseInfo_data.iloc[num,
2], BondBaseInfo_data.iloc[num,
3],
BondBaseInfo_data.iloc[num,
4], BondBaseInfo_data.iloc[num,
5]
])
else:
print(self.getCurrentTime(), ":empty ")
print(self.getCurrentTime(), ":Download Bond has Finished. ")
def bench_df(trade_days_list):
#筛选出沪深300每个交易日的成分股的代码、名字和权重，已保存csv防止wind quota exceed
#存在一个bug，wind数据中指数调整没有及时体现，函数取得值跟实际不符
bench_df = pd.DataFrame(
columns=['date', 'stock_code', 'stock_name', 'stock_weight'])
for day in trade_days_list:
strday = day[0]
bench_data = w.wset("indexconstituent", "date=" + strday,
"windcode=000300.SH")
temps_df = pd.DataFrame(bench_data.Data).T
temps_df.columns = ['date', 'stock_code', 'stock_name', 'stock_weight']
#保存了当天的样本和权重
bench_df = pd.concat([bench_df, temps_df])
# 将日期格式转化为文本
# 应该可以apply
bench_df['date'] = bench_df['date'].map(
lambda x: datetime.datetime.strftime(x, '%Y-%m-%d'))
#取每个股票的行业信息
bench_stocks = bench_df['stock_code'].drop_duplicates()
stocks_list = list(bench_stocks)
#print(len(stocks_list))
stocks_code = ','.join(stocks_list)
data = w.wss(stocks_code, "industry_sw",
"tradeDate=20170901;cycle=D;industryType=1")
stock_industry = pd.DataFrame([data.Codes, data.Data[0]]).T
stock_industry.columns = ['stock_code', 'stock_industry']
bench_df = pd.merge(bench_df, stock_industry, on='stock_code')
# 取出沪深300成分股每天的涨跌幅
bench_yield_df = pd.DataFrame(
columns=['date', 'stock_code', 'stock_yield'])
for stock in stocks_list:
bench_data = w.wsd(stock, "pct_chg,windcode", "2017-09-01",
"2017-12-31", "")
temps_df = pd.DataFrame(
[bench_data.Times, bench_data.Data[0], bench_data.Data[1]]).T
temps_df.columns = ['date', 'stock_yield', 'stock_code']
bench_yield_df = pd.concat([bench_yield_df, temps_df])
bench_yield_df['date'] = bench_yield_df['date'].map(
lambda x: datetime.datetime.strftime(x, '%Y-%m-%d'))
bench_yield_df.to_csv('data_produce/yield.csv')
bench_df = pd.merge(bench_df, bench_yield_df, on=['date', 'stock_code'])
bench_df = bench_df.sort_values(by=['date', 'stock_code'])
return bench_df
def get_universe(index_id, date=None, output_weight=False):
index_id = index_id.lower()
try:
if index_id == 'fulla' or index_id == 'ashare':
code = 'a001010100000000'
params = 'sectorid=' + code + ';field=wind_code' if date is None \
else 'date=' + str(date) + ';sectorid=' + code
raw_data = w.wset('sectorconstituent', params)
else:
short_params = 'windcode=' + index_id
params = short_params if date is None else short_params + ';date=' + str(
date)
raw_data = w.wset('IndexConstituent', params)
WindDataProvider.force_throw_err(raw_data,
'WindDataProvider.get_universe')
if output_weight:
return pd.DataFrame(
data=raw_data.Data[raw_data.Fields.index('i_weight')],
index=raw_data.Data[raw_data.Fields.index('wind_code')],
columns=['weight'])
else:
return raw_data.Data[raw_data.Fields.index('wind_code')]
except NameError:
pass
def get_index_constituent(index, date):
'''
用于从Wind中获取指数的成分
@param:
index: 需要获取成分的指数代码
date: 获取指数成分的日期
@return:
indexConstituent: 字典，键为成分的代码，值为成分股的中文名称
'''
check_connection()
if not isinstance(date, dt.datetime):
date = dt.datetime.strptime(date, '%Y-%m-%d')
data = w.wset('sectorconstituent', 'date=' + date.strftime('%Y-%m-%d') + ';windcode=' + index)
assert data.ErrorCode == 0, data.Data[0][0]
indexConstituent = dict(zip(data.Data[1], data.Data[2]))
return indexConstituent
def update_etfconstituent_today():
trade_date = datetime.datetime.now().date()
temp = w.wset(
"etfconstituent", "date={today_yyyymmdd};windcode=510050.SH;"
"field=wind_code,volume,cash_substitution_mark,"
"cash_substitution_premium_ratio,fixed_substitution_amount".format(
today_yyyymmdd=trade_date.strftime('%Y%m%d')))
df = pd.DataFrame(temp.Data).T
df.columns = temp.Fields
df.rename({'wind_code': 'sec_code'}, axis=1, inplace=True)
df['etf_sec_code'] = '510050.SH'
df['trade_date'] = trade_date
df.to_sql(ChinaEtfPchRedmList.__tablename__,
engine,
index=False,
if_exists='append')
def w_cnt_oi_rank(cnt,
start_date,
end_date,
fields=None,
order="long",
ranks="all"):
keywords = "startdate=" + str(start_date) + ";enddate=" + str(end_date) + ";wind_code=" + str(cnt) + ";order_by=" + str(order)\
+ ";ranks=" + str(ranks)
if fields is not None:
keywords += ";field=" + fields
tmp_data = w.wset("futureoir", keywords)
WindCheck(tmp_data)
tmp_data = pd.DataFrame(tmp_data.Data,
index=tmp_data.Fields,
columns=tmp_data.Codes).T
return tmp_data
def get_index_contents(date, index_code="000300.SH"):
"""
获取指数成分股 hs300： 000300.SH
@date <%Y-%m-%d>: 日期
@index_code <str>: 指数万得代码
"""
options = {"date": date, "windcode": index_code}
response = WDServer.wset(tablename="indexconstituent",
options=options2str(options))
test_error(response)
output = {
"sec_id": response.Data[1],
"sec_name": response.Data[2],
"sec_weight":
[x / 100 if x is not None else 0. for x in response.Data[3]]
}
return pd.DataFrame(output)
def get_spec(self):
"""
根据重新筛选股票周期，清空上一次的环境设置,并创建新的环境.
"""
# 由于回测期间公用一个spec object，需要在每次更新股票池的时候清空上一次的记录
self._codes = []
self._names = []
assert self._spec_id, "need to call check_spec_id() to get spec id in WindPy"
for id in self._spec_id:
res = w.wset(*self.gen_spec_param(id)).Data
self._codes.extend(res[0])
self._names.extend(res[1])
# 配对后去重
res_ = dict(zip(self._codes, self._names))
self._codes = list(res_.keys())
self._names = list(res_.values())
return res_
def post(self):
"""
json str:{"table_name": "sectorconstituent", "options": "date=2017-03-21;sectorid=1000023121000000"}
:return: 返回万得返回数据dict
"""
data_dic = request.json
logger.info('/wset/ data_dic:%s' % data_dic)
# print('data_dic:%s' % data_dic)
table_name = data_dic['table_name']
options = data_dic['options']
if not w.isconnected():
w.start()
if options == "":
options = None
ret_data = w.wset(table_name, options)
error_code = ret_data.ErrorCode
if error_code != 0:
msg = ERROR_CODE_MSG_DIC.setdefault(error_code, "")
logger.error('wset("%s", "%s") ErrorCode=%d %s' % (table_name, options, error_code, msg))
return {'error_code': ret_data.ErrorCode, 'message': msg}, 404
data_count = len(ret_data.Data)
# if data_count > 0:
# print('ret_data.Fields\n', ret_data.Fields)
# ret_data.Data[0] = [format_2_date_str(dt) for dt in ret_data.Data[0]]
# print('ret_data.Data\n', ret_data.Data)
for n_data in range(data_count):
data = ret_data.Data[n_data]
data_len2 = len(data)
if data_len2 > 0:
# 取出第一个部位None的数据
for item_check in data:
if item_check is not None:
break
# 进行类型检查，如果发现是 datetime, date 类型之一，则进行类型转换
if item_check is not None and type(item_check) in (datetime, date):
ret_data.Data[n_data] = [format_2_date_str(dt) for dt in data]
logger.info('%d column["%s"] date to str', n_data, ret_data.Fields[n_data])
ret_df = pd.DataFrame(ret_data.Data, index=ret_data.Fields, columns=ret_data.Codes)
# print('ret_df\n', ret_df)
ret_dic = ret_df.to_dict()
# print('ret_dic:\n', ret_dic)
return ret_dic
def contract_code(cmt_dict, start_date, end_date):
"""
根据品种分类，对每个品种分别下载其所有合约，包括目前存在和已经退市的，下载的内容包括
合约代码、上市退市日期等，为了之后在某一日期筛选当日有效合约准备。下载后所有合约信息
拼成一个大df，作为中间变量使用
"""
#cmt_dict是板块名和板块包含品种名的字典
cmt_code = [variety_code(v) for v in cmt_dict["all_cmt"]]
cmt_variety = zip(cmt_dict["all_cmt"], cmt_code)
cmt_list = []
for cmt in cmt_variety:
data = w.wset(
"futurecc", "startdate=" + start_date + ";enddate=" + end_date +
";wind_code=" + cmt[1])
df = pd.DataFrame(data.Data, index=data.Fields).T
cmt_list.append(df)
total_cnt = pd.concat(cmt_list)
return total_cnt
def getAStockCodesWind(self, end_date):
stockCodes = w.wset("sectorconstituent",
"date=" + end_date + ";sectorid=a001010100000000")
if stockCodes.ErrorCode != 0:
print "get data failed, exit!"
return None
else:
#print str(stockCodes.Data[2]).decode('unicode_escape')
stock_data = pd.DataFrame()
stock_data['code'] = stockCodes.Data[1]
stock_data['name'] = stockCodes.Data[2]
stock_data.to_csv("dataFileFeed/allStocks.csv",
index=False,
encoding="gbk") #去除索引
return stock_data['code']
def load_fund_pool(self, date, name, pool_number, source="wind_terminal"):
if source == "wind_terminal":
date = Date.change_to_str(date)
data = w.wset("sectorconstituent",
"date=%s;sectorid=%s" % (date, pool_number))
data = pd.DataFrame(data.Data, index=data.Fields).T
data.date = data.date.map(Date.change_to_str)
else:
data = None
out_sub_path = os.path.join(self.pool_load_out_path, name)
if not os.path.exists(out_sub_path):
os.makedirs(out_sub_path)
file = name + '_' + date + '.csv'
out_file = os.path.join(out_sub_path, file)
data.to_csv(out_file)
def QA_fetch_get_stock_list_special(date, id):
from WindPy import w
# w.start()
w.start()
w.isconnected()
if (is_valid_date(date) == False):
print("wrong date")
else:
if id in [
'big', 'small', 'cixin', 'yujing', 'rzrq', 'rq', 'yj', 'st',
'sst'
]:
awgs = 'date=' + date + ';sectorid=' + \
data_list.wind_stock_list_special_id[id]
data = w.wset("sectorconstituent", awgs)
return pd.DataFrame(np.asarray(data.Data).T,
columns=data.Fields,
index=data.Times)
def getData(self):
#for calculating index constituents
d = self.date.strftime('%Y%m%d')
s = '000905.SH'
p = w.wset('IndexConstituent','date='+d+';windcode='+s)
self.Constituent= p.Data[1]
self.stocks = pd.DataFrame({'stocks':p.Data[1]})
# for pulling the data frame
start = 0
end = 100
storage= []
for i in range(0,5):
p = w.wsi(self.Constituent[start:end],"close,amt",
self.date.strftime('%Y-%m-%d 09:30:00'),
self.date.strftime('%Y-%m-%d 15:01:00'))
time = pd.to_datetime(p.Times)
tickers = Series(p.Data[1])
close = Series(p.Data[2])
vol = Series(p.Data[3])
a = DataFrame({'time':time,
'ticker': tickers,
'close': close,
'vol':vol})
a = a.set_index(time)
storage.append(a)
start+= 100
end += 100
self.df = pd.concat(storage).pivot(index='time', columns='ticker', values='close')
#pull the index
storage= []
indices = ['000905.SH','000001.SH','000016.SH','000300.SH','399102.SZ']
p = w.wsi(indices,"close,amt",
self.date.strftime('%Y-%m-%d 09:30:00'),
self.date.strftime('%Y-%m-%d 15:01:00'))
time = pd.to_datetime(p.Times)
tickers = Series(p.Data[1])
close = Series(p.Data[2])
vol = Series(p.Data[3])
a = DataFrame({'time':time,'ticker': tickers,
'close': close,
'vol':vol})
a = a.set_index(time)
self.index = a.pivot(index='time', columns='ticker', values='close')
def getStockMonthToMySql(self):
start_date = '2011-11-01'
end_date = '2019-12-31'
total_trade_list = self.getTradeDay(startDate=start_date,
endDate=end_date,
Period='M')
wsetdata = w.wset(
"sectorconstituent",
"date=%s;sectorid=a001010100000000" % total_trade_list[0])
if wsetdata.ErrorCode != 0:
self.logger.debug("获取全A股数据有误，错误代码" + str(wsetdata.ErrorCode))
index_df = pd.DataFrame(wsetdata.Data,
index=wsetdata.Fields,
columns=wsetdata.Codes).T
if index_df.empty:
return
for trade_date in total_trade_list:
optionstr = "tradeDate=%s;cycle=M" % (
trade_date[:4] + trade_date[5:7] + trade_date[8:])
wssdata = w.wss(
codes=index_df['wind_code'].tolist(),
fields=["mkt_freeshares", "pe_ttm", "ps_ttm", "pct_chg"],
options=optionstr)
if wssdata.ErrorCode != 0:
self.logger.debug("获取因子数据有误，错误代码" + str(wssdata.ErrorCode))
return pd.DataFrame()
resultDf = pd.DataFrame(wssdata.Data,
index=wssdata.Fields,
columns=wssdata.Codes).T
df_list = []
for col in resultDf:
temp_df = pd.DataFrame(resultDf[col].values,
index=resultDf.index,
columns=['factor_value'])
temp_df['update_time'] = trade_date
temp_df['stock_code'] = resultDf.index.tolist()
temp_df['factor_name'] = col
df_list.append(temp_df)
total_fa_df = pd.concat(df_list, axis=0, sort=True)
self.GetDataToMysqlDemo.GetMain(total_fa_df,
'stock_factor_month_value')
self.logger.info("存储日期%s因子数据成功！" % trade_date)
def generate_variety_table(start_date, end_date):
w.start()
oi_df_list = []
start_date = '2017-12-13'
end_date = '2017-12-13'
# 通过品种列表，分别下载每个品种的持仓情况
drop_list = [
'settle', 'ranks', 'date', 'long_position', 'short_position',
'long_position_increase', 'short_position_increase',
'long_potion_rate', 'short_position_rate'
]
for variety in variety_code_list:
params = "startdate=" + start_date + ";enddate=" + end_date + ";varity=" + variety + ";order_by=long;ranks=all"
data = w.wset("futureoir", params)
if len(data.Data) == 0 or (variety in ['IC', 'IH', 'IF', 'T', 'TF'
]): #只看商品
continue
else:
df = pd.DataFrame(data.Data, index=data.Fields).T
df = df.drop(drop_list, axis=1)
df.drop(range(3), inplace=True)
df.index = df['member_name']
del df['member_name']
df['net_position'] = df['net_long_position']
df.loc[pd.isnull(df['net_position']),'net_position'] = \
-df['net_short_position'].loc[pd.isnull(df['net_position'])].fillna(0)
df['net_position_increase'] = df['net_long_position_increase']
df.loc[pd.isnull(df['net_position_increase']),'net_position_increase'] = \
-df['net_short_position_increase'].loc[pd.isnull(df['net_position_increase'])].fillna(0)
df = df.stack(dropna=False)
df.name = variety
oi_df_list.append(df)
# 将每个品种持仓情况的dataframe进行合并
whole_table = oi_df_list[0]
for i in range(1, len(oi_df_list)):
whole_table = pd.concat([whole_table, oi_df_list[i]], axis=1)
# 输出到csv文件中
variety_oi_file = 'Company_Contract_OpenInterest.csv'
whole_table.to_csv('../output/' + variety_oi_file, encoding='gbk')
# 返回文件名
return variety_oi_file
def update_a_share_description():
temp = w.wset(
"sectorconstituent",
"date={today_yyyymmdd};sectorid=a001010100000000".format(
today_yyyymmdd=today_yyyymmdd))
df = pd.DataFrame(temp.Data[1:]).T
df.columns = temp.Fields[1:]
df.rename({'wind_code': 'sec_code'}, axis=1, inplace=True)
sql = select([AShareDescription.sec_code])
df_in_db = pd.read_sql(sql, engine)
sec_codes_in_db = df_in_db['sec_code'].tolist()
# TODO: 保存全部A股，更新全部，包括名称
df_to_save = df.query('sec_code not in @sec_codes_in_db')
df_to_save.to_sql(AShareDescription.__tablename__,
con=engine,
index=False,
if_exists='append')
def getFuturesInfoFromWind(self, collection, cmd, **kwargs):
# 主要用于抓取wind里各合约的信息
self.windConn()
coll = self.db[collection]
ptn_1 = re.compile('\w+(?=\.)')
res_1 = ptn_1.search(cmd).group()
ptn_2 = re.compile('(?<=\.)\w+')
res_2 = ptn_2.search(cmd).group()
queryArgs = {'wind_code': {'$regex': '\A%s\d+\.%s\Z' % (res_1, res_2)}}
dt_res = list(
coll.find(queryArgs, ['contract_issue_date']).sort(
'contract_issue_date', pymongo.DESCENDING).limit(1))
if dt_res:
dt_last = dt_res[0]['contract_issue_date']
dt_start = dt_last - timedelta(1)
else:
dt_start = datetime(1990, 1, 1)
wres = w.wset(tablename='futurecc',
startdate=dt_start.strftime('%Y-%m-%d'),
enddate=datetime.today().strftime('%Y-%m-%d'),
wind_code=cmd)
wfields = wres.Fields
unit_total = len(wfields) * len(wres.Data[0])
self.logger.info(u'共抓取了关于%s品种%d个单元格数据' % (cmd, unit_total))
res = dict(zip(wfields, wres.Data))
res.pop('change_limit')
res.pop('target_margin')
df = pd.DataFrame.from_dict(res)
fu_info = df.to_dict(orient='index')
for i, v in fu_info.items():
v.update(kwargs)
# 用来解决如果出现NaT的数据，无法传入数据库的问题
if pd.isnull(v['last_delivery_month']):
v['last_delivery_month'] = None
if not coll.find_one({'wind_code': v['wind_code']}):
v['update_time'] = datetime.now()
coll.insert_one(v)
elif coll.find_one({'wind_code': v['wind_code']
})['last_trade_date'] != v['last_trade_date']:
# 有些品种的wind_code会变，比如TA005.CZC之前是1005的合约，现在变成了2005的合约，真特么SB
v['update_time'] = datetime.now()
coll.update({'wind_code': v['wind_code']}, v)
return
def get_weights(self):
w.start()
index_data = w.wset("indexconstituent", "date="+self.date+";windcode="+self.index_code).Data
index_data_code = index_data[1]
index_data_weight = index_data[3]
code_weights_all = dict(zip(index_data_code, index_data_weight))
code_weights = {}
for code in self.code_list:
try:
code_weights[code] = code_weights_all[list_jq2wind([code])[0]]
except KeyError:
code_weights[code] = 0.0
all_weights = np.sum(np.array([t for t in code_weights.values()]))
for code in self.code_list:
try:
code_weights[code] = code_weights_all[list_jq2wind([code])[0]] / all_weights
except KeyError:
code_weights[code] = 0.0
return code_weights
def get_wset(set_name, **options):
'''
获取数据集数据。
Parameters
-----------
set_name
集合名称
options
选项
Returns
----------
DataFrame
'''
options = dict_2_str(options)
data = w.wset(set_name, options)
data = pd.DataFrame(data.Data, index=data.Fields).T
return data
def queryDataByDate(begin, end, mkt="SSE"):
# 所有A股
sectorid = "a001010100000000"
if (mkt == "CFFEX"):
sectorid = "a599010101000000"
elif (mkt == "SHFE"):
sectorid = "a599010201000000"
elif (mkt == "DCE"):
sectorid = "a599010301000000"
elif (mkt == "CZCE"):
sectorid = "a599010401000000"
else:
print("Bad Market NAME,DEFAULT SSE")
conn = pymysql.connect(host='192.168.10.189',
user='******',
passwd='cc2718281828',
db='AMGMH',
charset='utf8')
cur = conn.cursor()
try:
cur.execute(
"select t1.DateTime, min(t2.DateTime) as nextDate from TDays t1, TDays t2 \
where t1.TradingCalendar=t2.TradingCalendar and t1.DateTime<t2.DateTime and \
t1.TradingCalendar='%s' and t1.DateTime>='%s' and t1.DateTime<='%s' \
group by t1.DateTime" % (mkt, begin, end))
results = cur.fetchall()
for row in results:
print(row[0])
print(row[1])
paramStr = "date=" + row[0].strftime(
"%Y%m%d"
) + ";sectorid=" + sectorid + ";field=wind_code,sec_name"
print(paramStr)
securityCodes = w.wset("sectorconstituent", "%s" % (paramStr))
saveSecurityCodes(securityCodes, row[0], row[1])
except:
print("Error when query trade date")
cur.close()
conn.close()
def update_bond():
everything = pd.read_csv("bonds.xlsx", encoding='gbk')
date = "date=" + str(int(time.strftime("%Y-%m-%d", time.localtime()))) + ";sectorid=1000008620000000;field=wind_code,sec_name"
inf = w.wset("sectorconstituent",date)
if inf.ErrorCode==0:
print("errrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrror")
return
new_bonds = inf.Data
for bond in new_bonds:
daoqiri = (w.wss("136670.SH", "maturitydate").Data)[0]
for index,ch in enumerate(daoqiri):
if ch == '/':
daoqiri[index]='-'
everything = everything.append(
pd.DataFrame({'证券代码': [bond[0][0]], '证券简称': [bond[1][0]], '到期日期':[daoqiri]}),
ignore_index=True)
#去重
everything.drop_duplicates(subset='证券代码',keep='last',inplace=True)
#按到期日近到远排序
everything.sort_values('到期日期',inplace=True)
everything.to_excel("bonds.xlsx", index=False, header=True)
def updateWindCode(self):
'''更新股票代码'''
today = date.today().strftime('%Y%m%d')
field = 'wind_code,sec_name' # 字段名：股票代码和股票名称
sector = '全部A股'
option = 'date=%s;sector=%s;field=%s' % (today,sector,field)
wset_data = w.wset('SectorConstituent',option)
if wset_data.ErrorCode == 0:
stockCodeData = zip(wset_data.Data[0],wset_data.Data[1]) # 返回值data[0],data[1]分别为代码和名称
sql = "delete from stockCode"
self.cur.execute(sql)
self.con.commit()
print '删除代码完毕'
# 插入到数据库中
sql = "insert ignore into stockCode values(%s,%s)"
self.cur.executemany(sql,tuple(stockCodeData))
self.con.commit()
else:
logging.info('ERROR-股票代码更新错误')
print '更新代码完毕'
def algo(context):
global position_now, position_target
date_now = context.now.strftime('%Y-%m-%d')
date_previous = get_trading_date_from_now(date_now, -1,
ql.Days) # 前一个交易日，用于获取因子数据的日期
select_time_value = select_time_model[date_now] # 择时信号计算
if date_now not in trading_date_list: # 非调仓日
pass
else: # 调仓日执行算法，更新position_target
position_now = False # 虚拟上，调仓日需要提前清仓
stock_dict[date_now] = {}
# 根据指数获取股票候选池的代码
all_code_set = set()
for index in INDEX:
code_set = set(
w.wset("sectorconstituent",
"date=" + date_previous + ";windcode=" + index).Data[1])
all_code_set = all_code_set | code_set
code_list = list(all_code_set) # 股票候选池
strategy = STRATEGY(code_list, date_previous, 0.9)
select_code_list = list_wind2jq(strategy.select_code())
if len(select_code_list) > 0: # 有可选股票时记录下可选股票
stock_now = WEIGHTS(select_code_list, date_previous).get_weights()
position_target = stock_now
else:
position_target = {}
# 择时判定
if select_time_value >= 0 and not position_now and position_target != {}: # LLT择时信号为正,空仓且有目标持仓状态
stock_dict[date_now] = position_target
position_now = True
elif select_time_value < 0 and position_now and position_target != {}: # LLT择时信号为负且持仓状态:
stock_dict[date_now] = {}
position_now = False
# 打印择时信号和仓位配置信息
print(date_now + ('日回测程序执行中...，择时值：%.2f' % select_time_value))
print(date_now + '日回测程序执行中...，选股：')
print(position_target)
def draw():
wb = xw.Book.caller()
wb = xw.books.active
sht = wb.sheets[0]
r = sht.range('C213').value
end = sht.range('J2').value
w_wsd = w.wsd("510050.SH", "close", "ED-20TD", end, "")
ETFclose = pd.Series(w_wsd.Data[0], index=w_wsd.Times)
resultcontango = pd.DataFrame()
for i in xrange(1, len(ETFclose), 1):
T = ETFclose.index[i]
optioncode = w.wset(
"optionchain",
u'date=%s;us_code=510050.SH;option_var=全部;call_put=全部;field=option_code,strike_price,month,call_put,expiredate'
% (T))
optioninfo = pd.DataFrame(optioncode.Data)
optioninfo = optioninfo.T
optioninfo = optioninfo[optioninfo.iloc[:, 1] * 100 %
5 == 0] #去掉行权价格为零头的期权
optioninfo.columns = ['Code', 'K', 'ex_day', 'C or P', 'T_days']
optioninfo['trade day'] = T
optioninfo.reset_index(inplace=True)
optioninfo.drop(optioninfo.columns[0], axis=1, inplace=True)
#找到ATM
ups = np.abs(optioninfo.iloc[:, 1] - ETFclose[i - 1])
optioninfo = optioninfo[ups == np.min(ups)]
##找到00
optioninfo = optioninfo[optioninfo.iloc[:, 2] == optioninfo.iloc[0, 2]]
##找到期权价格
w_wsdoption = w.wsd(optioninfo['Code'].tolist(), "close", T, T, "")
optioninfo['price'] = w_wsdoption.Data[0]
optioninfo['ETFprice'] = ETFclose[i]
F = (optioninfo.iloc[0, 6] - optioninfo.iloc[1, 6]) * np.exp(
(-r * (optioninfo.iloc[0, 4] + 1)) / 365) + optioninfo.iloc[0, 1]
contangorate = pd.Series(
(F - optioninfo.iloc[0, 7]) / optioninfo.iloc[0, 7] /
(optioninfo.iloc[0, 4] + 1) * 365)
contangorate.index = [T]
resultcontango = pd.concat([resultcontango, contangorate], axis=0)
def get_history_beta(index="881001.WI", start_date="2005-01-01", end_date="2016-12-31"):
w.start()
codes = w.wset("IndexConstituent","date=%s;windcode=%s;field=wind_code"%(end_date, index))
codes = codes.Data[0]
start_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
ind_df = pd.read_csv("%s/index/%s.csv"%(DATA_DIR, index))
ind_df = df_preprocess(ind_df)
ind_df = ind_df[ind_df.index >= start_date]
# 指数月度方差
month_ind_var_df = ind_df['return'].resample('BM').var()
df = pd.DataFrame({"var": month_ind_var_df}, index=month_ind_var_df.index)
for code in codes:
print("processing %s..."%(code))
sym_df = pd.read_csv("%s/stocks/%s.csv"%(DATA_DIR, code))
sym_df = df_preprocess(sym_df)
sym_df = sym_df[sym_df >= start_date]
if sym_df.shape[0] == 0:
continue
betas = []
# 枚举月
for i, date in enumerate(month_ind_var_df.index):
stock_return = sym_df.loc[(sym_df.index.year == date.year) & (sym_df.index.month == date.month), 'return']
index_return = ind_df.loc[(ind_df.index.year == date.year) & (ind_df.index.month == date.month), 'return']
if stock_return.shape == index_return.shape:
# 计算beta系数
beta = np.cov(stock_return, index_return)[0][1] / month_ind_var_df[i]
betas.append(beta)
else:
print code, date, stock_return.shape, index_return.shape
betas.append(np.nan)
df[code] = betas
df['date'] = df.index
df.to_csv("../result/history_beta.csv", index=False)
printpy(wsddata1)
# 通过wsd来提取各个报告期财务数据
print('\n\n' + '-----通过wsd来提取各个报告期财务数据-----' + '\n')
wsddata2 = w.wsd("600000.SH", "tot_oper_rev,tot_oper_cost,opprofit,net_profit_is", "2008-01-01",
"2015-12-22", "rptType=1;Period=Q;Days=Alldays;Fill=Previous")
printpy(wsddata2)
# 通过wss来取截面数据
print('\n\n' + '-----通过wss来取截面数据-----' + '\n')
wssdata = w.wss("600000.SH,600007.SH,600016.SH", "ev,total_shares",
"tradeDate=20151222;industryType=1")
printpy(wssdata)
# 通过wst来取日内成交数据
print('\n\n' + '-----通过wst来取日内成交数据-----' + '\n')
wstdata = w.wst("IF.CFE", "last,volume", "2015-12-22 09:00:00", "2015-12-22 14:04:45")
printpy(wstdata)
# 通过wsi来取日内分钟数据
print('\n\n' + '-----通过wsi来取日内分钟数据-----' + '\n')
wsidata = w.wsi("IF.CFE", "open,high,low,close,volume,amt", "2015-12-22 09:00:00",
"2015-12-22 14:06:15")
printpy(wsidata)
# 通过wset来取数据集数据
print('\n\n' + '-----通过wset来取数据集数据,获取沪深300指数权重-----' + '\n')
wsetdata = w.wset("IndexConstituent",
"date=20151222;windcode=000300.SH;field=date,wind_code,i_weight")
printpy(wsetdata)
__author__ = 'aming.tao'
from WindPy import w
from datetime import *
w.start()
data=w.wsd("600000.SH","close,amt","2013-04-30", datetime.today()-timedelta(1))#取浦发银行收盘价等信
data=w.wsd("600000.SH","close,amt", datetime.today()-timedelta(100))#
data=w.wsi("600000.SH","close,amt","2015-10-01 9:00:00")#取浦发银行分钟收盘价等信息
data=w.wst("600000.SH","open", datetime.today()-timedelta(0,2*3600), datetime.now())#取浦发银行tick数据信息
data=w.wss("600000.SH,000001.SZ","eps_ttm,orps,surpluscapitalps","rptDate=20121231")#取浦发银行等财务数据信息
data=w.wset("SectorConstituent",u"date=20130608;sector=全部A股")#取全部A 股股票代码、名称信息
w.wset("IndexConstituent","date=20130608;windcode=000300.SH;field=wind_code,i_weight")#取沪深300 指数中股票代码和权重
w.wset("TradeSuspend","startdate=20130508;enddate=20130608;field=wind_code,sec_name,suspend_type,suspend_reason")#取停牌信息
w.wset("SectorConstituent",u"date=20130608;sector=风险警示股票;field=wind_code,sec_name")#取ST 股票等风险警示股票信息
w.tdays("2013-05-01","2013-06-08")#返回5 月1 日到6 月8 日之间的交易日序列
w.tdays("2013-05-01")#返回5 月1 日到当前时间的交易日序列
w.tdaysoffset(-5,"2013-05-01")#返回5 月1 日前推五个交易日的日期，返回2013-4-19
w.tdaysoffset(-5)#返回当前时间前推五个交易日的日期
w.tdayscount("2013-05-01","2013-06-08")#返回5 月1 日到6 月8 日之间的交易日序列长度，为27
w.stop()
######## coding=gbk
__author__ = 'henry'
import site
import sys
import types
reload(sys)
sys.setdefaultencoding( "utf-8" )
from WindPy import w
from Utility import str1D
from Utility import str2D
if (w.isconnected() != True):
w.start(); #pwd:607387
date = sys.argv[1]
sector = sys.argv[2]
#1.List getStocks
print "WindStockReuslt:"
res= w.wset("SectorConstituent","date={0};sector={1}".format(date, sector))
print res
print "\n.Codes="+str1D(res.Codes)
print "\n.Fields="+str1D(res.Fields)
print "\n.Times="+str1D([ format(x,'%Y%m%d') for x in res.Times])
print "\n.Data="+str2D(res.Data)
tradedate text,
openprice real,
highprice real,
lowprice real,
closeprice real,
volume real,
amt real,
PRIMARY KEY (secid,tradedate)
)
""")
sql = "INSERT OR REPLACE INTO stockprice VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
# 通过wset来取数据集数据
print('\n\n'+'-----通过wset来取数据集数据,获取全部A股代码列表-----'+'\n')
wsetdata=w.wset('SectorConstituent','date=20160625;sectorId=a001010100000000;field=wind_code')
print(wsetdata)
for j in range(0,len(wsetdata.Data[0])):
# 通过wsd来提取时间序列数据，比如取开高低收成交量，成交额数据
print( u"\n\n-----第 %i 次通过wsd来提取 %s 开高低收成交量数据-----\n" %(j,str(wsetdata.Data[0][j])) )
wssdata=w.wss(str(wsetdata.Data[0][j]),'ipo_date')
wsddata1=w.wsd(str(wsetdata.Data[0][j]), "open,high,low,close,volume,amt", wssdata.Data[0][0], dt, "Fill=Previous")
if wsddata1.ErrorCode!=0:
continue
print (wsddata1)
for i in range(0,len(wsddata1.Data[0])):
sqllist=[]
sqltuple=()
sqllist.append(str(wsetdata.Data[0][j]))
if len(wsddata1.Times)>1:
