VBA/Excel in finance. Having more to say, please consider to be our guest blogger.

Apr
6

Black Scholes model assumes stock price follows GBM with constant volatility, however, the market implied volatilities of stock options often show "the volatility smile", which decreases with the strike level, and increases with the time to maturity. There are various proposed extensions of this GBM model to account for "the volatility smile". One approach is the

Download the paper and accompanying excel file at http://www.kelleyschool.com/papers.html.

**implied binomial tree**technique proposed by Rubinstein (1994), in which the author assumes the stock prices are generated by a modified random walk where the underlying assets volatility depends on both stock price and time, therefore it is an modification of basic Binomial tree method.**Implied binomial tree**uses the observable market option prices in order to estimate the implied distribution, to construct such a tree, optimization routine generally applies and technically it is more difficult than a basic Binomial tree. Here is a good paper implementing the implied binomial tree using an Excel spreadsheet without VBA. It demonstrates both the optimization needed to generate implied ending risk-neutral probabilities from a set of actual option prices and the backwards recursion needed to solve for the entire implied tree.Download the paper and accompanying excel file at http://www.kelleyschool.com/papers.html.

Apr
3

Another excel / VBA source for volatility calculation, including option based volatility such as implied volatility of Black Scholes model, volatility surface construction, Heston parameters estimation from option prices, etc; and a list of time series volatility calculation, for example, ARCH, ARIMA, EGARCH, EWMA, GARCH, GJR...

One thing you should be aware is some of the files involve a call to NAG library, one of the major benefits of the NAG Library is its inherent flexibility, it can be used by programmers developing in traditional languages, or by users of modern software packages and programming environments, like Microsoft Excel. Both of my former company and my current university have NAG library installed, so download & read more at your choice at http://php.portals.mbs.ac.uk/SerHuangPoon/Teaching/DataandProgrammes/tabid/973/Default.aspx and http://www.nag.co.uk/numeric/nagandexcel.asp

One thing you should be aware is some of the files involve a call to NAG library, one of the major benefits of the NAG Library is its inherent flexibility, it can be used by programmers developing in traditional languages, or by users of modern software packages and programming environments, like Microsoft Excel. Both of my former company and my current university have NAG library installed, so download & read more at your choice at http://php.portals.mbs.ac.uk/SerHuangPoon/Teaching/DataandProgrammes/tabid/973/Default.aspx and http://www.nag.co.uk/numeric/nagandexcel.asp

Mar
31

Visual Basic in finance application has been introduced many times in this blog, one especially useful book was reviewed in an old post Option Pricing Models and Volatility Using Excel-VBA. Here is another

at the moment the number of VBA codes is only a few, some useful files include:

Swaps Tool: Comprehensive tool to manage equity swap resets and set up new deal templates

Dividend Points: Retrieves Index Weights and Dividends Data from Bloomberg and Calculates Index Dividend Points

CA Tool: Dividends and Corporate Actions notification tool

Volatility: Building Volatilty Surface and using the SABR model for calibration

Trading Tool: Simple tool to set buy/sell targets and track prices and dividends

Visit the site if you are looking for

**VBA finance**site perhaps of your interest. As the site describes:The purpose of this site is to provide financial professionals with VBA code snippets and complete projects that can be useful in their work and development.

Our portfolio includes examples of:

* Data Extraction from different sources like Bloomberg and Reuters, internet sites like Yahoo and CBOE and financial systems like Murex

* Incorporating email capabilities with the VBA code which can be very useful when distributing reports on a frequent basis

* User defined functions and general topics like Add-ins, User Forms, utilizing the memory effectively, working with databases, etc ...

* VBA overview, syntax, keywords, classes

* Some useful Windows API functions and how to employ them

* Complete projects utilizing some of the examples above

Our portfolio includes examples of:

* Data Extraction from different sources like Bloomberg and Reuters, internet sites like Yahoo and CBOE and financial systems like Murex

* Incorporating email capabilities with the VBA code which can be very useful when distributing reports on a frequent basis

* User defined functions and general topics like Add-ins, User Forms, utilizing the memory effectively, working with databases, etc ...

* VBA overview, syntax, keywords, classes

* Some useful Windows API functions and how to employ them

* Complete projects utilizing some of the examples above

at the moment the number of VBA codes is only a few, some useful files include:

Swaps Tool: Comprehensive tool to manage equity swap resets and set up new deal templates

Dividend Points: Retrieves Index Weights and Dividends Data from Bloomberg and Calculates Index Dividend Points

CA Tool: Dividends and Corporate Actions notification tool

Volatility: Building Volatilty Surface and using the SABR model for calibration

Trading Tool: Simple tool to set buy/sell targets and track prices and dividends

Visit the site if you are looking for

**VBA finance**codes.
Sep
15

AAN left comments yesterday at the post Download historical stock price saying the methods I shared are either only for a single stock historical prices, such as Yahoo chinese historical stock data, or for multiple stocks quotes of the latest trading day, like Excellent Yahoo Finance Data Downloader, however, what he (and many others I guess) wants is an Excel to

Although I don't recommend to do this in Excel as it becomes messy with more and more stocks added, I attach a sample excel with Macro for

Steps:

**download multiple stocks historical quotes**from Yahoo finance, fair enough.Although I don't recommend to do this in Excel as it becomes messy with more and more stocks added, I attach a sample excel with Macro for

**multiple stocks quotes downloading**.**PS**: the main part of the macro is from http://www.mrexcel.com/forum/showthread.php?t=66516&page=2, what I did was modifying the code, allowing users to re-load data, which isn't permitted and returns an error in the original code.Steps:

**1**, download the attached excel, open it and enable data connections if your excel warns you for security reason;**2**, fill in the stocks symbols, start & end date you need in the sheet "Input",
Aug
27

VBA week...

The book starts with complex number, how to write macro code for it; followed by selected root-finding algorithms, and weighted least square regression; then introduces numerical integration, tree-building, black scholes, Heston model, GARCH, implied volatility, parameter estimation, etc. Preview is worth a thousand words, check yourself below:

**Option Pricing Models and Volatility Using Excel-VBA**is the best book I have read this year, recommended by a friend of mine couple of days ago. I didn't look positive at it at the beginning as there are dozes of books on similar topics and to be honest, I never heard of the author (now I know he works in industry). However, the more pages I dig, the less willing to stop & happier I feel as the author explains the volatility staff relevant to option pricing SOOOOO well and in plain language. More importantly, there are accompanying VBA codes for almost every example, if that's no enough, the author provides VBA solutions to the exercise as well, which encourage the readers to practice & make our hands dirty, unlike many other books do.The book starts with complex number, how to write macro code for it; followed by selected root-finding algorithms, and weighted least square regression; then introduces numerical integration, tree-building, black scholes, Heston model, GARCH, implied volatility, parameter estimation, etc. Preview is worth a thousand words, check yourself below: