Proof of Concept: Expected Credit Loss mit Power BI
Photo by Carlos Muza on Unsplash, abgerufen am 13.11.2019
In unserem Insight zum Thema Business Intelligence haben wir einen ersten Überblick zum Thema Self-Service BI (SSBI) Tools und derer potentieller Einsatzgebiete in Finanzinstituten gegeben. Darauf aufbauend stellen wir im folgenden Proof of Concept beispielhaft die Implementierung eines solchen Anwendungsfalles in Microsoft Power BI vor. Im Rahmen dessen erarbeiten wir einen konkreten Bericht in Microsoft Power BI Desktop bzw. anschließend ein Dashboard im cloudbasierten Power BI Dienst/Web Service, das ein Monitoring des Expected Credit Loss für ein Dummy Portfolio ermöglicht. Wir gehen hierbei insbesondere auf die Schritte 1 und 2 des Insights ein, also den Aufbau des Datenmodells, die Basisanalyse und die Visualisierung.
Der in Power BI Desktop erstellte Bericht steht lokal zur Verfügung und greift auf ein begrenztes Subset an Funktionen zu. Eine breitere Funktionspalette wird hingegen bei Verwendung des Power BI Web Services genutzt: Unter anderem kann der Bericht für andere freigegeben, innerhalb eines Teams erstellt und bearbeitet werden. In einem ergänzenden Dashboard lassen sich darüber hinaus die wichtigsten KPIs visuell zusammenfassen. Einige dieser Funktionen diskutieren wir in Schritt 3 unseres Beispiels.
Die Ausgangssituation:
Was ist der Expected Credit Loss? Was soll dargestellt werden?
Der Expected Credit Loss bezeichnet die Prognose des im Stressfall eintretenden Verlusts aus nicht nach Fair Value bewerteten Assets. Seit IFRS 9 muss diese auf Basis eines geeigneten Modells erstellt werden. In bestimmten Fällen kann hierfür eine vereinfachte Beschreibung herangezogen werden, die wir im Folgenden kurz vorstellen und im Anschluss bei der Berichterstellung verwenden.
Das Modell
Der erwartete Kreditverlust (Expected Credit Loss, ECL) ermittelt sich aus der Kredithöhe zum Ausfallzeitpunkt (Exposure at Default), der Verlustquote (Loss Given Default) und der Ausfallwahrscheinlichkeit (Probability of Default). Er kann zum jeweils aktuellen Zeitpunkt wie folgt berechnet werden:
Das Exposure at Default kann hierbei entweder als Rohwert eingehen oder um den als Collateral hinterlegten Betrag korrigiert werden. Der Expected Credit Loss unter Berücksichtigung vorhandener Collaterals (ECL*) berechnet sich in diesem Beispiel gemäß
Um den ECL im Falle von Fremdwährungsgeschäften einheitlich in Euro darzustellen, ist ggf. eine Umrechnung zum jeweils gültigen Wechselkurs (FX-rate) notwendig:
Welche Daten dienen als Grundlage?
Um den Expected Credit Loss nach oben stehendem Modell zu berechnen, sind Inputdaten notwendig, die häufig aus verschiedenen Systemen stammen. In diesem Beispiel modellieren wir die Daten daher absichtlich so, dass sie in unterschiedlichen Formaten vorliegen, um einen realitätsnahen Komplexitätsgrad nachzustellen (vgl. Abb. 1):
![Abbildung 1: Kennzahlen, Datenquellen und -formate der verwendeten Beispieldaten. [1-4]](https://images.squarespace-cdn.com/content/v1/54f9ea6be4b0251d5319ad8b/1581854220289-M1JIKBRTT9VRC53N6AGV/Bild1.png)
Abbildung 1: Kennzahlen, Datenquellen und -formate der verwendeten Beispieldaten. [1-4]
Schritt für Schritt zu Bericht und Dashboard
Im Falle einer selbstkonfigurierten Lösung sähe das Standardvorgehen auf Basis obiger Inputdaten wie folgt aus: Die einzelnen Quelldateien würden ganz oder teilweise in .xslx-Formate überführt, um die Daten so aufzubereiten, dass der Expected Credit Loss gemäß obiger Formel berechnet werden kann. Anschließend könnten die gewünschten Grafiken erstellt und formatiert werden, bevor sie zu einer Präsentation in MS PowerPoint zusammengestellt würden. Zur Aktualisierung der Daten bzw. Modifizierung der erstellten Grafiken müssten die beschriebenen Teilschritte erneut durchlaufen werden.
Um diese kleinteiligen Prozessschritte effizienter zu gestalten, bietet sich die Nutzung eines Self-Service BI Tools an. Beispielhaft nutzen wir im Folgenden zunächst MS Power BI Desktop zur Illustration der Berichtserstellung. Wir folgen dabei den Schritten, die wir im Insight zum Thema Business Intelligence genauer beschrieben haben:
Schritt 1: Das Datenmodell & Basisanalyse Als initialen Schritt müssen die angelieferten Daten in Power BI Desktop geladen werden, um das vollständige Datenmodell zu erstellen.
Um die verschiedenen Formate zu laden, bietet MS Power BI vordefinierte Importfunktionen für eine Palette an Formaten und Datenquellen an, die sogenannten Connectors (vgl. Abb. 2).
Abbildung 2: Power BI Menü zum Import verschiedener Datenformate. Quelle: MS PowerBI.
Abbildung 3: Vorschau der zu importierenden Daten. Hier kann eine Selektion von Datenfeldern und Feldinhalten vorgenommen werden. Quelle: MS PowerBI.
Nach Auswahl der Datenquelle wird eine Vorschau der zu importierenden Daten angezeigt, in der bereits eine erste Vorselektion stattfinden kann (vgl. Abb. 3).
Die nach Power BI importierten Daten müssen zunächst in Tabellen abgelegt bzw. zusammengefasst werden (vgl. Abb. 4).
Abbildung 4: Die importierten Daten werden in einer Tabelle abgelegt und vorformatiert. Quelle: MS PowerBI.
Dazu öffnet sich in Power BI der sogenannte Power Query Editor (vgl. Abb. 5), in dem auch die folgenden Transformationsschritte durchzuführen sind.
Abbildung 5: Der Power Query Editor bietet verschiedene Menüs zur Datentransformation an. Sind die Daten final bearbeitet, können die Änderungen ins Datenmodell übernommen und der Editor geschlossen werden. Quelle: MS PowerBI.
Die in der Tabelle abgelegten Daten werden zur späteren Verarbeitung transformiert, bzw. von Prozentzahlen in Dezimalwerte überführt, und in einer zusätzlichen Spalte („LGD_DECIMAL“) abgelegt (vgl. Abb. 6).
Abbildung 6: Die eingeladenen Daten des Loss Given Default werden in Dezimalzahlen umgewandelt und in einer zusätzlichen, benutzerdefinierten Spalte abgelegt. Quelle: MS PowerBI.
Abbildung 7: Power BI Menü um Daten aus externen Webquellen abzurufen. Quelle: MS PowerBI.
Analog wird mit den übrigen Datenquellen verschiedener Formate verfahren. Besonders hervorheben möchten wir an dieser Stelle lediglich das Vorgehen bzgl. der FX Kurse. Diese rufen wir von der EZB Webpage ab. Hierbei wählen wir zu Demonstrationszwecken bewusst zwei verschiedene Vorgehen für aktuelle und historische Wechselkurse.
Für aktuelle FX Kurse: Wir extrahieren die (tages-) aktuellen FX Kurse mit Hilfe der Power BI Screenscraping Routine (vgl. Abb. 7) aus der EZB Webpage und legen diese in einer Tabelle in Power BI ab.
Für historische FX Kurse ab 1999: Die EZB stellt u.a. eine .csv Datei zum Abruf bereit. Da die Datenstruktur jedoch für die von uns beabsichtigte, weitere Verwendung umständlich zu handhaben ist, nutzen wir an dieser Stelle zu Demonstrationszwecken ein selbst erstelltes Python Skript zum Transformieren der Daten. Selbstverständlich könnte man das gleiche Ergebnis auch mit Hilfe der Power BI Toolbox realisieren. Abb. 8 illustriert die Daten vor (linke Seite) und nach (rechte Seite) der Transformation.
Abbildung 8: Links: Auszug aus der .CSV Datei der historischen Wechselkurse der EZB, abgerufen am 14.11.2019. Rechts: Transformierte Wechselkursdaten auf Basis der EZB-Quelldaten. Die Transformation erleichtert die Verknüpfung der Wechselkurse mit den übrigen Geschäftsdaten.
Das Skript kann im Sinne einer All-in-One Lösung auch mit Hilfe des zugehörigen Add-ins direkt in Power BI ausgeführt werden (vgl. Hervorhebung in Abb. 9).
Abbildung 9: Power BI bietet die Möglichkeit sowohl R-als auch Python-Skripte zur unmittelbaren Datentransformation direkt im Programm auszuführen (siehe Menü “Skripts” auf der rechten Seite) ohne zuvor die Anwendung wechseln und die Daten erneut einladen zu müssen. Quelle: MS PowerBI.
Abbildung 10: Anlegen einer benutzerdefinierten Spalte, in der wir den berechneten ECL hinterlegen. Quelle: MS PowerBI.
Nachdem alle benötigten Datenquellen definiert und die Daten in Power BI importiert wurden, besteht der folgende Schritt darin, die Daten zu kombinieren und daraus die erforderlichen Informationen (hier: ECL) abzuleiten. Den ECL berechnen wir dann gemäß obiger Formel in einer benutzerdefinierten Spalte, (vgl. Abb. 10).
Schritt 2: Visualisierung & Data Discovery Ist das Datenmodell finalisiert und sind die gewünschten Größen berechnet, folgt die grafische Darstellung der Analyseergebnisse. Hierzu bietet Power BI ein Set an Visual Templates (vgl. Abb. 11), die mit den vorhandenen Daten verknüpft und anschließend nach individuellem Bedarf konfiguriert werden können.
Abbildung 11: In Power BI vorhandene Visual-Templates (links), die zunächst in einen Bericht bzw. in ein Dashboard eingefügt werden (rechts) und anschließend mit den gewünschten Datenfeldern verknüpft werden können (siehe Abb. 12). Quelle: MS PowerBI.
Im Folgenden nutzen wir die Templates für verschiedene Diagrammvarianten, um einen Bericht bzgl. des Expected Credit Loss zu erstellen. Einige der im Bericht konfigurierten Visuals werden anschließend als Kacheln auf einem Dashboard angeheftet. Das in der Slideshow der Abbildungen 12 bis 17 gezeigte Beispiel basiert ausschließlich auf Dummy Daten.
Falls der Bericht aktualisiert werden soll, müssen die Grafiken und Tabellen nicht erneut per Hand zusammengestellt werden: Power BI bietet hierfür die Funktion „Aktualisieren“, die diese Aufgabe automatisiert und umfassend durchführt (vgl. Abb. 18).
Abbildung 18: Power BI Desktop Menü mit hervorgehobener Schaltfläche "Aktualisieren". Quelle: MS PowerBI.
Auch die auf Berichten basierenden Dashboards werden im gleichen Zuge angepasst. Falls die Daten nicht wie in unserem Beispiel importiert, sondern als Live-Datenquelle eingebunden wurden, ermöglicht Power BI die Einrichtung einer automatischen Updatefrequenz, die Datenquellenanbindung per „Pushen“ nach Power BI oder auch ein Refresh bei jeder Benutzerinteraktion. Mehr zu diesem Thema finden Sie auf der Website von MS Power BI.
Schritt 3: Präsentieren, Schedulen & Teilen von Berichten und Dashboards
Abbildung 19: Power BI Desktop Menü mit hervorgehobener Schaltfläche zur Veröffentlichung des Berichts im Power BI Webservice. Quelle: MS PowerBI.
Ist ein Bericht (oder Dashboard) erstellt, wird es in aller Regel spätestens zu diesem Zeitpunkt mit anderen Nutzern geteilt. Hierfür gibt es verschiedene Möglichkeiten. Zum einen kann ein erstellter Bericht - ganz klassisch - als PDF exportiert und per Email versendet werden. Hierfür stellt bereits Power BI Desktop eine eigene „in PDF exportieren“ -Funktion zur Verfügung.
Abbildung 20: Pop-Up in Power BI Desktop nach Veröffentlichung eines Berichts. Quelle: MS PowerBI.
Veröffentlichung. Zum anderen gibt es auch die Möglichkeit, den Power BI Bericht direkt über den Power BI Web Service zu veröffentlichen und ihn dort für andere Nutzer verfügbar und editierbar (bspw. Filterung von Daten) zu machen. Um diese Features nutzen zu können, muss man sich zunächst im Power BI Web Service anmelden. Dies kann aus Power BI direkt erfolgen (vgl. Abb. 19), oder aber via Anmeldung im Browser und laden der (lokalen) Power BI Desktop Projekte. Bereits im Rahmen des Veröffentlichungsprozesses bietet Power BI den Abruf sogenannter Quick Insights an (vgl. Abb. 20).
Abbildung 21: Beispiel eines Quick Insights, das Power BI automatisiert aus den Basisdaten gewinnt. Quelle: MS PowerBI.
Abbildung 22: Möchte man einen Quick Insight weiterverwenden, gibt es die Möglichkeit diesen an ein vorhandenes Dashboard anzuheften oder ein neues Dashboard zu erstellen. Quelle: MS PowerBI.
Quick Insights. Die Quick Insights beinhalten eine neutrale, automatisierte Datenanalyse. Als Rückgabe werden eine Reihe an vorkonfigurierten Visuals zur Verfügung gestellt. Diese geben verschiedene Korrelationen wieder, die Power BI in den Basisdaten identifiziert hat (vgl. Abb. 21).
Abbildung 23: Nach Anheften einer neuen Dashboard Kachel kann man entweder zur Erstellung der „Telefonansicht“ wechseln, oder mit der Dashboarderstellung fortfahren. Quelle: MS PowerBI.
Da es sich um eine reine Datenanalyse ohne Berücksichtigung inhaltlicher Zusammenhänge handelt, müssen die Insights vor weiterer Verwendung selektiert und interpretiert werden. Hat man einen Quick Insight identifiziert, der z.B. im Rahmen eines Dashboards weiterver-wendet werden soll, kann dies unmittelbar über die entsprechende Schalt-fläche am Insight erfolgen, (vgl. Abb. 21 und 22). Sobald die Kachel am Dashboard angeheftet ist, schlägt Power BI außerdem die Erstellung einer Telefonansicht, die für mobile Endgeräte optimiert ist, vor (vgl. Abb. 23).
Abbildung 24: Wählt man "Telefonansicht erstellen", wird man automatisch durch die dazu notwendigen Schritte geleitet. Quelle: MS PowerBI.
Telefonansicht. Hierfür wird eine entsprechende Drag & Drop Funktionalität angeboten, die eine intuitive Zusammenstellung der mobilen Dashboardversion ermöglicht (vgl. Abb. 24).
Der Aufbau einer Layout-Variante für mobile Endgeräte ist auch für Berichte möglich (vgl. Abb. 25).
Abbildung 25: Auch für vorhandene oder neu zu erstellende Berichte bietet Power BI eine Assistenzfunktion zur Optimierung der Darstellung auf mobilen Endgeräten. Quelle: MS PowerBI.
In der rechten Hälfte des Bildschirmausschnitts werden die Visuals der jeweiligen Berichtsseite angezeigt, die in die linke Fensterhälfte gezogen, und dort bspw. in Größe und Position angepasst werden können. Ist die mobile Ansicht fertig, bzw. soll mit anderen Personen geteilt werden, kann ein QR-Code zwecks einfacherem Aufruf generiert und versendet werden (vgl. Abb. 26).
Abbildung 26: Das Generieren und Versenden eines QR-Codes erleichtert den Abruf des erstellten Dashboards über mobile Endgeräte. Quelle: MS PowerBI.
Abbildung 27: Menü zur Erstellung neuer Kacheln auf einem Dashboard. Quelle: MS Power BI.
Vom Bericht zum Dashboard. Nachdem wir im letzten Schritt ein Dashboard mit dem ausgewählten Quick Insight begonnen haben, fügen wir nun eine unserer in Power BI Desktop erstellten Berichtsseiten hinzu.
Dies erfolgt über die Schaltfläche „Liveseite anheften“ im Menü des erstellten Berichtes (vgl. Abb. 27). Alternativ kann man auch einzelne, im Bericht konfigurierte Visuals auf ein Dashboard übernehmen. Neben Berichtskomponenten und Quick Insights können auf dem Dashboard außerdem weitere Kacheln, bspw. mit Webinhalten, ergänzt werden (vgl. Abb. 28).
Abbildung 28: Beispiel eines Dashboards zum Expected Credit Loss. Quelle: MS PowerBI.
Freigabe & Rechte. Verfügt man über ein Pro- oder Premium Abo von Power BI, ist es zudem möglich einen Bericht, ein Dashboard oder Auszüge daraus für sich selbst und/oder Kollegen als Email-Abo einzurichten (vgl. Abb. 29).
Abbildung 29: Freigabe eines Berichts für andere Nutzer sowie Einrichtung eines Email-Abonnements für sich selbst und andere. Quelle: MS PowerBI.
Neben der generellen Freigabe eines Berichts oder Dashboards für andere Nutzer können einzelne Rechte präzise unter „Einstellungen“ festgelegt und eingeschränkt werden (vgl. Abb. 30). Die Erlaubnis Filter setzen zu dürfen ermöglicht eine eigenständige, rudimentäre Daten- bzw. Visualanalyse zur Beantwortung dynamisch aufkommender Fragen, wie es bspw. während einer Präsentation oder eines Meetings der Fall ist.
Abbildung 30: Auszug der Einstellungen bzgl. der Rechtevergabe an andere Nutzer sowie Customizing des generellen Verhaltens der Visuals in Berichten. Quelle: MS PowerBI.
Über die beschriebenen, ausgewählten Features hinaus bieten Power BI Desktop und der Power BI Web Service selbstverständlich zahlreiche weitere Funktionen und Möglichkeiten, Daten zu analysieren, aufzubereiten und visuell darzustellen. Je nach gewünschtem Anwendungsumfang ist im Einzelfall zu entscheiden, ob und für wie viele Mitarbeiter eine Pro- oder Premiumlizenz erforderlich bzw. sinnvoll ist.
Best Practice Tipps:
Basierend auf unserer Erfahrung sind die folgenden Punkte essentiell für den erfolgreichen, täglichen Gebrauch von SSBI Tools:
Die verwendeten Daten auf das notwendige Minimum reduzieren, um jederzeit den Überblick über das Datenmodell zu behalten (Stichwort „Lean Data“). Konkret bedeutet dies z.B. überzählige Spalten so früh wie möglich zu entfernen.
Datentypen direkt beim Einladen prüfen und ggf. mit Hinblick auf die weitere Verarbeitung korrigieren.
Vollständigkeit der Werte prüfen und ggf. entscheiden, was (fachlich) mit NULL oder N/A Werten geschehen soll, damit weitere Transformationen problemlos durchgeführt werden können.
Daten in gefilterten Visuals darstellen, um über den Erstellungszeitpunkt hinaus ein dynamisches Arbeiten zu ermöglichen.
Fazit
Insbesondere im Vergleich zu selbst entwickelten MS Office-BI Lösungen ist das Kombinieren zahlreicher Quelldaten, auch in verschiedenen Formaten, mit geringem Aufwand möglich.
Der Datenfluss von Input über Transformation bis zur Visualisierung ist übersichtlich gestaltet.
Einmal entwickelte Berichte können per Knopfdruck aktualisiert werden, wenn sich die verwendeten Datenquellen ändern.
Interaktive Visuals ermöglichen eine umfangreiche Auswertung der vorliegenden Daten bzw. Data Discovery ohne tiefere Programmierkenntnisse
QUELLEN:
[1] Logo BaFin: https://www.bafin.de/DE/Startseite/startseite_node.html, Download am 10.01.2020.
[2] Logo EZB: https://www.ecb.europa.eu/ecb/html/index.de.html, Download am 10.01.2020.
[3] Logo Deutsche Bundesbank: https://www.bundesbank.de/de/presse/pressematerial/logo/logo-der-deutschen-bundesbank-608248%20., Download am 10.01.2020.
[4] JSON- Logo: https://www.json.org/json-en.html, Download am 10.01.2020.
