Pair Trading Excel Sheet – with Backtesting

Pair Trading is a market neutral strategy where two highly co-related instruments are bought and sold together when there is a certain degree of deviation in their co-relation. Usually the stock or commodities selected for Pair Trading are from the same sector and moves together during most of the market events. For example: Banking stocks are always highly co-related since there long term movement depends on the same economic or news based factors. Pair Traders watch the co-related stocks over time and take action when they see any weakness in the co-relation. They go long in one of the stock and go short in another. The basic assumption is that the long position would profit more than the short position when the co-relation is strong again, or vice versa. In this post, we are giving away a Pair Trading Excel sheet which would help you automate this strategy. Also, there is a backtesting feature in the Excel sheet through which you can check the performance in different pair of Stocks. Although this is a low risk strategy, yet it should be practiced with caution. A proper Risk Management is mandatory for Pair Trading.

Check out the other popular Excel sheets posted in this blog here.

Pair Trading Excel Sheet

In this Excel sheet, we are going to identify Pair Trading candidates by comparing their current Price Ratio with 50 days average Price Ratio. A pre-defined divergence  of the price ration from the 50 days average would signal a trade. When that happens, one has to go long in one Stock and short the other. This is a very basic Pair Trading strategy, but very effective. You can also check the past performance of the selected  pair for last 1000 Trading days in the Excel sheet itself.

Parameter Description
Worksheet Name Pair Trading
Inputs Stock 1, Stock 2 (Cell E1,E2) Selected Pair Trading instruments
Buy (Cell G1) The Stock which should be bought when Pair Trading opportunity is signaled.
Divergence% (Cell G2) The divergence from of price ratio from 50 days average ratio which would signal a Pair Trading opportunity.
Investment per Stock (Cell G3) The Investment amount per stock for Pair Trading
Outputs Total Profit (Cell K1) Total profit for the selected pair in 1000 day backtesting period
Profit % (Cell K2) Profit in % terms
Cell A5:O1000 The complete trade log with divergence indicator

Screenshot

Below is the screenshot of Pair Trading Excel sheet. It indicates Pair Trades for HDFC Bank and Hindalco with divergence factor of 5%. The total profit of 31.07% is quite impressive. It would have been even better if a stop loss is maintained for losing trades.

Pair Trading

How to use Pair Trading Excel Sheet?

Step 1: Identify two co-related stocks as a pair Trading candidates. Mostly we recommend to use the stocks with similar Beta Values. Find Beta values for NSE stocks here.

Step 2: Copy the Stock names in the designated cells in Excel Sheet. The format should be NSE:<Stock Name>. The prices would automatically populate in the sheet.

Step 3: Select from the drop-down(cell G1) that which stock you want to Buy when Pair trade signal is indicated. For ex: If you enter Stock 1 here, then you would go Long in Stock 1 and go short in Stock 2.

Step 4: Enter the Divergence % and Investment per Stock.

Step 5: Check out the Pair Trading performance (Profit %). Adjust the above values to see the changes.

Step 6: Scroll down at the bottom of the sheet to see if Pair Trading signal is generated today.(Yes in Divergence column indicates Pair Trade Opportunity)

Download Link:

Access the Pair Trading Excel sheet from the below Google Docs link:

https://docs.google.com/spreadsheets/d/1KO1R_IC0IkFSdHeZOaDtZ0ST6uUMD8eHFMlxTm0UZ54/edit?usp=sharing

10 Comments

  1. Awesome Sir… may i ask if its possible to try it on google Intraday 1 minute automatic data .. canu try and let me know I dont think 5 percent divergence will every Show on intraday.. plz reply

  2. Hey,

    Firstly, thanks for the sheet, really really useful stuff. Somehow the sheet no longer seems to be generating yes/no and other data down at the current date. Furthermore, since we cannot edit the sheet there is no way to fix this. Do you know of any solution? Any help would be great. And thanks again!

    • Hi Karan,

      I can see the sheet updating everyday at my end. Can you please try again.

  3. Stupid question above, please do delete (I see it is given for moderation). Very useful sheet, however, regarding the backtest, doesn’t the sheet exhibit a look-ahead bias? In so far as it produces the yes/no signal based on the close but seems to continue the trade from the last day or exit it based on that? Maybe I am confused. Either way, thanks for the sheet :)

  4. Basically, if you’re using the close price to determine whether you going to be in/out of the position, while the “yes”, “no” signals are after the fact (i.e. based on close price), there is a look-ahead bias because you cannot know the close price during the trading day and therefore cannot make entry/exit, thereby invalidating the backtest?

  5. Very nice excel sheet indeed. Thanks for sharing. However, one query thou, is it possible to update the daily and historical data (just the way you have done on your web excel sheet) on our local machine excel sheet and if possible cud you share the info (one liner formula for any one scrip). Even if you are unable to share, still many many kudos to you for sharing such a utility.

Leave a Reply

Your email address will not be published. Required fields are marked *