|
PureBytes Links
Trading Reference Links
|
I have a multi-data chart set up with daily SPX in data1 and daily DJIA in data2. I
have a 20 day Pearson's R calculation between data1 and data2. The results match up
exactly with the "correl" function in Excel until . . . I have SPX data, but no DJIA
data on 11/7/78. TS simply uses the prior day's value for DJIA to fill in the blank
which makes sense. However, the two correlations no longer match. This would seem
to be fine and should fix itself after 20 periods have elapsed. However, the
correlations stay way off until 11/30/82, when another missing DJIA point accidentally
fixes things. Now obviously the easy answer is to fix the DJIA data file so the dates
exactly match SPX in data1. But this isn't the right answer for several reasons.
Imagine what happens when you try to run correlations intra-day when one stock opens
a few minutes before or after the other--things get entirely screwed up. I am worried
that I don't understand how TS reconciles slightly differing data series when using
multi-data charts. More importantly, I'm worried that some critical numbers aren't
being calculated properly and could (will) cost money. Could someone take a look at the
following from Excel and see if it makes sense? By the way, at the bottom, I have
included the correlation function I got off the Omega-list which I have really enjoyed
using. I thought others might find it useful. Any suggestions are greatly appreciated.
Sincerely,
Lawson McWhorter
lawson@xxxxxxxxxxxxx
Date SPX DJIA TSCor ExcelCorrel
10/23/78 98.18 839.66 0.96 0.96
10/24/78 97.49 832.55 0.97 0.97
10/25/78 97.31 830.21 0.98 0.98
10/26/78 96.03 821.12 0.99 0.99
10/27/78 94.59 806.05 0.99 0.99
10/30/78 95.05 811.85 0.99 0.99
10/31/78 93.15 792.45 1.00 1.00
11/1/78 96.85 827.79 1.00 1.00
11/2/78 95.61 816.96 1.00 1.00
11/3/78 96.18 823.11 1.00 1.00
11/6/78 95.19 814.88 1.00 1.00
11/7/78 93.85 814.88 0.93 0.99 <--no data for DJIA this day
11/8/78 94.45 807.61 0.93 0.99 <--now correlations are different
11/9/78 94.42 803.97 0.91 0.99
11/10/78 94.77 807.09 0.89 0.99
11/13/78 93.13 792.01 0.86 0.99
11/14/78 92.49 785.26 0.84 0.98
11/15/78 92.71 785.60 0.81 0.98
11/16/78 93.71 794.18 0.77 0.97
11/17/78 94.42 797.73 0.73 0.96
11/20/78 95.25 805.61 0.67 0.95
11/21/78 95.01 804.05 0.59 0.93
11/22/78 95.48 807.00 0.50 0.91
11/24/78 95.79 810.12 0.45 0.89
11/27/78 95.39 813.84 0.48 0.90
11/28/78 95.15 804.14 0.48 0.89 <--way different
11/29/78 93.75 790.11 0.56 0.88
11/30/78 94.70 799.03 0.75 0.84
12/1/78 96.28 811.50 0.74 0.83
12/4/78 96.15 806.83 0.70 0.78
12/5/78 97.44 820.51 0.77 0.83
12/6/78 97.49 821.90 0.88 0.94
12/7/78 97.08 816.09 0.90 0.96 <--should be fixed by here
12/8/78 96.63 811.85 0.90 0.96
12/11/78 97.11 817.67 0.92 0.97
12/12/78 96.59 814.97 0.91 0.97
12/13/78 96.06 809.86 0.90 0.97
12/14/78 96.04 812.54 0.86 0.95
10/29/82 133.71 991.72 0.99 0.99
11/1/82 135.47 1005.70 0.98 0.99
11/2/82 137.49 1005.70 0.85 0.97 <-- no 11/2/82 for the DJIA
11/3/82 142.87 1065.49 0.82 0.96
11/4/82 141.85 1050.22 0.79 0.96
11/5/82 142.16 1051.78 0.79 0.97
11/8/82 140.44 1037.44 0.79 0.98
11/9/82 143.02 1060.25 0.83 0.98
11/10/82 141.16 1044.52 0.84 0.98
11/11/82 141.76 1054.73 0.86 0.98
11/12/82 139.53 1039.92 0.85 0.98
11/15/82 137.03 1021.43 0.86 0.98
11/16/82 135.42 1008.00 0.87 0.98
11/17/82 137.93 1027.50 0.89 0.98
11/18/82 138.34 1032.10 0.88 0.98
11/19/82 137.02 1021.25 0.89 0.98
11/22/82 134.22 1000.00 0.96 0.98
11/23/82 132.93 990.99 0.96 0.98
11/24/82 133.88 1000.00 0.96 0.98
11/26/82 134.88 1007.36 0.97 0.98
11/29/82 134.20 1002.85 0.97 0.98
11/30/82 138.54 1039.28 0.97 0.97 <--finally the numbers match
12/1/82 138.72 1031.09 0.99 0.99
12/2/82 138.82 1033.11 0.99 0.99
{
Function: Correl
Descr: Pearson's correlation coefficient. Square to get R^2.
Correl = Sum( (xi - xmean) * (yi - ymean) ) / ( n * sigmax * sigmay)
Author: Terry Snyder (tsnyder@xxxxxxxxxxxxxxxxx)
History: v1.0 Initial coding 4/19/95
}
Inputs: X(NumericSeries), Y(NumericSeries), Length(Numeric);
Vars: Xmean(0), YMean(0), Loop(0), SumBlah(0);
Xmean = Summation(X,LENGTH)/Length;
Ymean = Summation(Y,LENGTH)/Length;
SumBlah = 0;
For Loop = 0 to Length - 1 begin
SumBlah = SumBlah + ( X[Loop] - Xmean ) * ( Y[Loop] - Ymean );
End;
Correl = SumBlah / ( length * StdDev(X,Length) * StdDev(Y,Length));
|