This course covers basic programming concepts for developing advanced financial models in Excel and how to implement them in Excel’s Visual Basic for Applications (VBA) programming language. Students will learn Visual Basic for Excel (VBA) programming. This course is not an introduction to Excel.
Learning Outcomes of Advanced Financial Modeling
- Record macros and debug code.
- Demonstrate how to control the flow of code with loops and if - then statements.
- Design a user interface by adding controls (buttons, scrollbars, drop down boxes, etc.)
- Write custom functions for spreadsheets (such as functions that use Black-Scholes formulas to price options.)
- Explain how to use VBA code to access data from databases and the web and manipulate pivot tables and charts to display the data.
- Utilize VBA code to create your own simulations using random number generators and to automate the use of Solver.
Click Here for Video Transcript
ROBIN DILLON-MERRILL: Welcome to Advanced Financial Modeling. I am Robin Dillon-Merrill, a professor in the Operations and Information Management group at the McDonough School of Business. This course is going to focus on teaching advanced financial modeling skills in Excel specifically using the Visual Basic for Applications, or VBA, programming language that is built into Excel.
This is not an Excel course. You will be a lot better using Excel at the end of this course. But I am not going to teach you Excel. I am assuming that you all have some basic Excel knowledge. And we will be focusing on the programming language within Excel to expand the power of your spreadsheets.
Imagine that in a previous Excel modeling task, you start with your blank spreadsheet and are given inputs in a worksheet. You relate the inputs with appropriate formulas to calculate the model outputs. You might create one or more charts to show the outputs graphically. You do all of this through the Excel interface, using its menus and ribbons, entering formulas into cells, and using chart tools.
Now suppose that instead of creating one spreadsheet model, you need to develop an application for other less technical people in your organization to use. The front end will present the user with dialog boxes or some other means for enabling them to specify the parameters of their problem. Your application will take this information, perform the appropriate model tasks, and eventually present a report of the results with accompanying charts. This application development is possible with VBA, as we will demonstrate in this course.
We have divided the concepts into three segments, which we are calling basic skills, advanced skills, and practical skills. We will first cover basic skills that are common to practically all programming languages, including developing subroutines, declaring variable types, and controlling the flow of the program.
Once we master the basics, we will examine how to enhance the user interface by creating custom dialog boxes, adding ActiveX controls, and creating custom functions.
Finally, we will focus on practical business-related exercises, such as creating simulations, using Excel to retrieve data from the web or databases, and creating Excel projects that interface with Word or PowerPoint. I look forward to exploring and discussing these topics with you.