Depotanalyse und Depotstatistik mit Excel

 

Sollten Sie Aktien oder Fonds besitzen, erhalten Sie am Jahresende einen Depotauszug und eine Ausschüttungsübersicht.

 

Daneben können Sie sich bei Ihrer Depotbank tagaktuelle Depotübersichten im Online-Verfahren ansehen und diese als Datei ausgeben, die Excel weiterverarbeiten kann.

 

Viele Anwender wissen jedoch mit der Excel-Datei ihrer Bank nichts anzufangen.

 

Mit einigen Tricks können Sie die Datei ihrer Bank nutzen um Depot-Titel nach folgenden Kriterien aufzugliedern um für größtmögliche Transparenz zu sorgen:

  • Länderübersicht
  • Branchenstruktur
  • Kategorie: Aktien bzw. Fonds
  • Aufgliederung nach Anschaffungshistorie (Anschaffungsjahr mit Einstandswert und aktuellem Wert)

Aufbau der Excel-Auswertung (Depotanalyse.xlsx):

 

A) Übersicht der Tabellenblätter

Depotstatistik und Depotanalyse mit Excel - Tabellenblaetter - www.excel-finanz.de

 

B) Erläuterungen zu den einzelnen Tabellenblättern

 

1) Tabellenblatt „Datei“

 

In das Tabellenblatt “Datei“ kopieren Sie die Depotdatei Ihrer Bank. Die vorliegende Muster-Depotdatei wurde aus einer Datei per 17.6.2020 abgeleitet und stark abgewandelt, so dass keine Rückschlüsse auf ein bestehendes Depot oder gar Depotempfehlungen möglich sind.

 

Abbildung - Depotdatei-Bank (auszugsweise)

Depotstatistik und Depotanalyse mit Excel - Depotdatei - Bank - www.excel-finanz.de

 

2) Tabellenblatt „Titel_Ausschüttung“

 

SVERWEIS greift auf das Tabellenblatt „Datei“ zu und findet die Aktien bzw. Fonds über den Suchbegriff (hier: ISIN-Nummer). Sie übernehmen nur die Felder aus der Depotdatei, die für Sie von Interesse sind.

 

Abbildung - Übernahmefelder

Depotstatistik und Depotanalyse mit Excel - Übernahmefelder - www.excel-finanz.de

  

Beginnend ab Spalte J nehmen Sie manuelle Einträge vor, die in weiteren Tabellenblättern für den Zugriff von Bedeutung sind:

 

a)     Länderkürzel, z.B. USA, D, CH

b)     Branchen, z.B. Internet, Chemie, Medizintechnik

c)     Kategorie: Aktien bzw. Fonds

 

Abbildung – Länder/Branchen/Kategorien

Depotstatistik und Depotanalyse mit Excel - Länder, Branchen, Kategorien - www.excel-finanz.de

 

Mit der Eintragung der Ausschüttungen nach Monaten ermitteln Sie die Dividendenrendite (sofern Jahr vollständig).

 

Abbildung – Ausschüttung

Depotstatistik und Depotanalyse mit Excel - Ausschüttung - www.excel-finanz.de

 

Selbstverständlich können Sie nach Belieben weitere Eintragungen vornehmen.

 

3) Tabellenblatt „Laender“

 

Die Funktion “SUMMENWENN“ greift auf das Tabellenblatt „Titel-Ausschuettung“ zu und addiert die aktuellen Depotwerte nach Länderkürzeln (mit grafischer Darstellung).

 

Abbildung – Statistik-Länder

Depotstatistik und Depotanalyse mit Excel - Statistik Länder - www.excel-finanz.de

 

Abbildung – Statistik-Länder (Diagramm)

Depotstatistik und Depotanalyse mit Excel - Statistik Länder Diagramm - www.excel-finanz.de

 

4) Tabellenblatt „Branchen“

 

Zugriff per SUMMENWENN auf das Tabellenblatt „Titel-Ausschuettung“ und Addition der Depotwerte nach Branchen (mit grafischer Darstellung).

 

Abbildung – Statistik-Branchen

Depotstatistik und Depotanalyse mit Excel - Statistik Branchen - www.excel-finanz.de

 

Abbildung – Statistik-Branchen (Diagramm)

Depotstatistik und Depotanalyse mit Excel - Statistik Branchen Diagramm - www.excel-finanz.de

 

5) Tabellenblatt „Aktien_Fonds“

 

Auch hier greift die Funktion SUMMENWENN auf das Tabellenblatt „Titel-Ausschuettung“ zu und addiert die Depotwerte nach den Kategorien “Aktien“ bzw. “Fonds“ (mit grafischer Darstellung).

 

Abbildung – Statistik-Kategorie

Depotstatistik und Depotanalyse mit Excel - Statistik Aktien Fonds - www.excel-finanz.de

 

Abbildung – Statistik-Kategorie (Diagramm)

Depotstatistik und Depotanalyse mit Excel - Statistik Aktien Fonds Diagramm - www.excel-finanz.de

 

6) Tabellenblatt „Historie“

 

Zugriff auf die Daten per SVERWEIS und Trennung nach Jahren der Anschaffung.

 

Hierbei sind vom Anwender gewisse manuelle Vorarbeiten erforderlich, sofern ein Aktien- oder Fonds-Titel mehrmals gekauft wurde, da der Einstandswert aus den einzelnen Kaufbelegen zu ermitteln ist.

 

Das gleiche gilt für Teilverkäufe eines Titels, da sich auch hierdurch die durchschnittlichen Anschaffungskosten ändern. Hier gilt das FiFo-Verfahren, d.h. First-in-First-out: Titel die zuerst gekauft worden sind, gelten als zuerst veräußert.

 

Der ermittelte Einstandswert muss mit dem aus der Bankdatei übereinstimmen.

 

Abbildung – Einstandswert bei mehrfachem Kauf eines Titels

Depotstatistik und Depotanalyse mit Excel - Aktien Fonds Einstandswert - www.excel-finanz.de

 

Die Aufsplittung nach Zeitpunkt der Anschaffung hat Bedeutung für den unteren Teil der Auswertung, da nach Jahren getrennt wird.

 

Abbildung – Historie nach Jahren

Depotstatistik und Depotanalyse mit Excel - Aktien Fonds Jahresstatistik - www.excel-finanz.de

 

Eine Kurzzusammenfassung der Jahreswerte mit grafischer Auswertung rundet die Statistik ab.

 

Abbildung – Historie nach Jahren – Zusammenfassung und Diagramm

Depotstatistik und Depotanalyse mit Excel - Aktien Fonds Jahresstatistik Diagramm - www.excel-finanz.de