portfolio optimization - extracting the optimal variance

[ilikesas]

Hi,

here is a part of a macro that I did to find the optimal portfolio with a smallest variance for a given rate of return:

/*OPTIMIZATION SECTION*/

PROC OPTMODEL;

VAR X{1..&VARCOUNT} >= 0;

NUM COEFF{1..&VARCOUNT, 1..&VARCOUNT} = [ %DO I = 1 %TO &VARCOUNT;

%DO J = 1 %TO &VARCOUNT;

&&COEFF&I&J %END; %END; ];

NUM R{1..&VARCOUNT}=[ %DO I = 1 %TO &VARCOUNT; &&MEAN&I %END; ];

/* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */

MINIMIZE F = SUM{I IN 1..&VARCOUNT, J IN 1..&VARCOUNT}COEFF[I,J]*X[I]*X[J];

/* SUBJECT TO THE FOLLOWING CONSTRAINTS */

CON BUDGET: SUM{I IN 1..&VARCOUNT}X[I] = 10000;

CON GROWTH: SUM{I IN 1..&VARCOUNT}R[I]*X[I] = 500;

 SOLVE WITH QP;

 PRINT X;

This macro works fine and it gives me the optimal solution, but how do I extract the actual variance of the portfolio which it calculated and which is supposed to be the minimal variance for a given return?

Thnak you!

[RobPratt]

The objective function is F. You can display its value in the listing by using the PRINT statement, like you did for X:

print F;

You can also save its value to a macro variable or a data set.

[ilikesas]

Hi RobPratt,

I did the Print F and the result that I got is SAS Output

F
694025835

This is weired becasue I double chequed with Excel and the weights that I obtain in SAS are the same as those that I obtain from Excel so they are correct, but the portfolio stdev in Excel is 2.63 ?

Also, I tried saving my weights into a data set by doing something like 

create data weights from xopt=x ;

 but this didn't give me any new data set....

Thank you!

[RobPratt]

Please attach your full code and data.

Correct syntax for CREATE DATA is:

create data weights from [i]=(1..&VARCOUNT) xopt=x ;

[ilikesas]

Hi RobPratt,

here is my data and the macro code:

data returns;

input a b c;

datalines;

10 2 5

3 -5 7

-1 -7 6

1 3 -2

;

run;

%MACRO a(FILE);

/*EXTRACTION OF CONTENTS*/

PROC CONTENTS DATA = &FILE /*in my example data there is no date!!!*/ OUT = CONTENTS(KEEP = NAME) NOPRINT;

RUN;

/*SETTING THE VARIABLES*/

DATA _NULL_;

SET CONTENTS;

CALL SYMPUT('VAR'||TRIM(LEFT(_N_)),name);

CALL SYMPUT('VARCOUNT',TRIM(LEFT(_N_)));

RUN;

/*CALCULATING THE VARIANCE COVARIANCE TABLE*/

PROC CORR DATA = &FILE OUT=CORRTABLE(WHERE=(UPCASE(_TYPE_) IN ("COV","MEAN"))) COV NOSIMPLE NOPRINT;

VAR %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;

WITH %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;

RUN;

/*COVARIANCE TABLE AND MEAN TABLE*/

DATA COVTABLE MEANTABLE; SET CORRTABLE;

IF UPCASE(_TYPE_) IN ("MEAN") THEN OUTPUT MEANTABLE;

ELSE OUTPUT COVTABLE;

RUN;

/*ASSIGNING THE COVARIANCE VALUES*/

DATA _NULL_; SET COVTABLE(DROP = _TYPE_ _NAME_);

ARRAY X{&VARCOUNT} %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;

DO I = 1 TO &VARCOUNT;

CALL SYMPUT('COEFF'||TRIM(LEFT(_N_))||TRIM(LEFT(I)),X{I}); END;

RUN;

/*ASSIGNING THE MEAN VALUES*/

DATA _NULL_; SET MEANTABLE(DROP = _TYPE_ _NAME_);

ARRAY X{&VARCOUNT} %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;

DO I = 1 TO &VARCOUNT;

CALL SYMPUT('MEAN'||TRIM(LEFT(I)),X{I}); END;

RUN;

