Vensim PLE plus and Excel 2010

[wasatchu]

I am new to Vensim PLE plus - read inept - and am trying to use the GET XLS LOOKUPS command to import some data from an Excel 2010 spreadsheet. The User Guide is less than helpful, so before I expend much effort trying to make it work, I would like assurances (and procedures, if possible) that it will work. The User Guide references 123, so I am a little concerned. The 'As Graph' function works fine, just want to expand my capabilities. Any handholding appreciated, thanks.

[Administrator]

There is an example in the help system. Can you get that to work?

http://www.vensim.com/documentation/ind ... ookups.htm

[wasatchu]

Thanks for the link. I have been using the pdf version (version 5 2007) of the User Guide. I did not know the in-program Guide existed. How exactly am I supposed to get it to work? Is there some naming convention that Vensim requires? See below.

GET XLS LOOKUPS ('ROI formulae.xlsx','project','4','F4' ) getting error message "...cannot include other expressions.

Tony: As a follow-up, I am storing both the .mls and .xlsx files in a Google Drive folder. I want to use 'named ranges', in this case "DwgRates" [E4:F41] with no headers [Month:Dwgs]. Any tips on how to construct the reference when using named ranges will be appreciated. Again, thanks.

[wasatchu]

Tony:
It seems to be working. Has to be saved as a 97-03 file. File name 8 characters, no spaces (assumed). The "Model", "Setting", "XLS Files", will let you know when it is the right format. I have not been able to get it to recognize named ranges, but 'col', 'cell' seems to work OK. My major problem was that it needed to be a "rate" not a "normal (auxiliary with lookups)" variable. Still need to get it to display correctly (I need to perfect the formula), but at least it is running.

[tomfid]

Vensim is compatible with xlsx, so you should be able to use any version of Excel (I use 2010 regularly). If not, there's some peculiarity to your file or system (multiple versions of Excel running?). There shouldn't be any file name limitations either (other than the usual system-imposed limits). There are named range examples in the help for GET XLS DATA or GET XLS CONSTANTS.

Tom

[wasatchu]

Tom;
My apologies. Vensim will recognize 2010 files with spaces, long names, and the xlsx extension. Not sure why it did not before. Still having difficulty using named ranges, but that is a trivial issue.
My current problem is that I am unable to use the graph/data developed. I get the error message "(dwg rate) is a special type and cannot be used as a variable." This is getting beyond the use of Excel 2010. Should I start a new issue/item?
Dennis

[Administrator]

Can you upload the files you are having trouble with? It's far easier to put things right if we can see what you are doing.

Tony.

[wasatchu]

Please see attached. Thanks.

[tomfid]

This is pointed at the wrong filename, but as soon as I change it to 'roi.xls' it works fine. An easy way to do that is to give it a query name flag, like '?roi', so that it'll ask you to locate the file on first use.

[wasatchu]

Either file will work, that is not the problem. The problem is being able to use the data after it is found.

[Administrator]

Try

Code: Select all

dwg rate value = DWG RATE ( Time )

[wasatchu]

Do not understand "Code Select All", nor which type to use. See attached screen shot.

[Administrator]

Create a new parameter called "dwg rate value". The equation for this should be "DWG RATE ( Time )".

[tomfid]

The user guide has a chapter on lookups: http://www.vensim.com/documentation/usr08.htm

[wasatchu]

I am just getting more confused. Reading Chapter 8 is not helping. See attached model and screenshot. Appreciate your patience, but may be better to pick this up next week.

[Administrator]

I forgot you are on PLE+.

Add time as a shadow variable on the view and draw an arrow into dwg rate value. Then it should work ok.

[wasatchu]

Thanks, that did it, though I cannot say I understand the syntax. I spoke too soon. The problem still remains that it does not seem possible to use the lookup data to drive an equation. I still get that same error message that "dwg rate" is a special type and cannot be used as a normal variable when I try to use the data. I need the "dwg rate" to drive the equation "dwg rate*time=dwgs produced", from which I can then subtract "re-design" (see vensim004.jpg). I should be leveling off at 100 dwgs not 38, which is my current time span (38 months). I will probably have to use the GET XLS LOOKUPS function several more times as I increase the complexity of this model. It should be more straightforward than this. I should be able to populate the Auxiliary with lookups graph directly from the spreadsheet using the GET XLS LOOKUPS. I am sure there is a simple solution to this, I just cannot find it. Again, your patience is appreciated. I am afraid my frustration is showing.

[tomfid]

The syntax of a lookup is just like a function call, e.g. y = f(x), where f() is the lookup. A quick trip through the User Guide chapter linked above may help with this.

In your case, you need a Lookup:

dwg rate lookup = GET XLS LOOKUPS('ROI.xls','project' ,'E','F4' )

and an Auxiliary that uses the lookup:

dwg rate = dwg rate lookup( Time )

to suppress units errors, you can use (Time/One Month) to normalize the input to a dimensionless value.

The Auxiliary + With Lookup syntax combines the lookup table and the lookup function call into a single variable, for brevity.

Also, you probably will have better luck if you use regular rates, rather than putting IF THEN ELSE and other expressions in your levels (INTEGs).

[wasatchu]

Thanks, I think I am beginning to understand. The 'lookup' functions much like a shadow variable. If this is the case, why not just say so, instead of a "special type" without any definition that I can find. Once you understand that it only needs to be defined and then it can be used in an equation like a shadow variable [the linkage seems to be automatic], then the problem goes away. Still need my equations to reflect 'reality', but that is something that I should be able to sort out [hope]. Again, thanks to all for your help. You are making this learning process a lot easier. I would never have been able to figure this out on my own.

[tomfid]

Actually they don't need to be shadow variables (unless appearing more than once on a diagram), but often they are used that way. Sounds like a good topic for a quick video clip ...