Use case for building MIFID II Best Ex reporting for an investment bank.
Requirement - implement complete reporting capability for yearly MIFID II BestEx disclosure.
Report could not be done manually due to data volume - 12mn records, and complexity of data sources - database and JSON interface.
Result - automated reporting capability run on demand. Implemented using Alteryx for extraction and analytics and Tableau for visualisation and export to XML. Project completed in 10 weeks by 1 person.
Key deliverables:
1. Top 5 venues report (regulatory disclosure report)
2. Extended dashboard with absolute numbers for validation and sign off
3. Quality control report for data management
4. XML exportable view
5. Documentation and User training
Solution:
As with most reporting and analytics projects, BestEx solution consisted of sourcing the data, formatting and enriching with reference data, calculations and filtering and presenting data in a meaningful way.
Screen 1 - data sourcing, enrichment and analysis in Alteryx
Data Acquisition & Enrichment
Data acquisition was a challenge due to complexity of the interfaces and high volume of records - 14mn rows, around 30 fields each. Tableau or Tableu Prep could not handle it, hence we decided to use Alteryx for data preparation.
Alteryx is a much powerful tool for data processing and works well with Tableau. It can generate output data in *.hyper format optimised for Tableau performance.
Alteryx tool connected to several Oracle databases (~5mn trades), JSON-based API (~7mn trades), excel and csv files (~2mn trades). After sourcing all data was unified into a single data model with unique attribute names across all sources. Once ready, we connected Alteryx to authorised data sources for reference data, such as Legal Entity, Client, Financial Instrument, Trader/Salesperson location and FX rates.
Analysis
Next step was to identify trades eligible for reporting which are mainly external trades executed by European Union entities, with clients or trader/salesperson based in Europe. Legal entity, client, trader and salesperson location was part of the reference data obtained before so we implemented a simple business rule to identify eligible records. Some trades did not have location available, these were aded into the reporting dataset following conservative approach but also flagged as exceptions.
In addition to eligibility all values were converted into Euros and additional check was done to identify instruments marked as not to be traded on a trading venue. After analysis has been completed total dataset was split into reportable, including exceptions, and not reportable parts. Reportable part (~8mn trades) was combined into *.hyper file and passed as a data source to Tableau.
Data handover between the tools was done using shared drive given infrequent and on-demand execution of the process. For more frequent use cases Alteryx server can seamlessly publish and refresh datasource on Tableau server.
Visualisation
Screen 2 - Top 5 execution venues report
Alteryx has its own reporting capability, which does only that - predefined reports in pdf or excel. Tableau provides much richer functionality for visualisation and interactive dashboards. Dashboards can be further manipulated to display custom views, filter the dataset, drill down from a resulting aggregate number to individual trades. These views and reports can also be extracted into pdf or excel for further distribution.
For RTS28 we created 3 main views:
Top5 venues dashboard displaying top 5 execution venues per instrument with respective volume percentage;
Extended view displaying absolute totals per venue with a split into business lines to support validation and approval;
Control dashboard to monitor trades with missing or invalid data. Reporting team was able to extract individual trade IDs and remediate issues for more accurate reporting.
In addition to the views above we created XML formatted view for extract and publication to follow internal reporting standards.
Handover and Training
To support adoption we worked closely with reporting team to ensure there are user champions who can run and update the application once consulting engagement finished. We documented business rules used by the tool, sources and filters used for future reference. Users were provided training and how-to manuals on refreshing the data, running the application, data extraction and export. These materials were added to the operational procedures.
After business management approved accuracy of the reports and successful completion of the knowledge transfer, reporting solution has been handed over to reporting team for future use.
Conclusion
With a simple but powerful analytics solution using Alteryx and Tableau we implemented new reporting capability for the bank which could not be done manually. Custom technology built would cost up to 10x of the effort we spent given the number of the datasources and conventional development lifecycle. This is a new efficient patter for reporting yet to be fully leveraged by the industry.
Comments