How Successful Investment Management Firms Efficiently Prepare Data for Analysis & Reporting

By Hardeep

October 1, 2020 · 10 min read

Most investment management firms extensively use Microsoft Excel as their de facto application because of its ease of use, flexibility, and powerful features for performing data analysis, calculations, financial modeling, and what-if scenarios, among other daily front, middle, and back-office tasks. At a typical investment management firm, the Excel workbooks are stored in a folder structure on a network drive that matches a structure similar to this – FIRM / DEPARTMENT / BUSINESS FUNCTION / TIME PERIOD. For example, XYZ Fund / OPERATIONS / RECONCILIATION / 2019-04-06. Although the folder structure is well thought out, the management of data stored in multiple workbooks can become overwhelming over time. A file folder structure has to be created and strictly maintained, over time, to ensure that workbook files, for different periods, are named and stored correctly ensuring the setup works. It is also paramount that the names for the workbooks follow the specified convention – otherwise, it can become difficult to identify and retrieve these workbooks as well as lead to errors when running-related macros.

Making correct backups consistently, over time these workbooks, and their data become error-prone. For example, if your operations department needs to work with 10 linked workbooks to perform a certain analysis, each workbook must be backed up separately to ensure that the complete set is backed up. Also, there is no way to confirm if you have omitted any files from your backup or included the wrong workbook. To a large extent, the success of the whole process depends, largely, on the domain knowledge and competency of the specific individual(s) working with these workbooks, daily. If or when the author of these workbooks, who is typically in charge of maintaining the naming conventions, is on leave from the company, this knowledge is unavailable. Even worse, if this person leaves the firm, that knowledge may be lost forever. In addition, the person’s successor might decide to start their own file naming and storing convention. This results in the whole analysis, summarization, and reporting process to fail. This also means that future reports based on these linked worksheets can fail because one or two worksheets are now missing from the backup. Furthermore, using workbooks is difficult because data consistency and validity are hard to enforce when the process is done manually.

Also, accidentally deleting records (rows, columns) from a workbook will not generate an alert until the summary report is re-run. A proposed solution to prevent these operational issues from happening would be to transfer all the relevant data into a relational database like Microsoft SQL Server and then use Microsoft Excel as the front-end to analyze this data. What are the advantages of integrating a database with Microsoft Excel? Databases prevent data integrity violations through the adoption of database security policies. These policies restrict data modifications to data meant to be used for reporting. Also, database auditing features allow the ability to check and see if any unauthorized modifications have been performed on the data being analyzed. In summary, using a database will ensure data validity, consistency, and prevent data from accidentally being deleted.