Pairs Trading Excel Spreadsheet
[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.
Contents
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.