2018-10-08

Flow Analytics for Fintech Series

Analyze and Monitor Sector Exposures with Flow Analytics


Introduction

Allocating equities to meet a set of target sector weights is a common approach to equity portfolio construction and management. Portfolio managers often use the Standard and Poor's Global Industry Classification codes to classify and allocate individual equity positions within a portfolio. These codes group companies into one of eleven sectors based on the company's primary market, such as consumer discretionary, consumer staples, energy, or health care.

gics sectors
Standard and Poors GIC Sectors

Once a portfolio's positions are allocated to match a set of target weights, their deviation from those weights must be managed and controlled over time. This post presents an example data automation workflow designed to analyze and monitor a portfolio's sector exposures. The topics discussed include:

  • A brief examination of Flow Analytics workflows
  • The types of workflow results we can generate
  • The workflow results we want this workflow to create
  • The data needed to produce our results
  • How the workflow actions generate the results
  • Using this workflow for continuous portfolio monitoring

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 Sector and Industry Exposures


What is a Flow Analytics 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, for example:

  • Enriched data exported to Excel, Google Sheets, a local file,
  • Database updates or inserts
  • API calls that supply data to other applications
  • Reporting elements for display in a dashboard
  • Emails that distribute reports or provide notifications

Before building a workflow, we should decide what results we wish to create as this will help determine the data workflow actions will need to generate those results.

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

A Flow Analytics workflow can generate a wide variety of results, some of which include datasets, tabular and grouped reports, tables, KPIs, data summaries, RDL reports, and data visualizations. When a workflow generates any of these of these results, it saves them to the Flow Analytics portal from where you can share them with others or distribute them via custom dashboards.

Since our goal is to view and analyze an equity portfolio's sector and industry exposures, we will generate a tabular report and accompanying chart to display the actual sector market values and allocations, the target sector weights, and the actual vs. target percentage and dollar variances.

Figure 2 shows the results created by this workflow.

sector vs model table and chart
Figure 2: Sector vs. Model Table and Sector Weights vs. Model Chart
What data do we need to create our required results?

To produce these results, we need three initial sets of data: the client's portfolio positions as of some date, security prices for those holdings as of the same date, and data to assign a sector and industry group to each holding.

Sources of this data may include a custodial data feed, such as the Schwab's Daily Custodial Data Download, or data from an aggregation service, i.e., MorningStar's ByAllAccounts service. This workflow uses a load data actions to get the required data from the Flow Analytics portal. Flow Analytics can easily import data from any local or remote source, so we could easily replace the Load Data actions to get data from another source.

Portfolio Position Data

This dataset includes the following data points:

  • Date
  • AccountID
  • Ticker
  • CUSIP
  • Description
  • Current Quantity

Only two data points are required to produce our results: the number of shares held, the CurrentQuantity data point in this dataset, and a security identifier, in this case, we are using ticker and CUSIP.

Figure 3 shows the portfolio positions data.

position data
Figure 3: Portfolio Position Data
Pricing Data

This dataset includes the following data points:

  • Date
  • Ticker
  • CUSIP
  • Description
  • ClosingPrice

There are many possible sources of pricing data, some of these 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 position data's as-of-date and a closing price per share as of that date.

Figure 4 shows the security price data.

pricing data
Figure 4: Pricing Data
Asset Classification Mapping Data

This dataset includes the following data points:

The asset classifications chosen by wealth managers are closely related to their money management strategy and style. Each holding within our position data is mapped to a unique asset classification within the asset the asset classification dataset. While some managers maintain these 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 asset classification data is maintained in-house.

Figure 5 shows the asset classification mapping data.

asset classification data
Figure 5: The Asset Classification Mapping Data
Sector Targets (Model) Data

We want to compare the actual sector market values and weights of our equity portfolio to a set of target weights. The Sector Model dataset contains these weights.

Figure 5 shows the sector targets data.

sector exposure targets
Figure 6: Sector Targets

Walkthrough - The Analyze Equity Sector Exposure 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

Actions 1 to 4: Load required datasets

Actions 5 to 7: Join datasets then apply filter to get common stocks

Actions 8 to 12: Build hypercube to aggregate and compare data

Actions 13 and 14: Generate results

sector exposure workflow
Figure 7: Analyze Sector Exposure Workflow
Workflow Explanation
Actions 1 to 4

The first four workflow actions load the position, price, asset classification, and sector targets into workflow's working data.

Figure 8 shows the working data loaded by action 1 to 4.

sector exposure workflow steps 1 to 4
Figure 8: Data loaded by actions 1 to 4
Actions 5 to 7

We need to apply the correct security classification to each of our positions. The first join action uses the ticker and CUSIP values to look up the correct asset classification from the asset classification map working data into the positions data. Likewise, the next action looks up each position's price from the security price data. Finally, a filter action is applied to remove the non-common stock holdings from the position data.

Figure 9 shows the position data after actions 5 to 7 run.

sector exposure workflow steps 5 to 7
Figure 9: Positions data after running actions 5 to 7
Actions 8 to 12

First, a build hypercube action creates a cube named Sector Analysis Cube, then an expression evaluation action runs to calculate the position and sector market values and weights.

By way of explanation, Figure 10 shows the expression builder dialog with the two expressions highlighted. The first highlighted expression shown multiplies the CurrentQuantity and ClosingPrice values and saves the result to a new data point CurrentValue. The second highlighted expression sums the CurrentValue within each hypercube group and saves the result as a new total value within each sector grouping.

After the expression evaluation runs, a filter action extracts the sector groups into a new working dataset named Sectors, then a lookup action gets the target sector weightings from the Sector Models dataset and adds them to the Sectors data. Finally, another expression evaluation action runs and calculates the actual vs. model variances.

Figure 11 shows the Sectors working dataset.

sector exposure workflow expression evaluation
Figure 10: The expression builder dialog
sector exposure workflow data result
Figure 11: The sectors workding dataset
Actions 13 and 14 - Workflow Results

Workflow action 14 is a tabular report result action. When this action runs, it builds a tabular report result from a specified dataset and saves it to the Flow Analytics portal. The Tabular Report Result dialog is used to configure this action as shown in figure 12.

Figure 13 displays the tabular report result as viewed in the Flow Analytics portal.

sector exposure workflow
Figure 12: The tabular report result dialog
sector exposure workflow
Figure 13: The tabular report result dialog
Other Workflow Applications

Portfolio Monitoring

Data automation workflows can do far more than just produce results. Here I'll discuss another possible use for this workflow.

Wealth managers must continuously monitor the portfolios they manage and take action whenever metrics vary significantly from their target. For example, this workflow could be modified to compare the sector weights of a group of portfolios to a set of target model ranges. We could then schedule the workflow to run daily at 4:00 PM. If any actual vs. target values are out of range, an email could be generated and sent to alert a portfolio manager to take action.

Figure 14 shows the workflow management page within the Flow Analytics portal where a trigger has been added which will run this workflow daily at 4:00 PM.

sector exposure workflow
Figure 14: The workflow management page - adding a trigger

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. I discussed how to build a data automation workflow to analyze and monitor sector exposure of an equity portfolio. I started with a brief discussion of data automation workflows. Next, I discussed the workflow results I want to produce followed by an examination of the data required to generate those results. I provided a step-by-step overview of the actions executed by the Evaluate Portfolio Performance workflow. Finally, I showed how to provide continuous monitoring by deploying this workflow from the Flow Analytic portal.

workflow managment

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.