QuantLib › quantlib-users

Re: QuantLibXL - VBA Attempt

Classic

List

Threaded

9 messages Options

Nicholas Manganaro-2

Re: QuantLibXL - VBA Attempt

Happy New Year, QuantLib-users!

I am still moving ahead slowly in building my analysis.
Before taking major strides, I thought it better to crawl, so I ran a quick
check to see if I could pull results by indexing into an array via VBA. The
macro is attached.
Unfortunately, my VBA results match what I am getting via QLXL functions
qlTimeSeries, qlTimeSeriesDates, and qlTimeSeriesValues on the spreadsheet.
I say "unfortunate" because the results are nonsensical to me. The first
elements entered into the datesArray1 and valueArray1 variant data
structures were 40910 and 0, respectively. Before using these arrays in the
qlTimeSeries constructor, I queried out the first entries of individual data
structures and got 40910 and 3.40282346638529E+38, respectively. Something
happened to make the 0 value very large.
After creating a qlTimeSeries object with datesArray1 and valueArray1, when
I query out its first elements, using qlTimeSeriesDates and
qlTimeSeriesValues, the series starts at the second date, and provides a
data value correctly associated with that second date. The length of the
time series is truncated to reflect the removal of the first time period
from consideration.
I am using QLXL 1.2.1, compiled using vc100, Win32 and Boost 1.53 , on a
Windows 7 Ultimate, SP1 desktop.
Is this what I should expect, or do I have to start compiling QLXL1.3?
Thanks in advance for taking a look.
Regards,
Nick

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

VBA_QL_Exercise.xls (50K) Download Attachment

Peter Caspers-4

Re: QuantLibXL - VBA Attempt

> and got 40910 and 3.40282346638529E+38, respectively. Something
> happened to make the 0 value very large.

this second value looks like Null<Real>(), i.e. the way QuantLib represents
a null floating point value. maybe this helps somehow already ?

best regards
Peter

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Luigi Ballabio

Re: QuantLibXL - VBA Attempt

