Engage in Pair Trading, a market-neutral strategy, where two closely related financial instruments are simultaneously bought and sold when a deviation in their correlation surfaces. Typically, stocks or commodities chosen for Pair Trading originate from identical sectors, mirroring each other’s movements during numerous market events. For instance, banking stocks, which consistently showcase high correlation due to their dependency on identical economic or news-driven factors, are commonly paired. Vigilant Pair Traders keep an eye on these correlated stocks over a period, stepping into action upon identifying a hiccup in the correlation. They strategically adopt a long position in one stock and a short in the other, operating under the fundamental assumption that the profits from the long position will outweigh the losses from the short position when the correlation strengthens again, or the reverse. In this article, we’re offering a Pair Trading Excel sheet that aids in automating this strategy. Moreover, the Excel sheet comes equipped with a backtesting feature, enabling you to assess performance across various stock pairs. While it’s largely deemed a low-risk strategy, exercising it with caution is key. Incorporating a solid Risk Management approach is imperative when engaging in Pair Trading.
Explore other popular Excel sheets showcased in this blog.
Pair Trading Excel Sheet
This Excel sheet will aid in pinpointing Pair Trading candidates by contrasting their current Price Ratio with the 50-day average Price Ratio. A predetermined divergence from the 50-day average triggers a trading signal. Consequently, you should adopt a long position in one stock and short the other. Although this represents a foundational Pair Trading strategy, its efficacy is notable. Moreover, you can scrutinize the historical performance of the selected pair for the preceding 1000 Trading days directly within the Excel sheet.
| Parameter | Description | |
| Worksheet Name | Pair Trading | |
| Inputs | Stock 1, Stock 2 (Cell E1,E2) | Instruments chosen for Pair Trading |
| Buy (Cell G1) | The stock to purchase when a Pair Trading opportunity surfaces. | |
| Divergence% (Cell G2) | The price ratio divergence from the 50-day average that signals a Pair Trading opportunity. | |
| Investment per Stock (Cell G3) | Investment allocation per stock for Pair Trading | |
| Outputs | Total Profit (Cell K1) | Overall profit for the selected pair during a 1000-day backtesting phase |
| Profit % (Cell K2) | Profit percentage | |
| Cell A5:O1000 | A detailed trade log with a divergence indicator | |
Below, view the integrated Excel Sheet designed for optimized Pair Trading
How to use Excel Sheet?
Step 1: Pinpoint two stocks with substantial correlation as Pair Trading contenders. Generally, we suggest utilizing stocks with similar Beta Values. Discover Beta values for NSE stocks here.
Step 2: Input the Stock names into the designated cells in the Excel Sheet, adhering to the NSE: format. The prices will auto-populate in the sheet.
Step 3: From the drop-down menu (cell G1), select the stock you wish to Buy when a Pair trade signal emerges. For instance, if you input Stock 1 here, you’ll opt to go Long in Stock 1 and Short in Stock 2.
Step 4: Insert the Divergence % and Investment per Stock.
Step 5: Evaluate Trading performance (Profit %) and tweak the above values to observe variations.
Step 6: Navigate to the sheet’s bottom to determine whether a Pair Trading signal has been generated today. (A “Yes” in the Divergence column indicates a Pair Trade Opportunity)
3 Comments