Macros to estimate value at risk

Value-at-risk (VaR) measures the risk of loss on a specific portfolio of financial asset. Jon Danielsson introduced how to apply the nonparametric(historic simulation) and parametric methods to estimate univariate and multivariate VaRs [Ref. 1]. And the simplest parametric ways are probably to imagine the daily returns as a normal distribution (or t-distribution) and therefore find the locations by probability. Then following his steps, for a single asset portfolio, I used Google’s adjusted returns since 2006; for a two-asset account, I used Google and Apple with a 0.3:0.7 weight ratio. The probabilities are both 1% and the money value sets at $1000. For the example below, parameters of the t-distribution were inferred by a routine of Proc NLMIXED in the first macro for univariate VaR [Ref. 2]; in the second macro, variance-covariance matrix was obtained again by the Cov() function of Proc IML for bi-variate VaRs.

SAS actually has two components for matrix computation: Proc IML and Proc FCMP. My personal experience is:
-- if code porting from VBA to SAS is needed, Proc FCMP should be the No.1 choice, because Proc FCMP is exactly built on the logic of EXCEL/VBA;
-- if code porting from Matlab or R to SAS is needed, Proc IML should be the preference, because they speak the same language.

Generally, Proc IML is more efficient than Proc FCMP for either programmer or machine (I was driven crazy by the nested loops of Proc FCMP). Of course, SAS/IML has a higher learning curve. For those who are willing to learn SAS/IML, Rick Wicklin’s new book is classical [Ref. 3]. I learned a lot by reading the free 2nd chapter online and the author’s blog. And I ordered one from Amazon and am waiting for it. Hope with it, I can delve into SAS/IML more.

References:
1. Jon Danielsson. ‘Financial Risk Forecasting’. Wiley, 2011
2. Steven Gilbert and Ling Chen. ‘Using SAS Proc NLMIXED for Robust Regression’. SAS Global 2007
3. Rick Wicklin. ’ Statistical Programming with SAS/IML Software’. SAS Publishing, 2010

/*******************READ ME*********************************************
* -  Macros to estimate value at risk  -
*
* SAS VERSION:    9.2.2
* DATE:           17jun2011
* AUTHOR:         hchao8@gmail.com
*
****************END OF READ ME******************************************/

****************(1) MODULE-BUILDING STEP********************************;
%macro univar(data = , var = , p = , value = , filepath = );
   proc sort data = &data out = _tmp01;
      by &var;
   run; 
   data _tmp02;
      set _tmp01 nobs = nobs;
      if _n_ = round(%sysevalf(&p)* nobs);
   run;
      
   ods select none;
   proc nlmixed data = _tmp01;
      parms mu 0 sigma2 1 dft 1;
      y = &var;
      pi = arcos(-1);
      z = (y-mu)/sqrt(sigma2);
      logl = lgamma(.5*(dft+1))-.5*log(sigma2)-.5*log(dft*pi)-lgamma(dft/2)
            -( (dft+1)/2 )* log(1+ (1/dft)*z**2);
      model y ~ general(logl);
      ods output ParameterEstimates = _tmp03;
   run;

   proc sql;   
      select std(&var) into: std from _tmp01;
      select estimate  into :dft from _tmp03 where parameter = 'dft';
      select estimate  into :sigma2 from _tmp03 where parameter = 'sigma2';
   quit;
   ods select all;

   data _tmp04;
      length var_desc $30;
      set _tmp02; var_value = - &var * &value; var_desc = 'Historical simulation VaR'; drop &var date;
      output;
      var_value = - &std * probit(&p) * &value; var_desc = 'Normal VaR'; 
      output;
      var_value = -sqrt(&sigma2) * tinv(&p, &dft) * &value; var_desc = 't-distribution VaR'; 
      output;
   run;

   ods html  file = "&filepath\&data..xls" gpath = "&filepath\" style = money;
   title; footnote;
   proc report data = _tmp04 nowd;
      col var_desc var_value ;
      define  var_desc /  'VaR type' style(column) = [foreground=lime just=center];
      define var_value / 'Value' format = dollar8.2 style(column) = [font_weight=bold] ;
   run;
   proc sgplot data = &data;
        histogram &var;
        density &var;
        density &var / type = kernel;
   run;
   ods html close;
%mend univar;

%macro mulvar(data =, var1 =, var2 =, p =, value =,  firstweight =, filepath = );
   %let secondweight =  %sysevalf(1 - &firstweight) ;
   data _tmp01;
      merge &data;
      by date;
      sum = &var1*&firstweight + &var2*&secondweight;
      if missing(&var1) + missing(&var2) > 0 then delete; 
   run;

   proc sort data = _tmp01 out = _tmp02;
      by sum;
   run; 
   data _tmp02;
      set _tmp02 nobs = nobs;
      if _n_ = round(%sysevalf(&p)* nobs);
   run;

   proc iml;
      start Cov(A);            
         n = nrow(A);         
         C = A - A[:,];       
         return( (C` * C) / (n-1) );
      finish;
      use _tmp01;
      read all var{&var1 &var2};
      y = &var1 || &var2;
      w = {&firstweight , &secondweight};
      sigma = sqrt(t(w)* Cov(y)* w);
      call symput('sigma', left(char(sigma)));
   quit;

   data _tmp03;
      length var_desc $30;
      set _tmp02; var_value = - sum * &value; var_desc = 'Historical simulation VaR'; keep var_:;
      output;
      var_value = - &sigma * probit(&p) * &value; var_desc = 'Normal VaR'; 
      output;
   run;

   ods html  file = "&filepath\%sysfunc(compress(&data)).xls" gpath = "&filepath\" style = money;
   title; footnote;
   proc report data = _tmp03 nowd;
      col var_desc var_value ;
      define  var_desc /  'VaR type' style(column) = [foreground=lime just=center];
      define var_value / 'Value' format = dollar8.2 style(column) = [font_weight=bold] ;
   run;
   ods graphics on;
   ods select ContourPlot BivariateHistogram;
   proc kde data = _tmp01; 
      bivar &var1 &var2 / plots= all;
   run;
   ods graphics off;
   ods html close;
%mend mulvar;

****************(2) TESTING STEP****************************************;
%getr(startday = '01jan2006'd, squote = goog, filepath = c:\tmp, 
      rpath = c:\Program Files\R\R-2.13.0\bin);
%getr(startday = '01jan2006'd, squote = aapl, filepath = c:\tmp, 
      rpath = c:\Program Files\R\R-2.13.0\bin);
%univar(data = goog, var = goog_r, p = 0.01, value = 1000, filepath = c:\tmp );
%mulvar(data = goog aapl, value = 1000,  p = 0.01, var1 = goog_r, var2 = aapl_r, 
      firstweight = 0.3, filepath = c:\tmp);
      
****************END OF ALL CODING***************************************;