Flow Analytics for Fintech Series

Introduction

Performance measurement involves multi-dimensional data analysis over a range of dimensions including time periods, sectors, countries, and asset classes, to name a few. It also requires the integration data from multiple sources such as custodians, account aggregation services and third-party research providers. Flow Analytics' robust data integration, in-memory hypercube expression evaluation, and reporting capabilities combine to provide a provide a robust performance measurement solution for wealth management firms.

This post shows how to build a simple data automation workflow that integrates and aggregates multiple sets of data, calculates monthly and quarterly investment returns, and generates several report elements to display those returns. The data and workflow demonstrated below are all available to registered users of Flow Analytics. Topics discussed include:

• An overview of Flow Analytics workflows
• Workflow result types
• The results we want this workflow to generate
• The data needed to generate the results
• A step-by-step overview of the Evaluate Portfolio Performance workflow
• Viewing workflow results and exporting data

Note - The example workflow and data shown below can be accessed from the Flow Analytics portal. See the Try this Workflow Yourself section for more information.

View Workflow Results - The report results from all the Flow Analytics for FinTech Series example workflows can be viewed here: report results dashboard

What data do we need to create our required results?

To evaluate the performance of our portfolio, we'll need to combine three sets of data. First, we need a dataset containing historical account positions for the time period under evaluation. Next, we need security pricing data for the same time period and, finally, we need a set of performance benchmark data.

It is important to note that the data required to generate this report could be in one, two, or even four more datasets and, if this were the case, it would have little impact on the workflow steps. I've used the datasets shown below only because it is what I most often see.

Daily Portfolio Positions Data

To keep this example simple, I'll use one year of settled account positions and ignore accruals and other transactions. Factoring in this activity is manageable but would complicate things a bit. There are only three required data points: a security identifier, i.e., ticker or CUSIP, a date, and the number of settled shares held in the portfolio.

Figure 3 shows the daily positions data.

Daily Pricing Data

We must calculate the portfolio's total daily market value for each trading day during the performance evaluation period, so we'll need a dataset containing historical prices for each portfolio position during this period. The historical pricing data must include a pricing date, one or more security identifiers for each portfolio holding, and a per share closing price as of the pricing date.

Figure 4 shows the daily pricing data.

Benchmark Data

For our benchmark data, we'll use the Russell 1000 index. I realize this is not correct from a performance attribution standpoint but, once again, this will simplify things.

For more information regarding index return calculation, see my Calculate Index Returns blog post.

Figure 5 shows the Russell 1000 index data.

I've not only developed performance measurement software but have also worked with many of the solutions currently available. Therefore, I have a reasonable understanding of the challenges and complexities wealth management firms face when selecting and implementing a solution. Finding a solution that passes the Goldilocks test (not too complicated or too simple but just right for the requirements) can be a daunting task.

Performance Evaluation Workflow Overview

Actions 1 to 4: Load and join position history, pricing and benchmark data

Actions 5 to 8: Build hypercube to group positions by day, calculate daily total market value, slice outdaily market values.

Actions 9 to 13: Join benchmark data, calculate daily portfolio returns, build hypercube and calculate subperiod returns.

Actions 14 to 17: Generate report results

Figure 6 monthly return data computed by workflow.

Actions 1 to 4

The first three workflow actions load the historical positions, daily prices, and benchmark data. Once the data is loaded, the join action (4), looks up the daily price value for each daily portfolio position from the security prices data.

Figure 7 shows the position history working data created by running actions 1 to 4.

Actions 5 to 8

These actions first total daily market values for each day of the performance evaluation period, then build a hypercube from the in-memory position history data using the date field as a dimension, next the daily market values are calculated using a hypercube expression evaluation action and, finally, the hypercube is filtered to extract the daily market value data.

Figure 8 shows the daily market data values generated by running actions 5 to 8

Actions 9 to 13

The first of these actions match the dates in the daily market values and Russell 1000 data to add the index values to the daily market values. The next action builds a hypercube from the daily market values dataset with two dimensions: quarter and month. Then an expression evaluation action calculates the monthly and quarterly portfolio and benchmark returns within each hypercube dimension. Finally, a pair of filter actions extract the monthly and quarterly return data from the hypercube.

Figure 9 shows the monthly and quarterly returns generated by running actions 9 to 13

Actions 14 to 17 - Workflow Results

Workflow action 14 is a data visualization result action. When this action runs, it builds a data visualization result from a specified dataset or hypercube and saves it to the Flow Analytics portal. The Create Data Visualization Result dialog is used to configure this action as shown in figure 10.

Figure 10 shows the dialog used to configure a Create Data Visualization action.

Figure 11 shows the data visualization result generated by workflow in the Flow Analytics portal.

Flow Analytics workflows can generate a wide variety of results including datasets, hypercubes, tabular reports, grouped reports, pivot tables, tables, KPIs, summary items, gauges, RDL reports, data visualizations, hypercube visualizations, filterable cross-tabulations, grouped cross-tabulations, filterable XY pivot tables, and filterable XYZ pivot tables. When a workflow generates any of these of these results, it saves them to the Flow Analytics portal from where you can share them with others or distribute them via custom dashboards.

Workflows can also serve as a data integration hub gathering data from multiple sources, transforming and enriching it, then exporting the data to other applications.

Exporting Working Data to a Positional File

For many reasons, wealth management firms continue to rely upon legacy software applications for mission-critical processes. These applications often require specific file formats to import, so integrating the flow of data to these applications is often a challenge that requires a custom programming effort. Positional files are one such format.

Flow Analytics workflows include positional file import and export actions to facilitate the integration of data with legacy applications.

Figure 12 shows the Export Working Data to Positional File configuration dialog.

Summary

---

This post is part of the Flow Analytics for FinTech series; it discussed how to build a data automation workflow to evaluate the monthly, quarterly, and total returns a portfolio. We started with a brief overview of data automation workflows; then we discussed the results we want to this workflow to produce followed by an examination of the data required to generate those results. Finally, it provided a step-by-step illustration of the Evaluate Portfolio Performance workflow.

---

To Try this workflow yourself