topic Re: efficient frontier of portfolio with proc iml : results summary and adding constraints in SAS/IML Software and Matrix Computations

efficient frontier of portfolio with proc iml : results summary and adding constraints

ilikesas — Thu, 29 Oct 2015 03:18:11 GMT

Hi,

suppose that I have the following stock return data for 3 stocks:

data returns;
input abc def zzz;
datalines;
10 2 5
3 -5 7
-1 -7 6
1 3 -2
;
run;

I used the following code to calculate the efficient frontier, i.e, for each level of risk (variance) to find the optimal return and the weights of the stocks that correspond to each risk-return combination:

Proc IML;
use returns;
read all var _num_ into RMAT[colname=varNames];
Print RMAT;
f = 1;
g = 2;
n = 4; /* number of rows*/
k = 3; /* number of columns*/
RAVG = j(f,k,0);
RAVG=RMAT[:,]; /* to calculate column average returns, a 1 x k matrix*/
Print RAVG;
xbar = REPEAT(RAVG,n,f); /* to create an n x k matrix*/
Print xbar;
RMX = j(n,k,0);
RMX = RMAT-xbar; /* to calculate RMX*/
Print RMX;
V = j(k,k,0);
V=RMX`*RMX /(n-1); /* to calculate covariance matrix*/
Print V;
VINV =j(k,k,0);
VINV = INV(V); /* to take inverse of V*/
Print VINV;
P = 0.05;
M={1,.05}; /* to define M and required return (must change)*/
Print M;
DIG = j(1,k,1);
R = j(g,k,0);
R =DIG//RAVG; /* to concatenate (combine) DIG and RAVG to a 2 x k matrix*/
Print R;
H =j(2,2,0);
H = R*inv(V)*R`; /* to calculate H*/
Print H;
HINV = j(2,2,0);
HINV = INV(H); /* to take inverse of H*/
PRINT HINV;
Do until (P<=0);
W = j(1,k,0);
W = VINV*R`*HINV*M; /* to calculate portfolio weights*/
Print W;
PVAR = j(1,1,0);
PVAR = W`*V*W; /* to calculate portfolio variance given a return and weights*/
Print PVAR;
P = P-.005;
M = M - {0, .005};
end;
quit;

Basically the core of the code is done, what I need are 2 special additions:

1) I would like to create a table which will contain the information in the following way:

Weight abc	Abc1	Abc2	Etc.
Weight def	Def1	Def2
Weight zzz	Zzz1	Zzz2
return	Ret1	Ret2
variance	Var1	Var2

2) I would like to add the constraint of no short selling, i.e, the weights should all be greater or equal to 0. I just don't know how to add constraints into Proc IML

Thank you very much!!

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

IanWakeling — Thu, 29 Oct 2015 08:34:36 GMT

I don't have a complete understanding of what you are trying to do here, but I think I can answer your first question. You need to create a matrix large enough to hold all the results and then use a do loop with a counter. Then as the loop iterates, write submatrices of the results table.

rtab = j(k+1, 10);
do i = 1 to 10;
  <existing code from do loop here>
  rtab[1:k, i] = W;
  rtab[k+1, i] = PVAR;
end;
print rtab [rowname=varNames];

A few other comments. V = COV(RMAT) will give the covariance matrix in just one statement, and note that there is no need to write declarative statements like V = j(k, k, 0);

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

Rick_SAS — Thu, 29 Oct 2015 17:37:45 GMT

And here are some general programming tips to simplify your code:
1) Use the NROW and NCOL functions to get the number of rows and columns for RMAT

2) Use the MEAN and COV functions for the computations of RAVG and V.

3) You don't need to use the REPEAT statement to make the mean vector a full matrix. SAS/IML knows how to subtract a mean vector from each row of a matrix.

4) There is no need to "pre-allocate" every variable before you assign to it. Just make the assignment and the dimensions of the variable will be determined automatically.

5) [Optional] You can get rid of the final loop if you want. You can create a 2 x 10 matrix of all the values of W and use that matrix in the computations.

The simplified code looks like this:


