## Vectorised Backtesting in Excel

00200

By Jacques Joubert

Now for those of you who know me as a blogger might find this post a little unorthodox to my traditional style of writing, however in the spirit of evolution, inspired by a friend of mine Stuart Reid (TuringFinance.com), I will be following some of the tips suggested in the following blog post.

Being a student in the EPAT program I was excited to learn the methodology that others make use of when it comes to backtesting. As usual, we start off in Excel and then migrate to R.

Having previously written a blog series on backtesting on Excel and then moving to R, I was very interested to see a slightly different method used by the QuantInsti team.

“By calculating transaction prices it opens up some very interesting doors for implementing MAE analysis”

The one main difference in the method is that it opens the door for performance metrics like:

• Total Positive returns
• Negative Returns
• Hit Ratio
• Average return

But suffers from not being able to plot an equity curve like my original method (that I like to think of us a vectorised backtest), you can however, easily incorporate the equity curve, as I did in this post.

### Mission Objective

Build the “Hello World” of trading strategies: the “Long Short Moving Average Crossover Strategy”.

#### Step 1: Get data

There are several places from which you can get data, however for this example we will get data from Yahoo Finance. I will be building this example using Google as a share. Here is a link to download the price data in Csv file format from Yahoo. Note: Please be sure to order from oldest to newest date.

Price data from Yahoo in CSV file format

#### Step 2: Create a column for both the long and the short simple moving average (SMA)

For this example I want you to make use of the 5 and 25 day SMA. For those of you who are new to trading strategies, a SMA is simply the total sum of closing price divided by the number of observations.

2.1) Create the short term SMA (5 days)

Using the following formula in Excel: =AVERAGE(G2:G6)

2.2) Create the long term SMA (25 Days)

Using the following formula in Excel: =AVERAGE(E2:E26)

#### Step 3: Generate trading signals

It is at this step where readers will pick up on a major difference from my previous blog posts on building a vectorised backtester. I will incorporate my original methodology in this post as well in order to plot the equity curve.

The next thing we need to do is to generate buy and sell signals

Logic for Step 3:

If:

In the previous day the (5)SMA was below the (25)SMA and in the current day there is a change where the (5)SMA is now above the (25)SMA,

Then:

Write the string “BUY” in the current field

Else If:

In the previous day the (5)SMA was above the (25)SMA and in the current day there is a change where the (5)SMA is now below the (25)SMA,

Then:

Write the string “SELL” in the current field

Else:

Add an empty string “” to the current field.

This is represented in Excel using the following formula:

Note:

The SMAs are calculated on closing prices and not adjusted close because we want the trade signal to be generated on the price data and not influenced by dividends paid.

#### Step 4: Get purchase / selling price of the trade

In the next column add the following Excel formula: =IF(J26<>””,G27,K26)

The logic is as follows:

If the trade signal column for the previous day (Very important to lag the indicator as to remove look-ahead bias) is not an empty string then make use of the previous price above the current field, else set the current field to the closing price for the day.

Note:

Some may argue that you can’t actually get the close for the day but you can if you put your order in at the closing auction, and even after the auction there are some residual orders that you can fill, one of the previous funds I worked for did exactly this.

#### Step 5: Calculate returns

Add a column called returns that makes use of the following Excel Formula: =IF(J26=”SELL”,K27/K26-1,IF(J26=”BUY”,1-K27/K26,””))

Logic:

If the previous day generated a SELL signal then take today’s closing price and divide it by the purchase price and subtract 1.

Else If the previous day generated a BUY signal then add 1 and subtract (today’s closing price and divide it by the purchase price).

This formula calculates the returns for a given trade.

#### Step 6: Calculate some performance metrics

• Positive returns: =SUMIF(L:L,”>0″)
• Negative returns: =SUMIF(L:L,”<0″)
• Hit Ratio =O4/(O4+O5)
• Average Returns =AVERAGE(L:L)

These aren’t the traditional portfolio performance metrics but by calculating the purchase and selling price it opens up some very interesting doors for implementing maximum adverse excursion analysis that can be used to optimise stop losses.

“Note: I wasn’t able to calculate these metrics in my previous method due to not having recorded the purchase and sale prices of transactions.”

#### Step 1: Add two new columns for the Daily returns and the natural log daily returns of the share

