Using PROC OPTMODEL to minimise Value At Risk (VAR).

[feresm]

Hi,

I was given a task to replace an excel solver application that, supposedly, optimises the value at risk for a bank. VaR is the maximum amount of money that a financial company is risking to lose in 1 day with a certain confidence, in this case it would be 99%. I am not a newbie in optimisation, but I am in SAS. The input data is the historical values for a given commodity futures (eg. Nickel for 1 month, 3 months, 6 months, 9 months, 1, 2 and 3 years), with some investment constraints, and the objective of calculating how much I should invest in each of the allocations to minimise the value at risk with 99% confidence. I am using a Viya 4 RACE machine with PROC OPTMODEL, but I am struggling to load the historical data into the model in a way that makes sense. Attached is a sample spreadsheet with the data as is.  Ii had to use the Viya Data Manager to load it to CAS and I am reading it from there (PUBLIC.nickel). I am stuck now on how to format the data in PROC OPTMODEL. The final objective function would be like PCTL(sum for all periods(allocation*data), 0.99) subject to constraints like total amount per allocation, max allocation per commodity, etc.

Any help would be welcome.

[Ksharp]

Calling @RobPratt

[pchristophel]

While @RobPratt is not available, here at least some hints that might help you. You probably want to use the "col"-keyword like in this example: https://go.documentation.sas.com/doc/en/pgmsascdc/default/casmopt/casmopt_optmodel_examples03.htm

Without knowing all the specifics, your code could look something like this:

data nickel_data;
	input date $ _1M _3M _6M _9M _1Y _2Y _3Y;
datalines;
26.03.2024	-0.018635056	-0.018385553	-0.018318607	-0.018039787	-0.017512672	-0.017208009	-0.016648498
25.03.2024	-0.016785527	-0.016721158	-0.016225539	-0.016015256	-0.015007492	-0.014073823	-0.013776717
22.03.2024	0.002820084	0.00266288	0.002205214	0.002018829	0.001855281	0.001093318	0.001293611
21.03.2024	0.005673496	0.005488334	0.005531348	0.004918331	0.00477321	0.004080033	0.003281195
20.03.2024	-0.027201333	-0.0265231	-0.025996132	-0.025217837	-0.024320574	-0.022829984	-0.021054457
19.03.2024	-0.010027965	-0.007000893	-0.007090242	-0.007114783	-0.006913758	-0.007053669	-0.006796098
;

proc optmodel;
	set <str> PERIODS=/_1M _3M _6M _9M _1Y _2Y _3Y/;
	set <str> DATES;
	num nickel{PERIODS, DATES};
	read data nickel_data into DATES=[date] {j in PERIODS} <nickel[j,date]=col(j)>;
	print nickel;
quit;

Note that variable names in SAS can't start with numbers, that's why I added the "_". You can also read the dates with a date format and use a set like this in OPTMODEL: 

set <num> DATES;

Hope this helps. If you share some SAS code or a mathematical description of a problem you want to solve I can try to help you a little more. But I am not sure if the PCTL function can be used in an OPTMODEL objective like this.

[feresm]

Thank you for your help!

I will try the solution you proposed and I will let you know. I am also not sure that I can use the PCTL function in the objective and this is one of the things I am wanting to try in this simplified model. The actual model will have at least 10 commodities (like, nickel, tin, iron ore, so on and so forth). The Var Model is calculated as follows using historical data:

1) multiply the amount for each commodity/term (eg. nickel 1M, nickel 3 month, etc for all the commodities) for the historical values. This amount is the variable for the model.

2) Add them all up to have a single vector of total amount allocated.

3) In excel we sort this to see what is the 99% value. In this case where we have 512 values of historical data, this value will be between the 5 or 6 smallest value (or highest value depending on we are working with negative or absolute values. I believe this sort would complicate the model and I am not sure either that we can locate a specific position in the resulting vector. I attached a spreadsheet with the full excel model. In this spreadsheet we use the LOWEST (or HIGHEST) excel function in the cell we optimise as objective function.

4) to avoid this sort we can alternatively use the percentile and this is one thing I am wanting to try. I am not aware os any other SAS function to use (open to new ideas!).

5) All this subject to some constraints like: the total amount of investment done in a single commodity, the total investment made in all commodities, etc.

Thank you again.

[RobPratt]

You can use the PCTL, LARGEST, and SMALLEST functions directly, without sorting.  For example:

proc optmodel;
   num n = 10;

   var X {1..n} >= 0 <= 1;
   con Mycon1:
      sum {j in 1..n} X[j] = 4;
   con Mycon2:
      sum {j in 1..n/2} X[j] >= 3;

   min Z1 = pctl(90, of X[*]);
   solve with nlp / algorithm=as ms;
   print X;

   min Z2 = largest(0.1*n, of X[*]);
   solve with nlp / algorithm=as ms;
   print X;

   max Z3 = smallest(2, of X[*]);
   solve with nlp / algorithm=as ms;
   print X;
quit;

[feresm]

thank you, Rob. You're a life saver! I will finish my code and post it here if you like.

[Ksharp]

You also could use PROC QUANTREG or PROC VARMAX to get VaR.
Check Page 11 :
http://support.sas.com/resources/papers/proceedings17/SAS0525-2017.pdf

[feresm]

Thank you! I will check, but I will go one step at a time. They assume normality for the VaR soo far. First, I will try this and then I can try this one, if the project moves forward. But, seems very interesting!.

[feresm]

Hello Guys,

sorry to bother you again with basic things, but I am stuck with the READ DATA for a while. I am trying to read a simple table generated from  a spreadsheet and it is throwing attached. The code I am using is largely based on your help:

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=/Total _1M _3M _6M _9M _1Y _Y2 _Y3/;

set <str> comm;

str commodity;

num lim_metals {comm, PERIODS};

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

print lim_metals;

I use a small flow beforehand to import the excel spreadsheet (attached) into the table WORK.LIM_METAL. Can you help me, please?

Thank you in advance. Regards, Marcio. 

[RobPratt · Accepted solution]

You have two errors.  The first one is that the data set column names are not _Y2 and _Y3 but rather _2Y and _3Y:

/*set <str> PERIODS=/Total _1M _3M _6M _9M _1Y _Y2 _Y3/;*/
set <str> PERIODS=/Total _1M _3M _6M _9M _1Y _2Y _3Y/;

The second one is that you have the two arguments reversed in the lim_metals parameter:

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

With these two corrections, there are no errors, but there is still a note:

NOTE 708-782: The name 'commodity' hides an outer declaration.

To avoid this, you can omit the str commodity declaration, which is not used anywhere.

[feresm]

Thank you again, Rob. I am embarrassed with these rookie mistakes. Anyway, I ran it here and did not get the NOTE you referred to. So, I will move on. Probably I will ask your help again when the time comes to use these data referenced by metals/periods to write the restrictions matrix and the objective function. Thank you again!

[RobPratt]

Glad to help, and no need for embarrassment.  Please start a new thread if you have additional questions.