def update_cangdan(self):
tsl = tslFunctions()
tsl.tsl_login()
conn_Local = create_engine(
str(r"mysql+pymysql://root:xxxx@localhost:3306/test"))
self.__start=(dt.parser.parse(str(pd.read_sql("select max(date) from register_values",con=conn_Local\
).values[0][0]))+datetime.timedelta(days=1)).strftime('%Y-%m-%d')
print(self.__start)
tradedaysList = tsl.getTradeDays(self.__start, self.__end)
if len(tradedaysList) == 0:
print("已经更新至最新日期%s" % self.__end)
return
update_cangdan_raw = pd.DataFrame(
tsl.tsbytestostr(
ts.RemoteExecute(
f'''X:=getbk('期货品种代码');
r:=array();
for i:=0 to length(X)-1 do
begin
SetSysParam(PN_Stock(),x[i]);
Ret:=GetFuturesDailyWarehouse(inttodate({self.__start.replace('-','')}),inttodate({self.__end.replace('-','')}),t);
r&=t;
end;
return r;''', {})[1]))
if len(update_cangdan_raw) == 0:
print("No Datas")
else:
print('1')
can_l = update_cangdan_raw.截止日.unique() #记录下返回表的所有日期
update_cangdan_raw['utime'] = datetime.datetime.now()
update_cangdan_raw.to_sql('register_raw',
conn_Local,
if_exists='append',
index=False,
chunksize=5000)
update_cangdan = update_cangdan_raw.loc[~(
update_cangdan_raw.是否小计 == '小计')]
#print(len(update_cangdan))
update_cangdan.fillna(value=0, inplace=True)
update_cangdan['value']=update_cangdan['已制成仓单的货物数量']+\
update_cangdan['今日注册仓单量']+update_cangdan['仓单数量']
update_cangdan[
'change'] = update_cangdan['当日增减'] + update_cangdan['仓单变动量']
rlt = update_cangdan.groupby(['代码', '截止日'],
as_index=False)['value', 'change'].sum()
rlt.columns = ['code', 'date', 'value', 'change']
rlt['code'] = rlt['code'].apply(lambda x: x.upper())
rlt.to_sql('register_values',
conn_Local,
if_exists='append',
index=False,
chunksize=5000)
ts.Disconnect()
def CsQueryMultiFields(field_dict,
end_date,
bk_name=_ashare,
stock_list=None,
condition="1",
**kwargs):
"""天软Query函数封装
与CsQuery()的不同是，此函数对每只股票提取的字段数量大于1。
"""
field_dict.update({"'IDs'": 'DefaultStockID()'})
if stock_list is None:
stock_list = "''"
else:
stock_list = "'%s'" % ";".join(map(tradecode_to_tslcode, stock_list))
if (end_date.hour == 0) and (end_date.minute == 0) and (end_date.second
== 0):
encode_date = tsl.EncodeDate(end_date.year, end_date.month,
end_date.day)
else:
encode_date = tsl.EncodeDateTime(end_date.year, end_date.month,
end_date.day, end_date.hour,
end_date.minute, end_date.second, 0)
func_name = "Query"
func_args = [bk_name, stock_list, condition, "''"] + list(
reduce(lambda x, y: x + y, field_dict.items()))
script_str = _gstr_from_func(func_name, func_args)
sysparams = {'CurrentDate': encode_date}
sysparams.update(kwargs)
data = tsl.RemoteExecute(script_str, sysparams)
df = parseByStock(data)
return df
def CsQuery(field_dict,
end_date,
bk_name=_ashare,
stock_list=None,
condition="1",
**kwargs):
"""对天软Query函数的封装
Parameters:
===========
field_dict:
"""
field_dict.update({"'IDs'": 'DefaultStockID()'})
if stock_list is None:
stock_list = "''"
else:
stock_list = "'%s'" % ";".join(map(tradecode_to_tslcode, stock_list))
if (end_date.hour == 0) and (end_date.minute == 0) and (end_date.second
== 0):
encode_date = tsl.EncodeDate(end_date.year, end_date.month,
end_date.day)
else:
encode_date = tsl.EncodeDateTime(end_date.year, end_date.month,
end_date.day, end_date.hour,
end_date.minute, end_date.second, 0)
func_name = "Query"
func_args = [bk_name, stock_list, condition, "''"] + list(
reduce(lambda x, y: x + y, field_dict.items()))
script_str = _gstr_from_func(func_name, func_args)
sysparams = {'CurrentDate': encode_date}
sysparams.update(kwargs)
data = tsl.RemoteExecute(script_str, sysparams)
df = parse2DArray(data, column_decode=['IDs'])
df['IDs'] = df['IDs'].apply(tslcode_to_tradecode)
df['date'] = end_date
return df.set_index(['date', 'IDs'])
def getDailyFuturesDetails(self, BegDate, EndDate, symbol):
detailsDF = pd.DataFrame(
self.tsbytestostr(
ts.RemoteExecute(
'''N:=TradeDays(StrToDate('%s'),StrToDate('%s'));
return nday(N, 'DATE', datetimetostr(sp_time()),
'CLOSE', close(),
'HIGH', high(),
'LOW', low(),
'OPEN',open(),
'AMOUNT',amount(),
"SETTLEMENT",Settlement(),
"OPENINT",OpenInterest(),
"VOL",vol(),
'CONTRACT',base(703001),
'COMMODITY',base(703003),
'MARGIN',FuturesTradingMarginRate(sp_time(),0));''' %
(BegDate, EndDate), {
'StockID': symbol,
"CurrentDate": ts.LocalCallFunc(
"StrToDate", [EndDate])[1]
}))[1])
try:
detailsDF['COMMODITY'] = [
x.upper() for x in detailsDF['COMMODITY']
]
except Exception as e:
print('-------------------')
print(e)
print('%s合约没有数据！！！' % symbol)
print('-------------------')
return detailsDF
def get_index_members(self,
idx,
start_date=None,
end_date=None,
dates=None):
r = []
if idx == '全部A股':
func = 'getahis3'
_ = '_gstr_from_func(func, [dd])'
else:
func = 'getBKMembers'
_ = '_gstr_from_func(func, [dd, idx])'
idx = "'%s'" % idx
for d in dates:
dd = d.strftime("%Y%m%d")
script_str = eval(
_, {
'dd': dd,
'idx': idx,
'_gstr_from_func': _gstr_from_func,
'func': func
})
data = tsl.RemoteExecute(script_str, {})
data = parse1DArray(data, "IDs", 1)
data['date'] = d
r.append(data)
data = pd.concat(r)
data['sign'] = 1
data['IDs'] = data['IDs'].str[2:]
return data.set_index(['date', 'IDs'])
def TsQuery(field_dict, dates, stock, **kwargs):
"""
天软时间序列函数
"""
field_dict.update({
"'date'": 'DateTimeToStr(sp_time())',
"'IDs'": 'DefaultStockID()'
})
stock = tradecode_to_tslcode(stock)
N = len(dates)
func_args = [str(N)] + list(reduce(lambda x, y: x + y, field_dict.items()))
func_name = "Nday"
script_str = _gstr_from_func(func_name, func_args)
end_date = max(dates)
if (end_date.hour == 0) and (end_date.minute == 0) and (end_date.second
== 0):
encode_date = tsl.EncodeDate(end_date.year, end_date.month,
end_date.day)
else:
encode_date = tsl.EncodeDateTime(end_date.year, end_date.month,
end_date.day, end_date.hour,
end_date.minute, end_date.second, 0)
sysparams = {'CurrentDate': encode_date, 'StockID': stock}
sysparams.update(kwargs)
data = tsl.RemoteExecute(script_str, sysparams)
df = parse2DArray(data, column_decode=['IDs', 'date'])
df['IDs'] = df['IDs'].apply(tslcode_to_tradecode)
df['date'] = pd.DatetimeIndex(df['date'])
return df.set_index(['date', 'IDs'])
def ticks(self, code, start_date, end_date):
ts_template = Template('''setsysparam(pn_stock(),'SH510500');
begT:= StrToDate('{{start_date}}');
endT:= StrToDate('{{end_date}}');
setsysparam(pn_cycle(),cy_1s());
setsysparam(pn_rate(),0);
setsysparam(pn_RateDay(),rd_lastday);
r:= select ["StockID"] as 'ticker', datetimetostr(["date"]) as "time", ["price"],
["buy1"], ["bc1"], ["buy2"],["bc2"], ["buy3"],["bc3"],
["sale1"],["sc1"], ["sale2"],["sc2"], ["sale3"],["sc3"]
from markettable datekey begT to endT of "{{code}}" end;
return r;''')
ts_sql = ts_template.render(
start_date=dateparse(start_date).strftime('%Y-%m-%d'),
end_date=dateparse(end_date).strftime('%Y-%m-%d'),
code=code)
fail, data, _ = ts.RemoteExecute(ts_sql, {})
def gbk_decode(strlike):
if isinstance(strlike, (str, bytes)):
strlike = strlike.decode('gbk')
return strlike
def bytes_to_unicode(record):
return dict(
map(lambda s: (gbk_decode(s[0]), gbk_decode(s[1])),
record.items()))
if not fail:
unicode_data = list(map(bytes_to_unicode, data))
return pd.DataFrame(unicode_data).set_index(['time', 'ticker'])
else:
raise Exception("Error when execute tsl")
def getCurrentPrice(self, ticker):
ts_sql = '''
setsysparam(pn_stock(),'{}');
rds := rd(6);
return rds;
'''.format(ticker)
fail, value, _ = ts.RemoteExecute(ts_sql, {})
return value
def ticks(self, ticker, date, func):
ts_sql = '''
setsysparam(pn_stock(),'{0}');
v:={2}(inttodate({1}));
return v;
'''.format(ticker, date, func)
fail, value, _ = ts.RemoteExecute(ts_sql, {})
return value
def getDailyFuturesCode(self,BegDate,EndDate,section='郑州商品交易所;上海期货交易所;大连商品交易所;中国金融期货交易所;上海国际能源交易中心'):
allContractsCode =pd.DataFrame(self.tsbytestostr(ts.RemoteExecute(
'''return select thisrow as '代码', spec(datetostr(inttodate(base(703018))),thisrow) as '最后交易日',\
spec(datetostr(inttodate(base(703002,0))),thisrow) as '变动日'\
from getbk('%s') end; '''%section,{}))[1])
specDayContracts1 = allContractsCode[allContractsCode['变动日']<=EndDate]
specDayContracts2 = specDayContracts1[specDayContracts1['最后交易日'] >= BegDate]
return specDayContracts2['代码']
def ticks(self):
ts_sql = '''
setsysparam(pn_stock(),'SH510500');
setsysparam(pn_precision(),3);
rds := rd(6);
return rds;
'''
fail, value, _ = ts.RemoteExecute(ts_sql, {})
return value
def history_allA(date): #date should be like '20180607'
t = """
return getabkbydate('深证A股;上证A股;中小企业板;创业板',inttodate({}));
""".format(date)
allA_code = TSLPy3.RemoteExecute(t, {})[1]
if len(allA_code) == 0:
print('can not get history allA stockcode')
else:
return allA_code
def CsQuery(field_dict,
end_date,
bk_name=_ashare,
stock_list=None,
condition="1",
code_transfer=True,
**kwargs):
"""对天软Query函数的封装
Parameters:
===========
field_dict: dict
计算字段{字段名称: 函数名称},字段名称需自加单引号。
例：{"'IDs'": 'DefaultStockID()'}
end_daet: str
截面日期
bk_name: str
天软板块名称，以分号分割。
stock_list: list
股票列表，默认的格式是6位代码
condition: str
天软Csquery参数
code_transfer: bool
是否要将股票列表的代码转为天软格式。如果stock_list中代码格式
没有后缀，那code_transfer需为True。
kwargs: dict
天软系统参数
"""
field_dict.update({"'IDs'": 'DefaultStockID()'})
if stock_list is None:
stock_list = "''"
else:
if code_transfer:
stock_list = "'%s'" % ";".join(
map(tradecode_to_tslcode, stock_list))
else:
stock_list = "'%s'" % ";".join(stock_list)
if (end_date.hour == 0) and (end_date.minute == 0) and (end_date.second
== 0):
encode_date = tsl.EncodeDate(end_date.year, end_date.month,
end_date.day)
else:
encode_date = tsl.EncodeDateTime(end_date.year, end_date.month,
end_date.day, end_date.hour,
end_date.minute, end_date.second, 0)
func_name = "Query"
func_args = [bk_name, stock_list, condition, "''"] + list(
reduce(lambda x, y: x + y, field_dict.items()))
script_str = _gstr_from_func(func_name, func_args)
sysparams = {'CurrentDate': encode_date}
sysparams.update(kwargs)
data = tsl.RemoteExecute(script_str, sysparams)
df = parse2DArray(data, column_decode=['IDs'])
df['IDs'] = df['IDs'].apply(tslcode_to_tradecode)
df['date'] = end_date
return df.set_index(['date', 'IDs'])
def get_st(self, start_date=None, end_date=None, dates=None):
r = []
for d in dates:
dd = d.strftime("%Y%m%d")
script_str = _gstr_from_func('getST', [dd])
data = tsl.RemoteExecute(script_str, {})
data = parse1DArray(data, "IDs", 1)
data['date'] = d
r.append(data)
data = pd.concat(r)
data['sign'] = 1
data['IDs'] = data['IDs'].str[2:]
return data.set_index(['date', 'IDs'])
def getData(sCode, sDate):
while True:
ts.ConnectServer('211.100.23.205', 443)
ts.LoginServer('fuchunguang', 'Fcg=888888')
if ts.Logined() == True:
print('已登录')
daycount = 0
for code in sCode:
#########deal min1
hCodeInfo1min = {}
with open('./{}_5s.csv'.format(code), 'w') as f:
f.write('code,mtime,open,high,low,close,vol,amount\n')
for date in sDate:
daycount += 1
# sql = 'setsysparam(pn_stock(),\'{}\');' \
# 'setsysparam(PN_Cycle(), cy_1m());setsysparam(pn_date(),inttodate({}));' \
# 'return nday({},"mdate",datetimetostr(sp_time()),"open",Open(),"high",High(),"low",Low(),"close",Close(),"vol",Vol(),"amount",Amount());' \
# .format(code, date, 240) # 240个正好是一天
sql = 'setsysparam(pn_stock(),\'{}\');' \
'setsysparam(PN_Cycle(), cy_5s());setsysparam(pn_date(),inttodate({}));' \
'return nday({},"mdate",datetimetostr(sp_time()),"open",Open(),"high",High(),"low",Low(),"close",Close(),"vol",Vol(),"amount",Amount());' \
.format(code, date, 2880) # 240个正好是一天
result = (ts.RemoteExecute(sql, {}))[1]
if result:
for data in result:
temp = data[b'mdate'].decode('gbk').replace(
':', '').replace('-', '').replace(' ', '')
if temp[:4] == '2019' or temp[:4] == '2020':
print(data[b'vol'])
f.write('{},{},{},{},{},{},{},{}\n'.format(
code, temp, data[b'open'],
data[b'high'], data[b'low'],
data[b'close'], data[b'vol'],
data[b'amount']))
print('{},{},{},{},{},{},{},{}\n'.format(
code, temp, data[b'open'],
data[b'high'], data[b'low'],
data[b'close'], data[b'vol'],
data[b'amount']))
ts.Disconnect()
break
else:
print('天软未登录')
return 0
def get_index_members(self,
idx,
start_date=None,
end_date=None,
dates=None):
r = []
func = 'getBKMembers'
idx = "'%s'" % idx
for d in dates:
dd = d.strftime("%Y%m%d")
script_str = _gstr_from_func(func, [dd, idx])
data = tsl.RemoteExecute(script_str, {})
data = parse1DArray(data, "IDs", 1)
data['date'] = d
r.append(data)
data = pd.concat(r)
data['sign'] = 1
data['IDs'] = data['IDs'].str[2:]
return data.set_index(['date', 'IDs'])
def get_index_weight(self,
idx,
start_date=None,
end_date=None,
dates=None):
r = []
func = 'IndexWeightGet'
idx = "'%s'" % idx
for d in dates:
dd = d.strftime("%Y%m%d")
script_str = _gstr_from_func(func, [idx, dd])
data = tsl.RemoteExecute(script_str, {})
data = parse2DArray(data, ["IDs"])
r.append(data)
data = pd.concat(r)
data['IDs'] = data['IDs'].str[2:]
data['date'] = pd.DatetimeIndex(data['date'].astype('str'))
data['weight'] /= 100.0
return data.set_index(['date', 'IDs'])
def run_script(script, sysparams):
data = tsl.RemoteExecute(script, sysparams)
return data
def getData(sDate, intervel, sContract):
#intervel 表示数据间隔要5秒的，还是一分钟数据，还是日线数据 e.g "5s"
#contract 合约名称 e.g "sc"
try:
os.makedirs('E:\CLionProjects\HedgeArbitrage/data')
except:
pass
while True:
ts.ConnectServer('211.100.23.205', 443)
ts.LoginServer('fuchunguang', 'Fcg=888888')
if ts.Logined() == True:
print('already login')
daycount = 0
for code in sContract:
hdateInfo = {}
notNullcounter = 0
for date in sDate:
daycount += 1
if intervel == '1m':
temp1 = 'cy_1m()'
temp2 = 240
elif intervel == '5s':
temp1 = 'cy_5s()'
temp2 = 2880
sql = 'setsysparam(pn_stock(),\'{}\');' \
'setsysparam(PN_Cycle(), {});setsysparam(pn_date(),inttodate({}));' \
'return nday({},"mdate",datetimetostr(sp_time()),"open",Open(),"high",High(),"low",Low(),"close",Close(),"vol",Vol(),"amount",Amount());' \
.format(code, temp1,date, temp2)
result = (ts.RemoteExecute(sql, {}))[1]
if result:
for data in result:
temp = data[b'mdate'].decode('gbk').replace(
':', '').replace('-', '').replace(' ', '')
if len(temp) < 14:
temp += '0' * (14 - len(temp))
if int(sDate[0]) <= int(temp[:8]) <= int(
sDate[-1]):
notNullcounter += 1
hdateInfo[temp] = [
data[b'open'], data[b'high'], data[b'low'],
data[b'close'], data[b'vol'],
data[b'amount']
]
print(temp)
# print('{},{},{},{},{},{},{},{}\n'.format(code,
# temp,
# data[b'open'],
# data[b'high'],
# data[b'low'],
# data[b'close'],
# data[b'vol'],
# data[b'amount']))
if notNullcounter:
with open(
'E:\CLionProjects\HedgeArbitrage/data/{}_{}.js'.
format(code, intervel), 'w') as f:
json.dump(hdateInfo, f)
ts.Disconnect()
break
else:
print('not login ')
return 0
setsysparam(pn_date(),inttodate({0}));
return
Query("A股","",True,"","代码",DefaultStockID(),
"名称",CurrentStockName(),
"最高价",high(),
"最低价",low(),
"昨收",StockPrevClose3(),
"收盘价",close(),
"开盘价",open(),
"涨幅(%)",StockZf3(),
"换手率(%)",StockHsl3(),
"量比",volrate(),
"对数收益率(%)",StockLnZf3(),
"是否下跌",IsDown(),
"是否上涨",IsUp(),
"是否平盘",IsEqual(),
"指定日是否一字涨停({1})",StockIsZt2(42466),
"指定日是否一字跌停({2})",StockIsDt2(42437),
"是否涨停({3})",StockIsZt(38450),
"是否跌停({4})",StockIsDt(38450),
"是否曾经涨停({5})",StockIsCJZt(38450),
"是否曾经跌停({6})",StockIsCJDt(38450),
"最新总市值(万)",StockTotalValue3(),
"最新流通市值(万)",StockMarketValue3(),
"市盈率(最近12个月)(类型=0)",StockPE3_V(0),
"市盈率(专用)(类型=0)",StockPE3_IV(0),
"市净率(最新财务数据)",StockPNA3_II());
""".format(date, date, date, date, date, date, date)
d2 = TSLPy3.RemoteExecute(t1, {})[1]
df5 = change_code(pd.DataFrame(d2))
def getTickDetails(self, BegDate, EndDate, contract_code, symbol):
# tick_details = pd.DataFrame(self.tsbytestostr(ts.RemoteExecute( '''a:=select ["StockID"],["StockName"],["date"],["price"],["vol"],["amount"],["cjbs"],["yclose"],["syl1"],["syl2"],["buy1"],["sale1"],["bc1"],["sc1"] from tradetable datekey %s+21/24 to %s+16/24 of '%s' end;b:=update a set ['date']=datetimetostr(['date']) end;return a;''' % (
# ts.LocalCallFunc("StrToDate", [BegDate])[1], ts.LocalCallFunc("StrToDate", [EndDate])[1], contract_code),{}))[1])
# tick_details.to_csv('%s%s_%s.csv'%(path,symbol,EndDate),index=False)
# return tick_details
# tick_details = pd.DataFrame(self.tsbytestostr(ts.RemoteExecute(
# '''a:=select ["StockID"],["StockName"],["date"],["price"],["vol"],["amount"],["cjbs"],["yclose"],["syl1"],["syl2"],["buy1"],["sale1"],["bc1"],["sc1"] from tradetable datekey %s+21/24 to %s+16/24 of '%s' end;b:=update a set ['date']=datetimetostr(['date']) end;return a;''' % (
# ts.LocalCallFunc("StrToDate", [BegDate])[1], ts.LocalCallFunc("StrToDate", [EndDate])[1], contract_code),
# {}))[1])
if self.mc is None:
# self.mc = mongodb_client()
self.mc.dbConnect()
# dateList=self.getTradeDays(
# (datetime.datetime.strptime(BegDate, '%Y-%m-%d') - datetime.timedelta(3)).strftime('%Y-%m-%d'), EndDate)
# BegDateAdj=dateList[max(0,dateList.index(BegDate)-1)]
tick_dict = self.tsbytestostr(
ts.RemoteExecute(
'''a:=select ["StockID"],["StockName"],["date"],["price"],["vol"],["amount"],["cjbs"],["yclose"],["syl1"],["syl2"],["buy1"],["sale1"],["bc1"],["sc1"] from tradetable datekey %s to %s+0.9999999 of '%s' end;b:=update a set ['date']=datetimetostr(['date']) end;return a;'''
% (ts.LocalCallFunc("StrToDate", [BegDate])[1],
ts.LocalCallFunc("StrToDate", [EndDate])[1], contract_code),
{}))[1]
# datetime.datetime.strptime(tick_dict['date'][0], '%Y-%m-%d %H:%M:%S').replace(microsecond=500000)
last_tick_datetime = None
count = 0
for d in tick_dict:
tick = CtaTickData()
tick.vtSymbol = d['StockName']
tick.symbol = d['StockID'] # 合约代码 CF1705 StockID
# tick.exchange = EMPTY_STRING # 交易所代码
# 成交数据
tick.lastPrice = d['price'] # 最新成交价
tick.volume = d['vol'] #最新成交量
tick.amount = d['amount'] # 成交金额
tick.cjbs = d['cjbs'] # 周期内成交笔数
tick.yclose = d['yclose'] # 上一收盘价
tick.preSettlement = d['syl2'] # 上一日结算价
# tick的时间
# 转换为datetime格式
try:
if len(d['date']) > 10:
tick.datetime = datetime.datetime.strptime(
d['date'], '%Y-%m-%d %H:%M:%S') # python的datetime时间对象
else:
tick.datetime = datetime.datetime.strptime(
d['date'], '%Y-%m-%d')
tick.date = tick.datetime.strftime('%Y-%m-%d') # 日期
tick.time = tick.datetime.strftime('%H:%M:%S') # 时间
# tick.tradingDay = d['price'] # 交易日期
except Exception as ex:
# 抛弃本tick
print('日期转换错误:%s,error:%s' % (d['date'], ex))
continue
# 1档行情
tick.bidPrice1 = d['buy1']
tick.askPrice1 = d['sale1']
tick.bidVolume1 = d['bc1']
tick.askVolume1 = d['sc1']
# 修正毫秒
if tick.datetime.replace(microsecond=0) == last_tick_datetime:
# 与上一个tick的时间（去除毫秒后）相同,修改为500毫秒
tick.datetime = tick.datetime.replace(microsecond=500000)
tick.time = tick.datetime.strftime('%H:%M:%S.%f')
else:
tick.datetime = tick.datetime.replace(microsecond=0)
tick.time = tick.datetime.strftime('%H:%M:%S.%f')
# 记录最新tick的时间
last_tick_datetime = tick.datetime
if symbol == 'TC':
symbol = 'ZC'
if symbol == 'RO':
symbol = 'OI'
if symbol == 'ER':
symbol = 'RI'
if symbol == 'WS':
symbol = 'WH'
if symbol == 'ME':
symbol = 'MA'
self.mc.dbInsert('FUTURE_TICK_DB',
'TS_%s_TickDatas' % symbol,
d=tick.__dict__)
count = count + 1
print('写入合约%s完成，共%d条' % (contract_code, count))
return
def getTradeDays(self, start, end):
daysList = self.tsbytestostr(
ts.RemoteExecute(
"begt:=strtodate('%s');endt:=strtodate('%s');return datetostr(spec(specdate(nday3(tradedays(begt,endt),sp_time()),endt),'SH000001'));"
% (start, end), {}))
return daysList[1]
def update_rank(self):
tsl = tslFunctions()
tsl.tsl_login()
# conn_Local=create_engine(str(r"mysql+pymysql://root:xxxx@localhost:3306/test"))
conn_Local = create_engine(
str(r"mysql+pymysql://root:[email protected]:3306/test"))
self.__start=(dt.parser.parse(str(pd.read_sql("select max(截止日) from rank_raw",con=conn_Local\
).values[0][0]))+datetime.timedelta(days=1)).strftime('%Y-%m-%d')
tradedaysList = tsl.getTradeDays(self.__start, self.__end)
print(tradedaysList)
if len(tradedaysList) == 0:
print("已经更新至最新日期%s" % self.__end)
return
for tday in tradedaysList:
update_rank_raw = pd.DataFrame(
tsl.tsbytestostr(
ts.RemoteExecute(
f'''endt:={tday.replace('-','')+'T'};
X:=GetFuturesID('',endt);
r:=array();
for i:=0 to length(X)-1 do
begin
SetSysParam(PN_Stock(),x[i]);
Ret:= GetFuturesTradeRankingByDate(endt,t);
r&=t;
end;
return r;''', {})[1]))
if len(update_rank_raw) == 0:
print("No Datas")
else:
print(tday)
#can_l=update_cangdan_raw.截止日.unique()#记录下返回表的所有日期
update_rank_raw['utime'] = datetime.datetime.now()
update_rank_raw['机构简称2'] = update_rank_raw['机构简称'].apply(name)
update_rank_raw['code'] = update_rank_raw.代码.apply(
lambda x: re.split('\d+', x.upper())[0])
update_rank_raw.to_sql('rank_raw',
conn_Local,
if_exists='append',
index=False,
chunksize=5000)
#update_rank_raw.dropna(axis=1, inplace=True)
duo = update_rank_raw.loc[update_rank_raw.排名类型 == '持买单量排名']
kong = update_rank_raw.loc[update_rank_raw.排名类型 == '持卖单量排名']
rlt_duo = duo.groupby(['code', '截止日', '机构简称'],
as_index=False)['数量', '比上交易日增减'].sum()
rlt_duo['rename'] = rlt_duo['机构简称'].apply(name)
rank_duo = rankk(rlt_duo)
rank_duo.columns = [
'code', 'date', 'name', 'value', 'change', 'rename', 'rank'
]
rank_duo.to_sql('rank_long',
conn_Local,
if_exists='append',
index=False,
chunksize=5000)
rlt_kong = kong.groupby(['code', '截止日', '机构简称'],
as_index=False)['数量', '比上交易日增减'].sum()
rlt_kong['rename'] = rlt_kong['机构简称'].apply(name)
rank_kong = rankk(rlt_kong)
rank_kong.columns = [
'code', 'date', 'name', 'value', 'change', 'rename', 'rank'
]
rank_kong.to_sql('rank_short',
conn_Local,
if_exists='append',
index=False,
chunksize=5000)
ts.Disconnect()