Mein privates Datawarehouse ‐Sparen mit MySQL

Die wunderbare Welt von Isotopp

Sonntag, 23. Juli 2006Mein privates Datawarehouse ­ Sparen mit MySQLMeine Sparkasse exportiert mir die Kontoauszüge aus Wunschauch als CSV. Die Dateien sehen so aus:

"Auftragskonto";"Buchungstag";"Valutadatum";"Buchungstext";"Verwendungszweck";"Begünstigter/Zahlungspflichtiger";"Kontonummer";"BLZ";"Betrag";"Währung";"Info""08154711";"30.12";"30.12.05";"LASTSCHRIFT";"DRP 08154711 040441777 INKL. 16% UST 5.38 EUR";"STRATO MEDIEN AG";"040441777";"10050000";"-39,00";"EUR";"Umsatz gebucht"

Weil ich wissen will, wofür ich mein Geld ausgebe, lade ich diese Daten in einMySQL.

Das geht so:Zunächst einmal muß ich mir eine Tabelle definieren, in die ich den Load vornehmenkann. Diese Tabelle hat Felder, die in erster Linie dazu geschaffen sind, die Datenaufnehmen zu können. Wir müssen die Daten noch bereinigen, sodaß es sich nochnicht um die endgültigen Felder oder Typen handelt.

-- load datawarnings;DROP TABLE IF EXISTS buchungen;CREATE TABLE buchungen ( auftragskonto char(8) NOT NULL, buchungstag_text char(10) NOT NULL, valutatag_text char(10) NOT NULL, buchungstext varchar(50) NOT NULL, verwendungszweck varchar(180) NOT NULL, gegenkonto_name varchar(100) NOT NULL, gegenkonto_nummer char(20) NOT NULL, gegenkonto_blz char(8) NOT NULL, betrag_text char(12) NOT NULL, waehrung char(3) NOT NULL, info varchar(255) NOT NULL, unique index ( buchungstag_text , buchungstext , verwendungszweck , gegenkonto_nummer , gegenkonto_blz , betrag_text)) ENGINE=MyISAM DEFAULT CHARSET=utf8;delete from buchungen;

Es fällt auf, daß in den Kontoauszügen keine eindeutigen Transaktionsnummernsind, sodaß ich keinen Primärschlüssel definieren kann. Mit dem Unique Index, denich dort definiere, versuche ich doppelte Datensätze zu entdecken. Dies kann jedoch

falsche positive Treffer liefern.

In diese Tabelle kann ich nun nacheinander die einzelnen CSV mit den

Kontoauszügen hinein laden:

load data infile

'/home/kris/Documents/banking/umsatz-22758031-29122004.csv'

into table buchungen

fields terminated by ";"

optionally enclosed by '"'

ignore 1 lines;

load data infile

'/home/kris/Documents/banking/umsatz-22758031-24012005.csv'

into table buchungen

fields terminated by ";"

optionally enclosed by '"'

ignore 1 lines;

...

Wir müssen diese Daten nun in brauchbare Datensätze umwandeln. Dazu wird erst

einmal eine Zieltabelle erzeugt und diese mit einem Primärschlüssel versehen.

-- prepare conversion stage

DROP TABLE IF EXISTS b;

create table b like buchungen;

alter table b add column id integer unsigned not null first;

alter table b add primary key (id);

alter table b change column id

id integer unsigned not null auto_increment;

Jetzt können die Daten umgeladen werden und danach die Felder bereinigt werden:

Das Betrag­Feld muß von "xxx.xxx,yy"­Syntax auf "xxxxxx.yy" umgestellt werden und

die Datumsfelder valutatag_text und buchungstag_text müssen in ISO­Syntax

umgestellt werden. Dabei muß das fehlende Jahr beim buchungstag_text aus dem

valutatag ergänzt werden.

-- load data into conversion stage

insert into b select NULL, buchungen.* from buchungen;

-- adapt betrag

update b set betrag_text = replace(betrag_text, ".", "");

update b set betrag_text = replace(betrag_text, ",", ".");

alter table b change column betrag_text

betrag decimal(12,2) not null;

-- adapt valutatag

update b set valutatag_text =

concat("20",

substring(valutatag_text, 7, 2),

"-",

substring(valutatag_text, 4, 2),

"-",

substring(valutatag_text, 1,2));

