Module 4 - Correlogram Analysis

In module one (1), we demonstrated the data preparation phase of time series analysis. In module two, we described a few steps to calculate numerous summary statistics and verify the significance of their values.

In this module, we present a few steps to conduct a correlogram analysis in Excel using NumXL functions and tools.

For sample data, we’ll use the S&P 500 closing logIn module 1, we showed that the logarithmic transformation of the prices provides better values distribution prices between January 2009 and July 2012.

SPDR log-prices

Many time series data sets exhibit time interdependency among their values. This is important to detect and will eventually factor in to improve the forecast quality of the model.

NumXL supports numerous functions and wizard user-interface, simplifying the process of constructing an ACF and partial ACF (aka PACF) plots.

Using the NumXL Correlogram toolbar, you can generate the ACF/PACF values and their plots in a few steps.

  1. Using the NumXL toolbar (or menu in Excel 97-2003), select Correlogram.
    Correlogram icon in NumXL Tab
  2. The Correlogram dialog box pops up. Fill in the location of your data, series time order, output options and location for the table and graphs to be generated in your worksheet.
    NumXL Correlogram Wizard or dialog box
  3. Once finished, the tool prints out the table (along with the formulas) into the target cells and creates a correlogram plot (if selected).
    Correlogram output table for &P 500 log monthly prices Correlogram plot for S&P500 monthly prices

The shaded area in the ACF and PACF plots represents the confidence intervals for the ACF and PACF values.

Note that PACF is significant (~100%) at lag order 1, and the ACF is declining very slowly. This is a common pattern indicating the presence of unit-rootIn module three, we tested the time series for the presence of unit-root..

Next, let’s take the first difference of the time series:

Plot for S&P 500 monthly log return

Next, let’s run the correlogram analysis on the differenced (i.e. log returns) time series.

Correlogram table for S&P 500 monthly log returns
Correlogram plot for S&P 500 monthly log returns

The log returns do not exhibit strong interdependency, though lag order 8 and 9 show marginal significance. This beg the following question:

Q1: Does the log-returns time series exhibit white-noise (no serial correlation)?

To answer this question, we’ll use the descriptive statistics wizard and check the white-noise test option.

Selecting descriptive statistics icon in the NumXL 2007/2010 toolbar

Now, check the white-noise (Ljung-Box) test field:

Select white-noise test in NumXL Descriptive Statistics wizard.

The summary statistics table with the white-noise test appears as follows:< p/>

Summary Statistics output table for S&P 500 monthly log returns with highlighted white-noise test.

The answer for our question is Yes, the time series does not exhibit significant serial correlation

What's Next

The weekly log-returns time series distribution possesses a fat-tails (i.e. excess-kurtosis > 0), which may happen if the squared returns are correlated (aka ARCH effect).

Q: Does the log-returns time series exhibit an ARCH effect? Are the squared weekly log-returns correlated or more like a white-noise distribution?

Plot for the S&P 500 squared monthly log returns.

Again, launch the descriptive statistics wizard from the NumXL toolbar (or menu in Excel 2003), and select the ARCH effect.

The S&P500 monthly log returns summary statistics table showing ARCH effect test

Examining the the ARCH effect test results, we conclude that the squared returns are serially correlated; i.e. we have a conditional heteroskedacity in the log returns.

Let’s examine the correlogram of the squared log-returns:

Correlogram plot for S&P 500 squared log monthly returns

The PACF shows a significant autocorrelation up to the 3rd lag order.


The correlogram analysis is a key tool to explore the inter dependency of the observation values; it can also be used as a tool to identify the model and the estimate the orders of its components.

In our example, we found that the weekly log returns are not correlated, but their squared values are. As a result, an ARCH/GARCH model may be in order here.

Have more questions? Submit a request