Advanced Excel and VBA for Financial Modeling

Advanced Excel and VBA for risk modeling, optimization, factor analysis and regression.


What's Inside

Excel is a programming language in and of itself. In this power-packed course, we use Excel and VBA (Visual Basic for Apps) for some serious financial number-crunching.

1. Risk Modeling

2. Factor Analysis

3. Numerical Optimization

4. Linear and Logistic Regression

Risk Modeling

A financial portfolio is almost always modeled as the sum of correlated random variables. Measuring the risk of this portfolio accurately is important for all kinds of applications: the financial crisis of 2007, the failure of the famous hedge fund LTCM and many other mishaps are attributable to poor risk modeling.

In this course, we cover the theory and practice of robust risk modeling:

  • Model risk using covariance matrices and historical returns
  • Refine this approach using factor models for dimensionality reduction and robustness
  • Generate realistic stress-test scenarios using these factor model
  • Calculate Value-at-Risk and understand the implications, strengths and weaknesses of this approach

Factor Analysis:

Factor analysis helps to cut through the clutter when you have a lot of correlated variables to explain a single effect.

This course will help you understand Factor analysis and it’s link to linear regression. See how Principal Components Analysis is a cookie cutter technique to solve factor extraction and how it relates to Machine learning .

What's covered?

Principal Components Analysis

  • Understanding principal components
  • Eigen values and Eigen vectors
  • Eigenvalue decomposition
  • Using principal components for dimensionality reduction and exploratory factor analysis.

Implementing PCA in Excel and VBA

  • Apply PCA to explain the returns of a technology stock like Apple
  • Find the principal components and use them to build a regression model

Numerical Optimization:

Optimization techniques are used everywhere, but until recently they were not that important in software. With the rising importance of machine learning that is changing, because training ML models requires optimization in parameter training.

This course focuses on the theory and implementation of optimization in Excel using Solver.

  • Understand the classic linear programming problem setup and the primal and dual problems
  • Really understand the simplex method - intuition, mechanics and implementation
  • Study various extensions to the Simplex method, including K-of-N constraints and quadratic objective functions
  • Understand what integer programming is, and how the LP-relaxation problem can be helpful
  • Be aware of the dangers of blindly using the LP-relaxation and then rounding off the solutions
  • Implement portfolio optimization for risk minimization in Excel and VBA

Linear and Logistic Regression:

This course will teach you how to build robust linear models and do logistic regression in Excel and VBA

Linear Regression is a powerful method for quantifying the cause and effect relationships that affect different phenomena in the world around us. This course will teach you how to build robust linear models that will stand up to scrutiny when you apply them to real world situations.

Logistic regression: Logistic regression has many cool applications : analyzing consequences of past events, allocating resources, solving binary classification problems using machine learning and so on. This course will help you understand the intuition behind logistic regression and how to solve it using cookie-cutter techniques.

Simple Regression :

  • Method of least squares, Explaining variance, Forecasting an outcome
  • Residuals, assumptions about residuals
  • Interpret regression results and avoid common pitfalls

Multiple Regression :

  • Introduce a categorical variable

Logistic Regression :

  • Applications of Logistic Regression, the link to Linear Regression and Machine Learning
  • Solving logistic regression using Maximum Likelihood Estimation and Linear Regression
  • Extending Binomial Logistic Regression to Multinomial Logistic Regression
  • Implement Logistic regression to build a model stock price movements in Excel and VBA

Course Curriculum

Get started now!

Certificate Available
1100+ Students
97 Lectures
11+ Hours of Video
Lifetime Access
24/7 Support
Instructor Rating

Loonycorn is comprised of a couple of individuals —Janani Ravi and Vitthal Srinivasan—who have honed their tech expertises at Google and Stanford. The team believes it has distilled the instruction of complicated tech concepts into funny, practical, engaging courses, and is excited to be sharing its content with eager students.

Popular Bundles