A package of Excel VBA macros for automating payment reconciliation and generating accounting reports with data quality control

The original situation - the finance department manually reconciles bank statements with the account register in Excel every day, spending 2-3 hours a day and making mistakes due to different date formats, bank fees and partial payments. The data comes in the form of several Excel/CSV files from different banks and the internal register of invoices. Need to put this together in one manageable process in Microsoft Excel with VBA macros.

Technical environment - Windows 10/11, Microsoft 365 Apps (Excel 64-bit). Only VBA is allowed (no third-party add-ons and no Power BI). Data is stored on a network drive, some files can be opened by other users. Volume - up to 50,000 lines of transactions per month, up to 10,000 invoices in the register. Stable work on Excel 64-bit is required, taking into account memory and performance limitations.

Subject area - payment reconciliation: full payment, partial payment, overpayment, refund, bank commissions in separate lines, different currencies (UAH, EUR, USD), different payment destination formats with invoice/contract number.

The concrete result is an Excel template file with ribbon/form buttons and a set of VBA modules that:
1) Imports statements from 2-3 different formats (CSV with separators ; or , and XLSX) and normalizes them into a single table (do not use Power Query).
2) Imports the internal register of invoices from a separate file, updates directories of counterparties and contracts.
3) Automatically matches transactions with invoices according to the rules: invoice number in the destination, amount with a tolerance of up to 0.5% (due to commissions/exchange rate differences), payment date within an acceptable window of +/- 10 days, support for partial payments with balance accumulation.
4) Creates a report on the status of invoices: paid, partially, overdue, unknown payment, suspicious duplicate. For unknown payments, it creates a list for manual posting with the option to pull up an invoice from the list.
5) Creates an execution log (log) with time, number of imported lines, number of matches, parsing errors.
6) Provides protection against common failures: missing columns, incorrect CSV encodings, empty values, duplicates, mixed date formats.

Restrictions - do not use external libraries, COM-access to bank clients, Internet requests. You need to minimize manual steps before running macros and selecting files. The code must be readable, with comments, divided into modules. It is forbidden to store passwords in the code.

Implementation stages - analysis of file examples and agreement of matching rules, development of the structure of the book and modules, implementation of import and normalization, implementation of matching and manual differentiation, generation of reports and logs, optimization of performance, testing on the control set.

Acceptance criteria (measurable) - import of 50,000 transactions and 10,000 invoices takes up to 3 minutes on a typical office PC; the accuracy of auto-calibration on the provided test set is not lower than 95% (errors are confirmed manually by the log); with an incorrect file, the macro does not crash, but issues a clear message and a log entry; the results of the reports are reproduced when running again on the same files.

Source materials - 3 examples of statements from different banks, an example of an invoice register and a description of the rules that are currently applied manually will be provided. You should also add a short user manual on a separate sheet.