R: R: QuantLibXL: date serial number error in Bonds.xls qlFloatingRateBond() stand alone example
Posted by Ballabio Gerardo-4 on
URL: http://quantlib.414.s1.nabble.com/QuantLibXL-date-serial-number-error-in-Bonds-xls-qlFloatingRateBond-stand-alone-example-tp14509p14543.html
Hello,
thanks to Lisa Ann's suggestion, I finally managed to identify this issue.
The problem isn't actually specific to QuantLibXL, it shows also in pure QuantLib as demonstrated by the attached test files. After some digging I found that it originates at lines 65-66 of file QuantLib/ql/cashflows/iborcoupon.cpp:
Date nextFixingDate = fixingCalendar.advance(
accrualEndDate_, -static_cast<Integer>(fixingDays), Days);
When fixingDays==Null<Natural>(), as in the default signature of qlFloatingRateBond, when casted to Integer it becomes 2147483647 (2^31-1) and thus when you try to advance accrualEndDate_ by that, you jump out of the allowed date range.
I've attached a minimal patch to fix this issue. I set the default value of fixingDays to 2 because that's what makes the QuantLibXL/Workbooks/StandaloneExamples/Bonds.xls example give the same results as with QuantLib 1.1. I'm not sure whether this would be appropriate in all cases. I've also not fully understood the logic of the code, so I'd suggest that the person who wrote that code have a look at it and perhaps write a better patch. According to the copyright notice, that's Nando ;-)
P.S. there's also a regression in the Bonds.xls file: in the FlowAnalysis sheet there's a matrix with this formula (cells A1:T30):
=qlBondFlowAnalysis(Bonds!K14,Bonds!K37)
Since QuantLibXL 1.2 this function has changed its interface: an additional AfterDate argument was inserted between ObjectId and Trigger, so now this breaks. The formula should be fixed like this:
=qlBondFlowAnalysis(Bonds!K14,,Bonds!K37)
You should also update the dates in cells Bonds!G8 and Bonds!G10 to year 2013, otherwise you'll get a "missing fixing" error in sheet Pricing. Even better, the dates should be calculated so that they're always up to date.
Gerardo
Gerardo Ballabio
Risk Management
Banca Profilo S.p.A.
Via Cerva 28 - 20122 Milano
Tel. +39 02 58408.463
www.bancaprofilo.it
-----Messaggio originale-----
Da: Lisa Ann [mailto:[hidden email]]
Inviato: giovedì 19 settembre 2013 12.59
A: [hidden email]
Oggetto: Re: [Quantlib-users] R: QuantLibXL: date serial number error in Bonds.xls qlFloatingRateBond() stand alone example
In the aforementioned Excel spreadsheet you can solve this issue amending the *FixingDays *argument of *qlFloatingRateBond()*... I hope this information can help you.
--
View this message in context: http://quantlib.10058.n7.nabble.com/QuantLibXL-date-serial-number-error-in-Bonds-xls-qlFloatingRateBond-stand-alone-example-tp14509p14522.html
Sent from the quantlib-users mailing list archive at Nabble.com.
------------------------------------------------------------------------------
LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99!
1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint 2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13.
http://pubads.g.doubleclick.net/gampad/clk?id=58041151&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head></head>
<body>
<div style="font-family:Calibri;font-size:10px">
Banca Profilo S.p.A.
Via Cerva, 28 - 20122 Milano - Tel. 02 58408.1, Fax 02 5831 6057
Capitale Sociale Euro 136.794.106,00 i.v.
Iscrizione al Registro Imprese di Milano, C.F. e P.IVA 09108700155 - [hidden email]
Iscritta all’Albo delle Banche e dei Gruppi bancari
Aderente al Fondo Interbancario di Tutela dei depositi
Aderente al Conciliatore Bancario Finanziario e all’Arbitro Bancario Finanziario
Appartenente al Gruppo bancario Banca Profilo e soggetta all’attività di direzione e coordinamento di Arepo BP S.p.A.
DISCLAIMER:
The information transmitted may contain confidential and/or privileged material.
Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the material from any computer.
</div>
</body>
</html>
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users
floatingratebond.cc (4K) Download Attachment
iborcoupon.cc (1K) Download Attachment
iborcoupon.patch (1K) Download Attachment
URL: http://quantlib.414.s1.nabble.com/QuantLibXL-date-serial-number-error-in-Bonds-xls-qlFloatingRateBond-stand-alone-example-tp14509p14543.html
Hello,
thanks to Lisa Ann's suggestion, I finally managed to identify this issue.
The problem isn't actually specific to QuantLibXL, it shows also in pure QuantLib as demonstrated by the attached test files. After some digging I found that it originates at lines 65-66 of file QuantLib/ql/cashflows/iborcoupon.cpp:
Date nextFixingDate = fixingCalendar.advance(
accrualEndDate_, -static_cast<Integer>(fixingDays), Days);
When fixingDays==Null<Natural>(), as in the default signature of qlFloatingRateBond, when casted to Integer it becomes 2147483647 (2^31-1) and thus when you try to advance accrualEndDate_ by that, you jump out of the allowed date range.
I've attached a minimal patch to fix this issue. I set the default value of fixingDays to 2 because that's what makes the QuantLibXL/Workbooks/StandaloneExamples/Bonds.xls example give the same results as with QuantLib 1.1. I'm not sure whether this would be appropriate in all cases. I've also not fully understood the logic of the code, so I'd suggest that the person who wrote that code have a look at it and perhaps write a better patch. According to the copyright notice, that's Nando ;-)
P.S. there's also a regression in the Bonds.xls file: in the FlowAnalysis sheet there's a matrix with this formula (cells A1:T30):
=qlBondFlowAnalysis(Bonds!K14,Bonds!K37)
Since QuantLibXL 1.2 this function has changed its interface: an additional AfterDate argument was inserted between ObjectId and Trigger, so now this breaks. The formula should be fixed like this:
=qlBondFlowAnalysis(Bonds!K14,,Bonds!K37)
You should also update the dates in cells Bonds!G8 and Bonds!G10 to year 2013, otherwise you'll get a "missing fixing" error in sheet Pricing. Even better, the dates should be calculated so that they're always up to date.
Gerardo
Gerardo Ballabio
Risk Management
Banca Profilo S.p.A.
Via Cerva 28 - 20122 Milano
Tel. +39 02 58408.463
www.bancaprofilo.it
-----Messaggio originale-----
Da: Lisa Ann [mailto:[hidden email]]
Inviato: giovedì 19 settembre 2013 12.59
A: [hidden email]
Oggetto: Re: [Quantlib-users] R: QuantLibXL: date serial number error in Bonds.xls qlFloatingRateBond() stand alone example
In the aforementioned Excel spreadsheet you can solve this issue amending the *FixingDays *argument of *qlFloatingRateBond()*... I hope this information can help you.
--
View this message in context: http://quantlib.10058.n7.nabble.com/QuantLibXL-date-serial-number-error-in-Bonds-xls-qlFloatingRateBond-stand-alone-example-tp14509p14522.html
Sent from the quantlib-users mailing list archive at Nabble.com.
------------------------------------------------------------------------------
LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99!
1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint 2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13.
http://pubads.g.doubleclick.net/gampad/clk?id=58041151&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head></head>
<body>
<div style="font-family:Calibri;font-size:10px">
Banca Profilo S.p.A.
Via Cerva, 28 - 20122 Milano - Tel. 02 58408.1, Fax 02 5831 6057
Capitale Sociale Euro 136.794.106,00 i.v.
Iscrizione al Registro Imprese di Milano, C.F. e P.IVA 09108700155 - [hidden email]
Iscritta all’Albo delle Banche e dei Gruppi bancari
Aderente al Fondo Interbancario di Tutela dei depositi
Aderente al Conciliatore Bancario Finanziario e all’Arbitro Bancario Finanziario
Appartenente al Gruppo bancario Banca Profilo e soggetta all’attività di direzione e coordinamento di Arepo BP S.p.A.
DISCLAIMER:
The information transmitted may contain confidential and/or privileged material.
Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the material from any computer.
</div>
</body>
</html>
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60133471&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users
floatingratebond.cc (4K) Download Attachment iborcoupon.cc (1K) Download Attachment iborcoupon.patch (1K) Download Attachment| Free forum by Nabble | Edit this page |