Hi Lothar,
From what I understood you have a grid with some base parameters (let’s call them static) and some parameters that vary according to a given time step (let’s call them dynamic). We can model these entities like the following
set TMC;
set MPC_BUS_ID;
set TIME_BUS within TMC cross MPC_BUS_ID;
param mpc_bus_vm {TIME_BUS};
param mpc_bus_va {TIME_BUS};
We created an additional set, TIME_BUS, to index the state of bus related parameters at a given time. Note that due to the within operator TIME_BUS is defined as a subset of TMC cross MPC_BUS_ID, so you can skip any bus at any time step and in fact define which buses are defined at a given time. For example, with the following data
TIME BUS mpc_bus_vm mpc_bus_va
1 1 1 9
1 2 2 8
1 3 3 7
2 2 4 6
2 3 5 5
2 4 6 4
3 3 7 3
3 4 8 2
3 5 9 1
you would have buses 1, 2 and 3 at time 1 with the corresponding values for the
mpc_bus_vm and mpc_bus_va parameters, buses 2, 3 and 4 for time 2 and so on. We’re basically creating a single table representation of the grid at each time step.
If you create a spreadsheet with a sheet named time_bus with the mentioned data
you can load it with the following script
reset;
load amplxl.dll;
model example.mod;
# load static data
let TMC := 1..3;
let MPC_BUS_ID := 1..5;
# load dynamic data
table time_bus IN "amplxl" "example1.xlsx":
TIME_BUS <- [TIME, BUS], mpc_bus_vm, mpc_bus_va;
read table time_bus;
# display data
display mpc_bus_vm, mpc_bus_va;
# display buses in use for each time step
for {t in TMC}{
display {(t,k) in TIME_BUS}
}
At the end of the script the buses of each time step are displayed. Model constraints could be expressed in a similar way.
Ideally you would preprocess the data beforehand and pass a single table with the TIME and BUS columns to AMPL. It’s also possible to pass a single table per sheet with the information of a given time step, like you mentioned, and process the data directly in AMPL with an indexed collections of tables.
The following script shows how to do it
reset;
load amplxl.dll;
model example.mod;
# load static data
let TMC := 1..3;
let MPC_BUS_ID := 1..5;
# load dynamic data
let TIME_BUS := {};
# auxiliary entities to read the data iteratively
set TEMP_BUS_ID;
param temp_vm {TEMP_BUS_ID};
param temp_va {TEMP_BUS_ID};
table test {t in TMC} IN "amplxl" "example2.xlsx" ("Sheet" & t):
TEMP_BUS_ID <- [mpc_bus_id], temp_vm ~ mpc_bus_vm, temp_va ~ mpc_bus_va;
# read the tables one by one
for {t in TMC}{
reset data TEMP_BUS_ID, temp_vm, temp_va;
read table test[t];
display temp_vm, temp_va;
# copy data to main entities
let TIME_BUS := TIME_BUS union setof {b in TEMP_BUS_ID}(t, b);
let {b in TEMP_BUS_ID} mpc_bus_vm[t, b] := temp_vm[b];
let {b in TEMP_BUS_ID} mpc_bus_va[t, b] := temp_va[b];
}
# display data
display mpc_bus_vm, mpc_bus_va;
# display buses in use for each time step
for {t in TMC}{
display {(t,k) in TIME_BUS}
}
In the example sheet[i] holds the data for time step i, adapt as needed.
The model is the same. Note the auxiliary entities in the .run file to load the data without affecting the readability/explainability of the original model.
Samples attached.
samples.zip (12.6 KB)