Excel Help Needed - Traders Laboratory

Go Back   Traders Laboratory > Trading Resources > Trading Indicators

Trading Indicators Post your custom trading indicators. If you download, remember to click INSTALL.


Comment
 
LinkBack Release Tools Display Modes
Excel Help Needed
Software Version: , by brownsfan019 brownsfan019 is offline
Developer Last Online: Jul 2008

Trading Platform: Rating:
Release Date: 04-16-2008 Last Update: n/a Installs: 2
Supported
Beta Stage

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:
Quote:
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.

Show Your Support

  • To receive notifications regarding updates -> Click to Mark as Installed.
  • If you like this indicator, please consider donating to the developer.
  • This modification may not be copied, reproduced or published elsewhere without the author's permission.
Thanks: 30
Thanked 85 Times in 56 Posts
  #1 (permalink)  
By thrunner on 04-16-2008, 08:59 PM
Re: Excel Help Needed

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):
Quote:
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" ) ;
Attached Images
File Type: gif Correlation4 SPY XLF 2008-04-16_194642.gif (20.9 KB, 147 views)
Last edited by thrunner; 04-16-2008 at 10:08 PM.
Thanks: 60
Thanked 47 Times in 25 Posts
Reply With Quote
  #2 (permalink)  
By brownsfan019 on 04-16-2008, 09:59 PM
Re: Excel Help Needed

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?
Thanks: 30
Thanked 85 Times in 56 Posts
Reply With Quote
  #3 (permalink)  
By brownsfan019 on 04-17-2008, 12:34 AM
Re: Excel Help Needed

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

Quote:
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
Thanks: 30
Thanked 85 Times in 56 Posts
Reply With Quote
  #4 (permalink)  
By darthtrader on 04-17-2008, 09:17 AM
Re: Excel Help Needed

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.
Thanks: 5
Thanked 31 Times in 17 Posts
Reply With Quote
  #5 (permalink)  
By Upthrust86 on 06-02-2008, 07:24 PM
Re: Excel Help Needed

I like this indicator, too, and have been trying to code it into e-Signal or metastock. Has anyone been able to do this?
Thanks: 1
Thanked 0 Times in 0 Posts
Reply With Quote
Comment


Currently Active Users Viewing This Release: 1 (0 members and 1 guests)
 
Release Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Forum Jump

Similar Threads
Release Release Starter Category Comments Last Post
MP on excel gomes Market Profile® 115 05-24-2008 10:56 AM
Market Profile in Excel winnie Market Profile® 5 01-31-2008 11:13 AM
Trading Strategy in Excel. TheJudge Beginners Forum 1 12-06-2007 07:49 AM
Technical analysis in Excel TheJudge Beginners Forum 2 11-06-2007 06:47 AM
Interesting Excel Sheets TinGull General Discussion 2 03-23-2007 04:46 PM


All times are GMT -4. The time now is 03:01 AM.

 


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70