ABM Sheets

Spreadsheet with Built-in Time

Tomáš Boďa (tominoboda@gmail.com)

Tomáš Petříček (tomas@tomasp.net)

Faculty of Mathematics and Physics

Charles University

Prague, Czech republic

Table of Contents

  1. Abstract
  2. Cannonball
  3. Introduction
  4. Time-aware Simulations
  5. Contributions
  6. Formative Interviews
  7. System Overview
  8. Case studies
  9. Limitations & Future Improvements
  10. Related work
  11. Conclusion
  12. References

Abstract

Spreadsheets are powerful tools for data analysis and modeling, but they are inherently limited to two dimensions - rows and columns, making it difficult to represent systems that evolve over time. This limitation poses challenges for domains like agent-based modeling, physics simulations, or financial market analysis, where the concept of time is fundamental. In particular, agent-based models require tracking multiple agents, each with evolving attributes, across discrete time steps - something traditional spreadsheets struggle to express without resorting to duplication and convoluted formulas.

We address this limitation by extending the spreadsheet paradigm with a built-in support for discrete time, allowing cells to reference their own values from previous time steps directly. This extension preserves the familiar spreadsheet paradigm while adding a powerful new dimension of time. With this approach, users can build and explore dynamic models, such as agent-based simulations with the same ease as traditional spreadsheet calculations. In this paper, we demonstrate how this time-aware spreadsheet enables new kinds of simulations and iterative processes that are impractical or impossible to implement in conventional spreadsheet software like Microsoft Excel or Google Sheets.

Cannonball

Let's start by showcasing ABM Sheets on a simple physics simulation of shooting a cannonball in a two-dimensional space.

Live demo of the model can be found on this link.

A cannonball has the following properties:

  • X and Y - the cannonball's coordinates
  • VX and VY - the cannonball's X and Y velocity
  • ANGLE - initial shooting angle
  • SPEED - initial shooting speed

Then there is a list of constant values:

  • GRAVITY - gravity
  • AIR_RESISTANCE - air resistance
  • TIME_FACTOR - time factor

The physics simulation of a cannonball is modelled as following:

  • ANGLE = 20 (can be tweaked)
  • SPEED = 50 (can be tweaked)
  • VX
    • initially set to SPEED * COS(RADIANS(ANGLE))
    • then updated as VX + -AIR_RESISTANCE * VX * TIME_FACTOR
  • VY
    • initially set to SPEED * SIN(RADIANS(ANGLE))
    • then updated as VY + (-GRAVITY + -AIR_RESISTANCE * VY) * TIME_FACTOR

Finally, the x and y coordinates rendered in the graph are modelled as following:

  • X = X + VX * TIME_FACTOR
  • Y = Y + VY * TIME_FACTOR

Notice how the X, Y and VX, VY cells self-reference their own values, calculating their new values from previous time steps.


Introduction

Spreadsheets are among the most widely used tools for data analysis, planning, and modeling due to their flexibility, accessibility, and low barrier to entry. They offer a familiar interface based on a two-dimensional grid of cells, which makes them ideal for a broad range of applications, from financial modeling to project management and scientific calculations. However, their core structural simplicity also introduces limitations, particularly when representing models that extend beyond two dimensions.

In recent years, several research efforts have explored ways to expand the capabilities of spreadsheets to address more complex computational needs. Espalier, for example, introduces a novel paradigm that combines spreadsheet usability with SQL-like expressiveness, enabling users to build rich organizational applications while editing hierarchical structured data within a visual environment. Nezt offers a live programming environment inspired by spreadsheets but enhanced with hierarchical cells and reusable custom functions, aiming to support application prototyping and scientific computing with greater expressiveness. Addressing the growing importance of real-time applications, a prototype spreadsheet model for streaming data demonstrates how users can stream and manipulate web-based data within a spreadsheet using temporal metadata and dynamic formulas. Finally, this paper looks at spreadsheets from a broader perspective and treats them as a programming language, highlighting their liveness, directness, and ease of deployment, while surveying research directions in spreadsheet software engineering such as testing, refactoring, and clone detection.

