dictDataSpec['freq'] = '1day'
listDictDataSpec = Utils.sweepParam(
dictDataSpec, Utils.dictStrategyParamRange[strParamSweep])
########################################################
# back test each data spec
########################################################
for nSpec, dictDataSpec in enumerate(listDictDataSpec):
print nSpec, len(listDictDataSpec), dictDataSpec['strCase']
strFileAddress = Utils.dirResultPerCase + dictDataSpec[
'strCase'] + '.pickle'
if os.path.exists(strFileAddress):
continue
#dfAll = Utils.getTradingDataPoint_Commodity(dictDataSpec).replace(np.inf, np.nan).dropna() # Error was here 20171012, fb.dce last none-NA data is 20170929
dfAll = Utils.getTradingDataPoint_Commodity(dictDataSpec).replace(
np.inf, np.nan)
dtLastObservation = dfAll.index[-1]
#dtEnter = dtLastObservation + datetime.timedelta(1, 0)
#if dtEnter.weekday() >= 5:
# dtEnter = dtEnter + datetime.timedelta(3, 0)
dtEnter = Utils.UtilsDB.dtTomorrow
rowLast = dfAll.ix[dtLastObservation]
rowLast.name = dtEnter
dfAll = dfAll.append(rowLast)
#dfAll = dfAll[dfAll['Volume']!=0]
dictDataSpec['df'] = dfAll
# set time start, end, step
dtDataStart = dfAll.index[0]
dtDataEnd = dfAll.index[-1]
def funcTopPort(strParamSweep):
# dtBackTestStart & dtBackTestEnd
dtBackTestStart = Utils.dtBackTestStart
#dtBackTestEnd = Utils.dtBackTestEnd
#########################################################
# read backtest result of rquired cases
#########################################################
# read stored results
listDictPerTopPort = Utils.getFileAddressForTopPort(strParamSweep)
# read dfPCTAll, dfVolatilityAll
print 'reading data'
#dictDataSpec = dict(Utils.dictDataSpecTemplate)
#dictDataSpecCase = listDictPerTopPort[0]
#dictDataSpec['strModelName'] = dictDataSpecCase['strModelName'][0]
#if 'freq' in dictDataSpecCase.keys():
# dictDataSpec['freq'] = dictDataSpecCase['freq'][0]
dictDataSpec = Utils.extractElementFromList(listDictPerTopPort[0])
listSPCT = []
listVolatility = []
for Secu in dictDataSpec['Secu']:
dictDataSpec['Secu'] = Secu
dfAll = Utils.getTradingDataPoint_Commodity(dictDataSpec).replace(
np.inf, np.nan)
dfAll = dfAll[['Open', 'High', 'Low', 'Close', 'Volume']]
dfAll = dfAll.dropna()
dtLastObservation = dfAll.index[-1]
dtEnter = dtLastObservation + datetime.timedelta(1, 0)
if dtEnter.weekday() >= 5:
dtEnter = dtEnter + datetime.timedelta(3, 0)
rowLast = dfAll.ix[dtLastObservation]
rowLast.name = dtEnter
dfAll = dfAll.append(rowLast)
if 'PCT' not in dfAll.columns:
dfAll['PCT'] = dfAll['Close'].pct_change()
sPCT = dfAll['PCT']
sPCT.name = Secu
listSPCT.append(sPCT)
sValueDaily = UtilsPortAsync.funcResampleDaily(dfAll['Close'])
sVolatility = sValueDaily.pct_change().rolling(40).std().shift(1)
sVolatility.name = Secu
listVolatility.append(sVolatility)
dfPCTAll = pd.concat(listSPCT, axis=1)
dfVolatilityAll = pd.concat(listVolatility, axis=1)
print 'iterate listDictPerTopPort'
for dictPerTopPort in listDictPerTopPort:
print dictPerTopPort
#########################################################
# prepare directory for saving
#########################################################
strDirParamSweep = Utils.dirResultPerCase + '/' + strParamSweep
if os.path.exists(strDirParamSweep) is False:
os.mkdir(strDirParamSweep)
strDirAddressCase = strDirParamSweep + '/' + dictPerTopPort[
'strCase'] + '/'
if os.path.exists(strDirAddressCase) is False:
os.mkdir(strDirAddressCase)
strFileAddressPrefix = strDirAddressCase
# save config file of the port
dictPerTopPortOut = dict(
(key, value[0]) for key, value in dictPerTopPort.iteritems()
if key not in ['strCase', 'listFileAddress', 'Secu'])
pd.Series(dictPerTopPortOut).to_pickle(strFileAddressPrefix + '/' +
'portconfig.pickle')
listDict = []
dictDTEnter = {}
dictIndicator = {}
for strFileAddress in dictPerTopPort['listFileAddress']:
pat = re.compile(r'.*?Secu([a-z]+\.[a-z]+).*?')
Secu = pat.match(strFileAddress).groups()[0]
sPCT = dfPCTAll[Secu]
sVolatility = dfVolatilityAll[Secu]
sVolatility.name = Secu
# dictOne
dictOne = dict(dictPerTopPort)
del dictOne['strCase']
del dictOne['Secu']
del dictOne['listFileAddress']
dictOneNew = {}
for k, v in dictOne.iteritems():
dictOneNew[k] = v[0]
dictOne = dictOneNew
#########################################################
# calculate performance for each Secu
#########################################################
# read indicator
try:
strategy = pd.read_pickle(strFileAddress)['strategy']
except:
strToLog = 'unreadable file:\n%s' % strFileAddress
logging.log(logging.ERROR, strToLog)
continue
sIndicator = strategy.df['indicator']
dictIndicator[Secu] = sIndicator
# calculate PCT daily, compatible with 1min back test
sPCTHoldMin = sPCT * sIndicator
sPCTHoldMin.name = Secu
sValueMin = (1 + sPCTHoldMin).cumprod()
sValueDaily = UtilsPortAsync.funcResampleDaily(sValueMin)
sPCTDaily = sValueDaily.pct_change()
dictMetric = Utils.funcMetric(sPCTDaily)
dictOne.update(dictMetric)
sPCTDaily.name = Secu
# find index Enter to 1) calculate trading cost 2) determine position when entering
sIndexBoth = strategy.indexLongEnter.append(
strategy.indexShortEnter)
listIndexEnter = set(sIndexBoth.date).intersection(
set(sPCTDaily.dropna().index.date.tolist()))
listIndexEnter = np.sort(list(listIndexEnter))
dictDTEnter[Secu] = listIndexEnter
sPCTDaily.ix[listIndexEnter] = sPCTDaily.ix[
listIndexEnter] - Utils.COMMISSION * 2
dictOne['sPCTDaily'] = sPCTDaily
# determine position
# here, the lambda x: 0.005/max(0.001, x) is to make sure any single product does not dominant the portfolio value
sPosition = sVolatility.apply(lambda x: np.nan)
sPosition.ix[listIndexEnter] = sVolatility.ix[
listIndexEnter].apply(lambda x: 0.005 / max(0.001, x))
sPosition = sPosition.sort_index()
sPosition = sPosition.ffill()
sPCTDaily = sPCTDaily * sPosition
sPosition.name = Secu
# no position when sIndicator=0
dictOne['sPosition'] = sPosition
dictOne['sVolatility'] = sVolatility
dictOne['listIndexEnter'] = listIndexEnter
#
listDict.append(dictOne)
# below are the dataframe for all products in a case
dfResult = pd.DataFrame(listDict)
dfPCT = pd.concat(dfResult['sPCTDaily'].values.tolist(), axis=1)
dfPosition = pd.concat(dfResult['sPosition'].values.tolist(), axis=1)
#########################################################
# for interday strategy, only consider the daily return
#########################################################
if dictPerTopPort['freq'][0] != '1day':
print 'not suitable for daily update'
# fill na after first trading day
for SecuCode in dfPCT.columns:
ixFirstTradingDay = dfPCT[SecuCode].dropna().index[0]
dfPCT.ix[(dfPCT.index >= ixFirstTradingDay) &
(dfPCT[SecuCode].isnull()), SecuCode] = 0
# adjust position
sNSecu = Utils.generateNSecu()
sNSecu = sNSecu.apply(lambda x: np.sqrt(x)) * 2
for strSecu in dfPosition.columns:
dfPosition[strSecu] = dfPosition[strSecu] / sNSecu
# calculate daily return
seriesDailyReturn = (dfPCT.fillna(0) * dfPosition).sum(1)
seriesDailyPosition = dfPosition.mean(1)
dictDataSpec = Utils.extractElementFromList(dictPerTopPort)
listToRemove = [
'strCloseAtDayEnd', 'strCase', 'Secu', 'listFileAddress',
'strMethodTrend', 'strModelName'
]
listParam = list(
set(dictPerTopPort.keys()).difference(set(listToRemove)))
# push to database
df = pd.concat([seriesDailyReturn, seriesDailyPosition], axis=1)
df = df.reset_index()
df.columns = ['trade_date', 'PCT', 'Position']
df['code'] = dictDataSpec['strModelName']
for strColumn in listParam:
if strColumn in dictDataSpec.keys():
df[strColumn] = dictDataSpec[strColumn]
else:
df[strColumn] = np.nan
strTB = dictDataSpec['strModelName']
strTB = Utils.UtilsDB.strTBPrefixPerformance + strTB
listColumnIndex = ['trade_date', 'code'] + listParam
Utils.UtilsDB.saveTB_DAILY(Utils.UtilsDB.DB_NAME_PERFORMANCE,
strTB, df, listColumnIndex)
continue
#########################################################
# adjust the position according to the number of all products available
#########################################################
sNSecuRaw = Utils.generateNSecu()
sNSecu = sNSecuRaw
sNSecu = sNSecuRaw.apply(lambda x: np.sqrt(x)) * 2
# Faith Position
for strSecu in dfPosition.columns:
dfPosition[strSecu] = dfPosition[strSecu] / sNSecu
pass
seriesDailyPosition = dfPosition.sum(1)
seriesDailyPosition.name = 'Position'
#########################################################
# calculate number of contracts for a product
#########################################################
# if some secu's weight is smaller than one contract, then do not trade
TotalMoney = Utils.TOTALMONEY
dfPositionDollar = dfPosition * TotalMoney
listPositionContract = []
listPositionDirection = []
listPositionDecimal = []
for SecuCode in dfPositionDollar.columns:
sPositionDollar = dfPositionDollar[SecuCode]
#sSettleRaw = Utils.dfExe.ix[SecuCode]['SettleRaw'].shift(1).ix[dictDTEnter[SecuCode]]
sSettleRaw = Utils.getDFOneProduct(
SecuCode, ['SettleRaw']).shift(1).ix[dictDTEnter[SecuCode]]
sSettleRaw.name = SecuCode
sNominalContract = sSettleRaw * Utils.dfDetail.ix[SecuCode,
'multiplier']
sPositionContract = sPositionDollar / sNominalContract.ix[
sPositionDollar.index].ffill()
sPositionContract = sPositionContract.apply(lambda x: np.round(x))
sPositionContract.name = SecuCode
listPositionContract.append(sPositionContract)
sIndicator = dictIndicator[SecuCode]
sPositionDirection = sPositionContract * sIndicator.ix[
sPositionContract.index].ffill()
sPositionDirection.name = SecuCode
listPositionDirection.append(sPositionDirection)
sPositionDecimal = sPositionContract * sNominalContract / TotalMoney
sPositionDecimal = sPositionDecimal * sIndicator.ix[
sPositionDecimal.index].ffill()
sPositionDecimal = sPositionDecimal.ffill()
sPositionDecimal.name = SecuCode
listPositionDecimal.append(sPositionDecimal)
dfPositionContract = pd.concat(listPositionContract, axis=1)
dfPositionDirection = pd.concat(listPositionDirection, axis=1)
dfWeightDirection = pd.concat(listPositionDecimal, axis=1)
# calculate daily return as in WH
listEquityDeltaDaily = []
for SecuCode in dfPositionDirection.columns:
listColumn = [
'OpenRaw', 'CloseRaw', 'SettleRaw', 'DeltaSettle',
'Open-PreSettle', 'Settle-Open'
]
dfTrading = Utils.getDFOneProduct(SecuCode, listColumn)
dfTrading = dfTrading.rename({
'OpenRaw': 'Open',
'CloseRaw': 'Close',
'SettleRaw': 'Settle'
})
# contracts in and out
sPositionDirection = dfPositionDirection[SecuCode]
sPositionDirectionDiff = sPositionDirection.diff()
sPositionIn = sPositionDirectionDiff.copy()
sPositionOut = -sPositionDirectionDiff.copy()
sPositionIn.ix[sPositionDirection.abs() < sPositionDirection.shift(
1).abs()] = 0
sPositionOut.ix[sPositionDirection.abs() >
sPositionDirection.shift(1).abs()] = 0
# calculate daily equity delta
sEquity = sPositionDirection * dfTrading[
'DeltaSettle'] - sPositionIn * dfTrading[
'Open-PreSettle'] + sPositionOut * dfTrading[
'Open-PreSettle']
sEquity = sEquity * Utils.dfDetail.ix[SecuCode, 'multiplier']
sEquity.name = SecuCode
listEquityDeltaDaily.append(sEquity)
dfEquityDeltaDaily = pd.concat(listEquityDeltaDaily, 1)
dfEquityDeltaDaily['Total'] = dfEquityDeltaDaily.sum(1)
# calculate daily equity PCT
sEquityPCT = dfEquityDeltaDaily['Total'] / Utils.TOTALMONEY
#########################################################
# output
#########################################################
#'''
# daily value & DD
seriesDailyReturn = sEquityPCT
sDailyReturn = sEquityPCT
#sDailyReturn = sDailyReturn[(sDailyReturn.index >= dtBackTestStart) & (sDailyReturn.index <= dtBackTestEnd)]
#seriesDailyPosition = seriesDailyPosition[(seriesDailyPosition.index >= dtBackTestStart) & (seriesDailyPosition.index <= dtBackTestEnd)]
sDailyReturn = sDailyReturn[sDailyReturn.index >= dtBackTestStart]
seriesDailyPosition = seriesDailyPosition[
seriesDailyPosition.index >= dtBackTestStart]
seriesDailyValue = (1 + sDailyReturn).cumprod()
seriesDailyValue.name = 'Cum Return'
seriesMaxValue = seriesDailyValue.expanding().max()
seriesMaxDD = (seriesMaxValue - seriesDailyValue) / seriesMaxValue
seriesMaxDD.name = 'Max DD'
dfOut = pd.concat(
[seriesDailyValue - 1, seriesMaxDD, seriesDailyPosition], axis=1)
# plot & savefig
dfOut = dfOut[['Cum Return', 'Max DD', 'Position']].ffill().dropna()
if dfOut.empty:
print 'empty dfOut'
shutil.rmtree(strFileAddressPrefix)
continue
dfOut.to_pickle(strFileAddressPrefix + 'dfOut.pickle')
# check
strFileAddress = strFileAddressPrefix + '/Check.xlsx'
excel_writer = pd.ExcelWriter(strFileAddress)
Utils.funcWriteExcel(dfPositionDirection, excel_writer,
'PositionDirection')
Utils.funcWriteExcel(dfEquityDeltaDaily, excel_writer,
'EquityDeltaDaily')
#Utils.funcWriteExcel(dfReturnValid, excel_writer, 'ReturnValid')
Utils.funcWriteExcel(dfWeightDirection, excel_writer,
'WeightDirection')
#Utils.funcWriteExcel(dfDailyReturnCheck, excel_writer, 'DailyReturn')
dfContractCode = Utils.getTableExe('ContractCode')
Utils.funcWriteExcel(dfContractCode,
excel_writer,
sheet_name='ContractCode')
dfContractCodeTomorrow = Utils.getTableExe('ContractCodeTomorrow')
Utils.funcWriteExcel(dfContractCodeTomorrow,
excel_writer,
sheet_name='ContractCodeTomorrow')
dfDominantChange = Utils.getTableExe('boolDominantChange')
Utils.funcWriteExcel(dfDominantChange,
excel_writer,
sheet_name='DominantChange')
dfDominantChangeTomorrow = Utils.getTableExe(
'boolDominantChangeTomorrow')
Utils.funcWriteExcel(dfDominantChangeTomorrow,
excel_writer,
sheet_name='DominantChangeTomorrow')
excel_writer.close()
#'''
# MySQL
if Utils.boolUsingDB:
#raise Exception
dictDataSpec = Utils.extractElementFromList(dictPerTopPort)
listToRemove = [
'strCloseAtDayEnd', 'strCase', 'Secu', 'listFileAddress',
'strMethodTrend', 'strModelName'
]
listParam = list(
set(dictPerTopPort.keys()).difference(set(listToRemove)))
# dfPositionDirection
df = dfPositionDirection.stack()
df = df.reset_index()
df.columns = ['trade_date', 'code', 'openInterest']
for strColumn in listParam:
if strColumn in dictDataSpec.keys():
df[strColumn] = dictDataSpec[strColumn]
strTB = dictDataSpec['strModelName']
strTB = Utils.UtilsDB.strTBPrefixPosition + strTB
listColumnIndex = ['trade_date', 'code'] + listParam
Utils.UtilsDB.saveTB_DAILY(Utils.UtilsDB.DB_NAME_POSITION, strTB,
df, listColumnIndex)
# sDailyReturn
df = pd.concat([seriesDailyReturn, seriesDailyPosition], axis=1)
df = df.reset_index()
df.columns = ['trade_date', 'PCT', 'Position']
df['code'] = dictDataSpec['strModelName']
for strColumn in listParam:
if strColumn in dictDataSpec.keys():
df[strColumn] = dictDataSpec[strColumn]
else:
df[strColumn] = np.nan
strTB = dictDataSpec['strModelName']
strTB = Utils.UtilsDB.strTBPrefixPerformance + strTB
listColumnIndex = ['trade_date', 'code'] + listParam
Utils.UtilsDB.saveTB_DAILY(Utils.UtilsDB.DB_NAME_PERFORMANCE,
strTB, df, listColumnIndex)