Fallstudie: Einfluss des Ölpreises und der Devisen- und Zinssätze auf Gewinn und Verlust
Live-Zugriff auf große internationale Datenquellen mit Risk Kit Data
Von Uwe Wehrspohn und Sergey Zhilyakov
[Download]
Inhalt
Präsentation von Risk Kit Data
Synchronisation von Zeitreihen
Kalibrierung von Ölpreis- und Zinsmodellen
Viele Menschen in der globalen Wirtschaft verarbeiten und analysieren täglich Informationen. Die Bedeutung eines schnellen und einfachen Zugriffs auf diese Informationen kann heute nicht hoch genug eingeschätzt werden. Etliche verschiedene Datenanbieter gewähren über spezielle APIs den Zugriff auf große Datenbanken mit Finanzdaten und Statistiken über das Internet. Dies vereinfacht einerseits den Zugang zu den Informationen. Andererseits ist ein gewisses technisches Wissen erforderlich, um diese Informationen zu lesen und zu parsen. Außerdem implementiert jeder Datenanbieter seine eigene API, was es schwierig macht, die Daten an einem Ort zu sammeln und zu aggregieren.
Risk Kit Data löst diese Probleme und bietet eine standardisierte Schnittstelle für den Zugriff und die Verarbeitung von Finanzdaten und Statistiken aus verschiedenen Datenquellen. Über eine benutzerfreundliche grafische Oberfläche können Benutzer Statistiken durchsuchen, Suchen durchführen, Daten direkt in Excel-Tabellen exportieren und noch mehr.
Risk Kit Data unterstützt fünf beliebte Datenquellen wie die Europäische Zentralbank EZB, Eurostat, Federal Reserve Economic Data FRED, die Weltbank und Apilayer Marketstack.
Präsentation von Risk Kit Data
Risk Kit Data ist ein Add-In für Microsoft Excel. Nach der Installation ist das Add-In als neue Symbolleiste verfügbar.
Abbildung 1. Symbolleiste Risikokit Daten.
Risk Kit Data bietet eine grafische Benutzeroberfläche zum Durchsuchen von Statistiken und Finanzdaten. Verwenden Sie das Finance-Task-Fenster, indem Sie in der Symbolleiste auf ‘Browse Financial Data’ klicken, um aktuelle und historische Aktien- und Indexpreise sowie Wechselkurse abzurufen. Greifen Sie auf das Daten-Task-Fenster zu, indem Sie auf ‘Browse Historical Data’ klicken, um die vollständigen Datenbanken der Europäischen Zentralbank EZB, von Eurostat, von Federal Reserve Economic Data FRED, der Weltbank und von Apilayer Marketstack zu durchsuchen.
Verwenden Sie die Beispiele unter dem Dropdown-Menü 'Example Workbooks', um mit Risk Kit Data zu beginnen. Unter 'Add-in Info' finden Sie die Online-Hilfe sowie die Informationen über das Add-in.
Um das Add-In in Excel zu deaktivieren, gehen Sie zu den ‘Add-In-Settings’ und klicken Sie auf ‘Disable Add-In’.
Beachten Sie, dass Sie ein persönliches API-Token von Apilayer Marketstack benötigen, um auf deren Datenfeeds zuzugreifen. Um ein Token anzufordern, gehen Sie in der Symbolleiste auf ‘Add-in Settings’ und folgen Sie dem angezeigten Link. Hier finden Sie auch einen Verweis auf die Nutzungsbedingungen für die Nutzung der Daten.
Abbildung 2. Add-in-Einstellungen und API-Token
Gewinn- und Verlustmodell
Der Ölpreis hat sich in den letzten Jahren stark verändert. Die hohe Volatilität betrifft vor allem Unternehmen, die Rohöl und andere Rohstoffe importieren. In diesem Abschnitt betrachten wir ein Gewinn- und Verlustmodell, bei dem der Ölpreis eine wichtige Quelle der Zufälligkeit ist. Wir beziehen auch den USD/EUR-Wechselkurs und einen 1-Jahres-EUR-Zinssatz als zusätzliche Beispiele für typische Marktfaktoren ein. Um die historischen Preise dieser Faktoren zu erhalten und um die Zeitreihen zu konsolidieren, werden wir Risk Kit Data verwenden.
Wir werden auch ein weiteres Add-In, Risk Kit, verwenden, um die Parameter von Prozessmodellen der Daten zu schätzen und eine Monte-Carlo-Simulation des Gewinn- und Verlustmodells durchzuführen. Für eine ausführliche Einführung in Risk Kit siehe Wehrspohn/Zhilyakov, Monte-Carlo-Simulation mit Risk Kit, 2021 .
Bestimmung des Modells
Als ersten Schritt definieren wir ein einfaches, rein illustratives GuV-Modell und identifizieren dessen Zufallsquellen (Abbildung 3). Zur Berechnung des Bruttogewinns und -verlusts beginnen wir mit einem Umsatz (D7) und ziehen alle Ausgaben einschließlich der Materialkosten (D8), Rohstoffe (D9), Personalkosten (D10) und Abschreibungen (D11) ab. Das führt zu dem Zwischenergebnis ‘Ergebnis vor Zinsen und Steuern’ (EBIT) (D12). Weiterhin berechnen wir das 'Ergebnis vor Steuern' (EBT) (D15), das der Summe der Zinserträge (D13) und der außerordentlichen Erträge (D14) addiert zum EBIT entspricht.
Abbildung 3. Gewinn- und Verlustmodell.
Nehmen wir an, dass der Umsatz (C7), die Materialkosten (C8), die Rohstoffe (C9), die Personalkosten (C10) sowie die Zinsen (C13) und die außerordentlichen Erträge (C14) einem Risiko unterliegen.
Wir nehmen an, dass der Umsatz (C7) eine Dreiecksverteilung mit den Parametern a=900, b=1000 und c=1050 hat, also dass der Umsatz zwischen 900 und 1050 schwankt mit 1000 als dem wahrscheinlichsten Wert.
Abbildung 4. Identifizierung der Quellen der Zufälligkeit.
Vielleicht fällt Ihnen auch die Funktion ‘OutputName’ in der Formelleiste auf, die an die Verteilung angehängt ist. Diese Funktion legt die Bezeichnung für dieses bestimmte Risiko fest, die wir später in den Diagrammen sehen werden.
Für die Materialkosten nehmen wir eine Normalverteilung mit Erwartungswert = 4% und Standardabweichung = 2% an.
Abbildung 5. Identifizieren der Verteilung für die Materialkosten.
Da die tatsächlichen Materialkosten variabel sind und von der Höhe des Umsatzes abhängen, modellieren wir sie als relative Größe ‘in % vom Umsatz’. Somit sind die gesamten Materialkosten in D8 gleich den relativen Kosten (C8) multipliziert mit dem Gesamtumsatz (D7).
Abbildung 6. Berechnen der gesamten Materialkosten.
Eine der größten Ausgaben in unserem Beispiel sind die Rohstoffe, die das Produkt aus dem Gesamtvolumen des importierten Öls (Fässer) und seinem Preis sind. Wir nehmen an, dass das Volumen des importierten Öls gleichmäßig im Intervall von 7,5 bis 9,5 Tausend Barrel verteilt ist. Um den Ölpreis zu schätzen, analysieren wir die historischen Marktdaten.
Auswahl der Daten
Um die historischen Preise zu erhalten, klicken Sie auf der Symbolleiste von Risk Kit Data (Abbildung 1) auf ‚Browse Historical Data‘. Das Add-In öffnet dann das Daten-Task-Fenster.
Abbildung 7. Daten-Task-Fenster.
Das Daten-Task-Fenster ermöglicht das Durchsuchen historischer Statistiken aus verschiedenen Datenquellen, das Durchführen von Suchen, das Filtern und Auswählen von Daten und das Laden der Daten in Excel.
Um eine Suche durchzuführen, aktivieren Sie die Registerkarte Suche (Abbildung 8), geben die Suchanfrage in das Textfeld ein und drücken ENTER. In unserem Beispiel suchen wir nach Rohöl bzw. auf Englisch ‘Crude Oil’. Beachten Sie, dass der Vorgang je nach Suchanfrage ein paar Minuten dauern kann.
Abbildung 8. Durchsuchen der Daten.
Es ist auch möglich, die Suche einzugrenzen, indem Sie die Ergebnisse für einige Datenquellen ausschließen. Klicken Sie dazu auf ‘Datenquellen filtern’ und deaktivieren Sie die Kontrollkästchen für die Datenquellen, die Sie ausschließen möchten.
Abbildung 9. Filtern von Datenquellen.
Wir werden die historischen Preise für Brent-Rohöl für die Schätzungen verwenden. Doppelklicken Sie auf die Tabelle 'Rohölpreise: Brent - Europe' in der Liste, um die historischen Daten zu laden. Sie werden feststellen, dass es vier ähnliche Tabellen in den Suchergebnissen gibt. Der Unterschied zwischen diesen Tabellen liegt in der Häufigkeit der Daten. Wir verwenden die erste Tabelle mit der täglichen Frequenz, die Preise für Geschäftstage liefert.
Nach einem Doppelklick aktiviert das Add-In die Registerkarte ‘Daten’.
Abbildung 10. Geladene Datenreihen.
In diesem Beispiel verwenden wir die Quelle Federal Reserve Economic Data, die nur eine Datenreihe pro Tabelle anbietet. Die anderen Datenquellen können Tausende von Datenreihen pro Tabelle haben. Verwenden Sie die Filter, um die Anzahl der Datenreihen zu reduzieren und um das Zeitfenster der Beobachtungen festzulegen (Abbildung 12).
Abbildung 11. Ölpreis zwischen 2011 und 2021
Die Ölpreise waren in den letzten zehn Jahren recht volatil und umfassten einen Bereich zwischen 9,12 USD pro Barrel am 21. April 2020 und 128,14 am 13. März 2012. Um eine homogenere Stichprobe zu erhalten, die für die aktuelle Marktsituation repräsentativ ist, legen wir daher das Zeitfenster auf die Zeit vom 21. Februar 2015 bis zum 21. Februar 2021 fest.
Abbildung 12. Filtern von Datenreihen.
Klicken Sie auf ‘Filter anwenden’, um die Datenreihen zu filtern.
Wenn Sie den Mauszeiger über dem Tabellennamen platzieren, sehen Sie die detaillierte Beschreibung im Tooltip. Beachten Sie die Einheiten der Daten.
Abbildung 13. Beschreibung der Tabelle.
Um die Daten in das Arbeitsblatt zu exportieren, klicken Sie auf ‘Export Data Series’. Das Werkzeug bietet hier zwei Optionen an. Die Daten können entweder als Zellfunktion oder als Rohwerte exportiert werden. Die Zellfunktion bietet oft mehr Kontrolle über die Daten. Insbesondere ist es hier möglich, die Beobachtungen nach Datum auf- und absteigend zu ordnen, mehrere Datenreihen auf einmal zu erhalten, das Zeitfenster nachträglich zu ändern usw.
Um die Serien-ID in der ersten Zeile und die Daten in der ersten Spalte auszugeben, aktivieren Sie die entsprechenden Kontrollkästchen.
Abbildung 14. Exportieren der Daten.
Das Add-In füllt automatisch die erforderliche Anzahl von Zeilen und Spalten.
Abbildung 15. Beispiel für die Zellfunktion DData.
Um die Parameter der Funktion zu aktualisieren, müssen Sie entweder die Funktion in der Formelleiste von Excel bearbeiten oder das Dialogfeld Funktion von Risk Kit Data verwenden. Aktivieren Sie dazu eine Zelle, in der Sie die Funktion platzieren möchten, und klicken Sie auf 'Insert Function' in der Symbolleiste von Risk Kit Data (Abbildung 1). In diesem speziellen Beispiel ordnen wir die Beobachtungen absteigend nach Datum, um das jüngste Datum an einer stabilen Position im Arbeitsblatt zu haben. Dies ist oft hilfreich, da spätere Prognosen auf dem jüngsten bekannten Wert einer Serie beginnen.
Dazu setzen wir das Argument 'DatesInIncreasingOrder' der Funktion auf FALSE1.
Abbildung 16. Aktualisieren der Funktion DData über das Dialogfeld ‘Excel-Funktion’.
Da die Ölpreise in US-Dollar angegeben sind, wir uns aber für den Euro interessieren, ist der nächste Schritt, die historischen Wechselkurse zu ermitteln und die Preise umzurechnen.
Dazu bleiben wir bei Federal Reserve Economic Data (FRED), um einen Datenanbieter für alle Zeitreihen zu haben.
Im Inhaltsverzeichnis des Daten-Task-Fensters wählen wir FRED als Datenquelle und gehen dann zu ‘Money, Banking & Finance’ - ‘Exchange Rates’ und wählen ‘Daily Rates’, um eine konsistente Periodizität mit den Ölpreisdaten zu haben. Dort wählen wir 'US / EUR Foreign Exchange Rate' durch Doppelklick auf den Eintrag aus.
Abbildung 17. Durchsuchen von Datentabellen und Quellen
Um das Zeitfenster für die Ölpreise anzupassen, setzen wir den Filter entsprechend und importieren die Daten als Array-Funktion in unser Arbeitsblatt. Beachten Sie, dass das in einem der vorherigen Schritte angegebene Zeitfenster automatisch auf die neu ausgewählte Datenreihe angewendet wird.
Abbildung 18. Filtern des Zielzeitfensters
Schließlich wählen wir einen 1-Jahres-EUR-Zinssatz entsprechend aus. Im Aufgabenbereich ‘Daten’2 wählen wir ‘FRED’ - ‘Money, Banking & Finance’ - ‘Interest Rates’ - ‘Interest Rate Swaps’ und wählen per Doppelklick den 1-Jahres-Tenor auf Basis EUR. Wir filtern das Zielzeitfenster wie bei der vorherigen Reihe und fügen die Daten als Array-Formel in das Arbeitsblatt ein.
Abbildung 19. Auswahl der Swap-Sätze aus FRED
Beachten Sie, dass das 'Von'-Datum und das 'Bis'-Datum als Grenzen des Zeitfensters für die Datenauswahl per Referenz definiert werden können. Dies bietet eine einfache Möglichkeit, dynamische Modelle zu erstellen, die sich leicht aktualisieren lassen. Auch ist das Enddatum der Reihe optional und kann daher weggelassen werden. In diesem Fall gibt die Funktion die aktuellsten verfügbaren Daten zurück.
Im folgenden Beispiel werden die Bezugsdaten auf einem zentralen Arbeitsblatt 'PARAMS' definiert und dann in den Formeln zitiert.
Abbildung 20. Dynamische Verknüpfung mit Daten
Synchronisation von Zeitreihen
Um die Ölpreise und Wechselkurse sowie die Zinssätze nach Datum zu synchronisieren, verwenden wir die Funktion ‘DDataSync’, die eine Vereinigung der Datumswerte in der ersten Spalte und die Datenreihen in den nachfolgenden Spalten zurückgibt. Um die Funktion einzusetzen, klicken Sie in der Symbolleiste auf ‘Insert Function’ (Abbildung 1). Wählen Sie im geöffneten Dialogfeld die Funktion ‘DDataSync’ und klicken Sie auf OK.
Abbildung 21. Dialogfeld der Funktionen von Risk Kit Data.
Die Funktion übernimmt die Bereiche im ersten Argument. Verwenden Sie das Excel-Argument-Trennzeichen, um die Bereiche abzugrenzen3.