Forecast Models with Auto-Update Features

The topic of this newsletter is about setting up a forecasting model (e.g., ARMA, GARCH, Regression) in such a way that you (or someone else) can readily maintain it as new data points become available and forecasts are auto-updated.

To demonstrate, we defined a GARCH model for the S&P 500 ETF monthly returns, calibrated the parameters' values, and constructed a volatility forecast term structure for the following 36 months. We then plugged in a new data point and observed the model advancing the forecast start date and updating forecast values.

Why should you care?

In practice, we create many excel-based models, some of them for single-use, but many spreadsheets are often shared with colleagues, revised occasionally, and linger around for a long time. Preparing your worksheet for an auto-update is relatively simple. Once done, you can easily add new data points or revise existing ones and see your calculations updated with minimal effort.

Ready, let's dig in!

Data Preparation

For this tutorial, we are using the monthly log-returns of the S&P 500 ETF (SPY) between January 2010 and November 2019:

This figure shows the S&P 500 monthly log-returns starting in January 2010 to Nov. 2019. The dataset has a date component and value.

First, we should append the current dataset with placeholders. We do so by advancing dates (e.g., Dec. 2019, Jan. 2019) and entering "#N/A" as their values.

In this figure, we demonstrate the process of adding placeholder datapoints at the end of the time series dataset. each new datapoint has a valid date and a value of #N/A.


We chose a GARCH(2,2) with the student's innovations to capture the time-varying monthly volatility dynamic for this data set. To define the model, locate an empty cell in your worksheet, and then click on the "GARCH" icon in the "NumXL" toolbar, and the GARCH wizard pops up.

In this figure, we show the GARCH wizard displayed. The Input dataset references the cell-range of input time series including the placeholders data points inserted at the end of the dataset.

In the ‘Input Data’ section, select the monthly returns' cell range, including the placeholders you added earlier. Not to worry, the NumXL functions will discard observations with "#N/A" values at either end of the input cell range.

Select the ARCH and GARCH in the Model Specifications section, and set the order to 2. Click on "Student t-distribution" under the innovations section. Now, click on the "OK" button to confirm your selection.

This figure shows the GARCH(2,2) model table, as generated by the NumXL GARCH wizard.

The GARCH wizard generates the model table and initializes its parameters' values to valid but sub-optimal values. To find an optimal set of values for the GARCH parameters, select the model table's header cell and click on the "Calibrate" icon in the "NumXL" toolbar. Microsoft Solver pops up with all its values set. Click Solve

This figure shows Microsoft solver with values initialized for optimizing the goodness of fit of the GARCH model with the data set.

Microsoft solver searches for an optimal solution for the GARCH process.

This figure shows the Solver results windows after the Solver finds an optimal solution.

Data Exploration

Earlier, we mentioned that the NumXL functions discard any observation in the input cell range with a value equal to "#N/A". How do you know where the dataset ends?

To answer the question above, you need to first calculate the dataset's size with two functions: RMNA(.) and COUNT(.). The RMNA(.) discards datapoints with "#N/A" and returns an array of all non-missing observations, and the COUNT(.) returns the size of this array.

This figure shows the formula for calculating the effective size of the input dataset using COUNT and RMNA functions.

You can also calculate the end date of the dataset using the Excel built-in INDEX(.) function and the dataset size above.

This figure shows the formula for calculating the date of the last observation with non-missing value in our dataset using INDEX function and the effective size of the input dataset.

In sum, you have a dataset of 119 non-missing values, and the latest observation fell in November 2019.


for this tutorial, you need to construct the monthly volatility forecast for the following 36 months. To do so, select the model table's header row and click on the "Forecast" icon in the NumXL toolbar.

This figure shows the Forecast wizard for GARCH model . Note the latest obs is referencing the input dataset including the placeholders datapoints.

In the "Latest obs." section, select the cell range in column "B," including the observation with missing values (i.e., #N/A).

For the volatility, leave it blank, and NumXL will use the in-sample volatility computed by the GARCH model itself. Click the "OK" button.

The forecast wizard generates a forecast table that expresses the time unit in terms of steps (i.e., month) measured after the input dataset's end.

This figure shows the forecasting table generated by NumXL wizard for the following 36 months. Note that it uses the step (e.g., month) as time unit.

Over time, adding new data points advances the last observation's (with non-missing value) date, impacting our interpretation of the step-unit in the forecasting table. To counter this problem, we added a column on the left of the forecast table to designate the corresponding calendar date.

To calculate the corresponding date for each step, we will NxEDATE(.) and CONCAT(.) functions, as shown below:

This figure shows the formula for calculating the calendar date for each period in the forecasting table using NxEDATE function.

Using the dataset end-date, the NxEDATE(.) returns the data that falls after N-months, where N is the number of steps in the forecast table.

What happens if the new data falls on a weekend or a holiday? Since our dataset is a financial time series, the date must fall on a workday; otherwise, move the date to the next nearest working date (NxAdjust(.)).

This figure shows the formula for adjusting the calculated date to the next nearest workday using NxAdjust(.) function.

That's it! Let's examine the forecast table and the monthly volatility term structure curve.

This figure shows the forecasting table with a date calendar column and a volatility term structure forecast for the following 36 months.

The GARCH model reveals a low volatility environment – relative to the historical level (i.e., 3.8%) but forecasts a steady rise in volatility over the coming year.

Adding datapoints

Let's examine what happens when we have the monthly return of December 2019, say -0.6%.

Step 1: Enter the new value in the dataset.

This figure shows the plug in process of new data point and replacing a given placeholder.

Step 2: Observe

This figure shows the updated calculation for the dataset size and end-date, and the forecasting table and volatility term structure curve.

The input dataset size increased to 120, and the end date is now Dec 2019.

In the forecast table, the corresponding date for step one is now January 2020 (was Dec 2019), and the volatility term structure is different (less smooth).

Step 3: Advanced

The forecast table above uses the same GARCH process that we calibrated earlier, especially the parameters' value.

For the sake of argument, let's assume you wish to re-calibrate the model but keep the ARCH/GARCH orders (i.e., P and Q) and the innovation distribution (e.g., student t) the same.

Like we did earlier, locate and select the GARCH model table's header row and click on the "Calibrate" icon in the NumXL toolbar.

The Solver pops up, but this time, the optimization will start (i.e., initial value) with the parameters' optimal values from the last run. Thus, we expect a rapid conversion.

This figure demonstrates the process of recalibrating the GARCH model using dataset with new data-point.

Click on the "Solver" button to commence the optimization process.

Upon completion, the Solver displays a "Find Solution" dialog. Click the OK button, and it will copy the values of the new parameters into the model table.

To examine the re-calibration impact on your forecast, look down at your forecast values in the table (and plot).


In this tutorial, we examined the two steps to set up your models for auto-update:

  • Adding placeholder datapoints at the end of the input dataset,
  • Select the dataset with the placeholder datapoints into the model and forecast.

As time progress and new observation realizes, we enter their actual values into the dataset (in place of the #N/A), and Excel updates calculation of all referencing cells (e.g., forecast table).



Please sign in to leave a comment.

Was this article helpful?
1 out of 1 found this helpful