Excel Trading Spreadsheet for Backtesting Strategies
[box type=”bio”] Jayantha has been selected as Campus Ambassador at AlgoJi- 2017. He is pursuing B.Tech. + M.Tech. (Dual Degree) from IIT BHU. His hobbies include maths and music.[/box]Excel Trading Spreadsheet shows you how to code and backtest a strategy in Excel using simple programming. In the Excel trading spreadsheet, we have taken the example of moving average strategy. However, you can also code & backtest strategies for other technical indicators in this tutorial.
Contents
Indicators for Excel Trading Spreadsheet
One of the best ways to make a profit in the markets is to just spot a trend, and based on whether it is a downtrend or an uptrend, enter a position in either long or short, and stay with the position till the trend reverses.
But how do you design an algorithm so that it can recognize trends?
The above image is a chart of SBI Hourly Closing Stock price plotted along with its 8 Period Moving Average and 21 Period Moving Average. Try to observe what happens to the 8 period SMA and 21 period SMA when the trend changes. We can observe that whenever there is a reversal, and the trend changes to an uptrend, the 8 Period SMA crosses the 21 Period SMA and becomes more than 21 Period SMA. And whenever the trend changes to a downtrend, the 8 Period SMA dips below the 21 Period SMA.
So now we have a strategy –
If 8 Period SMA > 21 Period SMA AND current position is Short, then exit Short position and Go Long on the same amount of shares of the stock.
If 8 Period SMA < 21 Period SMA AND current position is Long, then exit Long position and go Long on the same amount of shares of the stock.
Backtesting Excel Trading Spreadsheet
Now lets backtest our algorithm on historical data to see how it would have performed in the past. The data we are going to be using is hourly closing prices of SBIN starting from 2009 until 2017.
Let us write a macro/subroutine which runs on the data, and writes all the trades and the PnL statements in another sheet. This is how our data looks like
Now, Create a sheet named as “Trades”. This is where we will enter our trades. This is how the sheet “Trades” should look like
We will record the date, time, position and prices for entry as well as exit of he position. In the next column we will also calculate the PnL( i.e. Profit or Loss). Before you see the code given below, we encourage you to try to implement the algorithm yourself first.
Here is the code which will enter the trades into the second sheet –
Sub SMAStrategy()
Dim fastPeriod As Long '8
Dim slowPeriod As Long '21
Dim fastAvg As Double
Dim slowAvg As Double
Dim highestHigh As Double
Dim lowestLow As Double
fastPeriod = 8
slowPeriod = 21
Dim row As Long
Dim pnlRow As Long
pnlRow = 2
Dim position As String
Dim noOfSignals As Long
For row = 2 To Application.WorksheetFunction.CountA(Columns("A"))
If row > fastPeriod Then
fastAvg = Application.WorksheetFunction.Average _
(Range("F" & row - fastPeriod + 1 & ":F" & row))
'Range("F2:F9")
End If
If row > slowPeriod Then
slowAvg = Application.WorksheetFunction.Average _
(Range("F" & row - slowPeriod + 1 & ":F" & row))
End If
If row > slowPeriod Then
If position = "" Then
If fastAvg > slowAvg Then
position = "Long"
noOfSignals = noOfSignals + 1
Sheets("Trades").Cells(pnlRow, 1) = DateValue(Cells(row, 1).Value) 'Date
Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 2) 'Time
Sheets("Trades").Cells(pnlRow, 3) = "Long" 'Action
Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 6) 'Price
ElseIf fastAvg < slowAvg Then
position = "Short"
noOfSignals = noOfSignals + 1
Sheets("Trades").Cells(pnlRow, 1) = DateValue(Cells(row, 1).Value)
Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 2)
Sheets("Trades").Cells(pnlRow, 3) = "Short"
Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 6)
End If
End If
If position = "Long" And fastAvg < slowAvg Then
position = "Short"
noOfSignals = noOfSignals + 1
Sheets("Trades").Cells(pnlRow, 5) = DateValue(Cells(row, 1).Value)
Sheets("Trades").Cells(pnlRow, 6) = Cells(row, 2)
Sheets("Trades").Cells(pnlRow, 7) = "LongExit"
Sheets("Trades").Cells(pnlRow, 8) = Cells(row, 6)
Sheets("Trades").Cells(pnlRow, 9) = _
Sheets("Trades").Cells(pnlRow, 8) - Sheets("Trades").Cells(pnlRow, 4)
pnlRow = pnlRow + 1
Sheets("Trades").Cells(pnlRow, 1) = DateValue(Cells(row, 1).Value)
Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 2)
Sheets("Trades").Cells(pnlRow, 3) = "Short"
Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 6)
End If
If position = "Short" And fastAvg > slowAvg Then
position = "Long"
noOfSignals = noOfSignals + 1
Sheets("Trades").Cells(pnlRow, 5) = DateValue(Cells(row, 1).Value)
Sheets("Trades").Cells(pnlRow, 6) = Cells(row, 2)
Sheets("Trades").Cells(pnlRow, 7) = "ShortExit"
Sheets("Trades").Cells(pnlRow, 8) = Cells(row, 6)
Sheets("Trades").Cells(pnlRow, 9) = _
Sheets("Trades").Cells(pnlRow, 4) - Sheets("Trades").Cells(pnlRow, 8)
pnlRow = pnlRow + 1
Sheets("Trades").Cells(pnlRow, 1) = DateValue(Cells(row, 1).Value)
Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 2)
Sheets("Trades").Cells(pnlRow, 3) = "Long"
Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 6)
End If
End If
Next row
End Sub
After running this subroutine, this is how the sheet “Trades” should look like –
Select the column I. You will see the statistics of this column near the lower right.
The total number of trades made were 774, The average return per trade was 0.638 Rupees. The overall return was 493.26.
In the J column, enter the following formula into cell J2 – =I2/D2, and drag the formula to the rest of the cells in the column. This will give the returns in percentage. You should find the average return per trade to be 0.3%.
Equity Curve for Excel Trading Spreadsheet
In the first cell of the column K, enter the value of the trading price of the first trade(which is 109.68). In the second cell enter the formula – =K1+I2, and drag it to the rest of the cells.
You will observe that the last value of this equity curve is 602.94.
Overall percentage of return is = 602.94/109.68 = 5.497 = 449.7 % increase in the value of portfolio over a period of 8.25 years.
Which gives an annualized return of around 22.95% .
To finally plot the equity curve, select all the cells in column K containing the value of portfolio, click on the Insert tab, select line chart.
Your chart should look like this –
Further Improving the Excel Trading Spreadsheet
Now you have a knowledge of one of the most basic algorithms, you can improve on this by adding several indicators such as ADX, RSI, MACD, etc. To know more about such indicators and how to implement them in excel click here(embed the link to the article on Technical Analysis in excel).
Also note that in this article we have not considered brokerage fees and slippage costs, and these may have a significant impact on profitability.
Caution is advised while implementing such strategies in real life. Such strategies often may work only in the short term and tend to have huge drawdowns. We can see in the equity graph that we have a maximum drawdown of about 100, if we had stopped running this algorithm in June 2016, our portfolio would have ended up at a value of around 700. i.e. we would have ended up with around 638% of the initial investment instead of just 549% . So before investing your money, make sure you can handle the drawdowns.