minimising Value at Risk - Thread 2

[feresm]

@RobPratt 

Following the previous thread I made some advance on this project. I wrote a small document explaining the concepts and a small mathematical model to understand what I am trying to accomplish. The point I am stuck now is the following:

1) READ DATA for historical values. I found a way to it, but I am not sure it is correct. Anyway when I define the vector I am trying to optimise, it throws an error. The data has 3 dimensions, date, asset, period and I READ the information putting together Asset/Data (ATIVO_DATE), but in the end I need them separately. I could not find a way to read it that way.

2) I used an implicit variable to calculate the final vector to be minimised. It does not show any error, but when I use expand to check its contents it does not show.

3) I believe these problems are preventing the solver to work.

The code follows and I commented it to help you to understand it more easily. The files I used as well as the document I wrote are attached.

===== Code ====

/* we kept some words in Portuguese as in the original data

Portuguese vs English

ativo = asset

data = date

*/

DATA max_limits;

input ativo $ val_max;

datalines;

Nickel 5.5

Tin 2.2

;

%let max_metals=6.0;

/* the following macro variables are not used yet, since we are validating the model

with only two metal commodities*/

/*

%let max_agri=12;

%let max_total=20;*/

PROC OPTMODEL;

/* Read historic and constraints table from WORKLIB */

/* LIM_METALS contains the max values by asset/period

and the max of total assets by type (in this case METALS)

 per period */

set <str> PERIODS=/_1M _3M _6M _9M _1Y _2Y _3Y/;

set <str> COMMS;

num lim_metals {COMMS, PERIODS};

read data WORK.LIM_METAL into COMMS=[commodity] {j in PERIODS} <lim_metals[commodity, j]=col(j)>;

print lim_metals;

/* tried to do it like this, but it did not work

set DATES;

num hist_data {DATES, COMMS, PERIODS};

read data WORK.SANTANDER_HIST into [date ativo] {j in PERIODS} <hist_data[date, j, ativo]=col(j)>;

print hist_data;

*/

/*this one works, but not how I intended to. The periods were moved as a single column */

set <num> DATES;

/* added the above statement just to test. I do not think it is correct*/

set <str, num> ATIVOS_DATES;

num hist_asset{ATIVOS_DATES, PERIODS};

read data WORK.santander_hist_3 INTO ATIVOS_DATES=[ativo date]

{period in PERIODS} <hist_asset[ativo, date, period]=col(period)>;

print hist_asset;

num val_max{COMMS};

read data max_limits INTO COMMS=[ativo] val_max;

print val_max;

/* variables to optimise and constraints */

var amount{COMMS, PERIODS} >= 0;

/* not quite sure the following IMPVAR statement has worked

it does not show on expand and when I call the solver it throws an error.*/

impvar amount_date_comm_period {date in DATES}=sum{comm in COMMS,period in PERIODS} amount[comm,period]*hist_asset[comm,date,period];

/* the followin IMPVAR statemente seems to work fine*/

impvar total_comm {comm in COMMS}=sum{period in PERIODS} amount[comm, period];

/* BEFORE impvars. Kept it for test purposes.

con lim_comm {comm in COMMS}: sum{period in PERIODS} amount[comm, period]<= val_max[comm];

Replaced this line with the IMPVAR defined above*/

con lim_comm {comm in COMMS}: total_comm[comm] <= val_max[comm];

con lim_total: sum{comm in COMMS} total_comm[comm] <= &max_metals;

expand;

/* Objective Function */

min value_at_risk= smallest(6, of amount_date_comm_period[*]);

solve with nlp /algorithm=as ms;

[RobPratt]

If you want hist_asset to depend on DATES, COMMS, and PERIODS, you must first populate all three index sets before you populate the parameter.  You already populated COMMS and PERIODS, so you just need DATES:

set DATES;
read data WORK.santander_hist_3 into DATES=[date];
num hist_asset {DATES, COMMS, PERIODS};
read data WORK.santander_hist_3 into [date ativo] {j in PERIODS} <hist_asset[date, ativo, j]=col(j)>;
print hist_asset;

For the IMPVAR, you have the indices of hist_asset out of order:

/*impvar amount_date_comm_period {date in DATES}=sum{comm in COMMS,period in PERIODS} amount[comm,period]*hist_asset[comm,date,period];*/
impvar amount_date_comm_period {date in DATES}=sum{comm in COMMS,period in PERIODS} amount[comm,period]*hist_asset[date,comm,period];

