Subscript Data From Spreadsheets

impactdynamics · Post by **impactdynamics** » Mon Feb 05, 2007 11:54 am

I have built a client model which pulls in data from an Excel spreadsheet using GET XLS DATA and GET XLS CONSTANTS using Names in the formulae - works very well.

I now want to make the model multi-dimensional using subscripts

Please advise or point me in the right direction in order to determine:

1. How to format the subscripted time series data and constants in the spreadsheet

2. How to name the subscripted data in the spreadsheet - ie do you need to create a name for each separate data item or just the first cell in a suitably formatted table

Many thanks

Roger

[Edited on 2-5-2007 by impactdynamics]

Lee Jones · Post by **Lee Jones** » Mon Feb 05, 2007 12:16 pm

Roger,

I have attached an example model pulling data from a spreadsheet into subscripted variables and also using named ranges. I will post the accompanying spreadsheet in the next post.

You will see in the example that Demand, Supply Available and Priority are read from the spreadsheet and are subscripted. My preferred method is to create a single named range in Excel for all elements of a single subscripted constant, although the manual also suggests entering a reference to the first cell only.

Hope this helps

Lee

Lee Jones · Post by **Lee Jones** » Mon Feb 05, 2007 12:16 pm

.. and here is the excel data file for the sample model.

Lee

impactdynamics · Post by **impactdynamics** » Mon Feb 05, 2007 12:58 pm

Thanks Lee

yes it does - thanks

Please see marked up spreadsheet with call out questions

Rog

impactdynamics · Post by **impactdynamics** » Mon Feb 05, 2007 5:27 pm

thanks Lee - this is great

Now the Vensim model makes it much clearer.

It gives me a great start - I shall experiment with this

What is the significance of the ? in '?data.xls' - I thought you needed the whole filename ie 'xlsdatafile.xls' - is this some kind of useful shorthand?

Does the datafile always have to be in the same directory as the model?

What do you do if you need 3 dimensional data - as I do - Region-Inspection-Resource?

What would the Time Series Demand array look like if you wanted another dimension on it eg Demand for each region?

Many thanks again

Rog

Lee Jones · Post by **Lee Jones** » Tue Feb 06, 2007 11:01 am

Originally posted by impactdynamics
What is the significance of the ? in '?data.xls' - I thought you needed the whole filename ie 'xlsdatafile.xls' - is this some kind of useful shorthand?

Does the datafile always have to be in the same directory as the model?
Rog

data.xls is a placeholder reference to the actual xls file used by the model (hence the "?").

Go to menu option model>settings>xlsfiles and enter "Data" in the "For the reference" field (no need to enter a "?" char). Then select the actual spreadsheet to refer to. This approach means that if you change the spreadsheet name you don't have to modify all the equations using getxls functions.

Also, there is no requirement to have the spreadsheet in the same directory as the model.

impactdynamics · Post by **impactdynamics** » Tue Feb 06, 2007 3:05 pm

many thanks Lee

Best regards

Rog

Lee Jones · Post by **Lee Jones** » Wed Feb 07, 2007 9:59 am

Originally posted by impactdynamics
Please see marked up spreadsheet with call out questions

Just seen this. In my preferred method, all cells covering data for a single subscripted array are named (have a look at the named ranges already entered in the spreadsheet). So, for example, in the Demand section, the cells containing the figures 500, 300 and 750 have been grouped and named "Demand" for reference in the relevant GET_XLS_CONSTANTS function in the Vensim model.

impactdynamics · Post by **impactdynamics** » Wed Feb 07, 2007 10:18 am

this seems to work really well - thanks Lee

Rog