# XCF - Cross Correlation Function

Calculates the cross-correlation function between two time series.

## Syntax

XCF(Y, X, K, Method, Return_type)
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 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. no-lag) 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

1. The time series is homogeneous or equally spaced.
2. The two time series must be identical in size.
3. The Pearson correlation, $r_{xy}$, is defined as follows:

$$r_{xy}= \frac{\sum_{i=1}^N(x_i-\bar{x})(y_i-\bar{y})}{\sqrt{\sum_{i=1}^N(x_i-\bar{x})^2\times\sum_{i=1}^N(y_i-\bar{y})^2}}$$

Where:
• $\bar{x}$ is the sample average of time series X
• $\bar{y}$ is the sample average of time series Y
• $x_i \in X$ is a value from the first input time series data
• $y_i \in Y$ is a value from the second input time series data
• $N$ is the number of pairs $\left ( x_i,y_i \right )$ that do not contain a missing observation
4. The Spearman rank correlation, $h$, is defined as follows:

$$r =1-\frac{6\sum ( x_i-y_i )^2}{N\times(N^2-1}$$

Where:
• $x_i \in X$ (e.g. $x_i$ is in the first input time series data)
• $y_i \in Y$ (e.g. $y_i$ is in the second input time series data)
• $N$ is the number of pairs $\left ( x_i,y_i \right )$ that do not contain a missing observation
5. The Kendall tau ($\tau$) rank correlation is defined as follows:

$$\tau =\frac{N_C-N_D}{\frac{1}{2}N(N-1)}$$

Where:
• $N_C$ is the number of concordant pairs of observations, $(x_i, y_i)$ and $(x_j, y_j)$,
defined such that the ranks of the pairs of elements are in agreement.
That is, if $x_i \gt x_j$ then $y_i \gt y_j$, and if $x_i \lt x_j$ then $y_i \lt y_j$
• $N_D$ is the number of discordant pairs of observations, $(x_i, y_i$) and $(x_j, y_j)$, defined such that the ranks of the pairs of elements are not in agreement
• $N$ is the number of pairs $\left ( x_i,y_i \right )$ that do not contain a missing observation

## Examples

Example 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
A B C
Date Series1 Series2
1/1/2008 #N/A -2.61
1/2/2008 -2.83 -0.28
1/3/2008 -0.95 -0.90
1/4/2008 -0.88 -1.72
1/5/2008 1.21 1.92
1/6/2008 -1.67 -0.17
1/7/2008 0.83 -0.04
1/8/2008 -0.27 1.63
1/9/2008 1.36 -0.12
1/10/2008 -0.34 0.14
1/11/2008 0.48 -1.96
1/12/2008 -2.83 1.30
1/13/2008 -0.95 -2.51
1/14/2008 -0.88 -0.93
1/15/2008 1.21 0.39
1/16/2008 -1.67 -0.06
1/17/2008 -2.99 -1.29
1/18/2008 1.24 1.41
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 Method (0.448)
=XCF(\$B\$2:\$B\$20,\$C\$2:\$C\$20,3) Kendall Method (0.279)