Analysis of Moving Average Crossover Strategy Backtest Returns Using Pandas

In this post I thought I’d take advantage of the results we got from the moving average crossover strategy backtest in the last post (can be found here), and spend a bit of time digging a little more deeply into the equity curve and producing a bit of analysis concerning some key performance indicators and some generally (hopefully) interesting data.
For completeness, below is all the code needed to produce the strategy backtest results for our impending analysis, along with charting the equity curve just to make sure we have run it correctly.

#import relevant modules
import pandas as pd
import numpy as np
from pandas_datareader import data
from math import sqrt
import matplotlib.pyplot as plt

#download data into DataFrame and create moving averages columns
sp500 = data.DataReader('^GSPC', 'yahoo',start='1/1/2000')
sp500['42d'] = np.round(sp500['Close'].rolling(window=42).mean(),2)
sp500['252d'] = np.round(sp500['Close'].rolling(window=252).mean(),2)
#create column with moving average spread differential
sp500['42-252'] = sp500['42d'] - sp500['252d']
#set desired number of points as threshold for spread difference and create column containing strategy 'Stance'
X = 50
sp500['Stance'] = np.where(sp500['42-252'] > X, 1, 0)
sp500['Stance'] = np.where(sp500['42-252'] < X, -1, sp500['Stance'])
sp500['Stance'].value_counts()
#create columns containing daily market log returns and strategy daily log returns
sp500['Market Returns'] = np.log(sp500['Close'] / sp500['Close'].shift(1))
sp500['Strategy'] = sp500['Market Returns'] * sp500['Stance'].shift(1)
#set strategy starting equity to 1 (i.e. 100%) and generate equity curve
sp500['Strategy Equity'] = sp500['Strategy'].cumsum() + 1
#show chart of equity curve
sp500['Strategy Equity'].plot()

The analysis I have planned is as follows:

1) Charting section – we will create charts for the following series:
a) Rolling 1 year annualised volatility
b) Rolling 1 year hit ratio
c) Rolling 1 year returns
d) Daily return chart
e) Daily return distribution histogram

Our first step is to create a fresh DataFrame containing just the data we need, which is the strategy equity curve, along with the daily strategy returns. This is done as follows:

strat = pd.DataFrame([sp500['Strategy Equity'], sp500['Strategy']]).transpose()

Now we have to build up the DataFrame to include all the raw data which we will need to calculate the series outlined above, which we wish to chart. The code for this is:

#create columns that signifies whether each days return was positive, negative or flat.
strat['win'] = (np.where(strat['Strategy'] > 0, 1,0)) 
strat['loss'] = (np.where(strat['Strategy'] < 0, 1,0)) 
strat['scratch'] = (np.where(strat['Strategy'] == 0, 1,0)) 
#create columns with a cumulative sum of each of the columns created above strat['wincum'] = (np.where(strat['Strategy'] > 0, 1,0)).cumsum() 
strat['losscum'] = (np.where(strat['Strategy'] < 0, 1,0)).cumsum() strat['scratchcum'] = (np.where(strat['Strategy'] == 0, 1,0)).cumsum() 
#create a columns that holds a running sum of trading days - we will use this to create our percentages later 
strat['days'] = (strat['wincum'] + strat['losscum'] + strat['scratchcum']) 
#create columns that shows the 252 day rolling sum of the winning/losing/flat days 
strat['rollwin'] = strat['win'].rolling(window=252).sum() strat['rollloss'] = strat['loss'].rolling(window=252).sum() strat['rollscratch'] = strat['scratch'].rolling(window=252).sum() 
#create columns with hit ratio and loss ratio data 
strat['hitratio'] = strat['wincum'] / (strat['wincum']+strat['losscum']) strat['lossratio'] = 1 - strat['hitratio'] 
#create columns with rolling 252 day hit ratio and loss ratio data strat['rollhitratio'] = strat['hitratio'].rolling(window=252).mean() strat['rolllossratio'] =1 - strat['rollhitratio'] 
#create column with rolling 12 month return 
strat['roll12mret'] = strat['Strategy'].rolling(window=252).sum() 
#create column with average win, average loss and average daily return data 
strat['averagewin'] = strat['Strategy'][(strat['Strategy'] > 0)].mean() 
strat['averageloss'] = strat['Strategy'][(strat['Strategy'] < 0)].mean() 
strat['averagedailyret'] = strat['Strategy'].mean() 
#create column with rolling 1 year daily standard deviation and rolling 1 year annualised standard deviation
strat['roll12mstdev'] = strat['Strategy'].rolling(window=252).std() 
strat['roll12mannualisedvol'] = strat['roll12mstdev'] * sqrt(252)

