# 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:

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 Method (0.448)
=XCF(\$B\$2:\$B\$20,\$C\$2:\$C\$20,3) Kendall Method (0.279)