2018-10-08

Flow Analytics for Fintech Series

Evaluate Portfolio Performance with Flow Analytics


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

Watch Video



about the author
about the author icon
Ken McLaughlin

Ken is a managing partner with 4D-IQ and the lead developer of Flow Analytics. Before 4D-IQ, he co-founded Financial Technology Integrators where he led the development of InvestorsView, one of the first SaaS portfolio management, trading, and rebalancing platform for wealth managers. Before starting 4D-IQ, he was with Blackrock Financial where he developed risk analytics and optimization software.

Building a Workflow to Evaluate Portfolio Performance


What is a workflow?

A workflow performs three fundamental tasks: it loads data from one or data sources, then applies a sequence of actions to the data, and generates a set of results, for example:

  • Enriched data exported to Excel, Google Sheets, a local file,
  • Database updates or inserts
  • API calls that supply data to other applications
  • Reporting elements for display in a dashboard
  • Emails that distribute reports or provide notifications

Before building a workflow, we must, therefore, decide what results we wish to create.

fintech workflow diagram
Figure 1: Three Fundamental Workflow Tasks
What results do we want to create?

Since our goal is to view and evaluate the performance of a portfolio over time, we'll require the following results:

  • Quarterly returns vs. Russell 1000 table
  • Quarterly returns chart
  • Monthly returns vs. Russell 1000 table
  • Monthly returns chart

Figure 2 displays the table and chart results created by this workflow.

performance reporting required results
performance reporting required results monthly returns
Figure 2: Quarterly and Monthly Returns vs. Benchmark
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.

position history data
Figure 3: Daily Portfolio Position Data - 04/01/2017 to 03/31/2018
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.

historical price data
Figure 4: Daily Pricing Data - 04/01/2017 to 03/31/2018
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.

russell 1000 index data
Figure 5: Benchmark Data - Russell 1000 - 04/01/2017 to 03/31/2018

Walkthrough - The Portfolio Performance Evaluation Workflow


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.

portfolio performance evaluation workflow steps
Figure 6: Monthly return data computed by workflow
Workflow Explanation
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.

portfolio performance position history actions 1 to 4
Figure 7: 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

portfolio performance actions 5 to 8
Figure 8: Daily portfolio market values
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

portfolio performance actions 9 to 13
Figure 9: Quarterly and monthly returns
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.

portfolio performance data visualization dialog
Figure 10: Create Data Visualization Dialog
portfolio performance quarterly returns chart
Figure 11: Data Visualization Result - Quarterly Returns

Other Output Options


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.

positional file export dialog
Figure 12: Export the daily market values to a positional file

Can't Find a Solution? Challenge Us


Do you have a challenging manual task or process that is inefficient, takes more time than it should, and you would like to automate, but you've been able to find a solution. Tell us about it, and we'll design a data automation workflow to overcome your challenge. Just go to our contact us page and either call us, send us a message with the subject "I have a challenge," or use the email link.

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.


sample workflows icon

This workflow is available to registered users of Flow Analytics. To try it yourself simply log in to the Flow Analytics portal then click the workflow managment down arrow to open the Add Sample Workflow dialog. Click on the Get Workflow link next to the Custom Portfolio Holdings Report workflow listed in the dialog. The workflow will be added to your Flow Analytics account. You can then launch Cloud Connect designer to view and run this workflow.