Correlation & Excel

[Lewis_c]

Wasn't sure where to post this but I guess as most people will use correlation with excel, this is as good a place as any.

I've tried correlation in the past and found it to be of minimal use in data analysis or bot building. While it is of some use regarding the relationship of one horse to another it can be very misleading and lead you in the wrong direction.

Take two horses, Runner 1 and Runner 2.

They both start at 2.0.

R1 drifts out to 2.02, then 2.04 all the way to 2.30

Meanwhile R2 comes in, tick for tick, exactly the opposite the R1... 1.99, 1.98, 1.98 etc and stops at 1.85

The correlation is perfect:

R1 R2
2.00 2.00
2.02 1.99 -1
2.04 1.98 -1
2.06 1.97 -1
2.08 1.96 -1
2.10 1.95 -1
2.12 1.94 -1
2.14 1.93 -1
2.16 1.92 -1
2.18 1.91 -1
2.20 1.90 -1
2.22 1.89 -1
2.24 1.88 -1
2.26 1.87 -1
2.28 1.86 -1
2.30 1.85 -1

Now we carry on, but at this point the prices start to reverse

R1 R2
2.30 1.85
2.28 1.86 -1
2.26 1.87 -1
2.24 1.88 -1
2.22 1.89 -1
2.20 1.90 -1
2.18 1.91 -1
2.16 1.92 -1
2.14 1.93 -1
2.12 1.94 -1
2.10 1.95 -1
2.08 1.96 -1
2.06 1.97 -1
2.04 1.98 -1
2.02 1.99 -1
2.00 2.00 -1

Can you see the problem? Although the correlation remains perfect (as it should as both horses are still doing exactly the opposite of each other), you end up exactly where you started. Correlation only shows the relationship between prices and will not tell you when the prices reverse.

Lewis.

[wearthefoxhat]

When analysing two variables that move in perfect opposition to each other (as you described), the correlation will indeed remain perfect (i.e., -1 for a perfect negative correlation), but this does not provide any direct information on when prices will reverse or reach a turning point.

To identify potential reversals or turning points, you might consider using additional statistical or technical analysis methods beyond correlation:

1. Moving Averages:

Calculate moving averages for both series to smooth out short-term fluctuations and identify trends.
When the moving averages of the two series converge, cross, or diverge significantly, it could signal a potential reversal or change in trend.

2. Relative Strength Index (RSI):

RSI is a momentum oscillator that measures the speed and change of price movements. An RSI below 30 typically indicates that an asset is oversold, while an RSI above 70 indicates it is overbought.
Applying RSI to both series might give insight into overbought or oversold conditions that could signal a reversal.

3. Divergence Analysis:

Divergence occurs when the price of an asset moves in the opposite direction of a technical indicator. For instance, if one series continues to fall while an indicator like RSI or MACD starts to rise, this could indicate a potential reversal. Look for divergences between the price action and indicators such as RSI, MACD, or the moving averages of the two series.

4. Bollinger Bands:

Bollinger Bands consist of a moving average and two standard deviations above and below it. When the prices move outside these bands, it may indicate that the market is overbought or oversold, potentially leading to a reversal.
Use Bollinger Bands to analyze the volatility and potential reversal points.

5. Support and Resistance Levels:

Identify key support and resistance levels where prices have historically reversed. When one series approaches a significant support or resistance level, it could signal a potential reversal.

6. Regression Analysis:

Perform regression analysis to model the relationship between the two series over time. Significant changes in the regression slope might indicate a reversal.

7. Pattern Recognition:

Use pattern recognition techniques to identify chart patterns such as head and shoulders, double tops, or bottoms, which are commonly associated with price reversals.

8. Statistical Measures like Z-Scores:

Z-scores can be used to determine how far away a value is from the mean in terms of standard deviations. Extreme Z-scores (either high or low) might indicate an overextended move, which could precede a reversal.

Example:

Suppose you're using RSI to detect potential reversals. If the RSI for both series is approaching extreme levels (e.g., RSI > 70 or RSI < 30), you might anticipate a reversal.

=IF(RSI_A > 70 AND RSI_B > 70, "Potential Reversal", "No Reversal")

This formula would signal a potential reversal when the RSI for both series is above 70.

Summary:

Correlation alone won't tell you when prices will reverse, but by combining correlation analysis with other statistical or technical analysis tools, you can better identify potential turning points in the market.

[MemphisFlash]

The correlation of your problem is directly proportional to the time spent writing the problem