OBJECTIVES
- Become proficient in the use of VBA macros for many general applications in financial modelling and data analysis.
- Understand the main benefits and situations where automation through macros, as well as user-defined functions, can be most effective and efficient.
- Gain practical experience in a range of common types of application areas, and lay the foundation to develop skills further in other specialised areas (e.g. as covered in later courses of the program).
DESCRIPTION
- Overview. This course covers the main concepts and methods to get started and become proficient with with the use VBA to write macros and user-defined functions. It covers the reasons where automation may be needed, and the common areas where it is used in financial modelling and data analysis, as well as the necessary tools and techniques to implement these effectively.
- Practical work and exercises. Students are required to conduct numerous hands-on modelling exercises and can also follow along by replicating other examples that are shown in the text.
- Assessment tests. There are several assessment tests which test the key concepts and require one to do practical exercises using VBA (and Excel).
KEY TOPICS
- Introduction to VBA and macros. Benefits and uses. Automation of common tasks. Key steps to getting started. Recording and adapting code. Using Loops and Conditionality. Modules, variables and objects. Working with ranges and objects. Best practices. Run-time inputs and alerts. Debugging and checking. Worksheet events and changes. User-defined functions.
- Core applications of VBA to modelling. Sensitivity and scenario analysis. Circular references. GoalSeek.
- User-defined functions. Bespoke functions for financial calculations, data manipulation and analysis.
- Monte Carlo Simulation with VBA. Random sampling from distribution with user-defined functions. Repeated calculations and automation. Creating and running simulations. Results analysis and control. Examples of key applications: Cost budgeting, risk registers, cash flow risk, time schedule risk, project cost-time integration.
- Using VBA in other financial applications. Analytic and numerical solutions for Markowitz optimisation. Huang-Litzenberger formulas. Using Solver with VBA. Options valuation. Black-Scholes’ formulae.
- Applications of VBA to data manipulation, analysis, and statistics. Automating steps in data cleaning. Automation of filters and extraction. Data refresh. Multiple database queries and repeated use of criteria ranges. Determining the position and size of a data set. Reversing data sets by rows or columns. Consolidation of multiple worksheets. Working through files in folders. Adding or deleting worksheet categories. Reversing and splitting text items.
Take the course
III.a Macros, Automation, Algorithms and Applications
VBA macros and functions to automate common tasks, increase efficiency and create more powerful models.
Take the course
III.a Macros, Automation, Algorithms and Applications
VBA macros and functions to automate common tasks, increase efficiency and create more powerful models.