15,974 research outputs found
Visual Checking of Spreadsheets
The difference between surface and deep structures of a spreadsheet is a
major cause of difficulty in checking spreadsheets. After a brief survey of
current methods of checking (or debugging) spreadsheets, new visual methods of
showing the deep structures are presented. Illustrations are given on how these
visual methods can be employed in various interactive local and global
debugging strategies.Comment: 11 Pages, 5 Figure
Recommended from our members
Gencel : a program generator for correct spreadsheets
A huge discrepancy between theory and practice exists in one popular application area of functional programming--spreadsheets. Although spreadsheets are the most frequently used (functional) programs, few formal models of computation and type systems exist that would provide the foundation for creating reliable spreadsheets. Consequently, existing spreadsheets contain many errors, some of which have serious impacts. We argue in favor of creating a formal foundation for spreadsheets to help improve spreadsheet systems and make spreadsheets more reliable software assets. To this end, we have developed a table specification language that allows the definition of table templates that describe possible spreadsheet evolutions. This language is based on a table calculus that formally captures the process of creating and modifying spreadsheets. We have developed a type system for this calculus that can prevent type, reference, and omission errors from occurring in spreadsheets. On the basis of the table calculus we have developed Gencel, a system for generating reliable spreadsheets. We have implemented a prototype version of Gencel as an extension of Excel. A pilot study has indicated that the system is well accepted by end-user programmers
Protecting Spreadsheets Against Fraud
Previous research on spreadsheet risks has predominantly focussed on errors
inadvertently introduced by spreadsheet writers i.e. it focussed on the
end-user aspects of spreadsheet development. When analyzing a faulty
spreadsheet, one might not be able to determine whether a particular error
(fault) has been made by mistake or with fraudulent intentions. However, the
fences protecting against fraudulent errors have to be different from those
shielding against inadvertent mistakes. Faults resulting from errors committed
inadvertently can be prevented ab initio by tools that notify the spreadsheet
writer about potential problems whereas faults that are introduced on purpose
have to be discovered by auditors without the cooperation of their originators.
Even worse, some spreadsheet writers will do their best to conceal fraudulent
parts of their spreadsheets from auditors. In this paper we survey the
available means for fraud protection by contrasting approaches suitable for
spreadsheets with those known from fraud protection for conventional software.Comment: 16 Pages including extensive reference
On the Refinement of Spreadsheet Smells by means of Structure Information
Spreadsheet users are often unaware of the risks imposed by poorly designed
spreadsheets. One way to assess spreadsheet quality is to detect smells which
attempt to identify parts of spreadsheets that are hard to comprehend or
maintain and which are more likely to be the root source of bugs.
Unfortunately, current spreadsheet smell detection techniques suffer from a
number of drawbacks that lead to incorrect or redundant smell reports. For
example, the same quality issue is often reported for every copy of a cell,
which may overwhelm users. To deal with these issues, we propose to refine
spreadsheet smells by exploiting inferred structural information for smell
detection. We therefore first provide a detailed description of our static
analysis approach to infer clusters and blocks of related cells. We then
elaborate on how to improve existing smells by providing three example
refinements of existing smells that incorporate information about cell groups
and computation blocks. Furthermore, we propose three novel smell detection
techniques that make use of the inferred spreadsheet structures. Empirical
evaluation of the proposed techniques suggests that the refinements
successfully reduce the number of incorrectly and redundantly reported smells,
and novel deficits are revealed by the newly introduced smells
Training Gamble leads to Corporate Grumble?
Fifteen years of research studies have concluded unanimously that spreadsheet
errors are both common and non-trivial. Now we must seek ways to reduce
spreadsheet errors. Several approaches have been suggested, some of which are
promising and others, while appealing because they are easy to do, are not
likely to be effective. To date, only one technique, cell-by-cell code
inspection, has been demonstrated to be effective. We need to conduct further
research to determine the degree to which other techniques can reduce
spreadsheet errors.Comment: 8 Pages, 4 Figure
A Spreadsheet Auditing Tool Evaluated in an Industrial Context
Amongst the large number of write-and-throw-away spreadsheets developed for
one-time use there is a rather neglected proportion of spreadsheets that are
huge, periodically used, and submitted to regular update-cycles like any
conventionally evolving valuable legacy application software. However, due to
the very nature of spreadsheets, their evolution is particularly tricky and
therefore error-prone. In our strive to develop tools and methodologies to
improve spreadsheet quality, we analysed consolidation spreadsheets of an
internationally operating company for the errors they contain. The paper
presents the results of the field audit, involving 78 spreadsheets with 60,446
non-empty cells. As a by-product, the study performed was also to validate our
analysis tools in an industrial context. The evaluated auditing tool offers the
auditor a new view on the formula structure of the spreadsheet by grouping
similar formulas into equivalence classes. Our auditing approach defines three
similarity criteria between formulae, namely copy, logical and structural
equivalence. To improve the visualization of large spreadsheets, equivalences
and data dependencies are displayed in separated windows that are interlinked
with the spreadsheet. The auditing approach helps to find irregularities in the
geometrical pattern of similar formulas.Comment: 12 Pages, 2 Figures, 4 Table
A Toolkit for Scalable Spreadsheet Visualization
This paper presents a toolkit for spreadsheet visualization based on logical
areas, semantic classes and data modules. Logical areas, semantic classes and
data modules are abstract representations of spreadsheet programs that are
meant to reduce the auditing and comprehension effort, especially for large and
regular spreadsheets. The toolkit is integrated as a plug-in in the Gnumeric
spreadsheet system for Linux. It can process large, industry scale spreadsheet
programs in reasonable time and is tightly integrated with its host spreadsheet
system. Users can generate hierarchical and graph-based representations of
their spreadsheets. This allows them to spot conceptual similarities in
different regions of the spreadsheet, that would otherwise not fit on a screen.
As it is assumed that the learning effort for effective use of such a tool
should be kept low, we aim for intuitive handling of most of the tool's
functions.Comment: 12 Page
Ensuring Spreadsheet Integrity with Model Master
We have developed the Model Master (MM) language for describing spreadsheets,
and tools for converting MM programs to and from spreadsheets. The MM
decompiler translates a spreadsheet into an MM program which gives a concise
summary of its calculations, layout, and styling. This is valuable when trying
to understand spreadsheets one has not seen before, and when checking for
errors. The MM compiler goes the other way, translating an MM program into a
spreadsheet. This makes possible a new style of development, in which
spreadsheets are generated from textual specifications. This can reduce error
rates compared to working directly with the raw spreadsheet, and gives
important facilities for code reuse. MM programs also offer advantages over
Excel files for the interchange of spreadsheets.Comment: 15 pages; substantive references; code example
EuSpRIG TEAM work:Tools, Education, Audit, Management
Research on spreadsheet errors began over fifteen years ago. During that
time, there has been ample evidence demonstrating that spreadsheet errors are
common and nontrivial. Quite simply, spreadsheet error rates are comparable to
error rates in other human cognitive activities and are caused by fundamental
limitations in human cognition, not mere sloppiness. Nor does ordinary "being
careful" eliminate errors or reduce them to acceptable levels.Comment: 7 Pages, 1 Figur
Now You're Thinking With Structures: A Concept for Structure-based Interactions with Spreadsheets
Spreadsheets are the go-to tool for computerized calculation and modelling,
but are hard to comprehend and adapt after reaching a certain complexity. In
general, cognition of complex systems is facilitated by having a higher order
mental model of the system in question to work with. We therefore present a
concept for structure-aware understanding of and interaction with spreadsheets
that extends previous work on structure inference in the domain. Following this
concept, structural information is used to enrich visualizations, reactively
enhance traditional user actions, and provide tools to proactively alter the
overall spreadsheet makeup instead of individual cells The intended systems
should, in first approximation, not replace common spreadsheet tools, but
provide an additional layer of functionality alongside the established
interface. In ongoing work, we therefore implemented a tool for structure
inference and visualization along the common spreadsheet layout. Based on this
framework, we plan to introduce the envisioned proactive and reactive
interaction mechanics, and finally provide structure-aware unctionality as an
add-in for common spreadsheet processors. We believe that providing the tools
for thinking about and interacting with spreadsheets in this manner will
benefit users both in terms of productivity and overall spreadsheet quality.Comment: In Proceedings of the 5th International Workshop on Software
Engineering Methods in Spreadsheets (arXiv:1808.09174
- …