Reconciliation between ledger and agent statements (transactions and invoices monitoring)
Requirement - set up daily reporting capability to track reconciliation between ledger entries and agent statements. Business division required management information on trade settlement and age of reconciliation breaks. Reporting was necessary to support Brexit CASS 10-day rule mandate. Data extraction was a challenge from operations side due to necessity to use non-strategic Excel data sources and limited support from technology team.
Result - Trade reconciliation dashboard with security profiles to monitor reconciliation issues and their age per business line. Implemented and launched in production by 1 person in 6 weeks. Initial build took 2 weeks but we made a number of increments as reporting team came up with new use cases for data usage. This was crucial to reach regulatory compliance after Brexit.
Key deliverables -
1. Several interlinked monitoring dashboards in Tableau with email distribution
Control report for identification of data quality issues
SQL query for data extraction and calculation of reconciliation breaks
Solution -
Working with a trade reporting team we prepared data extraction query for an Oracle database to fetch reconciliation breaks eligible for reporting and assess their age.
Data base extraction was passed on to Tableau, where it was joined with tactical data sources for business reference data and presented in several dashboards to monitor breaks closure and support trade cancellation process for unresolved breaks.
Data Acquisition & Enrichment
We integrated Tableau with Oracle database to extract ledger and agent statements with indicated reconciliation breaks. Trade data was further joined with 2 tactical MS Excel datasources - register of client and house positions and business area mapping with responsible owners. MS Excel files were uploaded to Sharepoint to support version history and access permissions. Overall dataset was less than a 100K rows which was easy to process in Tableau.
Analysis & Visualisation
Analysis consisted of defining break age running a business rule to verify if a trade is eligible for reporting and providing various pre-filtered views for monitoring. These functions were completed in Tableau using variables and filtering functionality.
For age we created two new parameters using statement date and entry date for comparison, business rule based on trade location, product, business area and client vs house flag was also implemented as a new parameter .
For filtering we set up several dashboards - UK CASS eligible breaks necessary for post-Brexit compliance process, total UK breaks and global breaks view. RAG status based on percentage of aged breaks per division
For each division we assigned Red/Amber/Green (RAG) status depending on amount of breaks and their age.
Solution has been deployed to Tableau server and dashboards were sent to key users via email.
In addition to core report we also implemented control report for monitoring data quality issues, such as missing attributes or missmatches between trade settlement and system entry dates.
Screen 1 - Divisional View with data owners (blurred) and RAG status.
Handover and Training
Solution was developed in close collaboration with nominated champions from reporting team who provided requirements, were consulted about the solution and took over maintenance of the solution after it was adopted in production.
We documented the solution for future reference, detailing business rules used and steps necessary to run the solution such as updating tactical data sources and accessing required views. Training sessions were provided to all user groups who use the solution - reconciliation team, CASS reporting team and business area representatives responsible for remediation of breaks. These sessions were recorded and saved alongside solution details for future reference .
Conclusion
With reconciliation breaks reporting we quickly and efficiently saved business need. It was an automated solution with necessary controls and security profiles to be used for regulatory reporting. Technology team was reluctant to solve the problem unless it relies on only strategic data sources which is often not the case. This reporting solution both addresses business need and serves as a working prototype for potential strategic reporting implementation owned by technology.
Comments