2018-10-08

Flow Analytics for Fintech Series

Asset Allocation Analysis and Reporting


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

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 Analyze and Monitor a Portfolio's Asset Allocation


What is a Flow Analytics workflow?

A Flow Analytics workflow is a sequence of user-defined actions that, together, perform three fundamental tasks:

  1. Integrate data from one or more data sources
  2. Execute user-defined actions to process the data
  3. Use the processed data to generate a set of needed results

Workflows can generate a wide variety of results some of which include:

  • Reports and dashboard elements such as tables, charts, and data summaries
  • Enriched data for export to files, databases, spreadsheets, or third-party applications
  • Notifications and alerts
  • Emails
fintech series workflow diagram
Figure 1: Three Fundamental Workflow Tasks
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
asset allocatin vs model chart
Asset Allocation vs. Model
strategic asset allocation
Strategic Asset Allocation Model
asset allocatin vs model table
Asset Allocation vs. Model Table
asset allocation by asset type drill down
Asset Allocation by Asset Type Drill-Down

Figure 2: Workflow Results
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 data
Figure 3: Client 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.

pricing data
Figure 4: 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 classification data
Figure 5: 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.

asset classification data
Figure 6: The Asset Allocation Model

Walkthrough - The Analyze Asset Allocation Workflow


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

analyze asset allocation workflow steps
Figure 7: Analyze Asset Allocation Workflow Steps
Workflow Explanation
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.

analyze asset allocation model comparison
asset allocation reports steps 1 to 6
Figure 8: Data loaded by actions 1 to 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.

analyze asset allocation model comparison
analyze asset allocation join actions
Figure 9: Positions Data After Running Actions 4 to 6

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.

analyze asset allocation step 6
Figure 10: Expression Evaluation Action Configuration Dialog
analyze asset allocation steps 4 to 6 result
Figure 11: Workflow Actions 4 to 6 Results

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.

analyze asset allocation model comparison
analyze asset allocation model comparison
Figure 12:Build a Hypercube to Aggregate the Positions

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

analyze asset allocation model comparison
analyze asset allocation model comparison
analyze asset allocation model comparison
Figure 14: Using the Expression Builder Dialog to Compute Target Variances
analyze asset allocation model comparison
Figure 15: Actual Asset Allocation vs. Target Allocation
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.

chart result dialog
chart result dialog
chart result dialog
Figure 14: Configure Chart Result Dialog

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 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.

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.