QLXL, #num, engine linking and dependency/triggers

[Phillip Blom]

Hi all

First time on the list so I apologize for any breach of etiquette.

I started using QLXL for the first time this last week. First of all, great job!

Some house keeping:
1. Excel 2010 with auto calc on
2. I have searched for a solution on the net and use group but no luck

Here is what I'm doing:
1. Created rate helpers and stripped a curve (South African curve, Q/Q A/365F). Strips to within 0.05 bps across the curve. Again great job

2. I want to get a fwd swap curve matrix
   A) I can't use qlmakevanillaswap as it doesn't do Q/Q swaps but you do attached the pricing engine at creation time.

   B) So I create a qlschedule (I use it for fix and float legs) then I use this to create a qlvanillaswap (can't attach a engine at this stage).

All these objects create successfully and I can inspect them

   C) Then I call qlinstrumentsetpricingengine and set the single engine (created earlier) to each swap

So I have a 6x5 'matrix' of swap objects. The corresponding 'matrix' of setting the pricing engine all return TRUE.

However, when I ask it for its fair value rate or NPV some of the swaps return #num and some return the correct answer. If I ctrl-alt-F9 it, it doesn't correct. If I F2 the engine formula and then full recalc it comes right. If I close the workbook and then open it again and do a full recalc the problem persists but different cells work and don't work. It's a bit random.

I always use cell addresses and not string so I keep the dependency tree in line. However, when I inspect the #num error it says ' qlvanillaswapfairrate - null pricing engine '. So it seems to me that the pricing engine is randomly not linking sometimes.

I'm thinking triggers are the answers but if it is I'm clearly not using it properly.

Could anyone give me a helping hand here. Clearly I'm the one missing things here.

Regards,
Phil

------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

[Ballabio Gerardo-4]

Yes triggers are the solution.
 
The "randomness" lies in the order by which Excel refreshes the cells. The correct order is: 1. Call qlmakevanillaswap to recreate the object (that deletes any previous association with the pricing engine). 2. Call qlinstrumentsetpricingengine to relink the pricing engine. 3. Call qlvanillaswapfairrate to recalculate the number. If Excel picks another order, bad luck.

If I understand correctly, you can enforce the correct order of evaluation by calling qlvanillaswapfairrate with "trigger" pointing to the cell where you call the corresponding qlinstrumentsetpricingengine. That makes it explicit to Excel that the former cell depends on the latter.

Gerardo


-----Messaggio originale-----
Da: Phillip Blom [mailto:[hidden email]]
Inviato: giovedì 3 gennaio 2013 9.18
A: [hidden email]
Oggetto: [Quantlib-users] QLXL, #num, engine linking and dependency/triggers

Hi all

First time on the list so I apologize for any breach of etiquette.

I started using QLXL for the first time this last week. First of all, great job!

Some house keeping:
1. Excel 2010 with auto calc on
2. I have searched for a solution on the net and use group but no luck

Here is what I'm doing:
1. Created rate helpers and stripped a curve (South African curve, Q/Q A/365F). Strips to within 0.05 bps across the curve. Again great job

2. I want to get a fwd swap curve matrix
   A) I can't use qlmakevanillaswap as it doesn't do Q/Q swaps but you do attached the pricing engine at creation time.

   B) So I create a qlschedule (I use it for fix and float legs) then I use this to create a qlvanillaswap (can't attach a engine at this stage).

All these objects create successfully and I can inspect them

   C) Then I call qlinstrumentsetpricingengine and set the single engine (created earlier) to each swap

So I have a 6x5 'matrix' of swap objects. The corresponding 'matrix' of setting the pricing engine all return TRUE.

However, when I ask it for its fair value rate or NPV some of the swaps return #num and some return the correct answer. If I ctrl-alt-F9 it, it doesn't correct. If I F2 the engine formula and then full recalc it comes right. If I close the workbook and then open it again and do a full recalc the problem persists but different cells work and don't work. It's a bit random.

I always use cell addresses and not string so I keep the dependency tree in line. However, when I inspect the #num error it says ' qlvanillaswapfairrate - null pricing engine '. So it seems to me that the pricing engine is randomly not linking sometimes.

I'm thinking triggers are the answers but if it is I'm clearly not using it properly.

Could anyone give me a helping hand here. Clearly I'm the one missing things here.

Regards,
Phil

------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
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.
Corso Italia, 49 - 20122 Milano - Tel. [phone], Fax [phone]
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>


------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users