Backtesting in Excel

Backtesting in der Modellierung bezieht sich auf das Testen eines Vorhersagemodells anhand historischer Daten. In diesem Artikel geht es um die Vorgehensweise in Microsoft Excel, nicht um den theoretischen Hintergrund des Backtestings.

Wie führen wir Backtesting durch? Wir spulen die Zeit bis zum Beginn unserer Zeitreihe zurück, kalibrieren die Parameter des betreffenden Modells anhand der bis zu diesem Zeitpunkt verfügbaren Daten und führen eine Vorhersage (d. h. eine Prognose) für den nächsten Zeitraum durch. Als Nächstes verschieben wir den Zeitpunkt, kalibrieren die Werte der Parameter neu und führen eine weitere Projektion durch, usw. Am Ende unserer Übung hätten wir eine Reihe von Vorhersagen.

Beachten Sie, dass die einzige Annahme, die wir zu jedem Zeitpunkt treffen, die allgemeine Modelldefinition ist (z. B. ARMA(1,1)). Dennoch würden wir die Werte der Parameter nur anhand der bis zu diesem Zeitpunkt verfügbaren Informationen kalibrieren.

Dieser Ansatz entspricht der Praxis: Wir beginnen mit einem Ausgangsmodell und erstellen eine Prognose für den folgenden Zeitraum. Die Zeit schreitet fort. Ein neuer Zeitraum ist eingetreten, also fügen wir den neuen Datenpunkt an den aktuellen Eingabedatensatz an, kalibrieren die Parameter neu, führen eine Prognose für den folgenden Zeitraum durch und wiederholen den Vorgang.

Warum sollte mich das interessieren?

Dieser Artikel führt Sie durch die Schritte in Microsoft Excel, die zur Durchführung von Backtests erforderlich sind. Wir werden hauptsächlich zwei leistungsstarke, in Excel integrierte Funktionen verwenden: INDEX(.) und SEQUENCE(.), und nutzen den Excel-Mechanismus "Datentabelle", um die verschiedenen Szenarien auszuführen.

Das Backtesting generiert Vorhersagefehler, so dass Sie die Zeitreihe der Vorhersagefehler auf serielle Korrelation, Verteilungen, Ausreißer usw. genau untersuchen können, um die Genauigkeit und Leistung des Modells besser zu verstehen.

Lasst uns loslegen!

Für diese Ausgabe verwenden wir einen synthetischen stationären Datensatz mit 200 Beobachtungen. Der Datensatz folgt einem ARMA(1,1)-Prozess, wie nachstehend dargestellt.

Das vorgeschlagene Modell ist ARMA(1,1)

Backtesting-Verfahren

Für jede Iteration müssen wir Folgendes tun: (1) Definition des Eingangsdatensatzes (als Teilmenge der ursprünglichen Zeitreihe), (2) Kalibrierung der Parameterwerte des ARMA(1,1)-Modells unter Verwendung des Datensatzes in (1), (3) Berechnung einer Prognose für eine Periode im Voraus unter Verwendung des Modells in (2) und des Datensatzes in (1).

1. Eingabedatensatz

Um den Eingabedatensatz vollständig zu beschreiben, benötigen wir zwei Indizes: Start und Ende. Dann erzeugen wir mit der Funktion SEQUENCE(.) einen Satz von Indizes zwischen Start und Ende. Nun verwenden wir die Funktion INDEX(.), um alle Zellbereiche im ursprünglichen Datensatz mit Zeilenindizes im Sequenzsatz zurückzugeben.

Beispiel: 

Der ursprüngliche Eingabedatensatz lautet \$A\$3:\$A\$202. So wählen Sie die Zellen zwischen den Indizes 1 und 50 aus.

Beachten Sie, dass Sie einen Namen für Ihre Eingabedaten definieren und diesen Namen anstelle des Eingabezellenbereichs verwenden können.

2. Kalibrieren Sie das Modell

Wir verwenden die NumXL-Funktion ARMA_PARAM(.) und geben den Rückgabetyp=2 für kalibrierte Parameter an.

Beachten Sie, dass ARMA_PARAM(.) eine kompakte Form der Parameter des Modells zurückgibt, so dass in der obigen Abbildung der ARMA-Prozess dargestellt ist: \[\begin{array}{l} {X_t} = 1.485 + 0.401{X_{t - 1}} + 0.734{a_{t - 1}} + {a_t}\\ {a_t} \sim N(0,1.14) \end{array}\]

