> WHAT IT DOES
An Excel VBA workbook that generates professional PDF and XLSX commission statements for agents based on revenue share data. Commissions are captured at referred-customer level for each agent, with the referral period defined per customer. Multi-currency settlement is handled by pulling live FX rates from the Frankfurter API and converting all USD commission amounts to each agent's settlement currency automatically.
Statements are managed through a Statement Manager control panel with five action buttons: select/unselect all, refresh list, refresh treasury, and generate statements. Each output is saved in a dedicated folder per agent, labelled by statement number and agent name. Governance is reinforced throughout: the generator will not issue a statement if a customer referral has lapsed, or if key data from the revenue file — such as FX rates or agent details — are incomplete.
> WORKBOOK STRUCTURE
- OurAffiliates — issuing entity master; address details per entity
- Customers — referred customer directory with referral period and active-status formula
- Agents — agent directory with settlement currency, commission rate and wire fee
- Revenue Share — commission data entry per referred customer and agent
- Statement Manager — control panel: date inputs, statement list with checkboxes; aggregate commission totals by status displayed in a side panel; includes an instructions section
- Statement — print template driven entirely by named ranges
- Treasury — commission summary by issuing entity and settlement currency
> KEY FEATURES
- One-click generation of PDF and XLSX commission statements, saved to a folder chosen by the user, in named subfolders per agent
- Up to 15 merchant rows per statement, aggregated by customer and currency with subtotals (can be increased)
- Wire fee deducted from Total Payable automatically from the Agents directory
- Statement Manager UI with colour-coded status rows (inactive referrals flagged in orange)
- Commission-by-status sidebar panel updated on every list refresh
- Inactive referral guard: generation blocked with a warning if any customer referral has lapsed
- Statement template driven entirely by named ranges — layout changes never require code edits
- Treasury aggregates by issuing entity and settlement currency (statement count + total LCY)
- Entire workbook built at runtime by
InitializeWorkbook— clean slate deployment
Projects on this website are free to download for charities and non-profit organisations — please get in touch via the contact page.