| Financial Models Using R and Excel VBA

Aside from the R models below, many more financial models in R can be found in my book Analyzing Financial Data and Implementing Financial Models Using R, which is published by Springer and available at and Springer's website.


For comments, please send an e-mail to For more information about Cliff, please visit

R Models

Call Option Pricing with Stochastic Volatility (11/26/2015)

Original version: 02/07/2015. Thanks to Ed Fleth for alerting me to an error in the code. This is the R version of the Excel/VBA code I have previously written. This model implements the Heston Stochastic Volatility model. Compared to the Excel/VBA version, we can run the Monte Carlo simulation using many more iterations without having to hold up our computer's resources as long as it would have done so in Excel.

European Option Pricing Using Monte Carlo Simulation (02/03/2015) (PDF Link)

This code shows how to price European call and put options by simulating the sample path of a normal random variable. The results are then compared to the Black-Scholes-Merton option values.

Estimating Beta By Retrieving Yahoo Finance Data and Running OLS Regression (04/26/2014)

This R code allows you to estimate the beta of a firm. The program retrieves Adjusted Prices from Yahoo Finance for the firm and the market index based on user input for the frequency of the prices and time period. The program then runs a market model regression and reports summary statistics. For Excel users, I also have a file that retrieves Yahoo Finance data in Excel below.

Implementing Event Studies Using Nonparametric Approach (02/05/2014)

Typically, a t-test is used to determine statistical significance in an event study, but there may be instances in which we may think a t-test is not appropriate. In those instances, we may want to use the empirical distribution of the abnormal returns to determine statistical significance. This program implements the latter using a rank test.

Implementing Event Studies Using the Indicator (Dummy) Variable Approach (02/05/2014)

This program implements the event study methodology in one-pass by using a dummy variable to indicate event dates. This approach is described in Binder (1985) and Karafiath (1988). This approach is sometimes referred to as an "in-sample event study," because the event window overlaps with the estimation period.

Identifying Structural Breaks and Implementing Chow Test (02/02/2014)

Using the technique developed in Bai and Perron (2003), this program finds the breakpoints in the data using the strucchange package. Then, based on that breakpoint, a Chow Test is implemented to determine whether the coefficients of a regression between those periods are statistically different.

Downloading Headlines of Company News from Google Finance (02/02/2014)

This program allows you to obtain company news from Google Finance. The results are then saved into a CSV file with the date, time, headline, and URL of the news story.

Downloading Company Financials from Google Finance (02/02/2014)

This program shows how to download financial statement data for firms from Google Finance from within R. The program then saves the data into separate CSV files.

Retrieve Yahoo Finance data using R in the same format as getSymbols (quantmod, R Code) (01/11/2014)

The getSymbols command in quantmod has not been able to access data from Yahoo Finance since the last few days of 2013. If you have code that uses the getSymbols command, use this function instead and you will be able to get your data into the same structure as what you previously can get using getSymbols. Some renaming may have to be done for symbols that start with a carat (^). This fix to getSymbols is based on the get.hist.quote function in the tseries package.

GARCH(1,1) (06/10/2012)

This R code uses market data from Yahoo Finance and allows you to plot a variety of charts resulting from a GARCH(1,1) estimate of the conditional volatility.

Estimating the Term Structure of Interest Rates Using Penalized Splines (06/09/2012)

This program contains the R code used in "Estimating the Market Value of Illiquid Debt Using WRDS TRACE Data," the presentation I did at the R/Finance 2012: Applied Finance with R Conference, to estimate the term structure of interest rates. This program reads in US Treasury STRIPS data and estimates the term structure of interest rates from that data using a penalized spline model.

Black-Scholes-Merton OPM and Option Greeks (06/02/2012)

This R code allows you to calculate the Black-Scholes-Merton call and put option values as well as some Option Greeks. The way this code works is that you have to call the function first by typing the source("bs.R") prior to using the function by typing bs(S,X,r,v,T), where S is the underlying asset price, X is the option strike price, r is the risk-free rate, v is the asset volatility, and T is the time to maturity of the option.


Excel/VBA Models

Pricing Call Options with Stochastic Volatility (11/26/2015)