Ok great, now we have all our data ready to plot the various charts we mentioned above.

We can do this as follows:

strat['roll12mannualisedvol'].plot(grid=True, figsize=(8,5),title='Rolling 1 Year Annualised Volatility')

strat['rollhitratio'].plot(grid=True, figsize=(8,5),title='Rolling 1 Year Hit Ratio')

strat['roll12mret'].plot(grid=True, figsize=(8,5),title='Rolling 1 Year Returns')

strat['Strategy'].plot(grid=True, figsize=(8,5),title='Daily Returns')

strat['Strategy'].plot(kind='hist',figsize=(8,5),title='Daily Return Distribution',bins=100)

As a side note here, we could very quickly take a look at the skew and kurtosis of the daily return distribution as follows:

print("Skew:",round(strat['Strategy'].skew(),4))
print("Kurtosis:",round(strat['Strategy'].kurt(),4))

Skew: 0.0331
Kurtosis: 9.4377

So the daily return distribution is far from normal and exhibits ever so slightly positive skew and high kurtosis (n.b. skew of normal distribution is 0, and kurtosis of normal distribution is 3).

I’m going to proceed at this point to dig a little deeper and produce some key performance indicators (KPIs) which you would commonly find along with the analysis of any trading strategy returns. These aren’t meant to be exhaustive but I’ll try to hit on most of the major areas.

I plan to produce the following:

1) Annualized Return
2) Last 12 months Return
3) Volatility
4) Sharpe Ratio
5) Maximum Drawdown
6) Calmar Ratio (Annualized Return / Maximum Drawdown)
7) Volatility / Maximum Drawdown
8) Best Month Performance
9) Worst Month Performance
10) % of Profitable Months & % Non-Profitable Months
11) Number of Profitable Months/Number of Non Profitable Months
12) Average Monthly Profit
13) Average Monthly Loss
14) Average Monthly Profit/Average Monthly Loss

Before I go on I am going to quickly build another fresh DataFrame which will hold our strategy return data on a monthly basis instead of a daily basis – this will make certain calculations easier later on and allow us to produce a monthly returns table; this can be produced by “re-sampling” the original DataFrame column of daily strategy returns and building up from there.

#Create a new DataFrame to hold our monthly data and populate it with the data from the daily returns column of our 
#original DataFrame and sum it by month
stratm = pd.DataFrame(strat['Strategy'].resample('M').sum())
#Build the monthly data equity curve
stratm['Strategy Equity'] = stratm['Strategy'].cumsum()+1
#Add a column that holds the numerical monthly index (i.e. Jan = 1, Feb = 2 etc)
stratm['month'] = stratm.index.month

If we now call the

stratm.head(15)

function we can see the monthly DataFrame format:

Let’s start working through our list of KPIs:

#1) Annualised Return
    
days = (strat.index[-1] - strat.index[0]).days
cagr = ((((strat['Strategy Equity'][-1]) / strat['Strategy Equity'][1])) ** (365.0/days)) - 1
print ('CAGR =',str(round(cagr,4)*100)+"%")

CAGR = 2.28%

#2) Last 12 months Return
stratm['last12mret'] = stratm['Strategy'].rolling(window=12,center=False).sum()
last12mret = stratm['last12mret'][-1]
print('last 12 month return =',str(round(last12mret*100,2))+"%")

last 12 month return = -13.14%

#3) Volatility
voldaily = (strat['Strategy'].std()) * sqrt(252)
volmonthly = (stratm['Strategy'].std()) * sqrt(12)
print ('Annualised volatility using daily data =',str(round(voldaily,4)*100)+"%")
print ('Annualised volatility using monthly data =',str(round(volmonthly,4)*100)+"%")

Annualised volatility using daily data = 19.15%<br>Annualised volatility using monthly data = 15.27%

#4) Sharpe Ratio
dailysharpe = cagr/voldaily
monthlysharpe = cagr/volmonthly
print ('daily Sharpe =',round(dailysharpe,2))
print ('monthly Sharpe =',round(monthlysharpe,2))

daily Sharpe = 0.12
monthly Sharpe = 0.15

#5) Maxdrawdown
#Create max drawdown function
def max_drawdown(X):
    mdd = 0
    peak = X[0]
    for x in X:
        if x > peak: 
            peak = x
        dd = (peak - x) / peak
        if dd > mdd:
            mdd = dd
    return mdd  

