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.

Please download the Excel spreadsheet so that you can follow the example as we go along.

**“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
- Positive trades
- Negative trades
- Hit Ratio
- Average return
- MAE (Maximum Adverse Excursion)

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 ObjectiveClick To Tweet

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:

= IF(AND(H26>I26,H25<I25),”BUY”,IF(AND(H26<I26,H25>I25),”SELL”,””))

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

**take today’s closing price and divide it by the purchase price and subtract 1.**

*then*** Else If** the previous day generated a BUY signal

**add 1 and subtract (today’s closing price and divide it by the purchase price).**

*then*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″)
- Positive Trades: =COUNTIF(L:L, “>0”)
- Negative Trades: =COUNTIF(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.”**

**Adding an Equity Curve**

**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 ExcelClick To Tweet. 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!

October 20, 2015

Gary HartThank 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

Jacques JoubertDear 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

& also multiply it by your trade signal.

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

August 12, 2016

Bull And Bear InvestorHello, 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

Jacques JoubertHi 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

October 9, 2016

Jacques JoubertOk The article has been updated with all of the corrections. Thank you all for the feedback.

Best regards

Jacques Joubert

January 15, 2017

javadhow compute average return for RSI or MFI indecator

July 18, 2017

AndyThanks for sharing this, helped me design my SMA quite a bit

October 20, 2017

Sunny WongHi,

Good to see your post and share the trading logic by using excel. I have tried this for almost two years at home after work. I would like to share some point of view to you to get these strategy improve hence can inspire people while looking this post.

Thanks for some of your idea that inspire me to consider while preparing a back testing system. However, with long term investigation and low efficiency, I start trying to get professional software to run the backtest but still facing a lot of problem. It is really go starting platform to use excel that can strengthen your idea before approaching to expert level.

The Transaction Price shall be open price of next trading day. In reality, we cannot trade close price when the market is closed. Eg, in cell K27, the formula shall be =IF(J26″”,B27,K26) instead of =IF(J26″”,G27,K26).

Also, to improve the SMA parameter, I will use offset function that enable to make fast calculation by just change the parameters. I will add 5 and 25 to cell H2 and I2 respectively. Then the formula for cell H6 will be =IF(ROW()-1<$H$2,"",AVERAGE(OFFSET($E6,0,0,-$H$2,1))) while I6 will be =IF(ROW()-1<$I$2,"",AVERAGE(OFFSET($E6,0,0,-$I$2,1))).

Hope above message helps.

Regards,

Sunny Wong

October 24, 2017

Jacques JoubertHi Sunny Wong,

Thank you for your recommendation. It is certainly something that readers can implement if they don’t want to run the analysis on end of day prices. I also like how you used the offset function. I would recommend that you move from Excel into a Python environment and use the Numpy library.

Regarding the end of day close prices. It has been my professional experience that as an institutional investor your broker is able to get a price very close to the closing price, you can add a small slippage expense to transaction costs to account for this in the backtest. Most markets also have a post market trading that brokers can facilitate (but with poor liquidity).

I hope this helps shed some light.

Best Regards

Jacques Joubert

November 1, 2017

Jim ChangHere is food for thought: if the strategy is consistently a loser, do the opposite.