/*OPTIMIZATION SECTION*/

PROC OPTMODEL;

VAR X{1..&VARCOUNT} >= 0;

NUM COEFF{1..&VARCOUNT, 1..&VARCOUNT} = [ %DO I = 1 %TO &VARCOUNT;

%DO J = 1 %TO &VARCOUNT;

&&COEFF&I&J %END; %END; ];

NUM R{1..&VARCOUNT}=[ %DO I = 1 %TO &VARCOUNT; &&MEAN&I %END; ];

/* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */

MINIMIZE F = SUM{I IN 1..&VARCOUNT, J IN 1..&VARCOUNT}COEFF[I,J]*X[I]*X[J];

/* SUBJECT TO THE FOLLOWING CONSTRAINTS */

CON BUDGET: SUM{I IN 1..&VARCOUNT}X[I] = 10000;

CON GROWTH: SUM{I IN 1..&VARCOUNT}R[I]*X[I] = 500;

 SOLVE WITH QP;

 PRINT X;

%MEND;

%a (returns)

[RobPratt]

It appears that the portfolio standard deviation is calculated by post-processing the X values to sum to 1:

for {i in 1..&varcount} X[i] = X[i]/10000;
print (sqrt(F));

Alternatively, you can leave the X values alone and divide by the square of the budget:

print (sqrt(F/10000^2));

SAS Output

2.6344

[RobPratt · Accepted solution]

By the way, you can avoid all the macro language by instead reading the data sets directly in PROC OPTMODEL:

PROC OPTMODEL;
   set <str> ASSETS;
   read data contents into ASSETS=[NAME];
   VAR X{ASSETS} >= 0;
   NUM COEFF{ASSETS, ASSETS};
   read data covtable into [i=_NAME_] {j in ASSETS} <coeff[i,j]=col(j)>;
   NUM R{ASSETS};
   read data meantable into {j in ASSETS} <r[j]=col(j)>;
   /* MINIMIZE THE VARIANCE OF THE PORTFOLIO’S TOTAL RETURN */
   MINIMIZE F = SUM{I IN ASSETS, J IN ASSETS}COEFF[I,J]*X[I]*X[J];
   /* SUBJECT TO THE FOLLOWING CONSTRAINTS */
   CON BUDGET: SUM{I IN ASSETS}X[I] = 10000;
   CON GROWTH: SUM{I IN ASSETS}R[I]*X[I] = 500;
   SOLVE;
   PRINT X;
   print (sqrt(F/10000^2));
quit;

[ilikesas]

Hi RobPratt,

Thanks a lot now SAS outputs the same stdev that I obtained from Excel!!!

Also thank you for the code of putting the data set directly into proc optmodel without doing a macro!!!

You are helping me a lot so please allow me to ask you some more related questions 🙂

What I want to do now is for a given stdev to find the max return. 

The actual code:

data returns;
input a b c;
datalines;
10 2 5
3 -5 7
-1 -7 6
1 3 -2
;
run;

PROC OPTMODEL;
set <str> returns;
read data contents into returns=[NAME];
VAR X{returns} >= 0;
NUM COEFF{returns, returns};
read data covtable into [i=_NAME_] {j in returns} <coeff[i,j]=col(j)>;
NUM R{returns};
read data meantable into {j in returns} <r[j]=col(j)>;
/* Maximize the return */
maximize GROWTH= SUM{I IN returns}R[I]*X[I] ;
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET: SUM{I IN returns}X[I] = 10000;
con var: SUM{I IN returns, J IN returns}COEFF[I,J]*X[I]*X[J] =4;
SOLVE;
PRINT X;
print growth;
quit;

So here I have a budget of 10000 and want to find the max return for a variance = 4

The result that I get is:

SAS Output

1]	X
a	-822.39
b	1145.79
c	1212.73

GROWTH
173.01

This is different from what I got in Excel because in Excel I assumed that there is no short selling (i.e, all weights are greater or equal to 0) and in this code I also mentioned 'VAR X{returns} >= 0;' 

So what I can't figure out is why a = -822.39 since the smallest weight that I can get is 0?

Thank you very much!!! 

[RobPratt]

You should see these NOTEs in the log:

