Living on the edge: Spreadsheets and data reconciliation

I’m always surprised when investment managers tell me they still use spreadsheets for data reconciliation when there are plenty of automated tools available in the industry. Their reasons vary: They haven’t taken the time to move them over to their reconciliation platform. It isn’t broken (yet).  They’re comfortable with it. It’s just how they’ve always done it. It’s not a priority.

Spreadsheets like Microsoft Excel do offer a convenient and familiar interface. However, using them for reconciliation can have significant pitfalls that could result in errors, inefficiencies, and even regulatory and audit risks. That’s because spreadsheets are not designed for data reconciliation. They’re cumbersome and prone to errors, especially when dealing with large amounts of data. Additionally, they lack the advanced features and functionalities that a dedicated data reconciliation tool can provide.

While today’s investment operations leaders are aware of the many downsides of using spreadsheets for reconciliation, many are not encouraged or motivated to change because those drawbacks have yet to raise their ugly heads and catch up with them. 

Here are some of the major flaws and problem areas that often crop up in my conversations with investment operations leaders.:

1. Lack of Controls: Macros and sheets can be modified without any monitoring, making it challenging to ensure data integrity. This can lead to errors, which can be costly and time-consuming to correct. In addition, the lack of controls makes it difficult to track changes, which can be risky from an audit and regulatory perspective.
2. Audit Risk: Auditors need assurance that the data is accurate and the reconciliation process is transparent and repeatable. Spreadsheets do not offer the level of transparency and auditability required for auditors to feel confident in the accuracy of the data. Additionally, the lack of advanced features in spreadsheets can make it challenging to detect and correct errors. Primarily used to compare one data row to another (such as a position or transaction record), spreadsheets lack the functionality to support complex reconciliations such as cash proofing, NAV, and total equity, to name a few.
3. Regulatory Risk: From a regulatory perspective, using spreadsheets for data reconciliation is also very risky. Regulators require investment managers to maintain accurate and complete records. In fact, Dodd-Frank, EMIR, MiFID II, Basel III, and SOX all require some form of reconciliation. Spreadsheets lack audit controls and transparency, which can make it difficult to meet these regulatory obligations, potentially resulting in fines, penalties, reputational damage, or even legal action.
4. Key Person Risk: Spreadsheets are often complicated and obscure, and rarely understood by anyone other by the person who created it. What if that person leaves the company and there is no one else who can decipher the controls and models set up in obscure macros or fix a problem should something break?
   
   

What to Look For

Investment managers should consider using a reputable, proven solution that caters to their specific reconciliation needs and flexes with growth and change. Tools like Gresham’s Control for investment management offer advanced features and functionalities that can help investment managers streamline their reconciliation process, reduce errors and ensure regulatory compliance. 

Never compromise – ensure your chosen solution offers: 

• Advanced matching algorithms that can help identify and reconcile differences automatically
• Robust audit controls that track changes and provide a transparent and repeatable reconciliation process
• Workflow management tools that can help streamline the reconciliation process and improve efficiency
• Native support for reconciliation processes that are common to the buy-side community (i.e., NAV, cost-basis, P&L, total equity, cash proofing, etc.)
• Reporting and data analytics capabilities that can help investment managers gain valuable insights into their data and identify areas for improvement
• Capabilities to scale easily to higher volumes as the business grows and investment strategies and client needs become more sophisticated

Getting Your Priorities Straight

Contact Us!