In this paper, we focus on the problem of extending the traditional spreadsheet paradigm with discrete time. There are multiple domains where discrete time simulations are ubiquitous. Those include physics simulations, agent-based models or financial market analyses. The typical way of modeling time in spreadsheets is to reserve one of the available dimensions (rows or columns) for modeling time steps. For instance, the model may add a new row for each new time step. However, reserving one of the two available dimensions for time leaves the spreadsheet with only one dimension for the other aspects of the model, eventually making it difficult to model more complex models.

Therefore, there is a strong motivation for directly supporting time-aware models in a spreadsheet environment. Spreadsheets offer a transparent, interactive, and user-friendly platform that encourages experimentation and rapid prototyping. Making them more compliant with complex modeling would significantly lower the barriers to entry for users without formal programming experience, while still supporting powerful and expressive simulations. By embedding time into the core data structure, ABM Sheets provides a seamless way to model state changes over time, making it a powerful tool for various time-aware simulation approaches.

Time-aware Simulations

Discrete time is a fundamental component of many modeling domains, yet it remains difficult to represent effectively within the traditional spreadsheet paradigm, particularly as the model complexity grows.

Agent-based modeling (ABM) is one such domain. ABMs simulate the behavior of individual entities (agents) over time, requiring the tracking of multiple attributes for each agent across discrete time steps. This inherently introduces three-dimensional to the model: a set of agents, a set of attributes for each agent, and time. Traditional spreadsheets, constrained to two dimensions, force users into workarounds, such as encoding time along rows or columns, which quickly become error-prone and difficult to maintain as models scale in size and complexity.

Moreover, physics simulations present similar challenges. While simple physical phenomena can often be modeled analytically, more sophisticated simulations, especially those involving numerical integration of differential equations, require iterative, time-stepped computation. One example is the cannonball simulation presented at the beginning of this paper, as it illustrates the need to track object states over time. Expressing such evolving systems within conventional spreadsheet interfaces quickly becomes difficult, if not infeasible.

Last but not least, financial modeling also heavily relies on temporal data. Whether analyzing historical trends, simulating future market behaviors, or evaluating investment strategies, financial models must track entities whose values evolve over time. Representing these changes accurately and efficiently within traditional spreadsheets often requires duplicating logic or data, increasing the likelihood of errors and limiting the model's extensibility.

Contributions

This paper makes the following contributions:

Formative Interviews

Prior to implementing the system, a series of formative interviews was conducted to discover limitations of the traditional spreadsheet software, such as Microsoft Excel or Google Sheets, when applied to agent-based modeling tasks. Participants were given a simple agent-based modeling scenario and asked to implement it in Microsoft Excel using whatever approach they found most appropriate.

Participants

The study included four participants. Two had extensive experience with Microsoft Excel and used it regularly in either professional or personal contexts. The other two were university students studying computer science, with limited prior exposure to spreadsheet tools, but with solid foundation in programming concepts.

Assignment

The modeling assignment involved simulating a footrace between agents. Each agent had an individual speed and advanced through a one-dimensional space based on that speed. The simulation required agents to update their positions over discrete time steps, and to halt once any agent crossed the finish line — a classic example of an agent-based system with evolving state and conditional behavior over time.

Findings

Interestingly, one participant chose to avoid the time-stepped approach altogether, instead calculating agents' final positions using direct formulas. When asked why they opted to bypass modeling with time steps, they explained:

"I guess because once you set the rows to time steps, you have lost a dimension of your spreadsheet and so now everything is one-dimensional and everything about the dynamics has to be embedded in a single formula and that can get really complicated, so I was trying to get away from not having to do that for as long as possible. You've only got two dimensions, and if you use one for time, everything else becomes miserable."

This feedback directly supports the core hypothesis behind ABM Sheets - that embedding time as a native dimension in spreadsheet environments could alleviate major usability and expressiveness issues in modeling dynamic systems.

Another participant offered a valuable reflection on the enduring appeal of spreadsheet software:

"One of the benefits of Excel, as you probably know, is just the whip attitude of it, you know, this kind of interactive, reactive programming environment. The ability to mix data and formulas/programming in the same environment is extremely attractive."

This insight underscores the power of spreadsheets as hybrid tools for both data manipulation and lightweight programming. ABM Sheets aims to build on this foundation by preserving the interactivity and flexibility of traditional spreadsheets while extending their capabilities to support time-evolving simulations in a more intuitive and maintainable way.

