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

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.

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

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.

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