Proc IML;
use returns;
read all var _num_ into RMAT[colname=varNames];
n = nrow(RMAT);    /* number of rows*/
k = ncol(RMAT);    /* number of columns*/
RAVG = mean(RMAT); /* mean of columns */
V = cov(RMAT);     /* covariance matrix */
VINV = INV(V);     /* inverse of V */
R = j(1,k,1) // RAVG; /* concatenate DIG and RAVG to a 2 x k matrix*/
H = R*VINV*R`;   

P = do(0.05, 0.005, -0.005);  /* sequence of values for P */
M = j(1, ncol(P), 1) // P; 
W = VINV*R`*inv(H)*M;         /* portfolio weights*/
PVAR = T( vecdiag(W`*V*W ));  /* portfolio variance given a return and weights*/
print W[colname=(char(P)) rowname=varNames],
      PVAR[colname=(char(P))];

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

Rick_SAS — Thu, 29 Oct 2015 17:40:32 GMT

For (2), do you mean that you want to replace any negative weights with zeros? If so, you can use the elementwise maximum operator as follows:

W = W <> 0;

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

ilikesas — Sat, 31 Oct 2015 01:31:44 GMT

Hi Rick,

thank you for your code, now I have the weights in W and the variances in PVAR, for any given level of return. But is it possible to cretae data tables for both W and PVAR becasue when I tried doing:

data all;
set w pvar;
run;

I got an error message that tables w and pvar don't exist.

For the second part of the question, I just realized that I forgot to mention that there is another constraint: the sum of all weights should be equal to one - and this constraint is also applicable to the code that I put in the beginning of this topic.

So the additional constraint that each weight is >=0 would affect the entire calculaton from the beginning, otherwise the result would lose its meaning if all the negative weights are made = 0 and all the others kept as they are.

Thank you very much!!!

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

Rick_SAS — Sun, 01 Nov 2015 12:06:26 GMT

See this article about how to write data from SAS/IML matrices to SAS data sets.

If your goal is to find the optimal solution for a linear problem with constraints, that operation is called "linear programming." See the "Linear programming" example in the SAS/IML documentation. In SAS/IML 13.1 and beyond, use the LPSOLVE call to solve linear programming problems.

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

ilikesas — Sun, 01 Nov 2015 16:58:21 GMT

Hi Rick,

I went to the link for the discussion on your blog and managed to create 2 tables. But now I have 2 small problems with the names of the columns and rows.

Here is my code:

data returns;
input abc def zzz;
datalines;
10 2 5
3 -5 7
-1 -7 6
1 3 -2
;
run;

Proc IML;
use returns;
read all var _num_ into RMAT[colname=varNames];
n = nrow(RMAT); /* number of rows*/
k = ncol(RMAT); /* number of columns*/
RAVG = mean(RMAT); /* mean of columns */
V = cov(RMAT); /* covariance matrix */
VINV = INV(V); /* inverse of V */
R = j(1,k,1) // RAVG; /* concatenate DIG and RAVG to a 2 x k matrix*/
H = R*VINV*R`;

P = do(0.05, 0.005, -0.005); /* sequence of values for P */
M = j(1, ncol(P), 1) // P;
W = VINV*R`*inv(H)*M; /* portfolio weights*/
PVAR = T( vecdiag(W`*V*W )); /* portfolio variance given a return and weights*/
print W[colname=(char(P)) rowname=varNames],
PVAR[colname=(char(P)) rowname={'variance'}];
create Weights from W [colname=(char(p)) ] ;
append from W;
close Weights;
create Variance from pvar [colname=(char(P))] ;
append from pvar;
close Variance;

1) the column names for both new tables are output in a strange way. For ex, it should be 0.05 but I get _0D05

2) for the new table Weights instead of getting the stock names on the left I get numbers 1,2,3.

I tried doing

create Weights from W [colname=(char(p)) rowname=varNames ] ;

but then didn't get any table at all and an error message saying that "number of columns in W does not match witht he number of variables in the data set".

Thank you!

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

ilikesas — Sun, 01 Nov 2015 23:11:49 GMT

Hi Rick,

concerning my first reply to your last poste I think that I managed to the tables that I wanted with the necessary column and row names:

/*creating tables return and Variance - the vector P contains the returns!!!*/

create ret from p ;

append from p;

close ret;

create variance from pvar ;

append from pvar;

close variance; quit;

/*to put return and variance together*/

/*transposing and renaming*/

proc transpose data=variance

out=var_transposed;run;

data ret_transposed;

set ret_transposed (rename=(col1=return)); run;

/*adding n*/

data var_transposed;

set var_transposed;

n=_n_;run;

/*merging*/

data return_variance;

merge ret_transposed var_transposed;

by n; run;

/*to put all together*/

/*extracting the stock names*/

proc contents data=returns out=stocks (keep=NAME) ;

run ;

proc print data=stocks ; run ;

/*creating the name "variance"*/

data v;

input name$;

datalines;

variance

; run;

data names;

set stocks v;

n=_n_; run;

/*setting the weights and variance*/

data weights_variance;

set weights variance;

n=_n_; run;

/*combining all into a table*/

data swv;

merge weights_variance names;

by n; run;

As for my question about the constraint, is it possible to do an efficient frontier in proc iml with the constraint (becasue linear optimization is a one-by-one case)?

Thank you!!!

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

Rick_SAS — Thu, 12 Nov 2015 15:19:51 GMT

It would have been helpful if you had linked to this paper when you asked your question: "SAS and the Efficient Portfolio by Thomas H. Thompson and Ashraf El-Houbi ".

Re: efficient frontier of portfolio with proc iml : results summary and adding constraints

ilikesas — Sat, 14 Nov 2015 01:53:50 GMT

Hi Rick,

what a coincidence, I actually also found this paper before posting this question but the paper handles only the unrestricted case (i.e, short selling allowed so that the weights of the stocks can be negative) and that's the reason why I posted this question, in order to do the restricted case when short selling is not allowed.

Thank you!