Excel export and import in a loop

[Jonas]

Dear colleagues,

I would like to run two models iteratively and exchange parameters between them, which should be modified in Excel in between runs. I use GAMS Connect to communicate between GAMS and Excel.
The problem is, when importing from Excel, only the base parameter-value is imported (and not the updated value for each iteration). When I open the Excel file after having the model run, I see the parameter value of the last iteration, so I guess the problem is caused by the importing.
Is there maybe a simple solution/workaround to this? Otherwise, of course, one solution would be to get rid of Excel completely and do all the transformations in GAMS as well.
Many thanks!
Jonas


Here is a simple example of the embedded code I use for the export and import (here without any transformation in between).

Code: Select all

EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: qwati

- PandasExcelWriter:
    trace: 3
    file: test/data.xlsx
    symbols:
      - name: qwati
        range: qwati!A1
 
endEmbeddedCode

$onEmbeddedCode Connect:
- PandasExcelReader:
    file: test/data.xlsx
    symbols:
      - name: qwat_mod
        rowDimension: 0
        columnDimension: 0
        range: qwati!A1
- GAMSWriter:
    writeAll: True
  
$offEmbeddedCode

[bussieck]

One issue might be, that Connect's Excel reader reads values from the spreadsheet. It does not reevaluate formulas. So if you write to one part of the spreadsheet and hope that another part gets recalculated, I think this is not happening. The Excel API we use in Connect just does not do that. For that you will need to use gdxxrw which truely communicates with the Excel "server".

Another possibility is that you use the export (PandasExcelWriter) at execution time, but the import (PandasExcelReader) happens at compile time. Since you did not provide the entire logic of your loop, that could be an issue here too.

-Michael

[Jonas]

Dear Michael,

thank you very much for your quick and insightful reply!
Regarding the first point: interesting to know that gdxxrw is more powerful in that way. Yet, in the attached example I was referring to the very same cell for the export and the import, without any transformation. Both tasks were performed directly after each other in execution time.

As I would want to perform transformations in Excel, I replaced Connect with gdxxrw now (please see the full loop below). Yet, in this case, I run into problems as " Dollar control statements are processed during compilation and NOT during execution. The use of dollar statements that change data may be misleading when inside a LOOP or IF statement." (Error 509).

So the original idea of integrating some Excel transformations in between model iterations seems not to be possible, I guess.

Best,
Jonas

Code: Select all

set iter iterations /1*10/

loop(iter,
*first run MODEL1
  solve MODEL1 using lp maximizing Y;
    
* export parameters to Excel
Execute_unload "data_exchange.gdx",qwati;
Execute "GDXXRW i=data_exchange.gdx O=test/data_exchange.xlsx INDEX=LAYOUT!A4" ;

* import from Excel
$CALL  "GDXXRW  i=test/data_exchange.xlsx o=data.gdx index=Layout!A4"
$GDXIN data.gdx
$LOAD  qwat
$GDXIN

*then run MODEL1
  solve MODEL2 using MCP;
   
* define threshold
  stop(res,"1")= 1;
  stop("price",iter)$respwati(iter-1)=(respwati(iter)-respwati(iter-1))/respwati(iter-1)*100;
  stop("quant",iter)$resqwati(iter-1)=(resqwati(iter)-resqwati(iter-1))/resqwati(iter-1)*100;

break$((abs(stop("price",iter))<threshold) and(abs(stop("quant",iter))<threshold));

* repeat until threshold or iteration limit is reached
cnt = cnt+1
)
[code]