ReadData : Reads excel files and converts them to gdx format

Description


Category : GAMS FIN library


Mainfile : ReadData.gms   includes :  BondIndexData.xlsx  BroadIndexData.xlsx

parameters
         usd(*,*),dem(*,*),chf(*,*),
         USDPrices1(*,*),DEMPrices1(*,*),CHFPrices1(*,*),
         USDPrices0(*),DEMPrices0(*),CHFPrices0(*),
         ExchangeRates0(*),ExchangeRates1(*,*),
         IndexReturns(*);

$onEmbeddedCode Connect:
- ExcelReader:
    file: BondIndexData.xlsx
    symbols:
      - { name: USD,            rowDimension: 1, columnDimension: 1, range: USD!A7 }
      - { name: DEM,            rowDimension: 1, columnDimension: 1, range: DEM!A7 }
      - { name: CHF,            rowDimension: 1, columnDimension: 1, range: CHF!A7 }
      - { name: USDPrices1,     rowDimension: 1, columnDimension: 1, range: USDPrices1!A1 }
      - { name: DEMPrices1,     rowDimension: 1, columnDimension: 1, range: DEMPrices1!A1 }
      - { name: CHFPrices1,     rowDimension: 1, columnDimension: 1, range: CHFPrices1!A1 }
      - { name: USDPrices0,     rowDimension: 1, columnDimension: 0, range: USDPrices0!A1 }
      - { name: DEMPrices0,     rowDimension: 1, columnDimension: 0, range: DEMPrices0!A1 }
      - { name: CHFPrices0,     rowDimension: 1, columnDimension: 0, range: CHFPrices0!A1 }
      - { name: ExchangeRates0, rowDimension: 1, columnDimension: 0, range: ExchangeRates0!A1 }
      - { name: ExchangeRates1, rowDimension: 1, columnDimension: 1, range: ExchangeRates1!A1 }
      - { name: IndexReturns,   rowDimension: 1, columnDimension: 0, range: IndexReturns!A1 }
- GAMSWriter:
    writeAll: True
$offEmbeddedCode

alias(*,i,j);

set EE   currencies / USD,DEM,CHF /
    BB   bonds for structural model
    BB2  bonds for co-movements model
    CC   columns for structural model
    SS   set of scenarios for co-movements model
    BxE(*,EE)
    BxE2(*,EE);

parameter data(*,*),data2(*,*),data3(*);

data(i,j) = usd(i,j) + dem(i,j) + chf(i,j);

data2(i,j) =  USDPrices1(i,j)+ DEMPrices1(i,j) + CHFPrices1(i,j);

data3(i) =  USDPrices0(i)+ DEMPrices0(i) + CHFPrices0(i);


BB(i) = SUM(j$data(i,j), YES);
BB2(j) = SUM(i$data2(i,j), YES);

SS(i) = SUM(j$data2(i,j), YES);

CC(j) = SUM(i$data(i,j), YES);

BxE2(j,'USD') = SUM(i$USDPrices1(i,j), YES);
BxE2(j,'DEM') = SUM(i$DEMPrices1(i,j), YES);
BxE2(j,'CHF') = SUM(i$CHFPrices1(i,j), YES);

BxE(i,'USD') = SUM(j$usd(i,j), YES);
BxE(i,'DEM') = SUM(j$dem(i,j), YES);
BxE(i,'CHF') = SUM(j$chf(i,j), YES);


PARAMETERS
         AssetReturns(*,*,*), ExchangeRateReturns(*,*);

$onEmbeddedCode Connect:
- ExcelReader:
    file: BroadIndexData.xlsx
    symbols:
      - { name: AssetReturns,        rowDimension: 1, columnDimension: 2, range: "Asset Returns!A1" }
      - { name: ExchangeRateReturns, rowDimension: 1, columnDimension: 1, range: "Exchange Rate Returns!A1" }
- GAMSWriter:
    writeAll: True
$offEmbeddedCode

ALIAS(*,i,j,k);

SETS
         EE2 Currencies
         TT Time periods
         AA Asset classes
         AxE(*,*);


TT(i) =  SUM((j,k)$AssetReturns(i,j,k), YES );
AA(j) =  SUM((i,k)$AssetReturns(i,j,k), YES );
EE2(k) = SUM((i,j)$AssetReturns(i,j,k), YES );

AxE(j,'USD') = SUM(i$AssetReturns(i,j,'USD'), YES );
AxE(j,'EUR') = SUM(i$AssetReturns(i,j,'EUR'), YES );
AxE(j,'GBP') = SUM(i$AssetReturns(i,j,'GBP'), YES );
AxE(j,'JPY') = SUM(i$AssetReturns(i,j,'JPY'), YES );

DISPLAY TT,EE2,AA,AxE,ExchangeRateReturns;

EXECUTE_UNLOAD 'InputData';