Excel - Depot noch vorhandene Stückzahl berechnen

[Christian98]

Hallo,

ich habe für meine Aktiendepots ein Exceldokument erstellt, in diesem Dokument habe ich mehrere Listen, jede Liste stellt ein Aktiendepot dar. Für das Forum, habe ich von diesem Dokument die für das Problem irrelevanten Dinge gelöscht, um es etwas übersichtlicher zu gestallten, ich hoffe ich habe nichts wichtiges gelöscht.

Zum Verständnis:
Die Listen besitzen folgende Spalten mit folgenden Funktionen
"ISIN": Dient zur eindeutigen identifizierung eines Wertpapiers
"Name": Enthält Zahlen, diese werden von unten nach oben hochgezählt und geben an, welcher Kauf/Verkauf zuerst durchgeführt wurde (Erster Kauf/Verkauf steht ganz unten und besitzt nummer 1)
"Datum": Hier wird das Datum angegeben, wann Ein Kauf/Verkauf ausgeführt wurde
"Im Depot seit": Hier wird angegeben, seit wann sich ein Kauf in dieser Liste / Depot befindet, diese Spalte dient zur Dokumentation eines möglichen Depotübertrages
"Anzahl": Hier wird angegeben, wie viele Stück bei einem Kauf/Verkauf gekauft bzw verkauft wurden (Positive Zahlen = Kauf und Negative Zahlen = Verkauf)
"Umsätze": Wenn hier ein Text drinnen steht so stellt diese Zeile eine Zeile da, die die Gesamtwerte dieses Wertpapiers berechnet und sollte so nicht mit in diese Berechnung einfließen

In den Lila markierten Spalten habe ich bereits Versuche angestellt, die noch vorhandenen Stückzahlen zu berechnen, jedoch habe ich keine Lösung gefunden, die korrekt funktioniert.
In den Rot markierten Spalten sind Hilfsspalten in denen werden Berechnungen angestellt, welche ich für meine Versuche verwendet habe. Wenn möglich wäre es gut, möglichst wenige Hilfsspalten zu verwenden.

Ich möchte nun eine Spalte erstellen, in der angezeigt wird, wie viele der gekauften Mengen noch vorhanden sind, dieser Wert sollte für jede Zeile berechnet werden. Hierfür muss jedoch das FIFO-Prinzip beachtet werden, da an der Börse das FIFO-Prinzip gilt. Für das FIFO-Prinzip hätte ich die Spalte "Name" verwendet, da es ja sein könnte, dass an einem Tag mehrere Käufe/Verkäufe durchgeführt wurden, weshalb das Datum hierfür nicht wirklich geeignet ist. Außerdem muss beachtet werden, dass keine Wertpapiere eines Kaufs verkauft werden können, welche sich zum Zeitpunkt des Verkaufs nicht im Depot befunden haben, weshalb ich die Spalte "Im Depot seit" eingefügt habe.

Beispiel:

ISIN	Name	Datum	Im Depot seit	Anzahl	Noch vorhanden ST	Umsätze	Anmerkung
DE000A1ML7J1	Vonovia	25.11.2020		9	9	Ausblenden
DE000A1ML7J1	1	25.11.2020		9	9
DE0008490962	DWS Fond	20.05.2020		20	20	Ausblenden
DE0008490962	7	20.06.2024		-5
DE0008490962	6	20.07.2020		-5
DE0008490962	5	20.06.2020	20.06.2020	-10
DE0008490962	4	20.05.2020	20.05.2020	10	10		hier sind noch alle 10 übrig, da noch nichts davon verkauft wurde
DE0008490962	3	18.05.2020	18.05.2020	30	20		hier sind 20 übrig, da am 20.06.2020, 5 und am 20.07.2020 nochmal 5 verkauft
DE0008490962	2	10.05.2020	10.05.2024	10	5		hier sind 5 übrig, da am 20.06.2024 verkauft
DE0008490962	1	20.04.2020	20.04.2020	5	0		hier sind 0 übrig, da am 20.06.2020 verkauft

