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
Building a Workflow to Analyze and Monitor a Portfolio's Asset Allocation
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 Allocation vs. Model
Strategic Asset Allocation Model
Asset Allocation vs. Model Table
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.
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.
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.
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.
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
Figure 7: Analyze Asset Allocation Workflow Steps
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.
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.
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.
Figure 10: Expression Evaluation Action Configuration Dialog
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.
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
Figure 14: Using the Expression Builder Dialog to Compute Target Variances
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.
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.
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.
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.