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: