具有自动更新功能的预测模型

本期通讯的主题是如何建立预测模型（如 ARMA、GARCH、回归模型），以便您（或其他人）可以在获得新数据点和预测自动更新时随时对其进行维护。

为了证明这一点，我们定义了标准普尔 500 ETF 月收益的 GARCH 模型，校准了参数值，并构建了随后 36 个月的波动率预测期限结构。 然后，我们插入一个新的数据点，观察模型是否将预测起始日期提前并更新了预测值。

你为什么要关心？

在实际工作中，我们会创建许多基于 excel 的模型，其中一些是一次性使用的，但许多电子表格通常会与同事共享，偶尔进行修改，并长期存在。 为自动更新准备工作表相对简单。 一旦完成，您就可以轻松添加新数据点或修改现有数据点，并以最小的代价看到计算结果的更新。

准备好了，我们开始吧！

数据准备

在本教程中，我们使用的是标普 500 ETF (SPY) 在 2010 年 1 月至 2019 年 11 月期间的月度对数收益率：

首先，我们应该为当前数据集添加占位符。 为此，我们将日期提前（如 2019 年 12 月、2019 年 1 月），并输入 "#N/A "作为其值。

建模

我们选择带有学生创新的 GARCH（2，2）来捕捉该数据集的时变月度波动动态。 要定义模型，请在工作表中找到一个空单元格，然后点击 "NumXL "工具栏中的 "GARCH" 图标，GARCH 向导就会弹出。

在 "输入数据 "部分，选择 "月度回报 "单元格区域，包括之前添加的占位符。 不用担心，NumXL 函数会放弃输入单元格范围两端带有 "#N/A "值的观测值。

在 "模型规格 "部分选择 ARCH 和 GARCH，并将阶数设为 2。 点击 "创新 "部分下的 "学生 t 分布"。 现在，点击 "OK（确定）"按钮确认您的选择。

GARCH 向导会生成模型表，并将其参数值初始化为有效但次优的值。 要找到 GARCH 参数的最佳值集，请选择模型表的标题单元格并点击 "NumXL "工具栏上的 "Calibrate（校准）"图标。 Microsoft 求解器会弹出并设置好所有值。 点击求解

微软求解器为 GARCH 过程寻找最优解。

Data Exploration

前面我们提到，NumXL 函数会丢弃输入单元格区域中任何值等于 "#N/A "的观测值。 如何知道数据集在哪里结束？

要回答上述问题，首先需要使用两个函数计算数据集的大小： RMNA(.) 和 COUNT(.)。 RMNA(.) 会剔除带有 "#N/A "的数据点，并返回一个包含所有非遗漏观测值的数组，而 COUNT(.) 会返回该数组的大小。

您还可以使用 Excel 内置的 INDEX(.) 函数和上述数据集大小计算数据集的结束日期。

总之，您有一个包含 119 个非遗漏值的数据集，最近一次观测是在 2019 年 11 月。

预测

在本教程中，您需要构建未来 36 个月的月波动率预测。 为此，请选择模型表的标题行，然后点击 NumXL 工具栏上的 "预测 "图标。

在 "最新观测值 "部分，选择 "B "列的单元格范围，包括有缺失值的观测值（即 #N/A）。

关于波动率，请留空，NumXL 将使用 GARCH 模型本身计算的样本内波动率。 点击 "确定 "按钮。

预测向导生成的预测表以输入数据集结束后的步数（即月）为时间单位。

随着时间的推移，添加新的数据点会使最后一个观测值（无缺失值）的日期提前，从而影响我们对预测表中步长单位的解释。 为了解决这个问题，我们在预测表的左侧增加了一列来指定相应的日历日期。

如下所示，我们将使用 NxEDATE(.) 和 CONCAT(.) 函数计算每个步骤的相应日期：

使用数据集结束日期，NxEDATE(.) 返回 N 个月后的数据，其中 N 是预测表的步数。

如果新数据发布当天是周末或节假日，该怎么办？ 由于我们的数据集是金融时间序列，因此日期必须在工作日；否则，请将日期移动到下一个最近的工作日（NxAdjust(.)）。

就是这样！ 让我们来看看预测表和月度波动率期限结构曲线。

GARCH 模型显示，相对于历史水平（即 3.8%），波动环境较低，但预测未来一年波动率将稳步上升。

添加数据点

我们来看看 2019 年 12 月的月回报率，比如说-0.6%，会发生什么情况。

步骤 1: 在数据集中输入新值。

步骤 2: 观察

输入数据集增加到 120 个，结束日期为 2019 年 12 月。

在预测表中，第一步的对应日期现在是 2020 年 1 月（原为 2019 年 12 月），波动率的期限结构也有所不同（不那么平滑）。

步骤 3: 高级

上面的预测表使用的是我们之前校准过的 GARCH 过程，尤其是参数值。

为了便于论证，假设您希望重新校准模型，但 ARCH/GARCH 排序（即 P 和 Q）和创新分布（如学生 t）保持不变。

像之前一样，找到并选择 GARCH 模型表的标题行，然后点击 NumXL 工具栏上的 "校准 "图标。

解算器弹出，但这次优化将从上次运行的参数最佳值开始（即初始值）。 因此，我们期待快速转换。

点击 "求解器 "按钮开始优化过程。

完成后，求解器将显示 "查找解决方案 "对话框。 单击 "确定 "按钮，它将把新参数值复制到模型表中。

要检查重新校准对预测的影响，请查看表格（和图表）中的预测值。

结论

在本教程中，我们研究了设置模型进行自动更新的两个步骤：

• 在输入数据集末尾添加占位符数据点、
• 在模型和预测中选择带有占位数据点的数据集。

随着时间的推移和新观测数据的实现，我们将其实际值输入数据集（代替 #N/A），Excel 会更新所有引用单元格（如预测表）的计算。