Go Back   Traders Laboratory > Trading Resources > Trading Indicators

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

Comment
Bookmarks
del.icio.us StumbleUpon Google Digg Facebook Furl Reddit Netscape

 
LinkBack Release Tools Display Modes Language
Excel Help Needed
Software Version: , by brownsfan019 (Premium Trader) brownsfan019 is offline
Developer Last Online: May 2008

Trading Platform: Rating: -
Release Date: 04-16-2008 Last Update: n/a Installs: 0

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.
 
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, 85 views)
Last edited by thrunner; 04-16-2008 at 10:08 PM.
Reply With Quote
  #1 (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?
Reply With Quote
  #2 (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
Reply With Quote
  #3 (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.
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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Release Release Starter Category Comments Last Post
MP on excel gomes Market ProfileŽ 114 02-03-2008 04:04 PM
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 09:55 PM.

 


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