[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] Pair Trading Excel Spreadsheet allows you to code and backtest pair trading strategies which have a low risk profile. Pairs Trading is a market-neutral strategy where you rely on mean reversion of the ratio of two highly correlated stocks.

It is generally know that the stocks picked for pair trading should be correlated, i.e. if the first stock goes up for some reason, even the second stock will go up. Their price may not be in the same range, but you may find that their ratio doesn’t change much. Here it is very important to learn that the pair should be fundamentally correlated, not just technically correlated.

Fundamental correlation refers to picking stocks which belong to same industry, have similar business model and similar market cap structure.

Technical correlation refers to picking stocks which have a correlation ratio more than 0.90, and cointegration tests to validate the hypothesis.

For example, consider two similar ETFs, the SPY and the DIA – You can see from the graph that they are highly correlated.

Since the stocks’ ratio revert to their mean, if the ratio goes to a high value, we can go long on the underperforming stock, and go short on the overperforming one. The algorithm must decide at what point to initiate the pair trade.

Most commonly, trades are entered when the ratio of the stocks go above the mean plus one standard deviation, and when the ratio goes below the mean minus one standard deviation.

So in this case, if the ratio of SPY by DIA > Mean + STDEV –> Go Short on SPY, and go long on DIA
If ratio < Mean – STDEV –> Go long on SPY, and go short on DIA
And cover your positions when the ratio crosses the mean.

Now, lets backtest our strategy on historical data to see how it performs. You can download daily closing prices of SPY and DIA from 2012 to 2017 from Yahoo Finance.

Lets calculate the mean and the standard deviation of the ratio from 2012 to 2014, and test the strategy using these parameters on data from 2014 to 2017. If the ratio is truly mean reverting, then the values calculated from 2014 to 2014 should also be profitable. We can see that the stocks have a pretty high correlation of 0.98.

Now, we will write VBA code to enter the trades from 2014 to 2017. But, before that, create a second sheet with the following columns – Next, enter the following code in your VBA editor –

```Sub Pairs_Trade()
Dim row As Integer
Dim pnlRow As Integer
pnlRow = 2

For row = 380 To Application.WorksheetFunction.CountA(Columns("A"))
If Sheets("Trades").Cells(pnlRow, 1) = "" Then
If Cells(row, 4) > Cells(14, 8) Then
ElseIf Cells(row, 4) < Cells(14, 7) Then
End If
ElseIf Sheets("Trades").Cells(pnlRow, 2) = "ShortSPYDIA" Then
Debug.Print Cells(10, 8)
If Cells(row, 4) <= Cells(10, 8) And Cells(row - 1, 4) > Cells(10, 8) Then
Debug.Print row
pnlRow = pnlRow + 1
End If
ElseIf Sheets("Trades").Cells(pnlRow, 2) = "LongSPYDIA" Then
If Cells(row, 4) >= Cells(10, 8) And Cells(row - 1, 4) < Cells(10, 8) Then
pnlRow = pnlRow + 1
End If
End If
Next row
End Sub

```

This code will not calculate the PnL, but it’s pretty simple to do it in the spreadsheet. After running the code, you should see something like this in the sheet “Trades” – We see that it made only one trade in all those years, which yielded 1.29% profit. You will see the reason for its poor performance when you plot a graph of the ratio with time – The ratio of the stocks is not really mean reverting in the long run.