*Spalte Anmerkung wurde nur eingefügt, um die Tabelle zu erläutern.
*Sollten z.B. am 20.07.2024 nochmal 10 Stück des DWS Fonds verkauft werden, so müssten die noch vorhandenen des Kaufs "2" auf 0 gehen und bei Kauf "3" auf 15.

P.S. das Excel enthält normal ein Makro, welches zum Ein- und Ausblenden der Käufe und Verkäufe dient, dies habe ich entfernt, da ich mein Dokument sonst nicht hochladen hätte können.

Ich hoffe ihr versteht meinen Beitrag, über eure Hilfe würde ich mich sehr freuen!

Mit freundlichen Grüßen
Christian

 

[floklo4]

Ich bin kein Excel-Profi, kann dir hierbei nicht helfen. Wäre es für dich vielleicht einfacher, Portfolio Performance zu nutzen?

https://www.portfolio-performance.info/

 

[Christian98]

An Portfolio Performance habe ich auch schon gedacht, jedoch habe ich meine Finanzen lieber in Excel, aber vielen Dank für deinen Vorschlag!

Aber wenn sich das Ganze mit Excel nicht realisieren lässt muss ich mir überlegen, ob ich dennoch auch Portfolio Performance umsteige.

 

[florian.]

Portfolio Performance läuft komplett lokal, deine Daten sind also sicher.
Das was du da beschrieben hast, kann PP problemlos.
Kostenlos ist es auch.

Und man kann sich austoben mit den Widgets, um alles schön zu Visualisieren.
Ich würde da keine Zeit in ein Excel gefrickel verschwenden.

 

[Micha-]

Also ich möchte meine Excel Tabelle nicht missen, Portfolio Performance ist mir nicht immer so übersichtlich wie gewünscht.

Aber die Tabelle des TO ist IMHO nicht optimal aufgebaut

Ich habe einfach immer
Datum, Stückzahl, Kurs, daraus damaliger Wert, aktueller Kurs, aktueller Wert , Depot
(und noch diverse andere Daten, zum Beispiel berechne ich die aktuelle Steuerlast, Rendite pro Jahr, und so weiter)

Ein Verkauf ist dann negative Stückzahl

Somit ist eine Summe der aktuell vorhandenen Papiere getrennt oder über alle Depot nur eine einfach Rechnung.

 

[Krik]

Moin Christan98,

das ist dein Problem:

"Name": Enthält Zahlen, diese werden von unten nach oben hochgezählt und geben an, welcher Kauf/Verkauf zuerst durchgeführt wurde (Erster Kauf/Verkauf steht ganz unten und besitzt nummer 1)
(...)
"Umsätze": Wenn hier ein Text drinnen steht so stellt diese Zeile eine Zeile da, die die Gesamtwerte dieses Wertpapiers berechnet und sollte so nicht mit in diese Berechnung einfließen

Du mischt in diesen Spalten Informationsarten. Excels Formeln sind darauf nicht ausgerichtet, die wollen pro Spalte (und manchmal auch Zeile) immer den gleichen Informationstyp. Wenn du Informationsarten mischt, machst du dir das Leben nur selber schwer.


Ich hab mal ein Tabellenblatt gemacht, so wie ich das machen würde.

• Eingaben werden in den Spalten A, E und F erwartet. Fehlen sie, wird das entsprechende Feld rot gefärbt.
• Deinen gesuchten Wert findest du in Spalte G. War gar nicht so kompliziert, oder?
• Den Namen habe ich auf ein anderes Tabellenblatt ausgelagert.
• Alle Formatierungen (Rahmenlinien, Farben, fetter Text) werden automatisch erzeugt.
• Es gibt auch exemplarisch ein Tabellenblatt mit einer Pivot-Tabelle zur vielleicht einfacheren Übersicht oder Auswertung.

