Modern Portfolio Theory using SAStextregistered OR

1 Modern Portfolio T heory using S A S ® OR Murphy Cho y , School o f Information Sy stem, SM U , Singapore A BSTRACT Investment approaches in financial instru ments have been v aried and often produce unpredictable results. Many investors in the earlier day s of investment banking suffered catastro phical losses due to poor strategy and lack of understanding of the financial market. W ith the development of investment banking, many innovative investment strategies have been proposed to make portfolio returns higher than the overall market. One of the most famous theories of portfolio crea tion and management i s the modern portfolio theory proposed by Harry Markowitz. In this paper, we shall apply the theory in creating a portfolio of stocks as well as managing it. INTRODU CTION Investment b anking h as bee n one of the mos t interesting branches of banking fo r a variety of reasons ranging from the extreme l evels of salary and bonus o ne receive to the com plicated strategies in executing the various investment strategies in different financial markets. As many of the financial trades are done in environment where common people a re unable to access, many of the more sophisticated financial instruments are n ot available for common people to purchase as part of their portfolio. Fo r example, there are not man y people on the s treet who will purchase a fu tures contract of Euro worth $ 125,000. However, the com mon people will be likely to acq uire a small p ortfolio consisting of stocks a nd warrants. There are m any approaches to creating a profitable portfolio. Given the experience and k nowledge of the in dividual, one c an choose from the simple dollar ti me averaging strategies to the complex day trading techniques. For the SAS programmers, our access to SAS allows us a s imple way to manage o ur little nest of golden eggs with some programming and modern portfolio theory. MODERN P ORTFOLIO T HEORY The mode rn portfolio theory began with a paper (Markowitz, 1952) and book (Markowitz, 1 959) written by Harry Markowitz. The fundamental premise of modern p ortfolio theory is that any stocks has a probability to go up or down depending on the market an d th us their inclusi on or exclusio n in a portfolio does no t matter individually. However, when they are placed together, the inte raction between the s tocks reduces the overall pric e volatility which then contributes to the stability of the portfolio. Any po rtfolio is designed with re turns in mind a nd with the modern portfolio theory, one can choose an expected return and then seek to minimize the risk (volatility) a ssociated with the combination of stocks. ASSUMPTIO NS There are several assumptions about the modern portfolio th eory which are listed below (less than the original one). 1. Returns from the portfolio is normally distributed (multivariate normality is assumed). 2. Correlations between the stocks are fixed or constant for a period of time. 3. The investors seek to maximize their overall profit/economic utility. 4. All players in the market are rational and risk adverse. 5. Common information is available to all players in the market. 6. All players are price takers. W hile many of the assumptions are objectionable, we will discuss the short comings of th e modern portfolio theory in later sections and how we can overcome it in theory and SAS. M A THEM A TICAL FORM ULATION Assume the following annotation,   = weight allocation to each individual stock i in the portfolio.   = return of each individual stock i in the portfolio.   = volatility of each individual stock i in the portfolio.   = correlation coefficient between stock i and stock j in the portfolio. Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 2 Expected Returns               Volatility of portfolio                                   OPTIMIZ A TION OF T HE PORT FOL IO One of the key p roblems faced by the modern po rtfolio theory i s the selection of the s tocks in a way to reduce th e volatility while maintain an acceptable level of returns. This problem is an optimization problem and c an be solved using qua dratic programming. Quadratic programming is an advanced form of l inear programming where th e linearity assumption has been relaxed as the problem formulation requires a quadratic c alculation. Bel ow is the mathe matical formulation of the problem. Objective: Minimize   Subjected to the following constraints: Constraint 1 (Returns constraint) :      󰇛 󰇜 Constraint 2 (Budget constraint):          The constraints are fairly obvious to the portfolio creator. For any investo rs, they have a limited bu dget in which they are willing to invest given a sufficient level of return that they will receive. By reducing the volatility, they can expect that the return will not fluctuate too wildly leading to unpredictable level of return. How ever, occa sionally, some extremely conservative investors wish to further limit their possible loss and add in a limiter constraint. Objective: Minimize   Subjected to the following constraints: Constraint 1 (Returns constraint):      󰇛 󰇜 Constraint 2 (Budget constraint):          Constraint 3 (Limit constraint):      󰇛    󰇜    In certain environment, certain stock positions a re n ot possible and thus that has to be factored in a s well. This is particularly the case during the financial crisis of 2008-2009 where shorting of stocks is barred in certain markets. Objective: Minimize   Subjected to the following constraints: Constraint 1 (Returns constraint):      󰇛 󰇜 Constraint 2 (Budget constraint):          Constraint 3 (Position constraint):      The optimization of the portfolio can be subjected to many different constraints that are depend ent on the environment that it operates in. The flexibility of the approach makes it extremely attractive to the any in vestors with Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 3 good access to excel spreadsheets. However, it is precisely this simplicity that causes trouble fo r the users of the technique. Most us ers who implement t he technique in excel spreadsheet tends to over restrict th e n umber of stocks available for analysis. This leads to a severe shortage of possible combination which can reduce th e overall vola tility or contribute to the return. At the same time, the information that can be contained i n an excel s preadsheet will be limited by the c omputational abilities of excel. T here are several programming languages adopted in p ortfolio management and creation. We will cover the approach using SAS. GETTING THE RIGHT IN FO RM A TION Information is the most critical section of the modeling. The classic phrase ‘garbage in, garbage out’ is the most relevant here. SAS ha s several data importing abilities th at allow it to process information from the internet i n an efficient manner. This is one very im portant use of SAS in data gathering es pecially when one needs to b uild a portfolio of stocks using information collated from many places. In this paper, I will demonstrate th e use of recursive code calling to extract data. One of the easi est places to obtain ma ssive amount of stock information is from Yahoo! Finance. Yahoo! Finance provides a fre e service where the stock t icks are com piled in the form of CSV text files which can be read in to SAS easily. However, manu ally obtaining information from Yahoo! Site will b e disastrous given the h uge number of stocks online. The fastest way to obtain all these information is to pla y around with the URL. Below is an example of a URL from Yahoo! for historical stock information for Apple. http://ichart.finan ce.yahoo. com/table.csv? s=AAPL&d=3 &e=16&f=20 11&g=d&a=7&b=14 &c=1986 &ignore=.csv The URL can be broken into its constituent portion. Below is the explanation. http://ichart.finan ce.yahoo. com/table.csv? s=&d=&e= &f= &g=d&a=7 &b =14&c=1986&ign ore=.csv To basic ally extract the CSV, we only ne ed to give th e stock ticket name and the date informa tion to reference the CSV files. However, as m entioned earlier, it is very important to be abl e to have the list of stock ticks to be able to call upon Yahoo! to provide the data. In this case, we a re very fortunate to have a website which provides stock t icks for NASDAQ. http://www.nasdaqtrader.com/dynamic/SymDir/nasdaqlisted.tx t This file contains all the stock ticks that can be found on NASDAQ. W e can import all these information using the codes below. FILENAME NASDQ URL 'http://www.nasdaqtrader.com/dynamic/SymDir/nasdaqlisted.txt' ; DATA NASDAQSTOCKLIST; INFILE NASDQ DELIMITER = '|' MISSOVER LRECL = 128 FIRSTOBS = 2 ; LENGTH SYMBOL $5. SECURITYNAME $55. MARKETCATEGORY $15. TESTISSUE $2. FINANCIALSTATUS $2. ROUND $2. ; INPUT SYMBOL $ SECURITYNAME $ MARKETCATEGORY ~ TESTISSUE $ FINANCIALSTATUS $ ROUND $; RUN ; Once the information has been c ollected, we can now atte mpt to a utomate the entire process of downloading and processing the hi storical stock inform ation for port folio creation. Automating the c all can be easily d one via a recursive call using the data set as the source of calling information. Using the combination of CALL EXECUTE and data, we can automate the process easily. DATA _NULL_ ; SET NASDAQSTOCKLIST; CALL EXECUTE( "filename " ||Symbol|| " url '" || 'http://ichart.finance.yahoo.com/table.csv?s=' ||compress(Symbol)|| '&d=3&e=16&f=2011&g=d&a=7&b =14&c=1986&ignore=.csv' || "' DEBUG;" ); CALL EXECUTE( "data " ||Symbol|| ";" ); CALL EXECUTE( "infile " ||Symbol|| " dsd lrecl = 128 firstobs = 2;" ); CALL EXECUTE( "informat Date yymmdd10.;" ); CALL EXECUTE( "input Date Open High Low Close Volume AdjClose;" ); CALL EXECUTE( "format Date yymmdd10.;" ); CALL EXECUTE( "RUN;" ); RUN ; However, du e to the a mount of information being downloaded, this will take some time to fi nish even with a high band width internet service. Once all the stock i nformation has been download ed, we can then process the data and Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 4 prepare it for fu rther analysis . Fo r the sake of simplicity, we will be mo deling using the closing price for e ach stock at the end o f each day. W ith so many data sets in the library, one will s eek a simple way to combine the files together. One simple trick i s to use PROC CONTENTS with th e _ALL_ keyword to list all the data tables in the lib rary fo r management. PROC SQL ; DROP TABLE NASDAQSTOCKLIST; QUIT ; PROC CONTENTS DATA = WORK._ALL_ OUT = CONTENTS NOPRINT ; RUN ; PROC SORT DATA = CONTENTS(WHERE = (NOBS > 1000 ) KEEP = MEMNAME NOBS) NODUPKEY ; BY MEMNAME; RUN ; DATA _NULL_ ; SET CONTENTS; CALL EXECUTE( "PROC SORT DATA = " ||MEMNAME|| "(KEEP = DATE CLOSE);" ); CALL EXECUTE( "BY DATE;RUN;" ); RUN ; DATA _NULL_ ; SET CONTENTS END = EOF; IF _N_ = 1 THEN CALL Execute( "DATA OVERALL;MERGE" ); CALL EXECUTE(MEMNAME|| "(RENAME = (CLOSE = " ||MEMNAME|| "))" ); IF EOF THEN DO ; CALL EXECUTE( ";BY DATE;RUN;" ); END ; RUN ; Using the output from PROC CONTENTS, one can easily make use of the CALL EXECUTE ability to generate all the codes needed to run the process. The CALL EXECUT E com bo allows for a m assive amount of reduction of coding work needed. With the data prepared, we can now move on to the next step of modeling. CORREL A TION M A TRIX ESTIM A TION Estimation of the correlation matrix is one o f th e key steps to po rtfolio creation. The mo dern portfolio th eory uses the correlation matrix to calculate the po rtfolio variance. The c reation of correlation m atrix is extremely s imple in SAS. PROC CORR is the c lassic procedure to generate such a matrix. However, one could also use the covariance matrix to c alculate the p ortfolio variance. PROC CORR provides th e option to d o either on e and in this paper, for less computational work, I wi ll be using the covariance directly. PROC CORR DATA = OUT = CORRTABLE (WHERE=(UPCASE(_TYPE_) IN ( "COV" , "MEAN" ))) COV NOSIMPLE NOPRINT ; VAR ; WITH ; RUN ; At the same ti me, PROC CORR generate the means of the stocks as well which is needed as an input to the model. To us e all the information available, we can break the table into its constituent component which can then b e fed into the models. DATA COVTABLE MEANTABLE; SET CORRTABLE; IF UPCASE(_TYPE_) IN ( "MEAN" ) THEN OUTPUT MEANTABLE; ELSE OUTPUT COVTABLE; RUN ; Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 5 OPTIMIZING THE PORTFOL IO W it h the informa tion in place, we can start looking at optimizing the portfolio. The optimization of the portfolio involves the use of quadratic optimization. As this is a basic introduction to portfolio creation, we will stic k to the following assumptions and constraints. Objective: Minimize   Subjected to the following constraints: Constraint 1 (Returns constraint):      󰇛 󰇜 Constraint 2 (Budget constraint):          Basically in this s cenario, we are assuming limited budget with an expected return i n which we can have both long and short positio ns . W hile there are many different scenarios th at might occur, this is subjected to the i ndividual’s preferences a bout his investment. PROC OP TMODEL allows one to easily m anage the constraints as it in corporate the various types of optimization algorithm that are used given certain requirements are met. The s yntax is as follow. 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] = &BUDGET; CON GROWTH: SUM{I IN 1.. &VARCOUNT}R[I]*X[I] >= &RETURN_VALUE; %IF &TYPE ^= S %THEN %DO ; SOLVE WITH QP; PRINT X; %END ; %ELSE %DO ; FOR {I IN 1.. &VARCOUNT} X[I] .L B= - X[I] .U B; SOLVE WITH QP; %END ; PRINT X; QUIT ; As one will notice that the syntax is do ne up with macro variables in many p laces, the main driver for this is the need of a macro that calculate the weights in the portfolio automatically by feeding it w ith the data of stocks information. Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 6 PUTT ING THESE TOGETH ER A SAS Macro has been developed to achieve this. You can find it in the appendix A. CONCLUSION Portfolio creation is a very simple task in SAS given the abilit ies of SAS to access web information as well as excellent optimization routines. REFERE NCE Markowitz, H.M. (March 1952). "Portfolio Selection". The Journal of Finance 7 (1): 77 – 91. doi : 10.2307/2975974 . JSTOR 2975974 . Markowitz, H.M. (1959). Portfolio Selection: Efficient Diversification of Investments . New York: John Wiley & Sons. (reprinted by Yale University Press, 1970, ISBN 978-0- 300 -01372-6 ; 2 nd ed. Basil Blackwell, 1991, ISBN 978-1- 55786 - 108 -5 ) CONTA CT INFORMAT ION Your comments and questions are valued and encouraged. Contact the author at: Name: Murphy Choy Enterprise: School of Information Systems, Singapore M anagement University Address : 80 Stamford Road City, State ZIP : Singapore 178902 W or k Phone: +65-92384058 E-mail: goladin@gmail.com/murphychoy@smu.edu.sg A CKNOWLEDGEMENT SAS and all other SAS Institute Inc. product or service names are registered tr ademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies. Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 7 Appendix A /*************************************************************************** MODERN PORTFOLIO THEORY USING SAS/OR ****************************************************************************/ LIBNAME DATA 'H: \ DOCUMENTS \ SAS PAPERS \ MODERN PORTFOLIO SAS OR' ; /*************************************************************************** ****************************************************************************/ %MACRO MPT_OPT(FILE,BUDGET,RETURN_VALUE,TYPE); /*EXTRACTION OF CONTENTS*/ PROC CONTENTS DATA = &FILE(DROP = 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*/ Finance NESUG 2011 Modern Portfolio Theory using SAS OR,continued 8 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] = &BUDGET; CON GROWTH: SUM{I IN 1.. &VARCOUNT}R[I]*X[I] >= &RETURN_VALUE; %IF &TYPE ^= S %THEN %DO ; SOLVE WITH QP; PRINT X; %END ; %ELSE %DO ; FOR {I IN 1.. &VARCOUNT} X[I] .L B= - X[I] .U B; SOLVE WITH QP; %END ; PRINT X; QUIT; /**/ %MEND ; Finance NESUG 2011