Phone: +1 (888) 4279486
+1 (312) 2573777
Contact Us
XCF
Calculates the cross correlation function between two time series.
Syntax
Y
is the first univariate time series data (a one dimensional array of cells (e.g. rows or columns).
X
is the second univariate time series data (a one dimensional array of cells (e.g. rows or columns).
K
is the lag^{i} order (e.g. 0=no lag, 1=1st lag, etc.) to use with second time series input (X). If missing, the default lag order of zero (i.e. nolag) is assumed.
Method
is a switch to select the calculation method (1=Pearson (default), 2=Spearman, 3=Kendall).
Order  Description 

1  Pearson (default) 
2  Spearman 
3  Kendall 
Return_type
is a switch to select the return output (1 = correlation value(default), 2 = Std Error).
Method  Description 

1  Correlation Value 
2  Standard Error 
Remarks
 The time series is homogeneous or equally spaced.
 The two time series must be identical in size.

The Pearson correlation, , is defined as follows:Where:
 is the sample average of time series X
 is the sample average of time series Y
 is a value from the first input time series data
 is a value from the second input time series data
 is the number of pairs that do not contain a missing observation

The Spearman Excel rank correlation, , is defined as follows:Where:
 (e.g. is in the first input time series data)
 (e.g. is in the second input time series data)
 is the number of pairs that do not contain a missing observation

The Kendall tau () rank correlation is defined as follows:Where:

is the number of concordant pairs of observations, and ,
defined such that the ranks of the pairs of elements are in agreement.
That is, if and or if and .  is the number of discordant pairs of observations, ) and , defined such that the ranks of the pairs of elements are not in agreement. That is, if and or if and .
 is the number of pairs that do not contain a missing observation

is the number of concordant pairs of observations, and ,
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,$C$2:$C$20,1)  Pearson Method (0.317)  
=XCF($B$2:$B$20,$C$2:$C$20,2)  Spearman Excel Method (0.448)  
=XCF($B$2:$B$20,$C$2:$C$20,3)  Kendall Method (0.279) 
Files Examples
References
 Hamilton, J .D.; Time Series Analysis , Princeton University Press (1994), ISBN 0691042896
 Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0471690740