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

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 –

Here is the 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 –

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.