This investment dashboard back-end plug in was aimed at enabling a customer’s view returns on their investments over a given period (last 10 years).

Problem Statement

Customers of the investment management firm in question had made multiple requests to see the growth of their investments over the years of making scheduled monthly contributions. Some of these clients had also made withdrawals at some point during their relationship with the business and wanted a visual representation of their fund growth over the years.

This visualization was to be hosted on the investor portal, giving each investor a view of their individual portfolio’s growth in terms of investment returns as well as cumulative growth.

However, due to the share volume of data generated by the >1million customers of the firm, this data could not be computed and stored as the infrastructure to carry this out was not available. Thus, I defined a function to calculate each individual customer’s portfolio returns on the fly within < 2 seconds of . This resulted in a less expensive approach as the data would only be computed on demand rather than calculated and stored in memory.

Tool(s) Used: Microsoft SQL Server

Data Sourcing

The data was sourced from 2 different tables namely Ledger which contained all inflow and outflows in terms of fund units purchase and sold, and Fund_Price which contained historical records of daily fund prices since each fund’s inception.

The Solution

The solution was a function which would calculate each customer’s monthly account balances, inflows, outflows and investment returns over the last 10 years and return this data in the form of a table. The generated table would then be visualized in the customer’s profile on the investor portal.

The formula utilized for this calculation was:

Balance = Fund price * Number of units

Gains = Balance - Net Inflows (+ Net Outflows)

Assumptions

• Customers can only be subscribed to one fund at a time.