FE514 Financial Lab: VBA in Finance

From Hanlon Financial Systems Lab Web Encyclopedia
Jump to: navigation, search



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 X X
Web Campus

Instructors

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




Course Outcomes

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.


Course Resources

Textbook

  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 .




Grading

Grading Policies

Assignments – 50% Projects – 40%

Quiz -10%


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)

Ch. 1 and 2
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