Copyright ©
Anthony Sun
All Rights Reserved.
New examples will be added on a regular basic, so please do check back.
Any questions regarding the examples?
Please post them in the
forum.
VBA Project on Finance, Statistics, Economics, and Mathematics Examples
Random Number Generator
Chi-Square Distribution,
F Distribution,
Student-T Distribution,
Normal Distribution,
Log-Normal Distribution,
Log Pearson Type III Distribution,
Gamma Distribution,
Beta Distribution,
Hypergeometric Distribution,
Triangular Distribution,
Binomial Distribution, and
Multivariate Standard Normal Distribution.
Numerical Searching Methods
Option Pricing Models
Excel VBA Tutorial
This section contains Excel and VBA tutorial examples on various topics in finance, mathematics, statistics and
other general issues. Many advanced topics are included in this section. To find introductory and intermediate examples
please go to the
More Excel VBA section. For Excel examples without VBA please go to the
Just Excel section.
You can also
download the selected free sample program files (code protected).
(A 3-part
Excel VBA Basic Tutorial series for beginners
has been added in the
More Excel VBA section.)
A simple tutorial that shows users on how to compute mean and standard deviation from an array (also a demonstration of creating user-defined
functions).
This example involves generating multiple unique random numbers from 1 to 54 with resampling without replacement technique by utilizingsorting one array based on another array.
What is the probability of getting 3 cards with red hearts and two other cards when 5 cards are drawn from a deck? This tutorial simulates
an actual scenario. The probability distribution derived from this simulation happens to be a Hypergeometric distribution.
This tutorial shows how to create random numbers from a normal distribution given the standard deviation and the mean, and then computes
the confidence interval given the level of significance. Also, a histogram is introduced.
This example uses simulation to find the integral (area under) of a normal distribution curve within a specific interval.
The example computes the European call and put price based on Black-Scholes option pricing models. Cumulative Standard Normal Distribution is
also introduced.
The example computes the European call and put price based on Binomial option pricing models. Binomial coefficient is also computed.
This tutorial demonstrates on how to obtain the optimal portfolio (highest return with lowest standard deviation) using Harry Markowitz
theory. The largest Sharpe Ratio is used to determine the optimal set. The efficient frontier is plotted from the simulated
sample.
Using matrix algebra by utilizing the Excel functions such as, MInverse( ) and MMult( ), which performs matrix inversion and matrix
multiplication, respectively.
Use resampling with replacement, a probability distribution for the median is created, along with the standard deviation of the median,
which cannot be computed under mathematical formula (since there is none).
This example is perhaps the most advanced example so far on this website. It generates multivariate standard normal distribution
deviates from correlated variables and then compute the probability from the given z values. A numerical procedure, Jocobi search method
is used to derive the Eigenvectors and Eigenvalues.
This tutorial is two of the most popular tutorials on this site (guess which is the other one?). By assuming underlying probability
distributions (normal, uniform, and truncate normal) of the variables in the profit equation, we get a probability distribution for
the profit. It answers the questions like 'what is the chance that we will loss profit' and 'what is the chance that we will
make the X amount of profit.'
This tutorial contains option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code. Option sensitivities
are also know as the Greeks. They measures how sensitive the option price is toward changes in its parameters. All
Greeks are available in user-defined VBA functions and in mathematical formulas.