2018-10-08

Flow Analytics for Fintech Series

Create Custom Reports with Flow Analytics


Introduction

As highly trusted custodians of client data, wealth managers are well positioned to deliver personalized, data-driven services that meet the individual needs of their all clients. Data automation workflows provide a fast and flexible way to gather, combine, and process multiple data sources for delivery of highly personalized client content.

Most wealth management reporting solutions provide customized reporting capabilities via report designers that embed data connections, layout choices, and data processing logic in fixed a report template. These report designers offer limited connections to a fixed set of data sources with specific field names, data types, and formats. Therefore, once a report is designed and put into production, adding, removing, or modifying data sources and processing logic becomes extremely difficult which, in turn, limits the ability of firms to deliver genuinely personalized, data-driven services.

Data automation workflows provide maximum flexibility for data collection, processing logic, and output options which significantly enhances and expands a firms ability to deliver data-driven client services. This post discusses and demonstrates a basic workflow that loads several data sources, performs the required calculations and transformations, then outputs a report along with several enriched datasets. The topics discussed include:

  • A brief overview of data automation workflows
  • The types of workflow results we can produce
  • The report result we want this workflow to create
  • The data needed to build our report
  • How the workflow actions create the report
  • Other reporting and data output options
  • Automating report generation and distribution

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 Deliver Customized Portfolio Holdings Reports


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.

Here are some results you can create with a Flow Analytic's workflow:

  • Enriched data for export to Excel, Google Sheets, local or remote files, or to update database tables,
  • Data supplied to other applications via file exports or API calls,
  • Dashboard report elements such as tables and charts,
  • Emails with or without attachments to provide notifications or distribute information.

Before building a workflow, we should decide what results we wish to create as this determine the data we need and the required actions.

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

Our goal is to generate a report that groups a client's portfolio positions by asset class, asset type, and equity sector with totals and sub-totals for each group.

Flow Analytics provides many options for generating this type of information. I'll discuss several these. First, we'll look at the use a create grouped report action which builds a report and saves it to the Flow Analytics Portal. Next, I'll display a report generated from report definition language action and finally, we'll look at how to export report data to Excel.

I have shared this report in a public dashboard which can be viewed here: Client Reporting Dashboard

Figure 2 shows the portfolio holdings report generated by the grouped report action.

portfolio holdings report workflow required result
Figure 2: Portfolio Holdings Report
What data do we need to create our required results?

To generate our portfolio holdings report, we'll need to combine three sets of data: a data set containing client holdings as of a particular date, asset pricing data as of the same date, and a set of asset classification data that we'll use to group our report. To simplify things, I've made these datasets publicly available within the Flow Analytics portal.

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.

Client Holdings Data

There are many possible sources for client holdings data. The most common data sources are a custodial data feeds, such as the Schwab PortfolioCenter nightly file, or account aggregation services like Morningstar ByAllAccounts. I've included the minimum number of required data points although we could use either Ticker or CUSIP and if these are not available, we could use ISIN, SEDOL, or a custodial id so long as our pricing data uses the same identifier.

Figure 3 shows the client holdings data.

position data
Figure 3: Client Holdings Data
Pricing Data

Advisory firms also have many potential sources of pricing data. Typical data sources include custodial data feeds, account aggregation services, and third-party market data suppliers. At a minimum, the pricing data must contain a valuation date that matches the portfolio position's as-of date (or date and time), a per unit or per share market price or asset valuation amount, and one or more security identifiers such as ticker or CUSIP.

Figure 4 shows the security pricing data.

pricing data
Figure 4: Security Pricing Data
Asset Classification Data Map

The asset classification categories adopted by wealth managers are closely related to their money management strategies and style. While some managers maintain their classifications in-house, others rely on third-party financial data providers such as Morningstar or Standard and Poors. In this case, we are assuming the firm maintains their asset classification data in-house. Here individual asset identifiers are mapped to an asset class, an asset type, and Standard and Poor sector and industry designations.

Figure 5 shows the asset classification mapping data.

asset classification mapping data
Figure 5: Asset Classification Mapping Data

Walkthrough - The Custom Portfolio Holdings Report Workflow


As a software developer who spent over a decade developing wealth management solutions, I've written thousands of lines of code to do what this workflow does in nine steps. Therefore, I have a great deal of personal excitement and appreciation for the ease and simplicity of this solution. I doubt most readers will share my enthusiasm, but I urge you to spend some time and gain a little understanding. I include these details to show the advantages of data automation and to cut through the hype to demonstrate its simplicity and power.

Workflow Actions Overview

1: Load portfolio position data

2: Load security price data

3: Load asset classification data

4: Join security prices to positions

5: Join asset classfications to postions

6: Build hypercube

7: Evaluate expressions

8: Generate grouped report

Figure 6 shows the Custom Portfolio holdings report workflow viewed in the Cloud Connect design environment.

workflow steps
Figure 6: Custom portfolio holdings report workflow viewed in the Cloud Connect design environment
Workflow Explanation
Actions 1 to 5

The first three actions of this workflow load the required position, price, and asset classification data. Once the datasets are in working memory, the pricing data is joined to the position data using the key fields Ticker and CUSIP. Likewise, the asset classification data is joined to the position data using the same keys. After the join actions run, each position field has a closing price and a set of asset classifications.