3. Vorhersage

Unter Verwendung des ARMA_FORE(.), des Datensatzes in (1) und der in (2) berechneten Modellparameter können wir die Prognosewerte für die 1-Periode voraus berechnen.

Beachten Sie, dass ich die mittlere Prognose, den Prognosefehler und das Konfidenzintervall erstellt habe.

Daten-Tabelle

Jetzt haben wir gerade die Berechnung eines Schrittes abgeschlossen. Wir werden die Funktion "Datentabelle" von Excel verwenden, um die gleichen Berechnungen für die verbleibenden Perioden bis zum Schritt 200 durchzuführen.

Zunächst müssen wir die Ausgabetabelle vorbereiten:

Wählen Sie nun die gesamte Datentabelle aus, beginnend mit der Ausgabezeile und einschließlich der Zielindexspalte, wie unten dargestellt:

Wechseln Sie dann zur Symbolleiste "Daten" und suchen Sie unter "Was-wäre-wenn-Szenario" den Punkt "Datentabelle".

Das Dialogfeld "Datentabelle" wird eingeblendet. Suchen Sie die "Spalteneingabezelle", geben Sie einen Verweis auf den "Endindex" des Datensatzes ein und klicken Sie auf die Schaltfläche OK.

Die Datentabelle ersetzt die Werte des Zielindexes durch die Werte in unserer Datentabelle und speichert die Ergebnisse.

Beachten Sie, dass "Std. Error" von der Funktion ARMA_FORE(.) generiert wird, während die Spalte ganz rechts ("Error") den Fehler zwischen der Prognose und dem tatsächlich realisierten Wert darstellt.

Backtesting-Analyse

Zunächst werden wir die Backtesting-Prognoseergebnisse visuell mit den tatsächlich realisierten Werten vergleichen und dann die statistischen Eigenschaften näher untersuchen.

Der schattierte Bereich in der Grafik entspricht dem 95 %igen Konfidenzintervall der Prognose.

Das Diagramm zeigt eine gute Modellanpassung und damit eine gute Prognosegenauigkeit.

Als Nächstes wollen wir die statistischen Eigenschaften des Prognosefehlers (Prognose - Ist) mit Hilfe der zusammenfassenden Statistiken in der NumXL-Symbolleiste untersuchen.

Die Tabelle mit den zusammenfassenden Statistiken zeigt, dass der Prognosefehler ein Gaußsches Rauschen mit einem Mittelwert von Null und einer Standardabweichung von 1,0 ist.

Zusammenfassend lässt sich sagen, dass das ARMA(1,1)-Modell ein geeignetes Vorhersagemodell für den gegebenen Datensatz ist.

Was kommt als nächstes?

Wahrscheinlich fragen Sie sich jetzt, welche Werte die Parameter des Modells haben? Sind sie stabil?

Zunächst erstellen wir eine zweite "Datentabelle", aber in der Ausgabezeile stehen die Werte der Modellparameter, und führen die Datentabelle genau wie zuvor aus.

Analysieren Sie nun die Werte der einzelnen Parameter, wie wir es mit dem Prognosefehler getan haben.

Die Werte der Parameter (mit Ausnahme von theta) sind stabil und weisen einen Trend zu einem konstanten Wert auf. Die Theta-Werte (MA-Koeffizient) sind unbeständiger, bewegen sich aber zwischen 0,6 und 0,9.

Als Nächstes sollten wir die deskriptiven Statistiken und die zugrundeliegenden Verteilungen der Parameterwerte untersuchen, aber diese Aufgabe überlassen wir Ihnen.

Den Datensatz und die Analyse entnehmen Sie bitte dem beigefügten Arbeitsblatt.

Schlussfolgerung

In diesem Artikel wurden die Schritte zur Durchführung von Backtests für ein Prognosemodell mit minimalen oder gar keinen Zwischenberechnungen erläutert. Wir haben die integrierten Funktionen von Excel verwendet: INDEX(.) und SEQUENCE(.) verwendet und die Funktion "Datentabelle" genutzt, um die Berechnung für alle vordefinierten Indizes durchzuführen.

Sobald wir die Backtesting-Ergebnisse haben, wenden wir uns der statistischen Analyse zu und bewerten ihre Eigenschaften und Verteilung, um etwaige Verzerrungen (z. B. serielle Korrelation) in den Ergebnissen aufzudecken.