Konvertiere Sparkassen Girokonto CSV-CAMT-Format und Kreditkarten CSV-Format in Homebank kompatibles Format

kreditkarte-homebank.py

#!/usr/bin/python3
#
# The MIT License (MIT)
# Copyright (c) 2019 David Jany
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
# Konvertiere Sparkassen CSV-Format einer Kreditkarte in Homebank kompatibles Format
# Pythonskript mit Pythonmodul agate
#
#                                 	   ID
### CAMT csv header                    v
# 															
# 0  Umsatz getätigt von                       
# 1  Belegdatum                         
# 2  Buchungsdatum                      1 
# 3  Originalbetrag                      
# 4  Originalwährung                  	
# 5  Umrechnungskurs                      
# 6  Buchungsbetrag                     2
# 7  Buchungswährung         
# 8  Transaktionsbeschreibung           3         
# 9  Transaktionsbeschreibung Zusatz    4    
# 10 Buchungsreferenz
# 11 Gebührenschlüssel 					 
# 12 Länderkennzeichen                   
# 13 BAR-Entgelt+Buchungsreferenz        
# 14 AEE+Buchungsreferenz                
# 15 Abrechnungskennzeichen                            
#
### homebank header
# Feld		ID	Format/Inhalt
# date		1	format must be DD-MM-YY
# paymode		from 0=none to 10=FI fee
# info			a string
# payee		3	a payee name
# memo		4	a string
# amount	2	a number with a '.' or ',' as decimal separator, ex: -24.12 or 36,75
# category		a full category name (category, or category:subcategory)
# tags			tags separated by space, tag is mandatory since v4.5
# 
#
### imports
import argparse
import os
import sys
import datetime
import agate

### parse
parser = argparse.ArgumentParser()
parser.add_argument("csv", help="csv from Sparkasse Kreditkarte")
parser.add_argument("--debug", help="Print debug info", action='store_true')
args = parser.parse_args()
incsv = args.csv

def convert_date(date):
	return datetime.datetime.strptime(date, '%Y-%m-%d').strftime('%d/%m/%Y')

# If you need to have your own implementation just use this:
# Usage: save_csv(homebankaccount, 'filename.csv', delimiter=';')
def save_csv(table, path, **kwargs):
    from agate import csv

    if 'lineterminator' not in kwargs:
        kwargs['lineterminator'] = '\n'

    close = True
    f = None

    try:
        if hasattr(path, 'write'):
            f = path
            close = False
        else:
            dirpath = os.path.dirname(path)

            if dirpath and not os.path.exists(dirpath):
                os.makedirs(dirpath)

            f = open(path, 'w')

        writer = csv.writer(f, **kwargs)

        csv_funcs = [c.csvify for c in table._column_types]

        for row in table._rows:
            writer.writerow(tuple(csv_funcs[i](d) for i, d in enumerate(row)))
    finally:
        if close and f is not None:
            f.close()

# Parse the SPK-CSV (Enc=ISO-8859-15, Delimeter=;Dateformat=24.12.2019;locale=de_DE)
tester = agate.TypeTester(force={
	'Buchungsdatum': agate.Date("%d.%m.%y"),
	'Buchungsbetrag': agate.Number(locale='de_DE')
})
account = agate.Table.from_csv(incsv, encoding='iso-8859-15', delimiter=';', column_types=tester)
source = account.select(['Buchungsdatum', 'Buchungsbetrag', 'Transaktionsbeschreibung', 'Transaktionsbeschreibung Zusatz'])

# DEBUG: Original
if (args.debug):
	print(source)
	source.print_table(max_rows = 100)

# Only select relevant fields
basic = source # .select(['Buchungstag', 'Buchungstext', 'Beguenstigter/Zahlungspflichtiger', 'Verwendungszweck', 'payee', 'Betrag'])

# DEBUG: Original
if (args.debug):
	print(basic)
	basic.print_table(max_rows = 100)

# paymode will always be 1 (creditcard)
homebankaccount = basic.compute([
	('paymode',
	agate.Formula(agate.Number(), lambda r: 1)
	)
], replace=False)

# Convert date format
homebankaccount = homebankaccount.compute([
	('Buchungsdatum',
	agate.Formula(agate.Text(),
	lambda r: r['Buchungsdatum'].strftime('%d/%m/%Y')
	))
], replace=True)

# memo will alway be empty
homebankaccount = homebankaccount.compute([
	('memo',
	agate.Formula(agate.Text(), lambda r: '')
	)
], replace=True)

# Sort
homebankaccount = homebankaccount.select(['Buchungsdatum', 'paymode', 'Transaktionsbeschreibung Zusatz', 'Transaktionsbeschreibung', 'memo', 'Buchungsbetrag'])
# Rename
homebankaccount = homebankaccount.rename(column_names = ['date','paymode','info','payee','memo','amount'])

# Add empty columns category and tags
homebankaccount = homebankaccount.compute([
	('category',
	agate.Formula(agate.Text(),
	lambda r: ''
	))
], replace=True)
homebankaccount = homebankaccount.compute([
	('tags',
	agate.Formula(agate.Text(),
	lambda r: ''
	))
], replace=True)