Figure 7 shows the position working data after running actions 1 to 5.

report position data after running actions 1 to 5
Figure 7: Position working data after running actions 1 to 5
Actions 6 and 8

The Build Hypercube Action

Hypercubes are an essential feature of Flow Analytic's workflows; they enable you to group a dataset by any number of dimensions then, using an expression evaluation action, apply various mathematical and statistical operations to calculate required values such as totals, averages, and variances.

To configure the build hypercube action, we specify in the underlying dataset in the Working Data drop-down, then select check off the dimensions we want to include and, finally, name the hypercube result. When this action runs, it creates a newly named hypercube in working memory.

Figure 8 shows the action configuration dialog for the Build Hypercube action.

build hypercube dialog
Figure 8: Configuration dialog for the Build Hypercube action

The Expression Evaluation Action

Expression actions enable in-memory evaluation of various user-defined functions including:

  • Arithmetic
  • Statistical
  • Text and String
  • Date and Time
  • Time Series

We use the Build Expression dialog to configure and add an expression evaluation action as shown in Figure 6. To add an expression, you first select a working data collection, then the expression type and operation to evaluate and finally the required inputs. For example, the first highlighted expression in Figure 6 multiplies the values CurrentQuantity and ClosingPrice in the Positions dataset to calculate the CurrentValue for each position. The next highlighted expression performs a sum operation on the CurrentValue data point in Positions to calculate totals for each dimension of the Position Cube hypercube we added in step 6.

Figure 9 shows the configuration dialog for the Expression Evaluation action.

expression dialog
Figure 9: Configure the Expression Evaluation action

The Grouped Report Action

After the build hypercube and expression evaluation actions run, we have all the data needed to generate our client holdings report. For this, we use the Hypercube Group Report dialog to configure the grouped report action as shown in figure 7.

We first select the hypercube we'll use to build the report along with a report title and name. Next, we select the hierarchical report groupings from the underlying hypercube dimensions and, finally, the report columns and group totals under the Add Report Columns section.

Figure 10 shows the configuration dialog for the Build Grouped Report action.

configure hypercube grouped report
Figure 10: Configure the Build Grouped Report action

Viewing the Report in the Flow Portal

When the grouped report action runs, it creates a new report and automatically saves it to the Flow Analytics portal. You can view the report by logging into the portal and opening the Results -> Reports and Charts menu in the left-hand sidebar. After opening the report, you can print it, distribute it, or add it to a shareable dashboard.

Figure 11 shows the portfolio holdings report viewed within the Flow Analytics portal.

view report in portal
Figure 11: The portfolio holdings report viewed within the Flow Analytics portal

Other Reporting and Data Output Options


While grouped reports are flexible and easy to create, they are only one of many results a workflow can produce. To illustrate this, I'll briefly discuss an alternative reporting action along with two frequently used data export actions.

Generating Presentation Quality Reports with RDL

Another report generation option is the RDL Report action. This action uses the Report Definition Language, RDL, to enable straightforward design and delivery of precisely formatted custom reports. RDL provides complete control over the design and placement of all reporting elements such as tables, charts, headers, footers, and hierarchical report elements.

Figure 12 displays a custom RDL report produced by a Flow Analytics workflow.

rdl report
Figure 12: A custom RDL Report generated by Flow Analytics
Exporting Your Report Data to Other Applications

One of the main goals of data automation is the flexibility to augment rather than replace existing technology investments. Flow Analytics workflows provide maximum flexibility to gather data from and deliver to, any data source or destination. To illustrate this, I'll briefly introduce two data of the many available data export options below.

Example 1 - Export Data to Excel

Given the popularity of Microsoft Excel, it should be no surprise that this is one of the most frequently used export actions. Adding an Excel Data Export action is easy, you designate the dataset to export, or check Export All to output all datasets, then specify the file destination. When this action executes, it will export the data or datasets. If required, you can add subsequent actions to email the file, transfer it via FTP, or upload it to a cloud document repository such as Dropbox.

Figure 13 shows the configuration dialog for the Export Data to Excel action.

export data to excel
Figure 13: Configure the Export Data to Excel action

Workflow Automation


Workflow Deployment

While workflows are designed and tested in the Cloud Connect integrated development environment, they are stored and managed from within the Flow Analytics portal. After building a workflow, you can deploy it in two ways: a cloud-based, virtual environment running on Azure or via remote agents installed at a user-designated location.

Using a Trigger to Automate a Workflow Execution

For example, if we want to use our reporting workflow to generate a quarterly client holding report, we could use a monthly trigger scheduled to run every three months at 1:00 am on the first Monday of the last week of the third month. The Add Monthly Trigger dialog displayed in Figure 11 shows how to configure this. Once we add this trigger, we can use the manager workflows page within the Flow Analytics portal to monitor workflow.

Figure 15 shows the Flow Analytics portal Workflow Management page.

workflow managment
Figure 15: The Flow Analytics portal Workflow Management page

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 generate a custom portfolio holding report. It starts with a brief overview of data automation workflows. It then examined the report result we want to this workflow to produce followed by an examination of the data required to generate that result. I presented a step-by-step illustration of the Evaluate Portfolio Performance workflow followed by a discussion of alternative an report generation option. Several options for exporting the working data produced by the workflow were presented along with a brief discussion of workflow automation.

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.