Kontoauszüge automatisch auswerten

[Alfonso]

Hallo,

wir möchten Kontoauszüge, die als CSV vorliegen, einlesen und automatisch in einer Fakturasoftware einlesen. Die Software enthält offene Rechnungen und es soll automatisch erkannt werden, welche Rechnungen bezahlt wurden.

Welchen Algotithmus oder Methode würdet ihr verwenden um den Match zu machen? Die Software kennt Rechnungsnummer (Format PP-xx/jahr), wobei P ein beliebiger Präfix ist und xx eine fortlaufende Nummer. Ferner kennt die Software den Rechnungsempfänger und das Rechnungsdatum.

Wie würdet ihr vorgehen?

Danke
Alfonso

[Dejan Vu]

Erst alle offenen Rechnungen in eine Dictionary laden
Dann die CSV-Datei einlesen und Rechnungsnummern extrahieren (bzw. das, was wie eine Re-Nr. aussieht). Jeden Kandidaten in der Dictionary nachschlagen (ob es die Rechnungsnummer gibt). Dann den Betrag von der Rechnung abziehen und -wenn vollständig bezahlt- die Rechnung aus der Dictionary entfernen (wäre ja denkbar, das Teilzahlungen möglich sind).

Eine Fehlerbehandlung gehört auch dazu, d.h. extrahierte Rechnungsnummernkandidaten, die keiner Rechnung zugeordnet werden können könnten ein Tippfehler sein oder eben gar keine Rechnungsnummer.

Bei Verdacht auf Tippfehler könnte man den Kandidaten noch mit Levenshtein (max. Distanz 1) auf die übriggebliebenen Rechnungen in der Dictionary loslassen.

Auf jeden Fall ist das etwas riskant, denn ihr habt ja offensichtlich kein festes Format für die Angabe der Re-Nr. im Betreff-Feld. Ergo muss man parsen und interpretieren. Und da ist immer eine Unsicherheit dabei.

[Perlsau]

Moin, ich würde so vorgehen:

Meine Bank-CSV-Dateien enthielten damals, als ich mich damit befaßt hatte, Kontonummer und Bankleitzahl des Kunden oder Lieferanten (auch bei Abbuchungen, wenn im Laden mit Karte bezahlt wurde), heute wohl BIC oder was es da so gibt. Anhand dieser Daten sollte man den Kunden eindeutig und fehlerfrei ausfindig machen können. Bankdaten sind im Gegensatz zu Daten, die vom Kunden eingetragen wurden, annähernd 100%ig zuverlässig. Ich würde daher die Bankdaten als Erkennungsmerkmal präferieren und den eingegangenen Betrag von der ältesten unbezahlten Rechnung abziehen, unabhängig davon, was der Kunde diesbezüglich eingetragen hat. Gewöhnlich hat man ja sowieso nur eine offenstehende Rechnung pro Kunde, denn vor der Annahme des nächsten Auftrags sollte der vorhergehende bezahlt worden sein. In der Datenbank sollten dann auch die Bankdaten eine eigene Tabelle beanspruchen, wiel ein Kunde ja unter Umständen über mehrere Konten verfügt. Im Fall eines Neukunden, von dem noch keine Überweisungen vorliegen, könnte es natürlich sein, daß eine Zahlung nicht zugewiesen werden kann, weil eben noch keine Bankdaten eingetragen wurden. Diese Fälle müssen dann, nachdem auch das Parsen der Betreff-Felder nichts ergeben hat, von einem Mitarbeiter persönlich bearbeitet werden, indem er die Bankdaten einträgt und den betreffenden Datensatz noch einmal von der Software bearbeiten läßt.

[jaenicke]

Welcher Weg möglich ist, hängt sehr davon ab um welchen Kundenkreis es sich handelt. Wenn die Kunden fest erfasst sind und in der Regel mehrfach bestellen, ist letztere Methode von Perlsau möglich.
Allerdings ist die Frage, ob die Erfassung der Bankdaten, wenn sie nicht durch den Kunden hinterlegt wurden, datenschutzrechtlich in Ordnung ist. Das kann ich nicht beurteilen.

Wenn die Kunden aber eher nur einmal bestellen und auch keine Bankdaten hinterlegen (was bei Zahlung per Überweisung die Regel sein dürfte), lässt sich auf dem Weg nicht viel erreichen.

Daher würde ich eher eine Regular Expression erstellen, die auf das Format der Rechnungsnummer anspricht. Da das Format der Rechnungsnummer gut erkennbar ist, sollte das in der Regel kein Problem sein.

Problematisch ist es, wenn sich ein Kunde verschreibt. An der Stelle sollte es aber reichen, wenn man den Betrag abgleicht. Stimmt der nicht, wird die Rechnung in eine Prüfliste eingetragen, die dann manuell durchgesehen werden muss. Kommen Teilzahlungen öfter vor, müsste man sich da evtl. noch mehr einfallen lassen (Abgleich des Absenders und / oder des restlichen Betreffs mit den Daten des Kunden, ...).

[Dejan Vu]

Zitat von jaenicke:

Daher würde ich eher eine Regular Expression erstellen, die auf das Format der Rechnungsnummer anspricht. Da das Format der Rechnungsnummer gut erkennbar ist, sollte das in der Regel kein Problem sein.

Das deckt sich in etwa mit meinem Ansatz. Ich weiss nicht, ob eine RegEx hier geeignet ist. Man will ja u.a. auch unscharfe Matches und dann wird so eine RegEx sehr schnell sehr unübersichtlich. Aber im Prinzip in jedem Fall ein geeignetes Hilfsmittel.

Egal wie: Je mehr Freiheiten man dem Kunden lässt, sein Überweisungsformular auszufüllen, desto schwerer hat man es hinterher, die Information zu entschlüsseln.

Daher mein wichtigster Tipp: Sage dem Kunden genau(!), wie er die Überweisung durchzuführen hat.

• Rechnungsnummer ohne Zusätze ins Betrefffeld
• oder Kundennumer + Rechnungsnummer (je nach Anwendungsfall)
• oder nur die Kundennummer
• oder oder oder

Aber in jedem Fall EINE strikte Anweisung.

[jaenicke]

Zitat von Dejan Vu:

Ich weiss nicht, ob eine RegEx hier geeignet ist. Man will ja u.a. auch unscharfe Matches und dann wird so eine RegEx sehr schnell sehr unübersichtlich.

Die Unschärfe wird aber ja nicht bei der Rechnungsnummer selbst benötigt, denn die ist ja klar definiert.
Wenn deren Format nicht stimmt, bin ich mir nicht sicher, ob man da versuchen sollte automatisch etwas zu finden...

Man könnte natürlich den Schrägstrich zum Beispiel in einem zweiten Versuch einfach optional machen. Aber je mehr man weglässt desto höher wird die Chance einer Fehlerkennung. Wobei der Betrag natürlich noch eine zusätzliche Sicherheit bietet.

Ich schreibe bei Überweisungen zum Beispiel prinzipiell immer Rechnungsnummer (oder was drauf soll) und meinen Namen drauf um notfalls eine manuelle Zuordnung zu ermöglichen, wenn die Nummer nicht gefunden werden sollte.

Ich denke mal es sollte aber genügend historisches Material vorhanden sein um zu evaluieren welche Ansätze nötig sind ohne zu viel manuelle Nacharbeit zu erfordern.

[mm1256]

Hallo,

ich mache das seit knapp 2 Jahren so:

Bei jedem Kunden ist eine Standard-Bankverbindung, und wahlweise mehrere zusätzliche Bankverbindungen hinterlegt
Die Kontoauszüge werden in eine Datenbank eingelesen. Aus den nachstehenden Feldern erstelle ich einen MD5-Hash, der im Datensatz mit gespeichert wird, und für die Eindeutigkeit des Datensatzes verwendet wird.
Hintergrund: Es sollte ausgeschlossen werden, dass der User eine Datei mehrfach einliest. Zudem bieten manche Banking-Programme (z.B. Windata) die Option, beim Export den letzten Buchungstag nochmals zu wiederholen.

Felder für den Hash:
Datum : TDateTime; // Auszug-Datum
Valuta : TDateTime; // Valute-Datum
Blz : string[8];
Konto : string[10];
Umsatz : double;
BuchungsText: string[27];
VerwendungsZweck: string[216]; // 8 Verw.Zw. * 27 Zeichen
Auszug : double; // Format: AuszugNummer.Seite
WKennz : string[3];
AG_Name1 : string[27];
AG_Name2 : string[27];
AG_Blz : string[8];
AG_Konto : string[10];
AG_IBAN : string[22];
AG_BIC : string[11];

Wenn die Kontoauszüge in die DB eingelesen wurden, können sie gebucht werden, und werden im Erfolgsfall als gebucht gekennzeichnet. Für das Buchen gibt es eine kleine DB mit Buchungsregeln, die frei definierbar sind, und auf jeden Buchungssatz angewendet werden. Reichen die Regeln nicht aus, oder müssen sie angepasst/verändert werden, ändere ich nur die DB und muss das Programm nicht neu compilieren.

Aktuell läuft das so:

1. Ist eine Rechnungsnummer im Verwendungszweck eindeutig vorhanden? Stichwort Umbrüche bei den Verwendungszwecken.
2. Hat der Kunde (der aus der Rechnungsnummer ermittelt wird) einen Offenen Posten?
3. Passt dieser Offene Posten zum Betrag auf dem Kontoauszug? Dabei wird mögliche Skontierung berücksichtigt. Ist der Betrag höher, wird geprüft (rekursiv) ob der Kunde eventuell mehrere Rechnungen zu einer Zahlung zusammengefasst hat
3. Stimmt die Bankverbindung überein?

Dem Anwender werden dann in einem Buchungsdialog die automatisch ermittelten Daten angezeigt. Stimmen alle Buchungsregeln überein, wird das optisch angezeigt (die Ampel steht auf "grün") und der Anwender kann die Buchung sofort vornehmen. Fehlt z.B. die Bankverbindung, kann diese wahlweise gleich beim Kunden eingetragen werden. Im Gegenzug wird die Kundennummer im entsprechenden Zahlungsvorgang beim Kontoauszug eingetragen. Bereits beim Importieren der Kontoauszüge werden die Kontoauszüge gekennzeichnet, ob die Bankverbindung bereits bekannt ist. Ist eine Bankverbindung mehrfach vorhanden wird im Buchungsdialog darauf hingewiesen, um Fehlbuchungen zu vermeiden.