Yes, it's a null. The time series is telling you that there's no value
at that date. I was puzzled that a value = 0 should be discarded, but
it turns out that there's some code added in the Excel wrappers that
filter zeroes out: see
<https://github.com/lballabio/quantlib/blob/master/QuantLibAddin/qlo/timeseries.cpp>
on line 53 and following. If you want to enable zero values, just
remove the check for values[i] != 0.0 and recompile. Once you've done
it, you might also send a pull request to Eric (his repo is at
<https://github.com/eehlers/quantlib/>) or Ferdinando
(<https://github.com/fametrano/quantlib/>) so that they consider
including the change in the official addin.

Luigi

On Thu, Jan 2, 2014 at 12:00 PM, Peter Caspers <[hidden email]> wrote:

>> and got 40910 and 3.40282346638529E+38, respectively. Something
>> happened to make the 0 value very large.
>
> this second value looks like Null<Real>(), i.e. the way QuantLib represents
> a null floating point value. maybe this helps somehow already ?
>
> best regards
> Peter
>
> ------------------------------------------------------------------------------
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users

Nicholas Manganaro-2

Re: QuantLibXL - VBA Attempt

Thanks to one and all, including Michael who advised me to move to Scala.
Now I have a path to address this surprise, which is much appreciated. I
will seriously consider the pull request route, as I think it would be
generally beneficial.
The funny thing is that I pulled much of the example (modified to test the
query from the qlTimeSeries object) out of a response for an earlier inquiry
on how to use QuantLibXL with VBA, and there was no response indicating that
his situation might result.
Excelsior!
-Nick

-----Original Message-----
From: Luigi Ballabio [mailto:[hidden email]]
Sent: Thursday, January 02, 2014 6:16 AM
To: Peter Caspers
Cc: [hidden email]; QuantLib users
Subject: Re: [Quantlib-users] QuantLibXL - VBA Attempt

Yes, it's a null. The time series is telling you that there's no value at
that date. I was puzzled that a value = 0 should be discarded, but it turns
out that there's some code added in the Excel wrappers that filter zeroes
out: see
<https://github.com/lballabio/quantlib/blob/master/QuantLibAddin/qlo/timeser
ies.cpp>
on line 53 and following. If you want to enable zero values, just remove
the check for values[i] != 0.0 and recompile. Once you've done it, you
might also send a pull request to Eric (his repo is at
<https://github.com/eehlers/quantlib/>) or Ferdinando
(<https://github.com/fametrano/quantlib/>) so that they consider including
the change in the official addin.

Luigi

On Thu, Jan 2, 2014 at 12:00 PM, Peter Caspers <[hidden email]>
wrote:

>> and got 40910 and 3.40282346638529E+38, respectively. Something
>> happened to make the 0 value very large.
>
> this second value looks like Null<Real>(), i.e. the way QuantLib
> represents a null floating point value. maybe this helps somehow already ?
>
> best regards
> Peter
>
> ----------------------------------------------------------------------
> -------- Rapidly troubleshoot problems before they affect your
> business. Most IT organizations don't have a clear picture of how
> application performance affects their revenue. With AppDynamics, you
> get 100% visibility into your Java,.NET, & PHP application. Start your
> 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.c
> lktrk _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users

--
<https://implementingquantlib.blogspot.com>
<https://twitter.com/lballabio>

----------------------------------------------------------------------------
--
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Nicholas Manganaro

Re: QuantLibXL - VBA Attempt

In reply to this post by Luigi Ballabio

>> and got 40910 and 3.40282346638529E+38, respectively. Something
>> happened to make the 0 value very large.
>
> this second value looks like Null<Real>(), i.e. the way QuantLib
> represents a null floating point value. maybe this helps somehow already ?
>
> best regards
> Peter
>
> ----------------------------------------------------------------------
> -------- Rapidly troubleshoot problems before they affect your
> business. Most IT organizations don't have a clear picture of how
> application performance affects their revenue. With AppDynamics, you
> get 100% visibility into your Java,.NET, & PHP application. Start your
> 15-day FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.c
> lktrk _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users

Peter Caspers-4

Re: QuantLibXL - VBA Attempt

I think this is just a copy/paste thing (from the index wrapper)

qlo/index.cpp, lines 40-51 => qlo/timeseries.cpp, lines 49-60

In addition to the values[i]!=0.0 check, the restriction to positive
values (QL_REQUIRE(...)) does not make sense either for the time
series, nor does the comment / the exception message apply there.

However also for indexes one could think of allowing for zero or
negative fixings.

Peter

On 2 January 2014 16:19, Nicholas Manganaro <[hidden email]> wrote:

> Thanks to one and all, including Michael who advised me to move to Scala.
> Now I have a path to address this surprise, which is much appreciated. I
> will seriously consider the pull request route, as I think it would be
> generally beneficial.
> The funny thing is that I pulled much of the example (modified to test the
> query from the qlTimeSeries object) out of a response for an earlier inquiry
> on how to use QuantLibXL with VBA, and there was no response indicating that
> his situation might result.
> Excelsior!
> -Nick
>
> -----Original Message-----
> From: Luigi Ballabio [mailto:[hidden email]]
> Sent: Thursday, January 02, 2014 6:16 AM
> To: Peter Caspers
> Cc: [hidden email]; QuantLib users
> Subject: Re: [Quantlib-users] QuantLibXL - VBA Attempt
>
> Yes, it's a null. The time series is telling you that there's no value at
> that date. I was puzzled that a value = 0 should be discarded, but it turns
> out that there's some code added in the Excel wrappers that filter zeroes
> out: see
> <https://github.com/lballabio/quantlib/blob/master/QuantLibAddin/qlo/timeser
> ies.cpp>
> on line 53 and following. If you want to enable zero values, just remove
> the check for values[i] != 0.0 and recompile. Once you've done it, you
> might also send a pull request to Eric (his repo is at
> <https://github.com/eehlers/quantlib/>) or Ferdinando
> (<https://github.com/fametrano/quantlib/>) so that they consider including
> the change in the official addin.
>
> Luigi
>
>
> On Thu, Jan 2, 2014 at 12:00 PM, Peter Caspers <[hidden email]>
> wrote:
>>> and got 40910 and 3.40282346638529E+38, respectively. Something
>>> happened to make the 0 value very large.
>>
>> this second value looks like Null<Real>(), i.e. the way QuantLib
>> represents a null floating point value. maybe this helps somehow already ?
>>
>> best regards
>> Peter
>>
>> ----------------------------------------------------------------------
>> -------- Rapidly troubleshoot problems before they affect your
>> business. Most IT organizations don't have a clear picture of how
>> application performance affects their revenue. With AppDynamics, you
>> get 100% visibility into your Java,.NET, & PHP application. Start your
>> 15-day FREE TRIAL of AppDynamics Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.c
>> lktrk _______________________________________________
>> QuantLib-users mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>
>
>
> --
> <https://implementingquantlib.blogspot.com>
> <https://twitter.com/lballabio>
>
> ----------------------------------------------------------------------------
> --
> Rapidly troubleshoot problems before they affect your business. Most IT
> organizations don't have a clear picture of how application performance
> affects their revenue. With AppDynamics, you get 100% visibility into your
> Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics
> Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
> _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>

------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Nicholas Manganaro

Re: QuantLibXL - VBA Attempt

Peter,
Thanks for finding the evil twins ;).
If I'm going to recompile after making some changes along the line you
mention, I might as well update to 1.3 at the same time.
Negatives and zeroes seem like possible index or time series elements to me
too.
-Nick
-----Original Message-----
From: Peter Caspers [mailto:[hidden email]]
Sent: Thursday, January 02, 2014 12:43 PM
To: Nicholas Manganaro
Cc: Luigi Ballabio; QuantLib users
Subject: Re: [Quantlib-users] QuantLibXL - VBA Attempt

I think this is just a copy/paste thing (from the index wrapper)

qlo/index.cpp, lines 40-51 => qlo/timeseries.cpp, lines 49-60

In addition to the values[i]!=0.0 check, the restriction to positive values
(QL_REQUIRE(...)) does not make sense either for the time series, nor does
the comment / the exception message apply there.

However also for indexes one could think of allowing for zero or negative
fixings.

Peter

On 2 January 2014 16:19, Nicholas Manganaro <[hidden email]> wrote:

> Thanks to one and all, including Michael who advised me to move to Scala.
> Now I have a path to address this surprise, which is much appreciated.
> I will seriously consider the pull request route, as I think it would
> be generally beneficial.
> The funny thing is that I pulled much of the example (modified to test
> the query from the qlTimeSeries object) out of a response for an
> earlier inquiry on how to use QuantLibXL with VBA, and there was no
> response indicating that his situation might result.
> Excelsior!
> -Nick
>
> -----Original Message-----
> From: Luigi Ballabio [mailto:[hidden email]]
> Sent: Thursday, January 02, 2014 6:16 AM
> To: Peter Caspers
> Cc: [hidden email]; QuantLib users
> Subject: Re: [Quantlib-users] QuantLibXL - VBA Attempt
>
> Yes, it's a null. The time series is telling you that there's no value
> at that date. I was puzzled that a value = 0 should be discarded, but
> it turns out that there's some code added in the Excel wrappers that
> filter zeroes
> out: see
> <https://github.com/lballabio/quantlib/blob/master/QuantLibAddin/qlo/t
> imeser
> ies.cpp>
> on line 53 and following. If you want to enable zero values, just
> remove the check for values[i] != 0.0 and recompile. Once you've done
> it, you might also send a pull request to Eric (his repo is at
> <https://github.com/eehlers/quantlib/>) or Ferdinando
> (<https://github.com/fametrano/quantlib/>) so that they consider
> including the change in the official addin.
>
> Luigi
>
>
> On Thu, Jan 2, 2014 at 12:00 PM, Peter Caspers
> <[hidden email]>
> wrote:
>>> and got 40910 and 3.40282346638529E+38, respectively. Something
>>> happened to make the 0 value very large.
>>
>> this second value looks like Null<Real>(), i.e. the way QuantLib
>> represents a null floating point value. maybe this helps somehow already

>>
>> best regards
>> Peter
>>
>> ---------------------------------------------------------------------
>> -
>> -------- Rapidly troubleshoot problems before they affect your
>> business. Most IT organizations don't have a clear picture of how
>> application performance affects their revenue. With AppDynamics, you
>> get 100% visibility into your Java,.NET, & PHP application. Start
>> your 15-day FREE TRIAL of AppDynamics Pro!
>> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.
>> c lktrk _______________________________________________
>> QuantLib-users mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>
>
>
> --
> <https://implementingquantlib.blogspot.com>
> <https://twitter.com/lballabio>
>
> ----------------------------------------------------------------------
> ------
> --
> Rapidly troubleshoot problems before they affect your business. Most
> IT organizations don't have a clear picture of how application
> performance affects their revenue. With AppDynamics, you get 100%
> visibility into your Java,.NET, & PHP application. Start your 15-day
> FREE TRIAL of AppDynamics Pro!
> http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.c
> lktrk _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>

Nicholas Manganaro-2

Re: QuantLibXL - VBA Attempt

I've joined Git, downloaded the new versions of QuantLib and associated
modules, updated Boost++ and uninstalled and reinstalled Visual Studio (long
story) to find that my C++ directory settings disappeared from both VS and
the QuantLibAddin installation manual page.
Would someone be kind enough to update me on the location for the boost
references in C++ directories for the project in Visual Studio 2012?
Only then might testing and revisions follow.
Thanks.
-Nick
-----Original Message-----
From: Nicholas Manganaro [mailto:[hidden email]]
Sent: Thursday, January 02, 2014 5:59 PM
To: 'Peter Caspers'
Cc: 'Luigi Ballabio'; 'QuantLib users'
Subject: Re: [Quantlib-users] QuantLibXL - VBA Attempt

Peter,
Thanks for finding the evil twins ;).
If I'm going to recompile after making some changes along the line you
mention, I might as well update to 1.3 at the same time.
Negatives and zeroes seem like possible index or time series elements to me
too.
-Nick
<snip>

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users

Luigi Ballabio

Re: QuantLibXL - VBA Attempt

Hi Nicholas,
the instructions at <http://quantlib.org/install/vc10.shtml>
should also work for 2012.

Luigi

On Thu, Jan 16, 2014 at 2:21 AM, Nicholas Manganaro
<[hidden email]> wrote:

> I've joined Git, downloaded the new versions of QuantLib and associated
> modules, updated Boost++ and uninstalled and reinstalled Visual Studio (long
> story) to find that my C++ directory settings disappeared from both VS and
> the QuantLibAddin installation manual page.
> Would someone be kind enough to update me on the location for the boost
> references in C++ directories for the project in Visual Studio 2012?
> Only then might testing and revisions follow.
> Thanks.
> -Nick
> -----Original Message-----
> From: Nicholas Manganaro [mailto:[hidden email]]
> Sent: Thursday, January 02, 2014 5:59 PM
> To: 'Peter Caspers'
> Cc: 'Luigi Ballabio'; 'QuantLib users'
> Subject: Re: [Quantlib-users] QuantLibXL - VBA Attempt
>
> Peter,
> Thanks for finding the evil twins ;).
> If I'm going to recompile after making some changes along the line you
> mention, I might as well update to 1.3 at the same time.
> Negatives and zeroes seem like possible index or time series elements to me
> too.
> -Nick
> <snip>
>
>
> ------------------------------------------------------------------------------
> CenturyLink Cloud: The Leader in Enterprise Cloud Services.
> Learn Why More Businesses Are Choosing CenturyLink Cloud For
> Critical Workloads, Development Environments & Everything In Between.
> Get a Quote or Start a Free Trial Today.
> http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
> _______________________________________________
> QuantLib-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/quantlib-users

--
<https://implementingquantlib.blogspot.com>
<https://twitter.com/lballabio>

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today.
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
QuantLib-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/quantlib-users