Kategorie: Projekte

Importieren Sie Ihre Online Banking-Daten aus MySQL bzw. MariaDB mit Bordmitteln nach Excel.

Der erste Teil dieses Tutorials beschäftigte sich vor allem mit der Abholung der Daten Ihrer Bankkonten unter Zuhilfenahme des open Source Online-Banking Tools hibiscus und der exemplarischen Auswertung der Daten via. SQL. Hibiscus bietet die Möglichkeit, über HBCI Kontodaten bzw. Kontobewegungen von den Banken abzurufen und diese in einer relationalen
Datenbank zu speichern.

Im zweite Teil des Tutorial Hibiscus 2 Excel geht es um das regelmäßige Übertragen Ihrer Onlinebanking Daten nach Excel. Microsoft Excel bietet dafür einige Möglichkeiten, die Sie in der Ribbonbar unter Daten finden. So können Sie Daten z.B. aus CSV-Files, anderen Excel Dokumenten oder Access-Datenbanken importieren. Da Hibiscus die Möglichkeit bietet MySQL bzw. MariaDB zur Verwaltung der Bankdaten zu verwenden, möchte ich in diesem Tutorial den Import aus MariaDB über einen ODBC-Treiber demonstrieren.

MariaDB – ODBC Treiber

Bei MariaDB handelt es sich um einen Fork von MySQL. MariaDB ist eine relationale Datenbank, die Ihre Daten verteilt in Tabellen speichert. Um auf diese Daten zuzugreifen, gibt es verschiedene Möglichkeiten. In diesem Beispiel soll der Zugriff über einen ODBC-Treiber erfolgen. Diesen könne Sie hier für MariaDB herunterladen und anschließend installieren.

Um aus Excel heraus auf eine MariaDB via. ODBC-Verbindung zugreifen zu können, müssen Sie eine Verbindung konfigurieren. Öffnen Sie dazu die APP ODBC Data Sources (32bit).

Im Register Benutzer-DSN können Sie einen neue Datenquelle Hinzufügen.

Zuerst vergeben Sie einen Namen (hier hibiscus). Klicken Sie auf Next > .

Anschließend geben Sie noch den Server-Namen an, auf dem Sie Ihre Datenbank betreiben. Bei der aktuellen MariaDB Version erfolgt im Standard die Verbindung über den TCP/IP-Port 3307. Je nach Konfiguration der Datenbank (z.B. SSL) sind noch weitere Parameter zu definieren. In der Regel können Sie über Next > Next > Finish die Einrichtung abschließen.

Sie sollten (siehe Abbildung 03) bei der Einrichtung durch Eingabe des Datenbankbenutzers und Passworts + Button Test DSN die Verbindung prüfen. Dies reduziert die spätere lästige Fehlersuche.

Daten in Excel importieren

In Excel können Sie Daten aus MariaDB (oder auch einer einer anderen ODBC Datenquelle) importieren.

Dazu navigieren Sie in das Register Daten und klicken dort auf Daten abrufen. Wählen Sie in dem DropDown-Menü Aus anderen Quellen > Aus ODBC.

Als nächstes selektieren Sie die ODBC-Datenquelle, die Sie anfangs angelegt haben. in diesem Beispiel “hibiscus”. Weiter geht es mit OK. Geben Sie Ihre Zugangsdaten für die Datenbank ein.

Wenn Sie die Verbindung korrekt angelegt haben, sollten Sie eine Übersicht alles Tabellen und Views Ihrer Datenbank sehen. Wählen Sie z.B. die Tabelle konto.

Der Wizzard zeigt Ihnen nun eine Übersicht des Tabelleninhaltes. Über den Button Laden können Sie die Daten in die aktuell gewählte Excel Tabelle importieren. Zusätzlich wird die Abfrage in der Excel Datein gespeichert.

Daten aktualisieren

Sie können über verschiedene Wege den Importvorgang wiederholen, so dass die Daten in Ihrer Excel-Datei aktualisiert werden.

Im Register Daten finden Sie den Eintrag Alle aktualisieren. Über diesen werden alle, in Ihrer Excel Datei enthaltenen Abfragen, neu ausgeführt und die Daten erneut eingefügt.

Über die Schaltfläche Abfragen und Verbindungen können Sie aber auch einzelne Abfragen gezieht ausführen und diese verwalten.

Auswertung

Zur Auswertung Ihrer Daten stehen Ihnen nun alle Möglichkeiten von Excel zur Verfügung. Sie können mit den Daten rechnen, Sie in Pivot-Tabellen analysiere oder mit Hilfe von Diagrammen visualisieren.

Datenbank-Views

