Implied Volatility with Python’s Pandas Library AND Python in Excel

Authors: Brett Murphy and Aaron Waters

The March 6 New York Quantitative Python User’s Group (NY QPUG) Meetup included presentations by NAG (Numerical Algorithms Group), known for its high quality numerical computing software and high performance computing (HPC) services, and Enthought, a provider of scientific computing solutions powered by Python.

Brian Spector, a technical consultant at NAG, presented “Implied Volatility using Python’s Pandas Library.” He covered a technique and script for calculating implied volatility for option prices in the Black–Scholes formula using Pandas and nag4py. With this technique, you can determine for what volatility the Black–Scholes equation price equals the market price. This volatility is then denoted as the implied volatility observed in the market. Brian fitted varying degrees of polynomials to the volatility curves, then examined the volatility surface and its sensitivity with respect to the interest rate. See the full presentation in the video below:

Brian Spector of NAG demonstrates a technique and script for calculating Implied Volatility using Python’s Pandas Library at the March 2014 NYQPUG Meetup.

Implied Volatility Plot

An interactive Implied Volatility plot with Numpy, Pandas, Chaco, Matplotlib and nag4py

Then Aaron Watters, scientific software developer at Enthought, presented an overview of replacing VBA with Python in Excel using the PyXLL package. Instead of uncontrolled spreadsheet versions spreading across an organization, PyXLL allows you to load centrally-managed Python code and execute it in Excel, giving you the full breadth and power of the Python analytic computing ecosystem within the familiar user interface of Excel. Aaron showed a demo of a tool in Excel where he could browse his disk usage graphically.

Enthought: Chaco GUI in Excel

Chaco GUI running in Excel with data recalculating live in the spreadsheet

For those looking to get their latest Python models and algorithms out to Excel users, PyXLL greatly streamlines the process. See Aaron’s full demo of the functionality below:

Aaron Watters of Enthought presented an overview of replacing VBA with Python for Excel with PyXLL at the March 2014 NYQPUG Meetup.

Leave a comment