In this post, we will take a brief look at backtesting a trading strategy in Excel.
Excel is the tool of choice for most Financial Analysts. So, it only natural that we will use Excel for backtesting. Besides, there are many features in Excel that can be used for data analysis. In later posts, we will explore some of these features. Excel also has a programming element called Visual Basic for Applications (VBA). It is assumed that you already are familiar with Excel and its basic features. For all the work in the post, we will use Excel 2016.
For this post, we will consider one of the simplest strategies, Moving Average Crossover strategy using Simple Moving Average. The strategy involves calculation of two simple averages of the closing price and generating trade signals when the Short Term Moving Average(SMA) crosses the Long Term Moving Average(LMA). If the SMA is lower than LMA and crosses LMA to a higher value than LMA, then, a buy signal is generated. Similarly, if the SMA is higher than LMA and crosses LMA to a lower value than LMA, then, a sell signal is generated.
Following are the steps for back testing using this strategy:
1) Getting the data that will be used for back testing
Two common sources of getting historical data are Google Finance and Yahoo Finance. For our example, we have downloaded Apple Inc stock prices on NASDAQ for 2016 from Google Finance. After downloading, you can open the data files in Excel as shown below:
2) Calculating the Technical Indicators as SMA and LMA
We will consider SMA of 5 days and LMA of 20 days
The formula for 5 day SMA is using AVERAGE function on the closing price. Since the first four values will not be available, we will calculate it separately and enter the data manually as we will lose out on data points otherwise.
The formula entered in G6 will be AVERAGE(E2:E6). This can then be extended to the rest of the SMA column. Screenshot is shown below for reference.
Similarly, LMA is calculated but will use the formula AVERAGE(E2:E19) and entered in H21. This formula again will be extended for the rest of the LMA column. Screenshot is shown below for reference.
3) Once the Technical Indicators are calculated, Trade Signals can be generated
A Buy signal is generated if the SMA is lower than LMA and crosses LMA to a higher value than LMA. A Sell signal is generated if the SMA is higher than LMA and crosses LMA to a lower value than LMA. The formula is used is:
=IF(AND(G2<H2,G3>H3),"BUY",IF(AND(G2>H2,G3<H3),"SELL",""))
and is to be entered in cell I3. This formula can then be extended to the rest of the column.
If you are on Excel 2016, then, according to the Microsoft documentation, you should be able to use IFS on the lines described below:
=IFS(AND(G2<H2,G3>H3),"BUY", AND(G2>H2,G3<H3),"SELL",TRUE,"")
Per the documentation, Microsoft claims that IFS function is applicable to Excel 2016. However, I do not see it in the Excel 2016 that I bought, so, let us park this discussion here.
A screen shot with the Trade Signal formula is shown below:
4) Trade Price is next calculation
The logic is quite simple: If we have a trade signal of BUY or SELL, then, we will use the Open Price of the next day. If not, then, we carry over the previous value of the Trade Price.
Below formula is entered in cell I3:
=IF(I2<>"",B3,J2)
This formula is then extended to the rest of the column. Screenshot is shown below:
5) Returns calculation follows the Trade Price
For the purpose of calculating returns, we will assume that we deal with 1 unit at the start of the transaction. In our case, it will be a buy on 22nd February. Then, after that, we deal with 2 units, that is, we have a position on 1 unit at any point. Also, we discount any brokerage or transaction charges that may accompany the trades.
The below formula for the returns is entered in J3:
=IF(AND(I2<>"",J2<>0),IF(I2="BUY",1-J3/J2,J3/J2-1),"")
The column formatting can be converted to percentage. Formula is shown below:
6) After the Returns are calculated, calculate Total Returns
As we see many blanks in the excel sheet where no trades have occurred, we copy the Returns to another sheet and remove the blank rows as described in the steps below:
a) Select the Returns columns as shown below:
b) Click F5
c) In the Dialog Box titled Go To, click Special button
d) In the Dialog Box titled Go To Special, click on Blank Radio Button and click on OK button
This will select all blank rows in the worksheet
e) Right click on selected column and click Delete as shown below
f) Select Entire Row Radio Button in the Dialog Box titled Delete
This will delete all blank rows
Then, as a last step we calculate the Trade metrics as shown below:
Total Positive Trades = COUNTIF(A2:A11,">0")
Total Negative Trades = COUNTIF(A2:A12,"<0")
Total Trades = COUNTIF(A2:A11,"<>""")
Total Returns = =SUM(A2:A11)
The final result is shown below:
It is heartening to note the positive returns.
In future posts, we will see if we can improve on the trading strategy described.
Excel is the tool of choice for most Financial Analysts. So, it only natural that we will use Excel for backtesting. Besides, there are many features in Excel that can be used for data analysis. In later posts, we will explore some of these features. Excel also has a programming element called Visual Basic for Applications (VBA). It is assumed that you already are familiar with Excel and its basic features. For all the work in the post, we will use Excel 2016.
For this post, we will consider one of the simplest strategies, Moving Average Crossover strategy using Simple Moving Average. The strategy involves calculation of two simple averages of the closing price and generating trade signals when the Short Term Moving Average(SMA) crosses the Long Term Moving Average(LMA). If the SMA is lower than LMA and crosses LMA to a higher value than LMA, then, a buy signal is generated. Similarly, if the SMA is higher than LMA and crosses LMA to a lower value than LMA, then, a sell signal is generated.
Following are the steps for back testing using this strategy:
1) Getting the data that will be used for back testing
Two common sources of getting historical data are Google Finance and Yahoo Finance. For our example, we have downloaded Apple Inc stock prices on NASDAQ for 2016 from Google Finance. After downloading, you can open the data files in Excel as shown below:
2) Calculating the Technical Indicators as SMA and LMA
We will consider SMA of 5 days and LMA of 20 days
The formula for 5 day SMA is using AVERAGE function on the closing price. Since the first four values will not be available, we will calculate it separately and enter the data manually as we will lose out on data points otherwise.
The formula entered in G6 will be AVERAGE(E2:E6). This can then be extended to the rest of the SMA column. Screenshot is shown below for reference.
Similarly, LMA is calculated but will use the formula AVERAGE(E2:E19) and entered in H21. This formula again will be extended for the rest of the LMA column. Screenshot is shown below for reference.
3) Once the Technical Indicators are calculated, Trade Signals can be generated
A Buy signal is generated if the SMA is lower than LMA and crosses LMA to a higher value than LMA. A Sell signal is generated if the SMA is higher than LMA and crosses LMA to a lower value than LMA. The formula is used is:
=IF(AND(G2<H2,G3>H3),"BUY",IF(AND(G2>H2,G3<H3),"SELL",""))
and is to be entered in cell I3. This formula can then be extended to the rest of the column.
If you are on Excel 2016, then, according to the Microsoft documentation, you should be able to use IFS on the lines described below:
=IFS(AND(G2<H2,G3>H3),"BUY", AND(G2>H2,G3<H3),"SELL",TRUE,"")
Per the documentation, Microsoft claims that IFS function is applicable to Excel 2016. However, I do not see it in the Excel 2016 that I bought, so, let us park this discussion here.
A screen shot with the Trade Signal formula is shown below:
4) Trade Price is next calculation
The logic is quite simple: If we have a trade signal of BUY or SELL, then, we will use the Open Price of the next day. If not, then, we carry over the previous value of the Trade Price.
Below formula is entered in cell I3:
=IF(I2<>"",B3,J2)
This formula is then extended to the rest of the column. Screenshot is shown below:
5) Returns calculation follows the Trade Price
For the purpose of calculating returns, we will assume that we deal with 1 unit at the start of the transaction. In our case, it will be a buy on 22nd February. Then, after that, we deal with 2 units, that is, we have a position on 1 unit at any point. Also, we discount any brokerage or transaction charges that may accompany the trades.
The below formula for the returns is entered in J3:
=IF(AND(I2<>"",J2<>0),IF(I2="BUY",1-J3/J2,J3/J2-1),"")
The column formatting can be converted to percentage. Formula is shown below:
6) After the Returns are calculated, calculate Total Returns
As we see many blanks in the excel sheet where no trades have occurred, we copy the Returns to another sheet and remove the blank rows as described in the steps below:
a) Select the Returns columns as shown below:
b) Click F5
c) In the Dialog Box titled Go To, click Special button
d) In the Dialog Box titled Go To Special, click on Blank Radio Button and click on OK button
This will select all blank rows in the worksheet
e) Right click on selected column and click Delete as shown below
f) Select Entire Row Radio Button in the Dialog Box titled Delete
This will delete all blank rows
Then, as a last step we calculate the Trade metrics as shown below:
Total Positive Trades = COUNTIF(A2:A11,">0")
Total Negative Trades = COUNTIF(A2:A12,"<0")
Total Trades = COUNTIF(A2:A11,"<>""")
Total Returns = =SUM(A2:A11)
The final result is shown below:
It is heartening to note the positive returns.
In future posts, we will see if we can improve on the trading strategy described.
