Abfrage für Dividende pro Jahr erstellen

[Wonka]

Hallo,

ich kaufe seit ein paar Jahren Aktien im kleinen Stil und habe alle Käufe, Kaufpreis, Dividende,.. jetzt mal in eine Maria DB eingetragen und würde mir das gerne in Power BI Desktop auswerten.

Anbei das DB-Schema. Es geht um die Tabellen "kauf" und "dividende". Aus der Tabelle "aktie" joine ich mir am Ende noch den Namen, aber das ist erstmal egal. ISIN ist jeweils die ID, das ist die "Internationale Wertpapierkennung", die ich als ID verwende, da sie eindeutig ist.

Was will ich nun machen: Ich möchte eine View erstellen, in der ich mir pro Jahr für jede Aktiengesellschaft (also GROUP BY ISIN) und Jahr die Dividende ausrechne. Was es dabei zu beachten gilt ist natürlich, dass, wenn die Dividende z.B. im April 2020 ausgezahlt wird, und ich zu dem Zeitpunkt 20 Aktien dieser AG hatte, im August 2020 weitere 10 Aktien kaufe, dass dann nur 20 Aktien mit der Dividende multipliziert werden. Die 10 anderen Aktien hatte ich ja zum Zeitpunkt der Auszahlung noch nicht.

Eine Einschränkung, bei der ich auch nicht genau weiß, wie es ist, aber für mich auch bisher nie in Frage kam: wie lange ich eine Aktie vor der Hauptversammlung besitzen muss, damit ich dafür Dividende kassiere. Als Annahme setze ich kaufdatum = dividenden_datum.

Folgendes Sql-Statement habe ich mir dafür gebaut:

SELECT

YEAR(a.dividenden_datum),
a.isin,
SUM(b.anzahl),
a.dividende_pro_aktie,
SUM(b.anzahl) * a.dividende_pro_aktie as dividende

FROM

dividende a

JOIN

kauf b

ON

a.isin = b.isin

and

YEAR(a.dividenden_datum) = YEAR(b.kaufdatum)

GROUP BY

YEAR(a.dividenden_datum),

a.isin

ORDER BY

YEAR(a.dividenden_datum),

a.isin

Das Statement funktioniert, liefert mir aber zu wenige Ergebnisse. Ich erwarte 34 Einträge, die mir über 4 Jahre Dividende eingebracht haben. Bekommen tue ich aber nur 8. Ich würde jetzt ein Ergebnis mit 34 Einträgen erwarten, bei denen ich für alle Aktien, die ich pro AG in einem Jahr hatte, die Dividende sehe.

Der nächste Schritt, der aber technisch schon nicht geht, was am Grouping liegt, ich aber nicht weiß, wie ich das beheben kann, ist die Einschränkung, dass nur die Aktien berücksichtigt werden sollen, die bereits bei der Dividenden-Auszahlung in dem Jahr der Dividende in meinem Besitz waren (fett markiert).

SELECT

YEAR(a.dividenden_datum),
a.isin,
SUM(b.anzahl),
a.dividende_pro_aktie,
SUM(b.anzahl) * a.dividende_pro_aktie as dividende

FROM

`dividende` a

JOIN

kauf b

ON

a.isin = b.isin

and

YEAR(a.`dividenden_datum`) = YEAR(b.kaufdatum)

and

a.dividende_datum >= b.kaufdatum

GROUP BY

YEAR(a.dividenden_datum),

a.isin

ORDER BY

YEAR(a.dividenden_datum),

a.isin

Ich weiß, ich könnte auch direkt die Beträge speichern, die ich pro Jahr in Aktien investiert und die Dividende. die ich kassiert habe. Das soll aber auch eine Übung sein, etwas tiefer in Sql einzusteigen. Außerdem habe ich k.A., für welche Auswertung ich dies vielleicht mal brauchen kann.

Wäre super, wenn ihr mir helfen und auch etwas erklären könntet, warum es so ist.

Vielen Dank und viele Grüße!

 

[akretschmer]

tl;dr

Dein Select ist syntaktisch falsch, es hat 3 nicht-aggregierte Spalten, wovon aber nur 2 gruppiert sind. Das ist logisch falsch - und MySQL ist hirnamputiert genug dies nicht zu erkennen - und liefert statt einem Fehler (was korrekt wäre) lieber ein zufälliges und falsches Ergebniss - wie Du korrekt beobachtest hast.

