How we assisted a client in leveraging data from multiple data vendors to run company valuations

The Context

The client, an investment firm, wanted the TresVista Team to add the functionality to toggle between Key Performance Indicators (KPIs) from different data vendors (Canalyst, Capital IQ, and Visible Alpha) to the financial model used to perform company valuations. Initially, the client sourced the data through a single data vendor (Canalyst) which limited the client’s findings and conclusions as the forecast estimation methodology varied among different vendors. Hence, the client requested a toggle functionality to leverage multiple data-sources while evaluating an investment in a public company.

The Objective

To create a functionality that toggles between KPIs reported by different data vendors in a financial model template.

The model should have a seamless rollover i.e., the financial model should stay consistent when the data-sources are updated after a quarterly filing

The Approach

The TresVista Team followed the following process:

• Created a desktop app which inserts the data reported by Capital IQ and Visible Alpha to the Canalyst model, adds the KPI toggle function, and uploads the model to the cloud (AWS S3). This process is automated and repeats everyday to process any Canalyst models that gets updated (filings released, forecasts updated, etc.)
• Created a plug-in in MS-Excel which allowed the client to refresh all 3 data sources in the model while retaining the user’s progress/changes made. The plug-in also allowed the client to insert an alternate financial model template that uses the Capital IQ plugin to update data in real-time

The Challenges We Overcame

The primary hurdle faced by the TresVista Team was during the process of the model rollover. One of the data vendors provided a toolkit application to update the model with quarterly filings data. However, this toolkit removed pre-existing connections to other data vendors within the model. This threatened one of the project’s primary objectives which was to facilitate a seamless model rollover between different data vendors. To resolve this issue, the TresVista Team created an Excel workbook event, a code that gets triggered whenever an Excel workbook is opened. The code first checks if the file came from the toolkit and then reinserts the data connections, if required.

Another hurdle faced was the inconsistent reporting of data across vendors. For certain tickers, the date headers were not aligned amongst the three data vendors. For example, Canalyst’s FY 2021 revenue matched Capital IQ’s FY 2020 revenue. To overcome this, the team incorporated an offset formula in the financial model itself. The formula calculated the offset in the number of quarters and added this to the date-headers

Final Product (Sanitized)

The Value Add – Catalyzing the Client’s Impact

The TresVista Team provided the client with additional features like model roll-back, which allowed the client to retain work on the current model and roll-back to a previous version of the financial model template, thus ensuring no loss of data

The TresVista Team also added additional prompts for the users in case there was a lag in the data-source refresh. This can occur when quarterly results are announced, and one of the data-vendor updates their data, but others do not. The team identified a way to determine this problem and prompt the users about the data lag.

The system developed by the TresVista Team automated the process of template insertion, dataset fetch, and added additional features within the models. The TresVista Team automated the processing at the backend by pre-processing the models and saving them to cloud storage so that the users can access the processed model directly. Manually done, this process would take ~10 mins per ticker. There were ~3500 models that needed processing which translates to ~583 hours of manual processing time saved for the users.