|
PureBytes Links
Trading Reference Links
|
Hi Ron, Hi list,
To simplify the correlation study in Excel here is a flexible formula useful
for any study which needs a certain lookback period (data is Brit. Pound):
A B C D E
F
1 Date Open High Low Close 3
2 16-Mai-86 1,5110 1,5144 1,5051 1,5060
3 19-Mai-86 1,5052 1,5247 1,5026 1,5233
4 20-Mai-86 1,5156 1,5167 1,5028 1,5052
5 21-Mai-86 1,4971 1,5061 1,4955 1,4985
6 22-Mai-86 1,4938 1,4968 1,4782 1,4808
7 23-Mai-86 1,4777 1,4898 1,4720 1,4810 + 19,7% (*)
8 27-Mai-86 1,4820 1,5025 1,4811 1,4937 + 17,6%
9 28-Mai-86 1,4931 1,4985 1,4844 1,4885 - 8,0%
10 29-Mai-86 1,4887 1,4889 1,4765 1,4784 + 65,9%
11 30-Mai-86 1,4697 1,4720 1,4592 1,4637 + 10,3%
(*) put this formula in cell F7 and copy down:
=CORREL(OFFSET(B7,($F$1)*-1,0):OFFSET(E6,($F$1-1)*-2,0),OFFSET(B7,($F$1-1)*-
1,0):OFFSET(E7,0,0)) which is equivalent to CORREL(B2:E4,B5:E7) and
calculates the Autocorrelation between two consecutive 3 day time windows.
This allows just to put the lookback period into cell F1 and can alos be
used with other functions like mean, sum, etc. Advantage is that you always
have the same formula, even when comparing different time horizons like
3/5/8/13/21day correlation.
HTH & Happy New Year,
Thomas
PS: I am not quite sure what the formula '=correl(A$3$:D$5$,A3:D3)'
actually does [besides, the $ signs are not put correctly in this example,
it should be like '=correl($A$3:$D$5,A3:D3)' ]. Could you please specify the
formula again, I get an #NV error with this formula. Thank you.
----------------------------------------------------------------------------
Thomas Pfluegl
Rudersdorf 8
A - 4212 Neumarkt
Austria
TEL +43 / 7941 / 8106
e-mail: thomas.pfluegl@xxxxxxxxxxxxxxxxx
----------------------------------------------------------------------------
Austria/Europe --> high mountains --> Mozart --> no kangaroos
----------------------------------------------------------------------------
|