2018-10-08

Flow Analytics for Fintech Series

Calculate Index Returns


Introduction

The analytics skills required to compute index returns have a broad range of applications for wealth management professionals. Wealth managers use market indexes for a variety of purposes, from performance measurement and risk analytics to building asset allocation models and making strategic investment decisions. I, therefore, decided to write a post and do a video on how to perform these calculations using Flow Analytics. This post shows how to build a workflow that:

  • Loads five years of daily index values for the Standard and Poor 500 and the Russell 2000
  • Computes each index's daily return
  • Creates a new weighted index
  • Extracts the year, quarter, and month subperiod values from the date field
  • Calculate yearly, quarterly, and monthly returns for each index
  • Build tables and charts to display the index returns

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 Calculate Index Returns


What is a 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 must, therefore, decide what results we wish to create.

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

The goal of this post is to demonstrate how to develop an analytics-oriented workflow. To that end, all we really must do is load the data and perform the calculations. Nonetheless, to better illustrate things, I'll show how to produce these results.

  • A blended index created from the S&P 500 and Russell 2000
  • A dataset containing daily return values for each index
  • Yearly, quarterly, and monthly subperiod returns for each index
  • a set of tables and charts that display the subperiod returns

Figure 2 shows index return tables and chart results this workflow creates.

index returns quarterly return chart
index returns quarterly pivot table
Figure 2: Index return table and chart results
What data do we need to create our required results?

To calculate our index returns, we'll need index values for the S&P 500 and Russell 2000 for a five-year period. To simplify things, I've provided a dataset containing these values within the Flow Analytics portal. It is important to note that we could obtain this data from a variety of sources such as third-party data financial data providers or via calls to a publicly available API.

Index Data

The dataset required to run this workflow is straightforward. In this case, it consists of index returns data for the S&P 500 and Russell 2000 for the five-year year period starting 09/03/2013.

Figure 3 shows the index data we'll use to calculate our returns.

calculate index returns dataset
Figure 3: Index values dataset

Walkthrough - The Calculate Index Returns Workflow


The Workflow Actions

1:   Load index dataset

2:   Evaluate time-series and date expressions

3:   Build hypercube with dimensions year, quarter, and month

4:   Evaluate return calculations

5:   Filter hypercube to extract quarterly return values

6 - 10:   Create table and chart results

Workflow actions 1 through 5 do the work of loading the data, deriving required date values, calculating daily index changes, grouping the data points, and performing the return calculations. The remaining workflow actions create our table and chart results.

Figure 4 displays the quarterly return dataset result created by running this workflow.

calculating index return steps
Figure 4: Workflow actions 1 to 10 and quarterly index return data
Workflow Explanation
Actions 1 and 2

The index data loads first then an expression evaluation action is executed. Expression evaluation actions can perform any number and combination of user-specified operations including arithmetic, statistical, date-time. In this case, it builds the blended return index, evaluates the daily index returns and extracts the year, quarter, and month date values, figure 5 shows the resulting dataset.

Figure 5 shows the working data result from running actions 1 and 2.

calculating index return steps
Figure 5: Working data result of running actions 1 and 2 - index returns
Actions 3 to 5

Action 3 builds a hypercube from the index returns using the year, quarter, and month subperiod labels as grouping dimensions. Next, a hypercube expression evaluation action calculates the returns for each subperiod. Finally, we use a filter action to slice the quarterly return values from the hypercube, saving the sliced values to a new dataset called quarterly returns.

Figure 6 displays the Index Return Cube result created by running actions 3 to 5.

calculating index return steps
Figure 6: Index Return Cube working data created by running actions 3 to 5
Actions 6 to 10 - Workflow Results

Tables and charts that display index returns are not very useful on their own, but I've included them here for illustration, so let's take a closer look at how to create and view them.

Adding a Pivot Table Result

Workflow action 6 is a pivot table (a.k.a cross-tab) result action. When this action runs, it builds a pivot table from a specified hypercube and saves it to the Flow Analytics portal. We use the Pivot Table Result dialog to add this action. Within the dialog, you select a hypercube then the row and column dimensions, and a cross-tab value to display.

Figure 7 displays the configure Pivot Table Result dialog.

rdl report
Figure 7: Configuring a Pivot Table Result action

Other Output Options


There are very few circumstances where we would be required to calculate index returns or produce tables or charts to display index returns. A more realistic scenario might involve calculating the index returns for use as benchmarks to help evaluate investment performance. In this case, we might wish to calculate the return data then export it for use by another application.

Exporting Data Results to a Delimited File

Flow Analytics customers often deploy workflows to serve as an integration hub. In this case, they coordinate and manage the flow of data between various applications, databases, or files. Figure 8 shows the Export Data to Delimited File configuration dialog. Adding this action will export the data to the designated file each time the workflow runs.

rdl report
Figure 8: Export the index return to a delimited file

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 calculate index and benchmark returns for various time periods. It started with a brief overview of data automation workflows and then discussed the results we want to this workflow to produce followed by an exploration of the data needed to generate those results. Finally, it provided a step-by-step illustration of the Calculate Index Returns workflow.

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.