Elamur Research⌕
☰
home / papers / 2015 / Enron's Spreadsheets and Related Emails: A Dataset and Analysis

Enron's Spreadsheets and Related Emails: A Dataset and Analysis

Paper · 2015 · Felienne Hermans and Emerson Murphy-Hill

Summary

Most empirical spreadsheet research has relied on the EUSES corpus. That collection is small (about 4.5 thousand files), was mostly harvested from the public web, and is available only on request, so it says little about how companies actually use spreadsheets. Hermans and Murphy-Hill address this gap by mining the public Enron Email Archive (v1.3). They pulled the attachments out of its Outlook .pst files, removed duplicates using MD5 hashes, and ran the resulting files through the Spreadsheet Scantool, which is built on the Breviz core. The output is an industrial corpus of more than 15,000 spreadsheets plus over 65,000 emails that either carry spreadsheets or discuss them. Their analysis shows that the Enron files are larger and more formula-heavy than EUSES files. About a quarter of spreadsheets containing formulas show at least one Excel error. Function usage is narrow, and code smells, especially long calculation chains, are more frequent than in EUSES. Spreadsheets also come up often in email, typically around errors and updates. The resource gives researchers a realistic, openly accessible benchmark of closed-source business spreadsheets.

Key points

  • Extraction pipeline: The source was 190 .pst files (53 GB) from 130 employees, holding 752,605 emails. From 265,586 attachments, the authors found 51,572 Excel files, of which 16,189 were unique. Of these, 15,770 could be analyzed; the rest were protected or corrupt.
  • Scale compared with EUSES: The corpus has 79,983 worksheets, averaging 5.1 per file versus 3.7 in EUSES. It contains 20,277,835 formulas, 913,472 of them unique. On average, a spreadsheet with formulas holds 2,223 formulas, compared with 372 in EUSES.
  • Excel errors: 2,205 spreadsheets contain errors such as #REF! or #N/A. That is 24% of the 9,120 formula-bearing files and 14% of all files, with about 1.66M erroneous formulas in total.
  • Function usage and smells: 76% of spreadsheets use only the same 15 functions. Smells are substantially more common than in EUSES, most notably long calculation chains.
  • Email behavior: Around 10% of emails either attach or mention spreadsheets. These conversations frequently deal with spreadsheet errors and updates.

Our summary of the paper, in our own words — not its text. The work belongs to its authors; read it at the links above.

 Institutions

🎓North Carolina State UniversityRaleigh · United States15 works · 478 citations🎓Delft University of TechnologyDelft · Netherlands9 works · 771 citations

 Fields, topics and concepts

📚 Shelves 4The field of science this work belongs to (OpenAlex fields).Computer Science · 19,503 worksComputer Vision and Pattern Recognition · 3,621 worksInformation Systems · 1,888 worksSoftware · 150 works

Cite

Cite as Felienne Hermans and Emerson Murphy-Hill. 2015. Enron's Spreadsheets and Related Emails: A Dataset and Analysis. ICSE 2015 (IEEE/ACM).

The paper itself, not this page. BibTeX from the library's record of the work.

