How can family
offices overcome the
data management
challenge? 

Transparent, meaningful, efficient, timely reporting is central to family offices’ value proposition. Strong risk-adjusted performance aligned with family members’ priorities may be the primary allure of a family office. But almost as important is communicating that performance and the value of holdings to families.

Ultra-high-net-worth (UHNW) families typically have a plethora of accounts, portfolios, trusts and managers. While they may receive detailed reports on parts of their holdings, they often lack an aggregated picture of their allocations, performance and risks. Consolidated reporting is the answer.

But delivering that consolidated reporting is hard. Portfolios tend to be complex, spanning multiple asset classes, geographic markets and investment structures. Portfolio information may be spread across a large number of third-party banks, prime brokers, administrators and private sources – information that must be captured and normalised for the consolidated reporting to occur. And the end reports must be accurate, comprehensive and delivered to ever-tighter deadlines.

So how can family offices deliver the consolidated picture families want?

3 steps to consolidated reporting

Rich, consolidated reporting depends on three key steps: data sourcing, transforming and storing the data, and enrichment.

1. Data sourcing

Data sourcing poses the first challenge. Data sources for the kinds of complex, multi-asset class portfolios family offices typically manage are fragmented and comprise disparate, non-standardised datasets.

• To create a consolidated reporting service, family offices need to know:
• What assets and liabilities the family has.
• The valuation of those assets and liabilities at a given point in time.
• What transactions have occurred in the portfolios over a given time period.

How family offices opt to obtain that data varies.

Manual process

The most common method is a wholly manual process, where an analyst or other team member will access the different banking portals to download the data in CSV, Excel, PDF or whatever other format is available.

That work is extremely time-consuming. The data is held in a variety of portals that employ a multitude of settings and are complex to navigate, introducing significant risk of manual error.

For example: if one month you generate transactions from the portal based on trade date and the next they are based on value date, it creates a dark period between the two months where some transactions have not been picked up in the reporting.

Data timeliness can also be problematic. Oftentimes the investment manager or bank won’t make their report available until 15 days or more after month-end – especially in the case of non-liquid alternative investments.

Sourcing data for operating companies and real assets adds a further layer of complexity. It is much harder to automate, since there may be no portal available to present the information. There will be much greater variety in who provides the raw information about the instruments, ranging from managers, administrators and advisors to executives at subsidiaries. And extended turnaround times are common.

Relationship managers

An alternative data sourcing option, at least for a family office’s custodied portfolios, is to obtain periodic reports from relationship managers. This is less time-consuming and manually onerous for the family office. But significant risk of manual error and delay remains. The reports will usually be bank-branded PDF documents, making extracting the information to use downstream painful. The family office will also need to manually consolidate the data in Excel or an equivalent.

Automated process

The optimal scenario is an automated process using data feeds set up with the family office’s custodian banks. Information can then be relayed via SFTPs, APIs, EBICS, etc. depending on the counterparty (SFTP is the most common, but different jurisdictions favour different protocols).

Automated data sourcing requires no manual intervention, is comprehensive, delivers data in a consistent format over time and alleviates many of the security concerns around email data transfer. Automation also allows for daily data sourcing periodicity, enabling family offices to produce more timely, meaningful reports. A daily process is almost impossible to sustain in a manual environment.

Automation does require significant IT capabilities though to set up and maintain the feeds – resources that can either be deployed in-house or by working with a third party.

2. Transforming and storing data

Whether the data is sourced manually or automatically, it will have:

• Dozens of possible file formats, such as text, CSV, Excel, PDF and XML.
• Variations in the content – such as the data within it, the order the columns are in and the identifiers used to recognise certain positions.

This disparate information needs to be transformed into a single standardised schema that can be processed systematically for the family office’s downstream reporting. And once the data is harmonised, it has to be stored.

Database solutions are available from a number of software vendors, including AWS, Microsoft Azure, Oracle and mongoDB. Having picked a vendor, the next step is to create the database using an appropriate database architecture.

