Unit Testing Spreadsheet Logic

Errors in spreadsheets can be very expensive. Many errors are caused by incorrect formulae - and these should be simple to test. Unit testing is a very powerful paradigm from the programming world and could easily be applied to spreadsheets, which are essentially a programming interface.

With Resolver it is already possible to use the Python standard library module unittest [3] to test functions and support code that you use in user code.

Additionally, you can export a spreadsheet as a Python class and then test the formulae it contains. (You can set test data in the spreadsheet and make assertions about the results that should appear in cells containing formulae.) You can see an example of how to do this in Testing Spreadsheets.

We need to work out the best way of integrating a test framework into Resolver itself. Perhaps a separate 'test mode' where constants or data normally fetched from databases can be populated with test data, and Resolver will test the resulting values against a result set that you specify. This could help verify integrity whilst you are building a spreadsheet.

Authentication, Auditing, Versioning

Common problems with spreadsheets include:

• How do I know that this is the latest version?
• Distributing a spreadsheet that can only be modified by authorised individuals
• Being able to check / track who made what changes to a spreadsheet, and perhaps restore portions of it to a previous state

These things aren't currently possible with Resolver, but they are all technical challenges that have already been solved in various ways.

Resolver Server

Because you can export a spreadsheet as code, turning a spreadsheet into a web application (something that whole IT departments are sometimes dedicated to) should be very easy. What would be even nicer would be if you didn't have to! Publishing a Resolver spreadsheet across an intranet or the internet, including allowing some controllable interactions could be done by a Resolver server.

The Resolver Server is actually already happening and is in production use with a couple of customers.

AJAX Resolver

More complicated than just publishing spreadsheets is having a full Resolver interface (with code box!) as an AJAX application. Fortunately Silverlight could make that task a whole lot easier than it would have been otherwise.

Export as .NET assembly

Resolver spreadsheets are currently exported as Python code. Integrating this with a .NET application is relatively straightforward (the IronPython Engine was designed to be embedded and has a nice API), but if you could export spreadsheets as .NET assemblies it would be even more straightforward. (These assemblies would need to use the IronPython engine, but could provide a spreadsheet API that doesn't require any knowledge of IronPython on the part of the .NET programmer.)

Scripting Resolver Internals

Although you can do a lot from user code, there are some things you can't do. You can't create new documents or populate worksheets with data that doesn't have to be calculated again at the next recalculation.

What would be nice would be a full interface for scripting the Resolver interface - preferably both document level (macros attached to a document that can be triggered from buttons or called into from user code) and application level (macros installed into Resolver that can be used with any document).

This could include access to events like:

• Resolver started
• Document loaded
• Document closed
• Recalc started
• Recalc completed
• Resolver closed

This would bring in a whole new world of ways to crash Resolver!

I'd particularly like to be able to execute code when a document is first loaded (this is not too hard to fake-up from user code) and when a document is closed (which I haven't found a way to fake). That way I could automatically start subprocesses that communicate with the document and exit when the document is closed.

A user configurable toolbar would be a good companion for this, allowing you to bind application level macro functions to button.

We will probably start this by exposing a minimal public API for our core classes. That way you will be able to write external tools that can create Resolver documents, or load documents and extract information from then. Exposing that API from within Resolver would be a simple next step from here.

Report Generation

Resolver supports standard printing, which is often all that you need for spreadsheets. Sometimes spreadsheets are created to be part of business reports. For this a more sophisticated reporting system would be useful, including exporting to Word documents with embedded charts and so on. Fortunately this can be added to Resolver as a third party extension and is something I might experiment with myself.

Online Repository of Spreadsheet Modules

There are already several online sites stuffed to the gills with Python modules and .NET libraries that might be useful to spreadsheet creators (see the Useful Links page). Whilst not strictly a future feature of Resolver, it would certainly be a good thing to provide a central location where modules useful to creators of Resolver spreadsheets can live and be distributed. Perhaps in my copious spare time...

An equivalent of easy_install for Resolver modules would make a nice companion to the online repository however.

Excel Interaction

There are various ways that Resolver and Excel could interact. Resolver could drive Excel, Excel could drive Resolver, or they could simply chat to each other.

The difficult part is not making it happen, but working out what are the real use cases for this feature and how to build an intuitive interface.

Typed Cells

If you could specify the type of data a cell contains (date, number, text etc), then Resolver could automatically validate data that you enter - and convert it to the right type for when you access it from formulae and user code.

This is a relatively simple feature, but could be very powerful. It is a natural extension of the 'coloring by type' that Resolver already has.

External API

It would be really nice to start Resolver in 'server mode' [4] and expose an API - perhaps by XML-RPC or a similar mechanism (REST and JSON are the buzzwords of the moment, and for good reason).

That way you could interact with spreadsheets programatically from any other language. This could be run locally (one a single machine), or exposed across a network.

Partial Recalculation

This is a controversial one. All major spreadsheets get a performance boost by only recalculating part of the spreadsheet at a time. If you change a cell, then you only need to recalculate cells that depend on it (directly or indirectly).

That isn't very hard to do, but it doesn't rest easily with the Resolver paradigm of what a spreadsheet is. In a Resolver a spreadsheet is a program, and changing it means that it should be re-executed. User code can do things with cells that we can't track through normal dependency analysis - so only doing a partial recalculate can cause incorrect results.

