Modern data extractions that mean Excel is ex-hell

Date: October 6, 2017

A middle office’s data is so valuable, everyone wants to use it, but what’s the best way to efficiently and accurately share information?

Pullout-Modern data extractions that mean Excel is ex-hell.jpg

As a key component of an asset management firm, the middle office processes reams of valuable data and pushes it out to internal colleagues and departments. But what is the best way to maximize this process? How can data best be extracted from the performance analytics system, and then moved around or fed into the reporting platform without compromising its accuracy or integrity?

Technology is a massive enabler here. Data extraction needs to go way beyond Excel and FTP. The challenge is to efficiently extract data from multiple sources and then share it in a manner that doesn’t affect its accuracy or precision.

Data dinosaurs

Consider the old methods of data extraction and their potential for errors or latency:

  • Gigantic file imports into static Excel sheets
  • Cumbersome FTP access
  • Inefficient unformatted file dumps

But in today’s world, where unstructured data that doesn’t fit into columns and rows is growing at twice the rate of structured information, Excel file imports can be out of their depth. Top-performing managers know this and are working towards bringing data sophistication to multiple aspects of competitiveness, and not just portfolio management. The solution for such managers – not for the first time – lies in the cloud.

Cloud-based services give an ideal response to the problem, with:

  • Web APIs for programmatic data access
  • Dynamic data imports to support ‘active’ Excel sheets
  • Quicker roll-out of global portfolio management services
  • The ability to provide self-service analysis
  • The ability to scale resources when needed
  • A lower cost of ownership within a secure environment

API ever after

In particular, there is great value to be found in Application Programming Interfaces (APIs). An API call is essentially a code written by a programmer that allows one system to communicate with another, and sets rules as to the nature and limits of that exchange.

APIs act as railway junctions for data, allowing it to move in a managed and tightly-governed basis between systems. The idea is to integrate third parties’ clouds into the asset manager’s internal solutions via APIs. The APIs then allow various pieces of software, both cloud based and on-premise to communicate with each other, letting data move between the various parties in a timely and seamless fashion, and according to the rules placed around their movement.

Cloud-based applications supporting APIs allow for data to be extracted into central repositories such as a data warehouse or data lake. This data can then be used in other processes. This helps remove point-to-point data silos and enables a subscription and publication model. Some applications produce data and publish it to the data warehouse, while others need that data so subscribe to it for further reporting or analysis.

This means that new systems can talk to the old ones, so the same data sets can be shared across the firm. The result?

  • Better reporting
  • Closer alignment
  • Improved performance

An example of this is using an API to call data from a central system that contains signed off, book of record performance and risk data. This data can be dynamically imported to Excel and kept up to date ready for additional analysis. We know Excel is a vital tool for many asset managers, but by changing the way the data is managed and controlled, operational risk can be reduced and value gained from having higher quality data ready to analyse and share.

A report by Deloitte, The future of investment management: Open application programming interfaces, underscores this: “In the case of the investment management industry where market data is the lifeblood, getting accurate and timely market data in the requisite format continues to be a time-consuming and evasive process. However, these businesses now have the option of linking their systems with external data feeds, which provide real-time, historical, and reference data without the need for complex in-house data management systems,” says the report.

A match made in the cloud

There’s little point in leveraging the cloud without also taking advantage of the ability to better manage data through API access. Most people in the asset management industry understand that better data management is the key to performance. The more efficiently data can be managed and shared, the better an asset manager’s downstream processes will be. APIs allow data to be seamlessly shared between applications, and be kept secure from those who don’t need access. Because it’s programmatic, it can be an automated process and a more efficient way of sharing data. 

Key takeaways

  • Excel is no longer fit for data management tasks – it should be used for analysis of ‘clean’ data from central sources
  • Data management is key to analysis and reporting. It’s the lifeblood of the middle office
  • APIs allow data to be programmatically imported from central sources
  • APIs also allow new systems to talk to old ones, so the same data sets can be shared across the firm without a wholesale technology lift-out
  • The need to format and reformat data between systems is removed, creating a more trustworthy data set

Learn more about how the middle office can operate more efficiently in our complimentary eGuide, How can we automate and scale the middle office?