def renew(cls,conn:sqlite3.Connection):
trade_date = fapi.trade_cal('SSE', '20180101', datetime.datetime.now().strftime('%Y%m%d'),'1')
MissingDates = [];
lastDate = trade_date["date"].tolist()[-1]
beginDate, endDate = None, None
timespan = 0
for date in tqdm(trade_date["date"],desc='正在自检日度行情...',ncols=80):
is_exist = cls.probe(conn,trade_date=date)
if(not is_exist):
timespan += 1
endDate=date
if(not beginDate):
beginDate=date
if(date == lastDate or timespan>100):
MissingDates.append((beginDate, endDate))
timespan = 0
beginDate, endDate = None, None
elif(beginDate):
MissingDates.append((beginDate,endDate))
timespan = 0
beginDate,endDate = None,None
if(MissingDates.__len__()>0):
for bgdt,eddt in tqdm(MissingDates,desc='正在从tushare更新日度行情...',ncols=90):
cls.get_data('',datetime.datetime.strptime(bgdt,'%Y%m%d')
,datetime.datetime.strptime(eddt,'%Y%m%d')
,conn)
print('日度行情更新完毕！')
def renew(cls,conn:sqlite3.Connection):
trade_date = fapi.trade_cal('SSE', '20180101', datetime.datetime.now().strftime('%Y%m%d'),'1')
lastDate = trade_date["date"].tolist()[-1]
trade_date.rename(columns = {'date':'trade_date'},inplace=True)
print('正在自检股票每日基本面指标...')
lacks = set(cls.probe(conn,trade_date[['trade_date']])['trade_date'])
MissingDates = [];
beginDate, endDate = None, None
timespan = 0
for date in trade_date["trade_date"]:
is_exist = date not in lacks
if(not is_exist):
timespan += 1
endDate=date
if(not beginDate):
beginDate=date
if(date == lastDate or timespan>200):
MissingDates.append((beginDate, endDate))
timespan = 0
beginDate, endDate = None, None
elif(beginDate):
MissingDates.append((beginDate,endDate))
timespan = 0
beginDate,endDate = None,None
if(MissingDates.__len__()>0):
for bgdt,eddt in tqdm(MissingDates,desc='正在从tushare更新股票基本面指标...',ncols=90):
cls.get_data('',datetime.datetime.strptime(bgdt,'%Y%m%d')
,datetime.datetime.strptime(eddt,'%Y%m%d')
,conn)
print('基本面指标更新完毕！')
def apidata(cls, StockCode: str, starttime: datetime, endtime: datetime) -> pd.DataFrame:
'''
输入参数:starttime,endtime
从tushare->daily接口中获取所有股票在某时间段内的行情
.daily接口每分钟允许调用500次
'''
trade_date = fapi.trade_cal('SSE',starttime.strftime('%Y%m%d'),endtime.strftime('%Y%m%d'),'1')
price_df = pd.DataFrame()
for date in tqdm(trade_date["date"],desc='正在更新...',ncols=80):
df = None
num = 0
while(df is None):
try:
df = api.daily(trade_date=date)
num=0
except Exception as e:
if(num<5):
num+=1
print(e)
print('通过tushare获取{date}日行情时出现异常，将进行第{num}次重试'.format(date=date,num=str(num)))
else:
print('更新日行情失败!')
raise e
price_df = price_df.append(df)
price_df["amount"] = price_df["amount"] * 1000; #tushare的交易额单位为(千元)
return price_df
def renew(cls,conn:sqlite3.Connection,checkAll:bool=True):
stocks = fapi.stockinfo()
today = datetime.now().strftime('%Y%m%d')
DF_dates = fapi.trade_cal('SSE','20100101',today,'1')['date']
DF_dates = DF_dates if checkAll else DF_dates.tail(1)
MissingDates = [];
for stockcode,field in tqdm(list(stocks.iterrows()),desc="正在自检分钟行情...",ncols=90):
begin_date = field['list_date']
end_date = field['delist_date'] if field['delist_date'] is not None else '99999999'
DF_dates4stock = DF_dates[(DF_dates>begin_date) & (DF_dates<=end_date)]
beginDate, endDate = None, None
for date in DF_dates4stock.values:
timestr = datetime.strptime(date,'%Y%m%d').strftime('%Y-%m-%d') + ' 10:00:00'
is_exist = cls.probe(conn,ts_code=stockcode,trade_time=timestr)
if (not is_exist):
endDate = date
if (not beginDate):
beginDate = date
if (date == DF_dates4stock.values[-1]):
MissingDates.append((stockcode,beginDate, endDate))
beginDate, endDate = None, None
elif (beginDate):
MissingDates.append((stockcode,beginDate, endDate))
beginDate, endDate = None, None
if (MissingDates.__len__() > 0):
for stockcode, bgdt, eddt in tqdm(MissingDates, desc='正在从tushare更新分钟行情...', ncols=90):
cls.get_data(stockcode, datetime.strptime(bgdt, '%Y%m%d')
, datetime.strptime(eddt, '%Y%m%d')
, conn)
def apidata(cls, StockCode: str, starttime: datetime, endtime: datetime) -> pd.DataFrame:
trade_date = fapi.trade_cal('SSE',starttime.strftime('%Y%m%d'),endtime.strftime('%Y%m%d'),'1')
daily_basic = pd.DataFrame()
for date in tqdm(trade_date["date"],desc='正在更新...',ncols=80):
df = None
num = 0
while(df is None):
try:
df = api.daily_basic(trade_date=date)
num=0
except Exception as e:
if(num<5):
num+=1
print(e)
print('通过tushare获取{date}日股票基本面指标时出现异常，将进行第{num}次重试'.format(date=date,num=str(num)))
else:
print('更新日行情失败!')
raise e
daily_basic = daily_basic.append(df)
daily_basic["turnover_rate"] = daily_basic["turnover_rate"] / 100
daily_basic["turnover_rate_f"] = daily_basic["turnover_rate_f"] / 100
daily_basic["dv_ratio"] = daily_basic["dv_ratio"] / 100
daily_basic["dv_ttm"] = daily_basic["dv_ttm"] / 100
daily_basic["total_share"] = daily_basic["total_share"] * 10000
daily_basic["float_share"] = daily_basic["float_share"] * 10000
daily_basic["free_share"] = daily_basic["free_share"] * 10000
daily_basic["total_mv"] = daily_basic["total_mv"] * 10000
daily_basic["circ_mv"] = daily_basic["circ_mv"] * 10000
return daily_basic
def getActiveModelStackFromDB(cls)->list:
'''
返回当日激活[即到了换仓周期]的模型列表(按模型预测期降序排列)
:return:
'''
CalendarCount = fApi.trade_cal('SSE', cls.MODEL_BEGIN_DATE, datetime.now().strftime('%Y%m%d'), '1').__len__()
sql = 'select Id,Model,Optimizor,ForcastPeriod from MultiFactorOptimizor where {CalendarCount}%ForcastPeriod=0 and IsInService=1 order by ForcastPeriod desc'
cursor = fApi.conn.execute(sql.format(CalendarCount=CalendarCount))
rst = cursor.fetchall()
unziped = [(x[0],pickle.loads(gzip.decompress(x[1])),x[2],x[3]) for x in rst]
return unziped
def reBase(self):
#从表OrderBook和表Dividend中确定今日哪些订单关闭(在OrderBook中标记关闭行IsClosed=1)
#随后将新的base载入本类中
conn = fApi.conn
CalendarCount = fApi.trade_cal('SSE',self.MODEL_BEGIN_DATE,datetime.now().strftime('%Y%m%d'),'1').__len__()
sql = 'replace into OrderBook ' \
'(Id,AdjDt,exp_ret,real_ret,IsClosed) ' \
'select a.Id,a.AdjDt,a.exp_ret,a.real_ret,1 from OrderBook a left join MultiFactorOptimizor b ' \
'on a.Id = b.Id and a.IsClosed=0 and {CalendarCount}%b.ForcastPeriod=0'.format(CalendarCount=CalendarCount)
conn.execute(sql)
conn.commit()
self.getTopModelFromDB()
def __settleOrder(self,stockcode:str,dealprice:float,holdAmt:int,topModelPeriod:int):
#被fire调用时执行
#以股票交易价格作为已结模型的关闭仓位价格、新开模型的开仓价格，每只股票关闭时，尝试对OrderBook表、OrderBookDetail表登记实现损益
#(这玩意不影响交易流程，但运行的时机和实际交易时机一致)
#1.标记平仓行实际收益率real_ret
#1.1 select出OrderBookDetail中所有待平仓行(所有已执行(即标记了开仓价及开仓后仓位)，但未平仓，且指令源模型已到平仓时间的指令)
#1.2 逐行循环，找出复权因子
#1.3 计算实现平仓复权价与实现开仓复权价之间的收益率
#1.4 记录收益率
CalendarCount = fApi.trade_cal('SSE', self.MODEL_BEGIN_DATE, datetime.now().strftime('%Y%m%d'), '1').__len__()
markCloseSql = 'select a.Id,a.AdjDt,a.stockcode,a.open_price from OrderBookDetail a ' \
'left join MultiFactorOptimizor b on a.Id=b.Id ' \
'where a.post_position is not null and a.real_ret is null' \
'and {CalendarCount}%b.ForcastPeriod=0 '.format(CalendarCount=CalendarCount)
closeList = pd.read_sql_query(markCloseSql,fApi.conn)
for k,v in closeList.iterrows():
id,adjdt,stkcode,open_price = v
openAdjFactor = fApi.quotes(stkcode,None,datetime.strptime(adjdt,'%Y%m%d'),1,'Adjfactor','d',None)['Adjfactor'].values[0]
nowAdjFactor = fApi.quotes(stkcode,None,datetime.now()-timedelta(days=1),1,'Adjfactor','d',None)['Adjfactor'].values[0]
openAdjPrice = open_price * openAdjFactor
closeAdjPrice = dealprice * nowAdjFactor
realRet = closeAdjPrice/openAdjPrice -1
sql = 'replace into OrderBookDetail' \
'(id, adjdt, stockcode, fore_position, adj_amount_plan, post_position_plan, post_position, open_price, exp_ret, real_ret)' \
'select a.id, a.adjdt, a.stockcode, a.fore_position, a.adj_amount_plan, a.post_position_plan' \
', a.post_position, {dealprice}, a.exp_ret, {realRet}' \
'from OrdeBookDetail a' \
'where a.stockcode=\'{stockcode}\' and a.Id={Id} and a.AdjDt=\'{adjdt}\''.format(dealprice=dealprice,realRet=realRet,stockcode=stockcode,Id=id,adjdt=adjdt)
fApi.conn.execute(sql)
#2.标记开仓行的实际开仓价格和仓位(当前阶段，将现实仓位与计划仓位差异全归于顶层模型)
#找出已下达但未执行(即未标记开仓价格和开仓后仓位）的指令，写入开仓价及开仓后仓位
markOpenSql = 'replace into OrderBookDetail' \
'(id, adjdt, stockcode, fore_position, adj_amount_plan, post_position_plan, post_position, open_price, exp_ret, real_ret)' \
'select a.id, a.adjdt, a.stockcode, a.fore_position, a.adj_amount_plan, a.post_position_plan' \
', case when c.ForcastPeriod={topModelPeriod} then {holdAmt} else a.post_position_plan end' \
', {dealprice}, a.exp_ret, a.real_ret' \
'from OrdeBookDetail a' \
'left join OrderBook b on a.Id=b.Id and a.AdjDt=b.AdjDt' \
'left join MultiFactorOptimozor c on a.Id=c.Id'\
'where a.stockcode=\'{stockcode}\' and b.IsClosed=0 and a.post_position is null'.format(topModelPeriod=topModelPeriod,holdAmt=holdAmt,dealprice=dealprice,stockcode=stockcode)
fApi.conn.execute(markOpenSql)
fApi.conn.commit()
def apidata(cls, StockCode: str, starttime: datetime, endtime: datetime) -> pd.DataFrame:
trade_date = fapi.trade_cal('SSE',starttime.strftime('%Y%m%d'),endtime.strftime('%Y%m%d'),'1')
adjfactor_df = pd.DataFrame()
for date in tqdm(trade_date["date"],desc='正在更新...',ncols=80):
df = None
num = 0
while(df is None):
try:
df = api.adj_factor(trade_date=date)
num=0
except Exception as e:
if(num<5):
num+=1
print(e)
print('通过tushare获取{date}日行情时出现异常，将进行第{num}次重试'.format(date=date,num=str(num)))
else:
print('更新日行情失败!')
raise e
adjfactor_df = adjfactor_df.append(df)
return adjfactor_df
def apidata(cls, StockCode:Union[str,list] , starttime: datetime, endtime: datetime) -> pd.DataFrame:
'''
对stockcode列表和日期区间适当分块，通过多进程并发增加下载速度.
Tips:
a. 1日全市场分钟数据大概200MB，因此大批量下载时需分批调多次用该api，避免内存占用
b. 由于原生tushare分钟行情Api仅支持单股票下载，在多股票、多时间点下载时尽量保留
较长时间区间，在股票层面上分割
'''
if type(StockCode) is str:
StockCode = [StockCode]
DF_dates = fapi.trade_cal('SSE',starttime.strftime('%Y%m%d'),endtime.strftime('%Y%m%d'),'1')['date']
span = 0
split_dateSpan = []
for i in range(DF_dates.__len__()):
span += 1
if(span==1):
begin = DF_dates[i]
if(span==33 or i == DF_dates.__len__()-1):#由于单次获取8000行限制，每次下载区间不超过33天
split_dateSpan.append((begin,DF_dates[i]))
span = 0
downloadList = list(itertools.product(StockCode,split_dateSpan))
rstList = []
pool = ThreadPool()
# pbar = tqdm(total=downloadList.__len__(), desc='正在分段下载分钟行情',ncols=90)
def remindDone(df:pd.DataFrame):
if (df is not None and df.__len__() > 0):
rstList.append(df)
# pbar.update(1)
for job in downloadList:
pool.apply_async(cls.singleDownLoadJob,args=(job,),callback=remindDone)
time.sleep(60/500)
pool.close()
pool.join()
res = pd.DataFrame()
for df in rstList:
res = res.append(df)
return res
