def trdlist_format(self,
strat=None,
startlinenum=0,
date=None,
tablename=None,
outdir=None):
"""
生成标准交易单 ['code','name','num','multi','prc','val','tscost','inout'] ，此处tscost为金额
"""
if date is None:
date = dt.date.today()
if not tablename:
tablename = self.get_trdname(inputdate=date)
if not strat:
stratfilter = ''
else:
stratfilter = ''.join([' AND strat =\'', strat, '\''])
with da.DatabaseAssistant(dbdir=self._trd_dbdir) as trddb:
conn = trddb.connection
exeline = ''.join([
'SELECT code,name,num,multi,prc,val,tscost,inout FROM ',
tablename, ' WHERE row_id >',
str(startlinenum), stratfilter
])
trades = pd.read_sql(exeline, conn)
if not trades.empty:
trades['code'] = trades['code'].map(RawHoldingStocks.addfix)
trades['tscost'] = -np.abs(trades['val'] * trades['tscost'])
if outdir:
trades.to_csv(outdir, header=True, index=False)
else:
return trades
def list_pofval_gen(self,date = None,prctype = 'settle'):
""" 从标准数据库中读取持仓信息并写入文件 """
assert prctype in ('close','settle')
assetfut = '999997' if prctype=='settle' else '999998'
if date is None:
date = dt.datetime.today()
with da.DatabaseAssistant(dbdir=self._posi_db) as posidb:
conn = posidb.connection
stktb = '_'.join([self._pofname,'positions_stocks',date.strftime('%Y%m%d')])
exeline = ''.join(['SELECT code,num,multi,',prctype,' AS prc FROM ',stktb])
holdings = pd.read_sql(con=conn,sql=exeline)
pofval = holdings.loc[holdings['code']=='999999','num'].values[0]
if self._blog: # 有期货持仓
futtb = '_'.join([self._pofname,'positions_futures',date.strftime('%Y%m%d')])
exeline = ''.join(['SELECT code,num,multi,',prctype,' AS prc FROM ',futtb])
posifut = pd.read_sql(con=conn,sql=exeline)
holdings = holdings.append(posifut,ignore_index=True)
pofval += holdings.loc[holdings['code']==assetfut,'num'].values[0]
holdings = holdings[~holdings['code'].isin(gv.HOLD_FILTER)]
holdings = holdings[holdings['num']!=0]
holdings['code'] = holdings['code'].map(Products.addfix)
#### 写入文件 #######
holdings.to_csv(self._holdlst_dir,header=True,index=False)
with open(self._pofval_dir,'w') as pof:
pof.write(str(pofval))
print('[+]{0} : formatted holding list produced'.format(self._pofname))
def holdlist_format(self, titles, date=None, tablename=None, outdir=None):
""" 从数据库提取画图所需格式的持仓信息，tablename 未提取的表格的名称
存储为 DataFrame, 输出到 csv
titles 应为包含 证券代码 证券名称 证券数量 最新价格 的列表
需要返回字段 : code, name, num, multi, prc
"""
if date is None:
date = dt.datetime.today()
if not tablename:
tablename = self.get_holdname(inputdate=date)
with da.DatabaseAssistant(self._hold_dbdir) as holddb:
conn = holddb.connection
exeline = ''.join(
['SELECT ', ','.join(titles), ' FROM ', tablename])
holdings = pd.read_sql(exeline, conn)
holdings.columns = ['code', 'name', 'num', 'prc'] # 因此给的title只能有4个
# 剔除非股票持仓和零持仓代码 逆回购 理财产品等
holdings['code'] = holdings['code'].map(RawHoldingStocks.addfix)
holdings = holdings[~holdings['code'].isin(gv.HOLD_FILTER)]
holdings = holdings[holdings['num'] > 0]
if holdings.empty:
holdings = pd.DataFrame()
else:
holdings['multi'] = np.ones([len(holdings), 1])
#holdings['val'] = holdings['num']*holdings['prc']
holdings = holdings.sort_values(by=['code'], ascending=[1])
holdings = holdings.loc[:, [
'code', 'name', 'num', 'multi', 'prc'
]]
if outdir:
holdings.to_csv(outdir, header=True, index=False)
else:
return holdings
def get_newtables(self):
"""
提取已存储至数据库但还未处理 的 估值表的名称（数据库标准名称格式：产品代码_产品名称_日期）
"""
with da.DatabaseAssistant(dbdir=self._dbdir) as db:
temp = db.get_db_tablenames()
temp.remove('SAVED_TABLES')
savedtbs = set(temp)
with da.DatabaseAssistant(dbdir=self._netdbdir) as netdb:
cursor = netdb.connection.cursor()
netdb.create_db_table(tablename='PROCESSED_TABLES',
titles=['TableNames TEXT'],
replace=False)
temp = cursor.execute('SELECT * FROM PROCESSED_TABLES')
processedtbs = set([tb[0] for tb in temp])
newtables = sorted(list(savedtbs.difference(processedtbs)))
return newtables
def get_newtables(self):
"""
提取已下载但还未存入数据库 的 估值表的名称，为估值表原始名称 包含文件后缀
"""
currntbs = set(os.listdir(self._filedir)) # 文件夹中当前存储的估值表
with da.DatabaseAssistant(dbdir=self._dbdir) as db:
cursor = db.connection.cursor()
db.create_db_table(tablename='SAVED_TABLES',
titles=['TableNames TEXT'],
replace=False)
temp = cursor.execute('SELECT * FROM SAVED_TABLES').fetchall()
savedtbs = set([tb[0] for tb in temp])
newtables = sorted(list(currntbs.difference(savedtbs)))
return newtables
def get_totvalue(self,
titles,
date=None,
tablename=None,
othersource=None):
""" 提取 客户端软件 对应的总资产 """
if date is None:
date = dt.datetime.today()
if not tablename:
tablename = self.get_holdname(inputdate=date)
if not titles[0]: # 客户端持仓表格没有资产信息，需要从其他源（手填）提取
with open(othersource, 'r') as pof:
totval = float(pof.readlines()[0].strip())
else:
with da.DatabaseAssistant(dbdir=self._hold_dbdir) as holddb:
conn = holddb.connection
exeline = ''.join([
'SELECT ', ','.join(titles), ' FROM ',
tablename + '_summary'
])
values = conn.execute(exeline).fetchall()
totval = np.sum(values[0])
return totval
def table_to_db(self,
tbname_dict,
tabledir,
varstypes,
datemark='日期',
titlemark='科目代码',
omitchars='-%',
defaluttype='REAL',
replace=True):
"""
将估值表excel表格写入至数据库
tbname_dict 为估值表将在数据库中存储的名称，需在该方法外确认其是否已经更新过, 目前为包含rawname 和 tablename 的dict
vartypes 应给为可能包含的数据类型的字典
titlemark 需要能用做识别出标题行，同时识别出表格正文
replace 为 True 避免在同一张表格中多次写入
"""
hasdb = os.path.exists(self._dbdir) # 写入前数据库必须存在，数据库的建立在方法外实现
if not hasdb:
print('[-]Database does NOT exist, no update!')
return
self.table_info_check(tabledir=tabledir, datemark='日期') # 写入前需先检查估值表内容
rawtitles = self.get_table_titles(tabledir=tabledir,
titlemark=titlemark,
omitchars=omitchars) # 从估值表中提取初始标题
markpos = rawtitles.index(titlemark) # titlemark 所在的标题行的位置
titles = da.DatabaseAssistant.gen_table_titles(
titles=rawtitles, varstypes=varstypes,
defaluttype=defaluttype)['typed_titles'] # 标注了字段类型的标题
tablename = tbname_dict['tablename']
with da.DatabaseAssistant(dbdir=self._dbdir) as db:
db.create_db_table(tablename=tablename,
titles=titles,
replace=replace) # 创建表格
data = xlrd.open_workbook(tabledir)
table = data.sheets()[0]
# 寻找正表起始行
startline = -1
for dumi in range(table.nrows):
try:
# 检查首个元素类型，如果能转换为数值则为应该记录的行的起始行
int(table.row_values(dumi)[markpos])
except ValueError:
continue
else:
startline = dumi
break
if startline == -1: # 读完整个文件都没找到起始行则程序报错
raise Exception('[-]Can not find startline for table : %s' %
tablename)
# 已经找到正文起始行，开始写入数据库
cursor = db.connection.cursor()
try:
print('[*]Writing main body of the table...')
demonstrator = proshow.progress_demonstrator(table.nrows -
startline)
for dumi in range(startline, table.nrows): # 开始从正文行写入
exeline = ''.join([
'INSERT INTO ', tablename, ' VALUES (',
','.join(['?'] * len(titles)), ')'
])
cursor.execute(exeline, tuple(table.row_values(dumi)))
#db.connection.commit()
demonstrator.progress_show(currentnum=dumi - startline + 1,
title=tablename)
except: # 无论任何原因导致写入table失败，则都要删除未写完的table
print('[-]Writing table %s failed !' % tablename)
db.connection.execute(' '.join(['DROP TABLE', tablename]))
print('[+]Failed table %s dropped !' % tablename)
raise
else: # 如果表格正常完成更新，则需将其原始名称写入 SAVED_TABLES 数据表用作记录
cursor.execute('INSERT INTO SAVED_TABLES VALUES (?)',
(tbname_dict['rawname'], )) # 需要一个 tuple 作为输入
db.connection.commit() # 完成后再统一commit
print('[+]Writing table %s succed !' % tablename)
def trdlist_to_db(self,
textvars,
tabledir,
date=None,
tablename=None,
codemark='证券代码',
replace=False):
""" 将一张软件端导出的 交易/委托表格 更新至数据库
textvars 存储数据库格式为TEXT的字段
codemark 用于标识正表表头
tablename 表格存储在数据库中的名称
已经写入数据库的行将不再写入,需要确定数据表格按交易时间排序
"""
if date is None:
date = dt.date.today()
if not tablename:
tablename = self.get_trdname(inputdate=date)
with da.DatabaseAssistant(dbdir=self._trd_dbdir) as trddb:
conn = trddb.connection
c = conn.cursor()
with open(tabledir, 'r') as fl:
rawline = fl.readline()
foundtitle = False
linecount = 0
processed_lines = 0
while rawline:
line = rawline.strip().split(',')
if not foundtitle: # 寻找到正文开始标题后才开始写入
if codemark in line: #寻找正表标题
titles = line
codepos = titles.index(codemark) + 1
titlecheck = da.DatabaseAssistant.gen_table_titles(
titles=titles, varstypes={'TEXT': textvars})
titletrans = ['row_id INTEGER'
] + titlecheck['typed_titles']
titlelen = len(titletrans)
# title_empty = titlecheck['empty_pos'] # 此处尤其暗藏风险，假设正表数据没有空列
newcreated = trddb.create_db_table(
tablename=tablename,
titles=titletrans,
replace=replace)
if not newcreated: # 读取已经存储过的行数
processed_lines = c.execute(''.join([
'SELECT COUNT(', codemark, ') FROM ',
tablename
])).fetchall()[0][0]
rawline = fl.readline()
foundtitle = True
continue
else: # 已经找到了标题
linecount += 1
if linecount > processed_lines: # 只在有比数据库已存储的数据行更多的行提供时才继续写入
line = [linecount] + line
exeline = ''.join([
'INSERT INTO ', tablename, ' VALUES (',
','.join(['?'] * titlelen), ')'
])
c.execute(exeline, line)
conn.commit()
rawline = fl.readline()
print('%d lines updated to trddb with table %s \n' %
(linecount - processed_lines, tablename))
def trdlog_to_db(self, tscost, date=None, tablename=None):
"""
从交易记录读取信息并写入数据库,同时生成标准格式也写入数据库(注：此处tscost为比率)
写入数据库，因为数据比较少（100档才100行），因为每次都全部写入
需要考虑包含灵活换月的情况
"""
if date is None:
date = dt.date.today()
if tablename is None:
tablename = self.get_trdname(date)
trdlogname = '_'.join([tablename, 'trdlog'])
fulllog = pd.DataFrame()
stdtable = []
for strat in self._logdir:
stratinfo = strat.split('_')
cttype = stratinfo[1].upper()
montype = stratinfo[0]
contracts = RawHoldingFutures.get_contracts_ours(date=date,
cttype=cttype)
nvolumn = int(
open(os.path.join(self._cwdir[strat],
'nVolume.txt')).readline()[0].strip())
multi = self.multi_dict[cttype]
logdir = os.path.join(
self._logdir[strat], 'tradelog',
''.join(['tradelog_',
date.strftime('%Y%m%d'), '.txt']))
if not os.path.exists(logdir):
continue
with open(logdir) as fl:
line = fl.readline()
trdlogs = []
namefull = False
names = ['date', 'time', 'trade_action']
while line:
############# 生成可存入数据库格式 #############
vars = line.strip().split()
monthchg = len(vars) == 16
if monthchg: # 灵活换月
startnum = 5
else:
startnum = 3
trdlog = vars[:3]
trdlog += [v.split('=')[1] for v in vars[startnum:]]
if not namefull:
names += [v.split('=')[0] for v in vars[startnum:]]
namefull = True
if not monthchg: # 考虑到有肯能在灵活换月当天还会有交易，需要确保每行等长（灵活换月还包含nextqhprice）
trdlog.insert(5, 'NaN')
if len(names) < 14:
names.insert(5, 'nextqhprice')
trdlogs.append(trdlog)
############# 生成标准格式 #############
sn = int(trdlog[3])
prc = float(trdlog[6])
if '开仓' in trdlog[2]:
code = contracts[montype]
num = -nvolumn
val = prc * num * multi
inout = 'in'
stdtable.append([
strat, code, code, num, multi, prc, val, tscost,
inout, sn
])
elif '平仓' in trdlog[2]:
code = contracts[montype]
num = nvolumn
val = prc * num * multi
inout = 'out'
stdtable.append([
strat, code, code, num, multi, prc, val, tscost,
inout, sn
])
elif '换仓' in trdlog[2]:
real_contracts = RawHoldingFutures.get_contracts_real(
date=date, cttype=cttype)
code = real_contracts[montype]
num = nvolumn
val = prc * num * multi
inout = 'out'
stdtable.append([
strat, code, code, num, multi, prc, val, tscost,
inout, sn
])
if montype == 'near1': #远月换月还未定
code2 = real_contracts['near2']
prc2 = float(trdlog[5])
val2 = -num * prc2 * multi
stdtable.append([
strat, code2, code2, -num, multi, prc2, val2,
tscost, 'in', sn
])
else:
raise Exception('Unrecognized trade_action!')
line = fl.readline()
fulllog = fulllog.append(pd.DataFrame(trdlogs, columns=names),
ignore_index=True)
############ 写入 数据库 ################
with da.DatabaseAssistant(dbdir=self._trd_dbdir) as trddb:
conn = trddb.connection
fulllog.to_sql(name=trdlogname, con=conn, if_exists='replace')
stdtable = pd.DataFrame(stdtable,
columns=[
'strat', 'code', 'name', 'num',
'multi', 'prc', 'val', 'tscost',
'inout', 'sn'
])
stdtable['row_id'] = stdtable.index + 1
stdtable.to_sql(name=tablename, con=conn, if_exists='replace')
def trdlist_format(self,
titles,
tscostrate,
startlinenum=0,
date=None,
tablename=None,
outdir=None):
""" 从数据库提取画图所需格式的 交易 信息，tablename 未提取的表格的名称
存储为 DataFrame, 输出到 csv
titles 应为包含 的列表
需要返回字段 : code, name, num, prc,val,tscost,inout
做多 数量为正、金额为正， 做空为负、金额为负， 价格恒正, 交易成本恒为负
"""
tofilter = ['131810', '204001']
if date is None:
date = dt.date.today()
if not tablename:
tablename = self.get_trdname(inputdate=date)
def marktrans(x):
if '买' in x:
return 'in'
elif '卖' in x:
return 'out'
else:
raise Exception('Error in trdlist_format')
def reverseval(x):
if x == 'out':
return -1
elif x == 'in':
return 1
else:
raise Exception('Error in trdlist_format')
def tsratecalc(x):
if x == 'out':
return tscostrate + 1 / 1000 # 卖出会有印花税
else:
return -tscostrate
with da.DatabaseAssistant(dbdir=self._trd_dbdir) as trddb:
conn = trddb.connection
exeline = ''.join([
'SELECT ', ','.join(titles), ' FROM ', tablename,
' WHERE row_id >',
str(startlinenum)
])
trades = pd.read_sql(exeline, conn)
trades.columns = ['code', 'name', 'num', 'prc', 'inout']
# 剔除非股票持仓和零持仓代码
trades = trades[~trades['code'].isin(tofilter)]
trades['code'] = trades['code'].map(RawHoldingStocks.addfix)
trades = trades[trades['num'] > 0]
trades['inout'] = trades['inout'].map(marktrans)
trades['num'] = trades['num'] * trades['inout'].map(reverseval)
trades['val'] = trades['num'] * trades['prc']
trades['tscost'] = trades['val'] * trades['inout'].map(tsratecalc)
trades['multi'] = 1
trades = trades.sort_values(by=['code'], ascending=[1])
trades = trades.ix[:, [
'code', 'name', 'num', 'multi', 'prc', 'val', 'tscost', 'inout'
]]
if outdir:
trades.to_csv(outdir, header=True, index=False)
else:
return trades
def table_to_netdb(self,
tablename,
codedict,
indexmark='科目代码',
defaultvalcols=('市值', '市值本币')):
"""
从已经存储到数据库中的估值表中提取计算净值所需的基础元素，更新至netdb中
更新前需检查 PROCESSED_TABLES 中是否已经拥有了该表格，已经存在的话就不能写入，报错
tablename 为需要更新的表格名称 在估值表数据库中的名称 标准名称
codedict 为存储必须提取的行的字典，如果估值表中没有该行的话则其值应该为零
indexmark 为行标记，标记存储codedict需要提取的字段的列
valcols 可能作为存储数值的列的名称通常为 [市值 、市值本币]
"""
# 从数据库中的估值表中提取需要的字段的值，并赋予标准化名称
if not codedict:
print('[-]Codedict is empty can not update table : %s' % tablename)
output_dict = {} # 存储提取出的数值的字典
rev_codedict = {} # 以行名(indexmark列对应的值)为KEY
valcols = [indexmark]
for cd in codedict: # 初始化输出字典
output_dict[cd] = 0
cditem = codedict[cd]
if len(cditem) > 1:
valcols.append(cditem[1]) # 把非默认的列提取出来
rev_codedict[cditem[0]] = (cd, cditem[1])
else:
rev_codedict[cditem[0]] = (cd, )
#rev_codedict = {v[0]:k for k,v in codedict.items()} # 以行名为KEY，对应要素表列名为值的字典
# 提取数据
with da.DatabaseAssistant(self._dbdir) as db:
cols = db.get_table_cols(tablename)
# 匹配用作存储的值，同一只产品有些会是市值 、有些市值本币 所以需要逐一匹配
valmark = None
for col in defaultvalcols:
if col in cols:
valmark = col
break
if not valmark:
raise Exception('[-]在表格 %s 中没有匹配到存储数值的列' % tablename)
else:
valcols.insert(1, valmark)
cursor = db.connection.cursor()
exeline = ''.join(
['SELECT ', ','.join(valcols), ' FROM ', tablename])
rowvals = cursor.execute(exeline).fetchall() # 提取 indexmark 列对应的值
for row in rowvals:
rowname = row[0].strip()
if rowname in rev_codedict:
item = rev_codedict[rowname]
itemcol = item[1] if len(item) > 1 else valmark
output_dict[item[0]] = row[valcols.index(itemcol)]
# 写入 netdb 元素表
filedate = tablename[-8:]
with da.DatabaseAssistant(self._netdbdir) as netdb:
base_titles = ['date TEXT']
base_values = [filedate]
for cd in sorted(output_dict):
base_titles.append(' '.join([cd, 'REAL']))
base_values.append(output_dict[cd])
netdb.create_db_table(tablename='Net_Values_Base',
titles=base_titles,
replace=False)
try: # 开始写入净值基础数据
cursor = netdb.connection.cursor()
exeline = ''.join([
'INSERT INTO Net_Values_Base VALUES (',
','.join(['?'] * len(base_titles)), ')'
])
cursor.execute(exeline, tuple(base_values))
netdb.connection.commit()
except:
print('[-]Update Net_Values_Base with table %s failed !' %
tablename)
raise
else:
cursor.execute('INSERT INTO PROCESSED_TABLES VALUES (?)',
(tablename, )) # 需要一个 tuple 作为输入
netdb.connection.commit()
print('[+]Update Net_Values_Base with table %s succed !' %
tablename)
def generate_netvalues(self,earnvars = ('earn')):
""" 从头计算净值表，包括全部日期（包含非交易日） 暂时未考虑融券情况 """
digits = self.precision
confirmdays = self.confirmdays
w.start()
with da.DatabaseAssistant(self.netdbdir) as netdb:
conn_net = netdb.connection
basedata = pd.read_sql('SELECT * FROM Net_Values_Base',conn_net) #提取基础数据
sorteddata = basedata.sort_values(['date'],ascending=[1]) # 将数据根据日期排序，防止有些估值表发送较晚，更新靠后的情况
# 检查缺失交易日期，如果有缺失则只生成至缺失交易日前一（交易）日； 有交易日就应该有估值表，反之不然
alldates = sorteddata['date']
firstdate = alldates.values[0] # 基础数据起始日 须确保基础数据中没有重复项
lastdate = alldates.values[-1] # 基础数据结束日
tdays = w.tdays(firstdate,lastdate).Times # 期间交易日
trddays = pd.DataFrame([dt.datetime.strftime(t,'%Y%m%d') for t in tdays])
misstrd = ~trddays.isin(alldates.values)
trdmiss = trddays[misstrd.values] # 期间缺失的交易日
if not trdmiss.empty:
# 截止到第一个缺失交易日前的一个交易日
#cutdate=dt.datetime.strftime(w.tdaysoffset(-1,trdmiss.values[0][0]).Times[0],'%Y%m%d')
cutdate = dt.datetime.strftime(w.tdaysoffset(-1,trdmiss.values[0][0]).Times[0],'%Y%m%d')
cutpos = alldates<=cutdate # 需要提取的日期的位置
sorteddata = sorteddata[cutpos]
dates = sorteddata['date']
datenum = dates.__len__()
# 处理 融券的字段
if 'secloan' not in sorteddata.columns:
sorteddata['secloan'] = 0
if 'fee_secloan' not in sorteddata.columns:
sorteddata['fee_secloan'] = 0
# 计算实际付费 出入金 -- 费用为每日计提，累计显示，因而是逐渐增加的，如果一次减小说明当日支付上一付费季度的费用
fees = -(sorteddata.loc[:,['fee_service','fee_keep','fee_management','fee_secloan']+earnvars].diff()) #正常日计提 作差为正数，实际支付 作差为负数，取反则可以找到实际支付
fees[fees<0] = 0 # 将正常每日费用计提设为0
fees.iloc[0,:] = 0 # diff 导致第一行为NaN 需要填补为0
paid=fees.sum(axis=1) # 将每日各项费用加总，计算每日（账面）支付金额
################### 开始计算净值 #######################
netreal=sorteddata['assetnet']/sorteddata['sharenum'] # 计算真实(单位)净值
# 计算将业绩提成补回后的（累计）净值收益率
bookearns = sorteddata.loc[:,earnvars].diff() # 账面业绩提成，不是实际支付的 与fees['earn'] 是不一样的！！！
bookearns.iloc[0,:] = 0
bookearns[bookearns<0] = 0
bookearn = bookearns.sum(axis=1)
net_addearn = (sorteddata['assetnet']+bookearn)/sorteddata['sharenum']
cumret = net_addearn.values[1:]/netreal.values[:-1]-1
cumret = np.row_stack([np.zeros([1,1]),np.reshape(cumret,[datenum-1,1])])
netcum = np.cumprod(1+cumret)*netreal[0] # 计算补回业绩提成后的（累计）单位净值,并确保初始值与单位净值初始值对齐
# 在有申购赎回的时候需要采取平滑处理
sharechg = sorteddata['sharenum'].diff() # 计算份额变动差异 >0 为由申购， <0 为有赎回，有可能申赎数量相同，但不影响金额
sharechg[0] = 0
# 和前一个数值相比, 确定 confirm date 交易确认日 (T+C) 的位置
idxchg_TC = sharechg!=0 # type: pd.DataFrame
chgpos_TC = idxchg_TC[idxchg_TC].index # 变动位置的序号
chgdate = dates[chgpos_TC].values # 变动位置的对应的日期
opendate = [w.tdaysoffset(-confirmdays,c).Times[0].strftime('%Y%m%d') for c in chgdate]
openidx = dates.isin(opendate) # 开放日位置
inout = np.zeros_like(netreal)
inout[chgpos_TC] = np.round(netreal[openidx].values,digits)*sharechg[chgpos_TC].values # 将单位净值round到指定位置，乘以分额变动计算出在开放日申购赎回的金额
# 提取 开放日 到 确认日 期间的日期（TCm1）对应的位置，并在相应位置对应上申购赎回的金额
inout2 = np.zeros_like(netreal)
idxchg_TCm1 = np.zeros_like(netreal)
opennum = 0
opentot = opendate.__len__()
for dumi in range(datenum): # 将日期一个个与开放日比对
if opennum>=opentot: # 已经找到的开放日数量超过了所有 开放日数量，说明已经完成，可以退出了
break
mydt = dates.values[dumi]
if mydt>opendate[opennum] and mydt<chgdate[opennum]:
inout2[dumi] = inout[chgpos_TC[opennum]]
idxchg_TCm1[dumi] = 1
elif mydt>=chgdate[opennum]:
opennum += 1
amtchg = np.zeros_like(netreal)
comptot = sorteddata['assettot']-sorteddata['shares_sell']-sorteddata['secloan'] # 资产总额扣除应付赎回款\融券金额，总资产不包含已经支付的费用，需要加回
amtchg[1:] = comptot.values[1:]-comptot.values[:-1]+paid.values[1:]-inout[1:] # 每日资金变动金额，补回费用，剔除申购赎回
# 计算平滑后的 补回全部费用的 收益率
rets = np.zeros_like(netreal)
numerator = (comptot.values + paid.values + inout2)[1:] # 分子与确认日对齐
denominator = comptot.values[:-1]+(inout2+inout)[1:]
rets[1:] = numerator/denominator-1
level = 0.1 # 异常收益情况检查
assert all(rets<level) and all(rets>-level) ,'abnormal return value, check data !'
netvals = pd.DataFrame(np.column_stack([dates.values,netreal,netcum,np.cumprod(1+rets)*netreal[0],rets,amtchg,np.cumsum(amtchg)]),
columns=['Date','NetSingle','NetCumulated','NetCompensated','CompReturns','AmtChg','AmtCumChg'])
sql.to_sql(netvals,name='Net_Values',con=conn_net,if_exists='replace',index=False)
print(' '.join(['%s : Netvalues updated from' %self.pname,firstdate,'to',dates.values[-1]]))
w.close()
def holdlist_to_db(self,
textvars,
tabledir,
date=None,
tablename=None,
codemark='证券代码',
replace=True,
currencymark='币种'):
""" 将一张软件端导出的 持仓表格 更新至数据库
textvars 存储数据库格式为TEXT的字段
currencymark 用于识别汇总情况表头 目前只有通达信终端才有 若找不到就不建立表格
codemark 用于标识正表表头
tablename 表格存储在数据库中的名称
"""
if date is None:
date = dt.datetime.today()
if not tablename:
tablename = self.get_holdname(inputdate=date)
with da.DatabaseAssistant(dbdir=self._hold_dbdir) as holddb:
conn = holddb.connection
c = conn.cursor()
with open(tabledir, 'r') as fl:
rawline = fl.readline()
startwrite = False
summary = False
newtb = False
while rawline:
line = rawline.strip().split(',')
if not startwrite:
if currencymark: # 在找到详细数据之前会先查找汇总,如果不需要汇总则直接寻找标题
if not summary: # 检查持仓汇总部分
if currencymark in line: #寻找汇总标题
stitles = line
currpos = stitles.index(currencymark)
stitlecheck = da.DatabaseAssistant.gen_table_titles(
titles=stitles,
varstypes={'TEXT': (currencymark, )})
stitletrans = stitlecheck['typed_titles']
stitle_empty = stitlecheck['empty_pos']
stitlelen = len(stitletrans)
holddb.create_db_table(
tablename=tablename + '_summary',
titles=stitletrans,
replace=True)
rawline = fl.readline()
summary = True
continue
else:
if line[currpos] == '人民币': # 读取人民币对应的一行
exeline = ''.join([
'INSERT INTO ', tablename + '_summary',
' VALUES (',
','.join(['?'] * stitlelen), ')'
])
newline = []
for dumi in range(len(line)):
if not stitle_empty[dumi]:
newline.append(line[dumi])
c.execute(exeline, newline)
conn.commit()
#寻找正表标题
if codemark in line:
titles = line
titlecheck = da.DatabaseAssistant.gen_table_titles(
titles=titles, varstypes={'TEXT': textvars})
titletrans = titlecheck['typed_titles']
# title_empty = titlecheck['empty_pos'] # 此处尤其暗藏风险，假设正表数据没有空列
newtb = holddb.create_db_table(tablename=tablename,
titles=titletrans,
replace=replace)
rawline = fl.readline()
startwrite = True
if not newtb: # 表格已经存在就不必再写入
break
continue
elif startwrite and newtb: # 在已找到正文并且表格是新建的情况下才开始写
exeline = ''.join([
'INSERT INTO ', tablename, ' VALUES (',
','.join(['?'] * len(line)), ')'
])
c.execute(exeline, line)
conn.commit()
rawline = fl.readline()
if startwrite and newtb: #实现写入并退出循环
print('Table ' + tablename + ' updated to database !')
elif startwrite and not newtb:
print('Table ' + tablename + ' already in the database !')
else: # 未能实现写入
print('Table ' + tablename +
' cannot read the main body, nothing writen !')