Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
by Mouna Belaid (@mouna_belaid) and Oussema Ben Aziza (@OussemaBENAZIZA)
Internal control is part of a strict regulatory framework applicable to all banking institutions. Using Alteryx, we were able to carry out within a major European bank the control of many regulatory reports. That involves a second-level permanent control of the main financial indicators before sending the declaration report to the regulator. Alteryx has made it possible, on the one hand, to optimize the execution of the most complex processes and, on the other hand, to minimize the various risks that a bank could face. The solution is a combination of two powerful BI tools: Alteryx and Power BI.
Overview of the Use Case
The internal control framework is organized on the "three lines of defense" model in accordance with the Basel Committee and European Banking Authority guidelines. The second line of defense is provided by the compliance, finance, and risk functions. Within the internal control framework, these functions are tasked with continuously verifying that the security and management of risks affecting operations are ensured, under the responsibility of operational management, through the effective application of established standards, defined procedures, methods, and controls as instructed.
The Alteryx solution is nowadays able to automate an end-to-end process, from accessing, blending, and manipulating data to the generation of outputs. The Excel output files are fed to Power BI and are visually explored in order to consume results easily with the ability to filter, sort, and analyze anomalies detected in the regulatory reports.
Business challenges and problems to solve
The Common Solvency Ratio Reporting (COREP) is the standardized reporting framework issued by the European Banking Authority (EBA) for reporting financial information to the regulator, which will be applicable to all credit institutions in the European Union (EU). It covers credit risk, market risk, operational risk, own funds, and capital adequacy ratios. The COREP framework aims to improve transparency and standardization in EU regulatory data reporting practices, as well as enable more enhanced risk identification and management in supervisory practices.
Reviewing the consistency and efficiency of the main indicators of COREP reports could sometimes be challenging. It consists of comparing changes in the COREP reports produced over time.
Working solution
The solution is accomplished by chaining two Alteryx applications together such that the results from App #1 dynamically provide the options for the inputs in App #2. The user should access the first Alteryx application, browse to the directory where the COREP files are stored, and execute it. A second Alteryx application will pop up. The user needs to choose the closing dates concerned by the control for the COREP reports and enter the threshold that he wants to apply to them.
The execution of the second application will produce output files containing the results of the controls applied along each step of the Alteryx workflow.
The workflow that was developed behind the applications outlines six main parts.
First of all, a part of the input data needs to be extracted from a storage system and stored in the appropriate folder. Input files are a set of extracted .txt files and a set of Excel files used for mapping purposes. Each .txt file contains information about the financial statements of each closing period. A closing report size can sometimes exceed 2 million rows. We walked through a reconciliation process that consisted of checking if there were any significant changes along COREP reports during five closing periods.
After bringing data from a variety of input files, we have implemented three Alteryx macros in order to structure the data and to allow the creation of keys for each cell of the COREP reports before carrying out the controls.
The second part of the workflow aims to ensure that the anomalies detected in the reports have been corrected for the republished COREP reports along each defined closing period. The following three parts allow the control of any changes if the user would respectively provide quarterly, bi-yearly, and yearly COREP reports during the selected closing dates in the Alteryx application. The process involved data aggregation, data manipulation, and data filtering operations in order to identify, for example, the indicators that were entered or modified manually on the storage system of the input files. The Alteryx application allows the user to monitor the evolution of COREP reports for up to twelve consecutive closing periods. In the last part, the results of the controls can be checked by the analyst in the Excel files stored in the Output folder.
Finally, Power BI is used in order to create stunning and responsive graphics in interactive dashboards.
The following figure represents the process accomplished to meet the requirements related to the regulatory reporting project.
Benefits achieved
The implementation of this Alteryx solution allowed us to have an industrialized and scalable solution by optimizing the processing time to ensure the controls of COREP reports. Besides, the Alteryx solution helped us to facilitate the identification of anomalies in dedicated reports and to expand the scope of control which was limited.
The power duo of using Alteryx to provide a clean and well-organized output, then using Power BI for visualizing and slicing-and-dicing the output files, gives the user a robust means of analyzing nearly any COREP report.
