相関行列の構築

本号のテーマは、「N個の資産リターンの相関行列を効率的に構築するにはどうすればよいか」というサポートからの問い合わせに端を発している。

NumXLには、2つの時系列間のクロス相関を計算するための多数の関数が用意されています。しかし、例えば20変数に対して中間計算なしでこれを行うにはどうすればよいでしょうか？心配無用です。Excelには、数式内で使用できるMATCH(.)とINDEX(.)という関数が用意されており、これらを活用することで相関行列の構築を簡単かつ迅速に行うことができます。これがまさに、今回の記事で取り上げる内容です。

まとめると、次のような入力データテーブルを変換したい：

この相関行列に：

興味がおありですか？ さっそく始めよう！

データ準備

このチュートリアルでは、2015年9月28日から2020年2月17日（COVID-19が流行する直前の市場ボラティリティ）までの期間の16のETFの対数週次リターンを使用します。 米国株式市場（S&P500、ダウ平均、ラッセル1000/2000/3000など）、米国エネルギー市場（石油、天然ガス）、金属（金、銀、工業用卑金属）、そして最後に超短期債務市場（現金）をカバーする16のETFを選びました。

次に、週ごとのリターンのログを別のワークシートにコピーし、それぞれ別の列に並べたが、共通の日付フィールド（A列）を使ってすべての資産を整列させた。

では、データ・テーブルのセル範囲の名前を定義してみよう：

• 数式タブを選択または切り替える
• 入力セル範囲を選択し、"Define Name "をクリックする。

• Define Name "ダイアログがポップアップし、いくつかのフィールドがあらかじめ入力されている。例えば、"Refer to "にはデータテーブルのセル範囲があり、スコープは "Workbook "に設定されている）。
• Define Name "ダイアログの "Name "を意味のあるものに変更し、"RETURNS "とする。

• OKをクリックする。

アセット・シンボル・ティッカー（2行目）用に、別の定義名（例えば「SYMBOLS」）を作ってみよう。 上記と同じ手順を繰り返しますが、一番上の行はティッカーシンボルの行です。

さて、ネーム・マネージャーを調べると、以下のようになっているはずだ：

相関マトリックス

相関行列の場合、各行または各列は1つのアセットに対応するので、ティッカーシンボルを相関行列の列と行のヘッダーに設定します。

ヒント: 貼り付けオプション」の「トランスポーズ」機能を使えば、コピーした行（ティッカーシンボルの行など）を列に貼り付けることができる。

時系列参照

相関表では、各セルが列と行という2つの資産のリターン間の相互相関を表している。 例えば、下図のグレーのセルはIWBとDIAの相関を表している。

ここで、ティッカー・シンボルを使ってデータ・テーブルの時系列を参照する必要がある：

ステップ 1: ティッカーシンボルを数値オフセットに変換する。 MATCH(.)関数を使用して、SYMBOLS定義名内のティッカーのインデックスを評価する。 列ヘッダーと行ヘッダーの両方に対してそれを行う。 例

ステップ 2: 対応する時系列を参照

INDEX(.)関数を使用して、RETURNS定義名で、1つの列、しかし全ての行を参照する。 これは、列と行のヘッダーの両方に対して行いますが、オフセットを使用します。

ステップ 3: MATCH(.)関数とINDEX(.)関数の組み合わせ

ステップ 4: 好きな相関関数を選び、2つの時系列を渡し、返された値を表に格納する。

ステップ 5: 上の式を他のセル（行と列）にコピーして、相関行列全体を計算する。

結論

このチュートリアルでは、エクセルの組み込み関数を使用しました： MATCH(.)とINDEX(.)を使い、いくつかの簡単なステップで、中間計算なしで相関行列を構築しました。 さらに、データ集合をカプセル化するために "定義された名前 "機能を使用し、計算式だけでなく、オブザベーションや資産の追加といった入力データのメンテナンスも単純化しました。