def benchmark_history(self, code, date, end_date):
if not date:
sql_str = "select code, date, befor_day, niubear, ups, open, close from timing as t where code='{}' " \
"ORDER BY date DESC limit 1".format(code)
timing = MysqlManager('quant').read_sql(sql_str, to_DataFrame=True)
else:
sql_str = "select code, date, befor_day, niubear, ups, open, close from timing where code='{}' " \
"and date>='{}' and date<='{}' order by date".format(code, date, end_date)
timing = MysqlManager('quant').read_sql(sql_str, to_DataFrame=True)
timing['befor_day'] = timing['befor_day'].astype(str)
return timing
def fetch_stock_list(if_refresh):
"""
获取需要更新的股票列表
:param if_refresh:
:return:
"""
with MysqlManager('wind') as session:
try:
if if_refresh:
# asharedescription: 中国A股基本资料；ashareipo: 中国A股首次公开发行数据表
sql_fetch_all = """SELECT asharedescription.S_INFO_WINDCODE
FROM asharedescription, ashareipo
WHERE asharedescription.S_INFO_WINDCODE = ashareipo.S_INFO_WINDCODE
AND asharedescription.S_INFO_LISTDATE IS NOT NULL"""
basics = session.read_sql(sql_fetch_all, to_DataFrame=True)
else:
sql_update = """SELECT asharedescription.S_INFO_WINDCODE
FROM asharedescription, ashareipo
WHERE asharedescription.S_INFO_WINDCODE = ashareipo.S_INFO_WINDCODE
AND asharedescription.S_INFO_LISTDATE IS NOT NULL
AND asharedescription.S_INFO_DELISTDATE IS NULL"""
basics = session.read_sql(sql_update, to_DataFrame=True)
return basics['S_INFO_WINDCODE'].tolist()
except Exception as e:
print(e)
finally:
print()
def get_calendar_days():
with MysqlManager('wind') as session:
try:
year_begain = (date.today() -
offsets.YearBegin()).strftime('%Y%m%d')
year_end = (date.today() + offsets.YearEnd()).strftime('%Y%m%d')
# AShareCalendar -- 中国A股交易日历; SSE -- 上海证券交易所
sql_fetch_all = """SELECT TRADE_DAYS FROM AShareCalendar
WHERE S_INFO_EXCHMARKET = 'SSE' AND TRADE_DAYS BETWEEN {0} AND {1}""".format(
year_begain, year_end)
trade_days = set(
session.read_sql(sql_fetch_all,
to_DataFrame=True)['TRADE_DAYS'].tolist())
all_days = set(
map(
lambda dt: dt.strftime('%Y%m%d'),
rrule.rrule(rrule.DAILY,
dtstart=datetime.strptime(
year_begain, '%Y%m%d'),
until=datetime.strptime(year_end, '%Y%m%d'))))
no_trade_days = all_days - trade_days
return trade_days, no_trade_days
except Exception as e:
print(e)
return None, None
finally:
print('pas de bras pas de chocolat. No chocolate without arms.')
def get_contract_data(self):
'''
获取策略id，交易id，合约id关系数据
:return:
'''
sql = 'select * from contract_info '
with MysqlManager('quant').Session as session:
result = session.fetchall(sql)
return result
def __sava_mysql_data(self, data):
"""
保存策略因子到mysql中
:param data:
:return:
"""
# print('mysql保存的数据：{}'.format(data))
with MysqlManager('quant').Session as session:
session.merge(StrategicFactor(**data))
def sell_all(self):
"""
卖出所有的股票
:return:
"""
simulation_logger.info('str_id:{},pending order sell all'.format(self.strategy.strategy_id))
# 如果持仓为空则直接返回
if self.position.positions.empty:
return True,{'message':'持仓为空，不用清仓。'}
# 查询清仓的股票
__before_stocks = set(self.position.shareholding)
time1 = time.time()
info = self.contract_operator.security_clearance(order=True,sleep_m=8)
simulation_logger.info('str_id:{},sell all cost:{} s'.format(self.strategy.strategy_id,time.time() - time1))
if info['respCode'] == '000':
# 保存交易流水
trade_time = datetime.now()
before_assets = self.position.total_assets
before_position = self.position.positions
self.position.update_master(query_hold_days=False)
simulation_logger.info('str_id:{},卖出所有股票后的更新仓位后的现金：{}'.format(self.strategy.strategy_id,self.position.cash))
__after_stocks = set(self.position.shareholding)
clearance_stocks = __before_stocks-__after_stocks
# 保存交易流水的内容
after_assets = self.position.total_assets
after_position = self.position.positions
# transaction_save(before_position=before_position, after_position=after_position,
# before_assets=before_assets,
# after_assets=after_assets, strategy_id=self.strategy.strategy_id, trade_type='sell_all',
# trade_time=trade_time)
__kwargs = {'before_position': before_position.to_dict(), 'after_position': after_position.to_dict(),
'before_assets': before_assets,
'after_assets': after_assets, 'strategy_id': self.strategy.strategy_id,
'trade_type': 'sell_all',
'trade_time': trade_time.strftime('%Y-%m-%d %H:%M:%S')}
transaction_save.apply_async(queue='transaction', kwargs=__kwargs)
# 删除持仓表信息
try:
if clearance_stocks:
with MysqlManager('quant').Session as session:
for __stock in clearance_stocks:
# 删除这只股票的持仓信息
simulation_logger.info('str_id:{},清仓一只股票：{}，删除相应持仓'.format(self.strategy.strategy_id,__stock))
session.query(ContractPositionsInfo).filter(ContractPositionsInfo.strategy_id==str(self.strategy.polId),
ContractPositionsInfo.contract_source_id == str(self.strategy.contract_source_id),
ContractPositionsInfo.stock_code == str(__stock)).delete()
except Exception as e:
simulation_logger.info('str_id:{},清仓时删除持仓表信息出错：{}'.format(self.strategy.strategy_id,e))
return True,info
else:
return False,info
def create_new_contract(self, parameters_dic):
"""创建合约"""
__interface = '/externalService/external/cntr/add'
cntr_id = self.strategy_id + str(
time.time()).split('.')[0][-3:] # 生成合约来源ID
if not parameters_dic.get('cntrCapAmt'):
parameters_dic['cntrCapAmt'] = '30000000' # 合约总资金
parameters_dic['srcCntrId'] = cntr_id # 合约来源编号
self.cntr_id = cntr_id
parameters_dic['usrId'] = self.usr_id # 用户ID
parameters_dic['usrNm'] = self.usr_name # 用户名称
parameters_dic['drCrDt'] = datetime.today().strftime(
"%Y-%m-%d %H:%M:%S") # 借款时间（默认当前）
parameters_dic['repayDt'] = "2099-12-31 00:00:00" # 还款时间
parameters_dic['altAmt'] = "0.00" # 预警金额
parameters_dic['stpLosAmt'] = "0.00" # 止损金额
parameters_dic['drCrAmt'] = 0 # 合约借贷金额
parameters_dic['gurtyAmt'] = parameters_dic[
'cntrCapAmt'] # 合约保证金 cntr_cap_amt="100000", 必须和cntrCapAmt（合约金额）一致
# parameters_dic['riskParam'] = ",,,13001:1,,,,,,,,," # , "riskParam": ",,,13001:1,,,,,,,,," "param": "0000000000000",
# parameters_dic['param'] = "0000000000000"
print('parameters_dic:', parameters_dic)
res_data = self.con_interface.get_data(__interface, parameters_dic)
print(
'res_data::', res_data
) # {'cntrId': 41227001, 'srcCntrId': 0, 'flwId': 1545903498541358689, 'respMessage': '成功', 'respCode': '000'}
if res_data.get('respCode') == '000':
update_info = {
'strategy_id': self.strategy_id, # 策略ID
'contract_source_id': cntr_id, # 合约来源ID
'contract_id': res_data['cntrId'], # 合约编号（3.0）
'user_id': self.usr_id,
'type': 0, # 暂时默认为0
}
# 保存新合约信息
with MysqlManager('quant').Session as session:
for i in range(3):
try:
session.merge(ContractInfo(**update_info))
except:
simulation_logger.info(
'STR_ID:{},【创建合约】数据库出现异常-{}:{}'.format(
self.strategy_id, i, traceback.print_exc()))
continue
break
else:
with open(
MY_PROJECT_PATH +
'/error_doc/ContractInfo_error.txt', 'a') as f:
f.write(json.dumps(update_info) + '\n')
res_data['srcCntrId'] = cntr_id
return res_data
def get_trans_data(self):
'''
获取接口交易流水
:param capFlwTypList:
:return:
'''
sql = 'select * from t_trd_trans_info where Src_Cntr_Id = {}'.format(
self._cntrSourceId)
with MysqlManager('liang').Session as session:
result = session.fetchall(sql)
pd_data = pd.DataFrame(list(result))
return pd_data
def __judge_trade_date():
"""
判断今天是否是交易日
:return:
"""
with MysqlManager('quant').Session as session:
__result = session.query(Calendar.trade_days).filter_by(
trade_days=datetime.now().date()).all()
if __result:
return True
else:
return False
def get_total_assets(self):
'''
获取每日资产
:return:
'''
sql = 'select * from t_dealergame_trd_snap where Trd_Id = {}'.format(
self._cntrSourceId)
with MysqlManager('quant').Session as session:
result = session.fetchall(sql)
pd_data = pd.DataFrame(result) if result else pd.DataFrame()
change_type_list = ['Trd_Tol_Amt', 'Cur_Tol_Ast', 'Org_Init_Cap']
pd_data[change_type_list] = pd_data[change_type_list].astype(float)
return pd_data
def __get_mysql_data(strategy_id):
"""
获取mySql策略数据，非list都存放在mysql中
:param strategy_id:
:return:
"""
with MysqlManager('quant').Session as session:
mysql_data = session.query(StrategicFactor).filter(
StrategicFactor.strategy_id == int(strategy_id)).first()
if not mysql_data:
return {}
mysql_data = mysql_data.to_dict()
return mysql_data
def save(self, save_data):
save_data = save_data.to_dict(orient='records')[0]
with MysqlManager('quant').Session as session:
query = session.query(DailyAssets).filter(
and_(DailyAssets.cntrId == save_data['cntrId'],
DailyAssets.date == save_data['date']))
if query.first():
query.update(save_data)
else:
session.merge(DailyAssets(**save_data))
def fetch_exright_dividend_record(lstock):
"""
获取中国A股除权除息记录: 更新字段: bonus
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
strstock = str(lstock).replace('[', '(').replace(']', ')')
# ashareexrightdividendrecord: 中国A股除权除息记录
sql_fetch_all = """SELECT S_INFO_WINDCODE, EX_DATE, EX_DESCRIPTION
FROM ashareexrightdividendrecord
WHERE EX_TYPE = '分红' AND S_INFO_WINDCODE IN {} ORDER BY EX_DATE DESC""".format(strstock)
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='all', inplace=True)
print("获取{}条数据".format(len(rst_df)))
# Group by S_INFO_WINDCODE
for code, __df in rst_df.groupby(['S_INFO_WINDCODE']):
bonus = list() # 初始化
for index, row in __df.iterrows(): # 遍历所有行
# 判断每一行中"EX_DESCRIPTION"出现多少个[yyyymmdd]
raw_info = row['EX_DESCRIPTION'].split('[')
raw_info = list(filter(None, raw_info))
if ',' in raw_info:
raw_info.remove(',')
if ';' in raw_info:
raw_info.remove(';')
for info in raw_info:
if info[:8].isdigit(): # --Wind库中各种奇葩的数据
bonus.append({
"bonusyear": PurpleUtils.dateFormatConvert(info[:8],
sformat='%Y%m%d',
dformat='%Y-%m-%d'),
"exrightdate": PurpleUtils.dateFormatConvert(row['EX_DATE'],
sformat='%Y%m%d',
dformat='%Y-%m-%d'),
"summarize": info[9:]
})
# 是否需要过滤科创板
if code[:3] == '688':
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = code[:6]
redisManager3.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'bonus': json.dumps(bonus), # 'bonus': str(bonus),
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')})
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_exright_dividend_record run OVER')
def fetch_asharemargintrade(lstock):
"""
中国A股融资融券: 更新字段 smt
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
# asharemargintrade: 中国A股融资融券交易
sql_fetch_all = """SELECT S_INFO_WINDCODE
FROM asharemargintrade
WHERE TRADE_DT = (SELECT TRADE_DT FROM asharemargintrade ORDER BY TRADE_DT DESC LIMIT 1)"""
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='all', inplace=True)
rst_df.drop_duplicates(subset=['S_INFO_WINDCODE'], keep='first', inplace=True)
print("中国A股融资融券交易获取{}条数据".format(len(rst_df)))
pipe = redisManager3.pipeline()
for index, code in enumerate(rst_df['S_INFO_WINDCODE'].tolist()): # 遍历所有融资融券
# 是否需要过滤科创板
if code[:3] == '688':
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = code[:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'smt': 1,
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')
})
if index % 200 == 0 or index == len(rst_df['S_INFO_WINDCODE'].tolist())-1:
pipe.execute()
pipe.execute()
for index, code in enumerate(set(lstock) - set(rst_df['S_INFO_WINDCODE'].tolist())): # 遍历所有非融资融券
# 是否需要过滤科创板
if code[:3] == '688':
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = code[:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'smt': 0,
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')
})
if index % 200 == 0 or index == len(set(lstock) - set(rst_df['S_INFO_WINDCODE'].tolist()))-1:
pipe.execute()
pipe.execute()
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_asharemargintrade run OVER')
def sell(self,sell_info):
"""
卖出
:param sell_info:卖出信息
:return:
"""
simulation_logger.info('str_id:{},pending order sell:{}'.format(self.strategy.strategy_id,sell_info))
time1 = time.time()
info = self.contract_operator.place_an_order(stock_info=sell_info,trade_type='sell')
simulation_logger.info('str_id:{},sell cost:{} s'.format(self.strategy.strategy_id,time.time() - time1))
info['used_money'] = round(float(info.get('useAmt',0)),2)
info['trade_count'] = int(info.get('useQty',0))
if 'useAmt' in info.keys():
info.pop('useAmt')
if 'transQty' in info.keys():
info.pop('transQty')
if info['respCode'] == '000':
if info['trade_count']:
# 保存交易流水
trade_time = datetime.now()
before_assets = self.position.total_assets
before_position = self.position.positions
self.position.update_master(query_hold_days=False)
after_assets = self.position.total_assets
after_position = self.position.positions
# transaction_save(before_position=before_position,after_position=after_position,before_assets=before_assets,
# after_assets=after_assets,strategy_id=self.strategy.strategy_id,trade_type='sell_one',
# trade_time=trade_time,order_info=sell_info,result_info=info)
__kwargs = {'before_position':before_position.to_dict(),'after_position':after_position.to_dict(),'before_assets':before_assets,
'after_assets':after_assets,'strategy_id':self.strategy.strategy_id,'trade_type':'sell_one',
'trade_time':trade_time.strftime('%Y-%m-%d %H:%M:%S'),'order_info':sell_info,'result_info':info}
transaction_save.apply_async(queue='transaction',kwargs=__kwargs)
# 删除持仓表信息
try:
# 在持仓表中删除清仓的股票
if int(before_position.loc[before_position['inst'] == sell_info['stockCode'],'quantity'].values[0])==int(info['trade_count']):
simulation_logger.info('str_id:{},清仓一只股票成功：{}，删除持仓表中数据'.format(self.strategy.strategy_id,sell_info['stockCode']))
with MysqlManager('quant').Session as session:
session.query(ContractPositionsInfo).filter(ContractPositionsInfo.strategy_id==str(self.strategy.strategy_id),
ContractPositionsInfo.contract_source_id == str(self.strategy.contract_source_id),
ContractPositionsInfo.stock_code == str(sell_info['stockCode'])).delete()
except Exception as e:
simulation_logger.info('str_id:{},卖出单只股票时删除持仓表信息出错：{}'.format(self.strategy.strategy_id, e))
return True,info
else:
return False,info
def fetch_planB(lstock):
"""
中国A股利润表: 更新字段 income
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
strstock = str(lstock).replace('[', '(').replace(']', ')')
# ashareincome: 中国A股利润表
sql_fetch_all = """SELECT T.S_INFO_WINDCODE, T.ANN_DT, T.TOT_OPER_REV, T.OPER_PROFIT, T.NET_PROFIT_EXCL_MIN_INT_INC
FROM
(
SELECT S_INFO_WINDCODE, ANN_DT, TOT_OPER_REV, OPER_PROFIT, NET_PROFIT_EXCL_MIN_INT_INC
FROM ashareincome
WHERE S_INFO_WINDCODE in {} ORDER BY ANN_DT DESC
) AS T
GROUP BY T.S_INFO_WINDCODE""".format(strstock)
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='all', inplace=True)
rst_df.drop_duplicates(subset=['S_INFO_WINDCODE'], keep='first', inplace=True)
print("中国A股利润表{}条数据".format(len(rst_df)))
pipe = redisManager3.pipeline()
for index, row in rst_df.iterrows(): # 遍历所有行
# 是否需要过滤科创板
if row['S_INFO_WINDCODE'][:3] == '688':
code_type = '14906'
else:
code_type = '14901'
income = list() # 初始化
for k, v in zip(['total_revenue', 'operation_profit', 'net_profits'], ['TOT_OPER_REV', 'OPER_PROFIT', 'NET_PROFIT_EXCL_MIN_INT_INC']):
income.append({
"cb": k,
"lv": row[v] if not np.isnan(row[v]) else ''})
code_no_suffix = row['S_INFO_WINDCODE'][:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'income': json.dumps(income),
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')
})
if index % 200 == 0 or index == len(rst_df) - 1:
pipe.execute()
pipe.execute()
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_ashareincome run OVER')
def get_total_assets(self):
'''
获取每日资产
:return:
'''
sql = 'select * from daily_assets where cntrId={}'.format(
self._cntrSourceId)
with MysqlManager('quant').Session as session:
result = session.fetchall(sql)
if result:
pd_data = pd.DataFrame(result)
change_type_list = [
'tTAstAmt', 'curPLPct', 'stkMktValAmt', 'curBalAmt',
'curAvalCapAmt', 'postion'
]
pd_data[change_type_list] = pd_data[change_type_list].astype(float)
return pd_data
else:
return pd.DataFrame()
def fetch_ashareeodderivativeindicator(lstock):
"""
中国A股日行情估值指标: 更新字段 pb, pe, ps, tfc, mktc, total, ashares_trade
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
# ashareeodderivativeindicator: 中国A股日行情估值指标
sql_fetch_all = """SELECT S_INFO_WINDCODE, S_VAL_PB_NEW, S_VAL_PE, S_VAL_PS, S_DQ_MV,
S_VAL_MV, TOT_SHR_TODAY, FLOAT_A_SHR_TODAY
FROM ashareeodderivativeindicator
WHERE TRADE_DT = (SELECT TRADE_DT FROM ashareeodderivativeindicator ORDER BY TRADE_DT DESC LIMIT 1)"""
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='all', inplace=True)
rst_df.drop_duplicates(subset=['S_INFO_WINDCODE'], keep='first', inplace=True)
print("中国A股日行情估值指标 获取{}条数据".format(len(rst_df)))
pipe = redisManager3.pipeline()
for index, row in rst_df.iterrows(): # 遍历所有行
# 是否需要过滤科创板
if row['S_INFO_WINDCODE'][:3] == '688':
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = row['S_INFO_WINDCODE'][:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'pb': row['S_VAL_PB_NEW'] if not np.isnan(row['S_VAL_PB_NEW']) else '', # 市净率
'pe': row['S_VAL_PE'] if not np.isnan(row['S_VAL_PE']) else '', # 市盈率
'ps': row['S_VAL_PS'] if not np.isnan(row['S_VAL_PS']) else '', # 市销率
'tfc': row['S_DQ_MV'] * 10000, # 流通市值
'mktc': row['S_VAL_MV'] * 10000, # 总市值
'total': int(row['TOT_SHR_TODAY'] * 10000), # 总股本（股
'ashares_trade': int(row['FLOAT_A_SHR_TODAY'] * 10000), # A股流通股本
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')
})
if index % 200 == 0 or index == len(rst_df) - 1:
pipe.execute()
pipe.execute()
#
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_ashareeodderivativeindicator run OVER')
def termination_contract(self,
parameters_dic='',
delete_instruction=False):
"""终止合约"""
__interface = '/externalService/external/cntr/end'
res_info = {
'respCode': '400',
'respMessage': '请求失败！请输入正确的操作指令！【delete_instruction】',
}
if delete_instruction is False:
return res_info
if not parameters_dic:
parameters_dic = dict()
parameters_dic['srcCntrId'] = self.cntr_id # 合约来源ID
res_data = self.con_interface.get_data(__interface, parameters_dic)
# 终止合约处理合约表和合约持仓表的信息
with MysqlManager('quant').Session as session:
for i in range(3):
try:
session.query(ContractInfo).filter(
ContractInfo.contract_source_id ==
self.cntr_id).update({'flag': 0}) # 合约表合约信息软删除
session.query(ContractPositionsInfo).filter(
ContractPositionsInfo.contract_source_id ==
self.cntr_id).delete() # 合约持仓表信息删除
except:
simulation_logger.info(
'STR_ID:{},【终止合约】数据库出现异常-{}:{}'.format(
self.strategy_id, i, traceback.print_exc()))
continue
break
else:
with open(
MY_PROJECT_PATH +
'/error_doc/termination_contract_error.txt', 'a') as f:
save_info = {
'strategy_id': self.strategy_id, # 策略ID
'cntr_id': self.cntr_id # 合约来源ID
}
f.write(json.dumps(save_info) + '\n')
return res_data
def get_trans_data(self):
'''
获取接口交易流水
:param capFlwTypList:
:return:
'''
sql = 'select * from simulation_transaction where strategy_id={}'.format(
self._strategy_id)
with MysqlManager('quant').Session as session:
result = session.fetchall(sql)
pd_data = pd.DataFrame(result)
change_type_list = [
'trade_before_percent', 'trade_after_percent', 'trade_price',
'profit', 'entrust_price', 'used_money', 'cost_price',
'trade_before_assets'
]
pd_data[change_type_list] = pd_data[change_type_list].astype(float)
return pd_data
def fetch_ashareintroduction(lstock):
"""
中国A股公司简介: 更新字段city, legrep, officeaddr, introduction, regcapital
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
strstock = str(lstock).replace('[', '(').replace(']', ')')
# ashareeodderivativeindicator: 中国A股日行情估值指标
sql_fetch_all = """SELECT S_INFO_WINDCODE, S_INFO_CITY, S_INFO_CHAIRMAN, S_INFO_OFFICE,
S_INFO_CHINESEINTRODUCTION, S_INFO_REGCAPITAL
FROM ashareintroduction
WHERE S_INFO_WINDCODE in {}""".format(strstock)
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='all', inplace=True)
rst_df.drop_duplicates(subset=['S_INFO_WINDCODE'], keep='first', inplace=True)
print("中国A股公司简介获取{}条数据".format(len(rst_df)))
pipe = redisManager3.pipeline()
for index, row in rst_df.iterrows(): # 遍历所有行
# 是否需要过滤科创板
if row['S_INFO_WINDCODE'][:3] == '688':
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = row['S_INFO_WINDCODE'][:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'city': row['S_INFO_CITY'].strip() if row['S_INFO_CITY'] is not None else '', # 股票公司所在城市
'legrep': row['S_INFO_CHAIRMAN'].strip() if row['S_INFO_CHAIRMAN'] is not None else '', # 法人代表
'officeaddr': row['S_INFO_OFFICE'].strip() if row['S_INFO_OFFICE'] is not None else '', # 办公地址
'introduction': row['S_INFO_CHINESEINTRODUCTION'].strip() if row['S_INFO_CHINESEINTRODUCTION'] is not None else '', # 公司中文简介
'regcapital': str(row['S_INFO_REGCAPITAL'])+'万元' if row['S_INFO_REGCAPITAL'] is not None else '', # 注册资本（万元）
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')
})
if index % 200 == 0 or index == len(rst_df) - 1:
pipe.execute()
pipe.execute()
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_ashareeodderivativeindicator run OVER')
def fetch_asharedescription(lstock):
"""
中国A股基本资料: 更新字段 shrNm, company_name, shsc, ksh
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
strstock = str(lstock).replace('[', '(').replace(']', ')')
# ashareexrightdividendrecord: 中国A股除权除息记录
sql_fetch_all = """SELECT S_INFO_WINDCODE, S_INFO_NAME, S_INFO_COMPNAME, IS_SHSC, S_INFO_LISTBOARD
FROM asharedescription
WHERE S_INFO_WINDCODE IN {}""".format(strstock)
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='all', inplace=True)
rst_df['IS_SHSC'] = rst_df['IS_SHSC'].astype('int32')
rst_df.drop_duplicates(subset=['S_INFO_WINDCODE'], keep='first', inplace=True)
print("中国A股除权除息记录获取{}条数据".format(len(rst_df)))
pipe = redisManager3.pipeline()
for index, row in rst_df.iterrows(): # 遍历所有行
# 是否需要过滤科创板
if row['S_INFO_LISTBOARD'] == '434009000': # 上市板类型
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = row['S_INFO_WINDCODE'][:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'shsc': row['IS_SHSC'],
'ksh': 1 if code_type == '14906' else 0,
'shrNm': row['S_INFO_NAME'].strip(),
'company_name': row['S_INFO_COMPNAME'].strip(),
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')})
if index % 200 == 0 or index == len(rst_df)-1:
pipe.execute()
pipe.execute()
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_asharedescription run OVER')
def fetch_ashareipo(lstock):
"""
中国A股首次公开发行数据: 更新字段 ipoDate, pubPrice, pubAmt
:param lstock: 股票列表
:return: 更新对应的redis数据
"""
with MysqlManager('wind') as session:
try:
# ashareipo: 中国A股首次公开发行数据
sql_fetch_all = """SELECT S_INFO_WINDCODE, S_IPO_LISTDATE, S_IPO_PRICE, S_IPO_AMOUNT FROM ashareipo"""
rst_df = session.read_sql(sql_fetch_all, to_DataFrame=True)
rst_df.dropna(axis=0, how='any', inplace=True)
rst_df.drop_duplicates(subset=['S_INFO_WINDCODE'], keep='first', inplace=True)
print("获取{}条数据".format(len(rst_df)))
pipe = redisManager3.pipeline()
for index, row in rst_df.iterrows(): # 遍历所有行
# 是否需要过滤科创板
if row['S_INFO_WINDCODE'][:3] == '688':
code_type = '14906'
else:
code_type = '14901'
code_no_suffix = row['S_INFO_WINDCODE'][:6]
pipe.hmset('stkRealTimeState:{}_{}'.format(code_no_suffix, code_type),
mapping={'ipoDate': PurpleUtils.dateFormatConvert(row['S_IPO_LISTDATE'],
'%Y%m%d', '%Y-%m-%d'),
'pubPrice': row['S_IPO_PRICE'], # 发行价
'pubAmt': str(row['S_IPO_AMOUNT']) + '万股', # -- 万，亿更改
'timeShareDaily_saveTime': datetime.datetime.now().strftime('%Y%m%d%H%M%S')
})
if index % 200 == 0 or index == len(rst_df)-1:
pipe.execute()
pipe.execute()
except Exception as e:
print("error: {}".format(e))
finally:
print('fetch_ashareipo run OVER')
#! /user/bin/env python
# -*- coding=utf-8 -*-
"""
模拟盘批处理
"""
from quant_backend.rocket.simulation.simulation_entrance import simulation_entrance
from quant_backend.rocket.strategy.strategy_data import StrategyData
from quant_backend.models.fundamental import StrategicFactor
from quotations.manager.mysqlManager import MysqlManager
if __name__ == "__main__":
# with auto_session() as session:
with MysqlManager('quant').Session as session:
ids = session.query(StrategicFactor.strategy_id).filter_by(
simulation_flag=1, flag=1).all()
# print('ids:{}'.format(ids))
strategy_data = StrategyData()
for id_tmp in ids:
id_now = int(id_tmp[0])
# if id_now in [100000000000022]:
# continue
# if id_now not in [100000000000020]:
# continue
data = strategy_data.get_strategy_data(strategy_id=id_now)
print('daily simulation data:{}'.format(data))
# simulation_entrance(data=data)
simulation_entrance.apply_async(queue='simulation_scripts',
args=[data, 1])
#! /user/bin/env python
# -*- coding=utf-8 -*-
"""
策略数据类
"""
import pandas as pd
from datetime import datetime
from quant_backend.rocket.strategy.order import Order
from quotations.constants import benchmark
from quotations.manager.mysqlManager import MysqlManager
aliyun_engine = MysqlManager('quant').engine
class Strategy:
def __init__(self):
# 初始化全局变量
self.benchmark = benchmark
# 最大持仓，默认全仓
self.maxPosPct = 1
# 单只股票进场仓位
self.sglPosPct = 0.05
# 初始本金
self.tstCap = 30000000
# 单只持仓上限
self.sglMaxPosPct = 0.1
# 其他相关字段
# 自定义盘前盘后操作
self.handlers = {}
# 现在时间
def simulation_entrance(data, save_or_simulation=0):
"""
模拟盘函数入口，用户新对接模拟盘会在下一个交易日买卖，9:35之前的会在今天买卖
:param data:
:param save_or_simulation: 进行保存还是保存并模拟，0：只保存，1：保存并模拟
:return:
"""
# 如果data为空则不模拟
if not data or 'list' not in data.keys():
return
# 初始化数据
# 10607实盘中，10608实盘出错，10609实盘成功
data['simulation_success_flag'] = 10607
if 'back_success_flag' in data.keys() and int(
data['back_success_flag']) != 10605:
data['simulation_success_flag'] = 10608
data['simulation_time'] = datetime.now().strftime('%Y-%m-%d')
data['simulation_flag'] = 1
data['simulation_message'] = '实盘中'
data['strategy_message'] = '实盘模拟'
data['save_or_simulation'] = int(save_or_simulation)
# 用于java
data['bkstResponMessage'] = u'实盘中'
data['bkstResponCode'] = 10607
data['trade_type'] = 'simulation'
# 通知JAVA
# if data.get('simulation_new_flag',0) > 0:
# urlPost(data)
# 如果回测失败则直接退出模拟
# if data['back_success_flag'] == 10606:
# return
# 解析语法
data_calculate = parse(data)
# 整理策略
strategy = Strategy()
strategy.hqDB = DataCollection()
strategy.init_data(data_calculate)
simulation_logger.info(
'\n\n-----------------------------------------------------------------------------------------------------------------------'
)
simulation_logger.info('str_id:{},strategy:{}'.format(
strategy.strategy_id, strategy))
# 初始化实例信息
with MysqlManager('quant').Session as session:
query_res = session.query(
ContractInfo.contract_source_id,
ContractInfo.contract_id).filter_by(
strategy_id=data_calculate['strategy_id'], flag=1).all()
if not query_res: # 没有查询到对应的合约来源ID
simulation_logger.info('str_id:{},该策略没有创建合约，需重先创建新合约'.format(
strategy.strategy_id))
module_func = ModuleFunc(strategy_id=data_calculate['strategy_id'],
usr_id=data_calculate['user_id'],
usr_name='--') # 用户来源ID
__info = module_func.create_new_contract({'cntrCapAmt':
'30000000'}) # 创建新的合约
if __info['respCode'] != '000':
simulation_logger.info('str_id:{},合约创建失败：{}'.format(
strategy.strategy_id, __info))
exit(0)
else:
# 将合约id保存到strategy中
strategy.contract_id = __info['cntrId']
strategy.contract_source_id = __info['srcCntrId']
data['contract_id'] = int(__info['cntrId'])
data['contract_source_id'] = int(__info['srcCntrId'])
else:
simulation_logger.info('str_id:{},该策略已有合约，直接初始化'.format(
strategy.strategy_id))
cntr_id = query_res[0][0]
data['contract_id'] = int(query_res[0][1])
data['contract_source_id'] = int(query_res[0][0])
module_func = ModuleFunc(strategy_id=data_calculate['strategy_id'],
cntr_id=cntr_id,
usr_id=data_calculate['user_id'],
usr_name='--') # 用户来源ID
# 将合约id保存到strategy中
strategy.contract_id = query_res[0][1]
strategy.contract_source_id = query_res[0][0]
# 保存更新策略因子信息
# print('得到的数据：{}'.format(data))
strategy.data_update = data
# 得到合约的持仓实例
position = Position(contract_operator=module_func, strategy=strategy)
# 得到合约的挂单实例
pending_order = PendingOrder(contract_operator=module_func,
position=position,
strategy=strategy)
# 得到策略数据操作类型
strategy_data = StrategyData()
# 装在模拟盘回测框架
simulation_trader = prototype.clone('simulation_trader',
strategy=strategy,
position=position,
pending_order=pending_order,
strategy_data=strategy_data)
# 正式开始回测交易
simulation_trader.start()
# 关闭数据库连接
strategy.hqDB.close()
def place_an_order(self, stock_info, sleep_m=5, trade_type='buy'):
"""
新策略下单
trade_type: 默认为1（买）， 2为卖
"""
# 返回数据(模板)
res_info = {
'respCode': '000',
'respMessage': '成功',
}
stock_code = str(stock_info.get('stockCode'))
if trade_type == 'buy': # 买
stock_info['tradeType'] = "10801"
elif trade_type == 'sell': # 卖
stock_info['tradeType'] = "10802"
# 【买/卖】
business_res = self.business_instructions(stock_info) # 委托
# print('business_res:', business_res)
if business_res.get(
'status_code') or business_res.get('respCode') != '000':
res_info['respCode'] = '404'
res_info['respMessage'] = '请求异常，请重新尝试！详情：{}'.format(business_res)
return res_info
entrust_number = business_res.get('entrustNo') # 委托编号
print('entrust_number:', entrust_number)
# 状态追踪
wait_count, flag, new_res_info = self.__status_track(entrust_number,
sleep_m=sleep_m)
simulation_logger.info(
'STR_ID:{}【追踪结果】wait_count:{},flag:{},stockCode:{}'.format(
self.strategy_id, str(wait_count), str(flag), stock_code))
if flag == 0: # 下单失败
res_info['respCode'] = '404'
res_info['respMessage'] = '下单失败！'
return res_info
if trade_type == 'buy': # 买
# 保存合约信息到python合约持仓表
update_info = {
'strategy_id': str(self.strategy_id), # 策略ID
'contract_source_id': str(self.cntr_id), # 合约来源ID
# 'contract_id': self.cntr_id, # 传过来的合约编号（3.0）
'stock_code': stock_code, # 股票号码
'user_id': self.usr_id,
'type': 0, # 暂时默认为0
'flag': flag
}
# print('update_info:', update_info)
simulation_logger.info('STR_ID:{},【持仓信息存储】:{},stockCode:{}'.format(
self.strategy_id, update_info, stock_info.get('stockCode')))
# 下单成功后保存持仓信息
with MysqlManager('quant').Session as session:
for i in range(3):
try:
session.merge(ContractPositionsInfo(**update_info))
except:
simulation_logger.info(
'STR_ID:{},【下单】数据库异常-{}:{}'.format(
self.strategy_id, i, traceback.print_exc()))
continue
break
else:
with open(
MY_PROJECT_PATH +
'/error_doc/ContractPositionsInfo_error.txt',
'a') as f:
f.write(json.dumps(update_info) + '\n')
res_info['useQty'] = new_res_info.get('useQty') # 成交数量
res_info['useAmt'] = new_res_info.get('useAmt') # 成交金额
return res_info
def request_positions(self, query_hold_days=True):
"""
请求持仓
:param query_hold_days: 是否重新请求持仓天数
:return:
"""
result = self.contract_operator.query_position()
if result['cntrPosList']:
# 标识查询后不为空的状态
self.positions_flag = 1
result = pd.DataFrame(result['cntrPosList'])
if result.empty:
result = pd.DataFrame(columns=[
'inst', 'name', 'current_price', 'cost_price', 'quantity',
'quantity_sell', 'profit_count', 'profit', 'market_value',
'cost_value', 'status', 'hold_days', 'percent'
])
return result
result = result[[
'stkCd', 'stkNm', 'curPrc', 'cstPrc', 'secQty', 'avalSelQty',
'flotPLAmt', 'pLPct', 'curMktValAmt', 'cstAmt', 'recStatId'
]]
# 依次是股票 股票名 当前价 成本价 持仓数量 可卖数量 盈亏金额 收益 最新市值 成本金额 状态
result.rename(columns={
'stkCd': 'inst',
'stkNm': 'name',
'curPrc': 'current_price',
'cstPrc': 'cost_price',
'secQty': 'quantity',
'avalSelQty': 'quantity_sell',
'flotPLAmt': 'profit_count',
'pLPct': 'profit',
'curMktValAmt': 'market_value',
'cstAmt': 'cost_value',
'recStatId': 'status'
},
inplace=True)
# 规范类型
result[[
'current_price', 'cost_price', 'profit_count', 'market_value',
'cost_value'
]] = result[[
'current_price', 'cost_price', 'profit_count', 'market_value',
'cost_value'
]].astype('float32').round(2)
result[['quantity', 'quantity_sell', 'status'
]] = result[['quantity', 'quantity_sell',
'status']].astype(int)
result['profit'] = result['profit'].astype('float32').round(4)
# 得到持仓占比
if self.total_assets:
result['percent'] = round(
result['market_value'] / self.total_assets, 6)
elif self.cash:
__total_assets = result['market_value'].sum() + self.cash
result['percent'] = round(
result['market_value'] / __total_assets, 6)
# 得到持仓天数
# 在持仓不为空并且有更新标志或者现有仓位股票没有持仓天数的时候，更新持仓天数数据
if query_hold_days or set(result['inst']) - set(
self.hold_days.keys()):
with MysqlManager('quant').Session as session:
__result = session.query(
ContractPositionsInfo.positions_days,
ContractPositionsInfo.stock_code).filter(
ContractPositionsInfo.contract_source_id ==
self.strategy.contract_source_id).all()
for _days, _stock in __result:
self.hold_days[_stock] = int(_days)
for __stock in list(result['inst']):
result.loc[result['inst'] == __stock,
'hold_days'] = self.hold_days.get(__stock, 1)
else:
# 标识为查询为空的状态
self.positions_flag = -1
result = pd.DataFrame(columns=[
'inst', 'name', 'current_price', 'cost_price', 'quantity',
'quantity_sell', 'profit_count', 'profit', 'market_value',
'cost_value', 'status', 'hold_days', 'percent'
])
# simulation_logger.info('position:{}'.format(result))
# return pd.DataFrame([['浦发银行',105,12.500000,'2017-10-10','600000.SH',13.04,'',4,0.0513592,13.04,-0.041411,38400,-20730,37123]],columns=['name', 'count', 'current_price', 'date', 'inst', 'last_price', 'msg', 'no', 'percent','price','profit','quantity','profit_count','quantity_sell'])
return result
shrNm : 股票名称 AShareDescription.S_INFO_NAME
日行情 -- 每日17:00更新
"""
import time
import datetime
import numpy as np
from quotations.util.common import timeit, tradeit
from quotations.manager.mysqlManager import MysqlManager
from quotations.manager.redisManager import RedisManager
from quotations.plate.purple.compat import is_redispy2, is_redispy3
from quotations.plate.purple import utils as PurpleUtils
import json
# Chyi add
engine_wind = MysqlManager('wind').engine
redisManager3 = RedisManager('bus_3')
def fetch_stock_list(if_refresh):
"""
获取需要更新的股票列表
:param if_refresh:
:return:
"""
with MysqlManager('wind') as session:
try:
if if_refresh:
# asharedescription: 中国A股基本资料；ashareipo: 中国A股首次公开发行数据表
sql_fetch_all = """SELECT asharedescription.S_INFO_WINDCODE
FROM asharedescription, ashareipo
