Das Do-It-Yourself Haushaltsbuch auf Basis von Excel

Ziel des Do-It-Yourself Haushaltsbuchs ist eine individuell, den eigenen Bedürfnissen angepasste, Lösung für die Analyse der eigenen Finanzen zur Verfügung zu stellen. Anders als bei vielen anderen Excel-Lösungen müssen die Daten nicht händisch eingegeben werden, sondern wir beziehen die Datenbasis aus einem Export der Kontentransaktion bei der Hausbank. Wir nutzen die mächtige Funktionalität von sog. Pivot-Tabelle (Kreuztabellen) womit wir im Handumdrehen Analysen durchführen können.

Die Vorteile dieser Do-It-Youself Lösung sind die folgenden:

  • Volle Kontrolle: Kann auf die individuellen Bedürfnisse angepasst und erweitert werden.
  • Sicherheit: Die sensiblen Kontendaten werden nicht aus der Hand gegeben, sondern bleiben auf dem eigenen Rechner.
  • Automatisch: Durch Nutzung der exportierten Kontendaten ist keine manuelle Dateneingabe notwendig.
  • Baukastenprinzip: Kann Schritt für Schritt aufgesetzt werden. Jeder kann für sich selbst entscheiden wie weit er in die Analyse gehen möchte.
  • Einfachheit: Ist mit wenig Zeitaufwand sehr einfach in 15 Schritten umsetzbar.

Die folgenden Voraussetzungen müssen gegeben sein:

  • Der Zahlungsverkehr erfolgt größtenteils elektronisch
  • Der Zahlungsverkehr erfolgt größtenteils über ein Girokonto
  • Es besteht ein Online-Banking Zugang
  • Über das Online-Banking oder über einen Drittanbieter können Umsätze exportiert werden
  • Du hast eine Tabellenkalkulation (in diesem Beispiel nutzen wir Excel)

Wir werden jetzt Schritt für Schritt eine Übersicht für deine Zuflüsse (Einnahmen), Abflüsse (Ausgaben) und der persönlichen Bilanz erstellen. Im Beispiel machen wir eine Analyse für ein komplettes Jahr. Mit Excel-Mittel kann man aber – je nach exportierten Zeitraum – jeden beliebigen Zeitraum analysieren.

Umsätze herunterladen

Schritt 1: Melde dich bei deiner Hausbank für das Online-Banking an.

Schritt 2: Öffne die Umsatzübersicht für dein Girokonto

Schritt 3: Beispiel DKB: Wähle den maximal möglichen Zeitraum für deine Umsätze (wenn möglich mindesten die letzten 24 Monate) und klicke oben rechts auf „CSV herunterladen“ (gelb markiert)

Schritt 4: Öffne die exportierte Datei mit Excel

Struktur der exportierten Umsatzdaten anpassen

Schritt 5: In der exportierten Datei von der DKB löschen wir die ersten 6 Zeilen, da sie nur Metainformationen enthält. Wir wollen die Umsatzdaten ab Zeile 7 nutzen.

Schritt 6: Wir benennen den Reiter (Arbeitsblatt) in „_daten“ um (Rechter Mausklick auf den Reiter und dann Menüpunkt „Umbenennen“)

Schritt 7: In diesem Schritt identifizieren wir die Tabellenspalten, die wichtig für unsere Auswertung sind. Bei anderen Banken kann die Spalten-Struktur und Benennung anders aussehen. Wichtig ist, dass die drei Spalten Transaktionsdatum, Auftraggeber / Begünstigter und Betrag identifiziert werden.

  • Wir identifizieren im Reiter „_daten“ die Spalten mit dem Transaktionsdatum (hier: Wertstellung) und benennen die Spalte in „_datum“ um.
  • Wir identifizieren im Reiter „_daten“ die Spalten mit dem Auftraggeber / Begünstigter“ und benennen die Spalte in „_position“ um.
  • Wir identifizieren im Reiter „_daten“ die Spalten mit dem Betrag und benennen die Spalte in „_betrag“ um.
  • Wir ergänzen eine weitere Spalten „_monat“, „_jahr“ und „_art“

Schritt 8: Im nächsten Schritt formatieren wir die Tabelle. Dies ermöglicht uns z.B. direkt Spaltennamen zu referenzieren und sorgt dafür, dass neu Formeln im kompletten Bereich automatisch ausgefüllt werden.

Wir ergänzen für die neuen Spalten Formeln (dazu klicken wir zunächst auf die entsprechende Zelle und dann in den Formelbereich im Header von Excel wo wir dann die Formel hineinkopieren):

  • In der ersten leeren Zelle unter der Spalte „_monat“ fügen wir die folgende Formel ein:=WENN(MONAT([@[_datum]])<10;"0";"") & MONAT([@[_datum]]) & "/" & JAHR([@[_datum]])
  • In der ersten leeren Zelle unter der Spalte „_jahr“ fügen wir die folgende Formel ein: =JAHR([@[_datum]])
  • n der ersten leeren Zelle unter der Spalte „_art“ fügen wir die folgende Formel ein: =WENN([@[_betrag]]>0;"Zufluss";"Abfluss")