Both of these insights gave us valuable information that strenghtens the idea of ABM Sheets.

System Overview

ABM Sheets is designed to closely resemble traditional spreadsheet software, particularly Microsoft Excel, in order to minimize the learning curve for existing users. The user interface, formula syntax, and user interaction patterns are intentionally modeled after familiar spreadsheet conventions. This ensures that users can quickly adapt to the system while benefiting from its extended capabilities.

Built-in Discrete Time

The core innovation in ABM Sheets is the integration of discrete time as a native feature of the spreadsheet environment. This addition introduces several fundamental changes to how cells behave and how formulas are interpreted.

Most notably, cells in ABM Sheets are allowed to reference themselves within their own formulas. When a cell references itself, the value retrieved corresponds to the previous time step. To enable this behavior, each time-dependent cell must be initialized with a starting value — referred to as the default formula — from which the simulation can evolve over time.

Each cell in ABM Sheets can therefore have two formulas:

If a cell attempts to reference itself without a defined default formula, the evaluation results in an error due to the absence of an initial state.

To define both formulas within a single cell, ABM Sheets uses a dual-assignment syntax - the first = introduces the default formula, and the second = introduces the primary formula. If only one formula is provided, it is treated as the primary formula and applies uniformly across all time steps without referencing prior state.

To define a simple counter that starts at 1 and increments by 1 at each time step, the formula in cell A1 would be = 1 = A1 + 1:

Cell References

In a spreadsheet model where cells reference themselves arbitrarily, a mechanism to ensure that all cells are evaluated in correct and consistent order must be enforced. For instance, if A1 references B1, the system must make sure to evaluate B1 prior to A1 in order for A1 to have the most up-to-date value of B1.

To enforce this ordering, ABM Sheets employs a topological sorting algorithm that analyzes cell dependencies and determines a valid evaluation sequence. This guarantees that each cell is computed only after all the cells it depends on have been evaluated.

However, this approach can encounter issues when cyclic dependencies exist — when two or more cells depend on each other either directly or indirectly. For instance:

This configuration creates a circular dependency, which prevents topological sorting and leads to an evaluation error. ABM Sheets addresses this problem by leveraging default formulas to break dependency cycles across time steps. By providing an initial value through a default formula, a cell can be evaluated independently at the first time step, allowing dependent cells to reference its value without forming an immediate cycle. For instance:

In this revised version, A1 is initialized to 1 at the first time step. As a result, B1 can safely reference A1, and both cells can be evaluated without creating a circular dependency in the current time step. In subsequent time steps, values propagate forward using the established evaluation order, maintaining consistency and correctness.

This mechanism allows ABM Sheets to support recursive and interdependent cell logic while preserving the integrity of the simulation across discrete time steps.

Case Studies

Wolf-sheep Predation

The wolf-sheep predation model explores the stability of predator-prey ecosystems. It is an agent-based simulation that consists of two types of agents - predators (wolves) and prey (sheep). Wolves attempt to eat the sheep in order to survive. If their energy runs out, they die and respawn only after eating a sheep that crosses their path. On the other hand, if a sheep gets eaten, it dies and respawns after a certain time passes. This simulation aims to observe the changes in the wolf and sheep population over time.

Live demo of the model can be found on this link.

We can observe that at first, the population of sheep decreases as the wolves eat the sheep. As there is less sheep for the wolves to eat, their energy runs out shortly, resulting in the decrease of the wolf population. With this decrease, the sheep can regenerate and respawn and with less wolves to eat them, they prosper. However, with more sheep in the area, there is a higher probability of an encounter with the wolves, resulting in the increase of the wolf population.

Both the wolves and the sheep have X and Y coordinates representing their position in the area. We have a helper column POSITION calculated as CONCAT(X, Y), which gives us a unique identifier of their position.

Wolves have a column SHEEP ID representing the ID of the sheep in the same position as the corresponding wolf. In each step, if a sheep doesn't cross the wolf's position, their HEALTH is decremented by 1. Otherwise their HEALTH is regenerated as they eat the sheep. This is modelled as:

IF(AND(SHEEP_ID != "NONE", SHEEP_STATUS == "ALIVE"), MIN(MAX_HEALTH, HEALTH + 5), MAX(0, HEALTH - 1))

