5 Automation and Algorithms using VBA and Macros

Automation and Algorithms using VBA and Macros

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 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.

KEY TOPICS

  • Introduction to VBA and macros.  Benefits, uses · Key steps to getting started · Recording and adapting code · Loops· Conditionality · Working with ranges and objects · Code execution · Best practices · Run-time inputs and alerts · Debugging and checking.
  • Automation of common modelling tasks. Sensitivity and scenario analysis · Circular references · GoalSeek.
  • Automating data cleaning and manipulation  Filters and extraction · Data Refresh · Removing duplicates · Worksheet consolidation · Worksheet events and changes.
  • User-defined functions. · Bespoke calculations for statistics, financial modelling and data analysis.