ING DiBa csv to ledger converter

Andreas

2022-11-27 16:00

Because the fints api of ing diba is down for a while now I switched to parse the csv export. Before I used fints and this worked perfectly for years.

My new solution is only converting from csv to ledger -- no additional magic.

The contents of the csv begins with a non-csv part and the first line of actual csv contains the header of the rows below.

One line of csv looks like this:

26.08.2022;26.08.2022;VISA ALDI SUED;Lastschrift;NR XXXX YYYY STUTTGART KAUFUMSATZ 24.08 ...;100,00;EUR;-12,34;EUR

And I want an output like this:

# Lastschrift | VISA ALDI SUED | NR XXXX YYYY STUTTGART KAUFUMSATZ 24.08 ...
2022-08-26 FIXME
    Expenses:FIXME                             €12.34
    Assets:Girokonto

After conversion I copy the entries into a ledger file per month and manually add the accounts I want this entries booked into.

First working solution:

import csv
import io
from datetime import datetime
from decimal import Decimal
from pathlib import Path

import click


def csv2ledger(filename):
    # find start of csv and read csv data
    start_of_csv = -1
    data = []
    with open(filename, "r", encoding="cp1252") as fp:
        for index, line in enumerate(fp.readlines()):
            if start_of_csv < 0 and line.startswith("Buchung;Valuta"):
                start_of_csv = index
            if start_of_csv > 0 and index >= start_of_csv:
                data.append(line.strip())

    # add header; but reverse rows: newest at the end
    csv_data = io.StringIO(data[0] + "\n".join(reversed(data[1:])))
    csv_data.seek(0)
    for row in csv.DictReader(csv_data, delimiter=";"):
        yield row


def row2ledger(row):
    currency = "€" if row["Währung"] == "EUR" else row["Währung"]
    # convert amount from German to international numbers
    amount = Decimal(row["Betrag"].replace(".", "").replace(",", "."))
    # remove '.00'
    amount = int(amount) if amount == int(amount) else amount
    # reverse sign
    amount = f"{currency}{amount * -1}"
    dt = datetime.strptime(row["Valuta"], "%d.%m.%Y").date()
    details = " | ".join(
        [
            row.get(i) or ""
            for i in ["Buchungstext", "Auftraggeber/Empfänger", "Verwendungszweck"]
        ]
    )
    return (
        "# {}\n".format(details.strip())
        + f"{dt} FIXME\n"
        + f"    Expenses:FIXME                        {amount:>10}\n"
        "    Assets:Girokonto\n"
    )


@click.command()
@click.option("-f", "--filename", type=str, required=True)
def main(filename):
    target = Path(filename).stem + ".ledger"
    with open(target, "w") as fp:
        for row in csv2ledger(filename):
            fp.write(row2ledger(row))
            fp.write("\n")


if __name__ == "__main__":
    main()

A few things are ignored here, i.e. the "effective date" vs. "booking date". I chose to only use the effective date because this should be the same one I got from the fints-api before.

Disclaimer: This bookings are not used for anything official. They are used only for the personal bookkeeping of my finances.