The ability to use Gdxxrw to interact with a spreadsheet at execution time means that it is possible to have the spreadsheet do some things with data generated by GAMS and pass back the results interactively during a model run. In addition, there are certain processes that can be used in the spreadsheet that GAMS does not now contain. One such process is regression function estimation. Suppose a modeler wished estimate response functions across a group of scenarios run in a GAMS model. This can be done by interactively passing information to the spreadsheet, which in turn does the regression then collecting back the regression results. This can be done using a set of Gdxxrw, Execute_Unload and Execute_Load commands.
Example:
Suppose I have set up a portfolio selection problem and solved it under alternative risk aversion parameters. Now I wish to estimate response functions for the individual stocks and the mean income as a function of the risk aversion parameter. This is done in the following GAMS code (spreadinteract.gms)
LOOP (RAPS,RAP=RISKAVER(RAPS);
SOLVE EVPORTFOL USING NLP MAXIMIZING OBJ ;
VAR = SUM(STOCK, SUM(STOCKS,
INVEST.L(STOCK)*COVAR(STOCK,STOCKS)*INVEST.L(STOCKS))) ;
OUTPUT("RAP",RAPS)=RAP;
OUTPUT(STOCKS,RAPS)=INVEST.L(STOCKS);
OUTPUT("OBJ",RAPS)=OBJ.L;
OUTPUT("MEAN",RAPS)=SUM(STOCKS, MEAN(STOCKS) * INVEST.L(STOCKS));
OUTPUT("VAR",RAPS) = VAR;
OUTPUT("STD",RAPS)=SQRT(VAR);
OUTPUT("SHADPRICE",RAPS)=INVESTAV.M;
OUTPUT("IDLE",RAPS)=FUNDS-INVESTAV.L
);
DISPLAY OUTPUT;
set funstoestimate(*);
funstoestimate("mean")=yes;
funstoestimate(stock)=yes;
set regpar /intercept,rap,rapsquare,rapcube,rapfour,rsquare/;
set regres /coef,stderr/
set rsqp /r2/
parameter rsq(rsqp);
PARAMETER estimatedata(RAPS,*) data to estimate regression over
PARAMETER regestimate(Regres,regpar) RESULTS FROM MODEL RUNS WITH VARYING RAP
PARAMETER regestimates(*,Regres,regpar) RESULTS FROM MODEL RUNS WITH VARYING RAP
loop(funstoestimate,
estimatedata(raps,funstoestimate)=output(funstoestimate,raps);
estimatedata(raps,'Intercept')=1;
estimatedata(raps,'rap')=output('rap',raps);
estimatedata(raps,'rapsquare')=output('rap',raps)**2;
estimatedata(raps,'rapcube')=output('rap',raps)**3;
estimatedata(raps,'rapfour')=output('rap',raps)**4;
execute_unload 'regdata.gdx',estimatedata;
execute 'gdxxrw regdata.gdx o=gdxxrwss.xls par=estimatedata Rng=regdata!a1';
execute 'gdxxrw gdxxrwss.xls o=regdata.gdx par=regestimate Rng=regress!a1:f3
par=rsq Rng=regress!a4:b4 rdim=1';
execute_load 'regdata.gdx',regestimate,rsq;
regestimates(funstoestimate,Regres,regpar)=regestimate(Regres,regpar);
regestimates(funstoestimate,'coef','rsquare')=rsq('r2');
estimatedata(raps,funstoestimate)=0;
);
option regestimates:4:2:1;display regestimates;
where the portion in
| • | Red is running the GAMS model repeatedly for different risk aversion parameters. |
| • | Blue is setting up the functions to estimate and the sets of information to pass back and forth and setting up the data to estimate a fourth order polynomial for the particular function to be estimated (as controlled by the loop on funstoestimate). |
| • | Orange is unloading that data first to the regdata.gdx file. |
| • | Purple places it into the spreadsheet gdxxrwss.xls on the regdata sheet that will automatically compute the regression results after the data are entered. |
| • | Brown is loading the regression results from the spreadsheet into regress.gdx |
| • | Pink takes the data from regdata.gdx file loading it into the GAMS program. |
The spreadsheet gdxxrwss.xls sheets are
| • | regdata where the dependent variable goes into column b and the independent variables including an intercept goes into columns C-G as generated by the Gdxxrw command |
execute 'gdxxrw regdata.gdx o=gdxxrwss.xls par=estimatedata Rng=regdata!a1';
| • | the regress one where the data are copied into cells L3:Q28 from the regdata sheet and the Excel function |
=+LINEST(L3:L28,M3:Q28,FALSE,TRUE)
is used to perform the regression and place the results in the range b2:h6 with the labeling manually entered in row 1 and column A so it corresponds with the GAMS names.
In turn these data are loaded into a GDX file and on to GAMS using (note the Gdxxrw line below cannot be split into 2 lines when used but is here for readability)
execute 'gdxxrw gdxxrwss.xls o=regdata.gdx par=regestimate Rng=regress!a1:f3
par=rsq Rng=regress!a4:b4 rdim=1';
execute_load 'regdata.gdx',regestimate,rsq;
Finally the result is
---- 119 PARAMETER regestimates RESULTS FROM MODEL RUNS WITH VARYING RAP
intercept rap rapsquare rapcube rapfour rsquare
mean .coef 133.7605 -27.6837 1.8079 -0.0414 0.0003 0.9711
mean .stderr 2.3321 2.2489 0.2253 0.0061 4.603474E-5
BUYSTOCK1.coef 1.2887 0.1672 -0.0239 0.0007 -5.13349E-6 0.0773
BUYSTOCK1.stderr 0.4190 0.4041 0.0405 0.0011 8.270827E-6
BUYSTOCK2.coef 8.0474 -1.9637 0.1369 -0.0032 2.252415E-5 0.4483
BUYSTOCK2.stderr 0.9416 0.9080 0.0910 0.0025 1.858634E-5
BUYSTOCK3.coef 6.7634 -2.1940 0.1670 -0.0041 2.885521E-5 0.2948
BUYSTOCK3.stderr 1.2139 1.1706 0.1173 0.0032 2.396214E-5
BUYSTOCK4.coef 1.7944 0.8042 -0.0874 0.0024 -1.74360E-5 0.0918
BUYSTOCK4.stderr 0.7616 0.7344 0.0736 0.0020 1.503290E-5
where the spreadsheet was used to do 5 regressions.