Concept creation for the development of an Excel tool

Creation of specifications for an Excel programming tender

Thüga AG (Munich), the largest network of municipal energy and water service providers in Germany, has commissioned eWorks as Microsoft Excel experts to create a specification for the further development of the Excel software "Thüga-RegulierungsSteuerungsTool". This involves two Microsoft Excel applications with numerous VBA macros for forecasting the calculatory and commercial effects of various strategies in electricity and gas networks. In the course of this conception, both the a) technical and b) visual revision of the existing application for better usability and c) programming of new functionalities were to be designed.

Following a kick-off meeting at the start of the project, the first task was to analyze the status quo of the existing Excel applications. The objective was to identify suboptimal operating concepts as well as outdated or even faulty code sections of the VBA programming, to work out constructive improvements and to make recommendations.

During this project phase, it quickly became apparent that the existing Excel VBA macro programming was outdated and no longer met modern quality requirements: not only did it lack a uniform nomenclature and any form of modularization, but also consistent error handling. The mixing of user interface (GUI), calculation logic and data management was considered particularly challenging, resulting in unnecessarily time-consuming and cost-intensive software maintenance. eWorks suggested extracting the specialist logic into an Excel add-in based on Microsoft .NET / C# or VBA (Visual Basic for Applications) in order to better address this problem in the future. It also became apparent that the speed of many Excel formulas and VBA macros was lagging behind what was feasible, which made it seem sensible to optimize the performance of the Excel solution. In addition to the need for technical refactoring, the usability of the Excel solution was also evaluated, with a particular focus on a modern and user-friendly interface with (almost) no need for training.

The subsequent project phase was used to develop and design the innovations to be implemented. For example, the migration of both tools (electricity and gas) to a combined Excel application was designed, which seemed sensible due to the large number of overlaps - but proved to be non-trivial due to the large number of differences. Following the design of the new software architecture, further functionalities were planned, e.g. versioning of all data, a flexible scenario calculator, an improved reporting system with numerous evaluations and reports as well as multi-client capability for partner companies.

The concept was developed in very close cooperation with Thüga AG and was characterized by a large number of coordination rounds in order to make the software concept as robust, detailed and complete as necessary, but at the same time as comprehensible and catchy as possible. eWorks supported its client in an advisory capacity from the start of the project, created the concept, transferred it into a specification sheet and finally handed it over to the client. Subsequently, a project tender was initiated based on these specifications and an order for the creation of specifications and Excel programming was awarded as planned.