Every Rupee, Every Department: A 12-Source Spend Allocation Engine
The data engine behind a company's MIS reporting: it merges transactions from 12 sources across Indian and US banks, cards, payroll and payables (5 by API, 7 from PDFs), maps each one to a vendor and allocates it to one of 14 departments. Up to 3,000 transactions a month, about 70% less effort.
The problem
The company's spending was scattered across 12 places: Indian and US banks, corporate cards, payroll and payables tools. Some had APIs, most only produced PDFs. Nobody could see what each department actually spent in a month without stitching it together by hand.
What I built
The pipeline and tools behind the company's MIS reporting. It combines every source into one set of transactions, maps each transaction to an existing vendor or creates a new one, and allocates it to a department to calculate monthly spend across the company.
How it works
- Ingest: 12 sources merge into one transaction set. Five come in through their APIs where the accounts allowed access: Mercury, Brex, Pazy, Bill and Razorpay. Seven are read from PDF statements and reports: Rippling, greytHR, SVB, Deel, JP Morgan, HDFC Bank and HSBC. AI Builder document processing in Power Automate reads the PDFs and extracts the transactions.
- Vendor mapping: bank and card records rarely carry a clean vendor name, so Microsoft Copilot resolves each messy descriptor to one canonical vendor. "AMAZON.COM", "Amazon Services" and "amazon" all become Amazon, which carries its ledger code. New vendors are added when there's no match.
- Allocation: transactions from vendors that are already mapped go straight to their department. Unmapped ones go through a rules layer that works out the department from signals like the predefined budget owner for that bank account or the card user.
- Record manager: a Power Apps UI where the finance team views, corrects and manages the mapped records. Larger datasets live in Dataverse and smaller ones in SharePoint. The Power BI dashboard on top of this data was built by someone else.
Result
Up to 3,000 transactions a month across 8 bank accounts are now allocated to 14 departments with about 70% less effort than before.
My role
I built it myself: the source integrations, PDF extraction, vendor matching, allocation rules and the Power Apps record manager, with some help from an automation engineer.