# DEBUG: Structure to be saved
if (args.debug):
	print(homebankaccount)
	homebankaccount.print_table(max_rows = 100)

# Save to new CSV
homebankaccount.to_csv('kreditkarte_import.csv', delimiter=';')

Sparkasse2Homebank.md

Info

Homebank (http://homebank.free.fr) versteht nur seine eigenen CSV-Dateien. Hier sind zwei Konverter für Sparkassenkonten:

• Girokonto -> spk_csv-camt2homebank.py
• Kreditkarte -> kreditkarte-homebank.py

Es werden die Dateien kreditkarte_import.csv bzw. girokonto_import.csv erstellt bzw. überschrieben.

Installation

Die Installation nutzt pipenv als Pythonumgebung.

$ cd <Skriptordner>
$ pipenv install --three csvkit

Verwendung

Der Parameter --debug kann u.U. bei Verarbeitungsfehlern schnelle Hilfe leisten...

$ cd <Skriptordner>
$ pipenv shell
$ spk_csv-camt2homebank.py 20190318-Kontonummer-umsatz.CSV --debug
$ kreditkarte-homebank.py ...CSV --debug

Lizenz

The MIT License (MIT) Copyright (c) 2019 David Jany Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

spk_csv-camt2homebank.py

#!/usr/bin/python3
#
# The MIT License (MIT)
# Copyright (c) 2019 David Jany
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
# The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
#
# Konvertiere Sparkassen CSV-CAMT-Format eines Girokontos in Homebank kompatibles Format
# Pythonskript mit Pythonmodul agate
#
#                                 	   ID
### CAMT csv header                    v
# 0  Auftragskonto                       
# 1  Buchungstag                         
# 2  Valutadatum                       1 
# 3  Buchungstext                      2 
# 4  Verwendungszweck                  3 
# 5  Glaeubiger ID                      
# 6  Mandatsreferenz                    
# 7  Kundenreferenz (End-to-End)         
# 8  Sammlerreferenz                    
# 9  Lastschrift Ursprungsbetrag        
# 10 Auslagenersatz Ruecklastschrift
# 11 Beguenstigter/Zahlungspflichtiger 4 
# 12 Kontonummer/IBAN                  5 
# 13 BIC (SWIFT-Code)                  6 
# 14 Betrag                            7 
# 15 Waehrung                            
# 16 Info                              8 
#
### homebank header
# Feld		ID	Format/Inhalt
# date		1	format must be DD-MM-YY
# paymode	2	from 0=none to 10=FI fee
# info		3	a string
# payee		5,6	a payee name
# memo		4	a string
# amount	7	a number with a '.' or ',' as decimal separator, ex: -24.12 or 36,75
# category		a full category name (category, or category:subcategory)
# tags			tags separated by space, tag is mandatory since v4.5
# 
### classification                                  mbs_id
# date		= Valutadatum                           2
# paymode	= Buchungstext (as int)                 3
# info		= Buchungstext (as string)
# payee		= Beguenstigter/Zahlungspflichtiger
# memo		= Verwendungszweck                      4
# amount	= Betrag
# category	= empty
# tags		= empty
#
### paymode
# 0 = none			-
# 1 = kreditkarte		na
# 2 = schecks			EIGENE KREDITKARTENABRECHN., GUTSCHR. UEBERWEISUNG, RECHNUNG
# 3 = bargeld 			BARGELDAUSZAHLUNG
# 4 = ueberweisung		ONLINE-UEBERWEISUNG, ONLINE-UEBERWEISUNG TERM.,
# 5 = zwischen konten		!INTERNAL!
# 6 = einzugsermaechtigung	ONLINE-EINZUGSAUFTRAG
# 7 = dauerauftrag		DAUERAUFTRAG
# 8 = kartenzahlung		KARTENZAHLUNG, "SONSTIGER EINZUG", GUTSCHEINKAUF
# 9 = einzahlung		GUTSCHRIFT,EINZAHLUNG,LOHN  GEHALT, "SEPA UEBERTRAG HABEN", "BAR"(?)
# 10= FI Abgabe			ABSCHLUSS
# 11= lastschrift		FOLGELASTSCHRIFT, LASTSCHRIFT, SEPA-ELV-LASTSCHRIFT, ERSTLASTSCHRIFT, EINMAL LASTSCHRIFT

### imports
import argparse
import os
import sys
import datetime
import agate

### parse
parser = argparse.ArgumentParser()
parser.add_argument("csv", help="csv from mbs")
parser.add_argument("--debug", help="Print debug info", action='store_true')
args = parser.parse_args()
incsv = args.csv

def convert_date(date):
	return datetime.datetime.strptime(date, '%Y-%m-%d').strftime('%d/%m/%Y')

# If you need to have your own implementation just use this:
# Usage: save_csv(homebankaccount, 'filename.csv', delimiter=';')
def save_csv(table, path, **kwargs):
    from agate import csv

    if 'lineterminator' not in kwargs:
        kwargs['lineterminator'] = '\n'

    close = True
    f = None

    try:
        if hasattr(path, 'write'):
            f = path
            close = False
        else:
            dirpath = os.path.dirname(path)

            if dirpath and not os.path.exists(dirpath):
                os.makedirs(dirpath)

            f = open(path, 'w')

        writer = csv.writer(f, **kwargs)

        csv_funcs = [c.csvify for c in table._column_types]

        for row in table._rows:
            writer.writerow(tuple(csv_funcs[i](d) for i, d in enumerate(row)))
    finally:
        if close and f is not None:
            f.close()

# Lookup dict according to SPKS and Homebank payment definition
# Homebank: http://homebank.free.fr/help/index.html
# SPKS: No list available
paymode_map = {
	"ABSCHLUSS": 0,
	"EIGENE KREDITKARTENABRECHN.": 1,
	"GUTSCHR. UEBERWEISUNG": 1,
	"RECHNUNG": 1,
	"GELDAUTOMAT": 3,
 	"BARGELDAUSZAHLUNG": 3,
	"ONLINE-UEBERWEISUNG": 4,
	"SEPA UEBERTRAG SOLL": 4,
	"ONLINE-UEBERWEISUNG TERM.": 4,
	"ONLINE-EINZUGSAUFTRAG": 6,
	"DAUERAUFTRAG": 7,
	"KARTENZAHLUNG": 8,
	"SONSTIGER EINZUG": 8,
	"GUTSCHEINKAUF": 8,
	"GUTSCHRIFT": 9,
	"EINZAHLUNG": 9,
	"LOHN  GEHALT": 9,
	"SEPA UEBERTRAG HABEN": 9,
	"BAR": 9,
	"ABSCHLUSS":10,
	"ENTGELTABSCHLUSS":10,
	"FOLGELASTSCHRIFT":11,
	"LASTSCHRIFT":11,
	"SEPA-ELV-LASTSCHRIFT":11,
	"ERSTLASTSCHRIFT":11,
	"EINMAL LASTSCHRIFT":11,
}

def lookup(value):
	if value in paymode_map:
		return paymode_map[value]
	else:
		print("A new Buchungstext was found. Please add the corresponding Paymode to paymode_map.")
		exit()

# Parse the SPK-CSV (Enc=ISO-8859-15, Delimeter=;Dateformat=24.12.2019;locale=de_DE)
tester = agate.TypeTester(force={
	'Buchungstag': agate.Date("%d.%m.%y"),
	#'Buchungstag': agate.Date("%m/%d/%y"),
	'Betrag': agate.Number(locale='de_DE')
})
account = agate.Table.from_csv(incsv, encoding='iso-8859-15', delimiter=';', column_types=tester)
source = account.select(['Buchungstag', 'Buchungstext', 'Info', 'Beguenstigter/Zahlungspflichtiger', 'Verwendungszweck', 'Betrag', 'Kontonummer/IBAN', 'BIC (SWIFT-Code)'])
# date;paymode;info;payee;memo;amount;category;tags

# Generiere Payee-Feld bestehend aus "Konto {} Bank {}".format(Kontonummer, BLZ)
source = source.compute([
	('payee',
	agate.Formula(agate.Text(),
	#lambda r: convert_date(r['Buchungstag'])
	lambda r: "Konto {} Bank {}".format(r['Kontonummer/IBAN'],r['BIC (SWIFT-Code)'])
	))
], replace=False)

# DEBUG: Original
if (args.debug):
	print(source)
	source.print_table(max_rows = 100)

# Only get booked transactions
source = source.where(lambda row: row['Info'] != 'Umsatz vorgemerkt'  )

# Only select relevant fields
basic = source.select(['Buchungstag', 'Buchungstext', 'Beguenstigter/Zahlungspflichtiger', 'Verwendungszweck', 'payee', 'Betrag'])

# DEBUG: Original
if (args.debug):
	print(basic)
	basic.print_table(max_rows = 100)

# Lookup the corresponding payment type using a computation on 'Buchungstext' and the function lookup
homebankaccount = basic.compute([
	('Buchungstext',
	agate.Formula(agate.Text(),
	lambda r: lookup(r['Buchungstext'])
	))
], replace=True)

# Convert date format
homebankaccount = homebankaccount.compute([
	('Buchungstag',
	agate.Formula(agate.Text(),
	#lambda r: convert_date(r['Buchungstag'])
	lambda r: r['Buchungstag'].strftime('%d/%m/%Y')
	))
], replace=True)

# Rename
homebankaccount = homebankaccount.rename(column_names = ['date','paymode','info','payee','memo','amount'])

homebankaccount = homebankaccount.compute([
	('category',
	agate.Formula(agate.Text(),
	lambda r: ''
	))
], replace=True)

homebankaccount = homebankaccount.compute([
	('tags',
	agate.Formula(agate.Text(),
	lambda r: ''
	))
], replace=True)

# DEBUG: Structure to be saved
if (args.debug):
	print(homebankaccount)
	homebankaccount.print_table(max_rows = 100)

# Save to new CSV
homebankaccount.to_csv('girokonto_import.csv', delimiter=';')