def get_order_vol(capital, position_percent, price):
if price == 0:
log_helper.error('price is 0 when get order vol')
return 0
ret = int(capital * position_percent / (price * 100)) * 100
#log_helper.info('get order vol {3}[{0}, pos_cent:{1}, price:{2}]'.format(capital, position_percent, price, ret))
return ret
def sellBasket(self, stkPriceVol):
'''
一篮子股票卖出
stkPriceVol：pandas.DataFrame格式
'''
if len(stkPriceVol) == 0:
return
if self.broker == u'华泰证券':
for i in range(len(stkPriceVol)):
stk, price, vol = stkPriceVol.iloc[i, :]
self.sell(stk, price, vol)
elif self.broker == u'中信建投':
for i in range(len(stkPriceVol)):
stk, price, vol = stkPriceVol.iloc[i, :]
self.sell(stk, price, vol)
else:
stks = stkPriceVol.iloc[:, 0].astype(str)
prices = stkPriceVol.iloc[:, 1].astype(float).map(
lambda x: round(x, 2))
vols = stkPriceVol.iloc[:, 2].astype(int)
categorys = [1] * len(stks)
if (prices * vols).max() > self.riskLimit:
log_helper.error('[error]sellBasket: money out of risklimit')
return
self._sendOrders(list(stks), list(prices), list(vols),
list(categorys))
log_helper.info('[info]sellBasket:{0}'.format(stkPriceVol))
def get_sql(self, query, vars=None):
"""获取编译后的sql语句"""
# 记录程序执行开始时间
start_time = datetime.now().timestamp()#time.clock()
try:
# 判断是否记录sql执行语句
if self.is_output_sql:
log_helper.info('sql:' + str(query))
# 建立游标
self.cursor = self.connect.cursor()
# 执行SQL
self.data = self.cursor.mogrify(query, vars)
except Exception as e:
# 将异常写入到日志中
log_helper.error('sql生成失败:' + str(e.args) + ' query:' + str(query))
self.data = '获取编译sql失败'
finally:
# 关闭游标
self.cursor.close()
# 记录程序执行结束时间
end_time = datetime.now().timestamp()
# 写入日志
self.write_log(start_time, end_time, query)
return self.data
def cancel(self, stk, direction='buy', method='all'):
log_helper.info('cancel[stock:{0},direction:{1}]'.format(
stk, direction))
result = '\0' * 256
errInf = '\0' * 256
dir_id = 0 if direction == 'buy' else 1
exchangeID = '1' if stk[0] == '6' else '0'
entru = self.query_active_orders()
if entru is None:
return
hth = entru.loc[(entru['StockCode'] == stk) &
(entru['Direction'] == dir_id),
['EntruId', 'EntruTime']] #合同编号
hth.sort_values('EntruTime', inplace=True)
if len(hth) == 0:
log_helper.error('[error]请检查挂单是否存在')
return
if method == 'all':
for entruId in hth['EntruId']:
log_helper.info(entruId)
self._cancel_byID(exchangeID, entruID, result, errInf)
elif method == 'first':
entruId = str(hth['EntruId'].iloc[0])
self._cancel_byID(exchangeID, entruID, result, errInf)
elif method == 'last':
entruId = str(hth['EntruId'].iloc[-1])
self._cancel_byID(exchangeID, entruID, result, errInf)
def commit(self):
"""提交事务"""
try:
if self.connect:
self.connect.commit()
self.close_conn()
except Exception as e:
log_helper.error('提交事务失败：' + str(e.args))
def open_conn(self):
try:
if not self.connect:
self.connect=sqlite3.connect(self.dbname)
return self.connect
except Exception as e:
log_helper.error('连接数据库失败：' + str(e.args))
return False
def execute(self, query, vars=None):
"""执行sql语句查询，返回结果集或影响行数"""
if not query:
return None
# 记录程序执行开始时间
start_time = datetime.now().timestamp()
try:
# 判断是否记录sql执行语句
if self.is_output_sql:
log_helper.info('sql:' + str(query))
# 建立游标
if self.connect:
self.cursor=self.connect.cursor()
else:
self.open_conn()
self.cursor=self.connect.cursor()
# 执行SQL
result = self.cursor.executescript(query)
except Exception as e:
# 将异常写入到日志中
log_helper.error('sql执行失败:' + str(e.args) + ' query:' + str(query))
self.data = None
else:
self.commit()
# 获取数据
#try:
#self.col=[tuple[0] for tuple in self.cursor.description]
#if self.cursor.description:
# 在执行insert/update/delete等更新操作时，如果添加了returning，则读取返回数据组合成字典返回
#self.data =self.cursor.fetchall() #self.data = [dict((self.cursor.description[i][0], value) for i, value in enumerate(row)) for row in self.cursor.fetchall()]
#else:
# 如果执行insert/update/delete等更新操作时没有添加returning，则返回影响行数，值为0时表时没有数据被更新
#self.data = self.cursor.rowcount
#self.commit()
#except Exception as e:
# 将异常写入到日志中
#log_helper.error('数据获取失败:' + str(e.args) + ' query:' + str(query))
#self.data = None
#self.rollback()
#finally:
# 关闭游标
#self.cursor.close()
# 记录程序执行结束时间
end_time = datetime.now().timestamp()
# 写入日志
self.write_log(start_time, end_time, query)
# 如果有返回数据，则把该数据返回给调用者
return self.data
def get_stock_list(self, market_no):
nStart = 0
errinfo, count, result = self.clientHq.GetSecurityList(market_no, nStart)
if errinfo != "":
log_helper.error(errinfo)
return
res = result.splitlines()
for item in res:
log_helper.info(item)
res = [stock_code.split('\t')[0] for stock_code in res ]
#log_helper.info(res[:1500])
return res[1:]
def sell(self, stk, price, vol):
price = round(float(price), 2)
if vol * price > self.riskLimit:
log_helper.error(
'[error]sell[stock:{0},price:{1},vol:{2}]money out of risklimit'
.format(stk, price, vol))
return
log_helper.info(
'[info]sell[stock:{0},price:{1},vol:{2},money:{3}]'.format(
stk, price, vol, vol * price))
return self._sendOrder(str(stk),
float(price),
int(vol),
category=1,
priceType=0)
def connect_market_server(self):
try_times = 0
len_market_servers = len(market_server_addresses)
while try_times<300:
try_times += 1
try:
server_addr = market_server_addresses[try_times%len_market_servers]
self.clientHq = TradeX.TdxHq_Connect(server_addr[0], server_addr[1])
log_helper.info('connect hq success')
break
except TradeX.TdxHq_error, e:
log_helper.error("connect hq fail TdxHq_error error: {0}".format(e.message))
time.sleep(0.5)
except Exception,e:
log_helper.error("connect hq fail unkown error: {0}".format(e.message))
time.sleep(0.5)
def _cancel_byID(self, exchangeID, orderID, result, errInf):
if self.is_tradex:
errInf, result = self.client.CancelOrder(int(exchangeID), orderID)
log_helper.info(
'cancel_withID[{0},order_id:{1}] result:{2} errInf:{3}'.format(
exchangeID, orderID, result.decode("gbk"), errInf))
else:
self.dll.CancelOrder(self.client, exchangeID, str(orderID), result,
errInf)
try:
log_helper.info(
'cancel_withID[{0},order_id:{1}] result:{2} errInf:{3}'.
format(exchangeID, orderID, result.decode("gbk"), errInf))
except UnicodeDecodeError:
log_helper.error(
'cancel_withID[{0},order_id:{1}] error:{2}'.format(
exchangeID, orderID, errInf.decode("gbk")))
def init_stockholder_code(self):
gdInf = [i.split() for i in self._queryData(5).splitlines()]
minLen = min([len(s) for s in gdInf])
gdInf = [i[:minLen] for i in gdInf]
try:
stockholder_code_sh, stockholder_code_sz = [
str(i)
for i in pd.DataFrame(gdInf[1:], columns=gdInf[0])[u'股东代码']
][:2]
if stockholder_code_sz[0] == 'A':
stockholder_code_sh, stockholder_code_sz = stockholder_code_sz, stockholder_code_sh
except Exception, e:
log_helper.error('[error]DllTrader init exception:{0}'.format(e))
stockholder_code_sh, stockholder_code_sz = [
str(i)
for i in pd.DataFrame(gdInf[1:], columns=gdInf[0])[u'股东号']
][:2]
def sqlite (self,sql):
try:
start_time = datetime.now().timestamp()
col=[]
if self.connect:
self.cursor=self.connect.cursor()
else:
self.open_conn()
self.cursor=self.connect.cursor()
self.cursor.execute(sql)
except Exception as e:
# 将异常写入到日志中
log_helper.error('sql执行失败:' + str(e.args) + ' query:' + str(sql))
data = None
else:
# 获取数据
try:
#print(self.cursor.description)
if self.cursor.description:
data =self.cursor.fetchall()
col=[tuple[0] for tuple in self.cursor.description]
else:
# 如果执行insert/update/delete等更新操作时没有添加returning，则返回影响行数，值为0时表时没有数据被更新
data = self.cursor.rowcount
except Exception as e:
# 将异常写入到日志中
log_helper.error('数据获取失败:' + str(e.args) + ' query:' + str(sql))
data = None
self.rollback()
finally:
# 提交并关闭游标,关闭数据库
self.commit()#self.cursor.close()#self.close_conn()
# 记录程序执行结束时间
end_time = datetime.now().timestamp()
# 写入日志
self.write_log(start_time, end_time, sql)
return (col,data)
def copy(self, values, table_name, columns):
"""
百万级数据更新函数
:param values: 更新内容，字段之间用\t分隔，记录之间用\n分隔 "1\taaa\tabc\n2\bbb\abc\n"
:param table_name: 要更新的表名称
:param columns: 需要更新的字段名称：例：('id','userame','passwd')
:return:
"""
try:
# 建立游标
self.cursor = self.connect.cursor()
self.cursor.copy_from(StringIO(values), table_name, columns=columns)
self.connect.commit()
return True
except Exception as e:
# 将异常写入到日志中
log_helper.error('批量更新失败:' + str(e.args) + ' table:' + table_name)
finally:
# 关闭游标
self.cursor.close()
def available_close_vol(account_cache, userName, stock, percent=1):
if userName not in account_cache.keys():
return 0
positions = account_cache[userName]['position'].set_index('StockCode')
if stock not in positions.index:
log_helper.error('no position on {0} when stop loss'.format(stock))
return 0
position_size = positions['CoverableSize'][stock]
if position_size == 0:
return 0
if percent == 1:
return position_size
vol = int(position_size * percent / 100) * 100
if vol == 0:
return position_size
return vol
def __exit__(self,type,value,trace): if (type is not None and value is not None and trace is not None): log_helper.error( "type:"+ str(type) +"value:"+ str(value) + "\ntrace:", str(trace)) self.close_conn()
def buy_up_limit_work(context, data_cache_manager, pool_item_cache, pool_name):
for tempUser in pool_item_cache:
try:
if tempUser not in data_cache_manager.accounts.keys():
#log_helper.warn('account {0} not exist for pool {1}'.format(tempUser, pool_name))
continue
if data_cache_manager.exceed_max_trade_count(tempUser, pool_name):
log_helper.warn(
'{0} exceed max trade times at pool {1}'.format(
tempUser, pool_name))
time.sleep(15)
continue
posLimit = common.get_total_max_position_percent(
data_cache_manager.accounts[tempUser]['posLimit'])
usable = data_cache_manager.accounts[tempUser]['usable']
asset = data_cache_manager.accounts[tempUser]['asset']
poolItemList = list(pool_item_cache[tempUser].values())
total_pos = 0.0
for poolItem in poolItemList:
if poolItem.is_sent_order:
continue
pos = common.percent2float(poolItem.pos)
total_pos += pos
if not common.check_position_limit(data_cache_manager.accounts,
tempUser,
to_buy_percent=total_pos,
max_limit=posLimit):
poolItem.state = u'仓位超{0}'.format(posLimit)
continue
if poolItem.stock not in context.market_depths_df.index:
log_helper.warn(
'failed to get market data for stock:{0}'.format(
poolItem.stock))
continue
market_data = context.market_depths_df.ix[poolItem.stock]
pre_close = market_data['pre_close']
if pre_close <= 0:
continue
high_limit = round(pre_close * 1.1, 2)
if common.is_open_high_limit(market_data):
continue
last_px = market_data['last_px']
if last_px < (high_limit - 0.01):
continue
price = high_limit
vol = common.get_order_vol(asset, pos, price)
poolItem.is_sent_order = True
state = data_cache_manager.accounts[tempUser]['tradeApi'].buy(
poolItem.stock, price, vol)
log_helper.info(
'[{0}]{1} buy {2} at {3} @ {4} state:{5}'.format(
tempUser, pool_name, poolItem.stock, price, vol,
state))
state = state.splitlines()
state = [i.split('\t') for i in state]
if len(state) > 1:
poolItem.state = str(
pd.DataFrame(state[1:], columns=state[0]).iloc[0, 0])
data_cache_manager.orderid2pooldict[common.get_order_key(
tempUser, poolItem.state)] = pool_name
data_cache_manager.add_orderid_to_dict(
pool_name, poolItem.stock, poolItem.state, tempUser)
except Exception, e:
log_helper.error(u'[error]请检查{0}买入条件单是否正确:{1}'.format(
pool_name, e))
while try_times<300:
try_times += 1
try:
server_addr = market_server_addresses[try_times%len_market_servers]
self.clientHq = TradeX.TdxHq_Connect(server_addr[0], server_addr[1])
log_helper.info('connect hq success')
break
except TradeX.TdxHq_error, e:
log_helper.error("connect hq fail TdxHq_error error: {0}".format(e.message))
time.sleep(0.5)
except Exception,e:
log_helper.error("connect hq fail unkown error: {0}".format(e.message))
time.sleep(0.5)
if try_times >= 200:
log_helper.error("connect hq fail too many times(>=200)")
sys.exit(-1)
def get_stock_count(self, market_no):
errinfo, count = self.clientHq.GetSecurityCount(market_no)
if errinfo != "":
log_helper.error(errinfo)
else:
log_helper.info("market no:{0} 股票数量：{1}".format(market_no, count))
def get_stock_list(self, market_no):
nStart = 0
errinfo, count, result = self.clientHq.GetSecurityList(market_no, nStart)
if errinfo != "":
def get_market_depths(self, stock_list):
#log_helper.info('-----------begin')
#log_helper.info('get_market_depths:{0}'.format(stock_list[:10]))
query_stocks = []
i = 0
temp_arr = []
for item in stock_list:
i+=1
temp_arr.append((self._get_market_no(item), item))
if i%80==0:
query_stocks.append(temp_arr)
temp_arr = []
if len(temp_arr) > 0:
query_stocks.append(temp_arr)
df_arr = []
for item in query_stocks:
errinfo, count, result = self.clientHq.GetSecurityQuotes(item)
if errinfo != "":
log_helper.error("GetSecurityQuotes error:{0}".format(errinfo))
self.connect_market_server()
continue
res = result.splitlines()
res = [i.split('\t') for i in res]
length = min([len(x) for x in res])
if length == 1:
continue
df_arr.append(pd.DataFrame([i[:length]for i in res[1:]], columns=market_depth_columns))
if len(df_arr) == 0:
return None
ret_df = pd.concat(df_arr)
ret_df.set_index('stock_code', inplace=True)
ret_df[['last_px','pre_close','open','high','low','total_money','bid5_px','ask5_px','bid4_px','ask4_px','bid3_px','ask3_px','bid2_px','ask2_px','bid1_px','ask1_px']]\
= ret_df[['last_px','pre_close','open','high','low','total_money','bid5_px','ask5_px','bid4_px','ask4_px','bid3_px','ask3_px','bid2_px','ask2_px','bid1_px','ask1_px']].astype(np.float)
ret_df[['last_qty','total_vol','bid5_qty','ask5_qty','bid4_qty','ask4_qty','bid3_qty','ask3_qty','bid2_qty','ask2_qty','bid1_qty','ask1_qty']]\
= ret_df[['last_qty','total_vol','bid5_qty','ask5_qty','bid4_qty','ask4_qty','bid3_qty','ask3_qty','bid2_qty','ask2_qty','bid1_qty','ask1_qty']].astype(np.int32)
len_df = len(ret_df)
#log_helper.info('-------:{0}'.format(len(ret_df)))
# strong_stocks = []
# for i in range(len_df):
# row_data = ret_df.iloc[i]
# last_px = row_data['last_px']
# pre_close = row_data['pre_close']
# if last_px <=0 or pre_close <= 0:
# continue
# change_percent = round(last_px/pre_close, 2)
# if change_percent < 1.07:
# continue
# if row_data['open'] == row_data['low'] == row_data['high']:
# continue
# strong_stocks.append((ret_df.index[i], [last_px, pre_close, '{0}%'.format((change_percent-1)*100)]))
#log_helper.info('strong stocks:{0}'.format(strong_stocks))
return ret_df
def get_stock_count(self, market_no):
errinfo, count = self.clientHq.GetSecurityCount(market_no)
if errinfo != "":
log_helper.error(errinfo)
else:
log_helper.info("market no:{0} 股票数量：{1}".format(market_no, count))
