Home
Blog


Options Pricing in Excel with QuantLib



One tool for working with finance in Python is QuantLib. If you're building a basic model, there's a good chance it's already implemented in QuantLib; if you're working with something more advanced, it provides a good starting point.

Here's a basic example of options pricing. (You can download the example here, or see it on github.) This is the input to QuantLib:

Options pricing input in Excel.

The script reads in this data and returns a vanilla option price. This is using the Black-Scholes-Merton process for pricing, but many other options models are built in - the Heston/Hull White models and numerical models, for example. Here's where the computations are done:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# basic option
payoff = PlainVanillaPayoff(opt_type, strike)
exercise = EuropeanExercise(maturity)
europeanOption = VanillaOption(payoff, exercise)

# handle setups
underlyingH = QuoteHandle(SimpleQuote(underlying))
flatTermStructure = YieldTermStructureHandle(FlatForward(settlementDate, 
                                                         riskFreeRate, 
                                                         dayCounter))

dividendYield = YieldTermStructureHandle(FlatForward(settlementDate,
                                                     dividendYield,
                                                     Actual365Fixed()))

flatVolTS = BlackVolTermStructureHandle(BlackConstantVol(settlementDate,
                                                         calendar,
                                                         volatility, 
                                                         dayCounter))
# done
bsmProcess = BlackScholesMertonProcess(underlyingH, dividendYield,
                                       flatTermStructure, flatVolTS)
# method: analytic
europeanOption.setPricingEngine(AnalyticEuropeanEngine(bsmProcess))
value = europeanOption.NPV()
return value

The main part of this code is selecting what kind of model to use - computing the value of a vanilla option just scratches the surface of what QuantLib can do.

The output, with real time stock prices from Yahoo finance:

The result of the QuantLib options
						calculation, in Excel.

To use QuantLib with DataNitro, you need to compile Python bindings for it. Instructions for this are available on the QuantLib site. A compiled version of QuantLib for Python is also available with the enterprise version of DataNitro.