Enhancing excel business tools with additional relational and recursive capabilities

This paper presents a new plug in that enriches spreadsheet capabilities mainly in what concerns its potential regarding relational queries and recursive computational processes. Currently some apparently trivial and useful queries can only be handled with the support of programming skills. Spreadsheet users with low computer science skills should have a natural and easy way to handle those queries within the spreadsheet, without relying on external programming (e.g., VBA). The tool we have developed can be used with Prolog technology, and provides those features to the most used professional spreadsheet: Microsoft Excel. Throughout the paper we explore the plug-in features with several business examples.info:eu-repo/semantics/acceptedVersio

Wide, long, or nested data? Reconciling the machine and human viewpoints

Data expressed in tables may be re-arranged in various forms, while conveying the same information. This can create a tension when one form is easier to comprehend by a human reader, but another form is more convenient for processing by machine. This problem has received considerable attention for data scientists writing code, but rather less for end user analysts using spreadsheets. We propose a new data model, the “lish”, which supports a spreadsheet-like flexibility of layout, while capturing sufficient structure to facilitate processing. Using a typical example in a prototype editor, we demonstrate how it might help users resolve the tension between the two forms. A user study is in preparation

Xprolog: desenvolvimento de uma folha de cálculo dedutiva

As folhas de cálculo tradicionais apresentam limitações quando se trata de informação simbólica, que não é facilmente captada, principalmente quando se trata de raciocínio dedutivo. A presente dissertação tem como objetivo estender as capacidades das folhas de cálculo tradicionais, tal como o Excel, por exemplo, e desenvolver uma folha de cálculo com capacidades de representação de conhecimento e de raciocínio. Desde a primeira folha de cálculo com capacidades dedutivas durante os anos 80 até ao presente foram surgindo várias ferramentas que visaram colmatar estas limitações, e no presente trabalho pretende-se dar continuidade a essa investigação e desenvolvimento de forma a dar mais um passo em busca de uma combinação cada vez mais perfeita entre as folhas tradicionais e a dedução lógica e representação de conhecimento. Nesta dissertação é apresentado o Xprolog, uma ferramenta desenvolvida para Excel e que interpreta a linguagem Prolog, funcionando como um suplemento (add-in) para Excel. O Xprolog foi desenvolvido dentro da temática de folhas de cálculo dedutivas e, não sendo um sistema que possa neste momento ser considerado como um produto final, tem por objetivo dar um novo alento e promover um tema que tem vindo a perder algum fulgor nos últimos anos.Traditional spreadsheets have limitations when it comes to symbolic information, which is not easily captured, especially when it comes to deductive reasoning. This thesis aims to extend the capabilities of traditional spreadsheets such as Excel, for example, and develop one kind of spreadsheet with representation capabilities of knowledge and reasoning. Since the first worksheet with deductive capabilities during the 80's up to the present, various tools have emerged that aimed to resolve these limitations, and in this work we intend to continue this research and development in order to take another search in step an increasingly perfect combination between traditional spreadsheets and logical deduction and knowledge representation. In this thesis we introduce Xprolog, a tool developed for Excel that interprets the Prolog language, working as an add-in for Excel. The Xprolog was developed within the theme of deductive spreadsheets and not being yet, a system that can be considered as a final product, it aims to give new impetus and promote a theme that has been losing some enthusiasm in recent years

Error sentinel: A Rule-based spreadsheet program for intelligent data entry, error correction, and curation

Within the biological sciences, spreadsheets are commonly used as a data entry and storage medium. While this practice is simple and generally well understood, the unrestrained flexibility of the spreadsheet medium allows errors to accumulate and potentially propagate. Such errors impede accurate analysis, hindering research. The underlying problem is that the error correction facilities of typical spreadsheet programs are lackluster at best, if they exist at all. For this reason, Error Sentinel was developed. Error Sentinel is a spreadsheet program with programmable error correction facilities. These facilities allow users to define exactly what clean data is, along with corrections for erroneous data. Such rules are specified via a custom visual programming language. Once error correction rules are written, users inputting data need not be familiar with the rules or even have programming skills in order to utilize them. Error Sentinel can be used interactively like a typical spreadsheet program, or non-interactively as with more traditional error correction techniques. To test Error Sentinel\u27s real-world capabilities, it was successfully applied to the correction of the mtHaplogroups data set. This application has shown that Error Sentinel requires far less time and code to perform error correction than with previous methods. Benchmarking has shown that such gains are at only a modest cost in performance. While Error Sentinel appears quite simplistic compared to typical spreadsheet programs, its error correction facilities are robust, and it is fully capable of being applied to arbitrary data sets represented in the spreadsheet medium

The Lish: A Data Model for Grid Free Spreadsheets

Throughout the history of the spreadsheet, and throughout the majority of research into improving it, the grid of cells has remained a constant as the underlying data model. An idea that has received recent interest is to provide users with a spreadsheet-like environment based on something other than a grid. The attraction is that if salient features of the data structure can be made more explicit, the machine will be able to provide certain types of error checking and automation. In this project I consider one such grid replacement, a new data model which I call the “lish”. It is based on nested lists of cells, composed according to rules that allow repeating structures to be described. It allows columns, tables, groups of tables and other structures to be treated as coherent objects. This supports a novel form of cell range selection, and allows the machine to ensure that related structures are kept consistent. The model is also more accommodating than the grid of dynamic space allocation, where the number of cells occupied by a result is not known in advance. Then, I develop a “lish calculus”, an extension to vector arithmetic for hierarchical structures that provides a concise notation for calculations with lishes. This simplifies the usual spreadsheet formula expressions, and enables the machine to interpret them consistently with the context in which they are located. I evaluate the lish in the framework of the cognitive dimensions of notations, with the help of example use cases and a user study based on a prototype lish editor. These verify many of the hypothesised advantages, but also reveal some difficulties for users. I close with an analysis of how the lish might be revised to address these shortcomings, while continuing to capitalise on the essential benefits

DOI: 10.1017/S000000000000000 Printed in the United Kingdom NEXCEL, a Deductive Spreadsheet 1

Usability and usefulness have made the spreadsheet one of the most successful computing applications of all times: millions rely on it every day for anything from typing grocery lists to developing multimillion dollar budgets. One thing spreadsheets are not very good at is manipulating symbolic data and helping users make decisions based on them. By tapping into recent research in Logic Programming, Databases and Cognitive Psychology, we propose a deductive extension to the spreadsheet paradigm which addresses precisely this issue. The accompanying tool, which we call NEXCEL, is intended as an automated assistant for the daily reasoning and decision-making needs of computer users, in the same way as a spreadsheet application such as Microsoft Excel assists them every day with calculations simple and complex. Users without formal training in Logic or even Computer Science can interactively define logical rules in the same simple way as they define formulas in Excel. NEXCEL immediately evaluates these rules thereby returning lists of values that satisfy them, again just like with numerical formulas. The deductive component is seamlessly integrated into the traditional spreadsheet so that a user not only still has access to the usual functionalities, but is able to use them as part of the logical inference and, dually, to embed deductive steps in a numerical calculation.