Original Version: 11/28/12. Thanks to Evandro Liani for alerting me to an error in the code. This program calculates the value of a Call Option with Stochastic Volatility per Heston (1993). The Heston model is one of the most popular models of stochastic volatility used in practice. Stochastic volatility models assumes that volatility follows a random process. The program uses Monte Carlo simulation to generate the sample paths.

Mean-Variance Efficient Frontier (2 Assets) (02/10/2014)

I show the intuition behind constructing the mean-variance efficient frontier by using a 2-asset example. In this example, I first set target weights for the assets in the portfolio, then calculate the portfolio return and standard deviation corresponding to those weights. I then identify the minimum variance portfolio and the tangency portfolio, and plot the MV efficient frontier.

Binomial Options Pricing Model (04/03/2013)

In this file, I show how to calculate the value of a call option using a binomial OPM. The binomial model is a discrete time model, the results of which converge to the BSM model when the time steps are reduced to a sufficiently small amount. The up and down steps are based on the paper by Cox, Ross, and Rubinstein (1979).

Black-Scholes-Merton (BSM) Options Pricing Model - Closed Form and Monte Carlo (09/25/2012)

In this file, I show two approaches on how to calculate the value of a call and put option using Black-Scholes. First, I create a function in VBA to automate the option valuation process. Second, I use Monte Carlo simulation (also in VBA) to arrive at the option value. Note that these two approaches may yield different (albeit close) results depending on the inputs used. I also include Merton in the name of the model to give Merton credit for the portfolio replication approach that is commonly-used in deriving the solution to the Black-Scholes equation.

Pricing European and American Call Options When the Underlying Asset Pays Dividends (02/27/2012)

This program compares the values generated by the BSM Model assuming the underlying asset pays dividends. I show the values for both a European Call option and an American Call Option. This program allows you to put down an expected dividend payment schedule. If you can model the dividend payment in terms of a dividend yield (i.e., Dividend divided by Price), you can use the BSM file above.

Ordinary Least Squares and Weighted Least Squares Regression (02/27/2012)

This program allows the user to run a WLS regression using a two-factor model. WLS is useful in instances when the user believes more weight should be placed in some observations relative to other observations. To implement an OLS regression in the file, the user should change the values for the column of weights to the same number.

Calculating Holding Period Returns (02/24/2009)

Investors are often concerned with returns (percentage changes) in investment. This program shows how to calculate the holding period returns for different maturities using a single financial time series. In particular, I use S&P 500 Index data to calculate returns for one-year through ten-year holding periods.

Implied Volatility (02/24/2009)

Using the traded price of an option and the BSM OPM, we can arrive at the market's assessment of the volatility of the underlying asset. However, there is no closed-form solution to calculate the implied volatility of an option. In this file, I show how to use the Newton-Raphson method to calculate the BSM option value in VBA.

Quasi Monte Carlo Simulation (02/24/2009)

In this VBA program, I show how to simulate a normal distribution using Monte Carlo simulation.The program relies only on an approximation of the normal distribution because Excel does not generate true random numbers.  NOTE: This program may take a while to run because, by construction, Monte Carlo simulation works well when you have thousands of runs.

Generalized Autoregressive Conditional Heteroskedasticity (12/01/2007)

There is substantial evidence that returns of financial assets exhibit volatility clustering. This spreadsheet shows how to apply the GARCH(1,1) model in Excel. GARCH(1,1) was developed by Bollerslev (1986) and has been shown to provide as good an estimate as other GARCH(p,q) specifications where p+q > 2. This program uses the SOLVER function in Excel to calculate the MLE. Using the solver makes the implementation of the calculation substantially simpler than coding an algorithm in VBA. If you require the flexibility of coding this in VBA, you can consider using the Nelder Mead algorithm.

Retrieving Yahoo Prices (09/01/2008)

Being able to download data efficiently is important when dealing with large-scale empirical work. One of the benefits of using Excel is that you can automate downloading data from the Internet.  In this program, I show how to retrieve stock price data from Yahoo! Finance. NOTE: To make this program work, you will have to download and save the filename (as is) to your computer.

© 2015 Clifford S. Ang. All rights reserved.