ACFCI (Pro.)

Calculates the confidence interval limits (upper/lower) for the autocorrelation function.

Syntax

ACFCI(X, Order, K, alpha, upper)

X
is the univariate time series data (a one dimensional array of cells (e.g. rows or columns)).

Order
is the time order of the 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)

K
is the lag order (e.g. k=0 (no lag), k=1 (1st lag), etc.). If missing, the default of k=1 is assumed.

alpha
is the statistical significance level. If missing, the default of 5% is assumed.

upper
If true, returns the upper confidence interval limit. Otherwise, returns the lower limit.

Value	Description
0	return lower limit
1	return upper limit

Remarks

The time series is homogeneous or equally spaced.
The time series may include missing values (e.g. #N/A) at either end.
The lag order (k) must be less than the time series size, or else an error value (#VALUE!) is returned.
The ACFCI function calculates the confidence limits as:

$ \hat\rho_k - Z_{\alpha/2}\times \sigma_{\rho_k} \leq \rho_k \leq \hat\rho_k+ Z_{\alpha/2}\times \sigma_{\rho_k} $

Where:
- $\rho_k$ is the population autocorrelation function.
- $\sigma_{\rho_k}$ is the standard error of the sample autocorrelation.
- $\hat{\rho_{k}}$ is the sample autocorrelation function for lag k.
- $Z\sim N(0,1)$
- $P(\left|Z\right|\geq Z_{\alpha/2}) = \alpha$
For the case in which the underlying population distribution is normal, the sample autocorrelation also has a normal distribution:

$\hat \rho_k \sim N(\rho_k,\sigma_{\rho_k}^2)$

Where:
- $\hat \rho_k$ is the sample autocorrelation for lag k.
- $\rho_k$ is the population autocorrelation for lag k.
- $\sigma_{\rho_k}$ is the standard error of the sample autocorrelation for lag k.
Bartlett proved that the variance of the sample autocorrelation of a stationary normal stochastic process (i.e. independent, identically normal distributed errors) can be formulated as:

$ \sigma_{\rho_k}^2 = \frac{\sum_{j=-\infty}^{\infty}\rho_j^2+\rho_{j+k}\rho_{j-k}-4\rho_j\rho_k\rho_{i-k}+2\rho_j^2\rho_k^2}{T} $
Furthermore, the variance of the sample autocorrelation is reformulated:

$ \sigma_{\rho_k}^2 = \frac{1+\sum_{j=1}^{k-1}\hat\rho_j^2}{T} $

Where:
- $\sigma_{\rho_k}$ is the standard error of the sample autocorrelation for lag k.
- $T$ is the sample data size.
- $\hat\rho_j$ is the sample autocorrelation function for lag j.
- $k$ is the lag order.

Examples

Example 1:

	A	B
1	Date	Data
2	1/1/2008	#N/A
3	1/2/2008	-1.28
4	1/3/2008	0.24
5	1/4/2008	1.28
6	1/5/2008	1.20
7	1/6/2008	1.73
8	1/7/2008	-2.18
9	1/8/2008	-0.23
10	1/9/2008	1.10
11	1/10/2008	-1.09
12	1/11/2008	-0.69
13	1/12/2008	-1.69
14	1/13/2008	-1.85
15	1/14/2008	-0.98
16	1/15/2008	-0.77
17	1/16/2008	-0.30
18	1/17/2008	-1.28
19	1/18/2008	0.24
20	1/19/2008	1.28
21	1/20/2008	1.20
22	1/21/2008	1.73
23	1/22/2008	-2.18
24	1/23/2008	-0.23
25	1/24/2008	1.10
26	1/25/2008	-1.09
27	1/26/2008	-0.69
28	1/27/2008	-1.69
29	1/28/2008	-1.85
30	1/29/2008	-0.98

	Formula	Description (Result)
	=ACF($B$2:$B$30,1,1)	Autocorrelation of order 1 (0.235)
	=ACFCI($B$2:$B$30,1,1,5%,1)	Upper confidence interval for ACF of order 1 (0.37)
	=ACFCI($B$2:$B$30,1,1,5%,0)	lower confidence interval for ACF of order 2 (-0.37)

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

External Links

P.A.P Moran; Testing of Significance of Correlation between Time Series, page 397; Biometrica, 1947
Wikipedia - Confidence Interval