Sheep's logic is implemented in a similar way, however, when they are eaten by a wolf, they are resurrected after a certain period of time passes. The respawn time of the sheep is modelled as:

RESPAWN = IF(STATUS == "ALIVE", 0, IF(AND(PREV(STATUS) == "ALIVE", STATUS == "DEAD"), 10, RESPAWN - 1))

Notice the use of PREV in this formula. The PREV function takes in a cell reference and returns its value in the previous time step. In this way, we can observe the change of the sheep's STATUS in time and respond to the changes by updating the RESPAWN accordingly. The sheep's STATUS is modelled as follows:

STATUS = IF(STATUS == "DEAD", IF(RESPAWN > 0, "DEAD", "ALIVE"), IF(WOLF_ID != 0, "DEAD", "ALIVE"))

If the sheep is alive and a wolf crosses their way, the sheep becomes dead. If it is dead and the respawn time is greater then zero, it stays dead, otherwise is becomes alive again. The two above formulas inherently use the built-in mechanism of discrete time to observe changes in time and respond to them accordingly.

Running Race

The running race model is a simple agent-based simulation modeling a set of runners competing in a running race. Each runner has a certain speed assigned at the beginning and this speed is randomly incremented/decremented in each time step, not going below 0. If any agent crosses the finish line, the race stops and the winner is displayed.

Live demo of the model can be found on this link.

Each runner has a POSITION, SPEED and an indicator of whether it has crossed the finish line labeled FINISHED. The POSITION is initially set to 0 and calculated as:

POSITION = POSITION + SPEED.

The SPEED is initially set to RANDBETWEEN(1, 5) and calculated as:

SPEED = MAX(1, SPEED + (RAND() - 0.5) * 2).

The speed is randomly incremented/decremented in each new time step, but we make sure it never goes below 1 in order for the agents to always move forward. Then, for each agent we check whether they crossed the finish line using:

FINISHED = IF(POSITION >= FINISH_LINE, 1, 0)

Finally, we check if any runner has crossed the finish line by summing all FINISHED values and checking if their sum is greater than 0:

ANY_FINISHED = IF(SUM(FINISHED_FIRST, FINISHED_LAST) > 0, 1, 0)

If any agent has finished the race, we stop updating the other agents' positions and speeds and the race has ended.

Hotelling's Law

The Hotelling's Law model examines the optimal pricing of goods that stores sell in order to maximize their profit. In this models, we have stores and customers placed randomly in a one-dimensional space (line). Each store is assigned a random pricing of their goods. In each new time step, each store attempts to increase/decrease the pricing of their goods to see if they can attract more customers. If so, the stores update their prices. In the graph, we can observe the changing prices of the three stores in the model.

Live demo of the model can be found on this link.

This model is quite complex and difficult to describe using formula pseudocode. The main idea is that we create an interest matrix where rows represent customers and columns represent stores. Each cell in the matrix represents the interest of the given customer for the given store, calculated as:

INTEREST = ABS(STORE_POSITION - CUSTOMER_POSITION) + STORE_PRICE

Each store tries to increment or decrement their price to see whether they can gain more customers with their new pricing strategy. For this, we create two new matrices with the same structure as the matrix above, but we count in the new incremented/decremented prices.

Each customer looks at the interest matrix and chooses to go to the store with the lowest interest value. Each store then sums their total number of customers. This is performed also for the incremented/decremented interest rates. If a store gains more customers by either incrementing or decrementing their price, they update their current price based on the new best strategy. This process is repeated in each step of the simulation.

Limitations & Future Improvements

While ABM Sheets introduces a novel approach to integrating discrete time into spreadsheets, several limitations and opportunities for improvement remain.

Limitations

Future Improvements

Numerous efforts have been made to extend the traditional spreadsheet paradigm by enhancing its expressive power. In this section, we highlight several of these works and their key contributions. The complete list of references can be found in this section.

There have been attempts to integrate continuous streaming data, such as data from web services, to spreadsheets ([2], [3]), enabling the development of analytics applications without requiring formal programming experience. Gneiss [1] has built upon this idea by presenting a dynamic website builder that utilizes spreadsheets, directly mapping cells with streaming data to GUI elements.

