Parallelizing Large Excel-Based Calculations on Windows HPC Server & Azure

1. Parallelizing Large Excel-Based Calculations on Windows HPC Server & Azure

2. Please silence cell phones

3. Computational Finance Computational Finance emphasizes practical numerical methods rather than mathematical proofs and focuses on techniques that apply directly to economic analyses.

4. Quantitative Analytics Many problems in mathematical finance entail the computation of a particular integral. In many cases these integrals can be valued analytically, and in still more cases they can be valued using numerical integration, or computed using a PDE. However when the number of dimensions (or degrees of freedom) in the problem is large, PDEs and numerical integrals become intractable, and in these cases Monte Carlo Methods often give better results. Black-Scholes Formula Equity Options

5. Monte Carlo Methods • Any method which solves a problem by generating suitable random numbers and observing that fraction of the numbers obeying some property or properties. The method is useful for obtaining numerical solutions to problems which are too complicated to solve analytically. • Generate a set of (pseudo) random numbers in a known domain • Check whether each point is within the Area • Estimate the Area as a subset of the known domain • The higher the number of samples the more precise the calculation

6. Microsoft Excel Computational Environment Add-ins XLLs Data Sources Data Feeds RTDs VBA Application.ScreenUpdating = False Calculate Range(Cells(36 + 5 * (k - 1), 22), Cells(40 + 5 * (k - 1), 60)).Select Selection.Copy Range("V26").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks Application.ScreenUpdating = True

7. HPC – Deployment Choices On-premise HPC Head Node Desktop Compute Cloud via Idle Win 7/8 Workstation Cores Desktop User Broker Node(s) HPC Cluster Spreadsheet Azure Compute Proxies Azure Compute Instances

8. Traditional HPC environment Dedicated compute nodes Workstation nodes (CoW) & Server Scavenging Supports specialized hardware (Infiniband, GPU, etc.) On-Premise On-premise head node and broker nodes Administration- deployment, monitoring, diagnostics, & reporting Scheduler – FCFS, Balanced, Pools, Preemption Runtimes - Parametric sweep, MPI, SOA 2008 R2

9. Hybrid HPC – Burst to Azure Combine power of on-premise compute resources with scale-out capability of Azure. Cluster burst to Azure – Add cloud resources as necessary Worker Roles VMs On-premise head node & broker nodes Full Admin and Scheduler capabilities Work (jobs/tasks) pushed to Azure nodes when available Workloads: parametric sweep, MPI, SOA 2008 R2 SP1/SP2

10. HPC Hosted in Azure (HPC Scheduler on Azure) Application hosted entirely in Azure No on-premise Head Node Application is accessed from Rich client on premise Portal Web Application Multiple business models Packaged application is sold to a customer Application is available as a service in the cloud Scheduler and Runtimes Supported in Azure Workloads: Parametric Sweep, SOA, MPI No Head Node (Scheduler Service, no Admin Services)

11. Offloading Excel Calculations on a Cluster Compute Nodes Request Session Head Node Broker Assignment Brokers TaskAssignment Task Submission Results Spreadsheet 11 Results

12. Excel Components Network Share Excel 2010 Excel Workbook HPC/Excel Add-in Macros Network Resources VBA Driver WCF Broker Node(s) Excel 2010 HPC/Excel Client Excel Driver Excel Driver .NET / COM HPC/Excel Service Applications CLI Desktop Client Windows HPC Cluster

13. Overview Save temporary copy of workbook Start Excel and Load Workbook HPC_Initialize HPC_Partition (Loop) HPC_Execute HPC_Execute HPC_Execute HPC_Merge Desktop HPC Cluster

14. OverviewPorting a workbook for the cluster (1) Identify Iterative Calculation Usually a loop or similar construct Identify Business Logic Business logic should be unchanged Re-use code for desktop, cluster calculation Identify Startup Code Usually some pre-run or startup requirements

15. OverviewPorting a workbook for the cluster (2) Add Client "Control" Code Add Skeleton Macros Convert Desktop Code to Cluster Functions Add Local Test Functions Consolidate Desktop and Cluster Code

16. Original VBA CodeIterative loop calculation • FunctionRunModel() • ' set up variables • NumIterations = Range("C8").Value • ' run calculation in a loop • For n = 1 ToNumIterations • rslt = CalculateSingleIteration(n) • ConsolidateResultsrslt • Next n • ' complete • callUpdateCharts • End Function

17. Modified VBA CodeBusiness logic is moved, but unchanged • FunctionHPC_Execute( data As Variant ) As Variant • ' run single iteration • HPC_Execute = CalculateSingleIteration(data) • End Function • FunctionHPC_Merge( data As Variant ) • ' insert results into workbook • ConsolidateResults data • End Function

18. Demo Platinum Sponsor Diamond Sponsor

19. Win a Microsoft Surface Pro! Complete an online SESSION EVALUATION to be entered into the draw. Draw closes April 12, 11:59pm CTWinners will be announced on the PASS BA Conference website and on Twitter. Go to passbaconference.com/evalsor follow the QR code link displayed on session signage throughout the conference venue. Your feedback is important and valuable. All feedback will be used to improve and select sessions for future events.

20. Thank you! Platinum Sponsor Diamond Sponsor