Forex Excel 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]Forex Excel Spreadsheet shows you how to code and backtest strategies for the international currencies market. The Foreign Exchange market is the largest traded and most liquid market in the world. In this tutorial we demonstrate how to code and backtest forex strategies using Excel. As an example, we have backtested the ubiquitous moving average crossover strategy on the EUR/USD pair. It is easy to learn using Moving Averages because they are the most simple indicators, and readers are advised to move quickly to other sophisticated indicators.

Contents

Programming Strategy in Forex Excel Spreadsheet

The data that we are using is of EUR.USD which is recorded minute by minute.

Now let us calculate Slow SMA and Fast SMA for this data, but the question remains for what period should we calculate this.
Let us enter the values of Fast SMA and Slow SMA into a separate table, and make it such that if we change the values of the periods from this table, the SMAs should change automatically accordingly.
To do this make two columns, one each for the Fast SMA and the Slow SMA, and enter the following formula in the first cell of the column –
=AVERAGE(OFFSET(H2,(-1*$M$2+1),-1,$M$2,1))

And enter the formula –
=AVERAGE(OFFSET(I2,(-1*$M$3+1),-2,$M$3,1))
into the first cell of the second column, then apply is to the rest of the rows.
If you change the values of the periods in the table, you should see the Fast SMA and the Slow SMA change automatically. Try to plot a graph of the closing price, its Fast SMA, and its Slow SMA. To do this, select the three columns containing the three required values, click on the Insert tab, and select a line graph.
You should get a graph which looks like this –

Programming Strategy in Forex Excel Spreadsheet

If you change the periods of the Fast and Slow SMA, you will see the graph change automatically also. This will help you determine what window you have to consider to give you the best results.
Lets choose 75 as the period for the Fast SMA, 150 as the period for the Slow SMA.
Therefore,
If Fast SMA > Slow SMA AND previous position is Short, then exit Short position and Buy the EURUSD
If Fast SMA < Slow SMA AND previous position is Long, then exit Long Position and go Short on the EURUSD

Backtesting Forex Excel Spreadsheet

Use following macro code for backtesting this forex excel spreadsheet:

Sub SMAStrategy()
Dim fastPeriod As Long
Dim slowPeriod As Long
Dim fastAvg As Double
Dim slowAvg As Double
Dim highestHigh As Double
Dim lowestLow As Double
fastPeriod = 75
slowPeriod = 150
Dim row As Long
Dim pnlRow As Long
pnlRow = 2
Dim position As String
Dim noOfSignals As Long
fastPeriod = Cells(2, 13).Value
slowPeriod = Cells(3, 13).Value
For row = 2 To Application.WorksheetFunction.CountA(Columns("A"))
 Debug.Print row
 If row > fastPeriod Then
 fastAvg = Cells(row, 8).Value
 End If
 If row > slowPeriod Then
 slowAvg = Cells(row, 9).Value
 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, 2).Value) 'Date
 Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 3) 'Time
 Sheets("Trades").Cells(pnlRow, 3) = "Long" 'Action
 Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 7) 'Price
 ElseIf fastAvg < slowAvg Then
 position = "Short"
 noOfSignals = noOfSignals + 1
 Sheets("Trades").Cells(pnlRow, 1) = DateValue(Cells(row, 2).Value)
 Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 3)
 Sheets("Trades").Cells(pnlRow, 3) = "Short"
 Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 7)
 End If
 End If
 
 If position = "Long" And fastAvg < slowAvg Then
 position = "Short"
 noOfSignals = noOfSignals + 1
 Sheets("Trades").Cells(pnlRow, 5) = DateValue(Cells(row, 2).Value)
 Sheets("Trades").Cells(pnlRow, 6) = Cells(row, 3)
 Sheets("Trades").Cells(pnlRow, 7) = "LongExit"
 Sheets("Trades").Cells(pnlRow, 8) = Cells(row, 7)
 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, 2).Value)
 Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 3)
 Sheets("Trades").Cells(pnlRow, 3) = "Short"
 Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 7)
 End If
 
 If position = "Short" And fastAvg > slowAvg Then
 position = "Long"
 noOfSignals = noOfSignals + 1
 Sheets("Trades").Cells(pnlRow, 5) = DateValue(Cells(row, 2).Value)
 Sheets("Trades").Cells(pnlRow, 6) = Cells(row, 3)
 Sheets("Trades").Cells(pnlRow, 7) = "ShortExit"
 Sheets("Trades").Cells(pnlRow, 8) = Cells(row, 7)
 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, 2).Value)
 Sheets("Trades").Cells(pnlRow, 2) = Cells(row, 3)
 Sheets("Trades").Cells(pnlRow, 3) = "Long"
 Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 7)
 End If
 
 End If

Next row

End Sub

Create a second sheet named as “Trades” before running this code. After running this code, the sheet “Trades” should look like this –

Backtesting Forex Excel Spreadsheet

Here is the equity curve –

Forex Excel Spreadsheet Equity Curve

Forex Excel Spreadsheet for Different Time Frames

But what if you want to design a strategy for data of 30 minute or 1 hour intervals?
Here is a method which will take an integer which gives the length of the time frame as a parameter, and will separate the data accordingly and write it to another sheet called “Data”. i.e. if you give 30 as an input, it will take data at which was recorded at 30 minute intervals.

Public Function getData(timeFrame As Integer)
 Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

ActiveSheet.DisplayPageBreaks = False
 Dim row As Integer ' which row has to be selected in the first sheet
 Dim secondRow As Integer ' row index of the second sheet which contains selected data
 row = 1
 secondRow = 1
 Do While row < Application.WorksheetFunction.CountA(Columns("A"))
 Sheets("Data").Cells(secondRow, 1) = Cells(row, 1)
 Sheets("Data").Cells(secondRow, 2) = Cells(row, 2)
 Sheets("Data").Cells(secondRow, 3) = Cells(row, 3)
 Sheets("Data").Cells(secondRow, 4) = Cells(row, 4)
 Sheets("Data").Cells(secondRow, 5) = Cells(row, 5)
 Sheets("Data").Cells(secondRow, 6) = Cells(row, 6)
 Sheets("Data").Cells(secondRow, 7) = Cells(row, 7)
 row = row + timeFrame
 secondRow = secondRow + 1
 Debug.Print row & " " & secondRow
 Loop
 
 Application.ScreenUpdating = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

ActiveSheet.DisplayPageBreaks = True
End Function

Before calling this function, create a sheet named as “Data”. Now, call this function from a subroutine. The data will get collected in the sheet “Data”.
Now you can run the same subroutine named SMAStrategy on this data, which was described above, to backtest this strategy. This completes our forex excel spreadsheet.
This is how you get the equity curve for 30 minute interval data –

Forex Excel Spreadsheet for Different Time Frames

In this example, the drawdowns make us question whether this is really a good strategy.

Conclusion

We can see from our equity curve that there are massive drawdowns in both our strategies. These strategies are good for only learning purpose, and not useful for practical trading. Try to use a similar strategy for 60/120/240 minute windows. Also, consider using more indicators to improve the profitability. You can also try the strategy on different currency pairs to see on which pair the moving average works best. Running a strategy on multiple currency pairs simultaneously is also advisable, because it will reduce risk.