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)
#''' #''' import ThaiExpress.Common.Strategy as Strategy dictParam = dict(Utils.dictParamTemplate) dictParam['commission'] = 0.002 dictParam['switchPlot'] = False dictParam['boolStoploss'] = False dictParam['boolStopProfit'] = False strategy = Strategy.Strategy(df, dictParam) strategy.df['indicator'] = sIndicator dictResult = strategy.evaluateLongShortSimplified() print dictResult['DTMaxDD'] sPCTPort = strategy.df['returnPCTHold'] dictResult = Utils.funcMetric(sPCTPort) #sPCTPortAPN = sPCTPort * sPositionAPN.resample('1D').last().ffill().ix[sPCTPort.index].ffill() #dictResult = Utils.funcMetric(sPCTPortAPN) sPositionAPV = sPositionAPV.resample('1D').last().ffill().ix[ sPCTPort.index].ffill() sPCTPortAPV = sPCTPort * sPositionAPV sPCTPortAPV = sPCTPortAPV + 0.025 / 250 * (1 - sPositionAPV) dictResult = Utils.funcMetric(sPCTPortAPV) sPCTHold = sPCTPortAPV sPCTHold.name = SecuCode print dictResult print sIndicator.dropna().size print sIndicator.dropna().tail()
listDailyReturn = [] listPortConfig = [] listMetric = [] listDictCriterion = [] for root, dirs, files in os.walk(strDirModelReview): if root.find('NDayShift4') < 0 or root.find('strMethodTrendTS3') > 0: #continue pass for nFile, name in enumerate(np.sort(files)): if name == 'dfOut.pickle': strFileAddress = root + '/' + name dfOut = pd.read_pickle(strFileAddress) sDailyReturn = (dfOut['Cum Return'] + 1).pct_change() sDailyReturn.name = root listDailyReturn.append(sDailyReturn) dictMetric = Utils.funcMetric(sDailyReturn.ix[:-1]) dictMetric['name'] = root listMetric.append(dictMetric) if name == 'portconfig.pickle': strFileAddress = root + '/' + name sPortConfig = pd.read_pickle(strFileAddress) dictOne = sPortConfig.to_dict() dictOne['name'] = root listPortConfig.append(dictOne) dfAllCase = pd.concat(listDailyReturn, axis=1) dfPortConfig = pd.DataFrame(listPortConfig).set_index('name') dfMetric = pd.DataFrame(listMetric).set_index('name') dfMetric = pd.concat([dfPortConfig, dfMetric], axis=1) strFileAddress = '%s_AllCase.xlsx' % strParamSweep dfMetric.to_excel(strFileAddress, index=False)
ixLast = dfPartEnter.index[-1] else: ixLast = dfPartLowVolume.index[0] ''' sDollarReturnStopProfit = sDollarReturn[sDollarReturn > 1.1] if sDollarReturnStopProfit.empty is False: ixStopProfit = sDollarReturnStopProfit.index[0] ixLast = min(ixBeforeSettle, ixStopProfit) #''' sDollarReturn = sDollarReturn[sDollarReturn.index < ixLast] listSDollarReturnYear.append(sDollarReturn) if len(listSDollarReturnYear) == 0: continue sDollarReturn = pd.concat(listSDollarReturnYear, axis=0) sDollarReturn.name = strPair # duplicates? sDollarReturn = sDollarReturn.reset_index().drop_duplicates( subset='TradingDay').set_index('TradingDay').sort_index() listSDollarReturn.append(sDollarReturn) dfResult = pd.concat(listSDollarReturn, axis=1) sPCT = dfResult.fillna(0).mean(1) sPCT = sPCT[sPCT.index > datetime.datetime(2005, 1, 1)] sPCT0 = sPCT sPCT = dfResult.mean(1) sPCT = sPCT[sPCT.index > datetime.datetime(2005, 1, 1)] dictMetric = Utils.funcMetric(sPCT) print dictMetric
sVFee = (sPCTFee + 1).cumprod() sPCTFee.name = 'DollarPCTFee' sVFee.name = 'DollarVFee' dfDollarPCT = pd.concat([sPCT, sV, sPCTFee, sVFee, sDollar], axis=1) Utils.funcWriteExcel(dfDollarPCT.dropna(), excel_writer, sheet_name='DollarPCT') # calculate rolling return and metrics if Utils.boolMonitor is False: dt2005 = datetime.datetime(2005, 1, 1) dt2010 = datetime.datetime(2010, 1, 1) dt2015 = datetime.datetime(2015, 1, 1) listDictMetric = [] for dt in [dt2005, dt2010, dt2015]: dMetric, listOut = Utils.funcMetric(sPCT[sPCT.index > dt], True) dMetric['Fee'] = 0.0 dMetric['dtStart'] = dt listDictMetric.append(dMetric) ''' for fee in [0.25, 0.3, 0.4, 0.5]: dMetric, listOut = Utils.funcMetric(sPCT[sPCT.index>dt]-fee/1e4, True) dMetric['Fee'] = fee dMetric['dtStart'] = dt listDictMetric.append(dMetric) ''' dfMetric = pd.DataFrame(listDictMetric)[listOut + ['dtStart', 'Fee']] Utils.funcWriteExcel(dfMetric, excel_writer, sheet_name='MetricDollarPCT') # freeze panes for sheet in excel_writer.sheets: