构建相关矩阵

本期主题的灵感来源于一个支持性问题：如何高效地构建 N 种资产收益的相关矩阵？

NumXL 提供了多种函数用于计算两个时间序列之间的交叉相关性，但如何在不进行任何中间计算的情况下对 20 个变量进行此操作呢？别担心，Excel 提供了几个函数：MATCH(.) 和 INDEX(.)，我们可以在公式中使用这些函数，使构建相关性矩阵变得简单快捷。这就是本期我们将要介绍的内容。

总之，我们希望对以下输入数据表进行转换：

进入这个相关矩阵：

感兴趣吗？ 让我们开始吧

数据准备

在本教程中，我们将使用 16 种 ETF 在 2015 年 9 月 28 日至 2020 年 2 月 17 日（即 COVID-19 流行性市场波动之前）期间的对数周回报率。 我们选择的 16 只 ETF 涵盖美国股票市场（如标普 500 指数、道琼斯工业平均指数、罗素 1000/2000/3000 指数等）、美国能源市场（石油和天然气）、金属（黄金、白银和工业基本金属），以及超短债市场（现金）。

接下来，我们将日志周回报复制到一个单独的工作表中，每个工作表都有单独的一栏，但使用一个共同的日期字段（A 栏）对所有资产进行对齐。

现在，让我们为数据表的单元格区域定义一个名称：

• 选择或切换到公式选项卡
• 选择输入单元格区域，然后点击 "定义名称"。

• 弹出 "定义名称 "对话框，其中有几个字段已预先填入，例如 "引用 "有数据表单元格范围，范围设置为 "工作簿"）。
• 将 "定义名称 "对话框中的名称改为有意义的名称，因此我们选择了 "RETURNS"（回归

• 单击 "确定"。

让我们为资产符号 tickers（第 2 行）创建另一个已定义的名称（例如 "SYMBOLS"）。 重复上述步骤，但最上面一行的股票代码除外。

现在，如果您检查名称管理器，应该会看到以下内容：

相关矩阵

对于相关矩阵来说，每一行或每一列都对应一种资产，因此我们将把股票代码设为相关矩阵的列标题和行标题。

提示: 您可以使用 "粘贴选项 "中的 "转置 "功能，将复制的行（如股票代码行）粘贴到列中。

时间序列参考

在相关表中，每个单元格代表两种资产收益率之间的交叉相关性：列和行。 例如，在下图中，灰色单元格是 IWB 和 DIA 之间的相关性。

现在，我们需要使用股票代码来引用数据表中的时间序列：

步骤 1: 将股票代码转换为数字偏移量。 使用 MATCH(.) 函数评估 SYMBOLS 定义名称中的股票代码索引。 我们对列标题和行标题都要这样做。 例如

步骤 2: 参考相应的时间序列

使用 INDEX(.) 函数在 RETURNS 定义名称中引用一列，但不引用所有行。 我们对列标题和行标题都是这样做的，但使用的是其偏移量。

步骤 3: 合并 MATCH(.) 和 INDEX(.) 函数

步骤 4: 选择你喜欢的相关函数，传递两个时间序列，并将返回值存储在表格中。

步骤 5:: 将上述公式复制到其他单元格（行和列），计算整个相关矩阵。

结论

在本教程中，我们使用了 excel 的内置函数： MATCH(.)和 INDEX(.)，只需几个简单的步骤，就能构建出一个相关矩阵，而无需任何中间计算。 此外，我们还使用了 "定义名称 "功能来封装数据集，不仅简化了公式，还简化了输入数据的维护：添加观测值和/或资产。