Slide 1: Implementing Legacy Statistical Algorithms in a Spreadsheet Environment Stephen W. Liddle Information Systems Faculty Rollins eBusiness Center John S. Lawson Department of Statistics Brigham Young University Provo, UT 84602
Slide 2: Overview      Introduction Fundamentals of VBA in Excel Retargeting traditional algorithms to a spreadsheet environment Converting FORTRAN to VBA Conclusions
Slide 3: Why Convert FORTRAN Programs to Run in a Spreadsheet Environment?    Useful code available that is not implemented in standard statistical packages FORTRAN compilers not usually available on normal Windows workstation Many textbooks refer to published FORTRAN algorithms
Slide 4: Sources for Published FORTRAN Algorithms       STATLIB (http://lib.stat.cmu.edu/) General Archive Applied Statistics Archive Journal of Quality Technology Archive JASA Software Archive JCGS Archive
Slide 5: Advantages of Running Legacy FORTRAN Code in Excel    Comfortable environment for practitioners More user friendly input from spreadsheet Output to spreadsheet allows further graphical and computational analysis of results with Excel functions
Slide 8: VDG Inputs: Nickname: 1-hybrid Runs: 30 Factors: 6 Model Order(1/2): 2 Design Region(S/C): s Weight by N (Y/N): y Number of Radii (20-99): 20 Scaling Unit (suggest 1): 1 Design Radius/Region Radius: 1 Run VDG Design X1 0 -1 1 1 -1 1 -1 -1 1 1 -1 -1 1 -1 1 1 -1 2 -2 0 X2 0 -1 1 -1 1 -1 1 -1 1 -1 1 -1 1 -1 1 -1 1 0 0 2 X3 0 -1 -1 1 1 -1 -1 1 1 -1 -1 1 1 -1 -1 1 1 0 0 0 X4 0 -1 -1 -1 -1 1 1 1 1 -1 -1 -1 -1 1 1 1 1 0 0 0 X5 0 -1 -1 -1 -1 -1 -1 -1 -1 1 1 1 1 1 1 1 1 0 0 0 X6 2.3094 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 0.57735 -1.1547 -1.1547 -1.1547
Slide 14: Proposed Methodology     Understand original FORTRAN program Choose suitable I/O methods Convert original FORTRAN code to VBA Test and use resulting Excel code
Slide 15: Visual Basic For Applications    Built on ANSI BASIC Language engine of Microsoft Office Modern structured programming language   Has vast array of types, functions, programming helps Powerful support environment (Office platform)  Popular in business contexts
Slide 16: Excel Object Model   Objects in Excel are addressable in VBA Each object has:   Application Workbooks (Workbook) Worksheets (Worksheet) Properties Methods Range Chart
Slide 17: Input/Output Methods  Non-interactive   Output Region Files, databases Worksheet cells Message boxes Input boxes Custom GUI forms Input Region Clicking these buttons runs the ORPS1 and ORPS2 algorithms.  Interactive   
Slide 18: FORTRAN vs. VBA  b  b  4ac 2a   2 VBA: (-b+Sqr (b^ 2-4*a*c))/(2*a) FORTRAN: (-b+SQRT(b**2-4*a*c))/(2*a)
Slide 19: More Operators .EQ.  .NE.  .LT.  .LE.  .GT.  .GE.  = <> < <= > >= .AND. And  .OR. Or  .NOT. Not   // &
Slide 20: Data Types        INTEGER REAL DOUBLE PRECISION COMPLEX LOGICAL CHARACTER CHARACTER*length Other notable VBA types:  Byte, Integer, Long Single Double Non-primitive in VBA Boolean String String*length  Currency, Decimal, Date, Variant
Slide 21: Worksheet Functions        ChiDist(x,deg_freedom)  Returns one-tailed probability of the χ2 distribution. Returns the correlation coefficient of two cell ranges. Returns the Fisher transformation at a given x. Returns the Pearson product moment correlation coefficient for two sets. Returns the requested quartile of a data set. Returns the standard deviation of a data set. Returns the two-tailed P-value of a z-test. Correl(array1,array2)  Fisher(x)  Pearson(array1,array2)  Quartile(array,quart)  StDev(array)  ZTest(array,x,sigma) 
Slide 22: Flow-Control Statements FORTRAN Logical if IF (expr) stmt Block if IF (expr1) THEN stmt1 ELSE IF (expr2) THEN stmt2 … ELSE stmtn END IF VBA If expr T hen stmt If expr1 Then stmt1 ElseIf expr2 Then stmt2 … Else stmtn EndIf
Slide 23: Subtle Differences (“Gotchas”)  Implicit conversion of real to integer values    FORTRAN: truncate VBA: round Solution: use VBA’s Fix(), which truncates This introduces ambiguity Solution: supply explicit types everywhere  Both languages allow implicit typing  
Slide 24: Eliminating Goto Statements   Computer science accepts the axiom that goto is generally “considered harmful” We advocate rewriting alogrithms to use structured programming techniques where feasible   Sine qua non is “make it work” It’s a good idea for maintainability, understandability to move to structured form
Slide 25: Eliminating Goto Statements 6 DO 8 J=1,3 ... ... IF(OBJFN.GT.BESTFN) GO TO 7 ... GO TO 6 IF(J.EQ.3) GO TO 8 XK=BESTK-STEP CONTINUE 7 8
Slide 26: Eliminating Goto Statements For j=1 To 3 ... 6 ... IF(OBJFN.GT.BESTFN) GO TO 7 ... GO TO 6 7 IF(J.EQ.3) GO TO 8 XK=BESTK-STEP 8 Next j
Slide 27: Eliminating Goto Statements For j=1 To 3 ... 6 ... IF(OBJFN.GT.BESTFN) GO TO 7 ... GO TO 6 7 If j <> 3 Then xk = bestk - step End If Next j
Slide 28: Eliminating Goto Statements For j=1 To 3 ... Do Until objfn > bestfn ... Loop If j <> 3 Then xk = bestk - step End If Next j
Slide 29: Our Reasoning     Digital assets are fragile FORTRAN is not universally available Excel is a ubiquitous, powerful platform VBA is a full-featured language capable of handling sophisticated statistical computations
Slide 30: Conclusions   We recommend creating a Web-based repository of Excel/VBA implementations of classic statistical algorithms We can preserve our legacy algorithms in this modern spreadsheet environment E-mail us if you want a copy of our manuscript (liddle or lawson@byu.edu) 