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']