The architecture determines how data will flow from the files into the database and what columns in each file need to be mapped to which database field. Data validations must be embedded throughout the process to avoid importing incorrect data by accident and polluting the database. Unique keys are essential for maintaining unicity – for instance, where two banks have an identifier in common for an instrument but use slightly different naming conventions, it needs to be mapped into a single object. Foreign keys dictate the relationships between different tables in the database.

Positions, assets, liabilities and transactions all need to refer back to a single object. Without that relationship and unicity, any downstream calculations around concentration, performance and P&L will be impaired.

Crucially, the database architecture must also be designed to take account of what the family office wants to show, and what analytics and reporting it will need downstream – for example, if it wants transparency into the funds it has invested in, or to replicate the ownership structure of the family office. Get the initial database design and structure wrong and the cost of migrating and redoing it will be considerable.

Developing the database demands a variety of skillsets. It requires a software engineer to actually build and implement the database. The IT engineer is unlikely to possess sufficient knowledge of the financial datasets they are working with though, so the software developer will in turn need help from financial data experts to provide the specifications for what needs to be created. A data engineer may also be required downstream to enrich the raw data received from the different providers with ancillary data sources.

3. Data enrichment

Enrichment adds colour and granularity to the raw data custodians provide by appending it with descriptive metadata from secondary sources.

Some custodians offer nothing more than the basic data on assets/liabilities, their valuations and the transactions that have occurred. Others may include a few additional data points – such as names, some descriptions, a country or currency – but granularity is limited.

Where custodians do provide a degree of colour around the raw data, in many cases it will be subject to fair usage policies that prohibit users from programmatically extracting data fields from the files. So while the custodian file may feature information such as sectors and ratings, users are only allowed to look at it, not process it automatically.

Enrichment brings in prices, volumes, volume-weighted average prices, market capitalisations, sectors, etc., along with some more sophisticated datasets.

Enriching the data is costly. Some relatively open-source providers exist, but the datasets are limited. Using the big data vendors with strong market and instrument coverage is expensive, with the most basic licensing packages starting at around $50,000 to $60,000 a year.

Understanding what users are permitted to do with the data can also get complicated. The licence terms and conditions typically prohibit any programmatic transformations and external reporting of the data. That means a family office will be prevented from incorporating the vendor’s data in any reports it wants to make available to its families or third parties. Furthermore, the family office is not allowed to store that data – or any calculations derived from it – in its database, creating a massive problem for producing downstream consolidated reporting. Pricing is a case in point. If a data user calculates regressions and beta coefficients for some of its stocks using the data vendor’s prices, that is classified as derived data. The data licensee will not be allowed to report that information to anyone outside the organisation, or store the prices themselves or the calculations based on those prices in their database programmatically. Backdated licensing surcharges can be significant, with some running into the hundreds of thousands of dollars.

Along with the data licensing costs, different data vendor solutions have their own API endpoints. Firms will need to hire software developers with subject matter expertise to integrate those endpoints. More complex data, such as indices, ratings and ESG enrichments, increase the difficulty of integrating the vendor solution and transforming the data into the family office’s database ecosystem. The enriched information will then need to feed back into the database and link with the objects already created in there so the data can be used downstream.

Can you deliver?

Family offices increasingly aspire to deliver comprehensive, accurate and timely consolidated reports to families. And families will increasingly come to expect it. But to provide that high-quality consolidated reporting, firms first need to tackle a host of complex data challenges.

Sourcing, transforming, storing and enriching the data requires sophisticated IT engineering and financial data expertise. Such skillsets are in high demand and do not come cheap. Hefty data licence fees add to the cost.

Family offices have a choice. They can opt to hire an internal team of IT engineers and financial quants to develop and manage the necessary infrastructure. Or they can use a software-as-a-service capability to handle the end-to-end data management processes for them. What they can no longer do is ignore the growing calls for a consolidated reporting service.

About Landytech

Landytech provides web-based investment reporting solutions to family offices, asset managers and fiduciaries at a fraction of the cost of implementing and maintaining the data management and reporting function in-house. Our expert IT engineering and data teams bring years of experience in developing financial databases, reporting capabilities, risk analytics and software applications. Our simple onboarding process, automated data sourcing and high-quality reporting deliver the consolidated outputs today’s family offices need without any of the strain.