Overnight reconciliation, no spreadsheet babysitting
A small UK lender was spending up to three hours every morning reconciling six Excel workbooks before the credit committee could decide which lending lines to open. We replaced the routine with a scheduled pipeline and a dashboard the committee actually opens. The morning workload shrank to a five minute scan.
The problem
Every weekday morning, a credit operations analyst opened six workbooks and reconciled them by hand: loan origination, settlement, manual overrides, two scheme files, and an exceptions log. The reconciliation produced a Monday slide pack and a daily summary that the credit committee used to set lending limits for the day.
It took ninety minutes on a good day, up to three hours when the override sheet had been edited overnight or when the settlement file had moved its schema on the first of the month. The committee meeting started at 9.30 a.m. On bad days the analyst was reconciling while the meeting was already underway.
Three specific risks were building under the routine. The override sheet had three competing layouts for the same fields, so different analysts got different answers from the same numbers. The settlement file's schema changed on the first of every month, which meant the first working day of every month carried a higher error rate. And the whole process lived in one person's head. When the analyst was off, the morning slowed to a crawl and the committee made decisions on yesterday's view.
The lender did not want a 'transformation project'. They wanted Monday mornings back.
How we engaged
The brief came in by email, a single paragraph from the head of credit operations. We replied within one working day with five clarifying questions about volumes, sources and existing tooling. By the end of the week we had agreed a written scope, a fixed price and a five week timeline. The brief itself was one page.
We did not propose a phased multi-stream programme. We proposed one engagement, one deliverable, one handover. The committee chair signed it off the same afternoon.
One paragraph by email, a reply by tomorrow, a one page scope in writing. That is how we start every project.
Week one, just watching
We sat with the analyst running the reconciliation and wrote down every keystroke. Not what they thought they did, what they actually did. The result was a one page diagram of every input, every transformation and every output. It showed two surprises: the override sheet was being edited by three different people with no version history, and the settlement file's 'monthly schema change' was actually a renamed column that broke a VLOOKUP at the same time each month.
We did not propose a fix in week one. We just wrote down the system as it really was.
The architecture we picked
A small, sensible stack. Nothing exotic.
A scheduled Python job collected the six source files at 5.30 a.m. and wrote them into a Postgres staging schema. dbt models on top of staging produced four reconciled tables, replacing the manual reconciliation entirely. Power BI on top of dbt produced the dashboard the credit committee actually opened. We used Git for version control on the dbt project, with a documented branching pattern. And we wrote all eleven flagging rules as named, documented SQL statements against the reconciled tables, so any analyst could read and challenge them.
We deliberately did not introduce a new BI tool, did not migrate any data into a warehouse the lender did not already have, and did not write a long governance policy. Every choice was made against one test: would the credit committee still trust the morning view if the analyst was off for a fortnight.
Challenges and how we handled them
The override sheet
The biggest risk was the sheet that three people were editing with no version history. Our first instinct was to replace it with a small web form. The committee resisted, the people editing it would not learn a new tool inside the project window. We kept the sheet, added a thin Python validator that ran on every reconciliation and flagged any row that did not pass schema, and built a daily report of every change. The sheet stayed. The risk went away.
The monthly schema change
The settlement file's renamed column on day one of every month was breaking the existing process. We could not change the upstream system. Instead, the Python ingest job now detects the schema and applies the correct mapping, with an alert to the analyst if it sees a column it does not recognise. Five weeks live, four month-ends, zero failures.
The credit committee's trust
Automating a reconciliation that a committee depends on is a trust transfer, not a technical change. We ran the new pipeline alongside the old manual reconciliation for two full weeks, with a daily comparison report. The committee signed off when the comparison was clean for ten consecutive working days.
The audit walk-through
Internal audit was briefed at week three, not week five. They asked for lineage from source files to dashboard, a list of every transformation, the SQL for every rule, the version history of every change. The dbt project gave them all of it on a single afternoon. Zero findings.
Eleven rules, all in SQL
In three short sessions with the credit committee, we extracted what they actually looked for when they read the morning report.
Each rule fires into a single 'exceptions' tab on the dashboard. When the tab is green, the committee moves on. When it is amber or red, the rule tells them exactly what to ask.
What the morning routine became
By week five, the morning routine for the analyst had become: open the dashboard, scan the exceptions tab, ask one or two clarifying questions in Slack, sign off. The reconciliation itself ran without her, starting at 5.30 a.m. The committee opened the dashboard at 9.15 a.m., not 9.30 a.m. The first agenda item was the exceptions, not 'where are the numbers'.
What we did not do, deliberately
- We did not replace the override sheet, the committee wanted it where it was.
- We did not introduce a new BI tool, they already had Power BI.
- We did not write a long governance policy, the dbt project documents itself.
- We did not pitch a follow-on phase, we handed over and left.
Handover
The lender received the dbt project in a Git repository, a runbook for the Python ingest, a one page architecture diagram, a documented list of every rule, and a recorded walk-through video for any analyst joining the team in future. Two follow-up questions came back in the first month, both answered by email. We have not heard from them since, which is the outcome we wanted.
Have something that needs doing properly?
Send us a short brief. We reply within one working day with a clear price and timeline.