QuantLib › quantlib-users

Help With YieldCurveBootstrapping.xls

Classic

List

Threaded

7 messages Options

Rod Pienaar-2

Help With YieldCurveBootstrapping.xls

I need a little help with the YieldCurveBootstrapping.xls spreadsheet provided with the Excel examples. When I first open the spreadsheet I only get the Excel error message #NUM! in columns H & I (date & data). Even if I recalculate or edit values in cells I seem to get this error and I can't track down the cause.

Here is the weird thing! When I open the spreadsheet Option.xls and YieldCurveBootstrapping.xls together, so that they are both open at the same time, then the spreadsheet YieldCurveBootstrapping.xls works and the yield curve values are properly calculated. Its as though the Option.xls spreadsheet changes some value or setting that the YieldCurveBootstrapping.xls needs.

Here are my system details.

Operating System: Microsoft Windows XP Personal (32-bit)

OS Version: 5.01.2600 Service Pack 2

Processor: x86 Family 15 Model 47 Stepping 2, AuthenticAMD, ~2412MHz

Microsoft Excel: 2003

Regards

Rod

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Eric Ehlers-2

Re: Help With YieldCurveBootstrapping.xls

Hi Rod,

On Sun, July 20, 2008 10:17, Rod Pienaar wrote:
> Hi
>
> I need a little help with the YieldCurveBootstrapping.xls
> spreadsheet provided with the Excel examples. When I first
> open the spreadsheet I only get the Excel error message #NUM!
> in columns H & I (date & data). Even if I recalculate or
edit
> values in cells I seem to get this error and I can't track
down
> the cause.

After loading QuantLibXL-vc80-mt-s-0_9_0.xll and
YieldCurveBootstrapping.xls, hit Ctrl-Alt-F9 to force a full
recalculation.

> Here is the weird thing! When I open the spreadsheet
> Option.xls and YieldCurveBootstrapping.xls together, so that
> they are both open at the same time, then the spreadsheet
> YieldCurveBootstrapping.xls works and the yield curve values
> are properly calculated. Its as though the Option.xls
> spreadsheet changes some value or setting that the
> YieldCurveBootstrapping.xls needs.

Probably in the course of opening Option.xls you did something
to trigger a recalculation.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft
Excel as a client to the Grid

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Rod Pienaar-2

Re: Help With YieldCurveBootstrapping.xls

Thanks for the reply Eric. Unfortunately its not something as simple as that and I have already tried a full recalculation.

----- Original Message -----

From: "Eric Ehlers" <[hidden email]>

To: "Rod Pienaar" <[hidden email]>

Cc: <[hidden email]>

Sent: Tuesday, July 29, 2008 1:40 PM

Subject: Re: [Quantlib-users] Help With YieldCurveBootstrapping.xls

Eric Ehlers-2

Re: Help With YieldCurveBootstrapping.xls

Hello,

On Sat, August 2, 2008 11:41, Rod Pienaar wrote:
> Thanks for the reply Eric. Unfortunately its not something as
> simple as that and I have already tried a full recalculation.

Would you have some time to do some troubleshooting and send
the results?

Here is what I would suggest:

1) Start Excel

2) Load QuantLibXL-vc80-mt-s-0_9_0.xll

3) Enable logging. Open an empty workbook if there isn't
one open already.
In any cell, enter the formula ohSetLogFile(),
specifying the desired path
to the log file, e.g:

=ohSetLogFile("C:\path\to\log.txt")

Close the empty book.

4) Load YieldCurveBootstrapping.xls

5) Hit Ctrl-Alt-F9

6) In sheet Bootstrapping, use two empty cells to call
ohRetrieveError() on
the ranges which are returning #NUM:

=ohRetrieveError(H2:H32)
=ohRetrieveError(I2:I32)

7) Send another message with the results of 6) and the
contents of the logfile.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft
Excel as a client to the Grid

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Eric Ehlers-2

Re: Help With YieldCurveBootstrapping.xls

Hi Guilhem,

[cc Quantlib-users]

On Mon, August 4, 2008 09:53, Guilhem Lebreton wrote:

>
> Hi Eric
> I got the same error as Rod yesterday (Sunday 03rd August 2008)
> in Booststrapping worksheet in YieldCurveBootstrapping.xls
> workbook.
> Today (Monday 04th August 2008) , the results in H2:32 and
> I2:I32 are ok. Changing my PC date with Sunday 03rd August this
> morning involves the same error as yesterday.
> I am almost sure that the error is linked with date (business
> day / dayoff).
>
> Regards
> Guilhem LEBRETON

