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
Building a Workflow to Deliver Customized Portfolio Holdings Reports
Figure 1: Three Fundamental Workflow Tasks
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.
Figure 3: Client Holdings 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.
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.
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.
Figure 6: Custom portfolio holdings report workflow viewed in the Cloud Connect design environment
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.
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.
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:
- 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.
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.
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.
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.
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.
Figure 13: Configure the Export Data to Excel action
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.
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.
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.
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 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.