Hi All,

Okay this is possibly a bit long-winded and I could do with some general advice rather than specific. I currently work for an engineering company in the water sector and I’m trying to build an integrated modelling tool for the different aspects of the water supply sector. So for instance we have assets such as water treatment works, pumping stations, the pipe network, and then customers which are represented by customer demand. Currently we have spreadsheet based models of each of these components (and many others) but we have not integrated them into a model of the “whole” system yet, which is what I’ve been asked to try and do.

I did try and host all the sub-models in the same workbook but individually they are quite complex. They tend to work on time steps of 1 hour, so 24 periods a day need to be simulated, over a 5 year investment period. So to model everything together in one workbook and on one computer is very slow. However, I was thinking that many “pieces” of the model are actually quite separate, for instance the treatment works, whilst complex internally, externally it just receives some inputs (raw water, power, chemicals) and exports clean water. So in some ways it’s almost like an encapsulated object that has complex internal workings but that only sends/receives a few messages.

Would it therefore be possible to host each difference “piece” of the system model on a separate machine, and someone how coordinate them so that they receive the data they need at the start of the simulation time step, do their own thing, then publish the data that other parts of the model need at the end of the time step?

For example at the start of “hour 1” the water treatment works gets power from the grid, gets chemicals from the supplier, then produces clean water and outputs it at the end of hour 1, say it outputs 1000 cubic metres of water. Meanwhile the model that works out customer demand is running on a separate machine and at the end of hour 1 it just says “in total I need 900 cubic metres of water”. So 1000 units are produced, 900 consumed, and say the other 100 are kept in a reservoir (another separate sub model).

The issue with all this is coordination. I have been reading about how simulations like this have been created in the past and a key part is finding a way to coordinate the models in terms of the simulation time, and getting them to share the correct info. For the simulation time there would be a Master Clock that waits until all submodels have reported that they have completed their calculations for the current timestep, then moves the clock on one hour, and then tells the submodels to start their calculations for the next hour.

I had in mind some kind of “Master Controller” workbook that contains the Master Clock, and also acts as a repository for the data the submodels create that is available to the other submodels. For instance the water treatment works reports its hourly output, the customer demand reports the hourly demand, maybe a pipe leakage model reports which pipes have leaks on, etc, and each submodel picks the data it needs from this list in order to carry out its own internal calculations.

So to sum up what I am asking is if separate spreadsheets on difference machines can be made to coordinate and share data? Either through a Master Controller spreadsheet, or perhaps by accessing the Internet and having a website with the required data (via the “From Web” option on the Data tab).

I realise there is probably a better platform to do this on then Excel, but I am good with it and also VBA, but more importantly most of my colleuges (who would be creating the submodels and using the system model) are good with Excel but not much else, therefore it is a good platform for us to use.

Any ideas/comments welcome. Thanks in advance

-Rob