getting data from excel

[LAUJJL]

Hi

If you can load correctly the data from excell with the get xls lookup for one year, there is no reason why you could not load it with nested if then else with many years.
So if it does not work, your if then else's are not correct.
Better to join a simple model and a simple Excel sheet to
expose your case.
Of course you cannot make the parameters inside the get xls
lookup equal to an auxiliary depending on the year.
You have to explicitly write down each get xls lookup coresponding to each year.
If you have many years you may need many nested if then else.
There is perhaps a solution to that problem using Vensim Dss.
Regards.
JJ

[bob@vensim.com]

It seems like it would be easier just to do a data stream for holidays. I am presuming you are running a difference equation model with TIME STEP 1 and Time Units Day. Then just have

is holiday marker :RAW: := GET XLS DATA(...
is holiday = IF THEN ELSE(is holiday marker = :NA:,0,1)

and in Excel have

Day 20 83 121
Hm 1 1 1

the rest blank.

If you do want to use multiple lookups each needs its own GET XLS LOOKUPS definition. then you use

actual value = IF THEN ELSE(Time <365,first year lookup(Time/TIME STEP),
second year lookup((Time - 365)/TIME STEP))

and so on.
Is Holiday

[LAUJJL]

Hi

One eventual possibility is to load all the hollydays in an array, where for instance one subscript would be the year and the other the day of the year, with two arrays one for the leap year and another for a not leap year.
I would then be easier to fetch the information you need based on the year.
One other remark: what are you trying to do?
Your model seems to include a level of details rather surprising. Is it really necessary to get to this level?
There is a high risk to loose control of your model by making it to complicate.
If you are a student and want to proove that you can make complicate models, that is OK, but otherwise it is better to remember that a model is built to be useful to something.
Is it not possible to build first an exploratory model much simpler that permits to have a first idea and then test the utility to add more details later on?
About the utility to add details I try to settle it in advance, before starting the model,
eventually using small exploratory models or any other possibility using SD or not.
Regards.
JJ

[bob@vensim.com]

You won't be able to get this to work with Lookups - between points Lookups interpolate and since all the points are 1 they will always return 1.

You could do it with data variables and then use the GET DATA AT TIME function to get values with :NA: being used to represent 0.

Preprocessing the data in excel and just creating a huge hourly time series may actually be the most effective way to do this. You can have the data subscripted by terrritory and run for the full possible set of hours.

[bob@vensim.com]

Actually one other approach is more like that suggested by JJ. Create a variable like

Holiday day in year[Territory, SYear,SDay]

Then use something like (you will need to experiment to get this to work right)

is holiday[territory] = IF THEN ELSE(next holiday day[territory] = current day in year,1,0)

next holiday day[Territory] = VECTOR ELM MAP(holiday day in year[Territory,Sy0,Sd0]+(current year - initial year)*ELMCOUNT(sday)+next holiday day offset)

next holiday day offset = SAMPLE IF TRUE(year start hour :OR: holiday finish hour,IF THEN ELSE(year start hour,0,next_holiday day offset+1))

Those are not quite right but hopefully the give the idea - messy indeed.

[bob@vensim.com]

Yet another afterhtought - and this one is not so messy

In Excel

day 1 2 3 4
2005 1 0 0 0
2006 1 0 0 0

where 1 means holiday and 0 not. Have this for all 366 potential days and read it in as a matrix with GET XLS CONSTANT. Then in the model just use

is a holiday[teritory] = VECTOR ELM MAP(big array[territroy,y0,d0],(this year-year0)*ELMCOUNT(days)+(thisday - 1))

This is much less messy than my last suggestion.

[LAUJJL]

Hi

When speaking of details, I was just warning you about the fact that I have generally noticed that the utility is not necessarily growing with model complexity and if so much lower than linearly, and the work to do it is growing exponentially. If one faces this fact, is is often better to stay in a conventional environment, with a procedural language with data close at hand where one does the detail calculation and to keep the dynamic complexity in a separate model, as small as possible.
About the public holydays another way to do it is, instead of
having a 1 or 0 depending on the state of the day of the year, to have the first and the last day of the period and to
settle the maximum of periods possilbe in a year.
One can even have only one number to represent it.
For instance: if the second period is the 45th day of the year
and the last day the 55th, one multiplies 45 by one thousand and add 55 or 10 for the duration of the holiday.
the number would be 45055. To transform it back, one
uses the formula integer (45055 / 1000) for the first day
and modulo(45055,1000) for the last day.
If the number of holiday periods is 5 and 6 the maximum possible in the year one would have in a spreadsheet
1 2 3 4 5 6
20025 45055 180250 290310 350365 0

1 2 3 4 5 representing the different period of holidays and the corresponding number the first and last day of each period and the six with 0 in a case where there were 6 periods, meaning that there is only 5 periods in this case.
this reduces considerably the size of the arrays needed to
keep the necessary values.
Handling the results is easy.
I have not seen anywhere a limitation of the number of variables in Vensim?
Anyhow the limitation is much more in the handing of complexity than in the software to my opinion.
Regards.
JJ