Why Leading Investment Management Firms Store Critical Data Outside of MS Excel Workbooks

By Hardeep

August 1, 2020 · 10 min read

Here’s a typical Investment Management Firm Scenario – Operations team wasting valuable time and resources to recreate accidentally deleted Microsoft Excel workbooks or cleaning up data in inconsistent formats

 

Does this scenario sound familiar? After the close of the trading day, your operations team has been working to reconcile cash and positions with the fund administrator and provide the investment team with the most accurate picture of the portfolio. All of a sudden, they go to open one of the Excel workbooks, and their computer crashes, resulting in a complete loss of the summary workbook that they have been preparing all day.

When using Excel workbooks, data can be easily lost for any of the following reasons:

  1. Excel workbook corruption due to software failure, power surge, computer crash, disk, or network failure.
  2. Excel workbook corruption when a macro execution fails or halts midway.
  3. Accidental and unintentional Excel workbook deletion.
  4. Unintended or incorrect Excel updates such as entry of wrong or inconsistent data.

When this happens, the only way to recover this data is by the restoration of the file from the most recent good backup of the Excel workbook. As most investment management firm operations team members know all too well, this is a tedious and time-consuming process. The automated file recovery features in Excel are not reliable, to say the least. If you have had to work with Excel workbooks where the format of cells was not consistent among workbooks from different periods, you know how much of an issue data integrity can become.

A Practical Solution

One of the primary features of a database is that it allows us to define a data model that specifies and enforces the format and the validity of data that we will be storing. The data model considers the following requirements: 

  1. The items or entities we will be storing data about (like fair value, P/L, return, CUSIP, Bloomberg ID, etc.)
  2. The data types for each data field (currency, percentage, text, images, etc.)
  3. Data validation rules (for example, the data stored in the commission column is always a negative number.) 

Every time an analyst enters data into the database, the pre-defined data model ensures that only valid data is entered. This means that you would be able to avoid the following errors that are typically associated with workbooks. 

  1. Numeric Data entered in text fields
  2. Text entered in numeric fields
  3. Invalid data entered in fields – like an invalid year (20190) or invalid ticker price

Another valuable feature of a database is called Referential Integrity. Referential Integrity allows us to only accept data whose master records already exist. It also ensures that the master record cannot be deleted until all dependent child records have been deleted. 

For example, this database feature can ensure that an investor record will not be deleted from the database until all investor-related records, like contact information, allocation into a fund, P&L, return, etc. are removed. It can also ensure that investor-related details can’t be entered until the main investor record has been created. As a result, data integrity is enhanced by ensuring no phantom records are sitting within the data and ensuring relationship integrity among data elements. 

Databases can be automatically backed up on a consistent (daily, weekly, monthly) basis. For example, a data backup can be performed at midnight every day after the end-of-day processing and reconciliation has been done.

This means that users would have various backups available depending on how far back they would like them to go. Some databases like MS SQL Server have built-in advanced backup capabilities that allow data to be restored up to the immediate point of failure.

Databases can also be configured to know where to store the data backups and where to look for database restoration data. However, with workbooks, someone has to manually record this information, introducing the risk that backups can’t be performed if nobody knows where the backup files were stored. And usually, each workbook would require a backup file. Therefore, after 5 years, 60 backup files would be needed for just one business function within the middle-back office – assuming there was one Excel workbook for each month of related data.

The number of backup files can quickly get out of hand as time progresses. Sometimes, these network drives, where the Excel workbooks are being backed up, are not accessible from each end user’s computer, so IT needs to get involved to access these backups. 

Conclusion

When it comes to critical information, businesses need to make sure your data is protected and backed up in the event of an emergency. Sadly, disaster waits for no one and can strike at any time and in any form. Even something as minor as a computer crashing can result in a complete data loss. Regardless of the type of disaster that may happen, with data backups, you will be protected.