[feresm]

Hi Rob, thanks for your time again. It seems that I have a problem with the other of the parameters. Anyway, I did as you said, with a slight modification. The first change sort of worked, and the second did not.

The read data for the Santander_hist_3 worked, but the table was like in the figure I attached. Instead of having several columns for the periods it sort of transposed them for lines. Also , there were warnings of duplicate regarding the dates, which I believe are harmless because the compound keys (date, asset) are unique. I attached a printout together with the lim_metals, which worked ok. I don't mind if this still works to identify the data.

Fot the IMPVAR id did not work, I guess. the expand command does not show it and when I uncomment the solver statement it throws an error (attached it too). 

Any lights on these? Thank you again.

=======here is the code======

/* we kept some words in Portuguese as in the original data

Portuguese vs English

ativo = asset

data = date

*/

DATA max_limits;

input ativo $ val_max;

datalines;

Nickel 5.5

Tin 2.2

;

%let max_metals=6.0;

/* the following are not used yet since we are validating the model

with only two metal commodities*/

%let max_agri=12;

%let max_total=20;

PROC OPTMODEL;

/* Read historic and constraints table from WORKLIB */

/* LIM_METALS contains the max values by asset/period

and the max of total assets by type (in this case METALS)

 per period */

set <str> PERIODS=/_1M _3M _6M _9M _1Y _2Y _3Y/;

set <str> COMMS;

num lim_metals {COMMS, PERIODS};

read data WORK.LIM_METAL into COMMS=[commodity] {j in PERIODS} <lim_metals[commodity, j]=col(j)>;

print lim_metals;

/* test number 1

set DATES;

read data WORK.santander_hist_3 into DATES=[date];

num hist_data {DATES, COMMS, PERIODS};

read data WORK.SANTANDER_HIST into [date ativo] {j in PERIODS} <hist_data[date,ativo,j]=col(j)>;

print hist_data;

*/

/*this one works, but not how I intended to. The periods were moved as a single column

set <num> DATES;

set <str, num> COMMS_DATES;

num hist_data{COMMS_DATES, PERIODS};

read data WORK.santander_hist_3 INTO COMMS_DATES=[ativo date]

{period in PERIODS} <hist_data[ativo, date, period]=col(period)>;

print hist_data;

*/

/* test number 3*/

set <num> DATES;

num hist_data{DATES, COMMS, PERIODS};

read data WORK.Santander_hist_3 into DATES=[date];

read data WORK.Santander_hist_3 into COMMS=[ativo];

read data WORK.SANTANDER_HIST into [date ativo] {j in PERIODS} <hist_data[date,ativo,j]=col(j)>;

print hist_data;

num val_max{COMMS};

read data max_limits INTO COMMS=[ativo] val_max;

print val_max;

/* variables to optimise and constraints */

var amount{COMMS, PERIODS} >= 0;

/* not quite sure the following IMPVAR statement has worked

it does not show on expand and when I call the solver it throws an error.*/

impvar amount_date_comm_period {date in DATES}=sum{comm in COMMS,period in PERIODS} amount[comm,period]*hist_data[date,comm,period];

/* the following IMPVAR statement seems to work fine*/

impvar total_comm {comm in COMMS}=sum{period in PERIODS} amount[comm, period];

/* BEFORE impvars. Kept it for test purposes.

con lim_comm {comm in COMMS}: sum{period in PERIODS} amount[comm, period]<= val_max[comm];

Replaced this line with the IMPVAR defined above*/

con lim_comm {comm in COMMS}: total_comm[comm] <= val_max[comm];

con lim_total: sum{comm in COMMS} total_comm[comm] <= &max_metals;

expand;

/* Objective Function */

min value_at_risk= smallest(6, of amount_date_comm_period[*]);

solve with nlp /algorithm=as ms;

=======the table remain the same =======

[RobPratt · Accepted solution]

Looks like you just need to read the correct data set:

/*read data WORK.SANTANDER_HIST into [date ativo] {j in PERIODS} <hist_data[date,ativo,j]=col(j)>;*/
read data WORK.SANTANDER_HIST_3 into [date ativo] {j in PERIODS} <hist_data[date,ativo,j]=col(j)>;

With this change, the ERROR messages no longer appear.