Inside the PMS: Data Consolidation Step by Step

External wealth managers, especially those working for multiple clients, process huge amounts of data each day. The assets under their custody are spread over several dozen banks in Switzerland, Monaco, Dubai, UK and other countries.

To analyse their portfolios performance, asset managers need to retrieve and unify data from multiple online banking systems. Even with a medium scale of business, it consumes plenty of time when done manually. I described this problem and the threats of manual data consolidation in my previous blog post.  

‍

Portfolio Management System as an Answer

‍

As we say it at WealthArc, wealth management can, and should, benefit from digital innovations. With new technologies, there’s no need for performing data consolidation manually. Proper Portfolio Management Software can do it fully automatically, which gives the time back to the Asset Manager.  

Portfolio Management Software uses special APIs, i.e. application programming interfaces, that connect them with the electronic systems of custodian banks. They allow External Asset Managers to retrieve their clients’ data and bring them to their wealth management platform.  

But before a wealth manager sees it, the data needs to be unified. Banks deliver their data in diverse formats and differently, which hampers effective analysis.  

Data unification is one of the most important tasks of a Portfolio Management System. Let’s see what actually happens during this process.

‍

Portfolio Data Formats

‍

Most of custodian banks recognize the need for an automated delivery of portfolio data to PMS providers. It’s typically done in the morning (between 4 and 9am).  

The data contains:

• cash and security positions from an end-of-day portfolio snapshot,  
• cash and security transactions from the last business date.

Unfortunately, there’s no single standard for the data format and delivery protocols. Most custodian banks send their data in the CSV format via SFTP servers using a proprietary data format, but it’s not a rule. The number of files delivered varies from 2 up to 60.  

Data format can vary even within a single bank. Multinational corporations process their data in diverse branches using local systems, so the portfolio data is not unified. For instance, WealthArc has integrated data from over 50 international branches of 43 banks, and each of them uses a different format!

PMS Data Mapping  

‍

To facilitate portfolio performance analysis, the data visible in Portfolio Management Software needs to be unified.

‍

The unification process consists of:

1. transformation to a single format (at WealthArc, from SWIFT, JSON, etc. to CSV),
2. mapping of the attribute (portfolio name, instrument name, ISIN, etc.),
3. mapping of asset classes, regions, and sectors for positions,
4. mapping of transaction types for transactions,
5. supplementation of market data (price, maturity date, coupon rate, etc.) from an external provider (at WealthArc, it’s Refinitiv),
6. calculating values in additional currencies (for aggregation purposes).

Proper mapping is necessary to achieve high quality of data. It’s especially important because various instruments and transactions may be represented differently.

A simple example: FX forwards are sometimes represented in data as 2 legs (one short, one long), and sometimes as a single leg with net value. By the data unification process, we make sure that all FX forwards from any custodian bank are represented in the same way.  

Data mapping should also include data updates. What happens when a custodian bank charges you more than supposed to? You file a complaint and wait until the fee is adjusted. So how will this be reflected in your data? As a transaction update? Or two separate transactions (old fee reversal + new fee)?  

How Fast Banks Deliver Their Data

‍

It usually takes 1-4 weeks from the request to a bank before a PMS provider receives the first data. It usually takes longer with the EBICS protocol as it requires sending written security confirmations by post.  

At WealthArc, we distinguish 2 cases. If the data mapping process for a given bank is already established, the data appears on our platform within 48 hours.

In other cases, there’s a need to develop a new data mapping process by a Quantitative Data Analysts. It usually takes 4-6 weeks. New data mappings are checked against WealthArc standards and tested for correctness.  

Once the data mapping for a given bank is ready, the daily data feed is established, and portfolio data starts flowing to our platform.

‍

What Happens Next

‍

Once we establish a new API connection, and the given bank transfers the desired data daily, there’s still a need to monitor the transfer process. Proper monitoring guarantees high data quality.  

For this purpose, we’ve established multiple automated data monitoring and reconciliation mechanisms at WealthArc.

If you want to learn more about our technology, visit our product page. We explain there in detail how our platform performs data consolidation and what other tasks it may automate.

‍