def get_equity_reinvest_return(self, trading_day_list, sec_ids, freq='day'): equity_reinvest_return_dict = {} trading_day_list.sort() for i, date in enumerate(trading_day_list): if freq == 'day': df = DataAPI.EquRetudGet(secID=sec_ids, beginDate=date, endDate=date, field=u"secID,dailyReturnReinv", pandas="1") equity_reinvest_return_dict[date] = df.fillna(0).set_index( 'secID')['dailyReturnReinv'] else: if i > 0: last_period_end = trading_day_list[i - 1] df = DataAPI.EquRetudGet( secID=sec_ids, beginDate=last_period_end, endDate=date, field=u"secID,dailyReturnReinv,tradeDate", pandas="1") df['tradeDate'] = df['tradeDate'].apply( lambda x: x.replace('-', '')) df = df[df['tradeDate'] > last_period_end] df.drop('tradeDate', axis=1, inplace=True) df['dailyReturnReinv'] = df['dailyReturnReinv'] + 1 return_reinv = df.groupby('secID').prod(skipna=True) - 1 equity_reinvest_return_dict[date] = return_reinv[ 'dailyReturnReinv'] return equity_reinvest_return_dict
def get_features(security_id='', start_date='', end_date='', min_unit="5"): df = DataAPI.TradeCalGet(exchangeCD=u"XSHG,XSHE", beginDate=start_date.replace('-', ''), endDate=end_date.replace('-', ''), isOpen=u"1", field=u"calendarDate", pandas="1") t_dates = list(df['calendarDate']) df_min = DataAPI.MktBarHistDateRangeGet(securityID=security_id, startDate=start_date.replace( '-', ''), endDate=end_date.replace('-', ''), unit=min_unit, field=u"", pandas="1") df_min['vwap'] = df_min['totalValue'] / df_min['totalVolume'] # TODO normally it should be closePrice df_min['ret'] = df_min[['vwap' ]].rolling(2).apply(lambda x: x[-1] / x[0] - 1) df_min['label'] = [ 1 if item >= 0.0 else 0 for item in (([0.0] + list(df_min['ret']))[1:]) ] # df_min['dealPrice'] = (df_min['openPrice'] + df_min['vwap'])/2 df_min['dealPrice'] = df_min['vwap'] return df_min
def get_factor_return(self, trading_day_list, freq='day'): factor_return_dict = {} trading_day_list.sort() for i, date in enumerate(trading_day_list): if freq == 'day': factor_return_dict[date] = DataAPI.RMFactorRetDayGet( tradeDate=date, field=u"", pandas="1")[DATAYES_FACTOR_ORDER].T[0] else: if i > 0: last_period_end = trading_day_list[i - 1] df = DataAPI.RMFactorRetDayGet(beginDate=last_period_end, endDate=date, field=u"", pandas="1") df['tradeDate'] = df['tradeDate'].apply( lambda x: x.replace('-', '')) df = df[df['tradeDate'] > last_period_end] df.drop(['tradeDate', 'updateTime'], axis=1, inplace=True) df += 1 factor_return_dict[date] = df.prod() - 1 return factor_return_dict
def get_stock_return(self, begin_date, end_date, freq='month'): if freq == 'week': stock_return = DataAPI.MktEquwGet( beginDate=begin_date, endDate=end_date, field=['secID', 'endDate', 'chgPct']) stock_return = stock_return.pivot(index='endDate', columns='secID', values='chgPct') stock_return.index = stock_return.index.str.replace('-', '') elif freq == 'month': stock_return = DataAPI.MktEqumGet( beginDate=begin_date, endDate=end_date, field=['secID', 'endDate', 'chgPct']) stock_return = stock_return.pivot(index='endDate', columns='secID', values='chgPct') stock_return.index = stock_return.index.str.replace('-', '') elif freq == 'day': stock_return = DataAPI.MktEqudGet( beginDate=begin_date, endDate=end_date, field=['secID', 'tradeDate', 'chgPct']) stock_return = stock_return.pivot(index='tradeDate', columns='secID', values='chgPct') stock_return.index = stock_return.index.str.replace('-', '') else: raise InputParameterError('不支持的频率格式') return stock_return
def get_features(security_id=u"300634.XSHE", start_date='20191202', end_date='20191206', min_unit="1", tick=False, bc='', win_len=20): df = DataAPI.TradeCalGet(exchangeCD=u"XSHG,XSHE", beginDate=start_date.replace('-', ''), endDate=end_date.replace('-', ''), isOpen=u"1", field=u"calendarDate", pandas="1") t_dates = list(set(df['calendarDate'])) df_min = DataAPI.MktBarHistDateRangeGet(securityID=security_id, startDate=start_date.replace('-', ''), endDate=end_date.replace('-', ''), unit=min_unit, field=u"", pandas="1") df_bc_min = DataAPI.MktBarHistDateRangeGet(securityID=bc, startDate=start_date.replace('-', ''), endDate=end_date.replace('-', ''), unit=min_unit, field=u"", pandas="1") lst = [] for date in t_dates: _df_min = df_min[df_min.dataDate == date] _df_bc_min = df_bc_min[df_bc_min.dataDate == date] _df = get_features_by_date(security_id=security_id, date=date, min_unit=min_unit, tick=tick, df_min=_df_min, df_bc_min=_df_bc_min, win_len=win_len) lst.append(_df) try: ret = pd.concat(lst, axis=0) except Exception as ex: logger.warn('concat the feautres for security id:{0} with error:{1}'.format(security_id, ex)) ret = None return ret
def getRMCovarianceShort(self, i): if self.fre_type == 'short': data = DataAPI.RMCovarianceShortGet(tradeDate="%s" % i, beginDate=u"", \ endDate=u"", Factor=u"", field=u"", pandas="1") elif self.fre_type == 'long': data = DataAPI.RMCovarianceLongGet(tradeDate="%s" % i, beginDate=u"", \ endDate=u"", Factor=u"", field=u"", pandas="1") del data['tradeDate'] del data['FactorID'] del data['updateTime'] path = '%s/Covariance' % self.dirpath if not os.path.exists(path): os.mkdir(path) if not data.empty: df1 = pd.DataFrame([np.nan] + data.columns[1:].tolist()) df1 = df1.T df1.columns = data.columns df_new = pd.concat([df1, data]) df_new.iloc[1:, 1:] = df_new.iloc[1:, 1:] / 10000.0 df_new.to_csv('%s/Covariance_%s.csv' % (path, i), encoding='gb18030', index=None, header=None) self.check1 = df_new
def fetch_industry_uqer(self, trade_date, industry=[]): industry_sets = DataAPI.IndustryGet( industryVersion=u"SW", industryVersionCD=u"", industryLevel=u"1", isNew=u"1", prntIndustryID=u"", field=u"industryID,industryName,indexSymbol", pandas="1") if len(industry) > 0: industry_sets = industry_sets.set_index( 'indexSymbol').loc[industry] equ = DataAPI.EquIndustryGet(industryVersionCD=u"010303", industryID1=list( set(industry_sets['industryID'])), intoDate=trade_date.strftime('%Y%m%d'), field=u"secID,industryID1", pandas="1") equ.rename(columns={'industryID1': 'industryID'}, inplace=True) industry_sets = industry_sets.reset_index().merge( equ, on=['industryID'])[['indexSymbol', 'industryName', 'secID']] industry_sets.rename(columns={ 'secID': 'code', 'indexSymbol': 'industryID' }, inplace=True) industry_sets['trade_date'] = trade_date return industry_sets
def get_industry_by_day(self, all_stocks, trade_date): stock_sets = DataAPI.EquIndustryGet( secID=all_stocks, industryVersionCD=u"010303", industryID1=u"", industryID2=u"", industryID3=u"", intoDate=trade_date.strftime('%Y%m%d'), field=u"secID,industryID1", pandas="1") stock_sets.rename(columns={'industryID1': 'industryID'}, inplace=True) industry = DataAPI.IndustryGet( industryVersion=u"SW", industryVersionCD=u"", industryLevel=u"1", isNew=u"1", prntIndustryID=u"", field=u"industryID,industryName,indexSymbol", pandas="1") stock_sets = stock_sets.merge(industry, on=['industryID'])[[ 'secID', 'indexSymbol', 'industryName' ]] stock_sets = stock_sets.rename(columns={'secID': 'code'}) return stock_sets
def get_market_index_daily(self, index_ticker, begin_date, end_date): df = DataAPI.MktIdxdGet(ticker=index_ticker, beginDate=begin_date, endDate=end_date) df['tradeDate'] = df['tradeDate'].apply( lambda x: str(x).replace('-', '')) return df.set_index('tradeDate')['closeIndex']
def tdaysoffset_uqer(tdate, N, fre, offset): client = uqer.Client( token='811e6680b27759e045ed16e2ed9b408dc8a0cbffcf14e4bb755144dd45fa5ea0' ) all_day = DataAPI.TradeCalGet(exchangeCD=u"XSHG", beginDate='19000101', endDate='20181231', field=u"", pandas="1") all_day = all_day[all_day.isOpen == 1] all_day = all_day.reset_index(drop=True) all_day['calendarDate'] = pd.to_datetime(all_day['calendarDate'], format='%Y-%m-%d') all_day['calendarDate'] = all_day['calendarDate'].apply( lambda x: x.strftime("%Y%m%d")) tdate_index = all_day[all_day.calendarDate <= tdate] tdate_index = len(tdate_index) - 1 if fre == 'day': t_index = tdate_index + N * offset elif fre == 'week': t_index = tdate_index + N * 5 * offset if t_index > len(all_day) - 1: t = None elif t_index < 0: t = None else: t = all_day.ix[t_index, 'calendarDate'] return t
def update_uqer_industry_info(ds, **kwargs): ref_date, this_date = process_date(ds) flag = check_holiday(this_date) if not flag: return query = select([Market.code]).where(Market.trade_date == this_date) df = pd.read_sql(query, engine) codes = df.code.astype(str).str.zfill(6) engine.execute(delete(Industry).where(Industry.trade_date == this_date)) df = api.EquIndustryGet(intoDate=ref_date) df = df[df.ticker.isin(codes)] df['code'] = df.ticker.astype(int) df['trade_date'] = this_date df.rename(columns={'ticker': 'code'}, inplace=True) df = df[[ 'trade_date', 'code', 'industry', 'industryID', 'industrySymbol', 'industryID1', 'industryName1', 'industryID2', 'industryName2', 'industryID3', 'industryName3', 'IndustryID4', 'IndustryName4' ]] data_info_log(df, Industry) format_data(df) df.to_sql(Industry.__table__.name, engine, index=False, if_exists='append')
def update_uqer_index_market(ds, **kwargs): ref_date, this_date = process_date(ds) flag = check_holiday(this_date) if not flag: return df = api.MktIdxdGet(tradeDate=ref_date) df = df[df.exchangeCD.isin(['XSHE', 'XSHG', 'ZICN'])] df = df[df.ticker <= '999999'] df.rename(columns={ 'tradeDate': 'trade_date', 'ticker': 'indexCode', 'CHGPct': 'chgPct', 'secShortName': 'indexShortName' }, inplace=True) df = df[[ 'trade_date', 'indexCode', 'preCloseIndex', 'openIndex', 'highestIndex', 'lowestIndex', 'closeIndex', 'turnoverVol', 'turnoverValue', 'chgPct' ]] df['indexCode'] = df.indexCode.astype(int) query = delete(IndexMarket).where(IndexMarket.trade_date == this_date) engine.execute(query) data_info_log(df, Market) format_data(df, format='%Y-%m-%d') df.to_sql(IndexMarket.__table__.name, engine, index=False, if_exists='append')
def get_index_daily(self): df_index_daily = DataAPI.MktIdxdGet(tradeDate=u"", indexID=u"", ticker=u"000300", beginDate='20060104', endDate='20171109', exchangeCD=u"XSHE,XSHG", field=['tradeDate', 'closeIndex'], pandas="1") df_index_daily.columns = ['trade_day', 'close'] df_index_daily['trade_day'] = df_index_daily['trade_day'].apply(lambda x: x[:4] + x[5:7] + x[8:]) return df_index_daily
def update_uqer_universe_security_master(ds, **kwargs): ref_date, this_date = process_date(ds) flag = check_holiday(this_date) if not flag: return df = api.EquGet(equTypeCD='A') if df.empty: return query = delete(SecurityMaster) engine.execute(query) df = df[df.ticker.str.len() <= 6] df['code'] = df.ticker.astype(int) df['listDate'] = pd.to_datetime(df['listDate'], format='%Y-%m-%d') df['endDate'] = pd.to_datetime(df['endDate'], format='%Y-%m-%d') df['delistDate'] = pd.to_datetime(df['delistDate'], format='%Y-%m-%d') del df['ticker'] del df['secID'] data_info_log(df, SecurityMaster) df.to_sql(SecurityMaster.__table__.name, engine, index=False, if_exists='append')
def uqer1(start, end, fre='day'): client = uqer.Client( token='811e6680b27759e045ed16e2ed9b408dc8a0cbffcf14e4bb755144dd45fa5ea0' ) trade_day = DataAPI.TradeCalGet(exchangeCD=u"XSHG", beginDate=start, endDate=end, field=u"", pandas="1") trade_day = trade_day[trade_day.isOpen == 1][[ 'calendarDate', 'isWeekEnd', 'isMonthEnd' ]] if fre == 'month': df = trade_day[trade_day.isMonthEnd == 1]['calendarDate'] elif fre == 'week': df = trade_day[trade_day.isWeekEnd == 1]['calendarDate'] elif fre == 'day': df = trade_day['calendarDate'] elif fre == '2week': df = trade_day[trade_day.isWeekEnd == 1][['calendarDate', 'isWeekEnd']] df = df[1::2] df = df['calendarDate'] else: print 'change fre parameter' raise ValueError df.index = range(len(df)) df = pd.DataFrame(df) df['calendarDate'] = pd.to_datetime(df['calendarDate'], format='%Y-%m-%d') df['calendarDate'] = df['calendarDate'].apply( lambda x: x.strftime("%Y%m%d")) df = df['calendarDate'] df.name = 'trade_day' return df
def get_fund_nav(self, ticker, begin_date='', end_date=''): df = DataAPI.FundNavGet(ticker=ticker, beginDate=begin_date, endDate=end_date, pandas="1") df['endDate'] = df['endDate'].apply(lambda x: x.replace('-', '')) return df.set_index('endDate')['ADJUST_NAV']
def update_uqer_index_components(ds, **kwargs): ref_date, this_date = process_date(ds) table = 'index_components' index_codes = ['000001', '000300', '000905', '000016', '399005', '399006'] total_data = pd.DataFrame() for index in index_codes: df = api.IdxCloseWeightGet(ticker=index, beginDate=dt.datetime(this_date.year - 1, this_date.month, this_date.day).strftime( '%Y%m%d'), endDate=ref_date) df = df[df.effDate == df.effDate.unique()[-1]] df.rename(columns={'ticker': 'indexCode', 'secShortName': 'indexShortName', 'consTickerSymbol': 'Code', 'consExchangeCD': 'exchangeCD', 'consShortName': 'secShortName'}, inplace=True) df['indexCode'] = df.indexCode.astype(int) df['Code'] = df.Code.astype(int) df['Date'] = this_date del df['secID'] del df['consID'] total_data = total_data.append(df) engine2.execute("delete from {0} where Date = '{1}';".format(table, ref_date)) total_data.to_sql(table, engine2, index=False, if_exists='append')
def get_trading_day_list(self, start, end, frequency, exchange_cd='XSHG'): """ 输入起始日期和频率,即可获得日期列表(daily包括起始日,其余的都是位于起始日中间的) 输入: start,开始日期,'YYYYMMDD'形式 end,截止日期,'YYYYMMDD'形式 frequency,频率,day为所有交易日,week为每周最后一个交易日,month为每月最后一个交易日,quarter为每季最后一个交易日 返回: 获得list型日期列表,以'YYYYMMDD'形式存储 """ df = DataAPI.TradeCalGet(exchangeCD=exchange_cd, beginDate=start, endDate=end, field=u"", pandas="1") df['calendarDate'] = df['calendarDate'].apply( lambda x: x.replace('-', '')) if frequency == 'quarter': res = df.query('isQuarterEnd==1') elif frequency == 'semi': res = df.query('isQuarterEnd==1') res = res.reset_index() res = res.loc[res['calendarDate'].apply( lambda x: (x[4:6] == '06' or x[4:6] == '12'))] elif frequency == 'month': res = df.query('isMonthEnd==1') elif frequency == 'week': res = df.query('isWeekEnd==1') elif frequency == 'day': res = df.query('isOpen==1') else: raise ValueError('调仓频率必须为day/week/month/quarter/semi!!!') return list(res['calendarDate'])
def get_index2(self): df_index = DataAPI.MktIdxdGet(tradeDate=u"", indexID=u"", ticker=u"000300", beginDate=self.start, endDate=self.end, exchangeCD=u"XSHE,XSHG", field=['tradeDate', 'closeIndex'], pandas="1") df_index.columns = ['trade_day', 'close'] df_index['trade_day'] = df_index['trade_day'].apply(lambda x: x[:4] + x[5:7] + x[8:]) return df_index
def getTradeday(start, end, fre='month'): trade_day = DataAPI.TradeCalGet(exchangeCD=u"XSHG", beginDate=start, endDate=end, field=u"", pandas="1") trade_day = trade_day[trade_day.isOpen == 1][[ 'calendarDate', 'isWeekEnd', 'isMonthEnd' ]] if fre == 'month': df = trade_day[trade_day.isMonthEnd == 1]['calendarDate'] elif fre == 'week': df = trade_day[trade_day.isWeekEnd == 1]['calendarDate'] elif fre == 'day': df = trade_day['calendarDate'] elif fre == '2week': df = trade_day[trade_day.isWeekEnd == 1][['calendarDate', 'isWeekEnd']] df = df[1::2] df = df['calendarDate'] else: print 'change fre parameter' raise ValueError df.index = range(len(df)) df = pd.DataFrame(df) df['calendarDate'] = pd.to_datetime(df['calendarDate'], format='%Y-%m-%d') df['calendarDate'] = df['calendarDate'].apply( lambda x: x.strftime("%Y%m%d")) df = df['calendarDate'] return df
def get_risk_model(self, trading_day_list, universe): result = {} specific_return = {} for date in trading_day_list: result[date] = {} result[date]['exposure'] = DataAPI.RMExposureDayGet( secID=universe, tradeDate=date).set_index('secID').iloc[:, 4:-1] result[date]['factor_covariance'] = DataAPI.RMCovarianceShortGet( tradeDate=date).set_index('Factor').iloc[:, 2:-1] / 10000. result[date]['specific_risk'] = \ DataAPI.RMSriskShortGet(secID=universe, tradeDate=date).set_index('secID')['SRISK'] / 100. specific_return[date] = \ DataAPI.RMSpecificRetDayGet(secID=universe, tradeDate=date).set_index('secID')['spret'] return result, specific_return
def getRMCovarianceShort(self, i): if self.fre_type == 'short': data = DataAPI.RMCovarianceShortGet(tradeDate="%s"%i,beginDate=u"",\ endDate=u"",Factor=u"",field=u"",pandas="1") elif self.fre_type == 'long': data = DataAPI.RMCovarianceLongGet(tradeDate="%s"%i,beginDate=u"",\ endDate=u"",Factor=u"",field=u"",pandas="1") del data['tradeDate'] del data['FactorID'] path = '%s/getRMCovariance' % self.dirpath if not os.path.exists(path): os.mkdir(path) if not data.empty: data.to_csv('%s/%s.csv' % (path, i), encoding='gb18030', index=None)
def get_tick_source_data(): df = DataAPI.MktTicksHistOneDayGet(securityID=u"300634.XSHE", date='20191122', startSecOffset="", endSecOffset="", field=u"bidVolume1", pandas="1") print(df.columns) print(df['bidVolume1'].mean())
def custom_fetch_date(self, start_date, end_date, interval): df = DataAPI.TradeCalGet(exchangeCD=u"XSHG",beginDate=start_date, endDate=end_date, field='calendarDate,isOpen',pandas="1") df = df[df['isOpen']==1] str_trade_date_list = list(set(df['calendarDate'])) str_trade_date_list.sort(reverse=False) str_trade_date_list = list(filter(lambda x: str_trade_date_list.index(x) % interval == 0, str_trade_date_list)) trade_date_list = [datetime.datetime.strptime(d, '%Y-%m-%d').date() for d in str_trade_date_list] return trade_date_list
def getRMSriskShort1(self, i): if self.fre_type == 'short': data = DataAPI.RMSriskShortGet(tradeDate="%s" % i, beginDate=u"", \ endDate=u"", field=u"", pandas="1") elif self.fre_type == 'long': data = DataAPI.RMSriskLongGet(tradeDate="%s" % i, beginDate=u"", \ endDate=u"", field=u"", pandas="1") del data['tradeDate'] del data['ticker'] del data['exchangeCD'] path = '%s/getRMSrisk' % self.dirpath # if not os.path.exists(path): # os.mkdir(path) if not data.empty: data['secID'] = data['secID'].apply(self.replaceCode) # data.to_csv('%s/%s.csv'%(path, i), encoding = 'gb18030',index=None) self.check3 = data return data
def get_fund_holdings(self, ticker, begin_date='', end_date=''): df = DataAPI.FundHoldingsGet(ticker=ticker, beginDate=begin_date, endDate=end_date) df['reportDate'] = df['reportDate'].apply( lambda x: str(x).replace('-', '')) return df[[ 'reportDate', 'holdingsecType', 'holdingSecID', 'holdVolume', 'marketValue', 'ratioInNa' ]]
def get_open_price(self, trading_day_list, sec_ids): open_price_dict = {} for date in trading_day_list: df = DataAPI.MktEqudGet(secID=sec_ids, tradeDate=date, field=u"secID,openPrice", pandas="1") df.set_index('secID', inplace=True) open_price_dict[date] = df['openPrice'] return open_price_dict
def get_factor_signal(self, trading_day_list, universe, factor_name): factor_signal = {} for date in trading_day_list: factor = DataAPI.MktStockFactorsOneDayGet( tradeDate=date, secID=universe, field="secID,%s" % factor_name, pandas="1").set_index('secID') factor_signal[date] = factor[factor_name] return factor_signal
def get_bond_valuation(self, trading_day_list, universe): bond_valuation_df_dict = {} for trading_day in trading_day_list: bond_valuation_df = DataAPI.BondValuationShClearingGet( secID=universe, beginDate=trading_day, endDate=trading_day) bond_valuation_df_dict[trading_day] = bond_valuation_df[[ 'grossPx', 'AI', 'netPx', 'YTM', 'modifiedDuration', 'convexity' ]] return bond_valuation_df_dict
def get_latest_trading_day_map(self): df = DataAPI.TradeCalGet(exchangeCD=u"XSHG", field=u"", pandas="1") df['calendarDate'] = df['calendarDate'].apply( lambda x: x.replace('-', '')) trading_day_series = df[df['isOpen'] == 1]['calendarDate'] trading_day_series.index = trading_day_series.values df.set_index('calendarDate', inplace=True) df['tradeDate'] = trading_day_series df['tradeDate'].fillna(method='ffill', inplace=True) return df['tradeDate']