Importing/ Exporting data to/ from GAMS

Archiver · February 21, 2014, 5:53am

Dear Friends,

I want to run my model for 50 sets of parameters (I mean I have 50 numerical examples). I want to know whether GAMS can read the values of r1 to e from an EXCEL file (from A1 to Q30 in Sheet one) and then import the results (T1, T2, v, g, and z1) in Sheet two.

Moreover, I want to know whether GAMS can import the total time spent for solving the model, in Sheet three. The model in shown in orange color.

I would highly appreciate it somebody can help me.

Best regards.

SCALARS r1 the manufacturer capacity utilization /0.9/
r2 the supplier capacity utilization /0.8/
q deteriorating rate which is a fraction of the on-hand inventory /0.1/
F supplier setup cost per setup /400 /
A manufacturer ordering setup cost per cycle /50/
c supplier production cost per unit /5/
s the unit price charged by the supplier to the manufacturer /10/
b backorder cost /2/
h manufacturer inventory holding cost rate excluding interest charges /0.1/
j supplier inventory holding cost rate excluding interest charges /0.1/
Is supplier opportunity cost per dollar per unit time /0.02/
Ip manufacturer opportunity cost per dollar per unit time /0.06/
Ie manufacturer interest earned per dollar per unit time /0.04/
M the manufacturer trade credit period offered by the supplier / 0.0821917808219178/
N the customer trade credit period offered by the manufacturer / 0.0273972602739726/
k constant in the manufacturer demand function representing his market scale /5000000/
e price elasticity of manufacturer demand rate which is alpha in the thesis /1.5/ ;

VARIABLES
z1 total net profit ;
POSITIVE VARIABLES
T1 production time with backorder at manufacturer’s side
T2 production time when positive stock builds up and the stock depletes due to the demand and deterioration at manufacturers side
v the unit price charged by the manufacturer to the customers ;
T1.lo = 0.01 ;
T2.lo = 0.01 ;
v.lo = 1 ;
v.up = 30 ;

INTEGER VARIABLES
g number of shipments from the supplier to the manufacturer per production run;
g.lo = 1;
g.up = 30;

Equations
Profit define objective function
Constraint condition for NP1
PriceConstraint constraint for price ;

Profit … z1 =e= (1/((1/r1)T1+T2+(1/q)(log((1-(1/r1))exp(-qT2)+(1/r1)))))((v-s)(k*(v**(-e)))((1/r1)T1+(T2+((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))))-A-(sh/q)(k(v**(-e)))(((1/r1)-1)T2-((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1)))))-(b)((1/2)(T1**2)(k*(v**(-e)))((1/r1)-1)(1/r1))-s*(k*(v**(-e)))((1/r1)T2-(T2+((1/q)(log((1-(1/r1))exp(-qT2)+ 1/r1)))))+(vIe)((k(v**(-e))/(2r1))((M-N)2))-(sIp(k(v*(-e)))/q)(((1/r1)-1)T2-((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))) -vIp*(1/2)(k(v**(-e)))((N(2T2+2((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))-N))+N(T1+N-M)(1/r1))+(s-c)((k*(v**(-e))/r1)(T1+T2))-F/(g)- c(j+Is)(((k(v**(-e)))/r1)((1/r1)T1+T2+(1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))(T1+T2)(r2(1-((g)/2))+(((g)-1)/2)))-sIs((k*(v**(-e))/r1)*(T1+T2)*M)) ;
Constraint … M =l= T1 ;
PriceConstraint … v =g= s ;

MODEL NetProfit /ALL/ ;
option optcr=0, decimals=8;
SOLVE NetProfit USING MINLP MAXIMIZING z1 ;
option decimals=8;
Display z1.l;
Display T1.l, T2.l, v.l, g.l;

execute_unload
parameter ms, ss;

