Hello all, I am experimenting with pairs trading(stat arb) and need help. Specifically, I am looking for a way to get the ratios for each leg of a stock using as little work as possible. See below for an example of how I calculate a spread. AAL/LUV spread: I use google sheets and the google finance function to grab 30 days of the stock closing price for both AAL and LUV. I then use the average function to find the average price of those 30 days for both those stocks. So the 30 day average is: AAL=40.59 LUV=36.99 I then find the ratios by dividing the larger price by the smaller so: AAL/LUV=40.59/36.99=1.16 Thus my spread would be = AAL-LUV*1.16 Now that is a lot of steps to do for over 1000 pairs. Does anyone have an idea on how to condense this process? Currently, I am working in Google Sheets using the Google finance function. I would only be interested in the coefficient that balances the spread(i.e. 1.16 in the example above). Any help would be much appreciated. See the google doc sheet I am working in: https://docs.google.com/spreadsheet/ccc?key=0AlfPgsyN3k0TdE9lYXlNaWRYX09BUXdTaEJvZ1lKdEE&usp=sharing
http://arbpairs.com/ Take a look at this. They connect to the Sterling and RediPlus API. We offer both.
My suggestion is to start by thinking about the purpose of applying a ratio in the first place. The basic ratio techniques for stock are share-neutral, volatility-neutral, and dollar-neutral. Each approach has its own rationale grounded in concepts. What you are currently doing is dollar-neutral. Rather than just telling you a formula for Excel, you will be best served by considering these different approaches and deciding which is most appropriate for your trading.
I don't think I am familiar with being "share-neutral". I have experience creating pairs using linear regression and being beta neutral and I understand their purpose. For now I am content with being dollar neutral. I am more or less looking for someone who would want to modify my google sheet or provide some insight.
Your sum is wrong, should be 1.097. Why bother with your formula? Buy $1,000 AAL, and sell $1,000 LUV. No formula required.
@OTM-Options Good catch, I was handtyping this in a rush and miscalc'd the number but the principle is the same. The ratio matters because in order to hedge you have to neutralize some factor like your beta or dollar value. This becomes far more apparent in stocks with large price differences. An example of such a scenario would be BLK and WFC. If you bought 1000 shares of both, you would be weighed far to much on BLK thus killing your hedge so YES a formula is required.
Not number of shares, but dollar amount. Buy $1000 worth of BLK Sell $1000 worth of WFC Round the number of shares to a multiple of 5 or 10 to avoid odd number of shares.
From what I see in your formula you will always get the equivalent of: Buy $1,000 worth of XYZ. Sell $1,000 worth of ZYX.
Great observation! The whole purpose of the "formula" is so one can chart a spread. If you have questions about my methodology of pairs trading I would be more than happy to answer them via DM as I am trying to keep things on topic.