Multi Source Data Reconciliation: Automated ETF compliance processing

Challenge

Our client relied on an in-house, legacy spreadsheet system for daily reconciliation of complex data sets needed for regulatory compliance. This Excel-based system had remained largely unchanged for years, processing inputs received via email that were manually loaded and reconciled using intricate formulae across multiple worksheets. The client, facing inefficiencies and errors inherent in this manual process, sought a modern, robust cloud-based solution.

The legacy system presented several significant drawbacks. It lacked transparency, offering no audit trail or security measures to track changes or ensure data integrity. The manual data entry and reconciliation process was time-consuming, requiring substantial effort and specialist knowledge to resolve frequent issues. Incorrect or poorly validated incoming data often led to imbalances, while imperfect calendar management further complicated workflows. These inefficiencies not only slowed operations but also increased the risk of regulatory non-compliance, prompting the client to seek a more reliable, automated alternative.

Action

The primary objective was to replace the cumbersome spreadsheet system with a streamlined, cloud-based solution that addressed the identified challenges. The client aimed to achieve greater transparency, improved data accuracy, and a faster reconciliation process.

Additional goals included automating data inputs, enhancing validation, and providing an intuitive interface for monitoring and correcting discrepancies. Beyond solving immediate issues, 542 Digital sought to take a holistic approach, identifying opportunities to further improve the client’s operations during the transition.

Solution

Agile Processes

542 Digital adopted an Agile methodology, fostering close collaboration with the client through bi-weekly meetings to ensure transparency and alignment. Development leveraged AWS tools and the financial data platform, delivering a secure, scalable system with near-100% uptime. Also incorporated was FactSet/ICE API connectivity for XAU, FX, and LBMA data.

Rigorous testing—spanning unit, system, and end-to-end QA—validated the solution’s accuracy, uncovering previously undetected data issues. After client approval, the system was implemented in parallel with the legacy spreadsheet, supported by training and assistance until the client confidently transitioned fully to the new platform. The result was a faster, more accurate reconciliation process that met all prioritised requirements, exceeding expectations through clear communication, flexibility, and adherence to industry best practices. 

Development

542 Digital proposed a comprehensive cloud-based solution built on its financial data platform, hosted on AWS for scalability and security. The process began with an in-depth analysis of the client’s workflow, where 542 Digital’s team observed the end-to-end reconciliation process, documented data sources, and built a data dictionary to map inputs and formulae. Collaborating with data vendors, they shifted much of the input process from manual email uploads to API feeds, improving timeliness and accuracy.

The solution featured automated workflows, new table structures for input and calculated data, and a dedicated dashboard with a color-coded calendar widget. This allowed the client to view, authorise, and approve reconciliations daily, with full audit trails and granular control over data adjustments. Discrepancies were flagged instantly, with traceable data fields and automated notifications for late or erroneous inputs, reducing manual intervention significantly.

Conclusion

In conclusion, the transformation of the client’s legacy spreadsheet system into a modern, cloud-based solution exemplifies the power of tailored technology to overcome entrenched operational challenges. By partnering with 542 Digital, the client not only resolved critical issues but also gained a scalable, secure platform that elevated their regulatory reconciliation process.

The successful deployment of the solution, underpinned by AWS infrastructure, automated workflows, and a user-friendly dashboard, delivered on all key objectives: 

• faster processing
• enhanced accuracy
• full auditability.

Through an Agile approach, rigorous testing, and close collaboration, 542 Digital ensured a seamless transition, empowering the client with a system that not only met but exceeded expectations. This case study underscores 542 Digital’s commitment to listening, innovating, and delivering measurable value, providing a repeatable blueprint for turning complex data challenges into streamlined, future-proof successes.