Getting Data from Excel

[impactdynamics]

Challenge:

I am building a dynamics Balanced Scorecard for the telecoms sector of a global IT company. The model will be fed by over 200 variables each month via an Excel spreadsheet captured from a BI solution. Some time sreries data, most constants.

The model also has about 50 lookups which will also be fed from a spreadsheet but not via the BI system.

Later, I will need to duplicate views (to enable explicit relationships between products and customers , eg cross/up-selling) and Subsets (to denote geographic regions)

Questions:

What is the best mechanism for structuring and inputting data from the spreadsheet into the model, ensuring that the links are dynamic, ie update automatically?

Is is possible to capture the data by variable name rather than row and col references, which makes a much more elegant and practical solution? (Thanks to your great help with macros, this data is already structured by view and variable name and trial values are being compiled now.


Specific Problem:

I experimented with GET XLS DATA and GET XLS CONSTANTS with a very simple model and spreasheet in the same folder. Followed manual to the letter, included single quotes. Failed to run - dialog said spreadsheet does not exist - help!

[Administrator]

Can you post the simple model and spreadsheet so we can take a look?

Tony.

[impactdynamics]

Thanks Tony

Here are model and spreasheet

[bob@vensim.com]

Will reply in the Vensim forum.

[bob@vensim.com]

Reply to thread started in the System Dynamics Forum

Chapter 16 of the User's guide shows an example of this. If you are having trouble with file names best guess is that you have left off .xls in the filename. eg 'MySpreadsheet' won't work you need to have 'MySpreadsheet.xls'

As far as using variable names in the spreadsheet. If you want to do this you would need to either explicitly import that .xls file which parses for variable names (again see Chapter 16) or create in excel a sheet that parses variable names and puts results in a fixed location for the GET XLS functions to use.

[impactdynamics]

Bob - this is great - you were right of course

Many thanks for such swift reply.

I like the sound ot this parsing idea - do you have a simple example?

Regards

Roger

[bob@vensim.com]

Sorry no examples on that - it is Excel macro editing so there may be useful stuff on the web someplace.

[LAUJJL]

Hi

About your first question, duplicating views to represent relationship between products and customers and geographical sectors. Normally if you have to duplicate views there is a risk that this duplication could be avoided by careful use of subscripts. Of course not knowing your application I cannot be sure of it.
I have been some times tempted to duplicate views instead of building subscripts: if is easier to do. One has to choose between complexity of the sketch and complexity of the equations. In the long run, I found always preferable to prefer the simplification of the sketch with more complex equations, because mainly your can easily add more segments in your products or customers or geographical sectors. The overall model is more simple to understand too and it is too much easier to aggregate partial results and analyse them.
Regards.
JJ

[impactdynamics]

Thanks so much for this JJ - I agree, subscripts offer a much more elegant and simpler solution. Can I try a scenario on you?

A key margin dynamic in the mobile telecoms market is switching customers from commodidy voice and text services to high value data services.

Take any customer segment and suppose we use subscripts to denote cust revenues by voice and data respectively (ie same customer segment, subdivided by service type)

As the level of voice revenue increases (say by aggressive recruiting of new customers) the propensity to use and/or switch to data services increases in some non-linear relationship, depending on the segment.

So we need to model an explicit relationship between the increase in data as a factor of voice (among other things).

Question:

Can explicit, visible relationships exist across subscripted variables in Vensim - in this case it would be a lookup?

Regards

Roger

[LAUJJL]

Hi

I have not presently the time to answer your question, but I will do it as soon as possible, evnetually this week end.
Regards.
JJ

[LAUJJL]

Hi

Attached a simplistic model with cross influence between customers and type of communication.
There are three warnings for the unit control because the lookups are not normalized. It should be better to do it by using standard influence with standard revenue.
Regards.
JJ

[impactdynamics]

hi JJ

this really is much appreciated

Roger