Automating the daily reconciliation process for asset managers

Daily reconciliation processes are crucial for asset management firms needing to establish their positions. Here is how QuantSpark helped one firm automate this otherwise manual task: speeding up processes and reducing errors.

In the fast-paced world of asset management, efficiency and accuracy of the daily reconciliation process is crucial as it ensures errors are detected and reconciled before the firm can open for trading. Historically, this process has relied heavily on manual Excel-driven procedures leading to errors and inconsistencies, a lack of version control, and key person dependencies.

This year QuantSpark delivered an automated solution to this process for a leading Asset Manager.

What is the daily reconciliation process?

The daily reconciliation process is undertaken by asset management firms to establish their positions before trading for the day. The process involves a series of checks and verifications and is crucial for ensuring the accuracy of financial information, compliance with regulations, and the overall integrity of the asset management firm's operations.

While specific processes may vary among different firms, the general steps typically include:

1. Position reconciliation: Compare the previous day's closing positions with the current system's records. Verifying discrepancies and investigating any unexplained differences.

2. Cash reconciliation: Reconcile cash balances by comparing the previous day's closing cash balance with the current system's records. Investigating and resolving any discrepancies in cash balances.

3. Trade reconciliation: Match executed trades with the order management system (OMS) records.

4. Corporate actions reconciliation: Verify and account for any corporate actions (e.g., stock splits, mergers, dividends) that may affect position and adjust positions and cash balances accordingly.

5. Securities pricing reconciliation: Validate security prices used for valuation by comparing them with independent pricing sources or market prices.

6. Data Integrity checks: Validate the integrity of data used for various processes, including positions, transactions, and market data.

7. Reconciliation reporting: Generate reconciliation reports detailing the results of the reconciliation process. Document any exceptions, discrepancies, or issues that require further investigation or resolution.

8. Communication and documentation: Communicate with relevant departments or teams regarding any issues identified during the reconciliation process. Maintain thorough documentation of the entire reconciliation process for audit and compliance purposes.

Challenges of a manual Excel-reliant daily reconciliations process

1. Error-prone manual processes: Manual data entry and manipulation in Excel are susceptible to human errors such as typos, miscalculations, and data entry mistakes, introducing inaccuracies into the reconciliation process.

2. Time-consuming operations: Manual reconciliation processes are time-consuming, particularly as transaction volumes and portfolio complexity increase. This results in delays in completing reconciliations and subsequently delays in trading activities.

3. Limited scalability and automation: Excel and manual processes face scalability issues as asset management operations grow. Managing large volumes of data and transactions becomes challenging, leading to operational inefficiencies.

4. Version control and audit trail challenges: Multiple versions of spreadsheets circulating among team members create version control issues, increasing the risk of using outdated or incorrect information. Additionally, Excel lacks a robust audit trail, making it difficult to track changes and demonstrate compliance with regulatory requirements.

5. Dependency on individuals: Heavy reliance on specific individuals for manual processes introduces a risk to continuity if key personnel are absent or depart. This dependency poses operational challenges and potential disruptions in the reconciliation process.

6. Difficulty in identifying patterns or trends: Analysing data for patterns or trends manually in Excel can be challenging, limiting the ability to derive meaningful insights. Automated reconciliation tools offer advanced reporting and analytical capabilities, facilitating better decision-making.

   To overcome these challenges, many asset management firms are adopting specialised reconciliation software and automated solutions. These tools enhance accuracy, efficiency, and regulatory compliance, reducing reliance on error-prone manual processes and mitigating associated risks.

QuantSpark's solution

QuantSpark developed a solution to automate the key manual steps of our client's end-to-end daily reconciliations process, seamlessly integrating with the existing data infrastructure. The initial phase focused on streamlining file collection, data consolidation and verification before leveraging python scripts to perform automated checks on the data.

Key features of our solution:

1. Data consolidation: The system automates the consolidation of data from multiple sources, including Northern Trust and Charles River.

2. Pythonisation of checks: Manual checks were replaced with efficient Python scripts, reducing reliance on traditional Excel formulas and VBA code.

3. Front-end application: A user-friendly front-end application was implemented for the control team, allowing them to view, validate, and exempt checks.

4. Dashboards: PowerBI dashboards provide a visual representation of data, facilitating quick and informed decision-making.

5. Single source of truth: By consolidating data into one centralised location, the solution establishes a single source of truth, ensuring consistency and accuracy.

Impact of our solution:

• Reduction in manual processing: Automation significantly reduced manual intervention, minimising the likelihood of errors and enhancing operational efficiency.

• Decreased dependency on individuals: Key-person dependency was mitigated, ensuring continuity and consistency in the reconciliation process.

• Accelerated process completion: The time taken to complete the end-to-end process saw a notable reduction, enabling quicker opening for trading.

Conclusion: building trust in the data and speeding up decision-making

QuantSpark's innovative automation solution transformed our client's Daily Reconciliation Process. The solution not only addressed existing data quality challenges but also positioned our client for greater efficiency, saving over thirty minutes per day.

This automation ensures accuracy and resilience in an ever-evolving financial landscape.