You are currently viewing VBA Programming for Finance and Investments

VBA Programming for Finance and Investments

VBA Programming for Finance and Investments

VBA Programming for Finance and Investments

  • img 10+ hours of instruction
  • img 12 Demonstrations
  • img 20 Participant exercises
  • img Simulation based model in VBA
  • img 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

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