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.
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:
The sheet is non-editable by default, but you can make a copy and update it as required. Any sharing requests will not be entertained.
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
hello,,,,how can i download the xls & run the same on local machine
Hi Devesh,
As of now, we do not have a local version available.
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.
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 🙂
Hi Karan,
Can you please elaborate your query with an example.
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?
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.
Hi,
There is no one liner formula that can be used for Microsoft excel. But you can definitely use Macros. See below excel sheet, you would find some clue:
https://tradingtuitions.com/live-intraday-stock-data-in-excel-sheet-free-download/
any body having afl for pair trading ?
hi have any excel sheet for commodity live trading ??
Is the excel sheet working properly? I did not get the result properly and getting error #DIV/0 error(Function DIVIDE parameter 2 cannot be zero.)
Hi Viki,
Please see the updated link in the post:
https://tradingtuitions.com/pair-trading-excel-sheet-with-backtesting/
Few curious fellows play with the sheet often and make it unusable. I need to improve the security.
Thanks for your reply. But the issue not yet solved.
Could you please share to my email id?
Hi Viki,
Can u check the below link once:
https://docs.google.com/spreadsheets/d/1Y8cPU3tx0ZKbzVGeJoNuC33cvmEtxaihxkMuAZnZ7D4/edit?pli=1#gid=1248298988
Working now. Thank you very much.
still not working
Hi,
Can you please suggest what error you are facing? I just checked it and the sheet works perfectly fine.
https://docs.google.com/spreadsheets/d/1Y8cPU3tx0ZKbzVGeJoNuC33cvmEtxaihxkMuAZnZ7D4/edit?pli=1#gid=1248298988
I request you to give the link to excel sheet on Pair Trading. I have opend the link, but there is nothing. Thanks a lot.
Hi Parimal,
Please try again now
Smashing <3 !!!!!!!!!!!!!!!!!!!!!!!!
Thats a nice sheet, however 3 questions I have 1. how to decide at what % of divergence we should enter the trade and where should we book the profit, SL I can understand one can take as per their risk ability.
Hi Vikash,
FOr the stocks that are closely correlated to each other (ex: TCS and INFY), the % divergence of greater that 5% indicates a potential trading opportunity