Pairs Trading Excel Spreadsheet - AlgoJi

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

Picking Stocks for Pair Trading Excel Spreadsheet

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 –

neutral strategy where you rely on mean reversion of the ratio of two highly correlated stocks. Picking Stocks for Pair Trading Excel Spreadsheet

You can see from the graph that they are highly correlated.

Algorithm for Picking Stocks for Pair Trading Excel Spreadsheet

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.

Backtesting Pair Trading Excel Spreadsheet

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.

Ratios Pair Trading Excel Spreadsheet

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 –

Columns in Pair Trading Excel Spreadsheet

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
 Sheets("Trades").Cells(pnlRow, 1) = DateValue(Cells(row, 1))
 Sheets("Trades").Cells(pnlRow, 2) = "ShortSPYDIA"
 Sheets("Trades").Cells(pnlRow, 3) = Cells(row, 2)
 Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 3)
 ElseIf Cells(row, 4) < Cells(14, 7) Then
 Sheets("Trades").Cells(pnlRow, 1) = Cells(row, 1)
 Sheets("Trades").Cells(pnlRow, 2) = "LongSPYDIA"
 Sheets("Trades").Cells(pnlRow, 3) = Cells(row, 2)
 Sheets("Trades").Cells(pnlRow, 4) = Cells(row, 3)
 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
 Sheets("Trades").Cells(pnlRow, 5) = Cells(row, 1)
 Sheets("Trades").Cells(pnlRow, 6) = "ShortExit"
 Sheets("Trades").Cells(pnlRow, 7) = Cells(row, 2)
 Sheets("Trades").Cells(pnlRow, 8) = Cells(row, 3)
 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
 Sheets("Trades").Cells(pnlRow, 5) = Cells(row, 1)
 Sheets("Trades").Cells(pnlRow, 6) = "LongExit"
 Sheets("Trades").Cells(pnlRow, 7) = Cells(row, 2)
 Sheets("Trades").Cells(pnlRow, 8) = Cells(row, 3)
 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” –
Backtesting Pair Trading Excel Spreadsheet
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 –
Ratio Pair Trading Excel Spreadsheet
The ratio of the stocks is not really mean reverting in the long run.

Improving Pair Trading Excel Spreadsheet

This strategy can be improved by considering the mean and the standard deviation of only the recent data, and optimization for profit.