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)
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