Python in Excel: How to build ACF and PACF plots for time series forecasting

One of the biggest advantages of Python in Excel is that it brings more advanced analytical tools directly into the workbook environment where so much business data already lives. Time series analysis is a great example. Instead of stopping at a basic line chart, we can use Python libraries to study patterns over time, check how past values relate to future values, and build forecasting models without leaving Excel.

In this post, we will use a market volatility dataset to explore several common time series tools, including a volatility plot, ACF and PACF plots, and an ARIMA model. These visuals help us understand whether the data has patterns, persistence, or lagged relationships that may be useful for forecasting. You can follow along by downloading the exercise file below:

Preparing the data

Before building any time series model, we first need to isolate the variable we want to study and make sure it is organized by date. Here, we are focusing on the vol column, which represents market volatility over time.

import statsmodels.api as sm
from statsmodels.tsa.arima.model import ARIMA

market_df = xl("MarketVolatility[#All]", headers=True)

market_df["date"] = pd.to_datetime(market_df["date"])
market_df = market_df.set_index("date")

ts_df = market_df[["vol"]].dropna()
ts_df.head()

The output gives us a quick preview of the prepared time series. Each row is now indexed by date, with the corresponding volatility value beside it. This confirms that the data is in the right structure for time series analysis: one observation per date, ordered chronologically. From here, we can start asking more interesting questions, such as whether volatility tends to cluster, whether high-volatility periods are followed by more high volatility, and whether past values can help forecast future values.

Plotting volatility over time

Now that the volatility series is prepared, the first thing to do is simply plot it over time. This gives us a visual sense of how volatility behaves before we try to model or forecast it.

ts_df["vol"].plot(figsize=(10, 5), title="Market Volatility Over Time")
plt.ylabel("Volatility")

In the plot, volatility moves up and down throughout the period rather than following a smooth trend. We can also see that some periods are calmer, while others show sharper spikes. This is important because volatility often clusters: high-volatility periods may be followed by more high volatility, and low-volatility periods may persist for a while too. Seeing this pattern visually helps justify the next step, where we use ACF and PACF plots to check whether past volatility values are related to future ones.

Checking autocorrelation with an ACF plot

Next, we create an ACF plot, or autocorrelation function plot. This helps us see how strongly today’s volatility is related to volatility from previous periods. In other words, we are checking whether the series has memory.

sm.graphics.tsa.plot_acf(ts_df["vol"], lags=40)
plt.title("ACF Plot of Market Volatility")

In this plot, the first several lags are strongly positive, especially at the earliest lags. This suggests that volatility is persistent: when volatility is high on one day, it tends to remain elevated for the next few days as well. The correlations gradually decline as the lag increases, which is common in time series data where recent values matter more than older ones.

The shaded blue region represents the approximate confidence band. Values that extend beyond this band are more likely to be statistically meaningful. Here, several of the early lags clearly exceed the band, which gives us evidence that past volatility values may help explain or forecast future volatility. This supports the idea that an ARIMA-style model may be useful for this series.

Isolating direct lag relationships with a PACF plot

Now we’ll use the PACF plot to take this analysis one step further. While the ACF plot shows how volatility is correlated with its past values overall, the PACF plot helps isolate the direct relationship at each lag after accounting for the lags before it.

sm.graphics.tsa.plot_pacf(ts_df["vol"], lags=40)
plt.title("PACF Plot of Market Volatility")

This is helpful when deciding how much history a forecasting model may need. In this plot, the largest meaningful spike appears at lag 1, while most of the later lags fall within the shaded confidence band. That suggests yesterday’s volatility has a strong direct relationship with today’s volatility, but older values may not add much once the most recent value is accounted for.

In practical terms, this tells us that recent volatility matters, but we may not need a very long lookback window to capture the main pattern. This gives us useful guidance as we move toward building an ARIMA model.

Fitting an ARIMA model

Now that we have looked at the volatility series and its lag relationships, we can fit an ARIMA model. ARIMA is a common time series forecasting model that uses past values and past errors to explain the current value of a series.

model = ARIMA(ts_df["vol"], order=(1, 0, 5))
arima_model = model.fit()


pd.DataFrame({
    "coef": arima_model.params,
    "std_error": arima_model.bse,
    "z": arima_model.tvalues,
    "p_value": arima_model.pvalues
})

In this example, we use an ARIMA (1, 0, 5) model. The 1 means the model includes one autoregressive term, which lines up with what we saw in the PACF plot: the first lag appears especially important. The 0 means we are not differencing the data, so we are modeling the volatility series in its current form. The 5 means the model also includes five moving average terms, allowing it to account for short-term noise or shock patterns in the series.

The output table summarizes the fitted model. The coef column shows the estimated value of each model term. The std_error, z, and p_value columns help evaluate how reliable or statistically meaningful each estimate is. In general, smaller p-values suggest that a term is contributing meaningful information to the model.

At this stage, we are not just creating a forecast yet. We are checking whether the patterns suggested by the plots can be formalized in a model. This is the bridge from visual time series exploration to actual forecasting.

Conclusion and next steps

In this post, we used Python in Excel to move beyond a basic time series chart and into a more structured volatility analysis. We prepared the data, plotted volatility over time, used ACF and PACF plots to study lag relationships, and fit an ARIMA model to describe the series.

The biggest advantage of doing this in Python in Excel is that we can keep working directly from an Excel table while gaining access to more advanced statistical tools. Instead of exporting the data to another environment, we can use Python libraries like statsmodels right inside the workbook. This makes it easier to explore questions like whether past values influence current values, whether the series has persistence, and what kind of model structure may be appropriate.

Of course, this is still only part of the forecasting workflow. The plots and model summary help us understand the data and evaluate possible model terms, but they do not tell the whole story. We would still want to review model diagnostics, compare different model specifications, check forecast accuracy, and think carefully about whether the assumptions of the model make sense for the business problem.

From here, the natural next step is to use the fitted model to generate an actual forecast. If you want to see this analysis picked up from here and extended into forecasting, check out this follow-up post: