A scorecard for probability of default with sparkline

In the 1st chapter of their must-read credit risk modeling book, Gunter and Peter used the ratios of working capital(WC), retained earnings(RE), earnings before interest and taxes(EBIT), sales(S) and market value of equity(ME) over either total liabilities (TL) or total assets(TA), to build a logit default risk model for 4000 records by 791 firms through 10 years [Ref.1]. The authors implemented VBA’s user-defined functions in Excel to realize the modeling and scoring procedures. In SAS, Proc Logistic does the same job.

Macros in SAS and Excel can be used together. Excel2010’ new Sparkline functions visualize the fluctuations of values in many rows. For those firms which eventually didn’t default in Gunter and Peter’s example, Sparkline can help observe the changing pattern of default risk for specific firms, such as which year the firms’ maximum default risk occur. Collin and Eli disclosed some definitions to translate a SAS dataset to an Excel table [Ref. 2]. Thus, by using their method, a SAS macro that generates VBA code will automate this process for a default risk scorecard with Sparkline.

References:
1. Gunter Löeffler and Peter Posch. ‘Credit Risk Modeling using Excel and VBA’. The 2nd edition. Wiley.
2. Collin Elliot and Eli Morris. ‘Excel lent SAS Formulas: The Creation and Export of Excel Formulas Using SAS’ . SAS Global 2009.

/*******************READ ME*********************************************
* - A scorecard for probability of default(PD) with sparkline -
*
* SAS VERSION:    9.1.3
* EXCEL VERSION:  2010
* DATE:           31may2011
* AUTHOR:         hchao8@gmail.com
*
****************END OF READ ME******************************************/

%macro splscd(data = , path = , filename = );
   /*****************************************************************
   *  MACRO:      splscd()
   *  GOAL:       create xls and vba for a scorecard with sparkline
   *  PARAMETERS: data     = dataset for modeling and scoring
   *              path     = output path
   *              filename = name for scorecard
   *****************************************************************/
   options mprint mlogic;
   data _excelCol(where = (start le 256));  
      length label $2;  
      do c1 = -1 to 25;  
         do c2 = 0 to 25;  
            alpha1 = byte(rank('A')+ c1);  
            alpha2 = byte(rank('A')+ c2);  
            label = compress(alpha1||alpha2, " @");  
            start + 1;  
            fmtName = "column";  
            output;  
         end;  
      end;  
   run;

   proc format cntlin = _excelCol; 
   run; 

   proc logistic data = &data;
      model default = WCoverTA REoverTA EBIToverTA MEoverTL SoverTA;
      score data = &data out = _scored;
   run;

   data _tmp01;
      set _scored;
      where default = 0;
      keep id year p_1;
   run;

   proc sort data = _tmp01;
      by id year;
   run;

   proc transpose data = _tmp01 out = _tmp02(keep = id year:) prefix = year;
      by id;
      id year;
      var p_1;
   run;

   ods listing close;
   ods output variables = _vartab;
   proc contents data = _tmp02; 
   run;

   proc sql;
      select variable into: varlist separated by ' '
      from _vartab
      order by substr(variable, 5, 4)
      ;
      select count(*) format = column. into: col_num
      from _vartab
      ;
      select count(*)+1 format = column. into: spl_col
      from _vartab
      ;
   quit;

   data _tmp03;
      retain &varlist;
      set _tmp02 nobs = nobs;
      sparkline =.;
      call symput('nobs_plus', nobs + 1);
   run;

   ods html file = "&path\&filename..xls" style = minimal;
   option missing = ''; 
   title; footnote;
   proc print data = _tmp03 noobs;
   run;
   ods html close;

   proc sql;
      create table _tmp04 (string char(200));
      insert into _tmp04
      values("Sub sas2vba()")
      values("''''''''CREATE SPARKLINE'''''''''")
      values('Columns("spl_col:spl_col").ColumnWidth=30')
      values("Dim mySG As SparklineGroup")
      values('Set mySG = _ ')
      values('Range("$spl_col$2:$spl_col$nobs_plus").SparklineGroups.Add(Type:=xlSparkColumn, SourceData:="B2:col_numnobs_plus")')
      values('mySG.SeriesColor.ThemeColor=6')
      values("mySG.Points.Highpoint.Visible=True")
      values("''''''''FORMAT THE TABLE'''''''''")
      values('ActiveSheet.ListObjects.Add(xlSrcRange,Range("$A$1:$spl_col$nobs_plus"), , xlYes).Name="myTab"')
      values('Range("myTab[#All]").Select')
      values('ActiveSheet.ListObjects("myTab").TableStyle="TableStyleMedium1"')
      values("''''''''SAVE AS EXCEL2010 FORMAT'''''''''")
      values('ChDir "sas_path"')
      values('ActiveWorkbook.SaveAs Filename:="sas_path\excel_file.xlsx",FileFormat:=xlOpenXMLWorkbook,CreateBackup:=False')
      values("End Sub")
      ;
   quit;

   data _tmp05;
      set _tmp04;
      string = tranwrd(string, "spl_col", "&spl_col");
      string = tranwrd(string, "nobs_plus", "&nobs_plus");
      string = tranwrd(string, "col_num", "&col_num");
      string = tranwrd(string, "sas_path", "&path");
      string = tranwrd(string, "excel_file", "&filename");
      if _n_ in (3, 6, 10) then string = compress(string, ' ');
   run;

   data _null_;
      set _tmp05;
      file "&path\&filename..bas";
      put string;
   run;

   proc datasets nolist;
      delete _:;
   quit;
%mend splscd;

%splscd(data = test1, path = c:\tmp, filename = scorecard);