Ein Paket von Excel-VBA-Makros zur Automatisierung des Zahlungsabgleichs und zur Erstellung von Buchhaltungsberichten mit Datenqualitätskontrolle

Die ursprüngliche Situation: Die Finanzabteilung stimmt jeden Tag manuell Kontoauszüge mit dem Kontoregister in Excel ab, verbringt dabei 2-3 Stunden am Tag und macht Fehler aufgrund unterschiedlicher Datumsformate, Bankgebühren und Teilzahlungen. Die Daten stammen aus mehreren Excel-/CSV-Dateien verschiedener Banken und dem internen Rechnungsregister. Dies muss in einem überschaubaren Prozess in Microsoft Excel mit VBA-Makros zusammengestellt werden.

Technische Umgebung – Windows 10/11, Microsoft 365 Apps (Excel 64-Bit). Es ist nur VBA zulässig (keine Add-ons von Drittanbietern und kein Power BI). Die Daten werden auf einem Netzlaufwerk gespeichert, einige Dateien können von anderen Benutzern geöffnet werden. Volumen – bis zu 50.000 Transaktionszeilen pro Monat, bis zu 10.000 Rechnungen im Register. Unter Berücksichtigung von Speicher- und Leistungseinschränkungen ist eine stabile Arbeit mit Excel 64-Bit erforderlich.

Themenbereich – Zahlungsabgleich: Vollzahlung, Teilzahlung, Überzahlung, Rückerstattung, Bankprovisionen in separaten Zeilen, verschiedene Währungen (UAH, EUR, USD), verschiedene Zahlungszielformate mit Rechnungs-/Vertragsnummer.

Das konkrete Ergebnis ist eine Excel-Vorlagendatei mit Multifunktionsleisten-/Formularschaltflächen und einer Reihe von VBA-Modulen, die:
1) Importiert Anweisungen aus 2-3 verschiedenen Formaten (CSV mit Trennzeichen ; oder , und XLSX) und normalisiert sie in einer einzigen Tabelle (verwenden Sie Power Query nicht).
2) Importiert das interne Rechnungsregister aus einer separaten Datei, aktualisiert Verzeichnisse von Gegenparteien und Verträgen.
3) Gleicht Transaktionen automatisch mit Rechnungen gemäß den folgenden Regeln ab: Rechnungsnummer am Zielort, Betrag mit einer Toleranz von bis zu 0,5 % (aufgrund von Provisionen/Wechselkursunterschieden), Zahlungsdatum innerhalb eines akzeptablen Zeitfensters von +/- 10 Tagen, Unterstützung für Teilzahlungen mit Saldoakkumulation.
4) Erstellt einen Bericht über den Status von Rechnungen: bezahlt, teilweise, überfällig, unbekannte Zahlung, verdächtiges Duplikat. Für unbekannte Zahlungen wird eine Liste zur manuellen Buchung erstellt, mit der Option, eine Rechnung aus der Liste abzurufen.
5) Erstellt ein Ausführungsprotokoll (Protokoll) mit Zeit, Anzahl der importierten Zeilen, Anzahl der Übereinstimmungen und Analysefehlern.
6) Bietet Schutz vor häufigen Fehlern: fehlende Spalten, falsche CSV-Kodierungen, leere Werte, Duplikate, gemischte Datumsformate.

Einschränkungen – keine Verwendung externer Bibliotheken, COM-Zugriff auf Bankkunden, Internetanfragen. Sie müssen manuelle Schritte minimieren, bevor Sie Makros ausführen und Dateien auswählen. Der Code muss lesbar, mit Kommentaren versehen und in Module unterteilt sein. Es ist verboten, Passwörter im Code zu speichern.

Implementierungsphasen – Analyse von Dateibeispielen und Vereinbarung von Matching-Regeln, Entwicklung der Buch- und Modulstruktur, Implementierung von Import und Normalisierung, Implementierung von Matching und manueller Differenzierung, Erstellung von Berichten und Protokollen, Leistungsoptimierung, Tests am Kontrollsatz.

Akzeptanzkriterien (messbar) – Der Import von 50.000 Transaktionen und 10.000 Rechnungen dauert auf einem typischen Büro-PC bis zu 3 Minuten; die Genauigkeit der automatischen Kalibrierung des bereitgestellten Testsatzes beträgt nicht weniger als 95 % (Fehler werden manuell durch das Protokoll bestätigt); bei einer fehlerhaften Datei stürzt das Makro nicht ab, sondern gibt eine eindeutige Meldung und einen Protokolleintrag aus; Die Ergebnisse der Berichte werden reproduziert, wenn sie erneut für dieselben Dateien ausgeführt werden.

Es werden Quellmaterialien bereitgestellt: 3 Beispiele für Kontoauszüge verschiedener Banken, ein Beispiel für ein Rechnungsregister und eine Beschreibung der Regeln, die derzeit manuell angewendet werden. Sie sollten außerdem eine kurze Bedienungsanleitung auf einem separaten Blatt beifügen.