hamvocke / dkb2homebank Goto Github PK
View Code? Open in Web Editor NEWConvert DKB account CSV files to a Homebank compatible CSV format
License: MIT License
Convert DKB account CSV files to a Homebank compatible CSV format
License: MIT License
Hello hamvocke,
I'm having trouble to explain this.
Prior your change to the new format an incoming payment displayed the sender in the column 'payee' in HomeBank:
But now - after your change - the sender isn't displayed any more. Instead I see myself as the payee which isn't very informative for incoming payments as I can't see the real sender for this booking:
I hope you understand my problem...
Thanks for you efforts in this tool! I'm using it very often
Hello hamvocke,
it seems that you use the wrong date format for the visa credit card import.
I'm getting the error:
ValueError: time data '21.10.23' does not match format '%d.%m.%Y'
I changed the line 108 to the following code and it seems that the convert is successful then:
'date': convert_short_date(row["wertstellung"]),
First of all, thank you for sharing this script!
I had two issues. The DKB CSV I got uses two-digits for the year, so I used %y in convert_date. I also opened the file with 'utf-8-sig', otherwise the file's first line would start with the BOM character, causing identify_account_type to fail. I'm unfamiliar with python, so maybe that one was on me. Hope this is useful.
Hi Ham,
DKB has changed their format for the csv file. The old header looked like this:
"Kontonummer:";"DE00000000000000000000 / Alex";
"Von:";"01.01.2020";
"Bis:";"10.03.2020";
"Kontostand vom 10.03.2020:";"12,34 EUR";
"Buchungstag";"Wertstellung";"Buchungstext";"Auftraggeber / Begünstigter";"Verwendungszweck";"Kontonummer";"BLZ";"Betrag (EUR)";"Gläubiger-ID";"Mandatsreferenz";"Kundenreferenz";
And the new header:
"Kontonummer:";"DE33333333333333333333 / Alex";
"Von:";"01.01.2019";
"Bis:";"31.12.2019";
"Kontostand vom 31.12.2019:";"43,21 EUR";
"Freitextsuche (im Verwendungszweck und Empfängerdaten):";"";"im Verwendungszweck und Empfängerdaten";
"Betrag von:";"";"bis:";"";
"Buchungstag";"Wertstellung";"Buchungstext";"Auftraggeber / Begünstigter";"Verwendungszweck";"Kontonummer";"BLZ";"Betrag (EUR)";"Gläubiger-ID";"Mandatsreferenz";"Kundenreferenz";
I think the problem could be solved by skipping over the header part with reader.next()
until we reach actual input lines. Right now, the script crashes here:
/usr/bin/python3.8 /usr/share/pycharm/helpers/pydev/pydevd.py --multiproc --qt-support=auto --client 127.0.0.1 --port 44553 --file /home/alex/Development/dkb2homebank/dkb2homebank.py -c /home/alex/ownCloud/documents/Finanzen/CSV-Exports/cash-2019.csv
pydev debugger: process 4919 is connecting
Connected to pydev debugger (build 192.6817.19)
Traceback (most recent call last):
File "/home/alex/Development/dkb2homebank/dkb2homebank.py", line 60, in convert_DKB_cash
'date': convert_date(row["buchungstag"]),
File "/home/alex/Development/dkb2homebank/dkb2homebank.py", line 105, in convert_date
date = datetime.strptime(date_string, "%d.%m.%Y")
File "/usr/lib/python3.8/_strptime.py", line 568, in _strptime_datetime
tt, fraction, gmtoff_fraction = _strptime(data_string, format)
File "/usr/lib/python3.8/_strptime.py", line 349, in _strptime
raise ValueError("time data %r does not match format %r" %
ValueError: time data 'Buchungstag' does not match format '%d.%m.%Y'
Process finished with exit code 1
So strptime
receives a string it can't convert to a date ("Buchungstag").
Another idea would be to put the strptime line in a try-except block, but I'm not sure this is a good solution. I think next()
is rather the way to go. What do you think?
Best,
Alex
Hi Ham,
I'd like to add an option --output-dir
or even --output-file
with which I can choose where the output can be stored. Just using cwd isn't too useful for me. I am trying to build this myself, but I struggle with the unit tests for that. Then I thought, why not just write the file directly to stdout, so that the output can be piped into any file one wants?
What are your thoughts on that? I have some preliminary code, but it's not ready for a commit (though I could commit a WIP version so you can have a look if you like).
I will always get an error if I try to convert an exported visa csv file:
Traceback (most recent call last):
File "./dkb2homebank/dkb2homebank.py", line 117, in <module>
main()
File "./dkb2homebank/dkb2homebank.py", line 107, in main
convertVisa(args.filename)
File "./dkb2homebank/dkb2homebank.py", line 78, in convertVisa
'date': convertDate(row["wertstellung"]),
File "./dkb2homebank/dkb2homebank.py", line 93, in convertDate
date = datetime.strptime(dateString, "%d.%m.%Y")
TypeError: strptime() argument 1 must be string, not None
The file I used for the conversation attempt looks like that:
Kreditkarte:,1234********1234 Kreditkarte,,,,
,,,,,
Von:,02.07.2015,,,,
Bis:,30.06.2016,,,,
Saldo:,12345.67 EUR,,,,
Datum:,29.06.2016,,,,
,,,,,
Umsatz abgerechnet,Wertstellung,Belegdatum,Beschreibung,Betrag (EUR),Urspr�nglicher Betrag
Nein,29.06.2016,29.06.2016,buchung1,-1234,
Nein,24.06.2016,23.06.2016,buchung2,"11,79",
Ja,07.12.2015,04.12.2015,buchung in fremdwaehrung,"-41,82","-30,00 GBP"
Nein,06.12.2015,06.12.2015,buchung1,-1234,
Nein,24.11.2015,24.11.2015,buchung2,"11,79",
Hello hamvocke,
are you willing to support the new csv format in the new future?
The DKB is starting to switch over to a new website (modernized and all the things which nobody really needs ...) and now exports other formatted CSV files.
Greetings
Martin
Add a category to the conversion. This feature is available as well in HomeBank, but it is nice to have a json file for it. The rules could be described in a json file. Here is a possible patch:
From c065cb9ebb690829ba0fdffe8f4a44db641b393a Mon Sep 17 00:00:00 2001
From: <timeframe1@gmx.de>
Date: Wed, 29 Dec 2021 21:02:39 +0100
Subject: [PATCH 1/1] added a json file as a config to add categories
---
.gitignore | 1 +
README.md | 3 ++
dkb2homebank.py | 20 ++++----
parse_rules.py | 96 +++++++++++++++++++++++++++++++++++
requirements.txt | 6 +++
testfiles/category_rules.json | 13 +++++
6 files changed, 130 insertions(+), 9 deletions(-)
create mode 100644 parse_rules.py
create mode 100644 requirements.txt
create mode 100644 testfiles/category_rules.json
diff --git a/.gitignore b/.gitignore
index ffe7c5a..0381c8c 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,3 +1,4 @@
.idea/
__pycache__/
*.pyc
+Homebank.csv
\ No newline at end of file
diff --git a/README.md b/README.md
index 754ede1..706b697 100644
--- a/README.md
+++ b/README.md
@@ -23,6 +23,9 @@ You can also choose an alternative path for your output file, if the standard "c
./dkb2homebank.py --cash yourCashReportFile.csv --output-file ~/Documents/Finances/import_to_homebank.csv
+You can choose to add categories and tags to the new homebank csv file. The source for the parser is a json file. For an example seel `testfiles/category_rules.json`. (This can be archived in HomeBank as well, but a json file is a nice option).
+
+ ./dkb2homebank.py --cash yourCashReportFile.csv -p -pf testfiles/category_rules.json
Importing into Homebank
-----------------------
diff --git a/dkb2homebank.py b/dkb2homebank.py
index b9092f6..e7b1362 100755
--- a/dkb2homebank.py
+++ b/dkb2homebank.py
@@ -3,6 +3,7 @@
import argparse
import csv
from datetime import datetime
+from parse_rules import ParseRules
class DKB(csv.Dialect):
@@ -88,7 +89,7 @@ def identify_account_type(file_handle):
raise InvalidInputException("Can't recognise account type, is this a valid DKB export CSV?")
-def convert_DKB_cash(file_handle, output_file="cashHomebank.csv"):
+def convert_DKB_cash(file_handle, parse, output_file="cashHomebank.csv"):
"""
Convert a DKB cash file (i.e. normal bank account) to a homebank-readable import CSV.
@@ -107,12 +108,12 @@ def convert_DKB_cash(file_handle, output_file="cashHomebank.csv"):
'payee': row["beguenstigter"],
'memo': row["verwendungszweck"],
'amount': row["betrag"],
- 'category': None,
- 'tags': None
+ 'category': parse.category(row["beguenstigter"], row["verwendungszweck"]),
+ 'tags': parse.tags(row["beguenstigter"], row["verwendungszweck"])
})
-def convert_visa(file_handle, output_file="visaHomebank.csv"):
+def convert_visa(file_handle, parse, output_file="visaHomebank.csv"):
"""
Convert a DKB visa file to a homebank-readable import CSV.
@@ -131,8 +132,8 @@ def convert_visa(file_handle, output_file="visaHomebank.csv"):
'payee': None,
'memo': row["umsatzbeschreibung"],
'amount': row["betrag"],
- 'category': None,
- 'tags': None
+ 'category': parse.category(row["beguenstigter"], row["verwendungszweck"]),
+ 'tags': parse.tags(row["beguenstigter"], row["verwendungszweck"])
})
@@ -171,6 +172,7 @@ def setup_parser():
group.add_argument("-v", "--visa", action="store_true", help="convert a DKB Visa account CSV file")
group.add_argument("-c", "--cash", action="store_true", help="convert a DKB Cash account CSV file")
+ parser.add_argument("-p", "--parse_file", help="JSON file to parse categories and tags from keywords")
parser.add_argument('-o', '--output-file', default='Homebank.csv',
help='choose where to store the output file (default: working directory')
@@ -182,16 +184,16 @@ def setup_parser():
def main():
args = setup_parser()
-
+ parse = ParseRules(args.parse_file)
with open(args.filename, 'r', encoding='iso-8859-1') as csv_file:
account_type = identify_account_type(csv_file)
if "visa" == account_type or args.visa:
output = args.output_file or "visaHomebank.csv"
- convert_visa(csv_file, output)
+ convert_visa(csv_file, parse, output)
print(f"DKB Visa file converted. Output file: {output}") if args.debug else None
elif "cash" == account_type or args.cash:
output = args.output_file or "cashHomebank.csv"
- convert_DKB_cash(csv_file, output)
+ convert_DKB_cash(csv_file, parse, output)
print(f"DKB Cash file converted. Output file: {output}") if args.debug else None
diff --git a/parse_rules.py b/parse_rules.py
new file mode 100644
index 0000000..28cce96
--- /dev/null
+++ b/parse_rules.py
@@ -0,0 +1,96 @@
+import json
+import re
+import jsonschema
+from jsonschema import validate
+
+
+class ParseRules:
+ category_rules_schema = {
+ "definitions":
+ {
+ "rule":
+ {
+ "properties":
+ {
+ "key_word": {
+ "type": "string"
+ },
+ "conditions": {
+ "type": "array",
+ "items": {
+ "type": "string"
+ }
+ }
+ },
+ "required": ["key_word", "conditions"],
+ "additionalProperties": False
+ }
+ },
+
+ "type": "object",
+ "required": ["categories", "tags"],
+ "additionalProperties": False,
+ "properties":
+ {
+ "categories":
+ {
+ "type": "array",
+ "categories": {"$ref": "#/definitions/rule"}
+ },
+ "tags":
+ {
+ "type": "array",
+ "tags": {"$ref": "#/definitions/rule"}
+ }
+ }
+ }
+
+ def __init__(self, parse_file):
+ self.valide_json = False
+ if parse_file is not None:
+ self.json_file = open(parse_file)
+ self.__load_and_validate_jason()
+
+ def __del__(self):
+ if 'json_file' in locals():
+ self.json_file.close()
+
+ def __load_and_validate_jason(self):
+ try:
+ self.category_rules = json.load(self.json_file)
+ self.valide_json = True
+ except ValueError as err:
+ print(err)
+ self.valide_json = False
+ try:
+ validate(instance=self.category_rules, schema=self.category_rules_schema)
+ self.valide_json = True
+ except jsonschema.exceptions.ValidationError as err:
+ print(err)
+ self.valide_json = False
+
+ def category(self, payee, memo):
+ if self.valide_json:
+ return self.__apply_rule(self.category_rules['categories'], payee, memo, True)
+ else:
+ return None
+
+ def tags(self, payee, memo):
+ if self.valide_json:
+ return self.__apply_rule(self.category_rules['tags'], payee, memo, False)
+ else:
+ return None
+
+ def __apply_rule(self, rules, payee, memo, apply_once):
+ tags = []
+ for rule in rules:
+ for condition in rule['conditions']:
+ if bool(re.search(condition, payee + ' ' + memo, re.IGNORECASE)):
+ if apply_once:
+ return rule['key_word']
+ else:
+ tags.append(rule['key_word'])
+ break
+ if tags:
+ return ",".join(tags)
+ return None
diff --git a/requirements.txt b/requirements.txt
new file mode 100644
index 0000000..f8a4684
--- /dev/null
+++ b/requirements.txt
@@ -0,0 +1,6 @@
+argparse
+csv
+datetime
+json
+re
+jsonschema
\ No newline at end of file
diff --git a/testfiles/category_rules.json b/testfiles/category_rules.json
new file mode 100644
index 0000000..f940f6c
--- /dev/null
+++ b/testfiles/category_rules.json
@@ -0,0 +1,13 @@
+{
+ "categories": [{
+ "key_word": "test_category",
+ "conditions": ["foo", "shop"]
+ }],
+ "tags": [{
+ "key_word": "test_tag",
+ "conditions": ["shop"]
+ }, {
+ "key_word": "test_tag_2",
+ "conditions": ["shop"]
+ }]
+}
\ No newline at end of file
--
2.25.1
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.