@inproceedings{hermans2015enron,
  title = {{Enron's Spreadsheets and Related Emails: A Dataset and Analysis}},
  author = {Felienne Hermans and Emerson Murphy-Hill},
  booktitle = {ICSE 2015 (IEEE/ACM)},
  year = {2015},
  doi = {10.5555/2819009.2819013},
  url = {https://doi.org/10.5555/2819009.2819013}
}

References

40 references, 0 of them with a card of ours. ↗ leads to the work, «find» searches for one we have no id for yet.

  1. C. Scaffidi, M. Shaw, and B. Myers, “Estimating the numbers of end users and end user programmers,” in Visual Languages and Human-Centric Computing, 2005 IEEE Symposium on. IEEE, 2005, pp. 207–214. find
  2. F. Hermans, “Analyzing and visualizing spreadsheets,” Ph.D. dissertation, Delft University of Technology, the Netherlands, 2013. find
  3. C. M. Reinhart and K. S. Rogoff, “Growth in a time of debt,” National Bureau of Economic Research, Tech. Rep., 2010. find
  4. J. Cassidy, “The reinhart and rogoff controversy: A summing up,” April 2013, the New Yorker. find
  5. P. Coy, “Faq: Reinhart, rogoff, and the excel error that changed history,” April 2013, businessWeek. find
  6. R. Abraham and M. Erwig, “Goaldebug: A spreadsheet debugger for end users,” in Proceedings of the 29th international conference on Software Engineering. IEEE Computer Society, 2007, pp. 251–260. find
  7. F. Hermans, M. Pinzger, and A. v. Deursen, “Detecting and visualizing inter-worksheet smells in spreadsheets,” in Proceedings of the 2012 International Conference on Software Engineering. IEEE Press, 2012, pp. 441–451. find
  8. W. Dou, S.-C. Cheung, and J. Wei, “Is spreadsheet ambiguity harmful? detecting and repairing spreadsheet smells due to ambiguous computation,” in Proceedings of the 36th International Conference on Software Engineering. ACM, 2014, pp. 848– 858. find
  9. B. Klimt and Y. Yang, “Introducing the enron corpus.” in CEAS, 2004. find
  10. (2014, Oct.) Wikipedia. [Online]. Available: http://en.wikipedia.org/wiki/Personal Storage Table ↗
  11. R. McKeever and K. McDaid, “How do range names hinder novice spreadsheet debugging performance?” CoRR, vol. abs/1009.2765, 2010. [Online]. Available: http://arxiv.org/abs/1009.2765 — [arXiv:1009.2765](https://arxiv.org/abs/1009.2765) ↗
  12. ——, “Effect of range naming conventions on reliability and development time for simple spreadsheet formulas,” CoRR, vol. abs/1111.6872, 2011. [Online]. Available: http://arxiv.org/abs/1111.6872 — [arXiv:1111.6872](https://arxiv.org/abs/1111.6872) ↗
  13. R. McKeever, K. McDaid, and B. Bishop, “An exploratory analysis of the impact of named ranges on the debugging performance of novice users,” CoRR, vol. abs/0908.0935, 2009. [Online]. Available: http://arxiv.org/abs/0908.0935 — [arXiv:0908.0935](https://arxiv.org/abs/0908.0935) ↗
  14. F. Hermans, M. Pinzger, and A. van Deursen, “Detecting and refactoring code smells in spreadsheet formulas,” Empirical Software Engineering, pp. 1–27, 2014. find
  15. ——, “Detecting code smells in spreadsheet formulas,” in Proc. of ICSM ’12, 2012, pp. 409–418. find
  16. J. Cunha, J. P. Fernandes, J. Mendes, and J. S. Hugo Pacheco, “Towards a catalog of spreadsheet smells,” in Proc. of ICCSA’12. LNCS, 2012. find
  17. B. Nardi and J. Miller, “The spreadsheet interface: A basis for end user programming,” in Proceeding of The IFIP Conference on Human-Computer Interaction (INTERACT). North-Holland, 1990, pp. 977–983. find
  18. G. A. Miller, “The magical number seven plus or minus two: some limits on our capacity for processing information.” Psychological review, vol. 63, no. 2, pp. 81–97, March 1956. find
  19. F. Hermans, “Improving spreadsheet test practices,” in Center for Advanced Studies on Collaborative Research, CASCON ’12, Toronto, ON, Canada, November 18-20, 2013, J. R. Cordy, K. Czarnecki, and S. Han, Eds. IBM / ACM, 2013, pp. 56–69. [Online]. Available: http://dl.acm.org/citation.cfm?id=2555531 ↗
  20. A. Cassidy and M. Westwood-Hill. Removing pii from the edrm enron data set: Investigating the prevalence of unsecured financial, health and personally identifiable information in corporate data. [Online]. Available: http://www.nuix.com/images/resources/case study nuix edrm enron data set.pdf ↗
  21. F. Hermans, M. Pinzger, and A. van Deursen, “Supporting professional spreadsheet users by generating leveled dataflow diagrams,” in Proc. of ICSE ’11, 2011, pp. 451–460. find
  22. R. R. Panko, “Two corpuses of spreadsheet errors,” in System Sciences, 2000. Proceedings of the 33rd Annual Hawaii International Conference on. IEEE, 2000, pp. 8–pp. find
  23. S. G. Powell, K. R. Baker, and B. Lawson, “A critical review of the literature on spreadsheet errors,” Decision Support Systems, vol. 46, no. 1, pp. 128–138, 2008. find
  24. R. Marinescu, “Detecting design flaws via metrics in object-oriented systems,” in Proc. of TOOLS ’01. IEEE Computer Society, 2001, pp. 173–182. find
  25. M. Lanza, R. Marinescu, and S. Ducasse, Object-Oriented Metrics in Practice. Secaucus, NJ, USA: Springer-Verlag New York, Inc., 2005. find
  26. T. L. Alves, C. Ypma, and J. Visser, “Deriving metric thresholds from benchmark data,” in Proc. of ICSM ’10. IEEE Computer Society, 2010, pp. 1–10. find
  27. S. Olbrich, D. S. Cruzes, V. Basili, and N. Zazworka, “The evolution and impact of code smells: A case study of two open source systems,” in Proc. of ESEM ’09, 2009, pp. 390–400. find
  28. A. Bregar, “Complexity metrics for spreadsheet models,” in Proc. of EuSpRIG ’04, 2004, p. 9. find
  29. K. Hodnigg and R. Mittermeir, “Metrics-based spreadsheet visualization: Support for focused maintenance,” in Proc. of EuSpRIG ’08, 2008, p. 16. find
  30. S. Hole, D. McPhee, and A. Lohfink, “Mining spreadsheet complexity data to classify end user developers,” in Proc. of ICDM ’09. CSREA Press, 2009, pp. 573–579. find
  31. J. Raffensperger, “New guidelines for spreadsheets,” International Journal of Business and Economics, vol. 2, pp. 141–154, 2009. find
  32. K. Rajalingham, D. Chadwick, B. Knight, and D. Edwards, “Quality control in spreadsheets: a software engineering-based approach to spreadsheet development,” in Proc. HICSS ’00, 2000, pp. 133–143. find
  33. Y. Ayalew, M. Clermont, and R. T. Mittermeir, “Detecting errors in spreadsheets,” in Proc. of EuSpRIG ’00, 2000, pp. 51–62. find
  34. R. R. Panko, “What we know about spreadsheet errors,” Journal of End User Computing, vol. 10, no. 2, pp. 15–21, 1998. find
  35. S. Powell, K. Baker, and B. Lawson, “Errors in operational spreadsheets: A review of the state of the art,” in Proc. of HICCS ’09. IEEE Computer Society, 2009, pp. 1–8. find
  36. R. Abraham and M. Erwig, “Ucheck: A spreadsheet type checker for end users,” Journal of Visual Languages and Computing, vol. 18, pp. 71–95, 2007. find
  37. C. Chambers and M. Erwig, “Automatic detection of dimension errors in spreadsheets,” Journal of Visual Languages and Computing, vol. 20, pp. 269–283, 2009. find
  38. M. Erwig, “Software engineering for spreadsheets,” IEEE Software, vol. 26, pp. 25–30, September 2009. find
  39. R. Abraham and M. Erwig, “How to communicate unit error messages in spreadsheets,” in Proc of WEUSE ’05, 2005, pp. 1–5. find
  40. F. Hermans, M. Pinzger, and A. van Deursen, “Automatically extracting class diagrams from spreadsheets,” in Proc. of ECOOP ’10, 2010, pp. 52–75. TUD-SERG-2014-021 ISSN 1872-5392 find

Updated

↑ back to top

This page in Markdown → https://md.research.elamur.ai/free/en/papers/hermans-2015-enron-s-spreadsheets-related-emails/