Report on Progress with the Biz/Ed Virtual Learning Arcade Spreadsheet Models
30th August 2001

[ Jocelyn Ireson-Paine's Home Page | Publications ]

Introduction and progress so far

For an introduction to the project, see my previous report.

I've made quite a bit of progress since then, though slowed by pressure from other projects, as well as the computer and shoulder problems I've had. This report shows some demonstrations of stages in converting our house price model from an Excel spreadsheet to a server-side program that anyone can use over the Web. As well as putting the models on the Web, I have also developed a Web-based interface for doing the conversions. This is because I need to display quite a lot of information, so that I can easily check for things like duplicate names, undefined inputs, unused cells, and so on. The easiest thing way to do this was via the Web, avoiding the complexities of GUI programming. A useful offshoot is that this Web interface has turned out to be extremely close to the proposal we talked about in June, a "national model repository" that would allow teachers to upload their own spreadsheets and convert them into a server-side version permanently resident on our site.

The first page is the model just after having been decompiled from an SLK file. The page shows an MM source listing decompiled from the Excel file. At this stage, all attributes in it have the same name as the cell they came from. We'll change the names later, for error-reporting, and to help when producing a readable description of the model. This source listing is useful as a concise summary of the spreadsheet, but it's also possible to treat it as the main definition of the spreadsheet - I'm going to add an option which would put it into a text box, allowing you to edit and recompile it.

Following the source listing is a list of addresses of attributes. Since all attributes have the same name as their cells, this is fairly uninteresting, consisting of lots of lines like C11 -> C11, where the first C11 is a cell address and the second is the name of the attribute in it. This gets more interesting in the fourth stage of conversion, where MM has renamed attributes so they have meaningful names instead of cell locations.

After that is a list of annotations, e.g. cells which contain labels that aren't part of the calculation. This is empty at the moment, but becomes more interesting in the the second stage of conversion, where MM has guessed which cells are annotations. Finally, there's a "values" view of the spreadsheet (running it with the inputs it contained when given to me), and a formula view.

To help relate the different parts of this page, you'll see clusters of little links like "@ a e f" or "a e f v". Each cluster relates to one attribute, and each link goes to a different view of it in the same page. "@" (for At) goes to the "Addresses" listing, telling you which cell the atttribute is at. "a" (for Attribute) goes to the top of the source listing, showing the attribute in the list of attributes. "e" (for Equation) goes to the equation defining the attribute in that source listing. "f" (for Formula) goes to the appropriate cell (or at least its row) in the formula view of the spreadsheet. "v" (for Value) goes to the appropriate cell in the values view. In the source listing, these links follow a // symbol. This is an MM comment marker (as in C or Java), so that if you try compiling the source listing when it contains these links, it is still valid.

Here is the same model after asking MM to guess which cells are annotations. Note that these are now shown in grey in the formula and values views, and that they appear in the annotations listing. MM has also removed them from the equations and list of attributes in the source listing, because although they are cells, they're not really part of the calculation.

One stage further on is the model after asking MM to guess meaningful names for the attributes. It does this by looking to the left and above each atttribute's cell, seeing whether there's an annotation there. It shows all these in a text field to the right of the attribute in the source listing - you can edit these and then press Replace at the bottom of the listing to make the replacements.

Finally, we have the model after doing these replacements The equations in the source listing now form a readable description, which you could tidy and use in documentation. They also appear in the formula view. The address listing is now more interesting, because attributes don't have the same names as their cells.

Other models tested on this are:

Future work

These pages aren't yet interactive, because of problems with the Kawa implementation. Once that's fixed, it shouldn't be long before I can demonstrate interactive conversion, and some fully converted models.

Something I could easily add is a form that allows you to specify units for certain attributes (e.g. saying that Population has units MPeople), and then works out the derived dimensions for the rest, showing them and warning if any of the equations are trying to add or subtract incompatible units.

When I have the final version of this up, it will also be possible to specify which cells are inputs and outputs, and to change the layout. And it will also be possible to edit the MM source listing and compile that into a spreadsheet, rather than sticking with the decompiled original.

You might like to think about how the VLA version of this model is to look, since you probably don't want all the attributes in the same table. It's probably useful if I add something that allows you to group attributes into separate tables (e.g. by having a menu with numbers on in each cell, whereby you could tell MM that HouseholdIncome and InterestRate go in table 1, IncomeElasticity goes in table 2, etc.). It's possible to edit the HTML directly, but you tend to get lost amongst all the <TR> and <TD> elements.

30th August 2001

[ Jocelyn Ireson-Paine's Home Page | Publications ]