QuantlibXL: multiple calls to qlSetEvaluationDate()

[Wilkie Lai]

Hi Eric and all,
1.) I have experienced strange behavior with
qlSetEvaluationDate().  As I read from the
documentation there is only one global setting for
this variable in Excel so I would assume that once a
call to this function has been made, the evaluation
date will remain the same unless a different parameter
is passed to it.  However I have come to notice that
with multiple calls to this function it actually set
the global date to another date, seems like a
hard-coded one: 2005/06/18.  Can anyone share what is
going on behind the scene?

2.) Now because of this change in evaluation date I am
forced to do global recalculate (Ctrl-Alt-Shift F9,
usual Ctrl-Alt-F9 don't work) in order to fix the
date, but after multiple calls (usually 3-4 times)
then Excel will crash, pointing again to the XLL
library.  Would this be a  bug or there are other ways
to change/restate the evaluation date?


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

[eric ehlers]

Hello,

On 9/15/06, Wilkie Lai <[hidden email]> wrote:
> Hi Eric and all,
> 1.) I have experienced strange behavior with
> qlSetEvaluationDate().  As I read from the
> documentation there is only one global setting for
> this variable in Excel so I would assume that once a
> call to this function has been made, the evaluation
> date will remain the same unless a different parameter
> is passed to it.

Yes.

> However I have come to notice that
> with multiple calls to this function it actually set
> the global date to another date, seems like a
> hard-coded one: 2005/06/18.  Can anyone share what is
> going on behind the scene?

To my knowledge the only way of setting the evaluation date is with a
call to qlEvaluationDate().  So based on the behavior you describe my
first thought would be that somewhere in your workbook there is
another cell which is passing the value 2005/06/18 to
qlEvaluationDate().

I just noticed that the documentation that shipped with QLA 0.3.13
indicates that the evaluation date can also be reset by function
qlPiecewiseFlatForward().  This was true in 0.3.12 but is no longer
the case in 0.3.13 (in fact that function has been deleted
altogether), I have updated the online documentation
(http://www.quantlibaddin.org/evaluationdate.html).

> 2.) Now because of this change in evaluation date I am
> forced to do global recalculate (Ctrl-Alt-Shift F9,
> usual Ctrl-Alt-F9 don't work) in order to fix the
> date, but after multiple calls (usually 3-4 times)
> then Excel will crash, pointing again to the XLL
> library.  Would this be a  bug or there are other ways
> to change/restate the evaluation date?

Crashing Excel, that needs to be fixed.  If you could email me the
simplest possible example of a spreadsheet which demonstrates the
problem, and steps to recreate the error, I'll try to provide a patch.

Regards,
Eric

[Wilkie Lai]

Hi Eric and all,
1.) I have tried to construct a term structure object
using qlPiecewiseYieldCurve(), following the LIBOR
convention which should have a settlement lag of 2
days.  I have set up deposit rate and swap rate
through helpers forming the basis of the yield curve.
2.) As in the case of deposit rate with a 2 day
settlement lag, overnight rate would be 1 day from
today, tom/next rate would be 1 day after overnight,
and both are before spot date which by convention
would be today+2.  All set up without problem and
qlLatestDate() checked.
2.) Come the time when the curve object is
constructed, and trying to retrieve discount factor
through qlDiscount().  With the settlement lag at 2
and the overnight and tom/next rate, no discount
factor can be retrieved.  Log dumped shown that
negative day value is not supported.  Discount factor
will only be calculated if
- settlement lag set to 0, which is not the LIBOR
convention, OR
- overnight and tom/next deposit rate not included in
the term structure, then discount factor will be
calculated without problem.

I just ain't sure this would be the limitation of
quantlibXL, or have I setup some of the parameters
wrongly?
The distributed demo file YC_SwapDemo.xls actually has
the same problem.  When the settlement lag is set to 2
(which is the case for EURIBOR) then the discount
factor calculation would fail as well.

Regards,
Wilkie

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com