Debugging of spreadsheet programs

Spreadsheets are the most successful example of the End User Programming approach to software development. Today, spreadsheet applications, e.g., based on Microsoft Excel, can be found nearly everywhere in companies and are used for a variety of purposes. Studies showed for example that in over 80% of the examined companies spreadsheets are used for financial reporting. Furthermore, companies often use dozens or even hundreds of spreadsheet applications which often tend to be large and complex comprising hundreds or even thousands of formulas. Despite the fact that in many cases business-critical decisions depend on spreadsheets, a complete lack of quality control measures for this type of software applications can be observed in many companies. Not surprisingly, spreadsheets - which are usually developed by non-programmers - contain errors of various types. In some studies, researchers found at least one error in every single spreadsheet they analyzed. However, when business decisions are based on spreadsheets, such errors can lead to a significant financial loss or to other business risks as reported by the European Spreadsheet Risk Interest Group. Over the last decades, academics from different fields as well as industry have produced a rich set of methods, techniques and tools to ensure or improve the quality of traditional software artifacts. Only in recent years, the question of how to improve the quality of End User Programs and in particular spreadsheets has gained more attention in research and in practice. These developments led to a number of first proposals ranging, e.g., from process-related measures over visualization and software testing approaches to debugging techniques. In this project, we focus on the debugging stages of the spreadsheet development cycle. Therefore, our goal is to provide the spreadsheet developer with tools that support him or her in identifying the possible problem causes (error localization). These possible causes are called diagnoses and represent subsets of all formulas used in a spreadsheet that should be modified in order to make a spreadsheet application work as expected. In particular, we will analyze and systematically evaluate in which ways various existing approaches to software debugging can be applied, extended or combined to cope with the particularities of spreadsheet debugging. Based on these technical and algorithmic contributions, we aim to develop a classification system that helps us understand which error-spotting techniques are particularly well suited for which types of programs and application settings. Furthermore, we plan to conduct laboratory studies with real users at different stages of the project, in which we want to obtain a better understanding of how and to which extent the participants profit from using the provided debugging tools and how the interaction with an intelligent debugger for end users should be designed.

Spreadsheets are widespread in today`s IT supported society. While spreadsheets are often used as basis for far-reaching decisions, even such important documents are not always error- free. This is demonstrated by a growing list of spreadsheet-related horror stories, collected by the European Spreadsheet Risk Interest Group (ESPRIG). The DEOS project took up this issue and worked on establishing a variety of methods to help users recognize faulty behaviour of spreadsheets as well as to localize the root causes (cells) that are to blame for this behaviour. First, we elaborated on the question of how to design debugging methods to be as little effortful as possible for users, thus motivating and helping users to use such tools. For this purpose, we developed a method to split spreadsheets into consecutive, smaller but logically connected parts that can be individually tested by a user. A user study in Klagenfurt revealed that this approach is more engaging and effective than the usual inspection approach. We also looked into the root causes and different types of spreadsheet errors when regarding specific methods and applications. To that effect, we analysed a collection of mail conversations about a related set of spreadsheets, thereby identifying existing errors in those sheets. As a main result, we established the first publicly available corpus of spreadsheet files that also provides information about the type and location of faults that are contained therein. Such datasets are important for follow-up research work. For example, in our own work, we used this and similar datasets to train machine learning models to predict the likelihood and position of faulty formula cells in spreadsheets. Our first approach in this direction used a limited set of known measures to detect faults., The related research publication was awarded with an ACM SIGSOFT Distinguished Paper Award. Our subsequent work uses a larger collection of measurements and shows even better prediction performance. Lastly, we investigated common structural attributes of spreadsheets and methods to highlight suspicious structures that are based on these insights. We also developed a tool to communicate those structural cues to users and highlight possible issues. This tool, along with further add-ins, data, and other research outcomes is available at the DEOS projects web page, which we set up at www.spreadsheets.ist.tugraz.at. The DEOS project not only established Austria as competence centre for spreadsheet research, but also promoted international cooperation by connecting researchers from different parts of Austria and Germany. It also contributed in the build-up of an independent international community of expertise in spreadsheet research that is working to make spreadsheets easier and more reliable to use for everyone.