Home Basic Data Analysis Analysis of Moving Average Crossover Strategy Backtest Returns Using Pandas

# 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 math import sqrt
import matplotlib.pyplot as plt

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

#### You may also like

December 23, 2016 - 8:28 pm

This is amazing. Thank you so much!

December 24, 2016 - 8:40 pm

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

January 8, 2017 - 8:39 pm

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

January 9, 2017 - 6:02 pm

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.

February 21, 2017 - 7:11 am

[…] I’ll leave it up to you guys and girls to delve more deeply into the strategy returns – you can use my previous blog post where I analysed the returns of our moving average crossover strategy as inspiration. That post can be found here […]

August 10, 2018 - 6:42 am

Hello Jamieson,
Looking for an opportunity to work with you in capital markets space. Can you share your email to my email id.
Thanks,

March 20, 2020 - 6:29 pm

Amazing analysis, thanks for all the useful insights !

March 23, 2020 - 5:31 pm

Dear,

I enjoy this blog very much, but I do have a problem with maxdrawdown. I tried it for s&P500, Eurostoxx50 and BEL20, but the outcome of the max drawdown daily keeps at 0%. For this reason, the following two ratios are unable to calculate.
Can you help me with this?

March 24, 2020 - 6:42 am

Hi Alexander, thanks for the comment. I would suggest a figure of zero is of course incorrect as that would imply the price has never had a down day which is simply incorrect (assuming you are looking at a period of more than a few days at least).

Does the monthly draw down result in a correct figure when you run it? The 3 assets you mentioned, are you passing in a pandas series of each asset’s daily price?

Can you provide a bit more info regarding the two questions above, and hopefully we can get it working for you…