Have a Question?
Phone: +1 (888) 427-9486
+1 (312) 324-0367
Fax: +1 (312) 238-9092
Contact Us
XCF
Calculates the cross-correlation function between two time series.
Syntax
XCF(X, Order_1, Y, Order_2)
X
is the first univariate time series data (a one dimensional array of cells (e.g. rows or columns)).
Order_1
is the time order of the first data series (i.e. whether the first data point corresponds to the earliest or latest date (earliest date=1 (default), latest date=0)).
| Order | Description |
|---|---|
| 1 | ascending (the first data point corresponds to the earliest date) (default) |
| 0 | descending (the first data point corresponds to the latest date) |
Y
is the second univariate time series data (a one dimensional array of cells (e.g. rows or columns)).
Order_2
is the time order of the second data series (i.e. whether the first data point corresponds to the earliest or latest date (earliest date=1 (default), latest date=0)).
| Order | Description |
|---|---|
| 1 | ascending (the first data point corresponds to the earliest date) (default) |
| 0 | descending (the first data point corresponds to the latest date) |
Remarks
- The time series is homogeneous or equally spaced.
- The two time series must have identical size.
- The correlation is defined as:
![\[ \rho_{xy}= \frac{\sum_{t=1}^T(x_t-\bar x)(y_t-\bar y)}{\sqrt{\sum_{t=1}^T(x_t-\bar x)^2\times\sum_{t=1}^T(y_t-\bar y)^2}} \]](/sites/all/files/tex/26c9149ca11b3abc50d206ff92bd55deed52dcef.png)
Where:
-
is the sample cross-correlation between the X and Y time series.
-
is the sample average of the X time series.
-
is the sample average of the Y time series.
-
is the number of common non-missing observations in both the X and Y time series.
-
Examples
Example 1:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Series1 | Series2 |
| 2 | 1/1/2008 | #N/A | -2.61 |
| 3 | 1/2/2008 | -2.83 | -0.28 |
| 4 | 1/3/2008 | -0.95 | -0.90 |
| 5 | 1/4/2008 | -0.88 | -1.72 |
| 6 | 1/5/2008 | 1.21 | 1.92 |
| 7 | 1/6/2008 | -1.67 | -0.17 |
| 8 | 1/7/2008 | 0.83 | -0.04 |
| 9 | 1/8/2008 | -0.27 | 1.63 |
| 10 | 1/9/2008 | 1.36 | -0.12 |
| 11 | 1/10/2008 | -0.34 | 0.14 |
| 12 | 1/11/2008 | 0.48 | -1.96 |
| 13 | 1/12/2008 | -2.83 | 1.30 |
| 14 | 1/13/2008 | -0.95 | -2.51 |
| 15 | 1/14/2008 | -0.88 | -0.93 |
| 16 | 1/15/2008 | 1.21 | 0.39 |
| 17 | 1/16/2008 | -1.67 | -0.06 |
| 18 | 1/17/2008 | -2.99 | -1.29 |
| 19 | 1/18/2008 | 1.24 | 1.41 |
| 20 | 1/19/2008 | 0.64 | 2.37 |
| Formula | Description (Result) | |
|---|---|---|
| =XCF($B$2:$B$20,1,$C$2:$C$20,1) | Sample cross correlation with missing values (0.317) | |
| =XCF($B$3:$B$20,1,$C$3:$C$20,1) | Sample cross correlation without missing values (0.317) |
References
-
Hamilton, J .D.; Time Series Analysis
, Princeton University Press (1994), ISBN 0-691-04289-6
-
Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0-471-690740
