Most loan portfolio teams know exactly where their data quality problem lives. It lives in a folder called something like “Annual Review 2024 — FINAL_v3_JM edits.xlsx”, owned by a lender who has since left the firm, formatted in a way that makes no sense to anyone else, and treated as the source of truth for a €3bn book.
This is a guide for teams who want to fix that, practically, affordably, and without a multi-year transformation programme.
The scenario is a mid-sized bank with a leveraged loan portfolio of roughly €3bn, approximately 250 borrowers across the US and Europe, and a reporting infrastructure built almost entirely on individual lenders’ personal spreadsheets. No standardisation. No controls. Annual reviews populated manually, with client financials either typed in by hand or scraped from the SEC website one filing at a time. New borrower onboarding done on paper forms. Lenders use their own excel templates. That means one Excel file for each borrower.
The goal is to get to Power BI reporting that actually reflects the portfolio in near-real time. Here is how to do it.
Start with BCBS 239 — not with the technology
Before you change a single template or move a single data point, understand what BCBS 239 requires of you.
Risk data aggregation and reporting is not a purely technical exercise. The principles set expectations around data accuracy, completeness, timeliness, and, critically, the ability to demonstrate data lineage. That last point matters enormously here: when you move from a spreadsheet ecosystem to a structured database feeding Power BI, you need to be able to show exactly where every data point came from, who touched it, and when. A slick dashboard built on undocumented data flows will not satisfy your regulator, and it will not satisfy your internal audit function either.
This guide focuses on the data and reporting layer. Controls architecture, lineage documentation, and governance frameworks deserve a separate treatment and will be covered in a follow-up article. Do not treat their absence here as permission to skip them.
Standardise first — everything else depends on it
The root problem in most spreadsheet-based portfolio reporting is not the spreadsheet itself. It is that each lender has invented their own vocabulary. One lender’s “Adjusted EBITDA” is another’s “EBITDA (adjusted for one-offs)”. The column headers differ. The units differ. The time periods differ. You cannot aggregate data across a portfolio if the data does not share a common language.
The fix is a standardised financial input template, a single, locked-down set of field names, definitions, and formats that every lender uses for every borrower. This sounds obvious. It is also the step most teams skip because it requires a conversation about whose version of “Adjusted EBITDA” is correct, and those conversations are uncomfortable.
Have them anyway. The template needs to define, at minimum: CFR at origination, interest rate, adjusted EBITDA, revenue, free cash flow, and any other metrics used in credit decisioning or covenant tracking. Field names should be machine-readable, no spaces, consistent capitalisation, no ambiguity. Once the template exists, it becomes the input form that feeds your database. Everything upstream of that form is a process problem. Everything downstream of it is a data problem. Keeping them separate makes both easier to solve.
We have simplified the above purposely so you can see the big picture. For some banks this can be a significant exercise. The data points you capture may feed across multiple departments so there may be a coordination effort needed. There also data points needed for reg requirements to be considered. One bank we worked with had close to 300 data points.
On paper forms for new borrowers: replace them with online contract templates. There are several low-cost tools that let you build structured digital forms with e-signature capability and direct data export. The output should populate a table, not a PDF that someone then re-keys into a spreadsheet. Please note this is something you can build in-house. Don’t force yourself into out of the box solutions.
Automate the data you should not be typing manually
For US-listed borrowers, lenders are currently pulling financial data from SEC filings by hand. This is slow, error-prone, and unnecessary.
The SEC’s EDGAR system exposes a public API that allows programmatic retrieval of financial statement data. A straightforward Python script, using the requests library and the EDGAR company facts endpoint — can pull quarterly and annual filings for any listed company by CIK number. The data comes back in JSON format and can be written directly to your database. For teams without in-house Python capability, this is also a task that can be outsourced cheaply to a data engineer or handled via a third-party financial data provider.
For European borrowers, the picture is less clean. There is no EDGAR equivalent for most European jurisdictions, and coverage across commercial data providers varies. For this segment, consider using a third-party financial data service, the cost is usually justified by the reduction in manual effort, and the data quality is typically higher than hand-keyed inputs. In either case, data sourced via API should be tagged with a financial type of “API” at the point of ingestion. This is important: it lets you distinguish programmatically retrieved data from data that has been reviewed and classified by a human, which matters for both data quality workflows and audit purposes.
Build the database that connects everything
The architecture here does not need to be complex. A structured database, SQL Server or PostgreSQL are both sensible choices, that your input templates and API scripts write to, and that Power BI reads from, is sufficient. Make sure you design the tables for scale i.e. other departments can leverage what you’ve built.
A key design decision is the financial type field and the complete list you need. When data arrives via API, it is tagged “API”. When a lender reviews that data, verifies it against source documents, and updates the classification, the financial type changes to reflect the actual reporting basis, “Quarterly Management Accounts”, “Annual Audit”, “Interim”, and so on. This two-stage tagging gives you a clear audit trail and allows reporting to filter by data quality or data source at any point.
Also note, financials are recorded on a year to date basis. For a lot of calculations you may need the in the period value e.g. the value for the quarter for analysis and metrics such as LTM. This will add a layer of complexity.
For teams that are not ready to retire Excel entirely, VBA can be used to push data from a standardised Excel template directly into the database. This means you only have one Excel file to manage instead of 250. Controls are critical. This is not the long-term answer, it introduces risk at the boundary between the spreadsheet and the database, but it is a practical bridge for lenders who will need time to adapt to a new workflow. Build the push function with logging: every write to the database should record the user, the timestamp, and the source file. It also needs to be held for a review and locked once approved. This is the start of your lineage documentation.
What Power BI makes possible
Once the data is in a structured database with consistent field names, Power BI can do what it is genuinely very good at: turning a flat table of loan data into an interactive, drillable reporting environment.
The shift from static reporting to dynamic reporting is harder to describe than it is to experience. A report that shows a lender their top ten new borrowers onboarded in the past week, with the ability to right-click on any borrower and drill through to a detail page showing full covenant tracking, financial history, and origination terms, that is the kind of functionality that changes how a credit team actually manages a portfolio.
Some of the most useful report pages for a leveraged loan book at this scale: portfolio-wide covenant headroom (sorted by tightest first), borrower-level EBITDA trend over trailing twelve months, geographic and sector concentration, and a pipeline view for upcoming annual reviews. All of these are straightforward to build in Power BI once the underlying data is clean and consistently labelled.
The technology, in other words, is not the hard part. Power BI is powerful, relatively affordable, and integrates well with SQL Server if you are already in the Microsoft ecosystem. The hard part is the standardisation work that happens upstream of it — and the governance framework that makes the outputs trustworthy enough to put in front of a credit committee or a regulator.
Summary
The path from broken spreadsheets to reliable portfolio reporting is not a technology project. It is a data discipline project that technology can then support. Get the input forms right. Get the field names agreed. Document where the data comes from and who reviewed it. The Power BI dashboards at the end are almost the easy part.
The question worth asking before you start is this: how much of your current reporting problem is a tool problem, and how much of it is that no one has agreed on what “adjusted EBITDA” means? The answer will tell you where to spend your first three months.