NOTE: Maximum number of iterations reached.
NOTE: Objective = 175.59076489.
NOTE: The least infeasible solution found is returned.

Also notice the positive value for Infeasibility in the Solution Summary:

SAS Output

Infeasibility	8467.8268186

For your var constraint, try a less aggressive right-hand side like 4e8, which is on the order of the value from your earlier optimization.  Alternatively, change the right-hand side of your budget constraint to 1.

[ilikesas]

Hi Rob,

I set the budget constraint =1 and left the variance = 4 as before and I managed to get the same result that Ecxel solver gave me.

Here is the code:

data returns;
input a b c;
datalines;
10 2 5
3 -5 7
-1 -7 6
1 3 -2
;
run;

%MACRO a(FILE);
/*EXTRACTION OF CONTENTS*/
PROC CONTENTS DATA = &FILE /*in my example data there is no date!!!*/ OUT = CONTENTS(KEEP = NAME) NOPRINT;
RUN;
/*SETTING THE VARIABLES*/
DATA _NULL_;
SET CONTENTS;
CALL SYMPUT('VAR'||TRIM(LEFT(_N_)),name);
CALL SYMPUT('VARCOUNT',TRIM(LEFT(_N_)));
RUN;
/*CALCULATING THE VARIANCE COVARIANCE TABLE*/
PROC CORR DATA = &FILE OUT=CORRTABLE(WHERE=(UPCASE(_TYPE_) IN ("COV","MEAN"))) COV NOSIMPLE NOPRINT;
VAR %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
WITH %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
RUN;
/*COVARIANCE TABLE AND MEAN TABLE*/
DATA COVTABLE MEANTABLE; SET CORRTABLE;
IF UPCASE(_TYPE_) IN ("MEAN") THEN OUTPUT MEANTABLE;
ELSE OUTPUT COVTABLE;
RUN;
/*ASSIGNING THE COVARIANCE VALUES*/
DATA _NULL_; SET COVTABLE(DROP = _TYPE_ _NAME_);
ARRAY X{&VARCOUNT} %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
DO I = 1 TO &VARCOUNT;
CALL SYMPUT('COEFF'||TRIM(LEFT(_N_))||TRIM(LEFT(I)),X{I}); END;
RUN;
/*ASSIGNING THE MEAN VALUES*/
DATA _NULL_; SET MEANTABLE(DROP = _TYPE_ _NAME_);
ARRAY X{&VARCOUNT} %DO I = 1 %TO &VARCOUNT; &&VAR&I %END; ;
DO I = 1 TO &VARCOUNT;
CALL SYMPUT('MEAN'||TRIM(LEFT(I)),X{I}); END;
RUN;
/*OPTIMIZATION SECTION*/
PROC OPTMODEL;
VAR X{1..&VARCOUNT} >= 0;
NUM COEFF{1..&VARCOUNT, 1..&VARCOUNT} = [ %DO I = 1 %TO &VARCOUNT;
%DO J = 1 %TO &VARCOUNT;
&&COEFF&I&J %END; %END; ];
NUM R{1..&VARCOUNT}=[ %DO I = 1 %TO &VARCOUNT; &&MEAN&I %END; ];
/*maximize the growth*/
maximize GROWTH= SUM{I IN 1..&VARCOUNT}R[I]*X[I];
/* SUBJECT TO THE FOLLOWING CONSTRAINTS */
CON BUDGET: SUM{I IN 1..&VARCOUNT}X[I] = 1;
con f: SUM{I IN 1..&VARCOUNT, J IN 1..&VARCOUNT}COEFF[I,J]*X[I]*X[J] = 4 ;
SOLVE ;
PRINT X;
print growth;
%MEND;

%a (returns);

But now some other small issue appeared: the code that you wrote for doing the whole process without the macro works (at least when I tried it) only after I run the macro. When I opened SAS and wanted to find the optimal weights for my data with the macro-less code I got error messages such that data contents doesn't exist etc.

Thank you!!!

[RobPratt]

I meant that you don't need any macro language within the PROC OPTMODEL call, and you don't need to generate the MEAN&i and COEFF&i&j macro variables.  You do need to generate the meantable and covtable data sets before you can read them with the READ DATA statement in PROC OPTMODEL.