The pioneer work on Forms/3 [4] shows that it is possible to treat spreadsheets as an end-user programming system, introducing a declarative way of programming together with the notion of built-in time into the spreadsheet model.

Mito [6] takes a different approach by bridging spreadsheets with Python, enhancing spreadsheet expressiveness while simultaneously providing a pathway for users, especially those familiar with tools like Microsoft Excel but lacking formal programming experience, to learn Python.

Last but not least, [11] proposes extending spreadsheets with user-defined, reusable functions, with an objective to bring more expressive power of modern programming languages to the spreadsheet paradigm.

Conclusion

ABM Sheets demonstrates that it is both possible and practical to extend the spreadsheet paradigm with native support for discrete time, opening new possibilities of constructing dynamic, time-aware models without abandoning the intuitiveness and transparency of traditional spreadsheets. By allowing cells to reference their own prior states and by embedding time as a first-class concept, our approach increases the expressive power and unlocks a wide range of applications, from agent-based simulations to physical systems and economic models, that are otherwise difficult to implement in conventional spreadsheet tools.

Through case studies and user feedback, we have shown how this time-aware extension lowers the technical barriers for simulation modeling in spreadsheets, offering a powerful and accessible environment for experimentation, analysis and learning. We see this work as a step toward reimagining spreadsheets not just as static data tools, but as expressive environments for modeling complex, time-aware systems.

References

  1. Chang, K. S. P., & Myers, B. A. (2014, October). Creating interactive web data applications with spreadsheets. In Proceedings of the 27th annual ACM symposium on User interface software and technology (pp. 87-96)

  2. Chang, K. S. P., & Myers, B. A. (2015, April). A spreadsheet model for handling streaming data. In Proceedings of the 33rd Annual ACM Conference on Human Factors in Computing Systems (pp. 3399-3402).

  3. Vaziri, M., Tardieu, O., Rabbah, R., Suter, P., & Hirzel, M. (2014, July). Stream processing with a spreadsheet. In European Conference on Object-Oriented Programming (pp. 360-384). Berlin, Heidelberg: Springer Berlin Heidelberg.

  4. Burnett, M., Atwood, J., Djang, R. W., Reichwein, J., Gottfried, H., & Yang, S. (2001). Forms/3: A first-order visual language to explore the boundaries of the spreadsheet paradigm. Journal of functional programming, 11(2), 155-206.

  5. Bažant, P., & Maršálková, M. (2018, April). A non-tabular spreadsheet with broad applicability. In Companion Proceedings of the 2nd International Conference on the Art, Science, and Engineering of Programming (pp. 161-165).

  6. Diamond-Reivich, J. (2020). Mito: Edit a Spreadsheet. Generate Production Ready Python. In LIVE: Workshop on Live Programming.

  7. Jarvis, W. (2019). Styling in Espalier: a spreadsheet tool for manipulation of structured data. MSc dissertation, Massachusetts Institute of Technology.

  8. Nardi, B. A., & Miller, J. R. (1990). The spreadsheet interface: A basis for end user programming (Vol. 10, No. 647402.725609). Hewlett-Packard Laboratories.

  9. Hermans, F., Jansen, B., Roy, S., Aivaloglou, E., Swidan, A., & Hoepelman, D. (2016, March). Spreadsheets are code: An overview of software engineering approaches applied to spreadsheets. In 2016 IEEE 23rd International Conference on Software Analysis, Evolution, and Reengineering (SANER) (Vol. 5, pp. 56-65). IEEE

  10. Sarkar, A., Borghouts, J. W., Iyer, A., Khullar, S., Canton, C., Hermans, F., ... & Williams, J. (2020, April). Spreadsheet use and programming experience: An exploratory survey. In Extended Abstracts of the 2020 CHI Conference on Human Factors in Computing Systems (pp. 1-9).

  11. Jones, S. P., Blackwell, A., & Burnett, M. (2003, August). A user-centred approach to functions in Excel. In Proceedings of the eighth ACM SIGPLAN international conference on Functional programming (pp. 165-176).

  12. Gulwani, S., Harris, W. R., & Singh, R. (2012). Spreadsheet data manipulation using examples. Communications of the ACM, 55(8), 97-105.