in kurz: MySQL ist Sondermüll.

 

[Wonka]

Danke dir. Am Group by kann es aber nicht liegen, denn wenn ich nur noch

YEAR(a.dividenden_datum),
a.isin

selektiere stimmt das Ergebnis immer noch nicht. Dann selektiere ich ja nur die Spalten, die ich auch gruppiere. SUM(b.anzahl) ist doch summiert, folglich hat das Statement doch nur a.dividende_pro_aktie als nicht aggregierte Spalte?! Was ich auch schon gemacht habe: Da ein AVG rum, da ja jede Aktie einer AG dieselbe Dividende bekommt passt das auch.

Ich denke, ich habe die Einschränkung auf "nur Aktien, die vor dem Dividendendatum gekauft wurden" gelöst. Hierfür habe ich statt eine Abfrage auf die dividende-Tabelle ein Subselect eingefügt, mit dem ich nur die Aktien hole, deren dividenden_datum >= kaufdatum ist.

SELECT a.dividenden_datum, year(dividenden_datum), a.isin, SUM(b.anzahl), AVG(dividende_pro_aktie) FROM

(SELECT d.* FROM dividende d JOIN kauf k ON d.isin = k.isin WHERE d.dividenden_datum >= k.kaufdatum) a

JOIN kauf b ON a.isin = b.isin AND YEAR(a.dividenden_datum) = YEAR(b.kaufdatum) GROUP BY YEAR(dividenden_datum), a.isin

Das liefert aber auch falsche Ergebnisse. Ich verstehe es nicht.

 

[dabadepdu]

Ich verstehe es nicht.

Ja, das ist ziemlich problematisch, wenn die DB falsche Statements durchgehen lässt und sich Ergebnisse ausdenkt, statt Fehler zu werfen.
Wie soll man da auf die Idee kommen, dass etwas nicht stimmt?

Die Faustregel einer Aggregations Abfrage lautet:
Alle Spalten der Select Clause müssen mindestens entweder aggregiert sein oder gruppiert.

Schau Dir Deine Statements noch mal sorgfältig an und verstehe, was die Aggregation bedeutet und wofür group by bei einer Aggregation dient/dienen muss.

SELECT a.dividenden_datum, year(dividenden_datum), a.isin, SUM(b.anzahl), AVG(dividende_pro_aktie) FROM

Das sind 5 Spalten, die letzten beiden sind aggregiert, bleiben 3 für das Group by.

Oder mal als minimalistisches Beispiel:

SELECT AVG(dividende_pro_aktie) FROM dividende

Hier gibt es gar kein Group by, weil nur aggregiert wird und damit keins gebraucht wird. Entspricht 100% der Faustregel.

Das soll aber auch eine Übung sein, etwas tiefer in Sql einzusteigen.

Dafür hast Du Dir leider eine recht unpassende DB ausgesucht.


Die Frage, wie also eine Group By Abfrage aufgebaut sein muss, ist (hier wie fast immer) eine rein formale.
Ob Du Deine Mengen richtig zusammenschraubst, ob richtige Daten in den Tabellen stehen usw. ist was anderes. Halte das auseinander.

Die Vorstellung "Die DB würde einen Fehler melden, wenn das Group By falsch geschrieben wäre" wäre natürlich hilfreich dabei, vielleicht probierst Du es mal mit einem anderen System, solange Du noch lernst.

 

[Wonka]

Vielen Dank für die ausführliche Antwort. Das ist dann echt doof mit dem Fehler bzw. dass keiner kommt. Würde denn PostgreSql das ganze "richtig" handhaben, also mit Fehlermeldung und ist da etwas strikter? Ich habe die Maria DB auf meinem Server in einem Docker Container laufen. Auf derselben Instanz läuft auch WordPress. Wenn ich das samt der Daten auf PostgreSql umgezogen bekomme, dann könnte ich auch PostgreSql verwenden. Ansonsten würde ich nur ungern zwei Container mit DBs parallel laufen lassen. Aber das ist ein anderes Thema.