Pivot-Tabellen für die Analyse erstellen

Schritt 10: Über EINFÜGEN -> PivotTable erstellen wir eine neue Pivot-Tabelle.

Ein neuer Reiter wurde erstellt. Wir benennen den Reiter in „_abfluss“ um.

Schritt 11: Wir konfigurieren die Pivot-Tabelle nach unseren Bedürfnissen

  • Wir sehen einen leeren Pivotbereich
  • Auf der rechten Seite sehen wir den Konfigurationsbereich für Pivot-Tabellen
  • Wir ziehen die Felder „_jahr“ und „_art“ in den Bereich „Filter“
  • Wir ziehen das Feld „_position“ in den Bereich „Zeilen“
  • Wir ziehen das Feld „_betrag“ in den Bereich „Werte“

Schritt 12: Jetzt passen wir die Pivot-Tabelle an.

  • Im linken Bereich sehen wir die konfigurierte Pivot-Tabelle mit den Kontodaten.
  • Wir klicken mit der rechten Maustaste auf die Spalte „Summe von _betrag“ und wählen den Punkt „Wertfeldeinstellungen“ und dann „Zahlenformat“. Hier stellen wir als Format „Währung“ ein und bestätigen mit OK.
  • Im Filter wählen wir für das Jahr (_jahr) das letzte Jahr aus (im April 2020 also 2019)
  • Im Filter wählen wir für die Art (_art) den Eintrag „Abfluss“ aus
  • Neben dem Bereich „Zeilenbeschriftungen“ klicken wir auf das kleine Dreieck und wählen „Weitere Sortieroptionen“ aus
  • Im sich öffnenden Dialogfeld wählen wir „Aufsteigend (A bis Z) nach:“ und im Auswahlfeld „Summe von _betrag“ aus.

Die Pivot-Tabelle enthält nun alle Ausgaben (Abflüsse) für den ausgewählten Zeitraum sortiert nach den größten Positionen.

Schritt 13: Erstellen des Überblicks über die Einnahmen (Zuflüsse)

  • Rechtsklick auf den Reiter „_abfluss“ und der Auswahl des Punktes „Verschieben oder kopieren…“
  • Im Auswahlfeld die Option „Kopie erstellen“ anklicken und mit „OK“ bestätigen.
  • Den kopierten Reiter „Abfluss (2)“ in „_zufluss“ umbenennen
  • Im Filter „_art“ den Eintrag Zufluss wählen
  • Neben dem Bereich „Zeilenbeschriftungen“ klicken wir auf das kleine Dreieck und wählen „Weitere Sortieroptionen“ aus
  • Im sich öffnenden Dialogfeld wählen wir „Absteigend (Z bis A) nach:“ und im Auswahlfeld „Summe von _betrag“ aus.

Die Pivot-Tabelle enthält alle Einnahmen (Zuflüsse) für den ausgewählten Zeitraum sortiert nach den größten Positionen.

Schritt 14: Erstellen einer Bilanz für den ausgewählten Zeitraum

  • In den Reiter „_daten“ klicken
  • Über Einfügen -> PivotTable neue Pivot-Tabelle analog zu Schritt 10 erstellen
  • Den neuen Reiter in „_bilanz“ umbenennen
  • Feld „_jahr“ in den Bereich „Filter“ ziehen
  • Feld „_monat“ in den Bereich „Zeilen“ ziehen
  • Feld „_betrag“ in den Bereich „Werte“ ziehen
  • In der Pivot-Tabelle im Filter „_jahr“ das entsprechende Jahr (bei uns 2019) auswählen
  • In der Spalte „Summe von _betrag“ über Rechtsklick die Wertfeldeinstellung / Zahlenformat das Format Währung wählen.

Die Tabelle zeigt pro Monat und als Gesamtergebnis für das ganze Jahr an, wie die Bilanz von Zu- und Abfluss ist. Ein positiver Wert sagt aus, dass es mehr Einnahmen als Ausgaben gab und ein negativer Wert, dass es mehr Ausgaben als Einnahmen gab.

Diagramme erstellen