Ich empfehle dir, von oben nach unten zu arbeiten, statt wie jetzt von unten nach oben. Die Formeln müssen dann nur runtergezogen werden. Das kann man auch prophylaktisch für ein paar tausend Zeilen machen. Du musst dann später nur noch ISIN, Datum und Mengenänderung eintragen.
Brauchst du wirklich eine Zahl für die Reihenfolge? Ist das nicht obsolet, wenn man das Datum der Änderung hat? Siehe Zeilen 14 bis 17 im Blatt "Börse2". Obwohl die Reihenfolge vertauscht ist, rechnet er richtig.

 

[Christian98]

@Krik vielen Dank für deine Antwort, aber deine Tabelle beachtet nicht, wann das Wertpapier in das Depot gekommen ist, oder?

So wie es bei dir ist, funktioniert es bei meinem Excel in Spalte "Noch vorhanden ST" auch, nur ich habe noch keine Löung gefunden, wie man miteinberechnen kann seit wann sich eine Wertpapiertransaktion in einem Depot befindet. Denn es kann ja sein, dass ich ein Wertpapier in Broker A früher gekauft habe als in Broker B, dann übertrage ich diese Transaktion von Broker A auf Broker B und so müsste bei einem Verkauf zunächst das übertragene Wertpapier beachtet werden.

 

[Micha-]

seit wann sich eine Wertpapiertransaktion in einem Depot befindet

Das ist doch vollkommen irrelevant, oder wofür braucht man das?

Ergänzung (25. Mai 2024)

so müsste bei einem Verkauf zunächst das übertragene Wertpapier beachtet werden.

Eben, es zählt nur das Anschaffungsdatum

 

[Christian98]

@Micha- das ist meiner Meinung nach schon relevant, denn siehe mein Beispiel.

1. Kauf: Broker A am 15.01.2024 15ST
2. Kauf: Broker B am 20.02.2024 10ST

3. Verkauf: Broker B am 30.03.2024 5ST
--> Diese 5ST werden vom Kauf vom 20.02.2024 abgezogen, da sich ja zu diesem Zeitpunkt die früher gekauften 15 ST noch in Broker A befinden

4. Depotübertrag: Kauf vom 15.01.2024 geht am 30.04.2024 von Broker A auf Broker B über

5. Verkauf: Broker B am 30.05.2024 5ST
--> Diese 5 Stück müssten dann vom Kauf welcher am 15.01.2024 in Broker A stattgefunden hat abgezogen werden, da nach Depotübertrag und weil die Übertragenen vor den im Broker B gekauften, gekauft wurden

 

[Krik]

aber deine Tabelle beachtet nicht, wann das Wertpapier in das Depot gekommen ist, oder?

Die Summewenns-Formeln betrachten das Datum. Die Vorgangsnummer wird ignoriert. Die habe ich nur eingebaut, weil du oben geschrieben hast, dass du irgendwas mit der vor hast.

nur ich habe noch keine Löung gefunden, wie man miteinberechnen kann seit wann sich eine Wertpapiertransaktion in einem Depot befindet.

=Summewenns([Bereich, der summiert werden soll]; [Bereich mit den Daten(Datum)]; "<=" & [Zelle mit dem zu vergleichenden Datum])

Daten sind ich Excel Zahlen. Ich glaube es war die Anzahl der Tage seit dem 01.01.1901 oder so. Das heißt, man kann ganz normal mit Vergleichsoperatoren arbeiten.
"<" -> vor
"<=" -> vor oder an dem Tag
"=" -> genau an dem Tag
"=>" -> danach oder an dem Tag
">" -> danach
"<>" -> nicht an dem Tag/davor oder danach

Denn es kann ja sein, dass ich ein Wertpapier in Broker A früher gekauft habe als in Broker B, dann übertrage ich diese Transaktion von Broker A auf Broker B und so müsste bei einem Verkauf zunächst das übertragene Wertpapier beachtet werden.

Dann mach noch eine zusätzliche Spalte, in der der Broker drin steht und erweitere die Summenwenns-Formeln, dass sie diese beachten. Das killt die Übersichtlichkeit, du musst dann dir die Pivot-Tabelle passend drehen, damit du hinterher noch durchblickst.

 