Wie Sie eben sehen konnten, wurden Ihnen nur Tabellen und sogenannt Datenbank-Views zum Import angezeigt. Die im ersten Teil des Tutorials entwickelten Select-Statements können Sie nicht direkt eingeben.

Eine Möglichkeit komplexere Abfragen verwenden zu können bieten Datenbank-Views. Eine View ist eigentlich nichts anderes als ein in der Datenbank gespeichertes Select-Statement. Um z.B. die Umsätze Ihrer Konten in Excel auswerten zu können, legen Sie eine entsprechende View in der Datenbank (z.B. über HeidiSQL) an.

CREATE VIEW V_UMSAETZE AS
SELECT k.id,
       k.kontonummer,
       k.blz,
       k.bezeichnung,
       u.empfaenger_konto,
       u.empfaenger_blz,
       u.empfaenger_name,
       u.zweck,
       u.zweck2,
       u.betrag,
       u.saldo,
       u.datum,
       u.art
 FROM konto k, umsatz u
 WHERE u.konto_id = k.id
Quellcode 01: View V_Umsaetze

Diese View können Sie dann wie oben beschrieben in Excel importieren.

Hibiscus ist eine freie Online-Banking Software. In diesem Beitragsreihe geht es um eine Schnittstelle zu Excel als Alternative zu Money in Excel und die Analyse Ihrer Finanzdaten. Über diesen Weg lässt sich das eigene Haushaltsbuch automatisieren.

Beschäftigt man sich mit mit den eigenen Finanzen, findet man auf diversen Finanzblogger-Seiten – hier mit frugalisten.de, finanzfluss.de mal einige interessante Beiträge zu dem Thema – immer wieder den Ratschlag, ein Haushaltsbuch zu führen. . Oft sind sich die Blogger und auch die Kommentatoren einig, dass ein Haushaltsbuch die Mühe Wert ist, aber eben auch einiges an Mühe und Disziplin kostet, es zu führen. In sehr vielen Fällen läuft es auf eine Lösung in Excel oder einer anderen Tabellenkalkulation heraus. Auch bieten gerade fintechs und mittlerweile auch Banken für Ihre Kunden Analysefunktionen der Kontobewegungen.

In den folgenden zwei Beiträgen möchte ich Ihnen einen Open Source Ansatz zeigen, mit dem Sie eine ähnliche Funktionalität wie Money in Excel selber nachbilden können. Hierzu dient das Open Source Online Banking Tool hibiscus. Es fungiert als Schnittstelle zu Ihren Bankdaten. Wie Sie diese Daten dann nach Excel transportieren können, wird Thema des zweiten Beitrags werden.

Money in Excel

Microsoft bietet mit Money in Excel eine interessante Erweiterung von Microsoft 365 an. Die neue Lösung, die bisher nur in der US Version verfügbar ist, kommt als Template mit einem AddIn daher. Money in Excel bietet die Möglichkeit Konto- und. Kreditkartendaten in Excel zu importieren. Die Excel Vorlage bringt diverse Darstellungen und Auswertungen der eigenen Finanzdaten mit. So können Konten konsolidiert, regelmäßige Ausgaben ermittelt und in entsprechenden Charts dargestellt werden. Abgerufene Daten werden in sogenannten Snapshots eingefroren.

Das interessante an dieser Lösung ist, dass man die Auswertungen selber beliebig erweitern kann. Wer sich einen erste Eindruck zu Money in Excel verschaffen möchte, findet hier eine entsprechenden Artikel von Microsoft.

Microsoft bedient sich für den Import der Kontodaten der Dienste von Plaid. All denen , die Ihre Daten nicht über Plait abholen lassen möchten oder nicht auf die Veröffentlichung von Money in Excel in unseren Gefilden warten möchten, möchte ich hier eine alternative Lösung, basierend auf dem Open Source Online Banking Tool hibiscus, zeigen.

Hibiscus – Open Source Online Banking

Olaf Willhun initiierte mit seinem unter der GPL lizenzierten hibiscus, einem AddIn zu Jameica, ein Open Source Online-Banking Tool. Hibiscus bietet die Möglichkeit, vor allem über HBCI, Kontodaten abzurufen und Überweisungen sowie Lastschriften zu tätigen.

Hibiscus bringt ein eigenes Reporting mit. Es sind zeitliche Verläufe von Kontoständen sowie Einnahmen und Ausgaben Übersichten möglich. Eine sehr gelungene Funktion ist auch die automatisierte Kategoriezuordnung der einzelnen Buchungen. Dazu können Suchbegriffe und sogar Reguläreausdrücke verwendet werden. Kategorien können hierarchisch organisiert und über diese Hierarchie aggregiert ausgewertet werden. So ist es z.B. möglich, alle Ausgaben bei verschiedenen Discountern zu einer Kategorie Lebenshaltung zusammenzufassen.