For this I will make use of the adjusted closing price as I want dividends paid to be reflected in our strategies equity curve and total return profile.

Formula for Daily Returns is: (Today’s Price / Yesterday’s Price) – 1

Excel formula: =G3/G2-1

The formula is use for the natural log daily returns is: LN(Today’s price / Yesterday’s price)

Excel formula: = LN(G3/G2)

#### Step 2: Calculate the Long or Short holdings signals

In this column we want to know if we are currently holding a long or a short position. This is represented by 1 for long and -1 for short.

This builds on the moving average cross over strategy by going long if the short term SMA is above the long term SMA and short if the opposite is true.

“Note: you have to lag the signals by one day in order to remove look-ahead bias.”

In this example the Excel formula is as such: =IF(H26>I26, 1, -1)

#### Step 3: Calculate Strategy ln Daily Returns

This is the easy part, simply multiply the natural log daily return by the current position.

Excel formula: =R27*S27

#### Step 4: Calculate the cumulative returns for both the strategy and the share as if you bought and hold. (Do this to act as a comparison)

The formula to cumulate returns is simple, for LN returns you simply add them using =T27+U26.

Next you need to reverse the natural log using the following formula: =EXP(U27)-1

And then you need to calculate the stocks cumulative returns:

Excel formula =(1+Q27)*(1+Q26)-1

#### Step 5: Plot the returns

As can be seen from the chart above, this strategy isn’t a profitable one on this particular time frame and share but this tutorial’s focus is on building a backtester using Excel. I would encourage readers to explore other trading strategies by trying to incorporate the RSI indicator to act as a guide on how to size a position.

### Next Steps

Here are a few more trading strategies with example datasheets – Trading with ETF, Candlestick Trading Strategy, Pair Trading Strategy and Black-Scholes Option Pricing Model. If you are a coder or a tech professional looking to start your own automated trading desk, learn automated trading from live Interactive lectures by daily-practitioners. Executive Programme in Algorithmic Trading covers training modules like Statistics & Econometrics, Financial Computing & Technology, and Algorithmic & Quantitative Trading. Enroll now!

00200

## 5 thoughts on “Vectorised Backtesting in Excel”

1. October 20, 2015

Thank you for sharing this. It looks to me that there is an error in calculating the strategy daily returns (col S) on days that have a trade signal. You are using the close to close price for the daily return, but on days a trade occurs the opening price needs to be used.

For example the first day there is a position (row 27) the return should us the open price of that day (533.762426) as the entry, not the previous day’s close (531.352374). The daily return should be =-1*(G27/B27-1) which is 1.09%.

• October 22, 2015

Dear Gary,

Very well spotted. Thank you for pointing that out. That is a mistake I made.

There are two ways to fix this,

1.) Step 4 should make use of the closing price and not the open. (That is often how I run quick tests.) Note: you cant actually buy at the close and the “Blog Flirting with Models” has a great way of addressing this problem with end of day data) https://blog.thinknewfound.com/2015/10/building-better-backtests/

2.) Alternatively you can add the following formula to row 27 column S and all the fields below that column.
=IF(R27R26, ((E27/B27)-1)*R27, Q27*R27)

The logic is as follows:
If there is a change in trade signal then:
Calculate the daily return if we bought at the open. formula = (Close price / Open Price) – 1

Else:
Simply use the daily returns as before that are calculated from close to close multiplied by the trade signal.

Thank you again for the feedback.

How did you find the article? are there other topics that you would like us to write on?

Kind regards
Jacques Joubert

2. August 12, 2016

Hello, Jacques Joubert.

Interesting work. However, I found a mistake. When calculating cum returns on short position, the end capital should depend on the price of entry and exit of the position, but not on what happens in the middle. For example, if you alter G33 to 800, it changes all the data below (one should expect that one-off adverse price rise on short position and thus negative return should be offset by posititve return of returning back to 500-520 range, but that doesn’t happen).

Regards,
Bull And Bear Investor.

• August 12, 2016

Hi Bull and Bear Investor.

Thank you for this. Its because I didn’t make use of the Ln Returns. I will be updating the article over the weekend. The Equity curve doesn’t change that much with the standard returns (its off by 2%) Given you don’t fiddle with the adjusted prices by adding outliers.

But well spotted.

Kind regards
Jacques Joubert

3. October 9, 2016