Home / Statistical Tools / Analysis Tools / Time Series / How-To / Autocorrelation
Autocorrelation¶
Autocorrelation measures how strongly a series resembles itself a fixed number of periods earlier. This walkthrough uses twenty readings from a process that drifts, so consecutive readings are related and distant ones are not.
Goal¶
Compute the autocorrelation function over twenty readings, and read which lags fall outside the confidence bands.
Sample Data¶
Twenty consecutive bath temperature readings, in degrees. Press Copy for Excel, then paste into a blank worksheet.
| Temperature |
|---|
| 53.9 |
| 57.2 |
| 55.6 |
| 51.9 |
| 48.2 |
| 48.7 |
| 46.0 |
| 42.7 |
| 45.1 |
| 46.7 |
| 49.2 |
| 46.6 |
| 47.5 |
| 47.9 |
| 43.9 |
| 47.1 |
| 48.8 |
| 56.2 |
| 55.3 |
| 53.5 |
Steps¶
-
Put the data in Excel
Press Copy for Excel, click cell A1 in a blank worksheet, and press Ctrl+V. The header lands in row 1 and the twenty readings in rows 2 through 21.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Time Series → Autocorrelation.
-
Select your data
Select cells A1:A21.
-
Configure the analysis
Tick Temperature, then open the Correlation Options tab. The Correlation Options group offers Auto or Manual with a Lag Value: box that is enabled only under Manual.
Leave Auto selected and click Finish. With twenty rows, Auto computes five lags.
Result¶
A worksheet named ACF Temperature, with a needle chart and a table headed Autocorrelation Function. Columns are Lag, ACF, T-Stat and LBQ.
| Lag | ACF | T-Stat | LBQ |
|---|---|---|---|
| 1 | 0.7151 | 3.1981 | 11.8428 |
| 2 | 0.3426 | 1.0771 | 14.7113 |
| 3 | 0.0041 | 0.0123 | 14.7117 |
| 4 | -0.1315 | -0.3914 | 15.1873 |
| 5 | -0.2004 | -0.5920 | 16.3653 |
Lag 1 is 0.7151. A reading is strongly related to the one before it, which is what a drifting process looks like. By lag 3 the relationship has gone: 0.0041 is as close to nothing as this data gets.
LBQ is cumulative. It is not a per-lag figure; each row's value includes every lag up to and including that row, which is why it only ever climbs.
Lag 0 is not in the table. Every series correlates perfectly with itself at lag 0, so that row would always read 1.0 and is left out of both the table and the chart.
The confidence bands¶
The chart draws a pair of dashed bands. They are not straight lines on this analysis: each lag has its own standard error, which grows as earlier lags are absorbed into it, so the bands widen from left to right.
| Lag | Standard error | Band at that lag |
|---|---|---|
| 1 | 0.2236 | ±0.4680 |
| 2 | 0.3180 | ±0.6656 |
| 3 | 0.3360 | ±0.7032 |
| 4 | 0.3360 | ±0.7032 |
| 5 | 0.3385 | ±0.7086 |
The multiplier is the 97.5th percentile of a t distribution with 19 degrees of freedom, 2.0930, so the band at lag 1 is 2.0930 × 0.2236 = 0.4680.
Only lag 1 reaches outside its band, at 0.7151 against a band of 0.4680. Every other lag is well inside. The T-Stat column says the same thing in another form: it is the ACF divided by that lag's standard error, so a T-Stat past about 2 corresponds to a needle past the band.
Things to try next¶
- Switch to Manual and set Lag Value to 10. More lags are computed, and the bands keep widening. With twenty rows a lag of 10 uses only ten overlapping pairs, so the estimate at the far end is built on very little.
- Set Lag Value to 20. The run is refused: the analysis needs at least one more row than the number of lags, and the message says so, naming both numbers.
- Compare against Partial Autocorrelation on this same data. The two answer different questions and the difference shows clearly here.
N counts rows, not readings
The standard error and the Ljung-Box Q both use the number of ROWS you selected, blanks included, not the count of numeric values. Blank a row and those two columns change even for lags whose pairs never touched it.