Hibiscus bietet die Möglichkeit, die Kontobewegungen als CSV-Datei zu exportieren. Dies wäre ein einfacher Ansatz, um die Kontodaten in Excel weiter zu bearbeiten. Allerdings bedarf es dabei immer noch des manuellen Abgleichs.

Eine andere Möglichkeit, die Daten in einem eigenen Reporting respektive Excel-Haushaltsbuch für sich nutzbar zu machen, besteht darin, diese direkt aus der Datenbank zu lesen und in Excel zu importieren.

Hibiscus auswerten

Hibiscus nutzt in der Standard – Version der Anwendung eine dateibasierte H2 Datenbank. Es ist aber auch möglich, die Daten in einer eigenen MySql oder MariaDB – Datenbank zu speichert. Wie das geht, ist hier auf der Entwicklerseite beschrieben.

Nachdem Sie über hibiscus Ihre Bankverbindungen eingerichtet und die zugehörigen Kontobewegungen abgerufen haben, können Sie auf diese Daten über ein Datenbanktool wie z.B. HeidiSQL oder PhPMyAdmin zugreifen.

Abbildung 01 zeigt die Tabellen der aktuellen hibiscus – Version.

Kontostand und Umsatz

Den aktuellen Kontostand finden Sie beispielsweise in der Tabelle Konto.

SELECT k.id,
       k.kontonummer,
       k.blz,
       k.bezeichnung,
       k.saldo,
       k.saldo_datum
 FROM konto k

Quellcode 01: Aktueller Kontostand

Über die Tabelle umsatz, lassen sich alle Kontobewegungen ermitteln. Zur besseren Übersichtlichkeit, kann man die Tabelle konto zu den Umsätzen joinen.

SELECT k.id,
       k.kontonummer,
       k.blz,
       k.bezeichnung,
       u.empfaenger_konto,
       u.empfaenger_blz,
       u.empfaenger_name,
       u.zweck,
       u.zweck2,
       u.betrag,
       u.saldo,
       u.datum,
       u.art
 FROM konto k, umsatz u
 WHERE u.konto_id = k.id

Quellcode 02: Umsätze und Konto

Abbildung 02 zeigt, wie das Ergebnis dieser Abfrage aussehen könnte.

Wie bereits oben erwähnt, bietet hibiscus die Möglichkeit, Umsätze automatisch einer Kategorie zuzuordnen. Um diese Kategorien auszuwerten, können Sie das vorhergehende Select-Statement um einen weiteren Join zur Tabelle umsatztyp ergänzen. Sollten Sie in hibiscus nicht alle Umsätze einer Kategorie zugeordnet haben, ist hier ein outer join notwendig.

SELECT k.id,
       t.name AS kategorie,
       k.kontonummer,
       k.blz,
       k.bezeichnung,
       u.empfaenger_konto,
       u.empfaenger_blz,
       u.empfaenger_name,
       u.zweck,
       u.zweck2,
       u.betrag,
       u.saldo,
       u.datum,
       u.art
 FROM konto k,  
      umsatz u left join umsatztyp t ON  u.umsatztyp_id = t.id
 WHERE u.konto_id = k.id
  AND year(u.datum) = YEAR(NOW())

Quellcode 03: Umsatz nach Kategorien

Das Ganze sieht dann in etwa so aus.

Via SQL können Sie natürlich eine Vielzahl an Analysen durchführen. Ich möchte Ihnen aber abschließend nur noch zwei kleine Beispiel zeigen.

Umsatz nach Kategorie

Wenn Sie ermitteln möchten, welche Beträge Sie in Abhängigkeit einer Kategorie erzielt bzw. ausgegeben haben, können Sie ausgehende von dem oben entwickelten Select-Statement einfach eine Gruppierung (group by …) nach der Kategorie (hier t.name) durchführen und die Betrag-Spalte summieren. Mit MIN(DATUM) bzw. MAX(DATUM) ermitteln Sie noch den Zeitraum, in dem die Buchungen durchgeführt wurden. Das Ganze grenzen Sie dann noch über eine entsprechende WHERE – Klausel – hier mit year(datum ) = YEAR(NOW()) auf die Umsätze des aktuellen Jahres – ein.

SELECT year(u.datum) AS jahr,
       nvl(t.name,'?') AS kategorie,
       MIN(u.datum) AS von,
       MAX(u.datum) bis,
       SUM(u.betrag) AS sum_betrag
 FROM  umsatz u left join umsatztyp t ON  u.umsatztyp_id = t.id
 where year(datum ) = YEAR(NOW())
group BY nvl(t.name,'?')

Quellcode 04: Auswertung nach Kategorie

