I am trying to get my Excel to mimic this post from Brett Steenbarger but have been unable to. I was hoping some our programming experts might be able to help. The programming part needed is: He says a matter of minutes to create... :roll eyes: Guess that depends on how much Excel you know. Any help would be appreciated as always. -------------- Follow up: after emailed Brett, he shared this with me:
the most difficult part is the real time data feed (assuming you want that as well, even though he's just taking a static snapshot). Otherwise, he pretty much explained it. And it's pretty simple. You should ask him for a copy of his spreadsheet. It will be faster that way. Otherwise just follow what he says, 1) make 4 columns for the sectors (not sure why he only correlates 4, it should be all the majors... but anyways). 2) make four more columns and take the relative change between two vertical cells for each etf (y2/y1-1), and drag it across and down to fill the four new columns. 3)Create n more columns (equal to number of possible combos of correlations, here it equals 6)Take the n period (I think he used 26) correlation, every n periods. i.e. count down 26 cells and for each unique column pair (ex. change A and change B), select the 26X2 array (up to the 26th cell position) and use tools/dataanalysis/correlation function , then repeat for all possible combos (6 here). 4) Then just average the 6 combos in a last column, and plot that column vs. the iindex you want to compare (in this case s&p). You could link it to yahoo finance and update in 15 min intervals, but it gets a bit more tedious and requires vba. Make sure to verify its usefulness (sounds intuitive, but better to check).
Let me know if you are able to follow my instructions. Also you will need to find a 15 minute data feed to replicate his work. You can do it in real time with yahoo, but for backtesting, you need to find a 15 minute source in .csv (or suitable) format. You can play with daily quotes to start building.
I put together a simple three ETF example for you. Add as many ETFs as you want to look at the usefulness of the correlation index (here I just used a random generator to output the 3 ETF values and plotted ETF3 vs. avg corr of all 3, you can use yahoo finance to capture the daily data of actual ETFs, or find a 15 minute provider for the 15 min data). You could also manually enter the data every 15 min, but it's way to tedious. The author doesn't mention whether he does it in real time. oops... ETF change 1 should be B1/B0-1. ETF change 2, C1/C0-1, ETF change 3: D1/D0-1 My example also looks at correlation of 6 periods, you can extend to any number of periods you want (I think his example is 26). Just make sure to start 26 or however many periods down for the 1st correlation pair. Also, notice this example shows correlation going negative, implying a range bound interval according to the author.
If anyone has a handy vba dynamic update module to a 15 min price quote, can you post it for the OP? It'll save a lot of time.