In a previous post, we looked at how Copilot in Excel can help us build an ARIMA forecast with Python. That approach has some real advantages: Copilot can help generate code, explain unfamiliar syntax, and speed up the initial modeling process.
There are also advantages to building the model ourselves. Writing the Python code directly gives us more control over how the data is prepared, how the model is specified, and how the results are presented. It also makes it easier to understand what each step is doing rather than treating the forecast as a black box.
Excel already includes Holt-Winters-style forecasting, which can work well for data with a clear trend or repeating seasonal pattern. This dataset does not show that kind of regular structure. Instead, volatility moves unevenly, with short-term spikes, declines, and persistence from one period to the next. ARIMA gives us a better way to model those relationships using previous observations and recent forecast errors.
This is where Python in Excel adds real value. It gives us access to the statsmodels library, lets us specify the model directly, inspect fitted values and uncertainty, and customize the output while keeping the entire analysis inside the workbook.
In this example, we will use a time series of daily market-volatility values. Each row contains a date and its corresponding volatility observation. Our goal is to use the historical pattern in that series to estimate volatility for the next five business days.
Download the exercise file below to follow along:
Preparing the data
Before fitting the ARIMA model, we first import the dataset and the ARIMA class from statsmodels:
from statsmodels.tsa.arima.model import ARIMA
market_df = xl("MarketVolatility[#All]", headers=True)Next, we prepare the imported data for time-series modeling:
market_df["date"] = pd.to_datetime(market_df["date"])
market_df = market_df.set_index("date")
ts_df = market_df[["vol"]].dropna()The first line converts the date column into pandas datetime values. Even when dates look correct in Excel, Python may initially interpret them as text or another general data type. Using pd.to_datetime() ensures pandas recognizes them as actual dates that can be sorted, filtered, plotted, and used in time-series analysis.
We then use set_index("date") to make the dates the DataFrame’s row index. This gives the data a clear time-based structure, with each volatility value associated with a specific date.
Finally, we create ts_df, a smaller DataFrame containing only the vol column. The double brackets preserve it as a one-column DataFrame, while dropna() removes rows with missing volatility values. At the end of this step, we have a clean, date-indexed series ready to pass into the ARIMA model.
Fitting the model
Now we can create and fit the ARIMA model:
arima_model = ARIMA(
ts_df["vol"],
order=(1, 0, 5)
).fit()The first argument, ts_df["vol"], identifies the values we want to model. Here, we use single brackets because ARIMA expects a one-dimensional pandas Series rather than the full DataFrame.
The order argument controls the structure of the model. ARIMA orders are written as (p, d, q), where each number represents a different part of the model.
For order=(1, 0, 5), the 1 means the model uses one previous volatility value to help estimate the current value. This is the autoregressive, or AR, part of the model.
The 0 means we are not differencing the data. Differencing means modeling the change between consecutive observations rather than modeling the original values directly. Here, we are assuming the volatility series is stable enough to use as-is.
The 5 means the model considers forecast errors from the previous five observations. This is the moving-average, or MA, part of the model. In ARIMA, “moving average” does not mean calculating an average of the last five values. Instead, it means using recent prediction errors to improve future predictions.
Finally, .fit() estimates the model’s coefficients using the historical data. Without .fit(), we have only described the kind of ARIMA model we want. Calling .fit() actually trains the model and returns a fitted results object, which we store as arima_model.
In plain English, this ARIMA(1, 0, 5) model attempts to explain the current volatility value using the previous volatility value and the model’s prediction errors from the previous five periods.
Creating the forecast
Next, we use the fitted model to forecast the next five periods:
forecast = arima_model.get_forecast(steps=5)The resulting forecast object includes both the predicted values and measures of uncertainty, such as confidence intervals, which we can extract and return to Excel.
The forecast object contains the predicted values along with information about their uncertainty, so we extract those pieces and combine them into one DataFrame:
forecast_mean = forecast.predicted_mean
forecast_se = forecast.se_mean
forecast_ci = forecast.conf_int()forecast_df = pd.DataFrame({
"forecast": forecast_mean,
"standard_error": forecast_se,
"lower_bound": forecast_ci.iloc[:, 0],
"upper_bound": forecast_ci.iloc[:, 1]
})
forecast_df.head()The forecast column contains the model’s best estimate for each of the next five periods. In this output, volatility is expected to remain fairly steady at approximately 0.192 to 0.193.
The standard_error measures the uncertainty around each prediction. It rises from 0.019 for the first forecast to 0.027 for the fifth, showing that the model becomes less certain as it predicts farther into the future.
The lower and upper bounds give a likely range for each forecast. For example, the first predicted value is 0.192, with an interval from 0.156 to 0.229. By the fifth period, the forecast remains about 0.192, but the interval widens to 0.139 through 0.245.
Overall, the model predicts relatively stable volatility, while also showing that the range of possible outcomes grows wider over time.
The forecast values do not always come with the future dates we want, so this block creates five business-day dates and assigns them to the forecast:
last_day = ts_df.index.max()
future_dates = pd.bdate_range(
start=last_day + pd.offsets.BDay(1),
periods=5
)
forecast_df.index = future_dates
forecast_dfFirst, ts_df.index.max() finds the final date in the historical dataset. We then use pd.bdate_range() to generate the next five business days, beginning one business day after that final date. This skips Saturdays and Sundays, providing a simple business-day calendar. It does not account for exchange holidays.
Assigning future_dates to forecast_df.index does not change the forecast itself. It simply labels each predicted value with the date it represents, making the results easier to interpret, chart, and return to Excel.
Examining the model’s fitted values
Next, we collect the model’s fitted values:
fitted_df = pd.DataFrame({
"fitted": arima_model.fittedvalues
})
fitted_df.head()The fitted values are the ARIMA model’s estimated volatility values for dates already included in the historical dataset. In other words, these are not future forecasts. They show how the fitted model represents the existing data.
For example, the model estimates volatility of approximately 0.195 for January 3, 2017, 0.181 for January 4, and 0.178 for January 5.
These values become most useful when compared with the actual vol values for the same dates. If the fitted and actual values are generally close, the model is following the historical pattern reasonably well. The difference between an actual value and its fitted value is called a residual, or prediction error.
The first few fitted values should also be interpreted cautiously because ARIMA relies on previous observations and previous errors. At the beginning of the series, less historical information is available to the model.
Visualizing the volatility series and ARIMA results
Before comparing the model’s fitted values and forecasts with the original data, it helps to plot the historical volatility series on its own:
ts_df.loc["2018":, "vol"].plot(figsize=(11, 6), label="Actual volatility")
plt.title("Market Volatility")
plt.ylabel("Volatility")
plt.legend()
The dataset begins in 2017, but we plot the series beginning in 2018 to keep the chart focused and easier to read.
This chart gives us a visual baseline for understanding the series before adding the fitted values or future forecasts. It shows that volatility changes considerably throughout the year rather than following a smooth upward or downward trend.
Volatility is relatively elevated in parts of January and February, falls sharply around March, and reaches another noticeable low around the beginning of July. It then rises quickly into August before continuing to fluctuate through the remainder of the year.
The series appears to move around a fairly stable overall level, with repeated short-term spikes and drops. That pattern helps explain why an ARIMA model may be appropriate: the model can use recent observations and recent prediction errors to capture some of this short-term dependence.
At the same time, the chart also shows that volatility is noisy and difficult to predict precisely. We should therefore expect the forecast confidence intervals to matter just as much as the point forecasts themselves.
Now that we have both the historical observations and the model’s fitted values, we can plot them together:
plt.figure(figsize=(11, 6))
ts_df.loc["2018":, "vol"].plot(label="Actual volatility")
fitted_df.loc["2018":, "fitted"].plot(label="Fitted values")
plt.title("Actual vs Fitted Market Volatility")
plt.ylabel("Volatility")
plt.legend()
The blue line shows the actual volatility values, while the orange line shows the values estimated by the fitted ARIMA model for the same dates. Plotting them together helps us evaluate how well the model captures the historical pattern.
In this chart, the fitted values generally follow the direction and overall movement of the actual series. The model captures many of the rises and falls, including the larger movements around February, March, July, and August.
However, the fitted line is noticeably smoother. It often misses the full size of sudden spikes and drops because the model is estimating volatility from recent observations and past errors rather than reproducing every movement exactly.
Overall, the close overlap suggests that the model is capturing much of the short-term structure in the series. The gaps between the two lines represent the model’s residuals, or errors. Those residuals should still be examined to determine whether any meaningful patterns remain unexplained.
Finally, we extend the historical chart by adding the model’s five-period forecast:
plt.figure(figsize=(11, 6))
ts_df.loc["2018":, "vol"].plot(label="Actual volatility")
fitted_df.loc["2018":, "fitted"].plot(label="Fitted values")
forecast_df["forecast"].plot(label="Forecast")
plt.title("ARIMA Forecast of Market Volatility")
plt.ylabel("Volatility")
plt.legend()
The blue line shows the actual volatility values, the orange line shows the model’s fitted values for the historical period, and the green line shows the forecast beyond the end of the dataset.
In this example, the forecast remains close to 0.19 across the next five business days. Rather than predicting a sharp increase or decrease, the model expects volatility to settle near its recent level.
The second chart adds the forecast confidence interval:
plt.figure(figsize=(11, 6))
ts_df.loc["2018":, "vol"].plot(label="Actual volatility")
fitted_df.loc["2018":, "fitted"].plot(label="Fitted values")
forecast_df["forecast"].plot(label="Forecast")
plt.fill_between(
forecast_df.index,
forecast_df["lower_bound"],
forecast_df["upper_bound"],
alpha=0.3
)
plt.title("ARIMA Forecast with Confidence Interval")
plt.ylabel("Volatility")
plt.legend()
plt.fill_between() shades the area between the lower and upper forecast bounds. The green line is still the model’s best estimate, while the shaded region shows a wider range of plausible outcomes.
The forecast itself stays nearly flat, but the shaded interval widens as we move farther into the future. This shows that the model’s uncertainty increases with each additional forecast period. The main takeaway is therefore not that volatility will be exactly 0.19, but that the model expects it to remain around that level while acknowledging a fairly broad range of possible outcomes.
Conclusion
In this post, we used Python in Excel to prepare a market-volatility time series, fit an ARIMA model, and forecast the next five business days. The model expects volatility to remain near 0.19, but the widening prediction interval reminds us that uncertainty increases as we forecast farther into the future.
This ARIMA(1,0,5) model is meant as a practical example, not necessarily the best possible specification. A fuller analysis would compare different combinations of p, d, and q, review AIC or BIC, test for stationarity, inspect the residuals, and evaluate forecasts against held-out data.
There are also practical limitations. The generated dates exclude weekends but not market holidays, and no time-series model can anticipate unexpected market shocks. Because financial volatility often occurs in clusters, ARCH or GARCH models could also be useful next steps.
Even so, this example shows the added value of Python in Excel: we can move beyond a one-click forecast, inspect the model’s assumptions and uncertainty, and adapt the workflow to the characteristics of the data.