FE514 Financial Lab: VBA in Finance
Course Catalog Description
Introduction
| Campus | Fall | Spring | Summer |
|---|---|---|---|
| On Campus | |||
| Web Campus |
Instructors
| Professor | 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 |