Advanced Modelling and Analysis using Automation and Algorithms

Module D of the CinFM


Download Course Booklet

Course Description

This 3-day course covers areas of financial modelling and data analysis where automation or algorithms are required.

The main aims of the course are: First, to provide an understanding of the many areas where automation is useful, as well as a strong capability to automate tasks as appropriate in participants’ own applications. Second, to provide a foundation knowledge of a wide range of concepts or application in advanced finance and data analysis.

The course starts with an introduction to VBA macros, and their uses and benefits in terms of enhancing Excel modelling and overcoming limitation of the pure Excel tabular environment. We apply these to topics in general financial modelling and data analysis, conducting hands-on exercises in advanced sensitivity analysis, dealing with circularities, automation of general tasks in data manipulation, and the creation of user-defined functions for advanced statistical applications.

We then introduce a range of specific advanced topics, including risk assessment using Monte Carlo simulation, the valuation of derivative and options, an introduction to credit risk modelling, as well as some core concepts in machine learning. In each case, we explain the main concepts and conduct hands-on exercises to reinforce these whilst solidifying participants’ skills in algorithm conception, development and implementation.

On a stand-alone basis, this course serves to create a widely applicable set of capabilities in the use of automation using the Excel/VBA platform in financial modelling and data analysis, as well as some insight into the more general uses of automation that would often in practice be implemented in non-Excel platforms.

From the perspective of the CinFM, this course module serves to consolidate the skills learned in the other modules and round out the overall capability and knowledge of someone who holds the Advanced Certification in Financial Modelling and Data Analysis.

Learning Objectives


  • Learn to identify situations where automation is necessary or highly beneficial
  • Develop practical skills to create and use macros and user-defined functions
  • Enhance capabilities by build macros that deal with the most common situations encountered in financial modelling and data analysis
  • Learn about core concepts in risk assessment and the benefits in quantifying risk for decision purposes
  • Learn key aspects of Monte Carlo simulation, probability distributions and random sampling
  • Gain hands-on experience using simulation in a variety of business and financial contexts
  • Gain an understanding of risk-neutral valuation and its applicability
  • Learn the core concepts in credit risk
  • Conduct hands-on exercises using advanced Excel, VBA macros and user-defined functions for various applications in finance, derivatives and credit
  • Reinforce knowledge of advanced statistics
  • Learn key concepts in machine learning
  • Conduct hands-on exercises using macros to implement some selected core concepts in machine learning
  • Develop a solid capability to apply automation across a range of applications in modelling and data analysis

Key Topics Overview


  • VBA macros · Benefits, uses · Key steps
  • Sensitivity and scenario analysis · GoalSeek · Circular references · Optimisation · Solver
  • Data cleaning and manipulation · Automating filters and extraction · Automating Data Refresh · Worksheet consolidation · Worksheet events and changes
  • User-defined functions · Advanced statistics · Bespoke statistical calculations
  • Risk assessment and management · Probability distributions · Random sampling · Inverse functions · Random walks and Brownian motion · Monte Carlo simulation · Cost budgeting · Time schedule risk · Correlation · Results analysis and control
  • Introduction to quantitative finance · Risk-neutral valuation · Binomial trees · Options valuation · Black-Scholes’ formulae
  • Introduction to credit modelling · Vasicek and Merton formula · Default probabilities · Transition matrices · Portfolio losses · Capital requirements
  • Model calibration using optimisation · Multiple regression
  • Introduction to machine learning · Conditional probability · Bayesian analysis · Real options · Value of information · Entropy · Cluster analysis · Optimal decision trees and information sequencing · Reinforcement learning

 Course Agenda in Detail

Day 1: Core Applications of VBA Algorithms to Modelling and Data Analysis

AM: Getting Started with Excel VBA for Financial Modelling Applications

  • Course overview and introduction: The need for algorithms in modelling, data analysis, risk assessment, advanced financial calculations, statistical analysis and machine learning
  • Key concepts and methods when using VBA in Excel (use of loops, named ranges, recorded code, buttons etc.)
  • Hands-on exercises:
    • Automating Goal Seek (single and multiple output values)
    • Running sensitivity analysis
    • Scenario analysis (e.g. based on named ranges and dispersed inputs)
    • Resolving circular references (e.g. in financial statement modelling, project finance models etc.)
    • Optimisation using Solver: automating runs and resetting parameters
  • Recap, Q&A, Discussion

