Report on Progress with the Biz/Ed Virtual Learning Arcade Spreadsheet Models
5th February 2001

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

Introduction - making spreadsheets safe with Model Master

Model Master, or MM for short, is a programming language for describing spreadsheet models in a readable and maintainable way. It allows them to be written using the conventions one expects from a modern programming language, such as comments, identifiers rather than cell numbers, and included libraries rather than drag-and-paste copying. This makes MM programs much easier to read, write and maintain than raw worksheets. The MM compiler translates MM programs into spreadsheet code. I have developed MM over roughly eight months of independent research; it was featured in New Scientist for September 12th 1998 and the Oxford University Gazette for February 4th 1999, and demonstrated at Caleco 98 in Bristol. There is a description of an early version here: this includes links to Web copies of the New Scientist and Gazette features. [ There is a later paper, June 2001, here. ]

Applying Model Master to the Learning Arcade

For the Learning Arcade project, I am working with Andy Beharrell, Andrew Ramsden and Duncan Williamson to use MM for putting up a series of financial models onto the Web. The idea is that one of the economists will write the models directly in Excel. For each model, I shall then use an MM tool to extract the model formulae and layout information from Excel, and to 'decompile' this into an MM program. Besides having the advantages mentioned in the previous section, this states the mathematics behind the model in a way readily understood by students too inexperienced with spreadsheets to gain the same information by examining an Excel worksheet. An example, generated from one of Duncan Williamson's elasticities models, is shown at the end of this report.

The MM program will be able to be compiled back into an Excel file, and I shall put on the site one such file for each model, so that students can download it and run it locally under Excel. However, I shall also arrange for the models to be runnable directly over the Web, using HTML input forms and server-side code in the same way that Virtual Economy does. I intend that, by using MM, it should take around 1-2 hours to convert the average Biz/Ed model to the Web (not counting the time taken to write documentation): longer if the model uses goal-seeking, Visual Basic controls, or other special features.

In theory, we could run on the Web by using ActiveX controls. These, though, can only be run from browsers on Microsoft Windows systems; they also have security holes. To overcome this, the models will be run on the server by a special spreadsheet evaluator, which implements those Excel functions that we use. I shall also implement server-side graph plotting, so that users can request graphs of outputs against inputs, using the same techniques as we have with Virtual Economy. Finally, I may allow users to modify and recompile some of the models, building versions customised for their own purposes. This is useful if, for example, one needs to change the number of months over which an accounting period runs, or the number of input points against which outputs are to be plotted. Such changes to spreadsheet structure are difficult to do when working directly with the raw spreadsheet, but very easy in MM, amounting usually just to a change in one or two compile-time constants. All these things will be done in Java, so should be portable to a variety of server machines; we intend to run the system on a Biz/Ed machine.

Progress fo far

I visited Duncan Williamson to discuss the project, and put his elasticity model up as a Web-based server-side spreadsheet. This was at the start of the project, and involved a lot of manual intervention. I am now developing MM so that the different parts of what was previously a prototype can be used smoothly together for the current project, to automate model conversion. Work done so far on this includes:

In addition, I have discussed technical requirements for the server with Ian Sealy, and identified a conference at which I intend to present the work. This - "Controlling the Subversive Spreadsheet" - is organised by the EuSpRIG spreadsheet-risks research group at Greenwich College, and will take place in Amsterdam on July 5th-6th this year. I am also collaborating with a member of Clemson University on a comparative evaluation of the ease-of-use for students of writing MM programs versus raw spreadsheets; this may eventually give rise to some joint papers.

Future work

Work yet to be done includes:

I hope to finish everything by the start of July, for presentation at the Amsterdam conference. Timing before that is hard to predict, because amongst other commitments, I shall need to spend time working on the IFS's yearly BBC Budget Web site (which will include Virtual Economy), and also this year the BBC Election site (the election is expected to be on April 7th, apparently).

It might be useful to visit Bristol for a week or two during some of this project, and for that, it would help if the ILRT could find some cheap accomodation.

Example MM program

This is the MM program derived from an elasticities model given to me by Duncan Williamson:

  attributes
    new_quantity
    old_quantity
    new_real_income 
    old_real_income 
    demand_change 
    real_income_change 
    income_elasticity
    good_type
  where
    demand_change = new_quantity / old_quantity - 1  and
    real_income_change = new_real_income / old_real_income - 1  and 
    income_elasticity = demand_change / real_income_change  and
    good_type = if( income_elasticity > 0, 
                    "So, this product is a normal good.", 
                    "So, this product is an inferior good." 
                  )  

28th June 2001

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