Re: A possible bug?? (QuantLibXL)

[erik-44]

From: Jeff Yu <jjyu2000@ya...>
A possible bug??  
2004-04-13 08:40
 
 
 When I tried to use qlAmericanOption_FD through the function template,
 Menu->Insert->Function->qlAmericanOption_FD,  Excel is hung as soon as the
 expiry date is given.
 However, it is ok if I fill in the expiry date the last, ie, asset, strik,
 grid, ... vol, risk free rate go first and the expiry date the last.
 
 So I suspect the expiry date is what triggers the calculation even not all
 parameters are in place.
 
 Can someone replicate this as I am not 100% sure this is a bug .... There
 are too many dependencies, such as DLLs,
 in the Microsoft environment that can attribute to the problem so a bug
 might be exaggerated.  :-)
 
 Cheers,
 
 

        From: Ferdinando Ametrano <nando@qu...>
        Re: A possible bug??  
        2004-04-13 09:52
         Jeff,
         
         I will get back to you as soon as possible. If I don"t please file this bug
         in the bug tracker
         (http://sourceforge.net/tracker/?group_id=12740&atid=112740). Thank you.
         
         ciao -- Nando

I've had a look at this.  Excel's function template invokes the function
each time you tab through the input fields.  You note that Excel hangs
when you enter the expiry date (the 8th of the 10 input parameters).
The reason that Excel does not hang before then is because the relevant
QuantLibXL function (xlAmericanOption_FD() in file
QuantLibXL\qlxl\pricers.cpp) contains the lines
                                                                                                                                            Date valueDate       = QlXlfOper(xlvalueDate).AsDate();
        Date maturityDate    = QlXlfOper(xlmaturityDate).AsDate();
                                                                                                                             which throw an exception when the dates are null, causing the function to return #N/A to Excel.  Once both dates are provided, the above lines complete successfully, and the code proceeds to call the underlying Quantlib constructor FdAmericanOption(), which hangs because the remaining input parameters are still null.
                                                                                                                            The problem can be corrected by validating the input parameters at the beginning of function xlAmericanOption_FD() and exiting if any is missing:
                                                                                                                             if (xltype.IsMissing() ||
    xlunderlying.IsMissing() ||
    ... etc ...
    xlgridPoints.IsMissing())
    throw("missing inputs"); // returns #N/A to Excel
                                                                                                                             but I'm not sure that's the right thing to do, there may be times when you legitimately want to call a cell formula with some parameters missing, in which case the validation would have to be more elaborate.

I submitted an update to the bug tracker.

Regards
Eric