[Christian98]

@Krik Das mit der Summewenns Formel ist mir schon bewusst, jedoch bin ich nach über 15 Stunden versuchen noch immer zu blöd dafür eine Summewenns Formel zu bauen, die korrekt funktioniert.

 

[Micha-]

das ist meiner Meinung nach schon relevant,

Ok, ich war zu ungenau

es zählt nur das Anschaffungsdatum

Innerhalb des Depots

 

[Krik]

@Christian98
Die Formel ist recht einfach gebaut.

Das ist deine Tabelle von oben.

ISIN	Name	Datum	Im Depot seit	Anzahl	Noch vorhanden ST	Umsätze	Anmerkung
DE000A1ML7J1	Vonovia	25.11.2020		9	9	Ausblenden
DE000A1ML7J1	1	25.11.2020		9	9
DE0008490962	DWS Fond	20.05.2020		20	20	Ausblenden
DE0008490962	7	20.06.2024		-5
DE0008490962	6	20.07.2020		-5
DE0008490962	5	20.06.2020	20.06.2020	-10
DE0008490962	4	20.05.2020	20.05.2020	10	10		hier sind noch alle 10 übrig, da noch nichts davon verkauft wurde
DE0008490962	3	18.05.2020	18.05.2020	30	20		hier sind 20 übrig, da am 20.06.2020, 5 und am 20.07.2020 nochmal 5 verkauft
DE0008490962	2	10.05.2020	10.05.2024	10	5		hier sind 5 übrig, da am 20.06.2024 verkauft
DE0008490962	1	20.04.2020	20.04.2020	5	0		hier sind 0 übrig, da am 20.06.2020 verkauft

Angenommen, ich will die Zahlen in der Spalte "Anzahl" zusammenaddieren, aber nur wenn in Spalte "A" die gleiche ISIN und bei Name der gleiche Inhalt steht (ja, das macht keinen Sinn, ist aber nur ein Beispiel).

Bei mir läuft das immer so ab:

Gedanklich	Excel
Summiere mir die Spalte mit den Zahlen	=Summewenns([Spalte mit Anzahl];
wenn die ISIN	[Spalte mit ISIN];
mit der aus der gleichen Zeile übereinstimmt	[Zelle mit ISIN in der Zeile, wo die Formel gerade geschrieben wird];
und wenn der Name	[Spalte mit Name];
mit dem aus der gleichen Zeile übereinstimmt	[Zelle mit Name in der Zeile, wo die Formel gerade geschrieben wird])

"in der Zeile, wo die Formel gerade geschrieben wird" bedeutet, wenn ich die Formel bspw. in Zelle C13 schreibe, dann müssen die Zellkoordinaten auch immer auf 13 enden (A13, B13, etc.).

Bei mehreren Bedingungen wiederhole ich die letzten zwei Schritte solange, bis alle Bedingungen drin sind.
Wichtig ist, dass alle Bereiche (die Spalten, nicht die Zellen!) gleich groß sind. Excel weiß nicht, was es machen soll, wenn zB in der zu summierenden Spalte 18 Zellen und in der Kriterienspalte 19 Zellen sind. Dann kommt immer #Wert! raus.

 

[Christian98]

@Krik OK, ich werde mich nochmal hinsetzen vielleicht fällt mir noch was ein.
Wie du aus meinem hochgeladenen Excel entnehmen kannst habe ich bereits versucht das ganze mit Summewenns zu lösen, bisher jedoch ohne Erfolg.

 

[Christian98]

@Krik Ich glaube nun konnte ich das Ganze endlich lösen! 😊

Ich musste es leider verzipen, da ich das Dokument als .xlsb speichern musste, da ich ein Lösung mit Hilfsspalten und eine ohne Hilfsspalten gemacht habe. Bei der ohne Hilfsspalten wäre die Formel zu lang für ein normales Excel Format gewesen.

P.S. Das Dokument enthält ein Makro zum Ein- und Ausblenden der Transaktionen, das ist kein Virus!