🎯 Business Challenge
Month-end financial close was a 2-day nightmare: accountants manually copy-pasting data across 12 Excel templates, validating Trial Balances against accounting standards (SNC), and praying no formula broke.
- Human Error Risk: Copy-paste mistakes led to €50k discrepancy in one month (caught by auditors).
- Time-Intensive: 2 full days of work preventing analysts from value-add activities.
- No Validation: No automated check that debits = credits or accounts balanced.
- Version Control Chaos: "Final_Report_v3_FINAL_FINAL.xlsx"
💡 Solution Architecture
Built a Python automation script that reads Trial Balance, validates against SNC rules, and generates formatted financial statements in under 5 seconds.
Key Features
- Trial Balance Validation: Checks debits = credits, no orphan accounts, balance consistency.
- SNC Compliance: Maps accounts to correct financial statement lines per Portuguese GAAP.
- Template Population: Auto-fills 12 Excel templates (Balance Sheet, Income Statement, Cash Flow).
- Formula Verification: Validates all Excel formulas (Assets = Liabilities + Equity, etc.).
Technical Implementation
import pandas as pd
import openpyxl
# 1. Read Trial Balance
tb = pd.read_excel('trial_balance.xlsx')
# 2. Validate
assert tb['debit'].sum() == tb['credit'].sum(), "TB doesn't balance!"
assert tb['account_code'].isnull().sum() == 0, "Missing account codes"
# 3. Map to Financial Statements
income_stmt = tb[tb['account_code'].str.startswith('6')].copy()
balance_sheet = tb[~tb['account_code'].str.startswith('6')].copy()
# 4. Populate Templates
wb = openpyxl.load_workbook('BS_template.xlsx')
ws = wb['Balance Sheet']
ws['B5'] = balance_sheet[balance_sheet['account_code'] == '1100']['balance'].sum()
wb.save('Balance_Sheet_Final.xlsx')📈 Key Results
- <5 Seconds: Process that took 2 days now takes 5 seconds.
- 100% Accuracy: Zero math errors (validated by auditors).
- €50k Saved: Prevented repeat of €50k reconciliation discrepancy.
- Analyst Time Freed: 16 hours/month redirected to financial analysis.
🚀 Future Enhancements
- ERP Integration: Pull Trial Balance directly from SAP API (no manual export).
- Multi-Entity Consolidation: Automate group-level consolidations with eliminations.
- PDF Generation: Output board-ready PDF reports with charts.