Schritt 15: Über Diagramme Werte visuell darstellen

  • Klicke auf die Pivottabelle im Reiter „_zufluss“
  • Dann klicke auf Einfügen im Bereich EINFÜGEN -> Diagramme -> 2D Kreis.
  • Es wird ein Kreisdiagramm hinzugefügt.
  • Beim Klicken auf das Diagramm erscheint rechts ein Pinsel-Symbol. Hier kann eine andere Formatvorlage für das Diagramm ausgewählt werden.
  • Den Titel des Diagramms kann man durch Anklicken in „Zufluss“ ändern.
  • Für den „Abfluss“ gehen wir analog vor.
  • Das Diagramm für den Abfluss wird wahrscheinlich deutlich mehr unterschiedliche Positionen haben, so dass das Diagramm sehr überladen sein kann. Hier kann man sich damit behelfen, dass man nur die größten Positionen darstellt. Dafür bestimmt man einen Mindestbetrag, der im Diagramm noch angezeigt werden soll und klickt auf das kleine Dreieck in der Zeilenbeschriftung und wählt den Punkt „Wertefilter“ aus. Dort wählt man dann „Kleiner als…“ (Achtung wir haben hier negative Werte) und gibt dann den Betrag ein, der noch in Tabelle enthalten sein soll (z.B. -1000€). Alle Werte, die weniger als -1000€ haben werden dann herausgefiltert.

Man sieht bei dieser Aufstellung, dass vier Positionen für über die Hälfte der Ausgaben verantwortlich sind. Bei der Analyse von Kostentreibern sollte man sich auf die großen Positionen konzentrieren.

Um in der Bilanz nicht nur die einzelnen Werte für die Monate anzuzeigen, sondern auch die laufende Summe über die Monate (kumulierte Werte) fügen wir in der Pivottabelle noch eine weitere Ergebnisspalte ein:

  • Feld „_betrag“ ein zweites Mal Bereich „Werte“ ziehen (hier wird der kumulierte Wert angezeigt)
  • Mit Rechtsklick in der Spalte „Summe von _betrag2“ über die Wertfeldeinstellung / Werte anzeigen als / Laufende Summe in / Basisfeld: _monat.

Für die Bilanz nutzen wir nicht das Kreisdiagramm, sondern ein Säulendiagramm (EINFÜGEN -> Diagramme -> 2D Säulen) Wir können das Diagramm über die Formatvorlagen nach eigenem Geschmack formatieren.

Um die kumulierten Werte als Linie darzustellen klicken wir auf eine der Säulen. Alle Säulen der Reihe werden markiert und über Einfügen Liniendiagramm ändern wir die Reihe in eine Liniendarstellung.

Im Beispiel oben sieht man Monate mit mehr Ausgaben als Einnahmen (negative Balken). Bei drei Monaten überwogen die Einnahmen (positive Balken). Die aufsummierte Gesamtbilanz des Jahres fällt positiv aus (orange Linie). Die Jahreseinnahmen übertrafen damit die Jahresausgaben.

Das Do-It-Yourself Haushaltsbuch steht

Wenn noch nicht geschehen solltest du spätestens jetzt deine Datei im Excel-Format speichern. (Achtung: Das CSV-Format reicht hier nicht!)

Das Excel enthält für den exportierten Zeitraum alle Kontobewegung des Girokontos. Mit Excel-Mittel können diese Daten beliebig ausgewertet werden.

Im nächsten Schritt werden wir die Daten nach dem Finwohl-Geldfluss-Modell kategorisieren. Es ist aber auch eine individuelle Kategorisierung möglich.

Tipps und Tricks

Hier liste ich Tipps & Tricks für die Analyse der Daten auf.

Drill-Down auf die Einzelpositionen

Da Pivot-Tabellen die Ergbnisse nach bestimmten Kriterien aggregieren können durch Doppelklick auf jeden beliebigen Wert (Eurobetrag) im Wertebereich die Einzelpositionen aufgelistet werden. Dafür erstellt Excel automatisch einen neuen Reiter (Arbeitsblatt). Dieser Reiter kann nach der Nutzung wieder gelöscht werden.

Beliebigen Analyse-Zeitraum einstellen

  • Setze im FILTER der Pivot-Tabelle die Auswahl von _jahr auf „Alle“
  • Es wird jetzt das Ergebnis für alle Daten angezeigt (je, nachdem welcher Zeitraum vorher exportiert wurde)
  • Ziehe das Feld „_monat“ in den Bereich FILTER der Pivot-Tabelle
  • Über die Auwahl im Filter „_monat“ können nach bestätigen des Häkchens „Mehrere Elemente auswählen“ jetzt individuell Monate für die Ansicht ausgewählt werden (z.B. 04/2019 – 03/2020 für die letzten 12 Monate)

Monatliche (oder jährliche) Einnahmen / Ausgaben vergleichen

  • Ziehe das Feld „_monat“ in den Bereich SPALTEN der Pivot-Tabelle
  • Neben dem Gesamtergebnis für den gesamten Zeitraum werden auch die jeweiligen monatlichen Wert angzeigt.
  • Dies kann analog auch für das Feld „_jahr“ getan werden, um die jährliche Einnahmen / Ausgaben zu vergleichen

Zeilen nach Jahr gruppieren

Durch Ziehen des Feldes „_jahr“ in den Bereich ZEILEN als oberstes Element werden Daten nach Jahren gruppiert.