### 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.