Hi all,
How can we analyze the cross-correlations (8 lags, 8 leads) between variable B and the 4 other variables (C, D, E, F)?
Variable A represents the date of the corresponding observation. We would like to calculate the cross correlation of variable B versus the remaining 4 variables (i.e. C, D, E, F). We tried to solve this issue with =CORREL(OFFSET(
Dataset structure (example data):
<colgroup><col><col><col span="4"><col></colgroup><tbody>
</tbody>
In the end, the solution should look like this:
C D E F
Lag-8 X X X X
Lag-7 X X X X
Lag-6 X X X X
Lag-5 X X X X
Lag-4 X X X X
Lag-3 X X X X
Lag-2 X X X X
Lag-1 X X X X
Lag0 X X X X
Lag1 X X X X
Lag2 X X X X
Lag3 X X X X
Lag4 X X X X
Lag5 X X X X
Lag6 X X X X
Lag7 X X X X
Lag8 X X X X
Many thanks for your support.
How can we analyze the cross-correlations (8 lags, 8 leads) between variable B and the 4 other variables (C, D, E, F)?
Variable A represents the date of the corresponding observation. We would like to calculate the cross correlation of variable B versus the remaining 4 variables (i.e. C, D, E, F). We tried to solve this issue with =CORREL(OFFSET(
Dataset structure (example data):
B | C | D | E | F | Lags | |
1995-Q1 | 1.00 | 1.00 | 1.00 | 1.00 | ||
1995-Q2 | 0.01% | 2.00 | 2.00 | 2.00 | 2.00 | -8 |
1995-Q3 | 0.02% | 3.00 | 3.00 | 3.00 | 3.00 | -7 |
1995-Q4 | 0.03% | 4.00 | 4.00 | 4.00 | 4.00 | -6 |
1996-Q1 | 0.04% | 5.00 | 5.00 | 5.00 | 5.00 | -5 |
1996-Q2 | 0.05% | 6.00 | 6.00 | 6.00 | 6.00 | -4 |
1996-Q3 | 0.06% | 7.00 | 7.00 | 7.00 | 7.00 | -3 |
1996-Q4 | 0.07% | 8.00 | 8.00 | 8.00 | 8.00 | -2 |
1997-Q1 | 0.08% | 9.00 | 9.00 | 9.00 | 9.00 | -1 |
1997-Q2 | 0.09% | 10.00 | 10.00 | 10.00 | 10.00 | 0 |
1997-Q3 | 0.10% | 11.00 | 11.00 | 11.00 | 11.00 | 1 |
1997-Q4 | 0.11% | 12.00 | 12.00 | 12.00 | 12.00 | 2 |
1998-Q1 | 0.12% | 13.00 | 13.00 | 13.00 | 13.00 | 3 |
1998-Q2 | 0.13% | 14.00 | 14.00 | 14.00 | 14.00 | 4 |
1998-Q3 | 0.14% | 15.00 | 15.00 | 15.00 | 15.00 | 5 |
1998-Q4 | 0.15% | 16.00 | 16.00 | 16.00 | 16.00 | 6 |
1999-Q1 | 0.16% | 17.00 | 17.00 | 17.00 | 17.00 | 7 |
1999-Q2 | 0.17% | 18.00 | 18.00 | 18.00 | 18.00 | 8 |
<colgroup><col><col><col span="4"><col></colgroup><tbody>
</tbody>
In the end, the solution should look like this:
C D E F
Lag-8 X X X X
Lag-7 X X X X
Lag-6 X X X X
Lag-5 X X X X
Lag-4 X X X X
Lag-3 X X X X
Lag-2 X X X X
Lag-1 X X X X
Lag0 X X X X
Lag1 X X X X
Lag2 X X X X
Lag3 X X X X
Lag4 X X X X
Lag5 X X X X
Lag6 X X X X
Lag7 X X X X
Lag8 X X X X
Many thanks for your support.