Gencel: Automatic Generation of Correct Spreadsheets

Gencel™: Automatic Generation of Correct Spreadsheets

Problems with spreadsheets

Spreadsheets that are used in the real world are full of errors and cause companies and organizations to lose millions of dollars every year. Finding and correcting spreadsheet errors is a very difficult and time-consuming activity.

An alternative to debugging

Observing that all errors in spreadsheets result from updates or changes applied to the spreadsheet - be it during the creation of a new spreadsheet or while adapting an existing one - an obvious alternative to debugging is to prevent errors by making these update operations safe. We have created an extension to Excel, called Gencel™, that is based on the concept of a spreadsheet template, which captures the essential structure of a spreadsheet and all of its future evolutions. Such a template ensures that the spreadsheet can be changed only in the the anticipated ways, so that spreadsheets evolving from templates will provably never contain any reference, range, or type errors. Gencel™ can help to reduce maintenance costs while at the same time it dramatically increases the level of correctness and reliability of spreadsheets.

How to create and maintain correct spreadsheets with Gencel™

The basic idea behind the Gencel™ system¹ for creating correct spreadsheets is to anticipate the possible evolutions of a spreadsheet and capture a class of spreadsheets in a template. A program generator will then create an initial spreadsheet together with customized update operations for such a template. These customized update operations allow users to edit the created spreadsheet while ensuring that no reference, range, or type errors can be introduced into the spreadsheet.

Templates are created with an editor, called ViTSL, which is similar to Excel, but offers additional functionality to specify templates. These templates can then be loaded into the Gencel™ system, which is implemented as an extension to Excel, and allows the safe, error-free editing of spreadsheets. The relationship between these two components and templates and spreadsheets is summarized in the following figure.

The best and fastest way to get an impression of how the approach works is to read the first two sections (about 6 pages) of the ViTSL paper and watch the following short movie that demonstrates the working of the system.

Visual Specifications of Correct Spreadsheets (duration: 4 minutes) ViTSL.wmv.gz (7MB), ViTSL.wmv (11MB)

(Note that the video data of the wmv file doesn't seem to work on all players. In that case you can also download the much larger quicktime movie ViTSL.mov.gz (45MB), ViTSL.mov (255MB).)

Further Information

ClassSheets - Model-Based, Object-Oriented Design of Spreadsheet Applications, Jan-Christopher Bals, Fabian Christ, Gregor Engels, and Martin Erwig
Journal of Object Technologies, Vol. 6, No. 9, 2007

Inferring Templates from Spreadsheets, Robin Abraham and Martin Erwig
28th IEEE/ACM Int. Conf. on Software Engineering, 182-191, 2006

Gencel: A Program Generator for Correct Spreadsheets, Martin Erwig and Robin Abraham and Irene Cooperstein and Steve Kollmansberger
Journal of Functional Programming, Vol. 16, No. 3, 293-325, 2006

Visual Specifications of Correct Spreadsheets, Robin Abraham, Martin Erwig, Steve Kollmansberger, and Ethan Seifert
IEEE Int. Symp. on Visual Languages and Human-Centric Computing, 189-196, 2005

Automatic Generation and Maintenance of Correct Spreadsheets, Martin Erwig, Robin Abraham, Irene Cooperstein, and Steve Kollmansberger
27th IEEE Int. Conf. on Software Engineering, 136-145, 2005

ClassSheets: Automatic Generation of Spreadsheet Applications from Object-Oriented Specifications, Gregor Engels and Martin Erwig
20th IEEE/ACM Int. Conf. on Automated Software Engineering, 124-133, 2005

Contact

For more information, please contact: Martin Erwig

Notes

last change: June 29, 2006

Martin Erwig erwig@eecs.oregonstate.edu