Erstmal wäre gut zu wissen, ob PostgreSql strikter ist. Kann man das beim DBMS irgedwie erkennen? Gibt es dafür ein Schlagwort oder sind das dann einfach Erfahrungswerte?

 

[BerndB]

Vielen Dank für die ausführliche Antwort. Das ist dann echt doof mit dem Fehler bzw. dass keiner kommt. Würde denn PostgreSql das ganze "richtig" handhaben, also mit Fehlermeldung und ist da etwas strikter? Ich habe die Maria DB auf meinem Server in einem Docker Container laufen. Auf derselben Instanz läuft auch WordPress. Wenn ich das samt der Daten auf PostgreSql umgezogen bekomme, dann könnte ich auch PostgreSql verwenden. Ansonsten würde ich nur ungern zwei Container mit DBs parallel laufen lassen. Aber das ist ein anderes Thema.

Erstmal wäre gut zu wissen, ob PostgreSql strikter ist. Kann man das beim DBMS irgedwie erkennen? Gibt es dafür ein Schlagwort oder sind das dann einfach Erfahrungswerte?

Hallo Wonka,

schau dir das ganze erst einmal genau bevor du einen Datenbankwechsel ins Auge fasst. Die Zahlen werden dann schon für sich sprechen und dir zeigen das MariaDb ncht die schlechteste Entscheidung war.
Es gibt bestimmt Gründe (mir fällt zwar gerade keiner ein) PostgreSql einzusetzen, .....

Gruß

Bernd

 

[akretschmer]

Erstmal wäre gut zu wissen, ob PostgreSql strikter ist.

Definitiv.

 

[Wonka]

Danke euch für eure Einschätzung. Ich werde mir solche "MariaDb/Mysql vs PostgreSql"-Vergleiche durchlesen. Wenn ich jetzt weiß, worauf ich achten muss, muss ich vielleicht auch nur etwas diszipliniert sein, mich daran zu halten. Wichtig ist auch - und das ist bei meiner Anzahl Aktien/Datensätzen ja locker machbar, dass ich mir in Excel die Ergebnisse zusammenrechne, die ich auch von dem Sql-Statement(s) erwarte. Dann sehe ich eventuell auch leichter, woran es hakt und muss nicht "8 bekommene vs. 34 erwartete Datensätze" vergleichen.

 

[BerndB]

Hallo Wonka,

du kannst mir auch gerne einen SQL-Dump schicken, dann schau ich eimal nach deinem Query.

Weiterhin, nur zur Info, schau dir einmal den SQL-Mode an. Diesen kannst du in der init setzen z.B auf STRICT:

SQL_MODE

Used to emulate behavior from other SQL servers.

mariadb.com

"Strict Mode​

A mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled is called strict mode.

With strict mode set (default from MariaDB 10.2.4), statements that modify tables (either transactional for STRICT_TRANS_TABLES or all for STRICT_ALL_TABLES) will fail, and an error will be returned instead. The IGNORE keyword can be used when strict mode is set to convert the error to a warning.

With strict mode not set (default in version <= MariaDB 10.2.3), MariaDB will automatically adjust invalid values, for example, truncating strings that are too long, or adjusting numeric values that are out of range, and produce a warning.

Statements that don't modify data will return a warning when adjusted regardless of mode."

Gruß Bernd

 

[Thallius]

MySQL 8 läßt den Fehler auch nicht mehr durch. Ich frage mich eh warum alle noch mit 5.x arbeiten. Das ist wahrscheinlich älter als Postgres selbst…

 

[Wonka]

Vielen Dank für eure Antworten.

Die Geschichte mit dem Strict Mode schaue ich mir an. Danke auch für das Angebot bzgl. des Snapshots. Ich würde es erst nochmal selbst probieren und falls ich nicht weiter komme, würde ich geren darauf zurückkommen.

Ich denke, es macht definitiv Sinn upzudaten. Bei dem fertigen Docker Image habe ich gar nicht auf die Version geachtet und wüsste auch jetzt aus dem Stehgreif nicht, welche ich genau verwende. Das werde ich nachschauen, wenn ich den Server wieder starte.

 

[Wonka]

Kurz zur Info, das hier sehe ich, wenn ich PhpMyAdmin auf dem Server öffne:

