Un pachet de macrocomenzi Excel VBA pentru automatizarea reconcilierii plăților și generarea de rapoarte contabile cu controlul calității datelor

Situația inițială - departamentul financiar reconciliază manual extrasele bancare cu registrul de cont în Excel în fiecare zi, petrecând 2-3 ore pe zi și comitând greșeli din cauza diferitelor formate de date, comisioane bancare și plăți parțiale. Datele vin sub forma mai multor fișiere Excel/CSV de la diferite bănci și registrul intern al facturilor. Trebuie să puneți totul împreună într-un singur proces gestionabil în Microsoft Excel cu macrocomenzi VBA.

Mediu tehnic - Windows 10/11, Aplicații Microsoft 365 (Excel pe 64 de biți). Este permis doar VBA (fără suplimente terțe și fără Power BI). Datele sunt stocate pe o unitate de rețea, unele fișiere pot fi deschise de alți utilizatori. Volum - până la 50.000 de linii de tranzacții pe lună, până la 10.000 de facturi în registru. Este necesară o muncă stabilă pe Excel pe 64 de biți, ținând cont de limitările de memorie și de performanță.

Tematica - reconcilierea plăților: plată integrală, plată parțială, supraplată, rambursare, comisioane bancare în rânduri separate, diferite valute (UAH, EUR, USD), diferite formate de destinație de plată cu număr de factură/contract.

Rezultatul concret este un fișier șablon Excel cu butoane de tip panglică/formular și un set de module VBA care:
1) Importă instrucțiuni din 2-3 formate diferite (CSV cu separatori ; sau , și XLSX) și le normalizează într-un singur tabel (nu utilizați Power Query).
2) Importă registrul intern al facturilor dintr-un fișier separat, actualizează directoarele contrapărților și contractelor.
3) Potrivirea automata a tranzactiilor cu facturi conform regulilor: numarul facturii in destinatie, suma cu toleranta de pana la 0,5% (datorita comisioanelor/ diferentelor de curs), data platii intr-o fereastra acceptabila de +/- 10 zile, suport pentru plati partiale cu acumulare de sold.
4) Creează un raport privind starea facturilor: plătite, parțial, restante, plată necunoscută, duplicat suspect. Pentru plățile necunoscute, creează o listă pentru postare manuală cu opțiunea de a extrage o factură din listă.
5) Creează un jurnal de execuție (jurnal) cu timpul, numărul de linii importate, numărul de potriviri, erorile de analiză.
6) Oferă protecție împotriva erorilor comune: coloane lipsă, codificări CSV incorecte, valori goale, duplicate, formate de date mixte.

Restricții - nu utilizați biblioteci externe, acces COM la clienții băncii, solicitări pe internet. Trebuie să minimizați pașii manuali înainte de a rula macrocomenzi și de a selecta fișierele. Codul trebuie să fie lizibil, cu comentarii, împărțit în module. Este interzisă stocarea parolelor în cod.

Etape de implementare - analiza exemplelor de fișiere și acordul regulilor de potrivire, dezvoltarea structurii cărții și modulelor, implementarea importului și normalizării, implementarea potrivirii și diferențierii manuale, generarea rapoartelor și jurnalelor, optimizarea performanței, testarea pe setul de control.

Criterii de acceptare (măsurabile) - importul a 50.000 de tranzacții și 10.000 de facturi durează până la 3 minute pe un computer tipic de birou; acuratețea autocalibrării pe setul de testare furnizat nu este mai mică de 95% (erorile sunt confirmate manual de jurnal); cu un fișier incorect, macro-ul nu se blochează, ci emite un mesaj clar și o intrare în jurnal; rezultatele rapoartelor sunt reproduse la rularea din nou pe aceleași fișiere.

Materiale sursă - vor fi furnizate 3 exemple de extrase de la diferite bănci, un exemplu de registru de facturi și o descriere a regulilor care sunt aplicate manual în prezent. De asemenea, ar trebui să adăugați un scurt manual de utilizare pe o foaie separată.