mdd_daily = max_drawdown(strat['Strategy Equity'])
mdd_monthly = max_drawdown(stratm['Strategy Equity'])
print ('max drawdown daily data =',str(round(mdd_daily,4)*100)+"%")
print ('max drawdown monthly data =',str(round(mdd_monthly,4)*100)+"%")

max drawdown daily data = 37.06%
max drawdown monthly data = 33.65%

#6) Calmar Ratio
calmar = cagr/mdd_daily
print ('Calmar ratio =',round(calmar,2))

Calmar ratio = 0.06

#7 Volatility / Max Drawdown
vol_dd = volmonthly / mdd_daily
print ('Volatility / Max Drawdown =',round(vol_dd,2))

Volatility / Max Drawdown = 0.41

#8) Best Month Performance
bestmonth = max(stratm['Strategy'])
print ('Best month =',str(round(bestmonth,2))+"%")

Best month = 19.0%

#9) Worst Month Performance
worstmonth = min(stratm['Strategy'])
print ('Worst month =',str(round(worstmonth,2)*100)+"%")

Worst month = -10.0%

#10) % of Profitable Months & % Non-Profitable Months
positive_months = len(stratm['Strategy'][stratm['Strategy'] > 0])
negative_months = len(stratm['Strategy'][stratm['Strategy'] < 0])
flatmonths = len(stratm['Strategy'][stratm['Strategy'] == 0])
perc_positive_months = positive_months / (positive_months + negative_months + flatmonths)
perc_negative_months = negative_months / (positive_months + negative_months + flatmonths)
print ('% of Profitable Months =',str(round(perc_positive_months,2)*100)+"%")
print ('% of Non-profitable Months =',str(round(perc_negative_months,2)*100)+"%")

% of Profitable Months = 49.0%<br>% of Non-profitable Months = 45.0%

#11) Number of Profitable Months/Number of Non Profitable Months
prof_unprof_months = positive_months / negative_months
print ('Number of Profitable Months/Number of Non Profitable Months',round(prof_unprof_months,2))

Number of Profitable Months/Number of Non Profitable Months 1.08

#12) Average Monthly Profit
av_monthly_pos = (stratm['Strategy'][stratm['Strategy'] > 0]).mean()
print ('Average Monthly Profit =',str(round(av_monthly_pos,4)*100)+"%")
#13) Average Monthly Loss
av_monthly_neg = (stratm['Strategy'][stratm['Strategy'] < 0]).mean()
print ('Average Monthly Loss =',str(round(av_monthly_neg*100,2))+"%")
#14) Average Monthly Profit/Average Monthly Loss
pos_neg_month = abs(av_monthly_pos / av_monthly_neg)
print ('Average Monthly Profit/Average Monthly Loss',round(pos_neg_month,4))

Average Monthly Profit = 3.56%<br>Average Monthly Loss = -3.33%<br>Average Monthly Profit/Average Monthly Loss 1.0683

And finally…just to finish off and use a little more Pandas DataFrame functionality I am going to create a table of monthly returns.

The first step is to create a pivot table and resample it to create what is known as a “pandas.tseries.resample.DatetimeIndexResampler” object.

monthly_table = stratm[['Strategy','month']].pivot_table(stratm[['Strategy','month']], index=stratm.index, columns='month', aggfunc=np.sum).resample('A')

So that we can manipulate it a little mor easily, I am going to convert this object back into a “DataFrame” using the “.aggregate()” function.

monthly_table = monthly_table.aggregate('sum')

We can now put the finishing touches on by converting the index dates to show just the year rather than the full date, and then also replace the column month headers (currently in numerical format) with the proper “MMM” format.

First, we have to quickly drop one of the table column index levels which is currently the word “Strategy” – this will leave us with a table with only a single level column index that corresponds to the integer month representations.

#Drop the top level column index which curently shows as "Strategy"
monthly_table.columns = monthly_table.columns.droplevel()

Visually the table will change from:

to:

Now we just have to change the date index to show in a yearly format (YYYY) and the remaining column headers to show monthly format (MMM).

#replace full date in index column with just the correspnding year
monthly_table.index = monthly_table.index.year
#Replace integer column headings with MMM format
monthly_table.columns = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']

We are now left with a monthly returns table that looks like this:

Brilliant! Well i think that’s enough for now as this post is starting to get a little bit long.

I’ll have a think over the next few days about a possible topic for my next post and get to work on that.

Thanks for following along!!