Excel help needed

B

brownsfan019

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:
Above is one of my analytical tools that helps me identify market conditions as they're evolving. The blue line is the S&P 500 Index (SPY) at 15-minute intervals. As we can see, over the two day period charted, we have been quite rangebound.

The pink line represents the correlation among four key S&P 500 sectors: financials (XLF), energy (XLE), consumer discretionaries (XLY) and consumer staples (XLP). I calculate the correlation of each sector with every other sector over a moving one-day period (26 15-minute periods) and then plot the average of those correlations. Historically, this average correlation among sectors is .53. When we see the correlation significantly higher than .53, it suggests that the different sectors are moving very much in tandem intraday. When we see the correlation significantly lower than .53, it suggests that the different sectors are not moving in unison.
More...

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:

You have each ETF in a separate column of 15 minute closing values and then you calculate the 15-minute price change for the four ETFs (A,B,C,D) in a next set of columns. You then correlate the price changes for each ETF with every other ETF (A with B; A with C; A with D; B with C; B with D; C with D) over a period of one day (27 15-minute periods). The final sector correlation is simply the average of the six individual correlations and is updated every fifteen minutes.
More...
 
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).
 
Quote from dtrader98:

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.
More...

I did. No luck.

Make sure to verify its usefulness (sounds intuitive, but better to check). [/B]
More...

That's why I want to build it and see how it looks in real-time and such.
 
Quote from brownsfan019:

I did. No luck.



That's why I want to build it and see how it looks in real-time and such.
More...

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.

b7xis9.jpg



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.
 
You must log in or register to reply here.
Back
Top