The Context
The client, a Hedge Fund, wanted the TresVista Team to create a data repository tool that tracks the entire company’s performance using internal and external data sources. The client was unable to analyze the data present in large volumes. Due to MS-Excel’s limitation, the client could not incorporate all the analysis with dynamic slicing and dicing. Since the data was from both internal and third-party sources, standardization and transformation were necessary to simplify data analysis.
The Objective
To create an automated and dynamic data repository tool that tracks the performance of portfolio managers, visualize trends, KPIs, and other metrics pertaining to the investment strategies.
The Approach
The TresVista Team followed the following process:
- Collated and aggregated the data provided by the client, which was in the form of varied frequencies, into a single data source
- Standardized the consolidated data obtained by performing various data manipulation calculations on R programming language
- Computed and visualized this data on a visualization tool to make it easier for the client to identify patterns, trends, and outliers
- Generated summary reports and analysis at an individual portfolio manager level which could be downloaded through the tool
- The team monitored and updated the tool weekly and quarterly and constantly optimized the tool by adding more metrics for better analysis
The Challenges We Overcame
The major hurdle faced by the TresVista Team were:
- The client’s requirement to perform analysis from 2006 onwards and an increase in the number of portfolio managers led to a rise in the quantum of data. This led to constraints in storage and prolonged calculations in the tool. The TresVista Team overcame this by switching from MS-Excel to SQLite, an open database that facilitated efficient data storage. It was accessible through a wide variety of third-party tools
- Accounting for the quality of the data provided by the client containing inconsistent historical data, with missing and incorrect values
- Finding the GICS Sector Ticker mapping for all the historical tickers for the last ten years
Final Product (Sanitized)
The Value Add – Catalyzing the Client’s Impact
- The team developed a web application that is user friendly, has access control, and requires the least amount of TCO at the client end
- Streamlined the Extract, Transform, Load (ETL) process that saved around 85% of manual time and automated the process of Portfolio Manager report generation
- Provided separate credentials and data views for each end-user, thereby encapsulating sensitive data of each portfolio manager
