Finding faults in Excel

Koch Patrick | Foto: aau/Müller

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.”

Patrick Koch - Preisverleihung | Foto: KK

A few words with … Patrick Koch

What would you be doing now, if you had not become a scientist?

My intuition tells me that I would probably be tackling exciting (technical) problems somewhere. During my time in academia, I also learnt to appreciate the value of good coordination and communication, and I could imagine delving deeper into this area.

Do your parents understand what it is you are working on?

It’s always fun to find good explanations for the things I am working on. That usually works quite well on a very general level; unfortunately, it’s less successful when it comes to the details, as those tend to demand a broad basic knowledge that is specific to my area of work. But I suppose that is the case with any moderately complex occupational field.

What is the first thing you do when you arrive at the office in the morning?

Usually, first I deal with any events and e-mails that have accumulated since the previous working day. After that, project work is often very varied: starting with literature research and writing up concepts and publications, via developing and evaluating programs, all the way to organisational matters such as meetings and presentations.

Do you have proper holidays? Without thinking about your work?

Not often, admittedly. But when I do, for instance while I am hiking, I really enjoy tuning out of the issues of the (working) routine and concentrating fully on my surroundings and the path ahead of me.

What makes you furious?

Sometimes I get annoyed about preventable problems that provide little scope for learning. These might be problems due to non-assessable external influences or coordination errors, because different expectations have not been properly communicated.

What calms you down?

I find that sports provide a good balance (e.g. jogging, climbing, or simply a game of table-tennis), playing the guitar and, most of all, joint activities with good friends.

Who do you regard as the greatest scientist in history, and why?

I would not want to tether my answer to just one person. During the course of our history, a multitude of individuals has achieved great things, considering the circumstances of their respective day and age. I especially admire the versatility with which the great scientists of the past often viewed the world. In modern science, in particular, it is necessary to focus intently on a specific topic, the further we delve into the subject matter. However, I do believe that it is important to maintain a multi-faceted and networked perspective.

What are you afraid of?

As an engineer, the partial lack of foresight in dealing with modern technology is especially disconcerting for me; both from the perspective of the users and from that of the companies that develop these devices and services. I think it is good and important that our culture promotes steady technological transformation and also makes use of it. However, the societal and structural implications of these technologies are only rarely critically scrutinized, even though they ultimately affect us all.

What are you looking forward to?

In the short term, I am looking forward to an upcoming hiking holiday in Canada. In the medium term, I am excited about the fascinating challenges my project work has yet to reveal. And in the long run, I feel optimistic that we will cope well with the emerging problems of continued technological developments, and thus manage to gradually make our world slightly more magical. As Arthur C. Clarke put it so fittingly once: “Any sufficiently advanced technology is indistinguishable from magic.”

Der Beitrag Finding faults in Excel erschien zuerst auf Alpen-Adria-Universität Klagenfurt.

Source: AAU TEWI