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)
dfReviewParamMax = pd.concat(listSMax, axis=1) # which (NDayTrain, NDayTest) are selected at each model review point dfCriterion = pd.DataFrame(listDictCriterion).set_index( ['NMonthModelReview', 'NMonthLookBack', 'NDayShiftModelReview']).sort_index() dfCriterionMin = dfCriterion.ix[ixMin].reset_index().set_index( listReviewParameter + ['dtModelReviewEnd']).sort_index() dfCriterionMax = dfCriterion.ix[ixMax].reset_index().set_index( listReviewParameter + ['dtModelReviewEnd']).sort_index() # out strFileAddress = '%s_%s.xlsx' % (strParamSweep, strColumnToSelect) excel_writer = pd.ExcelWriter(strFileAddress, date_format='YYYYMMDD') Utils.funcWriteExcel(pd.DataFrame(pd.Series(dictMetricBest)), excel_writer, sheet_name='MetricOf%s' % strReviewSetOption) Utils.funcWriteExcel(pd.DataFrame(sDailyReturnBest), excel_writer, sheet_name='DailyReturn%s' % strReviewSetOption) Utils.funcWriteExcel(dfReviewParamMin, excel_writer, sheet_name='ReviewParam_MinCR') Utils.funcWriteExcel(dfReviewParamMax, excel_writer, sheet_name='ReviewParam_MaxCR') Utils.funcWriteExcel(dfCriterionMin, excel_writer, sheet_name='ParamAtEachReview_MinCR') Utils.funcWriteExcel(dfCriterionMax,
def funcShowStrategyPortSum( dictPerTopPort, listDictDataSpec, seriesDTRebalance, strMethodVolatility, NDayVolatilityLookBack, dtBackTestStart, #dtBackTestEnd, strFileAddress): # extract the selected data print 'read data' listSeriesClose = [] listSeriesReturn = [] listSeriesPosition = [] listSeriesIndicator = [] listSeriesStoploss = [] listSeriesReturnStoploss = [] listStoplossPrice = [] listStoplossPriceTomorrow = [] for dictDataSpec in listDictDataSpec: strategy = dictDataSpec['strategy'] if dictDataSpec['Secu'] not in Utils.listSecuAll: continue print dictDataSpec['strCase'] seriesVolatility = strategy.df['indicator'].apply(lambda x: np.nan) seriesPosition = strategy.df['indicator'].apply(lambda x: np.nan) dfAll = strategy.df listDTTestStart = seriesDTRebalance seriesReturn = strategy.seriesReturnPCTHoldDaily seriesReturn.name = dictDataSpec['Secu'] listSeriesReturn.append(seriesReturn) seriesClose = dfAll['Close'] seriesClose.name = dictDataSpec['Secu'] listSeriesClose.append(dfAll['Close'].copy()) seriesIndicator = strategy.df['indicator'].copy() seriesIndicator.name = dictDataSpec['Secu'] listSeriesIndicator.append(seriesIndicator) # stoploss price if 'Stoploss' in strategy.df.columns: series = strategy.df['Stoploss'] series.name = dictDataSpec['Secu'] listSeriesStoploss.append(series) series = strategy.df['returnStoploss'] series.name = dictDataSpec['Secu'] listSeriesReturnStoploss.append(series) seriesStoplossPrice = strategy.df['StoplossPrice'].copy() seriesStoplossPrice.name = dictDataSpec['Secu'] listStoplossPrice.append(seriesStoplossPrice) series = strategy.df['StoplossPriceTomorrow'] series.name = dictDataSpec['Secu'] listStoplossPriceTomorrow.append(series) boolStoploss = True else: boolStoploss = False # concat the extracted data print 'concat data' dfClose = pd.concat(listSeriesClose, axis=1).fillna(0) dfClose.index = dfClose.index.to_datetime() dfClose.index.name = 'dtEnd' dfReturn = pd.concat(listSeriesReturn, axis=1).fillna(0) dfReturn.index = dfReturn.index.to_datetime() dfReturn.index.name = 'dtEnd' dfReturn.ix[dfReturn.index[-1], dfReturn.columns] = 0 dfIndicator = pd.concat(listSeriesIndicator, axis=1) # indicator everyday dfIndicatorAtAction = dfIndicator.ix[ seriesDTRebalance] # when to change direction # calculate volatility, and replace 0 return in dfReturn with np.nan print 'calculate volatility data' listDTTestStart = seriesDTRebalance.tolist() listDictVolatility = [] dfReturnValid = pd.DataFrame() for nWindow in range(0, len(listDTTestStart)): listReturn = [] for SecuCode in dfReturn.columns: sDailyReturnAll = dfReturn[SecuCode] sDailyCloseAll = dfClose[SecuCode] # get the train window and test window dtTestStart = listDTTestStart[nWindow] dtTrainEnd = dtTestStart if nWindow == 0: continue else: dtTrainStart = listDTTestStart[nWindow - 1] # is there any trade sDailyReturn = sDailyReturnAll[ (sDailyReturnAll.index >= dtTrainStart) & (sDailyReturnAll.index < dtTrainEnd)].copy() sDailyClose = sDailyCloseAll[ (sDailyCloseAll.index >= dtTrainStart) & (sDailyCloseAll.index < dtTrainEnd)].copy() if sDailyReturn.empty: continue # calculate volatility & weight volatility = sDailyClose.pct_change().std() * np.sqrt( Utils.NTradingDayPerYear) weight = 0.1 / volatility #weight = 0.03 / volatility #weight = 0.1 if sDailyReturn.sum() == 0: # not in the top 20% of XSM sDailyReturn = sDailyReturn.apply(lambda x: np.nan) listReturn.append(sDailyReturn) listDictVolatility.append({ 'dtTestStart': dtTestStart, 'SecuCode': SecuCode, 'volatility': volatility, 'weight': weight }) if len(listReturn) == 0: continue else: dfReturnValid = dfReturnValid.append(pd.concat(listReturn, axis=1)) # append tomorrow index to ReturnValid ###### # diff between dfReturnValid and dfReturn # when there is no position for a secu, dfReturnValid is 0 * dfReturn ###### print 'calculate daily weight' dfReturnValid = dfReturnValid.append(dfIndicator.ix[-1].replace(0, np.nan)) # position of every day dfVolatility = pd.DataFrame(listDictVolatility) dfVolatility = dfVolatility.set_index('dtTestStart').sort_index() dfWeightOriginal = dfVolatility.reset_index().pivot(index='dtTestStart', columns='SecuCode', values='weight') dfWeight = dfWeightOriginal.ix[dfReturnValid.index].ffill() dfWeight.index.name = dfReturnValid.index.name # adjust weight to meet leverage limit dfSelected = dfReturnValid.applymap(lambda x: ~np.isnan(x)).astype(np.int) dfWeightValid = dfWeight * dfSelected positionUpper = Utils.LEVERAGE sTotalWeight = dfWeightValid.sum(1) sTotalWeight.to_csv('TotalWeight.csv') for Secu in dfWeightValid.columns: dfWeightValid[ Secu] = dfWeightValid[Secu] / sTotalWeight * positionUpper #dfWeightValid[Secu] = dfWeightValid[Secu] / sTotalWeight * sTotalWeight.apply(lambda x: min(x, 5)) dfWeightValid[Secu] = dfWeightValid[Secu].apply( lambda x: min(x, Utils.UpperPositionSingleContract)) pass # if some secu's weight is smaller than one contract, then do not trade TotalMoney = Utils.TOTALMONEY dfPositionDollar = dfWeightValid * TotalMoney listPositionContract = [] listPositionDecimal = [] for SecuCode in dfPositionDollar.columns: listColumn = ['CloseRaw', 'SettleRaw'] dfSecu = Utils.getDFOneProduct(SecuCode, listColumn) sPositionDollar = dfPositionDollar[SecuCode] #sSettleRaw = Utils.dfExe.ix[SecuCode].ix[sPositionDollar.index, 'SettleRaw'].shift(1) sSettleRaw = dfSecu.ix[sPositionDollar.index, 'SettleRaw'].shift(1) sSettleRaw.name = SecuCode sNominalContract = sSettleRaw * Utils.dfDetail.ix[SecuCode, 'multiplier'] sMarginContract = sSettleRaw * Utils.dfDetail.ix[ SecuCode, 'multiplier'] * Utils.dfDetail.ix[SecuCode, 'margin'] sPositionContract = sPositionDollar / sNominalContract #sPositionContract = sPositionContract.apply(lambda x: np.round(x)) def funcRound(x): if x < 0.3: return 0 elif x >= 0.3 and x <= 0.5: return 1 else: return np.round(x) sPositionContract = sPositionContract.apply(lambda x: np.round(x)) #sPositionContract = sPositionContract.apply(lambda x: funcRound(x)) sPositionDecimal = sPositionContract * sNominalContract / TotalMoney sPositionContract.name = SecuCode sPositionDecimal.name = SecuCode listPositionContract.append(sPositionContract) listPositionDecimal.append(sPositionDecimal) dfPositionContract = pd.concat(listPositionContract, axis=1) dfPositionDirection = (dfIndicatorAtAction * dfPositionContract).ffill() dfPosition = pd.concat(listPositionDecimal, axis=1) dfWeightValid = dfPosition # daily position seriesDailyPosition = dfWeightValid.sum(axis=1) seriesDailyPosition = seriesDailyPosition[ seriesDailyPosition.index >= dtBackTestStart] seriesDailyPosition.name = 'Position' # calculate daily dollar return, daily margin ''' print 'calculate daily return' TotalMoney = Utils.TOTALMONEY dfPositionDollar = dfWeightValid * TotalMoney listDollarReturnDaily = [] listDollarMarginDaily = [] for SecuCode in dfPositionDirection.columns: listColumn = ['SettleRaw', 'Delta'] dfSecu = Utils.getDFOneProduct(SecuCode, listColumn) sPositionContract = dfPositionDirection[SecuCode] #sSettleRaw = Utils.dfExe.ix[SecuCode].ix[sPositionContract.index, 'SettleRaw'] sSettleRaw = dfSecu.ix[sPositionContract.index, 'SettleRaw'] sSettleRaw.name = SecuCode #sCloseDelta = Utils.dfExe.ix[SecuCode].ix[sPositionContract.index, 'Delta'] sCloseDelta = dfSecu.ix[sPositionContract.index, 'Delta'] sCloseDelta.name = SecuCode sDollarReturnDaily = sPositionContract * Utils.dfDetail.ix[SecuCode, 'multiplier'] * sCloseDelta sDollarMarginDaily = sPositionContract.abs() * Utils.dfDetail.ix[SecuCode, 'multiplier'] * sSettleRaw * Utils.dfDetail.ix[SecuCode, 'margin'] listDollarReturnDaily.append(sDollarReturnDaily) listDollarMarginDaily.append(sDollarMarginDaily) dfDollarReturnDaily = pd.concat(listDollarReturnDaily, axis=1) dfDollarMarginDaily = pd.concat(listDollarMarginDaily, axis=1) dfDollarReturnDaily['Total'] = dfDollarReturnDaily.sum(1) dfDollarMarginDaily['Total'] = dfDollarMarginDaily.sum(1) # consider the commission fee for position adjustment dfAdjustPosition = (dfIndicatorAtAction != 0) & (dfIndicatorAtAction == dfIndicatorAtAction.shift(1)) listColumn = dfAdjustPosition.columns indexCommon = dfAdjustPosition.index & dfReturnValid.index dfWeightDelta = dfWeightValid - dfWeightValid.shift(1) dfWeightDelta = dfWeightDelta.applymap(lambda x: max(0, x)) dfReturnValid.ix[indexCommon, listColumn] = dfReturnValid.ix[indexCommon, listColumn] - Utils.COMMISSION * 2 * dfAdjustPosition.ix[indexCommon, listColumn].astype(np.float) * dfWeightDelta.ix[indexCommon, listColumn] dfWeightValid = (dfIndicatorAtAction.apply(lambda x: abs(x)) * dfWeightValid).ffill() # added in 20170929, vital error, although not affecting result a lot. dfReturnWeighted = dfReturnValid * dfWeightValid # daily dollar return recovered from decimal simulation dfDollarReturnDailyRecovered = dfReturnWeighted * Utils.TOTALMONEY dfDollarReturnDailyRecovered = dfDollarReturnDailyRecovered.fillna(0).astype(int) dfDollarReturnDailyRecovered['Total'] = dfDollarReturnDailyRecovered.sum(1) # calculate stat seriesDailyReturn = dfReturnWeighted.sum(axis=1) seriesDailyReturn = seriesDailyReturn[seriesDailyReturn.index >= dtBackTestStart] seriesDailyReturn = seriesDailyReturn #''' # calculate daily return as in WH print 'calculate daily return WH' listEquityDeltaDaily = [] for SecuCode in dfPositionDirection.columns: listColumn = [ 'OpenRaw', 'CloseRaw', 'SettleRaw', 'DeltaSettle', 'Open-PreSettle', 'Settle-Open' ] #listColumn = ['OpenRaw', 'CloseRaw', 'SettleRaw'] dfSecu = Utils.getDFOneProduct(SecuCode, listColumn) #dfSecu['DeltaSettle'] = dfSecu['SettleRaw'].diff() #dfSecu['PreSettle'] = dfSecu['SettleRaw'].shift(1) #dfSecu['Open-PreSettle'] = dfSecu['OpenRaw'] - dfSecu['PreSettle'] #dfSecu['Settle-Open'] = dfSecu['SettleRaw'] - dfSecu['OpenRaw'] #dfTrading = dfExe_Friday.ix[SecuCode][['OpenRaw', 'CloseRaw', 'SettleRaw', 'DeltaSettle', 'Open-PreSettle', 'Settle-Open']] dfTrading = dfSecu 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 - (sPositionIn.abs() + sPositionOut.abs() ) * dfTrading['OpenRaw'] * Utils.COMMISSION 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 ixCommon = dfEquityDeltaDaily.index.intersection(seriesDTRebalance) for dt in ixCommon: dfEquityDeltaDaily.ix[dt, 'NDTRebalance'] = dt.strftime('%Y%m%d') dfEquityDeltaDaily['NDTRebalance'] = dfEquityDeltaDaily[ 'NDTRebalance'].ffill() def funcCalcEquityPCT(sEquity): sEquity = sEquity.cumsum() + Utils.TOTALMONEY sPCT = sEquity.pct_change() sPCT.ix[0] = sEquity.ix[0] / Utils.TOTALMONEY - 1 return sPCT sEquityPCT = dfEquityDeltaDaily.groupby('NDTRebalance')['Total'].apply( funcCalcEquityPCT) seriesDailyReturn = sEquityPCT seriesDailyReturn.index.name = 'dtEnd' ### output for check excel_writer = pd.ExcelWriter(strFileAddress + 'Check.xlsx') Utils.funcWriteExcel(dfVolatility, excel_writer, 'Volatility') Utils.funcWriteExcel(dfReturnValid, excel_writer, 'ReturnValid') Utils.funcWriteExcel(dfWeightValid * dfIndicator, excel_writer, 'WeightDirection') Utils.funcWriteExcel(dfPositionDirection, excel_writer, 'PositionDirection') Utils.funcWriteExcel(pd.DataFrame(seriesDTRebalance), excel_writer, 'DTRebalance') Utils.funcWriteExcel(pd.DataFrame(seriesDailyReturn), excel_writer, 'DailyReturn') Utils.funcWriteExcel(pd.DataFrame(sEquityPCT), excel_writer, 'sEquityPCT') Utils.funcWriteExcel(dfEquityDeltaDaily, excel_writer, 'EquityDeltaDaily') #Utils.funcWriteExcel(dfDollarReturnDaily, excel_writer, 'DollarReturnDaily') #Utils.funcWriteExcel(dfDollarMarginDaily, excel_writer, 'DollarMarginDaily') #Utils.funcWriteExcel(dfDollarReturnDailyRecovered, excel_writer, 'DollarReturnDailyRecovered') #Utils.funcWriteExcel((dfDollarReturnDailyRecovered-dfDollarReturnDaily), excel_writer, 'DollarReturnDiff') if boolStoploss: Utils.funcWriteExcel(pd.concat(listSeriesStoploss, axis=1), excel_writer, 'Stoploss') Utils.funcWriteExcel(pd.concat(listSeriesReturnStoploss, axis=1), excel_writer, 'ReturnStoploss') Utils.funcWriteExcel(pd.concat(listStoplossPrice, axis=1), excel_writer, 'StoplossPrice') Utils.funcWriteExcel(pd.concat(listStoplossPriceTomorrow, axis=1), excel_writer, 'StoplossPriceTomorrow') #dfContractCode = Utils.dfExe['ContractCode'].reset_index().pivot_table(columns='SecuCode', index='TradingDay', values='ContractCode', aggfunc=lambda x: ' '.join(x)) dfContractCode = Utils.getTableExe('ContractCode') Utils.funcWriteExcel(dfContractCode, excel_writer, sheet_name='ContractCodeFullRename') #dfContractCodeTomorrow = Utils.dfExe['ContractCodeTomorrow'].reset_index().pivot_table(columns='SecuCode', index='TradingDay', values='ContractCodeTomorrow', aggfunc=lambda x: ' '.join(x)) dfContractCodeTomorrow = Utils.getTableExe('ContractCodeTomorrow') Utils.funcWriteExcel(dfContractCodeTomorrow, excel_writer, sheet_name='ContractCodeTomorrow') #dfDominantChange = Utils.dfExe['boolDominantChange'].reset_index().pivot_table(columns='SecuCode', index='TradingDay', values='boolDominantChange') dfDominantChange = Utils.getTableExe('boolDominantChange') Utils.funcWriteExcel(dfDominantChange, excel_writer, sheet_name='DominantChange') #dfDominantChangeTomorrow = Utils.dfExe['boolDominantChangeTomorrow'].reset_index().pivot_table(columns='SecuCode', index='TradingDay', values='boolDominantChangeTomorrow') dfDominantChangeTomorrow = Utils.getTableExe('boolDominantChangeTomorrow') Utils.funcWriteExcel(dfDominantChangeTomorrow, excel_writer, sheet_name='DominantChangeTomorrow') # freeze panes for sheet in excel_writer.sheets: excel_writer.sheets[sheet].freeze_panes(1, 1) excel_writer.close() # dfOut seriesDailyValue = (1 + seriesDailyReturn).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) # MySQL if Utils.boolUsingDB: 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) #if Utils.boolUsingDB: # # dfPositionDirection # df = dfPositionDirection.stack() # df = df.reset_index() # df.columns = ['trade_date', 'code', 'openInterest'] # listToRemove = ['strCloseAtDayEnd', 'strCase', 'Secu', 'listFileAddress', 'strMethodTrend', 'strModelName'] # listParam = list(set(dictPerTopPort.keys()).difference(set(listToRemove))) # 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 # listIndexColumn = ['trade_date', 'code'] + listParam # Utils.UtilsDB.saveTB_DAILY(Utils.UtilsDB.DB_NAME_PERFORMANCE, strTB, df, listColumnIndex) return dfOut
dfResultAllAllYear = pd.DataFrame() for year in range(2010, 2017 + 1): listDict = [] for ix, row in dfResultAll.iterrows(): sPCTDaily = row['sDailyReturn'] sPCTDaily = sPCTDaily[sPCTDaily.index.year == year] dictMetric = Utils.funcMetric(sPCTDaily) dictOne = dictMetric listDict.append(dictOne) dfResultAllOneYear = pd.DataFrame(listDict, index=dfResultAll.index) dfResultAllOneYear['year'] = year dfResultAllAllYear = dfResultAllAllYear.append(dfResultAllOneYear) dfResultAllAllYear.reset_index().groupby( ['year', 'NMonthLookBack', 'NMonthModelReview'])[strColumnToSelect].min() dfPerfByYear = pd.DataFrame(dfResultAllAllYear.reset_index().groupby( ['year', 'NMonthLookBack', 'NMonthModelReview'])[strColumnToSelect].min()) # out strFileAddress = '%s_%s.xlsx' % (strParamSweep, strColumnToSelect) excel_writer = pd.ExcelWriter(strFileAddress, date_format='YYYYMMDD') Utils.funcWriteExcel(dfPerfByYear, excel_writer, sheet_name='PerfOfReviewSet') Utils.funcWriteExcel(pd.DataFrame(pd.Series(dictMetricBest)), excel_writer, sheet_name='MetricOf%s' % strReviewSetOption) Utils.funcWriteExcel(dfTuple, excel_writer, sheet_name='BestParamAtEachReview') Utils.funcWriteExcel(pd.DataFrame(sDailyReturnBest), excel_writer, sheet_name='DailyReturn%s' % strReviewSetOption) excel_writer.close()
index_col='dtEnd').ix[-2] rowStoplossPriceTomorrow = pd.read_excel( strFileAddress, sheetname='StoplossPriceTomorrow', index_col='dtEnd').ix[-2] listRow = listRow + [ rowStoploss, rowStoplossPrice, rowStoplossPriceTomorrow ] listRowName = listRowName + [ 'Stoploss', 'StoplossPrice', 'StoplossPriceTomorrow' ] dfResult = pd.concat(listRow, axis=1) dfResult.columns = listRowName # dump to excel Utils.funcWriteExcel(dfResult, excel_writer, sheet_name=strStrategy) dfResult = dfResult.fillna(0) dictPerTopPort['dfResult'] = dfResult sDailyReturn = pd.read_excel(strFileAddress, sheetname='DailyReturn', index_col='dtEnd') sDailyReturn = sDailyReturn[sDailyReturn.columns[0]] sDailyReturn.name = strStrategy dictPerTopPort['sDailyReturn'] = sDailyReturn #s = pd.read_excel(strFileAddress, sheetname='DollarMarginDaily', index_col='dtEnd')['Total'] #s.name = strStrategy #dictPerTopPort['sDollarMarginDaily'] = s df = pd.read_excel(strFileAddress, sheetname='WeightDirection',