2018-10-08

Flow Analytics for Fintech Series

Evaluate Equity Sector Performance with Flow Analytics


Introduction

This post discusses evaluating sector performance within individual equity portfolios. It is part of the Flow Analytics Fintech Series .

A sector-based approach to managing the equity portion of a portfolio requires classification of individual equities into a hierarchy of sector and industry groups. Sector and industry exposures are a critical driver of equity market return variability over time. Equity sectors have similar earnings drivers within each sector and industry group and high return dispersion along with low correlation across groups. While sector-based approaches to managing the equity portion of a portfolio have, for the most part, given way to use of style box strategies, this approach is still commonly applied to large allocations of individual equities.

In this post, I'll show how to build an analytical workflow that calculates the total, quarterly, and monthly returns of each sector group within an individual equity portfolio.

Topics Discussed

This post presents an example data automation workflow designed to evaluate the performance of individual equity sectors within an investment portfolio. The topics discussed include:

  • A brief discussion of data automation workflows
  • The variety of results a workflow can generate and the results this workflow will create
  • The data required to produce our required results
  • The workflow actions used to produce the 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 Evaluate Equity Sector Performance


What is a data automation workflow?

A workflow performs three fundamental tasks: it loads data from one or data sources, applies a sequence of actions to that data, then generates a set of results. Here are some examples of workflow results:

  • 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 must first decide what workflow results we wish to create, so let's look at that topic next.

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

Our goal is to generate results that will allow us to view and analyze the performance of each sector grouping within an equity portfolio. We could use a variety of different results to accomplish this goal. This example workflow generate two results:

  • A pivot table that displays each sector's quarterly returns
  • A drill-down enabled column chart showing each sectors total return
  • A drill-down chart view that displays quarterly returns for a selected sector

Figure 2 shows these results viewed within the Flow Analytics portal.

Next, let's look at the data we'll use to create these results.

sector performance required results
Figure 2: Sector returns table and drill down chart
What data do we need to create our required results?

To evaluate the sector performance of our equity portfolio, we'll combine three sets of data: a dataset containing historical account positions for the period under evaluation, daily security pricing data for the same period and, finally, a dataset containing sector classifications for the position data. For purposes of this example, these datasets are meant to be as realistic as possible without being too complicated. We could very easily incorporate other data sources and still achieve the same results.

A brief description of each required dataset follows.

Daily Position Data

To keep this example simple, I'll use one year of settled account positions and ignore accruals, and other transactions since accounting for this activity would complicate things a bit. There are only three required data points: a security identifier, i.e., ticker or CUSIP, a date, and the number of settled shares held in the portfolio. I've included several other commonly used data points.

Figure 3 shows the daily position data

position history data
Figure 3: Daily Position Data - 04/01/2017 to 03/31/2018
Daily Pricing Data

To calculate market values, we need a dataset containing daily prices for each portfolio position during the performance evaluation period. The daily pricing data includes a pricing date, security identifiers, i.e., ticker and CUSIP, for and a closing price for each security.

Figure 4 shows the daily pricing data

security price data
Figure 4: Daily Pricing Data - 04/01/2017 to 03/31/2018
Asset Classification Mapping Data

Asset classification schemes vary significantly among wealth management firms so, to keep things flexible, we'll use a set of asset classification data which we will map to our positions. The asset classification data maps a ticker or CUSIP to an asset type, asset class, a sector group, and an industry group.

Figure 5 shows the asset classification mapping data.

asset classification data
Figure 5: Asset Classification Mapping Data
Sector Data

Finally, we are going to need a list of sector names. I will explain how these are used below.

Figure 6 shows the sector data.

sector data
Figure 6: Sector Data

Walkthrough - Evaluate Equity Sector Performance with Flow Analytics


This workflow starts with actions that load and combine the required datasets. It then uses a hypercube to group the positions by day and calculate daily total market values. A for each loop action is used to calculate the daily returns within each sector and, finally, another hypercube is used to compute the total, quarterly, and monthly sector returns.

Workflow Actions Overview

Actions 1 to 6: Load and combine required datasets

Actions 7 to 10: Use a hypercube and calculate the daily portfolio market values

Actions 11 to 16: Loop through sectors and compute their daily returns

Actions 17 and 18: Use a hypercube to calculate returns for each sector and time period

Actions 19 and 20: Generate the results

Figure 7 displays the workflow actions viewed in the CloudConnect application along with working data results created when the workflow runs.

sector performance workflow
Figure 7: Evaluate Equity Sector Performance workflow actios viewed in CloudConnect
Workflow Explanation
Actions 1 to 6

These workflow actions are straightforward, the first four actions load the required datasets, the next two actions join the asset classifications data and price data into the position history.

Actions 7 to 10

We need to calculate the daily market values for each sector grouping within the portfolio. To accomplish this, we build a hypercube with two dimensions: sector and date, then we perform a sum of the current position values within each hypercube group to get the total daily market values for each sector. Finally, we filter the hypercube to extract daily values.

Figure 8 displays the daily market values generated by actions 7 to 10.

sector performance steps t to 10
Figure 8: Daily market value working data generated by actions 7 to 10
Actions 11 to 16

We need to calculate the daily returns for each sector. To do this, we'll use a loop to extract each sector's daily market values into a temporary dataset called SectorDailyMarketValues then we'll calculate the daily returns within that dataset and pop the results out to a new dataset.

Figure 8 shows the SectorDailyMarketValues working data for the Basic Materials sector

sector performance workflow actions 11 to 16
Figure 9: SectorDailyMarketValues working data created by running actions 11 to 16
Actions 17 and 18

After actions 11 to 16 run, we have a dataset containing the daily market values and daily returns for each of our portfolio's sectors. To calculate the quarterly and monthly returns for each sector, we build a hypercube with dimensions sector, quarter, and month then apply a hypercube expression evaluation action to compute the returns.

Figure 9 shows a portion of the hypercube data generated when action 18 runs

sector performance workflow actions 17 and 18
Figure 10: Workflow Actions 11 to 16 - Sector Daily Market Values
Actions 19 and 20 - Workflow Results

Workflow action 19 is a hypercube-data-visualization result action. When this action runs, it builds a drill-down capable data visualization result from a specified hypercube and saves it to the Flow Analytics portal.

Figure 10 shows the Create Hypercube Data Visualization dialog

Figure 11 shows the data visualization result as viewed in the Flow analytics portal.

sector performance hypercube data visualization dialog
Figure 10: Create Hypercube Data Visualization Dialog
sector performance hypercube data visualization
Figure 11: Sector Returns Drill-Down Chart

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 discussed how to build a data automation workflow to calculate the monthly, quarterly, and total returns within the equity sector groups of a portfolio. It briefly discussed data automation workflows, the importance of specifying workflow results, and data requirements. Finally, it provided a step-by-step overview of the Evaluate Equity Sector Performance workflow.


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.