As innovation in finance develops and transactions become more complex they require a level of analysis that surpasses the ability of a standard spreadsheet program and necessitate the implementation of computer programming. The most effective way of harnessing the power of programming for finance is to learn Visual Basic Applications (VBA) within the context of relevant problems. This course will take participants from the basics of working in VBA’s integrated development environment to ultimately writing intermediate level code that can be used to amortize thousands of loans or run powerful simulations

Introduction to Object Oriented Programming (OOP)

I. The Basics of OOP Programming: Understanding Objects, Properties and Methods

Exercise: Creating Examples

II. Introduction to the Visual Basic Editor(VBE)

a. Working in Excel’s Integrated Development Environment

Exercise: Navigating the IDE

III. Functions and Subroutines

Exercise: An Example of Each

IV. Code Writing Conventions

Exercise: Clean-up te Code

V. Main Windows: project,Propertiess,Code and Watch

VI. The Object Library

Exercise: Find the Object

VII. Using the Macro recorder

Subroutines: Commanding Excel

I. Creating a subroutine

Exercise: Referencing and Moving Data

II. Variables

Exercise: My First Variable

III. Reading Data from Excel into VBA and Back

Exercise: Moving Data

V. The Offset Property

Exercise: Creating a Loop and Using Variables

VI. IF THEN ELSE statements

Exercise: Integrating If Then Statements into the Code

VII. Do While and Do Until Loops

Exercise: Trying Out Do While and Do Until Loops

Working with Ranges of Data

I. VBA Arrays: Declaration and Logic
II. Reading Ranges from Excel into VBA and Back

Exercise: My First Variable

III. Writing Arrays from VBA into Excel

Exercise: Creating a Subroutine with an Array

Using a Subroutine to Control Scenario Generation

I. Planning a Complex VBA Subroutine
II. Using Multiple Subroutines to Accomplish a Task
III. Setting up a Discounted Cash Flow Model for Multiple Scenarios

Exercise: Reading in All Necessary Information

Exercise: Controlling a Model through Code

Financial Calculations in VBA: Debt

I. Efficiency Techniques : Speeding Up Subroutine Calculations
II. Exporting Large Date Sets
III. Creating Dynamic Arrays in Excel for VBA use
IV. Nested Looping Through Arrays
V. Aggregating Looped Data

Exercise: Debt and Pooled Debt Calculations in Code

Optimization Using Excel and VBA

I. A Template for Automating Goal Seek & Solver

Exercise: Automating Goal Seek

II. Integrating Optimization in a Financial Model
III. Using Optimization to Determine a Company’s Most Efficient Capital Structure

Weighted Average Cost of Capital Optimization

Monte Carlo Simulation

I. Principles of Simulation

Exercise: A Coin Toss Example

II. Random number Generation

Exercise: Generating Pseudorandom Numbers in Excel and VBA

III. Tracking simulation results

Exercise: A Default Example

Automating Common Administrative Tasks in Finance

I. Automatically Generating Output Reports
II. Exporting Output Reports

Exercise: Building in Output Reporting

Functions: Returning Value

I. Creating a User Defined Functions (UDF)
II. Using a UDF
III. Creating a Custom Add-In for UDFs

Exercise: Creating a Weighted Average Function