"Assembly line" of pair trade ratios

[marko1046]

Does anyone have any experience in creating a loop on Google Sheets?

 

[i960]

If you just want to chart a spread of relative strength stop using a difference function and just chart the division.

 

[OTM-Options]

Does anyone have any experience in creating a loop on Google Sheets?

JavaScript ...... Below is a code snippet that should get you started. Tools > Script Editor Enter the following JavaScript code and Save.

function stocks() {
var stocks = ["AAPL", "GOOGL", "PCLN", "QQQ","MSFT","AMZN"];
   for (i in stocks); {
     return stocks
}
}

function fun_with_math(C1) {
  var results = C1 * 23
   return results
}

• In cell A1 enter: =stocks()
• In cell B1 enter: =fun_with_math(C1)
• In cell C1 enter any number.

 

[dealmaker]

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

PairCo has all the pair combinations but I think they'll want to charge you for it...


ps I am not affiliated with them