Summary
While working on research reports for a top investment banking client, Evalueserve’s Emerging Markets Team noted that, even though Microsoft Excel remains popular among Economists and Strategists for creating models, they are still left with a lot of manual work that is extremely time consuming and prone to errors. For example, exporting data into Spreadsheets becomes an increasingly cumbersome task, as it requires a lot of manual work and consumes time and resources. To overcome this challenge, the EM team developed Excel add-ins to transform these repetitive tasks into simplified Excel functions that perform data retrieval and consolidation. The add-ins allowed onshore analysts to have a pool of models at their disposal while giving them the option to turn those models into periodicals for research publications - All of this without having to migrate to another platform, allowing additional functionality through newly developed, advanced add-ins.
The Challenge
The Emerging Markets team develops financial models, which are a mix of market data on finance, economics, and forecasts. Given the large scope, the data is retrieved from internal platforms as well as externally from paid and public sources, such as Bloomberg, Reuters, and Havers.
This presents the following challenges:
- The analyst needs to deal with multiple APIs and methodologies in order to obtain the data to perform analysis and consolidate the unstructured data in a desired format.
- Data preparation and consolidation becomes a manual and time-consuming process.
- Manually processing the data sometimes leads to errors.
Our Solution
One of the solutions that simplifies this work is automating data retrieval through Excel add-ins. The add-ins are customized with formulas for distinct purposes, such as retrieving data from internal and external sources with their specific APIs while making sure they are compatible with our system. The add-ins execute the calculations and help the user to save the automatically produced analytics in local servers that are accessible to onshore and offshore analysts.
These tools are developed using C# Programming language that draws upon of a large library called Excel-DNA, which is very helpful in creating Excel addins. Both C# and Excel-DNA are open-source solutions that can be used free of charge.
These technologies allow us to automate models to the point at which there is no manual operation to be performed rather than clicking a “run” button or refreshing the Excel sheets. The add-ins in the spreadsheet perform all the logic of date ranges, data retrieval, data alignment and data display for charts or exhibits.
Business Impact
This solution has a far-reaching business impact for our clients. Model development becomes easier and faster if data retrieval from multiple sources can be done single-handedly without any customized APIs. That way, the analyst can focus more on the model itself rather than data processing or wrangling. The time that it takes to run a model is also extremely low compared to conventional run-times, as the solution uses lower-level source APIs that do not require any front-end tools, due to fewer overheads.
The Emerging Markets team regularly publishes reports on Fixed Income Local Currency Total Return indices retrieved from a Financial Data provider to perform analysis with different weights and caps for each country. Manually retrieving the data and performing the calculations manually normally takes around 60 minutes. With the new Excel add-in, we can fully automate data retrieval, formatting, and chart generation, all while reducing the run-time to just 30 seconds. Since we run the models twice a week, we have reduced the total time in executing this task from 100 hours/yr to just one hour/yr, a 99% time reduction. We also note that we have totally eliminated any possibility for human error, as all of the tasks are automated using add-ins with robust checks put in place.
Limitations
One of the limitations of using Excel add-ins is that they rely heavily on features that are pre-installed in this software package, like Excel formulas, which might not be very useful or convenient for teams that rely heavily on alternative tools, like Python or Jupyter notebooks. In addition, the solution is only available for Microsoft Excel running on Windows OS.
There are also some configuration-related constraints in running this tool, as it was developed using C#, which requires compilation. Hence, the user must use the same version of Microsoft Office (32 or 64 bit).
