$TITLE: M5-3.GMS reading from and writing to EXCEL

$ontext
demonstrate reading and writing from/to excel
here we read in from file M5.XLS, data is found in
sheet2, range (rng) from cell B3 to cell E9
be sure that this file M5.GMS is in the project directory.

"echo" output is written to a file M5.XLS sheet 2 cell B12

Results of regression are written to sheet 3

$offtext

SETS  I    observations                 /I1*I6/
      J    dep and ind var              /J1*J3/
      K(J) set of independent variables /J2*J3/
      L    intercept                    /L1/;

PARAMETERS
      Y0(I)
      X0(I,K);

PARAMETERS
 BENCH(I,J);

$CALL GDXXRW M5.xls par=BENCH rng=sheet2!B3:E9
$GDXIN M5.gdx
$LOAD BENCH
$GDXIN

DISPLAY BENCH;

Execute_Unload 'M5.gdx' BENCH
execute 'gdxxrw.exe M5.gdx par=BENCH rng=SHEET2!B12';

Y0(I)    = BENCH(I, "J1");
X0(I,K)  = BENCH(I, K);

DISPLAY Y0, X0;

VARIABLES
  ALPHA      intercept
  BETA(K)    slope coefficients (elasticities since estimated in logs)
  DEV        sum of squared deviations
  YHAT(I)    fitted values of the dependent variable;

EQUATIONS
  OBJECTIVE  objective function = sum of squared residuals
  EYHAT(I)   equation for the fitted values of Y (log linear)
  CRS        constraint constant returns: sum of slope coefficients = 1;


OBJECTIVE..  DEV =E= SUM(I, (YHAT(I) - Y0(I))*(YHAT(I) - Y0(I)));

EYHAT(I)..   LOG(YHAT(I)) =E= ALPHA + SUM(K, BETA(K)*LOG(X0(I,K)));

CRS..        SUM(K, BETA(K)) =E= 1;


* model OLS: unconstrainted OLS

MODEL OLS /OBJECTIVE, EYHAT/;

ALPHA.L   = 1;
BETA.L(K) = 1;
YHAT.L(I) = 2;

SOLVE OLS USING NLP MINIMIZING DEV;


* model OLSC: constrainted least squares, imposes CRS

MODEL OLSC /ALL/;

SOLVE OLSC USING NLP MINIMIZING DEV;

* process output to get observed and fitted values of Y


PARAMETER
 RESULTSA(L, *)
 RESULTSS(K, *)
 RESULTSF(I,*)
 RESULTS(*);

RESULTSA(L, "INTERCEPT") = ALPHA.L;
RESULTSS(K, "SLOPES") = BETA.L(K);

RESULTSF(I, "YHAT") = YHAT.L(I);
RESULTSF(I, "Y0") = Y0(I);

* more fun: calculating regression statistics

PARAMETERS
 MEANY, SS, SSE, RSQUARE, TSTAT(K);

MEANY = SUM(I, Y0(I))/CARD(I);
SS = SUM(I, POWER((Y0(I) - MEANY), 2));
SSE = DEV.L;

RSQUARE = 1 - SSE/SS;

TSTAT(K) = BETA.L(K)/(SQRT((SSE/(CARD(I)-2)) / (SUM(I, POWER(X0(I,K), 2)))));

RESULTSS(K, "TSTATS") = TSTAT(K);
RESULTS("RSQUARE") = RSQUARE;

DISPLAY MEANY, SS, SSE, RSQUARE, TSTAT;
DISPLAY RESULTSA, RESULTSS, RESULTS, RESULTSF;


Execute_Unload 'M5.gdx' RESULTSA
execute 'gdxxrw.exe M5.gdx par=RESULTSA rng=SHEET3!B3'

Execute_Unload 'M5.gdx' RESULTSS
execute 'gdxxrw.exe M5.gdx par=RESULTSS rng=SHEET3!B6'

Execute_Unload 'M5.gdx' RESULTSF
execute 'gdxxrw.exe M5.gdx par=RESULTSF  rng=SHEET3!B10'

Execute_Unload 'M5.gdx' RESULTS
execute 'gdxxrw.exe M5.gdx par=RESULTS rng=SHEET3!C18'