hfsl_course

FE514 Financial Lab: VBA in Finance
Financial Engineering (Lab)
Thursday 12:00pm-1:00pm
Hanlon Financial System Lab
Thursday 3:00PM-5:00PM
  • Fall On Campus
  • Spring On Campus
This course is an introduction to programming with VBA – the Visual Basic for Applications language. In this course, students will obtain VBA programming skills which enable them to understand and implement financial models. Students will learn how a simple VBA macro can automate time-consuming tasks and calculations. Furthermore, they will learn how advanced programs may be used for complex
derivative pricing directly within excel.

After taking this course, students will be able to:

  1.  Implement financial programs and models within the Excel/VBA environment.
  2. Create customized functions and procedures to facilitate computation.
  3. Write well-structured programs and understand VBA data structures and objects.
  1. Chapra C. Steven, Introduction to VBA for Excel, 2nd edition, Prentice Hall, 2010. (required).
  2. Simon Benninga, Financial Modeling (MIT Press), fourth edition. (recommended)
  3. Chandan Sengupta, Financial Modeling Using Excel and VBA, Wiley, 2nd Edition .
  4. Jackson Mary and Mike Staunton, Advanced modelling in finance using Excel and VBA, Wiley June 2001 .
  5. Lai Donny C. F., Humphrey K. K. Tung, Michael C. S. Wong and Stephen Ng, Professional Financial Computing using Excel and VBA, Wiley, June 2010 .
  6. Rouah Fabrice D. and Gregory Vainberg Option Pricing Models and Volatility using Excel-VBA, Wiley, April 2007 .

Assignments – 60% Final Projects – 40%

  Topic Readings Assignment
Week 1

Getting Started with Excel

Review of Excel Built-In Functions

Lecture notes

Chapter 33-35 (Textbook 2)

Chapter 4 (Textbook 3)
 
Week 2 Review of Excel Built-In functions and Analysis tools

Lecture notes

Chapter 33-35 (Textbook2)

Chapter 4 (Textbook 3)
 
Week 3

VBA Environment and Macro Recording

Data Types, Constants and Variables- Variable scope  

Chapters 1-3 (Textbook 1)

Lecture notes

Chapter 8 (Textbook 1)

Assignment 1
Week 4

Functions

Modular Programming

Sub procedures vs. Function procedures, passing by value vs. reference

Chapter 4 -5 (Textbook 1)

 
Week 5 The VBA Object Browser and Library: Objects, Properties, Methods, and Collections. Working with ranges.

Chapter 6 (Textbook 1)

Chapter 15 (Textbook 3)
 
Week 6

Structured Programming I: Decisions-

Nesting and Compound logical expressions
Chapter 11 (Textbook 1) Assignment 2
Week 7

Structured Programming II: Loops: For/Next,

Do/While/Exit
Chapter 12 (Textbook 1)  
Week 8 Working with data structures: Arrays, Passing Arrays to Procedures, Dynamic Arrays (vs. static) and the ReDim statement, Data Records Chapter 13 (Textbook 1) Assignment 3
Week 9

Creating and Accessing Files

VBA API for Bloomberg 

Chapter 14 (Textbook 1)

Lecture Notes.
 
Week 10 Creating custom Input, message boxes, other GUI objects- User Forms

Chapter 10 (Textbook 1)

Chapter 15 (Textbook 1)
Assignment 4
Week 11 Financial Application using VBA

The Binomial Option-Pricing Models.

Option Greeks
 
Week 12 Financial Application using VBA

The Black-Scholes Models

Using Monte Carlo Methods for Option Pricing
Project description due 
Week 13 Thanksgiving break – No Classes    
Week 14 Final Project Presentations