Financial VBA Code for Excel contains 37 Excel VBA programs for advanced finance and statistics with open source code. The Excel VBA programs are categorized into 3 subject areas: Finance and Statistics Models; Random Number Generation and Statistics; and Numerical Searching Methods and Option Pricing. Each set can be purchased separately and the full set if offered as a bundle at a discounted price.
Finance and Statistics Models Set (Set 1) contains different topics in finance and statistics from simple applications such as computing standard deviation and mean to more advanced models such as Monte Carlo simulation, multivariate standard normal distribution, multiple regression, and option pricing models. VBA programs in this set include:
- Standard Deviation and Mean - Computes the mean (average) and the standard deviation from an array range.
- Lotto Number Generator - This program involves sampling without replacement. Numbers of one array are sorted based on the numbers of another array.
- Playing Card Probability - Calculates the probability of getting certain cards when cards are drawn from a deck.
- Normal Distribution Random Number Generator - Creates random numbers from a normal distribution given the standard deviation and the mean, and then computes the confidence interval given the level of significance, alpha.
- Monte Carlo Integration - Computes the area under standard normal probability distribution from 0 to z using integral calculus.
- Black-Scholes Option Pricing Model (European Call and Put) - Derives call and put option prices based on the Black-Scholes model.
- Binomial Option Pricing Model - Derives call and put option price using the binomial model, also known as the Cox-Ross-Rubinstein option model.
- Portfolio Optimization - Calculates portfolio possibilities by assigning different weights for each stock using a random number generator. The optimal portfolio is derived by the efficient frontier Sharpe Ratio.
- Multiple Regression - Runs multiple regression analysis using matrix algebra by utilizing the Excel functions in a VBA program.
- Bootstrap (A Non-Parametric Approach) - Uses the re-sampling with replacement method to extract estimates (such as standard deviation and confident interval) from a non-parametric data set.
- Multivariate Standard Normal Probability Distribution - Processes an advanced version of the Monte Carlo Integration by utilizing a numerical procedure (specifically, Jocobi search method, for derivation of the Eigenvectors and Eigenvalues) and matrix algebra.
- Monte Carlo Simulation - Calculates probability by utilizing random numbers to derive probability distribution and outcome.
- Option Greeks Based on Black-Scholes Option Pricing Model - Contains option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code.
Random Number Generation and Statistics Set (Set 2) provides 12 random numbers generators that allow you to generate histograms from the probability distributions given specified parameters. There is also an option to output random numbers from the distribution on the spreadsheet. VBA programs in this set include:
- Random Number Generator (Normal Distribution) - Generates random numbers under the normal distribution which is the most commonly used probability distribution in statistics.
- Random Number Generator (Log Normal Distribution) - Generates random numbers under the log-normal distribution which is often assumed to be the distribution of a stock price.
- Random Number Generator (Chi-Square Distribution) - Generates random numbers under the chi-square distribution to test the difference between proportions.
- Random Number Generator (F-Distribution) - Generates random numbers under the F distribution which is commonly used for ANOVA (analysis of variance), to test whether the variances of two or more populations are equal.
- Random Number Generator (Student-T Distribution) - Generates random numbers under the Student’s t distribution which is used commonly for small sample sizes.
- Random Number Generator (Log Pearson Type III Distribution) - Generates random numbers under the Log Pearson Type III distribution which is commonly used in hydraulic studies.
- Random Number Generator (Multivariate Standard Normal Distribution) - Populates random multivariate standard normal deviates on the spreadsheet for analysis.
- Random Number Generator (Gamma Distribution) - Generates random numbers under the Gamma distribution which is most often used to describe the distribution of the amount of time until the nth occurrence of an event in a Poisson process.
- Random Number Generator (Beta Distribution) - Generates random numbers under the Beta distribution can be used in the absence of data. Possible applications are estimate the proportion of defective items in a shipment or time to complete a task.
- Random Number Generator (Hypergeometirc Distribution) - Generates random numbers under the Hypergeometric distribution which is a discrete distribution. It is alike the Binomial distribution. Both of the Hypergeometric distribution and the Binomial distribution describe the number of times an event happens in a fixed number of trials.
- Random Number Generator (Triangular Distribution) - Generates random numbers under the Triangular distribution which is often used when no or little data is available. It has 3 parameters, the minimum and the maximum that defines the range, and the more likely (the peak).
- Random Number Generator (Binomial Distribution) - Generates random numbers under the Binomial distribution which describes the number of successes in t independent Bernoulli (yes or no) trials with probability p of success on each trial.
Numerical Searching Methods and Option Pricing Set (Set 3) contains topics in applying different numerical searching methods to solve mathematical equations and implied volatility from option pricing models. It also includes vanilla option pricing models on futures, currency (foreign exchange), stock market index, and stocks that pay a known dividend. VBA programs in this set include:
- Numerical Searching Method (Newton-Ralphson) - One of the most commonly used numerical searching methods for solving equations.
- Numerical Searching Method (Secant Method) - This method, does not require the differentiation of the equation in question. Therefore, it can be used to solve complex equations without the difficulty that one might have to encounter in trying to differentiate the equations.
- Implied Standard Deviation For Black/Scholes Call (Newton Approach) - Calculates the implied standard deviation or implied volatility which is the volatility value that would make the theoretical value (in this case the Black-Scholes Model) equals to the given market price.
- Implied Standard Deviation For Black/Scholes Call (Secant Approach) - Unlike Newton-Ralphson precedure, Secant method does not require the first differential of the of the standard deviation with respect to the price (Black/Scholes) as an input.
- Numerical Searching Methods and Option Pricing - Performs a bisection searching method utilizing linear interpolation. It uses a minimum and a maximum starting numbers in the iteration process.
- Black-Scholes Option Pricing Model (European Call and Put) - Derives call and put option price based on the Black-Scholes model.
- Option Greeks Based on Black-Scholes Option Pricing Model - Calculates option sensitivities (delta, gamma, vega, theta, and rho) formulas and source code.
- European Option Model on Asset with Known Cash Payouts - Calculates call and put prices when a stock issues dividend, cash is paid to the holder of the asset.
- European Option Model on Asset with Continuous Cash Payouts (Index Option) - Calculates call and put prices for assets which have numerous distribution of cash payouts.
- European Option Model on Currency - Demonstrates the computation of Currency option prices by the method developed by Garman and Kohlhagen.
- European Option Model on Futures - Demonstrates the computation of futures option prices by the method developed by Black.
Get It (Pricing is available on the next page)(Updated on 2024-01-01) |
