Erstellen einer Korrelationsmatrix

Das Thema dieser Ausgabe wurde durch eine Unterstützungsanfrage inspiriert: Wie kann ich eine Korrelationsmatrix für meine N Vermögenserträge effizient erstellen?

NumXL verfügt über zahlreiche Funktionen zur Berechnung der Kreuzkorrelation zwischen zwei Zeitreihen, aber wie können wir dies beispielsweise für 20 Variablen ohne Zwischenberechnungen tun? Keine Sorge, Excel hat einige Funktionen: MATCH(.) und INDEX(.), die wir in unseren Formeln verwenden können, um die Erstellung einer Korrelationsmatrix einfach und schnell zu gestalten. Genau das werden wir in dieser Ausgabe behandeln.

Wir möchten also die folgende Tabelle mit den Eingangsdaten umwandeln:

in diese Korrelationsmatrix ein:

Interessiert? Dann nichts wie los!

Vorbereitung der Daten

In diesem Tutorial werden wir die logarithmischen wöchentlichen Renditen von 16 ETFs für den Zeitraum zwischen dem 28. September 2015 und dem 17. Februar 2020 (kurz vor der COVID-19-Epidemie) verwenden. Wir haben die 16 ETFs so ausgewählt, dass sie den US-Aktienmarkt (z. B. S&P 500, Dow Jones Industrial Average, Russell 1000/2000/3000 usw.), den US-Energiemarkt (Erdöl und Erdgas), Metalle (Gold, Silber und industrielle Basismetalle) und schließlich den Ultra-Short-Schuldenmarkt (Cash) abdecken.

Als Nächstes kopierten wir die protokollierten wöchentlichen Erträge in ein separates Arbeitsblatt, jeweils in eine eigene Spalte, richteten aber alle Anlagen an einem gemeinsamen Datumsfeld aus (Spalte A).

Lassen Sie uns nun einen Namen für den Zellbereich der Datentabelle definieren:

• Wählen Sie oder wechseln Sie zur Registerkarte Formeln
• Wählen Sie den Eingabezellenbereich aus und klicken Sie dann auf "Name definieren".

• Das Dialogfeld "Name definieren" wird eingeblendet, wobei einige Felder bereits ausgefüllt sind (z. B. enthält "Verweis auf" den Bereich der Datentabelle und der Bereich ist auf "Arbeitsmappe" eingestellt).
• Ändern Sie den Namen im Dialogfeld "Name definieren" in einen aussagekräftigen Wert, also wählen Sie "RETURNS".

• Klicken Sie auf OK.

Erstellen wir einen weiteren definierten Namen (z. B. SYMBOLS") für die Aktiva-Symbolticker (Zeile 2). Wiederholen Sie dieselben Schritte wie oben, aber für die oberste Zeile mit Tickersymbolen.

Wenn Sie nun den Namensmanager untersuchen, sollten Sie folgendes sehen:

Korrelationsmatrix

Bei einer Korrelationsmatrix entspricht jede Zeile und jede Spalte einem einzelnen Vermögenswert, so dass wir die Tickersymbole als Spalten- und Zeilenköpfe unserer Korrelationsmatrix festlegen.

Hinweis: Sie können die Funktion "Transponieren" unter "Einfügeoptionen" verwenden, um eine kopierte Zeile (z. B. eine Zeile mit Tickersymbolen) in eine Spalte einzufügen.

Zeitreihen-Referenzierung

In der Korrelationstabelle stellt jede Zelle die Kreuzkorrelation zwischen den Renditen der beiden Vermögenswerte dar: Spalte und Zeile. In der nachstehenden Abbildung stellt die graue Zelle beispielsweise die Korrelation zwischen IWB und DIA dar.

Nun müssen wir das Tickersymbol verwenden, um die Zeitreihe in der Datentabelle zu referenzieren:

Schritt 1: Konvertiert das Tickersymbol in einen numerischen Offset. Verwenden Sie die Funktion MATCH(.), um den Index des Tickers im definierten Namen SYMBOLS auszuwerten. Dies gilt sowohl für die Spalten- als auch für die Zeilenüberschrift. Zum Beispiel:

Schritt 2: Verweis auf die entsprechenden Zeitreihen

Verwenden Sie die Funktion INDEX(.), um im definierten Namen RETURNS auf eine Spalte, aber auf alle Zeilen zu verweisen. Wir tun dies sowohl für den Spalten- als auch für den Zeilenkopf, aber unter Verwendung des Offsets.

Schritt 3: Kombinieren Sie die Funktionen MATCH(.) und INDEX(.)

Schritt 4: Wählen Sie Ihre bevorzugte Korrelationsfunktion, übergeben Sie die beiden Zeitreihen, und speichern Sie den zurückgegebenen Wert in der Tabelle.

Schritt 5: Kopieren Sie die obige Formel in andere Zellen (Zeilen und Spalten), um die gesamte Korrelationsmatrix zu berechnen.

Schlussfolgerung

In diesem Tutorial haben wir die eingebauten Excel-Funktionen verwendet: MATCH(.) und INDEX(.), und haben in wenigen einfachen Schritten eine Korrelationsmatrix ohne Zwischenberechnung erstellt. Darüber hinaus haben wir die Funktion "definierte Namen" verwendet, um den Datensatz zu kapseln und nicht nur die Formeln, sondern auch die Pflege der Eingabedaten zu vereinfachen: das Hinzufügen von Beobachtungen und/oder Aktiva.