This would provide you with a number of your model
(you can see here all the messages http://www.gams.com/mccarl/mccarlhtml/modelstat_tmodstat.htm)
The information of this is precious as you can see if your solution is optimal, locally optimal, ifeasible etc.
The second parameter provides you with a status regarding your solver, and how to fix some things
(http://www.gams.com/mccarl/mccarlhtml/solvestat_tsolstat.htm)

ms=NetProfit.modelstat;
ss=NetProfit.solvestat;
option optcr=0;

\

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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.

Archiver · February 21, 2014, 7:08pm

Hi Zohreh

The easiest way is to define a set of scenarios:

Scen /scen1scen50/;

Then use an excel sheet with all the parameter inputs: in the rows the names of the parameters and in the columns the names of the scenarios. Import this table.

Then loop over the scenarios and save, within the loop after the solve statement, all the results in a parameter defined over the scenarios ((e.g. results(,scen)).

Results(â€œT1â€,scen) = t1.l;

Etc.

Send this to an excel sheet and generate a pivot table from it.

Cheers

Renger

From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Zohreh
Sent: Friday, February 21, 2014 5:53 AM
To: gamsworld@googlegroups.com
Subject: Importing/ Exporting data to/ from GAMS

Dear Friends,

I want to run my model for 50 sets of parameters (I mean I have 50 numerical examples). I want to know whether GAMS can read the values of r1 to e from an EXCEL file (from A1 to Q30 in Sheet one) and then import the results (T1, T2, v, g, and z1) in Sheet two.

Moreover, I want to know whether GAMS can import the total time spent for solving the model, in Sheet three. The model in shown in orange color.

I would highly appreciate it somebody can help me.

Best regards.

SCALARS r1 the manufacturer capacity utilization /0.9/

r2 the supplier capacity utilization /0.8/

q deteriorating rate which is a fraction of the on-hand inventory /0.1/

F supplier setup cost per setup /400 /

A manufacturer ordering setup cost per cycle /50/

c supplier production cost per unit /5/

s the unit price charged by the supplier to the manufacturer /10/

b backorder cost /2/

h manufacturer inventory holding cost rate excluding interest charges /0.1/

j supplier inventory holding cost rate excluding interest charges /0.1/

Is supplier opportunity cost per dollar per unit time /0.02/

Ip manufacturer opportunity cost per dollar per unit time /0.06/

Ie manufacturer interest earned per dollar per unit time /0.04/

M the manufacturer trade credit period offered by the supplier / 0.0821917808219178/

N the customer trade credit period offered by the manufacturer / 0.0273972602739726/

k constant in the manufacturer demand function representing his market scale /5000000/

e price elasticity of manufacturer demand rate which is alpha in the thesis /1.5/ ;

VARIABLES

z1 total net profit ;

POSITIVE VARIABLES

T1 production time with backorder at manufacturer’s side

T2 production time when positive stock builds up and the stock depletes due to the demand and deterioration at manufacturers side

v the unit price charged by the manufacturer to the customers ;

T1.lo = 0.01 ;

T2.lo = 0.01 ;

v.lo = 1 ;

v.up = 30 ;

INTEGER VARIABLES

g number of shipments from the supplier to the manufacturer per production run;

g.lo = 1;

g.up = 30;

Equations

Profit define objective function

Constraint condition for NP1

PriceConstraint constraint for price ;

Profit … z1 =e= (1/((1/r1)T1+T2+(1/q)(log((1-(1/r1))exp(-qT2)+(1/r1)))))((v-s)(k*(v**(-e)))((1/r1)T1+(T2+((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))))-A-(sh/q)(k(v**(-e)))(((1/r1)-1)T2-((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1)))))-(b)((1/2)(T1**2)(k*(v**(-e)))((1/r1)-1)(1/r1))-s*(k*(v**(-e)))((1/r1)T2-(T2+((1/q)(log((1-(1/r1))exp(-qT2)+ 1/r1)))))+(vIe)((k(v**(-e))/(2r1))((M-N)2))-(sIp(k(v*(-e)))/q)(((1/r1)-1)T2-((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))) -vIp*(1/2)(k(v**(-e)))((N(2T2+2((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))-N))+N(T1+N-M)(1/r1))+(s-c)((k*(v**(-e))/r1)(T1+T2))-F/(g)- c(j+Is)(((k(v**(-e)))/r1)((1/r1)T1+T2+(1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))(T1+T2)(r2(1-((g)/2))+(((g)-1)/2)))-sIs((k*(v**(-e))/r1)*(T1+T2)*M)) ;

Constraint … M =l= T1 ;

PriceConstraint … v =g= s ;

MODEL NetProfit /ALL/ ;

option optcr=0, decimals=8;

SOLVE NetProfit USING MINLP MAXIMIZING z1 ;

option decimals=8;

Display z1.l;

Display T1.l, T2.l, v.l, g.l;

execute_unload

parameter ms, ss;

This would provide you with a number of your model
(you can see here all the messages http://www.gams.com/mccarl/mccarlhtml/modelstat_tmodstat.htm)
The information of this is precious as you can see if your solution is optimal, locally optimal, ifeasible etc.
The second parameter provides you with a status regarding your solver, and how to fix some things
(http://www.gams.com/mccarl/mccarlhtml/solvestat_tsolstat.htm)

ms=NetProfit.modelstat;

ss=NetProfit.solvestat;

option optcr=0;

\

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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.

–
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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.

Archiver · February 23, 2014, 4:35pm

Dear Renger,

Thank you for your response. But, unfortunately, I did not understand exactly how I should do this. Could you please introduce a reference?

Best regards.

On Friday, February 21, 2014 12:53:22 PM UTC+8, Zohreh wrote:

Dear Friends,

I want to run my model for 50 sets of parameters (I mean I have 50 numerical examples). I want to know whether GAMS can read the values of r1 to e from an EXCEL file (from A1 to Q30 in Sheet one) and then import the results (T1, T2, v, g, and z1) in Sheet two.

Moreover, I want to know whether GAMS can import the total time spent for solving the model, in Sheet three. The model in shown in orange color.

I would highly appreciate it somebody can help me.

Best regards.

SCALARS r1 the manufacturer capacity utilization /0.9/
r2 the supplier capacity utilization /0.8/
q deteriorating rate which is a fraction of the on-hand inventory /0.1/
F supplier setup cost per setup /400 /
A manufacturer ordering setup cost per cycle /50/
c supplier production cost per unit /5/
s the unit price charged by the supplier to the manufacturer /10/
b backorder cost /2/
h manufacturer inventory holding cost rate excluding interest charges /0.1/
j supplier inventory holding cost rate excluding interest charges /0.1/
Is supplier opportunity cost per dollar per unit time /0.02/
Ip manufacturer opportunity cost per dollar per unit time /0.06/
Ie manufacturer interest earned per dollar per unit time /0.04/
M the manufacturer trade credit period offered by the supplier / 0.0821917808219178/
N the customer trade credit period offered by the manufacturer / 0.0273972602739726/
k constant in the manufacturer demand function representing his market scale /5000000/
e price elasticity of manufacturer demand rate which is alpha in the thesis /1.5/ ;

VARIABLES
z1 total net profit ;
POSITIVE VARIABLES
T1 production time with backorder at manufacturer’s side
T2 production time when positive stock builds up and the stock depletes due to the demand and deterioration at manufacturers side
v the unit price charged by the manufacturer to the customers ;
T1.lo = 0.01 ;
T2.lo = 0.01 ;
v.lo = 1 ;
v.up = 30 ;

INTEGER VARIABLES
g number of shipments from the supplier to the manufacturer per production run;
g.lo = 1;
g.up = 30;

Equations
Profit define objective function
Constraint condition for NP1
PriceConstraint constraint for price ;

Profit … z1 =e= (1/((1/r1)T1+T2+(1/q)(log((1-(1/r1))exp(-qT2)+(1/r1)))))((v-s)(k*(v**(-e)))((1/r1)T1+(T2+((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))))-A-(sh/q)(k(v**(-e)))(((1/r1)-1)T2-((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1)))))-(b)((1/2)(T1**2)(k*(v**(-e)))((1/r1)-1)(1/r1))-s*(k*(v**(-e)))((1/r1)T2-(T2+((1/q)(log((1-(1/r1))exp(-qT2)+ 1/r1)))))+(vIe)((k(v**(-e))/(2r1))((M-N)2))-(sIp(k(v*(-e)))/q)(((1/r1)-1)T2-((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))) -vIp*(1/2)(k(v**(-e)))((N(2T2+2((1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))-N))+N(T1+N-M)(1/r1))+(s-c)((k*(v**(-e))/r1)(T1+T2))-F/(g)- c(j+Is)(((k(v**(-e)))/r1)((1/r1)T1+T2+(1/q)(log((1-(1/r1))exp(-qT2)+(1/r1))))(T1+T2)(r2(1-((g)/2))+(((g)-1)/2)))-sIs((k*(v**(-e))/r1)*(T1+T2)*M)) ;
Constraint … M =l= T1 ;
PriceConstraint … v =g= s ;

MODEL NetProfit /ALL/ ;
option optcr=0, decimals=8;
SOLVE NetProfit USING MINLP MAXIMIZING z1 ;
option decimals=8;
Display z1.l;
Display T1.l, T2.l, v.l, g.l;

execute_unload
parameter ms, ss;

This would provide you with a number of your model
(you can see here all the messages http://www.gams.com/mccarl/mccarlhtml/modelstat_tmodstat.htm)
The information of this is precious as you can see if your solution is optimal, locally optimal, ifeasible etc.
The second parameter provides you with a status regarding your solver, and how to fix some things
(http://www.gams.com/mccarl/mccarlhtml/solvestat_tsolstat.htm)

ms=NetProfit.modelstat;
ss=NetProfit.solvestat;
option optcr=0;

\

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 http://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/groups/opt_out.

Archiver · February 24, 2014, 11:08am

Hi

See the gdxutils for the excel stuff.

Cheers

Renger

From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of Zohreh
Sent: Sonntag, 23. Februar 2014 16:36
To: gamsworld@googlegroups.com
Subject: Re: Importing/ Exporting data to/ from GAMS

Dear Renger,

Thank you for your response. But, unfortunately, I did not understand exactly how I should do this. Could you please introduce a reference?

Best regards.

On Friday, February 21, 2014 12:53:22 PM UTC+8, Zohreh wrote:

Dear Friends,

I want to run my model for 50 sets of parameters (I mean I have 50 numerical examples). I want to know whether GAMS can read the values of r1 to e from an EXCEL file (from A1 to Q30 in Sheet one) and then import the results (T1, T2, v, g, and z1) in Sheet two.

Moreover, I want to know whether GAMS can import the total time spent for solving the model, in Sheet three. The model in shown in orange color.

I would highly appreciate it somebody can help me.

Best regards.

SCALARS r1 the manufacturer capacity utilization /0.9/