Durch eine geschickte absteigende Sortierung nach dem summierten Betrag, sehen Sie sofort, in welche Kategorien Sie die größten Summen bewegen.

Gruppieren Sie die Daten neben der Kategorie noch nach dem aktuellen Monat (… GROUP BY nvl(t.name,’?’), MONTH(datum) ), so können Sie sich die Beträge je Kategorie pro Monat anzeigen lassen.

SELECT  MONTH(datum) AS monat,
        t.name kategorie, 
        MIN(datum) AS von,
        MAX(datum) bis,
        SUM(betrag) AS sum_betrag
 FROM  umsatz u left join umsatztyp t ON  u.umsatztyp_id = t.id
 where year(datum ) = YEAR(NOW())
group BY 
   nvl(t.name,'?'),
   MONTH(datum) 
ORDER BY 1 

Quellcode 05: Monatsauswertung nach Kategorie

Wiederkehrende Umsätze

In einem anderen Szenario geht es jetzt darum, herauszufinden, welche regelmäßigen Ausgaben Sie haben. ein erster Ansatz könnte so aussehen.

SELECT COUNT(*) AS anzahl_buchungen,
       u.empfaenger_konto,
       u.empfaenger_name,		
        MIN(datum) AS von,
        MAX(datum) bis,
        MAX(MONTH(datum)) AS anzahl_monate,
        SUM(betrag) AS sum_betrag
 FROM konto k, umsatz u
 WHERE u.konto_id = k.id
  AND year(datum ) = YEAR(NOW())
 GROUP BY 
   u.empfaenger_konto,
   u.empfaenger_name
 HAVING COUNT(*) > 1
ORDER BY 1 desc

Quellcode 06: Anzahl Buchungen

Mit dem Select- Statement aus dem Quellcode 06 gruppieren Sie Ihre Umsätze nach dem Empfängerkonto und zählen einfach die Anzahl der Buchungen. Ist die Anzahl größer als 1 ( HAVING COUNT(*) > 1 ) , wird es sich evtl. um eine wiederkehrende Buchung handeln.

Werten Sie, wie in diesem Beispiel nur ein Jahr aus, so können Sie mit MAX(MONTH(datum)) die Anzahl der Monate in denen es Buchungen gibt, ermitteln. Vergleichen Sie die Anzahl der Buchungen mit der Anzahl der Monate, sehen Sie grob, ob sich eine Buchung mehrmals pro Monat wiederholt. Auch über MIN(datum) bzw. MAX(datum) können Sie Rückschlüsse ziehen und sehen in welchem Zeitraum die Buchungen aufgetreten sind.

Sie können durch geschicktes Sortieren – z.B. absteigend nach dem Betrag (ORDER BY SUM(betrag) desc ) – sich auch erst um die großen Posten kümmern. Oder Sie schauen sich die vielen kleinen (HAVING COUNT(*) > 12) Buchungspositionen an.

Zwischenbilanz

Ich hoffe, dass die wenigen Beispiele hier, bereits deutlich machen, welche Informationen Ihre ganz persönlichen Daten hergeben. Fängt man erst einmal damit an, sich mit der Auswertung der Daten zu beschäftigen, fallen einem Dinge auf, die im Tagesgeschäft schnell untergehen. Seien es ungekündigte Verträge oder sich wiederholende Kleinstausgaben, die sich aber über die Zeit zu anschaulichen Beträgen summieren.

Sie können über SQL-Statements auch die Sparquote je Monat berechnen und sich die Kategorien mit den höchsten Umsätzen und damit verbunden mit dem größten Sparpotential ansehen.

Aber auch andere Erkenntnisse sind möglich. Wie oft geht man pro Woche/Monat zum Discounter? Wie oft – und vielleicht wie schnell – wird eine Bestellung bei amazon und Co. platziert? Was kostet im Jahr ein Auto ? (Versicherung, Reparaturen, Steuern, Tanken,..)

Welche Schlüsse Sie letztendlich aus diesen Informationen ziehen, bleibt natürlich Ihnen überlassen. Tipps und Diskussionen dazu finden Sie aber sicherlich in den entsprechenden Finanzblogs.

Wie geht es weiter?

Im zweiten Teil dieser Reihe, wird es – wie im Titel angekündigt – darum gehen, wie Sie die (eben) gewonnenen Daten mit Bordmitteln nach Excel automatisch und reproduzierbar übertragen können. Es ist natürlich auch möglich, die Daten in einem anderen Reporting-Tool auszuwerten. Allerdings ist der Aufwand, dies zu bewerkstelligen recht groß. Excel bietet so viele Möglichkeiten Daten auszuwerten und zu visualisieren, dass es sich im privaten Umfeld einfach anbietet Excel zu verwenden.