Portfolio Overview Reports Flow Charts

Portfolio Overview ReportsFlow Charts Rev 20110912 by Daniel Brockman Union Bank

Intro • Residential (Resi) Portfolio Overview Reports • Pro – Product Segments • Reg – Regional Segments • Tra – Special Segments • HELOC & Other Consumer (HNC) Portfolio Overview Reports • HNCEqu – Equity line of credit Segments • HNCO – Other Consumer loan Segments • This presentation shows control flow for ResiReg • Other reports have similar flow Union Bank

Automated Execution Go autoPortRpt_RES.bat autoPortRptSub_RES.bat autoPortRptSub_RES.log A END Union Bank

autoPortRptSub_RES.bat autoPortRptSub_RES.bat Principal input A autogosas portfrpt3 ResiReg Run SAS Raw output B ResiReg_rpt.xls autoPortRptFmt ResiReg_rpt.xls Format C Formatted output Resi_Regions_yyyymm.xls Rename Outputs rptcopy.bat D RETURN Union Bank

autogosas.bat Run portfrpt3.SAS with SYSPARM=ResiReg autogosas portfrpt3 ResiReg B Principal input Residential_tss_yyyymm %surfcity(): I.D. dates, inputs, outputs, perform process. %surfcity() E RETURN Raw output Chargeoffs Rptcopy.bat ResiReg_rpt.xls (autoPortRptSub) Union Bank

%surfcity(), part 1 %surfcity() E Get dates fryyyymm, toyyyymm %movector() I.D. Input data set %Choosedsnroot() F Union Bank

%surfcity(), part 2 Rptcopy.bat F %copybat() Principal input Process inputs, produce outputs %promonth() Residential_tss_yyyymm G ResiReg_rpt.xls Chargeoffs RETURN Union Bank

%promonth(), part 1 %promonth() Input for yyyymm G %genyyyymm()generate next yyyymm(the oldest component of the reporting programs) Data alpha H 2 Loops: Segments (tabs) & Dates I n n alpha Last Segment Last yyyymm y y Chargeoffs RETURN Union Bank

%promonth(), part 2 %promonth() G H Yyyy mm Seg I alpha Data vartopone Chargeoffs I1 J %Resimtx() %Resimtx() calculates metrics RETURN I2 Union Bank

%promonth(), part 3 %promonth() H G Yyyy mm Seg I Proc transpose J vartopone &all_d Proc append Proc export aggvartop ResiReg_rpt.xls RETURN Raw output Union Bank

autoPortRptFmt Formatting autoPortRptFmt ResiReg_rpt.xls C Kill any existing excel process Taskkill /f /t /im excel.exe Raw output ResiReg_rpt.xls autoResidRpt.xlsmautostarts Fbin.xlsm formatting macros autoResidRpt.xlsm Formatted output Fbin.xlsm Resi_Regions_yyyymm.xls K RETURN (autoPortRptSub) Union Bank

Fbin.xlsm Raw output Fbin.xlsm ResiReg_rpt.xls K RunResidRpt() L Formatted output Resi_Regions_yyyymm.xls RETURN (autoPortRptFmt) Union Bank

RunResidRpt() RunResidRpt() Formatting the output L Resi_Line_Definitions.xlsm ResidRptGo() RT=“ResiReg” RT=ReportType() Get list of Tabs SegTags=TabList() Format one Segment (tab) n Last Segment RETURN y PortResidPageFmt (Fbin.xlsm) M Union Bank

PortResidPageFmt(), part 1 PortResidPageFmt Format one Segment (tab) M Key = Cell in Col B See t_proofing? Insert Column A y n HIGH LOOP Last Row Skip=linefmt() Delete Line n Leave HIGH LOOP P n y Skip>0? Proof=1 Skip<0? y y n Insert blank line Q Union Bank

Linefmt() Skip=linefmt() Format one Line M Skip=0 Stylo= title, %, $, etc. Line-skip signal Skip = skipper() Stylo=numstilo() Apply format per Stylo& Key RETURN Handle special cases Union Bank

PortResidPageFmt(), part 2 PortResidPageFmt Format one Segment (tab) M Col A special formatting and numbering HIGH LOOP Leave HIGH LOOP Q Cols A & B Last Row Col B row label =thesaur() n y R Union Bank

PortResidPageFmt(), part 3 PortResidPageFmt Format one Segment (tab) M Cols A & B HIGH LOOP Leave HIGH LOOP R Q Last Row n y Go to Next proofing row Set print area AGONY LOOP Skip or Delete row RETURN Skip=linefmt() P (RunResidRpt()) Union Bank

The End Union Bank

Portfolio Overview Reports Flow Charts