Excel Help Needed

[brownsfan019 17]

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.

 

He says a matter of minutes to create... Guess that depends on how much Excel you know.

 

Any help would be appreciated as always.

[thrunner 10]

Sorry, don't really program Excel but here is a link for doing Pearson correlation in Excel: http://www.mnstate.edu/wasson/ed602calccorr.htm

 

However, correlation of multiple data series is fairly easy in Tradestation EL. One can import more data points and additional data series as well (such as the other sectors).

 

re: code for TS Correlation:

Insert 15 min chart SPY as data 1, XLF, XLE, XLY, XLP as data 2,3,4,5 respectively. You will need real time subscription if you are using TS for mixing these data. MC doesn't complain as much about these data. Then apply the following indicator (name it Correlation4 for example):

inputs:

IndepData( Close of data1 ),

DepData1( Close of data2 ),

DepData2( Close of data3 ),

DepData3( Close of data4 ),

DepData4( Close of data5 ),

 

Length( 26 ),

Length2(3),

PosCorrAlert( .7 ),

NegCorrAlert( -.7 ) ;

 

variables:

CorrelVal( 0 ),

CV1(0), CV2(0), CV3(0), CV4(0)

;

 

 

//CorrelVal = ( Correlation( IndepData, DepData, Length ) +

CV1= DepData1;

CV2= DepData2;

CV3= DepData3;

CV4= DepData4;

 

 

CorrelVal = ( Correlation( CV2, CV1, Length ) +

Correlation( CV3, CV2, Length ) +

Correlation( CV4, CV1, Length ) +

Correlation( CV4, CV2, Length ) +

Correlation( CV4, CV3, Length ) +

Correlation( CV1, CV3, Length ) ) / 6

;

 

//Plot1( jtHMA(CorrelVal, 6) , "Correl" ) ;

Plot1 (CorrelVal, "Correl");

//Plot2( PosCorrAlert, "PosCorrAlert" ) ;

//Plot3( NegCorrAlert, "NegCorrAlert" ) ;

 

{ Alert criteria }

if CorrelVal crosses over PosCorrAlert then

Alert( "Positive correlation alert" )

else if CorrelVal crosses under NegCorrAlert then

Alert( "Negative correlation alert" ) ;

Edited April 17, 2008 by thrunner

[brownsfan019 17]

th - thanks!!

 

Can you post the EL that goes with this that you mentioned? I have MultiCharts and can import into that.

 

Thanks!

 

Any other takers?

[brownsfan019 17]

I got an email back from Brett with this explaining how to do in Excel:

 

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.

 

 

Ummm... ok.....

 

 

Thanks to thrunner for the TS code! I will try in MC tomorrow.

 

I would also like to get the Excel sheet working as well for anyone that can help figure this '5 minute' project out...

 

THANKS

[darthtrader 10]

I think this is where you really want to learn some basic VBA if you really want to work with excel like this. I've been contemplating this but just haven't got around to learning it.

For a straight out spreadsheet it shouldn't be too bad though, just alot of columns.

Subtract A2 from A1 to get the 15 minute change in price. Once you have those make the columns for all the correlations and use the CORREL function , ie =CORREL(A1:A27, B1:B27). Once you have those make one more column, add the six correlation values across and divide by 6.

[Upthrust86 10]

I like this indicator, too, and have been trying to code it into e-Signal or metastock. Has anyone been able to do this?