- 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).
- 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 effecitvely.
- Practical Work and Exercises. Readers are expected to build simple examples for themselves as they follow the text. The course also contains downloadable data sets or simple models that allow a reader to do this practical work without having to enter large sets of data or repeat previous steps.
- Quizzes and Exercises. There are several Quizzes, which are placed throughout the course. Some require the construction of small models or parts of models.
- 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. Portfolio optimisation with Solver. Bespoke calculations for statistical and risk 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.
- 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. User-defined functions for bespoke analysis.
- Algorithms in machine learning. Introduction and key concepts. Model calibration using optimisation. Value of information. Entropy. Cluster analysis. Optimal decision trees and information sequencing. Reinforcement learning. Other machine learning algorithms and methods.