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
Building a Workflow to Calculate Index Returns
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.
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.
Figure 4: Workflow actions 1 to 10 and quarterly index return data
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.
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.
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.
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.
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.
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.
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.