In this topic pippa was asking about how markets relate to one another.

I thought that I would kick off a new topic that shows a scientific way to measure the relationship between two markets. I created a spreadsheet that you can download and use as a template for doing this sort of thing. If you can operate a spreadsheet then you can do this yourself.

Here are the results from the spreadsheet.

Market correlation of closing prices from 1 June 2007 to 13 July 2007.

	ES	NQ	YM	ZB

ES	1.00	0.68	0.96	0.01

NQ	0.68	1.00	0.77	-0.11

YM	0.96	0.77	1.00	0.05

ZB	0.01	-0.11	0.05	1.00

The correlation coefficient shown above measures how closely one market moves in relation to another. The correlation is a value from -1 to 1. -1 Indicates a perfect negative correlation, 1 indicates a perfect positive correlation and 0 indicates no correlation.

The ZB and NQ, for example, have a weak negative correlation. This means that when the NQ moves up the ZB will move down. The YM and ES have a strong (96%) correlation. The YM and ES will almost always move in step with each other.

The attached spreadsheets show the daily closing prices of the markets but there's nothing stopping you from doing this on intraday prices. Make sure that you line-up the times of your data series correctly if you're comparing European or Eastern markets with the US markets.

how do i increase the amount of data please so that the correl table still calculates.

The easiest way to get the correlation of 2 sets of prices is to do the following.

1. Put the data into 2 columns in the spreadsheet.


2. Under those 2 columns, in any available unused cell, enter the =CORREL() function.


3. The =CORREL() function needs 2 parameters, these are the 2 columns of prices that you've entered.


4. Say the columns of prices are A and B and they run from row 1 to 200. The CORREL function will look like this: =CORREL(A1:A200,B1:B200)

If you want to do it the way that I've done it using named ranges and an indirect function reference then do the following.

Insert the number of extra rows you want somewhere in the middle of the data series. To do this, right click a row number on the left (say row 8) and from the menu select insert. This will insert 1 row. Say you wanted to insert 100 rows. Then select 100 rows starting from row 8. The right click the rows select on the left and from the menu select insert. This will preserve the names in the spreadsheet. Paste your data in here and that should work.

Let me know if that solves it.

Are you using Excel 2007 btw?

Thanks. Very good information. I'm going to use it to underscore the hypothesis that the indexes are like a family, namely the S&P is the Mom and Pop, the YM the older sibling who doesn't stray far from the parents, and the NQ and ER2 are the youngest who run all over place before finally migrating back to mom and pop.

when I add a new index and copy the formulas
it doesnot calculate.
What am I missing here?

jaban: You need to name the new index that you add. What version of Excel are you using? Usually, just to the left of the formula bar is a space that you can type in a name. Use the same name that you use when you label the matrix below the columns. It uses that name to find the data.

steve: You're welcome. What time frame do you look at the correlation on?

Hi,
I use Excel 2002 plus the
microsoft add. update files.

I added the new names as you said
that's oke now , but still get a wrong value
sign in the matrix
Version problem maybe?

or is there a " . " and " , " problem , as there is
a difference between US and European versions?

Your version should be okay. Does the spreadsheet show a #VALUE value?

Update: Jaban emailed me his spreadsheet and I fixed it for him. He hadn't named the ranges correctly so let me give some extra instructions for someone extending the spreadsheet:

Say you want to add the DAX to the data...

1. In cell F1 enter DAX


2. In cells F2 to F31 enter the DAX's data


3. In cell F33 enter DAX


4. In cell A38 enter DAX


5. Name the range F2:F31 DAX


6. Copy/paste the formula from E34:E37 to F34:F37


7. Copy/paste the formula from B37:F37 to B38:F38

The important thing to remember is that the labels on the matrix below the price ranges have the same name (exactly) as the ranges that you name of the spreadsheet.

I look at it intra-day to see who is leading and lagging. I use a dashboard system to gauge whether a move has momentum by looking at who is participating in the directional move and to what degree they are participating. If everyone is going down the same path I have found that my probability of a successful trade is greater.

Do you plot the correlation of the market that you're trading against another market or indicator? Or do you just keep an eye on the correlation coefficient on the dashboard?

Comments received by email and published with author's permission:

quote:

---

Your statistical "correlation coefficient" is correct especially because the basic correlation lies in the fact that each and every on of the YM components also reside in the ES so it stands to reason that when YM starts to move (Up or Down), the ES will shadow that move with reciprocity between the ES moves and the YM then tagging along;

On the other hand there is no commonality between NQ or ZB so they will reflect a move, one against the other, except in the most rare of occasions;

Moreover, and in ZB representing the 30 year Treasury Bonds while NQ represents the Nasdaq 100, it is easy to understand that advancing Markets negatively impact the Bond Markets, with the reverse at play when Bonds advance to the disadvantage of the Equity Markets . . .

---

quote:

---

As for correlating ER2 against the YM and/or ES, a quick look at the way ES, YM and NQ have been setting Higher Highs and Higher Closing Prices, it should be clear that NQ (which has a good number of its components in ES and even a couple in YM (eg: MSFT is an NQ component, is a YM component and is also an ES component)) can have a correlated relation to the "more sophisticated Indices";

Those moves, on the other hand, are more of a "push me - pull you" variety;

ER2, on the other hand, virtually stands alone (see how it has been lagging below its early June High) without participating in the current "Big Boys" Market Run;

That's because ER2 is comprised of the "aspiring, promising newer corporation" which have yet to make their mark and may never do so because if and when they start to make that mark, the larger corporation may use Merger and Acquisition tactic to take the smaller caps out;

Another factor at work on the ER2 is that it was "Rebalanced" at the close of Quadruple Witching Expiration with that nature of that "Rebalancing" still being digested by Analysts, Managers, the Institutions, et al.;

Similarly, ES is still in the process of being adjusted to fill in the 8 vacancies caused by the disappearance of those equities taken out by M&A;

Those "disappearances" were complete by the end of June (ie: end of Q2 / end of the 1st half;

So far, WFR, PCP, and 2 others have been added to $SPX (ES), with 4 others still to be identified;

Those additions will assuredly bring about Volatility in the equities to be added and that, in turn, will bring about Volatility in ES;

---