You actually see this with other spreadsheets. You can make function calls using their scripting languages (VBA for Excel), and those function calls aren't always re-evaluated when you change something. This means that results can be incorrect until you force a full recalc.

Resolver gets round this by recalculating in the background. Even when a recalculation is in progress, the user interface is still responsive. A patch-up function is executed immediately - so that you see some value straight away - once the full recalculate has finished, the results are synced back to the display.

'The Boss' is reluctant to switch to partial recalculation; because of the 'paradigm breakage'. In terms of major improvements to performance we will probably need to offer it as an alternative recalculation model (I think).

More IDE Features in the Codebox

The code editor in Resolver is based on a great text editting component. It isn't a full IDE though, and when editing code there are lots of IDE features that would be useful. Fortunately these can be added incrementally to the code editor.

A Python Console

Ok, so this isn't a vital feature - but given the nature of Resolver it is an obvious one! A console would be great for experimenting with. You could also do interesting things like run a set of calculations a 100 times to do 'what-if' projections. This will be added to Resolver soon, and it can't be soon enough in my opinion...

You can already get a lot of the benefits of a console through judicious use of print statements in your code. Add a print statement and press F9 to force a recalculation, and you will see the result of the print in the output pane at the bottom of the user interface.

Charting

This website shows how to do charting with ZedGraph and image worksheets. It won't be long before a full charting solution is built into Resolver, including floating images on the grid.

Full Undo & Redo in the Code Box

Adding undo and redo for all the spreadsheet actions was a long and painful process, but we did it.

Unfortunately undo and redo in the code box is a different story. Performing a recalculation means recreating the generated code from the spreadsheet, which blows the undo and redo stacks in the code box. This means that undo and redo history in the code editor doesn't persist between recalculations. Fixing this means that we will have to implement full undo and redo ourselves rather than using the built-in features of the codebox. This is quite a lot of work (but relatively straightforward), but will have to be done fairly soon.

Clear Formatting

By default deleting cells doesn't clear the formatting those cells contain. This is standard behaviour for spreadsheets, but Resolver doesn't yet provide an alternative way of clearing formatting.

Import and Export CSV

This isn't a difficult feature to add (although you might be surprised by how many corner cases CSV has). Adding this will make Resolver much more flexible in the number of data sources and programs that Resolver can interact with.

Recently Used Documents

A list of recently opened documents should appear somewhere in the file menu.

Write Back to Databases

Resolver already makes it easy to fetch data from databases. It is possible to write back to databases with user code and buttons, but it ought to be easier.

More Controls in the Grid

Resolver supports buttons and drop down lists in the grid. It is only a matter of time before more controls are added. In fact, being able to create whole application user interfaces as part of resolver would be good, but perhaps belongs in The Future section of this page.

A Standard Directory for Resolver Modules

At the moment there is no standard place to import from modules that you have written yourself. Spreadsheets can import from the Library directory (part of the Resolver installation), and also from the same directory as the spreadsheet, but there is nowhere convenient to put modules that you want to share between spreadsheets.

A CPython Compatibility Layer

Resolver spreadsheets exported as Python classes use our 'Library' code. This is the Workbook, Worksheet, Cell and so on. This code is currently dependent on IronPython - so you can't export a Resolver spreadsheet as code and use it in a CPython application. Adding a compatibility layer that removes the dependency on IronPython wouldn't be very difficult.

This is something I would really like to happen, and isn't very difficult technically. Of course if you implement this yourself, be sure to let us know.

Referencing Cells in Resolver Files

The irony is, that for compatibility with imported files, you can already reference cells in external Excel files. You can't yet reference cells in external Resolver workbooks.

Drag and Drop

You should be able to open '.rsl' files by dropping them onto the Resolver interface. I can't tell you the number of times I have tried this and then remembered that we haven't implemented it yet!

R1C1 Format

Whatever the heck R1C1 format is, we need to support it! Actually I do know what it is, I just don't particularly want it myself - but I guess we need it really.

Restart Mode

That isn't a very good name to describe this feature, but it is still important. The PythonEngine(s) that are at the heart of Resolver documents cache imports in between calculations. This means that imorting functions and code from external modules is very fast after the first time they are executed.

Unfortunately, if you edit the modules in between calculations then Resolver will use the cached one rather than picking up the new version. This is standard Python behaviour but can still be frustrating. A simple solution is to have a way of 'resetting' the engine that clears out imported modules. Probably 'Ctrl-F9' instead of F9 will reclaculate but clearing out all the imported modules first.

One way round this difficulty at the moment, is to use Import with Reload (instead of import) whilst you developing modules to use within your spreadsheets.

Report Crash Dialog

We haven't currently got a public bug tracker, which we will setup. We also need a default crash handler so that these can be reported automatically (giving the user the choice of course).

Defects

These are ordinary bugs in Resolver - the Crashers are listed separately.

There are a host of minor little quirks (the code box doesn't format triple quoted strings properly for example) that I haven't mentioned here, this is only a list of the significant defects. If you discover any more by the way, please let the development team know.

foo = ']]>'

Crashers

Unfortunately some of the bugs in Resolver can cause it to crash, meaning that potentially you could lose data. Luckily most of them are pretty obscure, but it is worth knowing about them so that you can avoid them. Hopefully the existence of this list will act as a spur to the development team to make it shrink.

This list will have disappeared into the annals of history by the time Resolver comes out of beta!