Spreadsheet Development Methodologies using Resolver: Moving spreadsheets into the 21st Century

We intend to demonstrate the innate problems with existing spreadsheet products and to show how to tackle these issues using a new type of spreadsheet program called Resolver. It addresses the issues head-on and thereby moves the 1980’s “VisiCalc paradigm” on to match the advances in computer languages and user requirements. Continuous display of the spreadsheet grid and the equivalent computer program, together with the ability to interact and add code through either interface, provides a number of new methodologies for spreadsheet development.

💡 Research Summary

The paper begins by diagnosing the chronic shortcomings of conventional spreadsheet applications, which have largely retained the same grid‑based user interface and proprietary formula language since the era of VisiCalc. These legacy designs create three interrelated problems: (1) users are unaware that a spreadsheet is essentially a program, so the hidden computational flow is opaque; (2) the lack of systematic testing, version control, and modularity makes spreadsheets fragile as data structures evolve; and (3) the divide between business users who build models and IT departments that must maintain or re‑engineer them leads to “fit‑for‑purpose” spreadsheets persisting far beyond their useful life.

To address these issues, the authors introduce Resolver, a next‑generation spreadsheet environment that simultaneously displays the traditional cell grid and the underlying executable Python program. Every cell entry—whether a constant, a formula, or a formatting directive—is automatically translated into a line of Python code. Conversely, users can write Python code in a dedicated “coding pane,” defining functions, importing libraries, or performing arbitrary data manipulation, and those definitions become instantly available to cell formulas. This bidirectional view eliminates the artificial barrier between spreadsheet users and developers.

Resolver’s execution model is divided into six ordered sections. Three sections are system‑generated and immutable: (a) import statements and worksheet creation, (b) constants and formatting derived from grid data, and (c) the formula block generated from cell formulas. The remaining three sections are user‑editable: (1) Pre‑constants, where users can import external modules, connect to databases, and define helper functions before any grid data exists; (2) Pre‑formulae, which runs after constants are available but before grid formulas are evaluated, allowing “tweaking” of calculations; and (3) Post‑formulae, which runs after all formulas have produced results, enabling final polishing, reporting, or exporting. This clear separation of concerns mirrors modern software engineering practices, supporting debugging, unit testing, and incremental development.

By adopting Python as the underlying language, Resolver leverages a rich ecosystem of libraries (e.g., pandas for data analysis, sqlalchemy for database access, requests for web services). Users can therefore replace the common “copy‑and‑paste” data import pattern with declarative, repeatable code that pulls live data from relational databases, APIs, or flat files. The paper highlights that Resolver can import entire tables or arbitrary SQL query results directly into new worksheets, and can keep them synchronized in real time, dramatically reducing manual data‑staleness and error risk.

Collaboration is enhanced through “Shared Worksheets,” which allow multiple users to edit the same underlying data simultaneously. Because each document can reference the shared worksheet, any change propagates instantly to all dependent models, eliminating the need for email‑based template distribution and manual aggregation.

A notable innovation is the support for worksheet‑level formulas. Instead of replicating a cell formula across thousands of rows, a single formula can populate an entire worksheet based on one or more source worksheets. This approach automatically adapts to row or column insertions, preventing the classic “off‑by‑one” errors that plague traditional spreadsheets.

The authors also discuss multidimensional analysis. While pivot tables are the standard tool, they are notoriously fragile. Resolver sidesteps this by treating separate worksheets as relational tables and allowing header‑based joins, filters, and aggregations using pure Python expressions. This provides “slice‑and‑dice” capabilities without requiring users to pre‑define multidimensional cubes, preserving the familiar iterative development style of spreadsheets.

Finally, the paper addresses the long‑standing gap between spreadsheet models and production code. Because a Resolver document is both a spreadsheet and a valid Python script, the computational core can be extracted directly into larger software systems without a costly translation step. The authors contrast this with tools that convert spreadsheets to C++ (e.g., Calc4Web), noting that such generated code is opaque to business users and lacks seamless integration. Resolver’s transparent code view enables business users to see the evolving program as they build it, while IT can instantly reuse the same algorithms in enterprise applications.

In summary, Resolver re‑imagines spreadsheet development by (1) exposing the underlying program in a high‑level, widely‑known language; (2) structuring execution into clearly defined, editable phases; (3) providing native support for external data sources, real‑time collaboration, worksheet‑level formulas, and multidimensional analysis; and (4) bridging the gap between end‑user modeling and enterprise‑grade code. The paper argues that these capabilities collectively overcome the brittleness, maintenance burden, and IT‑business disconnect that have plagued spreadsheets for decades, positioning Resolver as a viable platform for building robust, auditable, and scalable analytical solutions in the 21st‑century enterprise.