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.
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
Building a Workflow to Evaluate Equity Sector Performance
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
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
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.
Figure 5: Asset Classification Mapping 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.
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.
Figure 7: Evaluate Equity Sector Performance workflow actios viewed in CloudConnect
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.
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
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
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.
Figure 10: Create Hypercube Data Visualization Dialog
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.
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.
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.