Summary
Microsoft Excel is an excellent tool for building models that perform operations on data and produce charts and table visualizations. There is a substantial amount of functionality built in the application to perform data wrangling, sorting and filtering. Yet what happens when the original data is not perfectly tabulated, or a model needs several sheets to be updated on occasions when there is additional data flow? To remedy this, we could add more columns to perform calculations or use VBA codes to update sheets, data tables, filters, etc. Although either of these solutions would resolve the problem, they don’t provide a hassle-free solution, leading to less productivity as it takes longer to develop, maintain and run the model.
The Evalueserve Emerging Markets (EM) team managed to find a turnaround using C# add-ins, which the team developed for a large investment bank over the past year[1] to support data retrieving and consolidation. This add-in allows for complex operations to be performed using tailor-made formulae and is highly efficient, which we will further explain in this case study.
The Issue at Hand
As businesses around the world move to hybrid work models, companies increasingly face challenges to ensure that employees are compliant with their office attendance policies. To achieve this goal, the Evalueserve Business HR team was using the Excel output generated directly through the punching of access cards through ORC doors. It is to be noted that the Excel output is a system-generated file and hence is available only in a highly unstructured form, and it therefore requires a significant amount of manual work, as VBA macros alone are insufficient to put the output in a desired format. The team in charge of putting together the attendance spreadsheet with desired columns such as employee names, employee codes, dates and in-and-out hours previously took approximately five hours. Being a multinational firm, Evalueserve hosts a range of clients, and despite having a universal hybrid work policy, there are teams that require people to come to the office as per their client’s needs, which can differ from Evalueserve work policies. Implementing these exceptions to such teams requires additional manual work, and over time, the database becomes overly heavy and does not allow it to be properly worked upon due to slowness and Excel crashes. Across a long run, producing this report manually would not be feasible, as the database would become ever larger, and the Excel crashes would be increasingly more frequent. Moreover, the team in charge of the model would have to manually adjust attendance thresholds considering holidays, further increasing the complexity of the operation.
Our Solution – Build on the Success of C# Add-ins
The EM team proposed implementing C# add-ins for this task, as they have already helped one of our top investment banking clients with a similar problem it was facing in the past year. The team utilized the ExcelDNA library to code user-defined formulas in C#, employing Microsoft’s Visual Studio Code Integrated Development Environment (VSCODE), which were later packaged as an Excel add-in compatible with all MS Office versions in use. C# add-ins are easy to install and perform calculations in memory with just a few formulas that perform data transformation, as opposed to adding columns manually or through VBA macros. On top of this, the C# add-in comes with a built-in business calendar feature that allows holidays to be discounted, further eliminating the need for manual interventions. The C# add-in was later applied on a system-generated attendance output file to place highly unstructured data in a desired format.
Business Impact
The C# add-in solution turned out to be extremely useful as it streamlined the whole attendance tracking process through:
Efficiency gains: The entire office attendance task is now being done with just a click of a mouse in around 30 seconds, a marked improvement from the five hours that it used to take the Business HR team to do it manually (with only a slight use of VBA macros).
Zero error: The model is fully automated with a 0% chance of human error, a complete reversal from the previous method in which the final output file had multiple errors and the process was particularly slow.
To further these developments, the EM team is currently looking at possibilities of implementing similar solutions for other corporate teams where the issue at hand resembles this case study.
Limitations
Although C# add-in tools can positively influence the productivity of teams working with Excel models, making changes to existing features or adding additional functionalities remains a challenge for users. This is due to the unavailability of source code, as they are compiled to bytecode and thus do not allow users to access them, unlike Python scripts that can be modified as per the project requirements. Also, with C# being a niche language, it is relatively harder to find the right talent, compared to Python or R that has an immense talent pool.
[1] To learn more about C# add-ins, see our case study, Excel Add-ins Boost Data Retrieval for Legacy Processes
Talk to One of Our Experts
Get in touch today to find out about how Evalueserve can help you improve your processes, making you better, faster and more efficient.
Overview & Impact
Evalueserve's HR team faces challenges managing unstructured access card data, with manual work, client-specific needs, and a growing database leading to frequent Excel crashes and an unsustainable process.
Efficiency Gains
Zero error
Authors
Alberto Iturra
Senior Research Lead
Pablo de la Barra
Lead Analyst
Sergio Lillo
Analyst
Felipe Solis
Analyst