alter table b change column valutatag_text

valutatag date not null;

-- adapt buchungstag

update b set buchungstag_text =

concat(year(valutatag),

"-",

substring(buchungstag_text, 4,2),

"-",

substring(buchungstag_text, 1,2));

alter table b change column buchungstag_text

buchungstag date not null;

-- drop info

alter table b drop column info;

Ich will nun außerdem eine Gruppierung meiner Ausgaben vornehmen. Dazu führeich eine Spalte "gruppe "ein. Mit Hilfe einer weiteren Tabelle "wichtige_geldsenken"matche ich dann den gegenkonto_name und fülle die Gruppe:

-- add gruppealter table b add column gruppe varchar(20) not null;

Und jetzt die wichtige_geldsenken Tabelle:

DROP TABLE IF EXISTS `wichtige_geldsenken`;CREATE TABLE `wichtige_geldsenken` ( `id` int(10) unsigned NOT NULL auto_increment, `pattern` varchar(100) NOT NULL, `gruppe` varchar(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOCK TABLES `wichtige_geldsenken` WRITE;INSERT INTO `wichtige_geldsenken` VALUES (77,'sparkasse','Konto und Karte');INSERT INTO `wichtige_geldsenken` VALUES (78,'ga','Geldautomat Inland');INSERT INTO `wichtige_geldsenken` VALUES (79,'qsc','Internet');INSERT INTO `wichtige_geldsenken` VALUES (80,'linux new media','Zeitungen');INSERT INTO `wichtige_geldsenken` VALUES (81,'premiere','Fernsehen');INSERT INTO `wichtige_geldsenken` VALUES (82,'walmart','Einkauf');INSERT INTO `wichtige_geldsenken` VALUES (83,'kabel bw','Fernsehen');INSERT INTO `wichtige_geldsenken` VALUES (84,'gez','Fernsehen');INSERT INTO `wichtige_geldsenken` VALUES (85,'t­mobile','Telefon');INSERT INTO `wichtige_geldsenken` VALUES (86,'finanzkasse','Steuern undStrafen');INSERT INTO `wichtige_geldsenken` VALUES (87,'domainfactory','Internet');INSERT INTO `wichtige_geldsenken` VALUES (88,'nagel ue','Kleidung');INSERT INTO `wichtige_geldsenken` VALUES (89,'mobilcom','Telefon');INSERT INTO `wichtige_geldsenken` VALUES (90,'domainfactory','Internet');INSERT INTO `wichtige_geldsenken` VALUES (91,'strato','Internet');INSERT INTO `wichtige_geldsenken` VALUES (92,'stadtwerke','Gas WasserScheisse');INSERT INTO `wichtige_geldsenken` VALUES (93,'deutsche bahn','Bahn');INSERT INTO `wichtige_geldsenken` VALUES (94,'debeka','Versicherung');INSERT INTO `wichtige_geldsenken` VALUES (95,'ec­ga','Geldautomat Ausland');INSERT INTO `wichtige_geldsenken` VALUES (96,'scheck in','Einkauf');INSERT INTO `wichtige_geldsenken` VALUES (97,'mastercard','Kreditkarte');INSERT INTO `wichtige_geldsenken` VALUES (98,'dr.','Miete');INSERT INTO `wichtige_geldsenken` VALUES (99,'a.t.u','Auto');INSERT INTO `wichtige_geldsenken` VALUES (100,'ungeheuer','Auto');INSERT INTO `wichtige_geldsenken` VALUES (101,'agip','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (102,'aral','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (103,'avia','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (104,'bab','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (105,'citti','Einkauf');INSERT INTO `wichtige_geldsenken` VALUES (106,'efa','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (107,'esso','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (108,'expedia','Reisen');INSERT INTO `wichtige_geldsenken` VALUES (109,'fantasy','RPG');INSERT INTO `wichtige_geldsenken` VALUES (110,'gravis','Toys und Gadgets');INSERT INTO `wichtige_geldsenken` VALUES (111,'foto','Toys und Gadgets');

INSERT INTO `wichtige_geldsenken` VALUES (112,'heinrich','Kleidung');INSERT INTO `wichtige_geldsenken` VALUES (113,'hem','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (114,'hotel','Reisen');INSERT INTO `wichtige_geldsenken` VALUES (115,'jet­tank','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (116,'karstadt','Einkauf');INSERT INTO `wichtige_geldsenken` VALUES (117,'kassen­','Steuern und Strafen');INSERT INTO `wichtige_geldsenken` VALUES (118,'leichtsinn','Toys und Gadgets');INSERT INTO `wichtige_geldsenken` VALUES (119,'media markt','Toys undGadgets');INSERT INTO `wichtige_geldsenken` VALUES (120,'mios','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (121,'plaza','Einkauf');INSERT INTO `wichtige_geldsenken` VALUES(122,'rundfunkgebuehren','Fernsehen');INSERT INTO `wichtige_geldsenken` VALUES (123,'saturn','Toys und Gadgets');INSERT INTO `wichtige_geldsenken` VALUES (124,'sb tank','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (125,'segelkiste','Kleidung');INSERT INTO `wichtige_geldsenken` VALUES (126,'total/','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (127,'trx','Reisen');INSERT INTO `wichtige_geldsenken` VALUES (128,'tst. bensheim','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (129,'vobis','Toys und Gadgets');INSERT INTO `wichtige_geldsenken` VALUES (130,'willenberg','Toys und Gadgets');INSERT INTO `wichtige_geldsenken` VALUES (131,'shell','Sprit');INSERT INTO `wichtige_geldsenken` VALUES (132,'spreadshirt','Kleidung');INSERT INTO `wichtige_geldsenken` VALUES (133,'armin meier','Kleidung');INSERT INTO `wichtige_geldsenken` VALUES (134,'itzehoer','Versicherung');INSERT INTO `wichtige_geldsenken` VALUES (135,'ec­pos','Geldautomat Ausland');INSERT INTO `wichtige_geldsenken` VALUES (136,'euf­ga','Geldautomat Ausland');INSERT INTO `wichtige_geldsenken` VALUES (137,'dell','Toys und Gadgets');INSERT INTO `wichtige_geldsenken` VALUES (138,'yvonne','RPG');UNLOCK TABLES;

Mit Hilfe dieser Mappingtabelle und der folgenden Query kann ich jetzt das Feldgruppe in b sinnvoll belegen:

update b set gruppe = ( select gruppe from wichtige_geldsenken as w where b.gegenkonto_name like concat(w.pattern, "%") order by length(pattern) desc limit 1) where b.betrag < 0;

Wenn meine pattern­Liste vollständig ist, ist jetzt das Feld gruppe bei allen Ausgabenkorrekt belegt.

Ich kann nun Fragen stellen:

select gegenkonto_name, count(gegenkonto_name) as eingaenge from b where betrag>=0group by gegenkonto_nameorder by eingaenge desc;+--------------------------------------------------------+-----------+| gegenkonto_name | eingaenge |+--------------------------------------------------------+-----------+| WEB.DE AG AMALIENBADSTR. 41 | 12 || MYSQL GMBH | 7 || MYSQL GMBH SCHLOSSERSTR. 4 72622 NUERTINGEN | 3 || COOP SCHLESWIG-HOLSTEIN EG BENZSTR. 10 | 2 |+--------------------------------------------------------+-----------+

select gegenkonto_name,

select gegenkonto_name, count(gegenkonto_name) as abgaenge from b where betrag<0group by gegenkonto_nameorder by abgaenge desc;+---------------------------------------------------------+----------+| gegenkonto_name | abgaenge |+---------------------------------------------------------+----------+| SCHECK IN CENTER KA DURLACH | 36 || MOBILCOM COMMUNICATIONSTECH | 22 || STRATO MEDIEN AG | 22 || VERMIETER | 19 || T-MOBILE DEUTSCHLAND GMBH | 18 || DEUTSCHE BAHN KARLSRUHE HB | 17 || KABEL BW GMBH & CO. KG | 17 || QSC AG | 17 || STADTWERKE KARLSRUHE | 17 |...

Mit den Gruppen kann ich nun auch sehen, wo das Geld hin gegangen ist:

select gruppe, count(betrag) as abgaenge, sum(betrag) as total from b where betrag<0group by gruppeorder by total;

wird mir sagen, wofür ich mein Geld ausgebe.Geschrieben von Kristian Köhntopp in MySQL um 11:50 | Kommentare (10) | Trackbacks (2)

TrackbacksTrackback­URL für diesen Eintrag

GeIsotopptHeute Abend hatte ich das Vergnügen, Kris ein wenig durch die Stuttgarter Innenstadtzu lotsen. Zum Standardprogramm gehörten: Döner bei Ützel Brützel, flanieren überdie Theodor­Heuss­Strasse, ein Besuch auf dem original Hamburger Fischmarkt undeine runWeblog: :: CrUisErs BLOG ::Aufgenommen: Jul 27, 21:30

DIY Quicken with MySQLKris explains how his own private data warehouse for personal finance managementworks. Only in german, but the code is easy enough to grok the idea. ...Weblog: www.gadgetguy.de ­ The GadgetGuyAufgenommen: Jul 31, 19:26

KommentareAnsicht der Kommentare: (Linear | Verschachtelt)

Sehr schöner Artikel. Danke dafür. :) Ich glaub, das werde ich mal nachbasteln.... ;)#1 Knutsen (Link) am 23.07.2006 12:16

Danke Kris, das verfeinert meinen simplen CSV in MySQL doch um einiges ... :­)#2 Eimann am 23.07.2006 13:40

Danke für diesen gewohnt lehrreichen Artikel von dir :)

Das muss ich bei Gelegenheit mal nachbauen.#3 ff (Link) am 23.07.2006 18:00

Wunderbar das Tutorial, bin zwar vertraut mit den Basics, aber die Daten so hin undher zuschieben, dass bedarf einiger Routine.Und was man nicht alles damit machen kann.Errechne die montalichen Durchschnittsausgaben per Gruppe oder komplett...#4 superstition am 24.07.2006 10:33

Wie kriegt man seine Sparkasse dazu, einem so einen individuellen Serviceanzubieten? Und auf welchem Weg bekommt man die CSVs? verschärftverblüfftsei#5 Malte Steckmeister (Link) am 28.07.2006 20:58

Das ist das normale Onlinebanking der Sparkasse ­ http://www.sparkasse­ka.de/,dann Internet Banking, dann einloggen und da im normalen PIN/TAN­Banking kannman die Auszüge als HTML oder CSV runterladen, ganz normal.#5.1 Isotopp (Link) am 29.07.2006 06:33

Tja, schön wärs. Ich kann hier bei meiner (eigentlich nicht ganz kleinen)Sparkasse Holstein nur Kontoauszüge als PDF runterladen, wo ich "immerhin"zwischen DIN A4 und C6 wählen kann...#5.1.1 Malte Steckmeister (Link) am 29.07.2006 20:00

Kleiner Tipp: Dann schau mal im Onlinebanking nicht bei "Kontoauszug",sondern bei "Umsätze". Dort findest Du über der Umsatztabelle den Button"Umsätze speichern". Auf der nächsten Seite kannst Du dann zwischen CSVund MT940 wählen...#5.1.1.1 Jens Bergmann am 01.01.2007 22:53

Herrje, dagegen ist das CSV, das man von der Sparda West kommt mal richtigScheiße. Kein Gegenkonto, da muß man mit den Patterns richtig aufpassen, wennman gegen den Verwendungszweck matched. Dann kommen aber interessante Dingebei raus.#6 Sebastian (Link) am 29.07.2006 16:22

Vielen Dank dafür. Die Datumsumrechnung kann ich gerade akut gebrauchen, ich warschon dabei die Daten vorm Load merhfach durch awk zu jagen.

Kennst Du weiterführende Lektüre, die anhand solcher netten Beispiele mein MySQL­Wissen vertiefen kann?#7 Sven Thomsen (Link) am 26.08.2008 18:43

Kommentar schreibenName

E­Mail

Homepage

Antwort zu

KommentarUmschließende Sterne heben ein Wort hervor (*wort*), per _wort_ kann

ein Wort unterstrichen werden.

Um maschinelle und automatische Übertragung von Spamkommentaren

zu verhindern, bitte die Zeichenfolge im dargestellten Bild in der

Eingabemaske eintragen. Nur wenn die Zeichenfolge richtig eingegeben

wurde, kann der Kommentar angenommen werden. Bitte beachten Sie,

dass Ihr Browser Cookies unterstützen muss, um dieses Verfahren

anzuwenden.

Hier die Zeichenfolge der Spamschutz­Grafik eintragen:

BBCode­Formatierung erlaubt Daten merken?