Export looped tables to Excel

Archive of Gamsworld Google Group
Post Reply
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Export looped tables to Excel

Post by Archiver »


Hi, I am looping over a parameter and want to export the marginals of an equation to excel; this is a 3-dimensional output. This is the equation that I want to export the marginals of:
QUANT(Y,K,J)..B(Y,K,J)=G=B1(K,J);

and I am looping over a parameter, scenario:
LOOP (Scenario, B1('1','CA')=xvector(scenario);

And the problem is that when I use the these commands,
execute_unload "results.gdx" scenario quant.m
execute 'gdxxrw.exe results.gdx equ=quant.m rng=NewSheet!A1'

Only the last iteration of the loop is exported. I want all the iterations! When I tried using "put" it said the dimensions were wrong, I'm guessing because I was following instructions for using "put" on a vector.

Please help!!

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

RE: Export looped tables to Excel

Post by Archiver »


You just have to define a parameter that collects IN every loop the results (



Parameter results(scenario, y,k,j,“marginal”)



Loop(scenario,

Solve mymodel

results(scenario, y,k,j,“marginal”) =quant.m(y,k,j);

);

Export results to gdx



Cheers

Renger

From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Kendra Williamson
Sent: Samstag, 6. Februar 2016 00:00
To: gamsworld
Subject: Export looped tables to Excel



Hi, I am looping over a parameter and want to export the marginals of an equation to excel; this is a 3-dimensional output. This is the equation that I want to export the marginals of:

QUANT(Y,K,J)..B(Y,K,J)=G=B1(K,J);



and I am looping over a parameter, scenario:

LOOP (Scenario, B1('1','CA')=xvector(scenario);



And the problem is that when I use the these commands,

execute_unload "results.gdx" scenario quant.m

execute 'gdxxrw.exe results.gdx equ=quant.m rng=NewSheet!A1'



Only the last iteration of the loop is exported. I want all the iterations! When I tried using "put" it said the dimensions were wrong, I'm guessing because I was following instructions for using "put" on a vector.



Please help!!



--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

Re: Export looped tables to Excel

Post by Archiver »


Success! Thank you, Renger! I wasn't sure what the "marginal" was doing, and it was creating an error, so I took it out and got the result I wanted. I would be interested to know what it might be doing though, is it supposed to label the excel column?

Frank,
I agree. This is what I used:
PARAMETER
xvector(scenario);
xvector(scenario)=ord(scenario);
PARAMETER
results(scenario,y,k,j);
......the rest of the model....
LOOP (Scenario, B1('1','CA')=xvector(scenario);
SOLVE mymodel USING NLP MAXIMIZING Z;
results(scenario,y,k,j)=quant.m(y,k,j);
execute_unload "results.gdx" scenario results
execute 'gdxxrw.exe results.gdx par=results rng=NewSheet!A1');

On Sunday, February 7, 2016 at 2:57:40 PM UTC-6, Renger van Nieuwkoop wrote:

You just have to define a parameter that collects IN every loop the results (



Parameter results(scenario, y,k,j,“marginal”)



Loop(scenario,

Solve mymodel

results(scenario, y,k,j,“marginal”) =quant.m(y,k,j);

);

Export results to gdx



Cheers

Renger

From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Kendra Williamson
Sent: Samstag, 6. Februar 2016 00:00
To: gamsworld
Subject: Export looped tables to Excel



Hi, I am looping over a parameter and want to export the marginals of an equation to excel; this is a 3-dimensional output. This is the equation that I want to export the marginals of:

QUANT(Y,K,J)..B(Y,K,J)=G=B1(K,J);



and I am looping over a parameter, scenario:

LOOP (Scenario, B1('1','CA')=xvector(scenario);



And the problem is that when I use the these commands,

execute_unload "results.gdx" scenario quant.m

execute 'gdxxrw.exe results.gdx equ=quant.m rng=NewSheet!A1'



Only the last iteration of the loop is exported. I want all the iterations! When I tried using "put" it said the dimensions were wrong, I'm guessing because I was following instructions for using "put" on a vector.



Please help!!



--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Archiver
User
User
Posts: 7876
Joined: 7 years ago

RE: Export looped tables to Excel

Post by Archiver »


.M of a variable gives you the marginal (very often zero).

Cheers

Renger



From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Kendra Williamson
Sent: Montag, 8. Februar 2016 19:24
To: gamsworld
Subject: Re: Export looped tables to Excel



Success! Thank you, Renger! I wasn't sure what the "marginal" was doing, and it was creating an error, so I took it out and got the result I wanted. I would be interested to know what it might be doing though, is it supposed to label the excel column?



Frank,

I agree. This is what I used:

PARAMETER

xvector(scenario);

xvector(scenario)=ord(scenario);

PARAMETER

results(scenario,y,k,j);

......the rest of the model....

LOOP (Scenario, B1('1','CA')=xvector(scenario);

SOLVE mymodel USING NLP MAXIMIZING Z;

results(scenario,y,k,j)=quant.m(y,k,j);

execute_unload "results.gdx" scenario results

execute 'gdxxrw.exe results.gdx par=results rng=NewSheet!A1');


On Sunday, February 7, 2016 at 2:57:40 PM UTC-6, Renger van Nieuwkoop wrote:

You just have to define a parameter that collects IN every loop the results (



Parameter results(scenario, y,k,j,“marginal”)



Loop(scenario,

Solve mymodel

results(scenario, y,k,j,“marginal”) =quant.m(y,k,j);

);

Export results to gdx



Cheers

Renger

From: gams...@googlegroups.com [mailto:gams...@googlegroups.com] On Behalf Of Kendra Williamson
Sent: Samstag, 6. Februar 2016 00:00
To: gamsworld
Subject: Export looped tables to Excel



Hi, I am looping over a parameter and want to export the marginals of an equation to excel; this is a 3-dimensional output. This is the equation that I want to export the marginals of:

QUANT(Y,K,J)..B(Y,K,J)=G=B1(K,J);



and I am looping over a parameter, scenario:

LOOP (Scenario, B1('1','CA')=xvector(scenario);



And the problem is that when I use the these commands,

execute_unload "results.gdx" scenario quant.m

execute 'gdxxrw.exe results.gdx equ=quant.m rng=NewSheet!A1'



Only the last iteration of the loop is exported. I want all the iterations! When I tried using "put" it said the dimensions were wrong, I'm guessing because I was following instructions for using "put" on a vector.



Please help!!



--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+...@googlegroups.com.
To post to this group, send email to gams...@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

--
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.
Top
Post Reply

Return to “Archive Google Group”