Flow Analytics for Fintech Series

Introduction

Asset allocation analysis and reporting are essential to the services rendered by wealth management firms. Wealth managers must aggregate, and summarize data from a variety of sources including custodians, pricing feeds, and third-party research which can be a time consuming and resource intensive. This post explains how to build a Flow Analytics workflow to automate the data integration and aggregation processes required to analyze and report on a client's strategic asset allocation. Topics discussed include:

• What a Flow Analytics workflow is
• What results we want this workflow to generate
• The datasets used to produce the results.
• The workflow actions applied to the data
• How to view results

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 results do we want to create?

The development of a workflow begins with identification of a set of desired results which helps determine the data needed and the workflow actions required.

The goal of this workflow is to provide information that facilitates understanding and analysis of a client's current asset allocation. Therefore, it will generate the following set of summary tables and charts:

• An Asset Allocation vs. Model Chart
• A Strategic Asset Allocation Model Chart
• Asset Allocation vs. Model Table
• Asset Allocation by Asset Type Drill-Down

What data do we need to create our required results?

To produce these results, we need three initial sets of data: (1) client positions as of some date, (2) security prices for each asset, (3) asset classification mappings, and (4) an asset allocation model.

Here is a brief description of each dataset:

Client Position Data

Here, the critical data points are the quantity, ticker, and CUSIP; I've included the other data points to provide a more realistic example.

Figure 3 shows the position data.

Position Pricing Data

In this dataset, the primary data points required are the closing price and the pricing date which must match the date in the positions data.

Figure 4 shows the pricing data.

Asset Classification Mapping Data

This dataset maps each client position to a set of asset classification values.

Figure 5 shows the asset classification mapping data.

Asset Allocation Model (Targets)

This dataset contains the client's target percentages for each asset class.

Figure 6 shows the asset allocation model.

It is important to note that this workflow is just one of the many ways we could combine, configure and sequence actions to generate the results we need. Also, I've tried to make this workflow as realistic as possible without making it too complicated. In the explanation provided below, I've grouped related workflow actions.

Workflow Actions Overview

Actions 1 to 3: Load required datasets

Actions 4 to 6: Actions 4 to 6 include join actions that add the asset classification and pricing data to the position data and an expression evaluation action that calculates required values.

Actions 7 to 13: Build hypercube to aggregate and compare data

Actions 14 to 18: Generate results

Actions 1 to 3

The first three workflow actions load the Positions, SecurityPrices, and AssetClassificationMap datasets into the workflow's working data memory.

Figure 8 displays the datasets loaded by actions 1 - 3.

Actions 4 to 6

To group the position data by asset class and asset type, we need to add the asset classification values in the AssetClassification dataset to the Positions dataset. The first join action performs this task. The second join action adds the prices from the SecurityPrices data to the positions.

Figure 9 shows the portfolio positions data after running the join actions.

We need to calculate the market value and weight of each position. We use an expression evaluation action to do this.

Figure 10 displays the Expression Builder dialog.

Figure 11 shows the results of running actions 4 to 6.

Actions 7 to 13

Now that we've calculated the market value and weight of each position, we need to aggregate these values by asset class and asset type. When the hypercube build action runs, it creates a new hypercube dataset with dimensions asset class and asset type. Next, an expression evaluation action calculates the total market value and weight of each asset class and asset type group.

A filter action slices the asset allocation data from the hypercube. We then need to compare the actual asset allocation to the model. A compare data to model action to performs the comparison and saves the result to a new working dataset called AssetAllocationVsModel as shown in Figure 13.

Finally, we use another expression evaluation action to compute the actual asset allocation's variances to the model.

Figure 14 displays how this action is configured in the Expression Builder dialog

Figure 15 shows the actual vs. target allocation data

Actions 14 to 18 - Generate Results

Finally, actions 14 to 18 generate the required table and chart results. As each action executes, it creates either a table or chart result which is then automatically saved to the Flow Analytics portal.

Summary

This post is part of the Flow Analytics for FinTech series. It discussed how to build a data automation workflow to analyze and report a client's actual asset allocation versus a strategic model. It started with a brief discussion of Flow Analytics workflows. Next, an explanation of the required workflow results was presented followed by an examination of the data needed to generate those results. It then provided a step-by-step overview of the actions executed by the workflow and, finally, looked at how to produce the results.

To Try this workflow yourself