Automatic generation and maintenance of correct spreadsheets

ABSTRACT

Existing spreadsheet systems allow users to change cells arbitrarily, which is a major source of spreadsheet errors. We propose a system that prevents errors in spreadsheets by restricting spreadsheet updates to only those that are logically and technically correct. The system is based on the concept of templates that describe the principal structure of the initial spreadsheet and all of its future versions. We have developed a program generator that translates a template into an initial spreadsheet together with customized update operations for changing cells and inserting/deleting rows and columns for this particular template.We have designed a type system for templates that ensures the following form of "spreadsheet maintenance safety": Update operations that are generated from a type-correct template are proved to transform the spreadsheet only according to the template and to never produce any omission, reference, or type errors.Finally, we have developed a prototype as an extension to Excel, which has been shown by a preliminary usability study to be well accepted by end users.

REFERENCES

• 1: Robin Abraham , Martin Erwig, Header and Unit Inference for Spreadsheets Through Spatial Analyses, Proceedings of the 2004 IEEE Symposium on Visual Languages - Human Centric Computing (VLHCC'04), p.165-172, September 26-29, 2004 [doi>10.1109/VLHCC.2004.29]
• 2: R. Abraham, M. Erwig, S. Kollmansberger, and E. Seifert. Visual Specifications of Correct Spreadsheets. Technical Report CS05-60-1, School of EECS, Oregon State University, 2005.
• 3: Y. Ahmad, T. Antoniu, S. Goldwater, and S. Krishnamurthi. A Type System for Statically Detecting Spreadsheet Errors. In 18th IEEE Int. Conf. on Automated Software Engineering, pages 174--183, 2003.
• 4: Tudor Antoniu , Paul A. Steckler , Shriram Krishnamurthi , Erich Neuwirth , Matthias Felleisen, Validating the Unit Correctness of Spreadsheet Programs, Proceedings of the 26th International Conference on Software Engineering, p.439-448, May 23-28, 2004
• 5: Polly S. Brown , John D. Gould, An experimental study of people creating spreadsheets, ACM Transactions on Information Systems (TOIS), v.5 n.3, p.258-272, July 1987 [doi>10.1145/27641.28058]
• 6: Margaret Burnett , Curtis Cook , Omkar Pendse , Gregg Rothermel , Jay Summet , Chris Wallace, End-user software engineering with assertions in the spreadsheet paradigm, Proceedings of the 25th International Conference on Software Engineering, May 03-10, 2003, Portland, Oregon
• 7: Margaret Burnett , Andrei Sheretov , Bing Ren , Gregg Rothermel, Testing Homogeneous Spreadsheet Grids with the "What You See Is What You Test" Methodology, IEEE Transactions on Software Engineering, v.28 n.6, p.576-594, June 2002 [doi>10.1109/TSE.2002.1010060]
• 8: S. Ditlea, Spreadsheets can be hazardous to your health, Personal Computing, v.11 n.1, p.60-69, Jan. 1987
• 9: M. Erwig, R. Abraham, I. Cooperstein, and S. Kollmansberger. Gencel --- A Program Generator for Correct Spreadsheets. Technical Report TR04-60-11, School of EECS, Oregon State University, 2004.
• 10: Martin Erwig , Margaret M. Burnett, Adding Apples and Oranges, Proceedings of the 4th International Symposium on Practical Aspects of Declarative Languages, p.173-191, January 19-20, 2002
• 11: Marc Fisher , Mingming Cao , Gregg Rothermel , Curtis R. Cook , Margaret M. Burnett, Automated test case generation for spreadsheets, Proceedings of the 24th International Conference on Software Engineering, May 19-25, 2002, Orlando, Florida [doi>10.1145/581339.581359]
• 12: F. J. Lerch , M. M. Mantei , J. R. Olson, Skilled financial planning: the cost of translating ideas into action, Proceedings of the SIGCHI conference on Human factors in computing systems: Wings for the mind, p.121-126, March 1989
• 13: Raymond R. Panko, What we know about spreadsheet errors, Journal of End User Computing, v.10 n.2, p.15-21, Spring 1998
• 14: R. R. Panko. Spreadsheet Errors: What We Know. What We Think We Can Do. In Symp. of the European Spreadsheet Risks Interest Group (EuSpRIG), 2000.
• 15: S. L. Peyton Jones. Haskell 98 Language and Libraries: The Revised Report. Cambridge University Press, Cambridge, UK, 2003.
• 16: S. Prabhakarao, C. Cook, J. Ruthruff, E. Creswick, M. Main, M. Durham, and M. Burnett. Strategies and Behaviors of End-User Programmers with Interactive Fault Localization. In IEEE Int. Symp. on Human-Centric Computing Languages and Environments, pages 203--210, 2003.
• 17: K. Rajalingham, D. R. Chadwick, and B. Knight. Classification of Spreadsheet Errors. Symp. of the European Spreadsheet Risks Interest Group (EuSpRIG), 2001.
• 18: Gregg Rothermel , Margaret Burnett , Lixin Li , Christopher Dupuis , Andrei Sheretov, A methodology for testing spreadsheets, ACM Transactions on Software Engineering and Methodology (TOSEM), v.10 n.1, p.110-147, Jan. 2001 [doi>10.1145/366378.366385]
• 19: U.S. Department of Education. Audit of the Colorado Student Loan Program's Establishment and Use of Federal and Operating Funds for the Federal Family Education Loan Program, July 2003. Report ED-OIG/A07-C0009.
• 20: U.S. Department of Health and Human Services. Review of Medicare Bad Debts at Pitt County Memorial Hospital, January 2003. Report A-04-02-02016.

INDEX TERMS

Primary Classification:
  D. Software
  D.2 SOFTWARE ENGINEERING
      D.2.2 Design Tools and Techniques

Additional Classification:
  D. Software
  D.2 SOFTWARE ENGINEERING
      D.2.7 Distribution, Maintenance, and Enhancement

  H. Information Systems
  H.4 INFORMATION SYSTEMS APPLICATIONS
      H.4.1 Office Automation
          Subjects: Spreadsheets

General Terms:
Languages, Reliability

Keywords:
end-user software engineering, error prevention, program generation, spreadsheet, template, type system