Yes, of course, that's it. Workbook
YieldCurveBootstrapping.xls doesn't explicitly set the
evaluation date, leaving the value to default to today's date,
which causes the book to fail on a non-business day.

The workbook Options.xls includes an explicit call to
qlSettingsSetEvaluationDate(), which explains the earlier
observation that opening Options.xls causes
YieldCurveBootstrapping.xls to work.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft
Excel as a client to the Grid

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Rod Pienaar-2

Re: Help With YieldCurveBootstrapping.xls

In reply to this post by Rod Pienaar-2

Eric

I did a full uninstall and reinstall and this is now working. I guess my first installation got corrupted somehow.

Regards

Rod

----- Original Message -----

From: [hidden email]

To: [hidden email]

Cc: [hidden email]

Sent: Saturday, August 02, 2008 11:41 AM

Subject: Re: [Quantlib-users] Help With YieldCurveBootstrapping.xls

Thanks for the reply Eric. Unfortunately its not something as simple as that and I have already tried a full recalculation.

----- Original Message -----
From: "Eric Ehlers" <[hidden email]>

To: "Rod Pienaar" <[hidden email]>

Cc: <[hidden email]>

Sent: Tuesday, July 29, 2008 1:40 PM

Subject: Re: [Quantlib-users] Help With YieldCurveBootstrapping.xls

Hi Rod,

On Sun, July 20, 2008 10:17, Rod Pienaar wrote:
> Hi
>
> I need a little help with the YieldCurveBootstrapping.xls
> spreadsheet provided with the Excel examples. When I first
> open the spreadsheet I only get the Excel error message #NUM!
> in columns H & I (date & data). Even if I recalculate or
edit
> values in cells I seem to get this error and I can't track
down
> the cause.

After loading QuantLibXL-vc80-mt-s-0_9_0.xll and
YieldCurveBootstrapping.xls, hit Ctrl-Alt-F9 to force a full
recalculation.

> Here is the weird thing! When I open the spreadsheet
> Option.xls and YieldCurveBootstrapping.xls together, so that
> they are both open at the same time, then the spreadsheet
> YieldCurveBootstrapping.xls works and the yield curve values
> are properly calculated. Its as though the Option.xls
> spreadsheet changes some value or setting that the
> YieldCurveBootstrapping.xls needs.

Probably in the course of opening Option.xls you did something
to trigger a recalculation.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft
Excel as a client to the Grid

Eric Ehlers-2

Re: Help With YieldCurveBootstrapping.xls

In reply to this post by Eric Ehlers-2

Hello Rod,

On Mon, August 4, 2008 19:53, Rod Pienaar wrote:
> Eric
>
> I did a full uninstall and reinstall and this is now working.

> I guess my first installation got corrupted somehow.

I notice that you're not subscribed to quantlib-users, maybe
you missed the attached message that I sent yesterday?

Your earlier two messages reporting failure were sent on 20 Jul
and 2 Aug - each a nonworking day - and then yesterday it was
OK. Maybe the explanation for the resolution is the fact that
the book only works on business days rather than the reinstall?

In any case, many thanks for the update and I'm very pleased to
hear that it's OK.

Regards,
Eric

-------------------------
Eric Ehlers
nazcatech sprl | Brussels | http://www.nazcatech.be
Distributed computing for pricing analytics - Use Microsoft
Excel as a client to the Grid

On Mon, August 4, 2008 12:25, Eric Ehlers wrote:

> Hi Guilhem,
>
> [cc Quantlib-users]
>
> On Mon, August 4, 2008 09:53, Guilhem Lebreton wrote:
>>
>> Hi Eric
>> I got the same error as Rod yesterday (Sunday 03rd August
>> 2008)
>> in Booststrapping worksheet in YieldCurveBootstrapping.xls
>> workbook.
>> Today (Monday 04th August 2008) , the results in H2:32 and
>> I2:I32 are ok. Changing my PC date with Sunday 03rd August
>> this
>> morning involves the same error as yesterday.
>> I am almost sure that the error is linked with date (business
>> day / dayoff).
>>
>> Regards
>> Guilhem LEBRETON
>
> Yes, of course, that's it. Workbook
> YieldCurveBootstrapping.xls doesn't explicitly set the
> evaluation date, leaving the value to default to today's date,
> which causes the book to fail on a non-business day.
>
> The workbook Options.xls includes an explicit call to
> qlSettingsSetEvaluationDate(), which explains the earlier
> observation that opening Options.xls causes
> YieldCurveBootstrapping.xls to work.
>
> Regards,
> Eric