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

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:

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:

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

We can do this as follows:

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

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.

If we now call the

function we can see the monthly DataFrame format:

Let’s start working through our list of KPIs:

CAGR = 2.28%

last 12 month return = -13.14%

Annualised volatility using daily data = 19.15%
Annualised volatility using monthly data = 15.27%

daily Sharpe = 0.12
monthly Sharpe = 0.15

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

Calmar ratio = 0.06

Volatility / Max Drawdown = 0.41

Best month = 19.0%

Worst month = -10.0%

% of Profitable Months = 49.0%
% of Non-profitable Months = 45.0%

Number of Profitable Months/Number of Non Profitable Months 1.08

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.

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.

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.

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

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

It's only fair to share...
Written by s666