A familiar situation for many: A large Excel file that includes many sheets, rows and columns – and in the end, the correct result stubbornly refuses to materialize. Troubleshooting can be complicated whenever numerous formulas and references are involved. Patrick Koch is working on the project “Debugging of spreadsheet programs (DEOS)”, funded by the FWF, which aims to simplify the search for errors. He recently received the “ACM SIGSOFT Distinguished Paper Award” for his publication on this subject.
“Excel, with its many references and formulas, is basically a program that is created directly by the user”, Patrick Koch from the Department of Applied Informatics explains. Accordingly, it is quite powerful: After all, Excel is often used to handle large accounting tasks and similar jobs. If a fault happens to slip into such a sheet, the consequences can be problematic. Koch goes on to say: “However, to date there are very few reliable debugging methods and quality assurance tools. This is the gap we hope to close with our project.” In other words, the team, led by Konstantin Schekotihin, works on adopting debugging methods that already exist for other programming languages for the spreadsheet domain, as well as the development of new approaches. Alongside Alpen-Adria-Universität in Klagenfurt, the project also involves Graz University of Technology and TU Dortmund University.
At the International Conference on Software Engineering (ICSE) in the Swedish city of Gothenburg, Patrick Koch was recently presented with the “ACM SIGSOFT Distinguished Paper Award” for his publication. One of the issues he addresses in the paper is the question of a good test oracle. A test oracle is usually a source that can determine so-called expected results, i.e. the correct answers. In his work, Koch applies machine learning algorithms to train an oracle that is able to accurately classify cells as either ‘faulty’ or ‘correct’ by means of a vast database of example spreadsheets. Then, one should know: “What do errors look like? Are they more likely in long or short formulas? Where might the errors be located?” The oracle should be able to predict the locations of errors.
Patrick Koch works from an office in his home town of Graz. He studied Software Development and Business Management at the local University of Technology. When asked about his reasons for choosing this path, he lights up with his enthusiasm for technology. “Even as a small boy, I simply knew: programming is cool!”. Why might that be his take on the matter, while others regard programming as boring and complicated? “Programming has a magical quality. With proper preparation, you can bring things to life and control them. For me that is a tempting challenge, while others might say: ‘Oh, that is complicated, I’d rather not get involved.’” Patrick Koch is a so-called First Academic from a parental home without academic education, but he discovered his approach to algorithmic, structured thinking at an early age and he believes: “You have to really apply yourself to the matter and learn to understand the processes. Then, anyone can do it.”
Koch, P., Schekotihin, K., Jannach, D., Hofer, B., Wotawa, F. & Schmitz, T. (2018). Combining Spreadsheet Smells for Improved Fault Prediction, ICSE 2018, https://www.icse2018.org/event/icse-2018-new-ideas-and-emerging-results-combining-spreadsheet-smells-for-improved-fault-prediction.