BumbleBee: a refactoring environment for spreadsheet formulas

Abstract

Spreadsheets are widely used in industry. It is estimated that end-user programmers outnumber regular programmers by a factor of 5. However, spreadsheets are error-prone: several reports exist of companies that have lost big sums of money due to spreadsheet errors. In previous work, spreadsheet smells have proven to be the cause of some of these errors.To that end, we have developed a tool that can apply refactorings to spreadsheet formulas, implementing our previous work on spreadsheet refactoring, which showed that spreadsheet formula smells are very common and that refactorings for them are widely applicable and that refactoring them with a tool is both quicker and less error-prone.Our new tool Bumblebee is able to execute refactorings originating from both these papers, by means of an extensible syntax, and can furthermore apply refactorings on entire groups of formulas, thus improving upon the existing tool RefBook. Finally, BumbleBee can also execute transformations other than refactorings.

Visualising Formula Structures to Support Exploratory Modelling

“…Focusing supporting users in their use of spreadsheet and often encouraging a more disciplined or rigours approach to development and use (including automated assessment of quality features), (Burnett et al Burnett 2002, Hendry and Green 1994, Hermans and Dig 2014, Panko and Sprague 1998, Sajaniemi 2000.…”

“…These points reveal themselves in anecdotal evidence of businesses and professionals who have working spreadsheets, which are rarely refined or modified because of the risk of 'breaking' a working 'solution'. The work of Hermans and Dig (2014) in supporting re-factoring within spreadsheets, illustrates one way of reducing the emergent complexity of spreadsheet modelling. …”

Preventing data errors with continuous testing

“…Tools such as RefBook [2], BumbleBee [35], and CheckCell [4] can help prevent errors in spreadsheets. For example, RefBook and BumbleBee help programmers and end users, respectively, create refactoring macros for spreadsheet formulae, which minimizes copyand-paste and manual editing errors.…”

Parsing Excel formulas: A grammar and its application on 4 large datasets

“…Recent research has often focused on analyzing and visualizing spreadsheets. () More recently, researchers have attempted to detect data and table clones in spreadsheets() and to define spreadsheet smells : applications of Fowler's code smells to spreadsheets,() followed by approaches to refactor spreadsheets() and to apply testing practices on spreadsheets . These research works analyze the formulas within spreadsheets and therefore often involve formula parsing.…”