particular file — will then be directly callable from Excel. Consider the by now well-

known function to value European call options in the Black-Scholes-Merton model in

Example 12-2.

Example 12-2. Python script for import with DataNitro into Excel

Valuation of European call options in BSM model

for use with DataNitro and Excel spreadsheets

functions.py

analytical Black-Scholes-Merton (BSM) formula

def bsm_call_value(S0, K, T, r, sigma):
”’ Valuation of European call option in BSM model.
Analytical formula.

            Parameters

            ==========

            S0  :   float

                            initial stock/index level

            K   :   float

                            strike  price

            T   :   float

                            time-to-maturity    (for    t=0)

            r   :   float

                            constant    risk-free   short   rate

            sigma   :   float

                            volatility  factor  in  diffusion   term



            Returns

            =======

            value   :   float

                            present value   of  the European    call    option

            ”’

from math import log, sqrt, exp
from scipy import stats
S0 = float(S0)
d1 = (log(S0 / K) + (r + 0.5 * sigma * 2 ) T) / (sigma sqrt(T))
d2 = (log(S0 / K) + (r - 0.5 sigma * 2 ) T) / (sigma sqrt(T))
value = (S0 stats.norm.cdf(d1, 0.0, 1.0)

• K exp(-r T) * stats.norm.cdf(d2, 0.0, 1.0))
  return value

If this script is imported via DataNitro, with UDFs enabled, you can use the valuation

formula from Excel. In practice, you can then type the following into an Excel cell:

=   bsm_call_value(B1,  100,    2.0,    B4, B5)

This is the same then as with any other Excel formula. For example, have a look at

Figure 12-7. In the upper-left corner you see a parameterization of the Black-Scholes-

Merton model. When you click on Insert Function in the FORMULAS tab of Excel, you

can enter a function dialog for the option valuation formula from Python (you find it under

DataNitro functions). Once you have provided references to the cells containing the

single parameter values, Python calculates the option value and returns the result to Excel.