Dieses Schema wende ich auch bei den Lieferantenrechnungen an, und an der Implementierung für die Personaldaten (Löhne) arbeite ich gerade.

Der Vorteil der DB-Lösung liegt also darin: Sie ist flexibel erweiterbar. Möchte dann ein Anwender zudem die Daten noch an die Buchhaltung übergeben, was ja früher oder später der nächste logische Schritt bei der Verarbeitung der Buchungen ist, so stehen hierzu bereits alle Daten zur Verfügung. Man muss nur noch die Exportfunktion programmieren.

[Mavarik]

Zitat von Alfonso:

Welchen Algotithmus oder Methode würdet ihr verwenden um den Match zu machen? Die Software kennt Rechnungsnummer (Format PP-xx/jahr), wobei P ein beliebiger Präfix ist und xx eine fortlaufende Nummer. Ferner kennt die Software den Rechnungsempfänger und das Rechnungsdatum.

Wie würdet ihr vorgehen?

Die Frage ist was machen die Kunden...

1.) Der Kunde schreibt seinen Namen anders als bei Dir in der Datenbank erfaßt..
Beispiel: Bei Dir steht "A.L.S.O" der Kunde schreibt aber "ALSO" oder "Ra Maier" -> "Rechtsanwälte Petersen, Klaus & Maier"
2.) Der Kunde gibt nicht aktuele Rechnungsnummer an, sondern hat die Vorgabe der Banksoftware übernommen und es ist die alte Rechnungsnummer
3.) Der Kunde schreibt nicht sauber (ohne Bankprogramm) 2712/15 ergibt "27l1(1S"
4.) Der Betrag stimmt nicht mit der Rechnung überein. Vertippt oder (unberechtigtes) Skonto

Dann kommt es darauf an wie die Rechnungen aussehen...

100 Kunden erhalten die gleiche Rechnung mit der gleichen Summe... Blöd für einen Betragsvergleich.

Bei mir läuft die Analyse über mehrere Stufen.

1. Texte nominieren... Sonderzeichen raus und ggf. Token wie "Ra" -> "Rechtsanwalt" erstzen. Hier habe ich eine "selbstlernende" Liste die bei einem Treffen die Ersetzungstabelle befüllt.
2. Falschbuchstaben-Erkennung. S -> 5, ( -> /, l -> 1 usw.
3. Offene gegen Betrag falls Betrag nur 1x vor kommt und ggf. Rest noch passt..

usw... Insgesammt habe ich 8 Stufen und daran 4 Monate entwickelt...

Meine Trefferquote liegt zu Zeit bei ca. 1000 Buchnungen werden ca. 80 nicht erkannt oder werden zur Überprüfung (um sicher zu gehen) auf dem Bildschirm angezeigt. Davon können i.d.R. 60 mit ok bestätigt werden. Rest ist Handarbeit.

Zur Zeit arbeite ich noch an der Zuordnung Paypal, Amazon & Kreditkartenabrechnung (Währungsumrechnungen, Gutschriftverrechnung und splitten von Sammeleinzügen...)

Nächster schritt wir eine Multi-Thread Anpassung, da die ganzen Datenbankzugriffe und analysen zu langsam geworden sind.

Es gibt noch viel zu tun...

Mavarik

[mm1256]

Hallo,

die Frage "was machen die Kunden" stellt sich - so wie es aussieht - beim TE in diesem Umfang wohl gar nicht, und somit wäre das Parsen der Texte nicht sein größtes Problem. Es geht offensichtlich um Privatliquidation für Ärzte, und da wird sich die Anzahl der (Privat-)Patienten nicht stetig ändern. Somit wäre dann die Zuordnung wiederkehrender Zahlungen über die Bankverbindung wohl der erste - und bereits erfolgreiche - Schritt. Erst dann würden die zusätzlichen Überprüfungen folgen.

[Mavarik]

Zitat von mm1256:

Hallo,

die Frage "was machen die Kunden" stellt sich - so wie es aussieht - beim TE in diesem Umfang wohl gar nicht, und somit wäre das Parsen der Texte nicht sein größtes Problem. Es geht offensichtlich um Privatliquidation für Ärzte, und da wird sich die Anzahl der (Privat-)Patienten nicht stetig ändern. Somit wäre dann die Zuordnung wiederkehrender Zahlungen über die Bankverbindung wohl der erste - und bereits erfolgreiche - Schritt. Erst dann würden die zusätzlichen Überprüfungen folgen.

Das mag sein, aber es ging um Kontoauszüge automatisch auswerten...

Zum Beispiel bei Einzügen von Dritten wird die Zielkonto Verbindung nicht mit übertragen, sondern nur der Name.
Genauso bei Überweisungen. (Frei nach dem Motto - Musst Du doch wissen, hast es doch selber gemacht)