Typical Investment Management Firm Operations Scenario – Managers are unable to limit read-write access to data within Microsoft Excel workbooks
With risk mitigation being one of the top concerns for an investment management firm investor, operational risk must be a priority for a successful investment management firm. In most investment management firms, an operations analyst, working with a Microsoft Excel workbook will have access to all of the worksheets within the workbook, even though they may only need access to one or two worksheets, or in some cases only a small section of a specific worksheet. When using Excel for data storage, there is no way to limit access to any of the data within the workbook(s).
For example, an operations analyst, performing end-of-day P&L analysis for a portfolio, could also have access to the worksheet storing the investor P&L data. This means, that the analyst will know what the P&L is for each investor participating in that fund. In most instances, the investment management firm trusts its employees, and this should not be a problem. However, to show a potential investor that you have tight operational risk controls in place, you could advise them that your fund can limit access to their personal, and financial data.
Another reason to limit full read and write access to the data is that an operations analyst with unlimited access might accidentally change or delete the contents of a cell or row in a worksheet without even knowing it. These errors are very difficult and time-consuming to detect, and correct. On the other hand, if the investment management firm is storing the data in a database, data security can be easily put in place to ensure that the access to data can be limited in both scope, and range based on each operation analyst’s exact role and project requirements.
Also, as is typically the case for most investment management firms, the default access could be read-only access for most operations analysts, thereby preventing the data from accidentally being deleted. This kind of permission control is very difficult to implement with Microsoft Excel workbooks, since you are restricted to either giving full read, write access to everyone, or denying the same permissions to everyone. There is only one level of security available which is driven by the Microsoft Excel workbook password.
An Elegant Practical Solution
Databases can be easily set up to allow multiple users access to the data, allowing everyone to keep working Excel workbooks are designed to be used by a single person at a time. This makes it impossible for multiple users to access and modify different parts or worksheets, while someone else is working on a different worksheet within the same workbook.
Databases, on the other hand, are designed with concurrent usage features that are built-in, which makes it easy for multiple people to seamlessly add, delete, insert or update data in the same database simultaneously.
In a typical scenario, when multiple team members try to update the same record at the same time – the first user who initiated the update will always have priority and the second user will be notified that they cannot update the record at that moment – until the first user is done.
Database multi-user capabilities are very useful for data entry projects where different individuals are entering data. This will resonate well with the middle, and back-office users, such as the accounting and tax departments. The only way to achieve this in Microsoft Excel is to create multiple workbooks containing each worksheet that can be worked on by separate team members, and then combine the worksheets back into the main workbook at the end of each day to have a continuous, consistent record for the period’s activities.
This extra added manual step is not needed when using a database, as all inputs from multiple users would go straight to the same database. Reports on this data can be generated instantly without waiting for any further data consolidation.
In brief, multi-user databases are accessible from multiple computers simultaneously. Several people can be working together to update information concurrently, ensuring all employees have access to the most up-to-date information at all times. In turn, optimizing how you control information so it works for you and allows you to become strategic users of the data.
Added Advantage – Data Analytics & Data Visualization
Integrating a database as your back-end within your investment management firm operations, allows a fund to collect relevant operational data during the trading day, over long periods. This data can then be used for various front, middle, and back-office functions.
Using databases, along with Microsoft Power BI, also allows an investment management firm to perform more data analytics like real-time dashboards, drill-down analysis, segmentation, and cohort analysis, data grouping and Binning, predictive analytics, etc.
Also, it is relatively easy to email this analysis to the relevant parties, as well as make it web and mobile access. Excel, when combined with a database as the back-end to its front-end, will provide a long-term, reliable approach for most investment management firms looking to grow AUM.
The mindset shift is that Microsoft Excel should primarily be used for data analysis, testing financial models, and what-if scenarios. It should not be used for data storage purposes. The data used by Excel workbooks should be extracted from a database that should be used to store, manage, and accumulate data over long periods.
This approach allows an investment management firm to efficiently and successfully continue using Excel for data analysis, but handle data storage, integrity, and management with a database.