[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

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.

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, 4) = Cells(row, 6)  'Price
ElseIf fastAvg < slowAvg Then
position = "Short"
noOfSignals = noOfSignals + 1
End If
End If
If position = "Long" And fastAvg < slowAvg Then
position = "Short"
noOfSignals = noOfSignals + 1
pnlRow = pnlRow + 1
End If
If position = "Short" And fastAvg > slowAvg Then
position = "Long"
noOfSignals = noOfSignals + 1
pnlRow = pnlRow + 1
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%.

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 –