Imports

[Doug Daly]

Hi again, one last question. I'm having trouble using imports. I want to be able to import numpy and such and use them in the functions. The function works when I test it just to spit out a value to ensure it's passing it back to excel, but as soon as I use an import i get a #VALUE! in Excel. Does ExcelPython support using other python libs?

Update:

Debug says: "No module named numpy found"

EDIT:

Think I figured it out. Sorry.

[Eric Reynolds]

Hi Doug,
Very happy to help you but could u provide some more details? Perhaps copy
your code here so I can see what ur trying to do.
Regards

[Doug Daly]

Hi Eric,

Figured it out, (see my post below). Could use your help with my last post at the bottom for a "Type Mismatch" I can't figure out.

Thanks,

[Eric Reynolds]

ah yes sorry i was replying to the automatic sourceforge e-mails i didn't see that post for some reason i'll take a look now

[Doug Daly]

So here's how I did it. Even though Enthough Canopy registers its python with the registry and everything will work from the command line, ExcelPython can't find the DLL (even if you add the Enthough dir to the PATH variable).

My fix, maybe not the most elegant, was to just install WinPython (which includes all the useful packages like scipy, numpy, etc.). And you also have to make sure a copy of ExcelPython27.dll is in the System32 folder - even if you're using x64.

[Doug Daly]

Last issue I'm having is a type mismatch I can't figure out - following the Wiki on here as well as the CodeProject tutorial.

Getting a type mismatch here:

Function calcCapValue(times As Range, fRates As Range, strike As Range, vol As Range, delta As Double, pv As Range) As Variant

    Set methods = PyModule("PyFunctions", AddPath:=ThisWorkbook.Path)
    Set result = PyCall(methods, "CalculateCapValue", KwArgs:=PyDict("times", times.Value2, "fwdRates", fRates.Value2, "strike", strike.Cells(1, 1).Value2, "flatVol", vol.Cells(1, 1).Value2, "delta", delta, "pv", pv.Cells(1, 1).Value2))

    calcCapValue = PyVar(PyGetItem(result, "res")) ' <--- TYPE MISMATCH

    Exit Function

End Function

Using the Python Function:

import numpy as np
from scipy.stats import norm

def CalculateCapValue(times, fwdRates, strike, flatVol, delta, pv):

    capPrice = 0;

    #Iterate through each time for caplet price

    for i in range(0, len(times)):

        ifr = float(fwdRates[i][0])
        iti = float(times[i][0])

        d1 = (np.log(ifr/strike)+((flatVol**2)*iti)/2)/(flatVol*np.sqrt(iti))
        d2 = d1 - (flatVol*np.sqrt(iti))
        Nd1 = norm.cdf(d1)
        Nd2 = norm.cdf(d2)

        capPrice += pv*delta*(ifr*Nd1 - strike*Nd2)

    return {"res": capPrice}

Can't figure out why...

[Eric Reynolds]

Ok so the reason is simple but subtle.

The value contained in capPrice is of type numpy.float64, and ExcelPython doesn't support automatic conversion of NumPy types to VBA types (nor will it ever unless I can find a way to implement this without making ExcelPython dependent on NumPy).

So the ways to get round this are to modify the Python code:

return {'res': float(capPrice)}

or the VBA code:

calcCapValue = PyVar(PyBuiltin("float", PyTuple(PyGetItem(result, "res"))))

[Eric Reynolds]

Btw I see you're using it for finance too :)

I bet 80% of the ppl who downloaded ExcelPython work in that sector (me too)

[Doug Daly]

Thanks, works like a charm. Not in the field just yet, finishing up school now - hoping to work in the field. Using it for a LMM model for a class project. Coded the model in C++/CUDA but needed to calculate those caplet spot volatilities. This library worked great for doing that in Python. Thanks again!