PM: Key Uses of VBA Macros in Data Manipulation and Analysis

  • Introduction to further concepts and syntax in VBA (e.g. objects, other forms of loops and execution control)
  • Hands-on exercises:
    • Cleaning and manipulating data sets using automated steps
    • Automating the running of database queries
    • Automating advanced filters and data extraction
    • Running macros based on detecting changes in a worksheet and other events
    • Automating data refresh for PivotTable
  • Further topics, exercises or demos (time-permitting)
    • Consolidating multiple worksheets together
    • Conducting operations on all charts or PivotTables
  • Day 1 Recap, Q&A, Discussion, Close

Day 2: Bespoke Functions, Risk Modelling and Simulation

AM: User-defined Functions for Advanced Modelling and Statistical Analysis

  • Introduction to user-defined functions
  • Hands-on exercises:
    • Creating user-defined functions (various examples in replacing table of calculations and creating more flexible ways to change model structure and size of data sets)
    • Bespoke statistical calculations (e.g. semi-deviations, probability distribution sampling)

PM: Introduction to Risk Assessment and Monte Carlo Simulation with VBA Macros

  • Meaning, definitions and uses; benefits and decision-making impact; risk management frameworks and approaches
  • Probability distributions to describe risk in quantitative terms: Key measures and selected distributions; inverse functions and random sampling
  • Hands-on exercises:
    • Creating random samples from distributions (using Excel and analytic forms)
    • Building and running a simulation model for cost budgetting and contingency planning; analysis of results, general interpretation and applicability of insights to more general contexts
    • Modelling time schedule risk and integrated cost-schedule modelling
    • Quantifying survivorship bias in index composition and performance
  • Introduction to further topics and hands-on exercises (time-permitting):
    • Further probability distributions
    • Creating correlated random samples
    • Adding user controls to manage simulation and results and create model flexibility (e.g. automating the addition, naming or deletion of results worksheets)
    • Assessing confidence intervals using simulation
  • Day 2 Recap, Q&A, Discussion, Close

Day 3: Introduction to Quantitative Finance and to Machine Learning

AM: Introduction to Quantitative Finance and Credit Modelling

  • Introduction to risk-neutral valuation and its applicability to valuation of options and other derivatives
  • Random walks and Brownian motion
  • Hands-on exercises in options valuation using macros and user-defined functions
    • Simulation methods
    • Binomial trees
    • Closed-form solutions; Black-Scholes’ formula
  • Introduction to credit risk modelling: Credit spread, implied probability of default, correlations between defaults, and portfolio effects, Vasicek and Merton models, capital requirements
  • Hands-on exercises using Excel calculations, macros and user-defined functions:
    • Credit spread, implied probability of default, transition matrices
    • Portfolio losses and capital requirements calculations using selected models

PM: Foundation Concepts in Advanced Statistics and Machine Learning

  • Overview of objectives, terminology and techniques (e.g. supervised, unsupervised learning, reinforcement learning)
  • Conditional probabilities and Bayesian analysis
  • Hands-on exercises:
    • Reversing probability trees
    • The value of information gained in testing or exploration processes, valuing flexibility and real options
  • Further advanced statistics
  • Hands-on exercises:
    • Confidence intervals for regression line
    • Multiple regression
    • Regression analysis as an optimisation progress
  • Core concepts in machine learning (e.g. measuring distances, overfitting, bias and variance, maximum likelihood estimation, entropy, training, testing and validation)
  • Hands-on exercises using functions, bespoke calculations or macros:
    • Implementing cluster analysis
    • Creation of optimal information sequences and decision trees
    • Reinforcement learning
  • Overview of other methods (Classification-based Regression, Principal Components Analysis, Naïve Bayes Classifier, Support Vector Machines, Neural Networks)
  • Day 3 Recap, Q&A, Discussion, Course Close