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.

Plot of daily log prices for the S&P 500 ETF between January 2009 and July 2012.

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 a wizard user interface, simplifying the process of constructing 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.
    NumXL toolbar with the correlogram icon selected.
  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.
    Dialog box of the NumXL Correlogram Wizard.
  3. Once finished, the tool prints out the table (along with the formulas) into the target cells and creates a correlogram plot (if selected).
    Autocorrelation (ACF) and partial autocorrelation (PACF) table for S&P 500 log monthly prices.
    Autocorrelation (ACF) and partial autocorrelation (PACF) plot for S&P 500 log 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 ETF monthly log returns.

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 or ACF and PACF plot for S&P 500 monthly log returns, generated by NumXL correlogram wizard.

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.

Invoking the Summary Statistics Wizard in the NumXL Toolbar in Excel.

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

Selection of 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 white-noise test highlighted.

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 squared S&P 500 monthly log returns.

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

ARCH effect test results for S&P 500 monthly log returns.

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 or ACF and PACF plot for squared S&P 500 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.


Support Files

Related Links


Article is closed for comments.

Was this article helpful?
5 out of 7 found this helpful