I was trying to write an Excel UDF using xlwings to return finance options calculation from the Mibian library. I've tried the code below.
import xlwings as xw
import mibian
@xw.func
def BSPutOptionImpVol(underlyingPrice,strike,interestRate,expiryDays,premium):
c = mibian.BS([underlyingPrice, strike, interestRate, expiryDays], putPrice=premium)
return c.impliedVolatility
From Excel, I then call the function with the following =BSPutOptionImpVol(45,32,1,127,0.95)
It's returning the following error:
"NameError: name 'norm' is not defined call = self.underlyingPrice * norm.cdf(self.d1) - \ File ""C:\Users...\anaconda3\lib\site-packages\mibian__init__.py"", line 307, in _price [self.callPrice, self.putPrice] = self._price() File ""C:\Users...\anaconda3\lib\site-packages\mibian__init__.py"", line 276, in init estimate = eval(className)(args, volatility=mid, performance=True).putPrice File ""C:\Users...\anaconda3\lib\site-packages\mibian__init__.py"", line 29, in impliedVolatility self.class.name, args, putPrice=self.putPrice) File ""C:\Users...\anaconda3\lib\site-packages\mibian__init__.py"", line 293, in init c = mibian.BS([underlyingPrice, strike, interestRate, expiryDays], putPrice=premium) File ""c:\users...\documents\python scripts\BSPutImVol.py"", line 6, in BSPutOptionImpVol ret = func(*args) File ""C:\Users...\anaconda3\lib\site-packages\xlwings\udfs.py"", line 298, in call_udf res = call_udf(script, fname, args, this_workbook, FromVariant(caller)) File ""C:\Users...\anaconda3\lib\site-packages\xlwings\server.py"", line 195, in CallUDF return func(args) File ""C:\Users...\anaconda3\lib\site-packages\win32com\server\policy.py"", line 586, in _invokeex_ return S_OK, -1, self._invokeex_(dispid, lcid, wFlags, args, None, None) File ""C:\Users...\anaconda3\lib\site-packages\win32com\server\policy.py"", line 283, in _invoke_ return self._invoke_(dispid, lcid, wFlags, args) File ""C:\Users...\anaconda3\lib\site-packages\win32com\server\policy.py"", line 278, in _Invoke_"
I have also tried just calling the function without passing the parameters in (ie the input values are in the python code) but I still get the same error.
However, if I comment out xlwings and just run the Python code from Spyder as below, it works.
#import xlwings as xw
import mibian
#@xw.func
def BSPutOptionImpVol(underlyingPrice,strike,interestRate,expiryDays,premium):
c = mibian.BS([underlyingPrice, strike, interestRate, expiryDays], putPrice=premium)
# return c.impliedVolatility
print(c.impliedVolatility)
I'm a newbie to Python, so appreciate any help and advice. Thanks.
