FE514 Financial Lab: VBA in Finance



Course Catalog Description

Introduction

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.

Campus Fall Spring Summer
On Campus
Web Campus

Instructors

Professor Email Office
Amin Salighehdar
asalighe@stevens.edu Altorfer 301

More Information

Course Description


Course Outcomes

At the end of 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.


Course Resources

Textbook

Chapra C. Steven, Introduction to VBA for Excel, 2nd edition, Prentice Hall, 2010. (required).

Simon Benninga, Financial Modeling (MIT Press), fourth edition. (recommended)

Chandan Sengupta, Financial Modeling Using Excel and VBA, Wiley, 2nd Edition .

Jackson Mary and Mike Staunton, Advanced modelling in finance using Excel and VBA, Wiley June 2001 .

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 .

Rouah Fabrice D. and Gregory Vainberg Option Pricing Models and Volatility using Excel-VBA, Wiley, April 2007 .



Grading

Grading Policies

The final grade in the class will be determined in the following manner:

  • 50% Assignments
  • 10% Quiz
  • 40% Project

There will be homework assignments for all section covered in this course.


Lecture Outline

Topic Reading
Week 1 • Understand the Excel/VBA Environment • Displaying the Developer Ribbon • Saving Macros • An Excel Interface and a VBA Macro Program (Simple Addition Program)
Week 2 • Review Excel • Excel Techniques • Matrices • Excel Functions Ch. 3
Week 3 • Understand Macro Recording • Absolute and Relative References • Using Macro Recording to Learn about VBA • User defined functions in VBA • Understand the idea of a function and how to use a function in VBA • Sub Procedures in VBA • Write a Procedure with Visual Basic Editor • How to run Sub procedure • Understand the difference between Function and Sub in VBA • Invoke a Sub procedure with a Call statement • Invoke a Function procedure with its name Ch. 3,4,5 (Txt 1) Ch. 15(Txt3)
Week 4 • Functions/Sub in VBA • Data types • How to declare different data types in VBA • Variable scope in VBA • Passing by Value or By Reference • Static variables Ch. 5,8 Ch. 15
Week 5 • Decisions in VBA (simple if/else and nested if/else statements in VBA) • How to write simple loops in VBA Ch. 11,12 Ch. 15
Week 6 • Object-Oriented Programming in VBA • Understand the concept of an object • Understand how methods can be used to manipulate an object properties • Working with ranges and collections Ch. 6
Week 7 • Data Structures: Arrays and Records • How to define an array and use it • Store one/multi-dimensional data in arrays • Pass arrays to procedures • Dynamic arrays • Define a record and pass it to procedures Ch. 13
Week 8 • String and Dialogue Boxes • Use VBA string functions to concatenate strings • Create the buttons and messages in Message Boxes • Creating and Accessing Files • Read data from a file into a VBA procedure and write results into another file • Custom Dialogue Boxes • Create your own custom dialogue box to obtain information and display results. Ch. 10 13
Week 9 Break Ch. 5
Week 10 • Review of Probability and Statistics • Review simple statistical models like regression Lecture Notes
Week 11 • Financial Applications in VBA
Week 12 • The Binomial Option-Pricing Models- Mathematics and Implementation
Week 13 • Monte Carlo Simulation • Optimization
Week 14 In Class Quiz One or two questions. In class coding required