Reporting Robotics: The Elegant Way Out of Compliance Reporting Madness

We are all into cloud-based data warehouses now. Over the past 5 years, FinTech has moved all of their data assets into user-friendly platforms where data can be easily accessed and analyzed. We now have data catalogs, data lineage, semantic layers, and more for what some might call "data shopping."

However, having all the data at hand doesn't mean that reporting becomes instantaneous. It's akin to walking into Walmart to buy ingredients for your Thanksgiving dinner and expecting the meal to be ready right after checkout. That’s totally unrealistic.

The challenge with data, much like cooking, is that you need to follow a recipe, mixing ingredients in a specific order, adhering to rules, formulas, criteria, and performing data preparation for final consumption.

For decades, IT visionaries have envisioned a self-service BI platform, pouring millions into systems intended for ease of use. Yet, these often end up as mere shopping carts. Users gather their data, retreat to their desktops, and start the real data manipulation in Excel. At the end of the day, what users truly seek is to draw solid conclusions that incorporate business rules, what-ifs, and focus on specific data subsets.

To be fair, natural language processing has advanced significantly, allowing users to ask questions in plain English. However, the process can be time-consuming, often taking minutes or even hours, and might not provide the steps taken or the raw data for validation. Sometimes, the queries are misinterpreted, leading to results that are nonsensical or misleading.

This is why users often prefer downloading datasets into Excel; there's a psychological comfort in physically manipulating data, giving them a sense of ownership and control. This aspect is often overlooked by many BI tools.

Another common issue is delegating regulatory report coding to technology teams. It's like handing over your family's traditional Thanksgiving recipe to someone unfamiliar with your culinary heritage. The result might be disappointing, late, and not something you'd trust with regulators.

Data preparation for regulatory reporting isn't just a set process; it requires active intervention, much like adjusting the seasoning in your gravy or checking your pie in the oven to ensure it doesn't burn. Data must be adjusted, remediated, curated, and acted upon as surprises arise.

What I've observed repeatedly is a DIY approach: there are standardized official data feeds from authoritative sources, followed by a semi-automated step-by-step process to compile the report. While compliance officers often have the data skills to tweak these processes or even improve them, much of this work still happens in Excel, involving numerous VLOOKUPs, sub-reports, and checks.

Regulatory changes occur frequently, demanding quick adaptations to reports with zero tolerance for errors. Regulatory risk and fines are luxuries financial institutions can't afford, and sometimes, even substantial investments in risk mitigation barely

move the needle.

If you've read this far and find these issues resonate with you, you're at the right place at the right time.

The pattern I've successfully implemented time and again in my over 25 years in FinTech isn't about using the latest shiny tools but making the most of what's readily available: MS Office.

Pattern: Do not cook Data in Excel

Excel has a big brother that doesn't get the recognition it deserves — MS Access. Often misunderstood, like SharePoint, Access can connect in real-time to nearly any data source relevant in FinTech, from Excel files to Oracle databases via ODBC or ODATA drivers.

With Access, you can query data directly from Oracle, SQL, or any other database, making it seem like local tables. You can then perform joins (similar to VLOOKUPs) across different sources effortlessly through a graphical interface, without needing to write SQL code.

From a data architecture perspective, this process isn't about cramming everything into one mammoth query. Instead, break it down into manageable chunks, use Access to store pre-processed data, and proceed with further steps for remediation, validation, or enhancement.

You can automate report generation with macros in Access, which execute a chain of queries and criteria, ensuring repeatability and consistency. Often, the data volume isn't an issue since you're working with pre-summarized or harmonized data.

This approach not only guarantees consistency but also allows for real-time data delivery to tools like Excel, Power BI, or directly into reports. However, mastering this method requires learning and practice, akin to learning to use advanced kitchen appliances. Embrace this paradigm shift, and you'll find yourself moving beyond Excel's limitations, into a world where your data reporting processes are both robust and dynamic.

In Summary

By leveraging Microsoft Access as a powerful yet underutilized tool in your reporting arsenal, you can transform compliance reporting from a dreaded task into a streamlined, efficient process. Instead of wrestling with Excel's limitations, Access allows you to manage your data with the finesse of a master chef, ensuring that each report is not only compliant but also insightful and timely. This method empowers you to take control, reduce errors, and adapt quickly to regulatory changes, all while maintaining the detailed oversight that gives you confidence in your data integrity.

Post Production

Beauty of this approach is that in the back end you are generating SQL, which is a universal language in Databases; so once your little Reporting Robotic solution is mature enough you can call that guy in Technology and ask them to replicate the exact whole thing; even the interim control tables, reports, etc. So there is no wasted time, all of the sudden you had become an desktop wise ETL developer (or a Data Ninja as I prefeer).

If you like this article please share and subscribe. Further questions or comments please write me to pedro.castellanos@getridofexcel.com