Regulatory reporting implementation in the banking industry
Overview
Banks’ financial regulatory reports are datasets that contain information about the financial activities, products, and services that business clients use. These reports are aligned with the regulations of the Croatian National Bank and European Central Bank, and some are connected to the euro area.
Financial regulatory reporting is all done for the purpose of supervising credit institutions, financial stability analysis, risk management, monetary policy formulation and management, and economic analysis and research.
For financial regulatory reports to fulfill their purpose, data needs to be collected and reported in the same form and rules according to the regulations. In the past, there was data collection on a country level but without standardization rules, so it posed difficulties in unifying reports. Data now holds great value because it is collected based on harmonized concepts and definitions and is therefore applicable for deeper and more detailed analysis.
Before financial data can be used for such analysis, banks have to collect data and perform ETL processes to convert them into metrics aligned with requirements and regulations.
Regulatory reporting is highly important because it doesn’t only provide detailed data for future utilization, but also supervises financial activities among banks. Since Croatia entered the euro currency area, all Croatian banks have to consolidate their data in accordance with ECB regulations and provide the necessary data across attributes in the exact form stated in those regulatory documents.
For that, Croatian banks must implement systems to deliver that data; this is where our data engineering services come in.
Digital Poirots were brought on the projects as cooperators to Koios to help major banks transition their data into forms and databases that comply with regulatory reporting guidelines. The goal was to help Croatian banks implement regulatory financial reporting in their systems.
Project scope
Procedure:
- Data analysis
- ETL jobs
- Data validation
- Report generation
Project duration:
- 6 months
Methods:
- Identification of proper datasets (tables from DWH and files from various sources)
- Implementation of complex business logic with the usage of SQL queries, PL/SQL procedures, and ETL tools
- Implementation of ETL jobs by using SSIS for data fetching
- Realization of data validation controls through SQL queries
- JOIN of multiple tables that are integral parts of report systems
- Report generation through SSRS and creation of datasets according to parameters
Technologies used:
Oracle database
PL/SQL
Data Stage ETL tool
SQL Server database
SSRS tool
Excel
Visual Studio (with SSIS & SSRS extensions)
SSIS tool
What we wanted to accomplish:
Main issues:
What we needed to accomplish:
- Detect and fetch data sources
- Implement complex business logic for report calculation
- Implement data validation
- Implement report generation
Main issues:
- Deciding on which sources provide the most relevant data and which don’t
- Complex business logic
- Short and strict deadline
- Changes in report specifications from HNB (Croatian National Bank)
- Changes in requirements from clients
Our Approach:
ECB financial regulatory reporting is created through specific regulations and guidelines. To report everything to the Croatian National Bank, these reports have to include data that can be found in the banks’ various sources: data warehouse and data lake. The process had to follow business logic, implemented in a data warehouse with SQL, and rules aligned with banks’ business processes. For that reason, data engineering processes had to be implemented to identify and collect data from the proper datasets, and with the usage of ETL tools, transferred into a separate staging database that will be used for data processing. ETL tools were also used to fetch data from the staging database to the source database used in the application.
Data validation also had to be done, to comply with the Croatian National Bank and European Central Bank regulations and requirements. Data had to be checked for validity and correctness before the final report generation. After validation, the correct and needed data had to be fetched and fed into reports.
Solution:
Through cooperation with Koios, we helped work on transitioning to regulatory reporting, which becomes mandatory for every bank in the country because of the implementation of the Euro currency in the market. Since banks have multiple data sources where they store data about their users, it was necessary to identify relevant data that is needed for such reporting.
To expedite the report generation procedure, data engineering methods and processes were implemented to combine multiple data sources to create a new database with all the relevant data that will be used as the application source.
Our task was to enable complex regulatory reporting calculations, their automatic generations, and data validation. From data preparation to report generation automation, the approach was to prepare everything to be implemented in the application later on.
Results:
Enabled automated regulatory reporting for major banks in Croatia
Admired by