• Server-Typ: MariaDB
• Server-Verbindung: SSL wird nicht verwendet
• Server-Version: 10.4.11-MariaDB-1:10.4.11+maria~bionic-log - mariadb.org binary distribution
• Protokoll-Version: 10

Es scheint also schon eine ziemlich aktuelle Version zu sein?!

 

[Wonka]

Was mir beim Rumprobieren und auswerten der Ergebnisse gestern aufgefallen ist: Ich hatte noch einen Denkfehler. Ich darf ja nicht nur z.B. die Aktienkäuft von Firma X summieren im Jahr z.B. 2021 summieren und mit der Dividende multiplizieren, sondern ich muss alle Aktienkäufe kumulieren. Also, wenn ich 2019 angefangen haben zu kaufen, dann alle Käufe einer Aktie von 2019, für 2020 muss ich die Aktien von 2019 und 2020 aufsummieren, für 2021 die von 2019, 2020 und 2021. Wie das genau geht habe ich mir schon rausgesucht, aber noch nicht ausprobiert / in der Theorie verstanden.

Das würde ich erstmal probieren und dafür dann eine separate View erzeugen, die mir die Aktien, die ich pro Jahr einer AG hatte, abbildet.

 

[Wonka]

Ich hatte ja geschrieben, dass ich einen Denkfehler bisher in meinem "Konzept" hatte. Und zwar wollte ich ja immer nur die Aktien mit der Dividende mulitplizieren, deren Kaufdatum <= Dividendendatum, gruppiert und gejoined nach Jahr und ISIN. Das ist natürlich falsch, da ich auch für die Aktien Dividende bekomme, die ich in den Vorjahren gekauft habe. D.h. wenn ich 2019 4 Aktien von Firma A gekauft habe und in 2020 3, dann bekomme ich 2021 für 7 Aktien die Dividende.

Hierfür habe ich nun ein Statement gebaut, das grundsätzlich funktioniert, aber noch einen Fehler hat (Annahme, immer vor dem Dividendendatum gekauft):
- von Firma A habe ich 2019 4 Aktien gekauft -> 2019 4 Aktien im Besitz
- von Firma A habe ich 2020 3 Aktien gekauft -> 2020 7 Aktien im Besitz, mir werden aber 11 Aktien angezeigt (2 x 4 aus 2019+ 3)
- von Firma A habe ich 2021 keine Aktien gekauft -> 2021 7 Aktien im Besitz, mir werden aber 14 Aktien angezeigt (2 x 4 aus 2019 + 2 x 3 aus 2020)

select
t1.isin,
t1.kaufjahr as Dividendenjahr,
sum(t2.gekaufte_aktien) as Gesamtkauf
from
(
select a.isin,
YEAR(a.dividenden_datum) as kaufjahr,
sum(a.anzahl) as gekaufte_aktien
from (select dividende.isin, dividende.dividenden_datum, kauf.anzahl, dividende.dividende_pro_aktie from dividende left join kauf on kauf.isin = dividende.isin where kauf.kaufdatum <= dividende.dividenden_datum) a
group by YEAR(a.dividenden_datum), a.isin
order by YEAR(a.dividenden_datum), a.isin
) as t1
inner join
(
select a.isin,
YEAR(a.dividenden_datum) as kaufjahr,
sum(a.anzahl) as gekaufte_aktien
from (select dividende.isin, dividende.dividenden_datum, kauf.anzahl, dividende.dividende_pro_aktie from dividende left join kauf on kauf.isin = dividende.isin where kauf.kaufdatum <= dividende.dividenden_datum) a
group by YEAR(a.dividenden_datum), a.isin
order by YEAR(a.dividenden_datum), a.isin
) as t2
on t1.kaufjahr >= t2.kaufjahr and t1.isin = t2.isin

group by t1.kaufjahr, t1.isin
order by t1.kaufjahr, t1.isin

Ich habe einen abgespeckten Dump angehängt. Leider war das nur als ZIP möglich. Wenn ihr dem nicht traut, kann ich auch gerne nochmal alles als Text anhängen.

Wäre super, wenn ihr mir da weiterhelfen könntet.

Vielen Dank und viele Grüße!

 

[Georg V.]

Du kaufst die bereits gekauften Aktien noch einmal, wenn Du immer eine Summe über den gesamten Zeitraum bildest.