# 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
#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:

1 |
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:

1 2 3 4 5 6 7 8 9 10 |
#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:

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

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

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

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

1 |
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:

1 2 3 4 5 |
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.

1 2 3 4 5 6 7 8 9 |
#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

1 |
stratm.head(15) |

function we can see the monthly DataFrame format:

Let’s start working through our list of KPIs:

1 2 3 4 5 |
#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%

1 2 3 4 5 |
#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%

1 2 3 4 5 |
#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%

Annualised volatility using monthly data = 15.27%

1 2 3 4 5 |
#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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#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%

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

Calmar ratio = 0.06

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

Volatility / Max Drawdown = 0.41

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

Best month = 19.0%

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

Worst month = -10.0%

1 2 3 4 5 6 7 8 |
#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%

% of Non-profitable Months = 45.0%

1 2 3 |
#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

1 2 3 4 5 6 7 8 9 10 11 |
#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%

Average Monthly Loss = -3.33%

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.

1 |
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.

1 |
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.

1 2 |
#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).

1 2 3 4 5 |
#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!!

This is amazing. Thank you so much!

No probs! How did you find my post explaining the moving average cross over code I posted for you? Did it make things clear?

Just found your blog by O-U model keyword…and..i can’t beleive my eyes… fully explained…in simple terms…and working backtesting python script for pairs analysis. Learned aloooot from it. Very practical and easy to understand. You saved me a big part of my nervous system. 🙂

Great to hear the blog helped you out…makes it worthwhile!! I hope you managed to get your own version of it working as you want it…if you have any questions at all, let me know.