Please enable JavaScript to view this site.

Vensim Help

Navigation: Reference Guide > Simulating Models > Changing Constant and Lookup Values

Getting Constant Changes from Spreadsheets

Scroll Prev Top Next More

Vensim Provides an automated link to spreadsheets with GET XLS CONSTANTS and related functions. They will automatically load in the current values from the named spreadsheet before any change files are read or interactive changes are made.  

If you have a large number of changes to make, or if you want to change Lookups, you might want to set up changes in a spreadsheet and then save them to a tab or comma delimited file.  It is somewhat faster to process these than to connect to Excel or 123 to get the constants.

Suppose you have the model equations:

sex : female,male ~~|

county : madison,morane,melrose ~~|

initial population[county,sex]=0 ~Person~|

saturation lookup(0,0,0,0) ~Dmnl~|

and you have a Microsoft Excel spreadsheet containing:

The Constants are entered in a tabular format and the Lookup is entered with its X-axis on one row and its Y-axis on another row.  Note that the initial and closing parens ( ) are necessary to mark this as a Lookup for Vensim.  Saving this spreadsheet as a Tab Delimited file would give:

       Population stats compiled by RQW,
"initial population[madison,sex]"22000                27000
"initial population[morane,sex]"        14000                9000
"initial population[melrose,sex]"35000                36000
saturation lookup(0        1        2        
               1        1        0        )

You can then specify this file to be used as a changes file during simulation.  Any row with an empty first column is ignored.  Thus the first two rows are simply comments are far a Vensim is concerned.

NOTE It is very important to make sure that the order of subscripts along columns is the same in the model and the spreadsheet.

Saving a spreadsheet file as a .csv (comma delimited) file will also work.

NOTEIf you want to use output from spreadsheets in this manner, the file must have extension .txt, .tab or .csv.  This is a signal not to expect the strict .cin format that Vensim normally requires.