VBA Programming for Finance and Investments
10+ hours of instruction
12 Demonstrations
20 Participant exercises
Simulation based model in VBA
Loan level amortization model in VBA
VBA Programming for Finance and Investments
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. Includes over 10 hours of instruction, 12 demonstrations, 20 participant exercises, a VBA based simulation model and a VBA based loan level amortization model.
Course Outline
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
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
IV. FOR NEXT Loops
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
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
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
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
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
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
I. Automatically Generating Output Reports
II. Exporting Output Reports
Exercise: Building in Output Reporting
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
