Inferring templates from spreadsheets

ABSTRACT

We present a study investigating the performance of a system for automatically inferring spreadsheet templates. These templates allow users to safely edit spreadsheets, that is, certain kinds of errors such as range, reference, and type errors can be provably prevented. Since the inference of templates is inherently ambiguous, such a study is required to demonstrate the effectiveness of any such automatic system. The study results show that the system considered performs significantly better than subjects with intermediate to expert level programming expertise. These results are important because the translation of the huge body of existing spreadsheets into a system based on safety-guaranteeing templates cannot be performed without automatic support. We also carried out post-hoc analyses of the video recordings of the subjects' interactions with the spreadsheets and found that although expert-level subjects needed less time and developed more accurate templates than less experienced subjects, they did not inspect fewer cells in the spreadsheet. %and found that expert-level subjects spend less time and inspect fewer cells in the spreadsheet and develop more accurate templates than subjects with less experience.

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: Robin Abraham , Martin Erwig, Goal-Directed Debugging of Spreadsheets, Proceedings of the 2005 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC'05), p.37-44, September 20-24, 2005 [doi>10.1109/VLHCC.2005.42]
• 3: Robin Abraham , Martin Erwig , Steve Kollmansberger , Ethan Seifert, Visual Specifications of Correct Spreadsheets, Proceedings of the 2005 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC'05), p.189-196, September 20-24, 2005 [doi>10.1109/VLHCC.2005.70]
• 4: 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.
• 5: 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
• 6: 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]
• 7: 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
• 8: 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]
• 9: Michael J. Coblenz , Andrew J. Ko , Brad A. Myers, Using Objects of Measurement to Detect Spreadsheet Errors, Proceedings of the 2005 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC'05), p.314-316, September 20-24, 2005 [doi>10.1109/VLHCC.2005.67]
• 10: Gregor Engels , Martin Erwig, ClassSheets: automatic generation of spreadsheet applications from object-oriented specifications, Proceedings of the 20th IEEE/ACM international Conference on Automated software engineering, November 07-11, 2005, Long Beach, CA, USA [doi>10.1145/1101908.1101929]
• 11: Martin Erwig , Robin Abraham , Irene Cooperstein , Steve Kollmansberger, Automatic generation and maintenance of correct spreadsheets, Proceedings of the 27th international conference on Software engineering, p.136-145, May 15-21, 2005, St. Louis, MO, USA [doi>10.1145/1062455.1062494]
• 12: Martin Erwig , Robin Abraham , Steve Kollmansberger , Irene Cooperstein, Gencel: a program generator for correct spreadsheets, Journal of Functional Programming, v.16 n.3, p.293-325, May 2006 [doi>10.1017/S0956796805005794]
• 13: 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
• 14: 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]
• 15: Marc Fisher , Gregg Rothermel, The EUSES spreadsheet corpus: a shared resource for supporting experimentation with spreadsheet dependability mechanisms, Proceedings of the first workshop on End-user software engineering, p.1-5, May 21-21, 2005, St. Louis, Missouri
• 16: T. R. G. Green and M. Petre. Usability Analysis of Visual Programming Environments: A `Cognitive Dimensions' Framework. Journal of Visual Languages and Computing, 7(2):131--174, 1996.
• 17: R. L. Hayen and R. M. Peters. How to Ensure Spreadsheet Integrity. Management Accounting, 60(9):30--33, 1989.
• 18: Tomás Isakowitz , Shimon Schocken , Henry C. Lucas, Jr., Toward a logical/physical theory of spreadsheet modeling, ACM Transactions on Information Systems (TOIS), v.13 n.1, p.1-37, Jan. 1995 [doi>10.1145/195705.195708]
• 19: 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
• 20: Clayton Lewis , Gary Olson, Can principles of cognition lower the barriers to programming?, Empirical studies of programmers: second workshop, Ablex Publishing Corp., Norwood, NJ, 1987
• 21: R. Mittermeir , M. Clermont, Finding High-Level Structures in Spreadsheet Programs, Proceedings of the Ninth Working Conference on Reverse Engineering (WCRE'02), p.221, October 29-November 01, 2002
• 22: R. R. Panko. Applying Code Inspection to Spreadsheet Testing. Journal of Management Information Systems, 16(2):159--176, 1999.
• 23: 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.
• 24: Kamalasen Rajalingham , David Chadwick , Brian Knight , Dilwyn Edwards, Quality Control in Spreadsheets: A Software Engineering-Based Approach to Spreadsheet Development, Proceedings of the 33rd Hawaii International Conference on System Sciences-Volume 4, p.4006, January 04-07, 2000
• 25: K. Rajalingham, D. R. Chadwick, and B. Knight. Classification of Spreadsheet Errors. Symp. of the European Spreadsheet Risks Interest Group (EuSpRIG), 2001.
• 26: Boaz Ronen , Michael A Palley , Henry C. Lucas, Jr., Spreadsheet analysis and design, Communications of the ACM, v.32 n.1, p.84-93, Jan. 1989 [doi>10.1145/63238.63244]
• 27: 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]
• 28: P. Saariluoma and J. Sajaniemi. Extracting Implicit Tree Structures in Spreadsheet Calculation. Ergonomics, 34(8):1027--1046, 1991.
• 29: J. Sajaniemi. Modeling Spreadsheet Audit: A Rigorous Approach to Automatic Visualization. Journal of Visual Languages and Computing, 11:49--82, 2000.
• 30: Christopher Scaffidi , Mary Shaw , Brad Myers, Estimating the Numbers of End Users and End User Programmers, Proceedings of the 2005 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC'05), p.207-214, September 20-24, 2005 [doi>10.1109/VLHCC.2005.34]
• 31: Thompson SH Teo , Margaret Tan, Quantitative and Qualitative Errors in Spreadsheet Development, Proceedings of the 30th Hawaii International Conference on System Sciences: Information System Track-Organizational Systems and Technology, p.149, January 03-06, 1997
• 32: 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.
• 33: 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.
• 34: A. G. Yoder and D. L. Cohn. Real Spreadsheets for Real Programmers. In Int. Conf. on Computer Languages, pages 20--30, 1994.

INDEX TERMS

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

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

General Terms:
Design, Experimentation, Human Factors

Keywords:
end-user software engineering, spreadsheet specification, template inference