0
$\begingroup$

I am looking for a way to write data from several iterations during a loop process into my excel. How can i change the location it is written to during every loop iteration?

those 2 lines would keep writing everything in the same place if i start repeating the command. Can i somehow make it change the sheet every iteration?

´´´

loop(t,
    execute_unload "Results_Country1.gdx" y.l
    execute 'gdxxrw.exe Results_Country1.gdx var=y.L'
);

´´´´

$\endgroup$
4
$\begingroup$

It is much better to collect all y.l(i) in a parameter with an extra index t, and export that in one swoop. I.e.

parameter results(t,i); 
loop(t,
*  calculate y.l(i)
    solve ...;
    results(t,i) = y.l(i);  
); 
execute_unload "AllResults.gdx", results;
execute 'gdxxrw.exe AllResults.gdx par=results';

Calls to Excel are expensive so they should preferably not be done inside a loop. It also helps structuring the model a bit better: separate model logic from reporting. Mixing them often leads to messy models. In addition, combining things in a larger multidimensional "cube" has some advantages for Excel: we can do filtering, pivot tables, pivot charts, store in data model etc.

$\endgroup$
2
  • $\begingroup$ thank you again. I saw you have written some of the GAMS documentation :). Do you more coding examples (other than on your blog?). $\endgroup$ – DerEddie Nov 12 '20 at 15:57
  • 2
    $\begingroup$ The most interesting stuff I can share is documented on my blog. Typically the models are small and demonstrate just one or two concepts, so they are easier to understand than real models which tend to be more complex and messier. Most concepts are independent of GAMS and can be applied to any modeling tool (in many posts I don't even mention GAMS). $\endgroup$ – Erwin Kalvelagen Nov 12 '20 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.