Problem with Command Script using GET XLS CONSTANTS

Use this forum to post Vensim related questions.
Post Reply
Tomi
Junior Member
Posts: 6
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Problem with Command Script using GET XLS CONSTANTS

Post by Tomi »

Hello,

I'm trying to run simulations using command scripts and getting the constants from excel. I found this old post while seeking solutions to my problem. I'm having similar problems, so I thought I'll continue from here.

Here is my command script:

Code: Select all

SIMULATE>RUNNAME|S01.vdf
SIMULATE>SETVAL|ExcelSheet :is: 'Sheet1'
MENU>RUN

SIMULATE>RUNNAME|S02.vdf
SIMULATE>SETVAL|ExcelSheet :is: 'Sheet2'
MENU>RUN
Here is the model:
model.JPG
model.JPG (10.31 KiB) Viewed 1284 times
constant.JPG
constant.JPG (104.86 KiB) Viewed 1284 times
excelsheet.JPG
excelsheet.JPG (99.66 KiB) Viewed 1284 times
The excel file "Test.xls" has 2 sheets, "Sheet1" and "Sheet2". They both have values only on cell A1. I'm getting the correct values from excel if I change the string 'Sheet1' to 'Sheet2' manually in the model.

The command script runs change the ExcelSheet variable to 'Sheet1' and 'Sheet2' as it should. But the variable constant stays the same, that is the value from the excel Sheet1. So it seems that even though the ExcelSheet is changing, the GET XLS CONSTANTS function does not notice this change. Is there any solution to this? This way would be very convenient for me to handle simulation parameters as I could handle the parameters from excel and run the simulations using the cmd file.

And I'm using at the moment Vensim DSS 6.2.

Appreciate all the help,
Tomi

Administrator
Super Administrator
Posts: 3624
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator »

Can you upload anything so we can test?
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559

Tomi
Junior Member
Posts: 6
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi »

Yes, all the files are as attachements. (Commant script in a zip-file, because could not upload it otherwise)
Attachments
CMDxlsTest2.zip
(274 Bytes) Downloaded 48 times
Test.xls
(25.5 KiB) Downloaded 44 times
TestXlsConstants.mdl
(1.3 KiB) Downloaded 41 times

Administrator
Super Administrator
Posts: 3624
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator »

Thanks. I'm seeing the same issue.

I do recall that the Excel values are imported first (so possibly before any values are set such as the sheet name). I'll look into this and see if I can fix it.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559

tomfid
Administrator
Posts: 3049
Joined: Wed May 24, 2006 4:54 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by tomfid »

Note the provision in the docs for SETVAL:
If you are using the SETVAL command to set a string variable do not include the surrounding single quotes. For example:

SIMULATE>SETVAL|productname:IS:Metronone
However, that alone is not sufficient to resolve the problem - it still seems to fail silently.

tomfid
Administrator
Posts: 3049
Joined: Wed May 24, 2006 4:54 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by tomfid »

The .cin file trick also fails.

Tomi
Junior Member
Posts: 6
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi »

Thanks for testing the model. So, it seems like there is no easy fix to that problem.

Administrator
Super Administrator
Posts: 3624
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator »

There is no easy fix I'm sorry to say.

The external data/constants (any GET_XLS.. functions) are executed first, so any changes from CIN files/SETVAL do not get applied to the GET... functions. I'm working on it though.
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559

Tomi
Junior Member
Posts: 6
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi »

Alright, then I have to find another solution for controlling my simulations. Thank you for your help!

And if you get it fixed somehow let me know.

tomfid
Administrator
Posts: 3049
Joined: Wed May 24, 2006 4:54 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by tomfid »

How many datasets do you have? You could create a data variable for each, then switch the data used numerically, rather than switching the source name. It's a little inefficient, but it won't matter unless the dataset is large.

Tomi
Junior Member
Posts: 6
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi »

Back to this one again.

So, there is roughly 30 parameters and 10 different scenarios that I would like to control from the excel sheet.

I hoped that I could define all the parameters and scenarios in the excel and then just control from the command script the scenario runs. This would have been a very convenient way to control the simulations and a way to handle all the important parameters in one excel sheet (in this way it is easy to play with different scenarios). Also, this would have made it easy to define different sensitivity runs for different scenarios using the command script.

Is there any other way I could handle the simulations from the excel using command scripts? Or some other similar way? And I would rather not use visual basic. I'm not sure what you mean by doing this with data variables.

See the excel file as an attancement.
Attachments
Test02.xls
(45 KiB) Downloaded 31 times

Administrator
Super Administrator
Posts: 3624
Joined: Wed Mar 05, 2003 3:10 am

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Administrator »

Here is something that works, but it's a little "clunky".

You read in all the values from the "All" sheet, and then use another constant to switch between them.

Units will be a problem using this method as all constants read in at the same time can only be given the same units.
Attachments
2019-12-02 Thread 7333.zip
(11.22 KiB) Downloaded 27 times
Advice to posters seeking help (it really helps us to help you)
http://www.ventanasystems.co.uk/forum/v ... f=2&t=4391

Units are important!
http://www.bbc.co.uk/news/magazine-27509559

Tomi
Junior Member
Posts: 6
Joined: Wed Oct 02, 2019 7:20 am
Vensim version: DSS

Re: Problem with Command Script using GET XLS CONSTANTS

Post by Tomi »

Thanks a lot, I can work with this!

I don't mind about the units. For me the drawback of this solution is that I can't use the synthesim mode with the parameters that are defined in excel.

Btw, is there some kind of best practice way for managing multiple simulation runs?

Post Reply