You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Edward Ivanovic <ed...@gmail.com> on 2010/12/04 05:45:28 UTC
Using FinanceLib
Hi, I'd like to use the pv() function that used to be a static method in the
FinanceLib class. I just upgraded to POI 3.7 and it's no longer available
(seems the entire FinanceLib class is no longer public).
I'm wondering if there is another way to access the pv method now directly
from my Java code (not working with Excel files - just want to use the
financial methods directly).
Thanks
Re: Using FinanceLib
Posted by Yegor Kozlov <ye...@dinom.ru>.
The visibility of FinanceLib was changed to public in r1044370
You should try with the latest nightly build from here:
http://encore.torchbox.com/poi-cvs-build/
Yegor
On 12/6/2010 7:24 PM, Yegor Kozlov wrote:
> OK, I got the request. The visibility of the FinanceLib class will be
> raised from default to public.
>
> Thanks for raising this topic.
>
> Regards,
> Yegor
>
>> That's a shame. Why hide a perfectly good Java financial library?
>>
>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>
>>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>
>>>> That's interesting - it no longer comes up in the classes list on
>>>> http://poi.apache.org/apidocs/index.html
>>>> I wonder if the direct page link that you can find via a Google
>>>> search is
>>>> an
>>>> orphaned page.
>>>>
>>>>
>>> I updated the online apidocs to reflect current POI trunk.
>>>
>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>> defined as public:
>>>> final class FinanceLib {...
>>>>
>>>>
>>>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>
>>>>
>>>> I think this is why I can't access any methods out of it even
>>>> though the
>>>> methods themselves are declared as public.
>>>>
>>>>
>>> It was done intentionally. FinanceLib is intended for POI internal use
>>> only. This class can be renamed / re-packaged in future versions of
>>> POI or
>>> access
>>> to its methods can be changed from 'public' to 'default' or less.
>>>
>>>
>>> Yegor
>>>
>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>> declared as a public class. Can it be reverted to public again?
>>>>
>>>> Thanks
>>>>
>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>> wrote:
>>>>
>>>> Hi, I'd like to use the pv() function that used to be a static
>>>> method in
>>>>>> the
>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>> available
>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>
>>>>>> The methods on FinanceLib are all public static, so you ought
>>>>>> to be
>>>>> fine:
>>>>>
>>>>>
>>>>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>
>>>>>
>>>>> Note that in 3.8, the package will change to
>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Jon Svede <js...@yahoo.com>.
I can confirm this fixed my issue.
Thanks, Marcel!
Jon
----- Original Message ----
From: Yegor Kozlov <ye...@dinom.ru>
To: user@poi.apache.org
Sent: Fri, December 10, 2010 8:04:08 AM
Subject: Re: NPV bug?
The patch was applied in r1044370.
Please try the latest build from trunk. Daily builds can be downloaded
from here:
http://encore.torchbox.com/poi-cvs-build/
Regards,
Yegor
On 12/9/2010 8:34 AM, Marcel May wrote:
> Thx Jon and David.
>
> I added the patch fixing NPV to the ticket.
>
> Jon, let me know if the patch does not work.
> There's a simple JUnit test case included, too.
>
> Cheers,
> Marcel
>
> On Dec 9, 2010, at 8:28 AM, Jon Svede wrote:
>
>> I created issue #50437 and attached my .xls file. Marcel can work from that
>>and
>> submit his test case or I can create one.
>>
>>
>> Jon
>>
>>
>>
>>
>> ----- Original Message ----
>> From: Jon Svede<js...@yahoo.com>
>> To: POI Users List<us...@poi.apache.org>
>> Sent: Wed, December 8, 2010 9:10:40 PM
>> Subject: Re: NPV bug?
>>
>> Hi all,
>>
>> Sorry for the delay in responding.
>>
>> I can create the ticket and the test case, I'll try to get to this by the
end
>> of the week.
>>
>> Thanks for the confirmation!
>>
>> Jon
>>
>>
>>
>>
>>
>> ----- Original Message ----
>> From: David Fisher<df...@jmlafferty.com>
>> To: POI Users List<us...@poi.apache.org>
>> Sent: Wed, December 8, 2010 5:42:15 PM
>> Subject: Re: NPV bug?
>>
>> Hi Marcel,
>>
>> Please have a look at http://poi.apache.org/guidelines.html in the
"Submitting
>> Patches" section.
>>
>> Thanks and Regards,
>> Dave
>>
>> On Dec 8, 2010, at 2:46 PM, Marcel May wrote:
>>
>>> <npv.patch.txt>
>>>
>>> Oops, the patch got swallowed.
>>>
>>> On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
>>>
>>>> I can confirm this is a bug - just had the same problem.
>>>>
>>>> a) the NPV computation is wrong
>>>> - sum += ds[i] / Math.pow(rate + 1, i);
>>>> + sum += ds[i] / Math.pow(rate + 1, i+1);
>>>>
>>>> I think the computation should actually use the FinanceLib#npv method
>>(which
>>>> is correct).
>>>>
>>>> b) The impl does not support ranges
>>>>
>>>> I got a patch fix a) and b), including a test case:
>>>>
>>>>
>>>>
>>>> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>>>>
>>>> Cheers,
>>>> Marcel
> ...
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Yegor Kozlov <ye...@dinom.ru>.
The patch was applied in r1044370.
Please try the latest build from trunk. Daily builds can be downloaded
from here:
http://encore.torchbox.com/poi-cvs-build/
Regards,
Yegor
On 12/9/2010 8:34 AM, Marcel May wrote:
> Thx Jon and David.
>
> I added the patch fixing NPV to the ticket.
>
> Jon, let me know if the patch does not work.
> There's a simple JUnit test case included, too.
>
> Cheers,
> Marcel
>
> On Dec 9, 2010, at 8:28 AM, Jon Svede wrote:
>
>> I created issue #50437 and attached my .xls file. Marcel can work from that and
>> submit his test case or I can create one.
>>
>>
>> Jon
>>
>>
>>
>>
>> ----- Original Message ----
>> From: Jon Svede<js...@yahoo.com>
>> To: POI Users List<us...@poi.apache.org>
>> Sent: Wed, December 8, 2010 9:10:40 PM
>> Subject: Re: NPV bug?
>>
>> Hi all,
>>
>> Sorry for the delay in responding.
>>
>> I can create the ticket and the test case, I'll try to get to this by the end
>> of the week.
>>
>> Thanks for the confirmation!
>>
>> Jon
>>
>>
>>
>>
>>
>> ----- Original Message ----
>> From: David Fisher<df...@jmlafferty.com>
>> To: POI Users List<us...@poi.apache.org>
>> Sent: Wed, December 8, 2010 5:42:15 PM
>> Subject: Re: NPV bug?
>>
>> Hi Marcel,
>>
>> Please have a look at http://poi.apache.org/guidelines.html in the "Submitting
>> Patches" section.
>>
>> Thanks and Regards,
>> Dave
>>
>> On Dec 8, 2010, at 2:46 PM, Marcel May wrote:
>>
>>> <npv.patch.txt>
>>>
>>> Oops, the patch got swallowed.
>>>
>>> On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
>>>
>>>> I can confirm this is a bug - just had the same problem.
>>>>
>>>> a) the NPV computation is wrong
>>>> - sum += ds[i] / Math.pow(rate + 1, i);
>>>> + sum += ds[i] / Math.pow(rate + 1, i+1);
>>>>
>>>> I think the computation should actually use the FinanceLib#npv method (which
>>>> is correct).
>>>>
>>>> b) The impl does not support ranges
>>>>
>>>> I got a patch fix a) and b), including a test case:
>>>>
>>>>
>>>>
>>>> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>>>>
>>>> Cheers,
>>>> Marcel
> ...
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Marcel May <ma...@googlemail.com>.
Thx Jon and David.
I added the patch fixing NPV to the ticket.
Jon, let me know if the patch does not work.
There's a simple JUnit test case included, too.
Cheers,
Marcel
On Dec 9, 2010, at 8:28 AM, Jon Svede wrote:
> I created issue #50437 and attached my .xls file. Marcel can work from that and
> submit his test case or I can create one.
>
>
> Jon
>
>
>
>
> ----- Original Message ----
> From: Jon Svede <js...@yahoo.com>
> To: POI Users List <us...@poi.apache.org>
> Sent: Wed, December 8, 2010 9:10:40 PM
> Subject: Re: NPV bug?
>
> Hi all,
>
> Sorry for the delay in responding.
>
> I can create the ticket and the test case, I'll try to get to this by the end
> of the week.
>
> Thanks for the confirmation!
>
> Jon
>
>
>
>
>
> ----- Original Message ----
> From: David Fisher <df...@jmlafferty.com>
> To: POI Users List <us...@poi.apache.org>
> Sent: Wed, December 8, 2010 5:42:15 PM
> Subject: Re: NPV bug?
>
> Hi Marcel,
>
> Please have a look at http://poi.apache.org/guidelines.html in the "Submitting
> Patches" section.
>
> Thanks and Regards,
> Dave
>
> On Dec 8, 2010, at 2:46 PM, Marcel May wrote:
>
>> <npv.patch.txt>
>>
>> Oops, the patch got swallowed.
>>
>> On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
>>
>>> I can confirm this is a bug - just had the same problem.
>>>
>>> a) the NPV computation is wrong
>>> - sum += ds[i] / Math.pow(rate + 1, i);
>>> + sum += ds[i] / Math.pow(rate + 1, i+1);
>>>
>>> I think the computation should actually use the FinanceLib#npv method (which
>>
>>> is correct).
>>>
>>> b) The impl does not support ranges
>>>
>>> I got a patch fix a) and b), including a test case:
>>>
>>>
>>>
>>> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>>>
>>> Cheers,
>>> Marcel
...
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Jon Svede <js...@yahoo.com>.
I created issue #50437 and attached my .xls file. Marcel can work from that and
submit his test case or I can create one.
Jon
----- Original Message ----
From: Jon Svede <js...@yahoo.com>
To: POI Users List <us...@poi.apache.org>
Sent: Wed, December 8, 2010 9:10:40 PM
Subject: Re: NPV bug?
Hi all,
Sorry for the delay in responding.
I can create the ticket and the test case, I'll try to get to this by the end
of the week.
Thanks for the confirmation!
Jon
----- Original Message ----
From: David Fisher <df...@jmlafferty.com>
To: POI Users List <us...@poi.apache.org>
Sent: Wed, December 8, 2010 5:42:15 PM
Subject: Re: NPV bug?
Hi Marcel,
Please have a look at http://poi.apache.org/guidelines.html in the "Submitting
Patches" section.
Thanks and Regards,
Dave
On Dec 8, 2010, at 2:46 PM, Marcel May wrote:
> <npv.patch.txt>
>
> Oops, the patch got swallowed.
>
> On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
>
>> I can confirm this is a bug - just had the same problem.
>>
>> a) the NPV computation is wrong
>> - sum += ds[i] / Math.pow(rate + 1, i);
>> + sum += ds[i] / Math.pow(rate + 1, i+1);
>>
>> I think the computation should actually use the FinanceLib#npv method (which
>
>>is correct).
>>
>> b) The impl does not support ranges
>>
>> I got a patch fix a) and b), including a test case:
>>
>>
>>
>> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>>
>> Cheers,
>> Marcel
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Jon Svede <js...@yahoo.com>.
Hi all,
Sorry for the delay in responding.
I can create the ticket and the test case, I'll try to get to this by the end
of the week.
Thanks for the confirmation!
Jon
----- Original Message ----
From: David Fisher <df...@jmlafferty.com>
To: POI Users List <us...@poi.apache.org>
Sent: Wed, December 8, 2010 5:42:15 PM
Subject: Re: NPV bug?
Hi Marcel,
Please have a look at http://poi.apache.org/guidelines.html in the "Submitting
Patches" section.
Thanks and Regards,
Dave
On Dec 8, 2010, at 2:46 PM, Marcel May wrote:
> <npv.patch.txt>
>
> Oops, the patch got swallowed.
>
> On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
>
>> I can confirm this is a bug - just had the same problem.
>>
>> a) the NPV computation is wrong
>> - sum += ds[i] / Math.pow(rate + 1, i);
>> + sum += ds[i] / Math.pow(rate + 1, i+1);
>>
>> I think the computation should actually use the FinanceLib#npv method (which
>>is correct).
>>
>> b) The impl does not support ranges
>>
>> I got a patch fix a) and b), including a test case:
>>
>>
>>
>> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>>
>> Cheers,
>> Marcel
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by David Fisher <df...@jmlafferty.com>.
Hi Marcel,
Please have a look at http://poi.apache.org/guidelines.html in the "Submitting Patches" section.
Thanks and Regards,
Dave
On Dec 8, 2010, at 2:46 PM, Marcel May wrote:
> <npv.patch.txt>
>
> Oops, the patch got swallowed.
>
> On Dec 9, 2010, at 2:33 AM, Marcel May wrote:
>
>> I can confirm this is a bug - just had the same problem.
>>
>> a) the NPV computation is wrong
>> - sum += ds[i] / Math.pow(rate + 1, i);
>> + sum += ds[i] / Math.pow(rate + 1, i+1);
>>
>> I think the computation should actually use the FinanceLib#npv method (which is correct).
>>
>> b) The impl does not support ranges
>>
>> I got a patch fix a) and b), including a test case:
>>
>>
>>
>> Jon, do you want to create the bug ticket? Then I'll add my patch for it.
>>
>> Cheers,
>> Marcel
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Marcel May <ma...@googlemail.com>.
Index: src/java/org/apache/poi/ss/formula/functions/Npv.java
===================================================================
--- src/java/org/apache/poi/ss/formula/functions/Npv.java (revision 1043675)
+++ src/java/org/apache/poi/ss/formula/functions/Npv.java (working copy)
@@ -17,6 +17,7 @@
package org.apache.poi.ss.formula.functions;
+import org.apache.poi.ss.formula.TwoDEval;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
@@ -30,66 +31,30 @@
* income.
*
* @author SPetrakovsky
+ * @author Marcel May
*/
-public final class Npv implements Function2Arg, Function3Arg, Function4Arg {
+public final class Npv implements Function {
-
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
- double result;
- try {
- double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
- double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
- result = evaluate(rate, d1);
- NumericFunction.checkValue(result);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- return new NumberEval(result);
- }
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
- ValueEval arg2) {
- double result;
- try {
- double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
- double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
- double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
- result = evaluate(rate, d1, d2);
- NumericFunction.checkValue(result);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- return new NumberEval(result);
- }
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
- ValueEval arg2, ValueEval arg3) {
- double result;
- try {
- double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
- double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
- double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
- double d3 = NumericFunction.singleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex);
- result = evaluate(rate, d1, d2, d3);
- NumericFunction.checkValue(result);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
- return new NumberEval(result);
- }
-
public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
int nArgs = args.length;
if (nArgs<2) {
return ErrorEval.VALUE_INVALID;
}
- int np = nArgs-1;
- double[] ds = new double[np];
- double result;
- try {
+ double result;
+ try {
+ double[] ds;
+ if (2==nArgs && args[1] instanceof TwoDEval) {
+ // eg A4:A10
+ ds = extractNumbers((TwoDEval) args[1]);
+ } else {
+ // eg A4,A5,...
+ ds = new double[args.length-1];
+ for (int i = 0; i < ds.length; i++) {
+ ds[i] = NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
+ }
+ }
double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
- for (int i = 0; i < ds.length; i++) {
- ds[i] = NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
- }
- result = evaluate(rate, ds);
+ result = FinanceLib.npv(rate, ds);
NumericFunction.checkValue(result);
} catch (EvaluationException e) {
return e.getErrorEval();
@@ -97,11 +62,23 @@
return new NumberEval(result);
}
- private static double evaluate(double rate, double...ds) {
- double sum = 0;
- for (int i = 0; i < ds.length; i++) {
- sum += ds[i] / Math.pow(rate + 1, i);
- }
- return sum;
- }
+ private double[] extractNumbers(final TwoDEval pArg) {
+ // Validate
+ LookupUtils.ValueVector vector = LookupUtils.createVector(pArg);
+ if (null == vector) {
+ throw new RuntimeException("area" + pArg.getWidth() + "x" + pArg.getHeight() + " must be either row or column");
+ }
+ double[] res = new double[vector.getSize()];
+ for (int i = 0; i < res.length; i++) {
+ res[i] = extractDouble(vector.getItem(i));
+ }
+ return res;
+ }
+
+ private double extractDouble(final ValueEval pValue) {
+ if (pValue instanceof NumberEval) {
+ return ((NumberEval) pValue).getNumberValue();
+ }
+ throw new RuntimeException("Can not convert to number: " + pValue);
+ }
}
Index: src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java
===================================================================
--- src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java (revision 0)
+++ src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java (revision 0)
@@ -0,0 +1,64 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import junit.framework.TestCase;
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+
+/**
+ * Tests for {@link Npv}
+ *
+ * @author Marcel May
+ * @see <a href="http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx">Excel Help</a>
+ */
+public final class TestNpv extends TestCase {
+
+ public void testEvaluateInSheetExample2() {
+ HSSFWorkbook wb = new HSSFWorkbook();
+ HSSFSheet sheet = wb.createSheet("Sheet1");
+ HSSFRow row = sheet.createRow(0);
+
+ sheet.createRow(1).createCell(0).setCellValue(0.08d);
+ sheet.createRow(2).createCell(0).setCellValue(-40000d);
+ sheet.createRow(3).createCell(0).setCellValue(8000d);
+ sheet.createRow(4).createCell(0).setCellValue(9200d);
+ sheet.createRow(5).createCell(0).setCellValue(10000d);
+ sheet.createRow(6).createCell(0).setCellValue(12000d);
+ sheet.createRow(7).createCell(0).setCellValue(14500d);
+
+ HSSFCell cell = row.createCell(8);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+ // Enumeration
+ cell.setCellFormula("NPV(A2, A4,A5,A6,A7,A8)+A3");
+ fe.clearAllCachedResultValues();
+ fe.evaluateFormulaCell(cell);
+ double res = cell.getNumericCellValue();
+ assertEquals(1922.06d, Math.round(res * 100d) / 100d);
+
+ // Range
+ cell.setCellFormula("NPV(A2, A4:A8)+A3");
+
+ fe.clearAllCachedResultValues();
+ fe.evaluateFormulaCell(cell);
+ res = cell.getNumericCellValue();
+ assertEquals(1922.06d, Math.round(res * 100d) / 100d);
+ }
+}
Property changes on: src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java
___________________________________________________________________
Added: svn:mime-type
+ text/plain
Added: svn:keywords
+ Date Revision
Added: svn:eol-style
+ native
Re: NPV bug?
Posted by Jon Svede <js...@yahoo.com>.
I just re-read this, so yes, I will create a bug and upload my test file.
Marcel can then attach the patch/test case as needed.
Thanks!
Jon
----- Original Message ----
From: Marcel May <ma...@consol.de>
To: POI Users List <us...@poi.apache.org>
Sent: Wed, December 8, 2010 3:33:54 PM
Subject: Re: NPV bug?
I can confirm this is a bug - just had the same problem.
a) the NPV computation is wrong
- sum += ds[i] / Math.pow(rate + 1, i);
+ sum += ds[i] / Math.pow(rate + 1, i+1);
I think the computation should actually use the FinanceLib#npv method (which
is correct).
b) The impl does not support ranges
I got a patch fix a) and b), including a test case:
Jon, do you want to create the bug ticket? Then I'll add my patch for it.
Cheers,
Marcel
On Dec 7, 2010, at 10:48 PM, Jon Svede wrote:
> Some additional information:
>
> If I enumerate the values in the area in the formula, the evaluation works
> (=NPV(B5,B1,B2,B3,B4) ). However the result doesn't match what Excel has.
>
> Thanks,
>
> Jon
>
>
> ----- Original Message ----
> From: Jon Svede <js...@yahoo.com>
> To: POI Users List <us...@poi.apache.org>
> Sent: Tue, December 7, 2010 9:44:40 AM
> Subject: NPV bug?
>
> I've attached a simple example of my issue but here is the description.
>
> I have a spreadsheet with values (not formulas) in B1:B4. In B5 there is the
> value 0.10.
>
>
> In B6 I have this formula:
>
> =NPV(B5,B1:B4)
>
> This seems to work fine in Excel.
>
> When I try to evaluate this B6 cell, I get the errorCode 15 that I've
>previously
>
>
> e-mailed about. This error isn't thrown overtly, the CellValue from the
> evaluator returns 0 and the errorCode is now set to a non-zero value.
>
>
> When I debug this, it's falling in the NPV.evaluate(ValueEval[] args, int
> srcRowIndex, int srcColumnIndex) method. This will eventually end up in the
> OperandResolver.chooseSingleElementFromAreaInternal(AreaEval ae, int
>srcCellRow,
>
>
> int srcCellCol) which has this if statement in it:
>
> if (ae.isColumn()) {
> if(ae.isRow()) {
> return ae.getRelativeValue(0, 0);
> }
> if(!ae.containsRow(srcCellRow)) {
> throw EvaluationException.invalidValue();
> }
> return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
> }
>
> My error is coming from the throw clause. Interestingly, if I comment that
>out,
>
>
> I get a better error message:
>
> java.lang.IllegalArgumentException: Specified row index (5) is outside the
> allowed range (0..3)
>
> So my question is, is this a bug or is this user error? Separating that out: is
>
> my usage of NPV user error on my part and is that throw statement a bug?
>
> This example of the NPV function is used heavily in the spreadsheet I am trying
>
> to use POI with (and I don't know Excel nearly well enough to have known about
> or how to use the NPV function). I've tried changing it so that the range of
> cells includes B5 but this doesn't change the behavior (meaning that guessing
> about what the proper usage of NPV isn't helping me).
>
> Thanks in advance for any advice or suggestions.
>
> Jon
>
>
>
>
> ----- Original Message ----
> From: Jon Svede <js...@yahoo.com>
> To: POI Users List <us...@poi.apache.org>
> Sent: Tue, December 7, 2010 7:17:17 AM
> Subject: Re: Using FinanceLib
>
> Is this function, the FinanceLib.npv() method, the one used internally by POI
> when a cell in a spreadsheet specifies the NPV function?
>
> Thanks,
>
> Jon
>
>
>
>
>
> ----- Original Message ----
> From: Edward Ivanovic <ed...@gmail.com>
> To: POI Users List <us...@poi.apache.org>
> Sent: Mon, December 6, 2010 4:24:58 PM
> Subject: Re: Using FinanceLib
>
> That's great. Thanks Yegor!
>
>
> On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
>
>> OK, I got the request. The visibility of the FinanceLib class will be
>> raised from default to public.
>>
>> Thanks for raising this topic.
>>
>> Regards,
>> Yegor
>>
>>
>> That's a shame. Why hide a perfectly good Java financial library?
>>>
>>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>>
>>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>>
>>>> That's interesting - it no longer comes up in the classes list on
>>>>> http://poi.apache.org/apidocs/index.html
>>>>> I wonder if the direct page link that you can find via a Google search
>>>>> is
>>>>> an
>>>>> orphaned page.
>>>>>
>>>>>
>>>>> I updated the online apidocs to reflect current POI trunk.
>>>>
>>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>>
>>>>> defined as public:
>>>>> final class FinanceLib {...
>>>>>
>>>>>
>>>>>
>>>>>http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>>a
>>>>>
>>>>>
>>>>> a
>>>>>
>>>>> I think this is why I can't access any methods out of it even though the
>>>>> methods themselves are declared as public.
>>>>>
>>>>>
>>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>>> only. This class can be renamed / re-packaged in future versions of POI
>>>> or
>>>> access
>>>> to its methods can be changed from 'public' to 'default' or less.
>>>>
>>>>
>>>> Yegor
>>>>
>>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>>
>>>>> declared as a public class. Can it be reverted to public again?
>>>>>
>>>>> Thanks
>>>>>
>>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>>> wrote:
>>>>>
>>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>>> in
>>>>>
>>>>>> the
>>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>>> available
>>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>>
>>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>>
>>>>>> fine:
>>>>>>
>>>>>>
>>>>>>
>>>>>>http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>>l
>>>>>>
>>>>>>
>>>>>> l
>>>>>>
>>>>>> Note that in 3.8, the package will change to
>>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>>
>>>>>> Nick
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Marcel May
ConSol* Software GmbH
Franziskanerstr. 38
81669 München
Tel: +49 (0)89-45841-155
Fax: +49-(0)89-45841-111
marcel.may@consol.de
http://www.consol.de
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Marcel May <ma...@consol.de>.
I can confirm this is a bug - just had the same problem.
a) the NPV computation is wrong
- sum += ds[i] / Math.pow(rate + 1, i);
+ sum += ds[i] / Math.pow(rate + 1, i+1);
I think the computation should actually use the FinanceLib#npv method (which is correct).
b) The impl does not support ranges
I got a patch fix a) and b), including a test case:
Re: NPV bug?
Posted by Jon Svede <js...@yahoo.com>.
Some additional information:
If I enumerate the values in the area in the formula, the evaluation works
(=NPV(B5,B1,B2,B3,B4) ). However the result doesn't match what Excel has.
Thanks,
Jon
----- Original Message ----
From: Jon Svede <js...@yahoo.com>
To: POI Users List <us...@poi.apache.org>
Sent: Tue, December 7, 2010 9:44:40 AM
Subject: NPV bug?
I've attached a simple example of my issue but here is the description.
I have a spreadsheet with values (not formulas) in B1:B4. In B5 there is the
value 0.10.
In B6 I have this formula:
=NPV(B5,B1:B4)
This seems to work fine in Excel.
When I try to evaluate this B6 cell, I get the errorCode 15 that I've previously
e-mailed about. This error isn't thrown overtly, the CellValue from the
evaluator returns 0 and the errorCode is now set to a non-zero value.
When I debug this, it's falling in the NPV.evaluate(ValueEval[] args, int
srcRowIndex, int srcColumnIndex) method. This will eventually end up in the
OperandResolver.chooseSingleElementFromAreaInternal(AreaEval ae, int srcCellRow,
int srcCellCol) which has this if statement in it:
if (ae.isColumn()) {
if(ae.isRow()) {
return ae.getRelativeValue(0, 0);
}
if(!ae.containsRow(srcCellRow)) {
throw EvaluationException.invalidValue();
}
return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
}
My error is coming from the throw clause. Interestingly, if I comment that out,
I get a better error message:
java.lang.IllegalArgumentException: Specified row index (5) is outside the
allowed range (0..3)
So my question is, is this a bug or is this user error? Separating that out: is
my usage of NPV user error on my part and is that throw statement a bug?
This example of the NPV function is used heavily in the spreadsheet I am trying
to use POI with (and I don't know Excel nearly well enough to have known about
or how to use the NPV function). I've tried changing it so that the range of
cells includes B5 but this doesn't change the behavior (meaning that guessing
about what the proper usage of NPV isn't helping me).
Thanks in advance for any advice or suggestions.
Jon
----- Original Message ----
From: Jon Svede <js...@yahoo.com>
To: POI Users List <us...@poi.apache.org>
Sent: Tue, December 7, 2010 7:17:17 AM
Subject: Re: Using FinanceLib
Is this function, the FinanceLib.npv() method, the one used internally by POI
when a cell in a spreadsheet specifies the NPV function?
Thanks,
Jon
----- Original Message ----
From: Edward Ivanovic <ed...@gmail.com>
To: POI Users List <us...@poi.apache.org>
Sent: Mon, December 6, 2010 4:24:58 PM
Subject: Re: Using FinanceLib
That's great. Thanks Yegor!
On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
> OK, I got the request. The visibility of the FinanceLib class will be
> raised from default to public.
>
> Thanks for raising this topic.
>
> Regards,
> Yegor
>
>
> That's a shame. Why hide a perfectly good Java financial library?
>>
>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>
>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>
>>> That's interesting - it no longer comes up in the classes list on
>>>> http://poi.apache.org/apidocs/index.html
>>>> I wonder if the direct page link that you can find via a Google search
>>>> is
>>>> an
>>>> orphaned page.
>>>>
>>>>
>>>> I updated the online apidocs to reflect current POI trunk.
>>>
>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>
>>>> defined as public:
>>>> final class FinanceLib {...
>>>>
>>>>
>>>>
>>>>http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>
>>>>
>>>>a
>>>>
>>>> I think this is why I can't access any methods out of it even though the
>>>> methods themselves are declared as public.
>>>>
>>>>
>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>> only. This class can be renamed / re-packaged in future versions of POI
>>> or
>>> access
>>> to its methods can be changed from 'public' to 'default' or less.
>>>
>>>
>>> Yegor
>>>
>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>
>>>> declared as a public class. Can it be reverted to public again?
>>>>
>>>> Thanks
>>>>
>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>> wrote:
>>>>
>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>> in
>>>>
>>>>> the
>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>> available
>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>
>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>
>>>>> fine:
>>>>>
>>>>>
>>>>>
>>>>>http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>
>>>>>
>>>>>l
>>>>>
>>>>> Note that in 3.8, the package will change to
>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: NPV bug?
Posted by Yegor Kozlov <ye...@dinom.ru>.
Sounds like a bug. Can you create a new bug in bugzilla, and upload the
file and a sample code that shows the problem? Ideally it would be a
junit test case.
Yegor
On 12/7/2010 7:44 PM, Jon Svede wrote:
> I've attached a simple example of my issue but here is the description.
>
> I have a spreadsheet with values (not formulas) in B1:B4. In B5 there is the
> value 0.10.
>
>
> In B6 I have this formula:
>
> =NPV(B5,B1:B4)
>
> This seems to work fine in Excel.
>
> When I try to evaluate this B6 cell, I get the errorCode 15 that I've previously
> e-mailed about. This error isn't thrown overtly, the CellValue from the
> evaluator returns 0 and the errorCode is now set to a non-zero value.
>
>
> When I debug this, it's falling in the NPV.evaluate(ValueEval[] args, int
> srcRowIndex, int srcColumnIndex) method. This will eventually end up in the
> OperandResolver.chooseSingleElementFromAreaInternal(AreaEval ae, int srcCellRow,
> int srcCellCol) which has this if statement in it:
>
> if (ae.isColumn()) {
> if(ae.isRow()) {
> return ae.getRelativeValue(0, 0);
> }
> if(!ae.containsRow(srcCellRow)) {
> throw EvaluationException.invalidValue();
> }
> return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
> }
>
> My error is coming from the throw clause. Interestingly, if I comment that out,
> I get a better error message:
>
> java.lang.IllegalArgumentException: Specified row index (5) is outside the
> allowed range (0..3)
>
> So my question is, is this a bug or is this user error? Separating that out: is
> my usage of NPV user error on my part and is that throw statement a bug?
>
> This example of the NPV function is used heavily in the spreadsheet I am trying
> to use POI with (and I don't know Excel nearly well enough to have known about
> or how to use the NPV function). I've tried changing it so that the range of
> cells includes B5 but this doesn't change the behavior (meaning that guessing
> about what the proper usage of NPV isn't helping me).
>
> Thanks in advance for any advice or suggestions.
>
> Jon
>
>
>
>
> ----- Original Message ----
> From: Jon Svede<js...@yahoo.com>
> To: POI Users List<us...@poi.apache.org>
> Sent: Tue, December 7, 2010 7:17:17 AM
> Subject: Re: Using FinanceLib
>
> Is this function, the FinanceLib.npv() method, the one used internally by POI
> when a cell in a spreadsheet specifies the NPV function?
>
> Thanks,
>
> Jon
>
>
>
>
>
> ----- Original Message ----
> From: Edward Ivanovic<ed...@gmail.com>
> To: POI Users List<us...@poi.apache.org>
> Sent: Mon, December 6, 2010 4:24:58 PM
> Subject: Re: Using FinanceLib
>
> That's great. Thanks Yegor!
>
>
> On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>
>> OK, I got the request. The visibility of the FinanceLib class will be
>> raised from default to public.
>>
>> Thanks for raising this topic.
>>
>> Regards,
>> Yegor
>>
>>
>> That's a shame. Why hide a perfectly good Java financial library?
>>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>>
>>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>> That's interesting - it no longer comes up in the classes list on
>>>>> http://poi.apache.org/apidocs/index.html
>>>>> I wonder if the direct page link that you can find via a Google search
>>>>> is
>>>>> an
>>>>> orphaned page.
>>>>>
>>>>>
>>>>> I updated the online apidocs to reflect current POI trunk.
>>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>>
>>>>> defined as public:
>>>>> final class FinanceLib {...
>>>>>
>>>>>
>>>>>
>>>>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>>
>>>>> a
>>>>>
>>>>> I think this is why I can't access any methods out of it even though the
>>>>> methods themselves are declared as public.
>>>>>
>>>>>
>>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>>> only. This class can be renamed / re-packaged in future versions of POI
>>>> or
>>>> access
>>>> to its methods can be changed from 'public' to 'default' or less.
>>>>
>>>>
>>>> Yegor
>>>>
>>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>>
>>>>> declared as a public class. Can it be reverted to public again?
>>>>>
>>>>> Thanks
>>>>>
>>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>>> wrote:
>>>>>
>>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>>> in
>>>>>
>>>>>> the
>>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>>> available
>>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>>
>>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>>
>>>>>> fine:
>>>>>>
>>>>>>
>>>>>>
>>>>>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>>
>>>>>> l
>>>>>>
>>>>>> Note that in 3.8, the package will change to
>>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>>
>>>>>> Nick
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
NPV bug?
Posted by Jon Svede <js...@yahoo.com>.
I've attached a simple example of my issue but here is the description.
I have a spreadsheet with values (not formulas) in B1:B4. In B5 there is the
value 0.10.
In B6 I have this formula:
=NPV(B5,B1:B4)
This seems to work fine in Excel.
When I try to evaluate this B6 cell, I get the errorCode 15 that I've previously
e-mailed about. This error isn't thrown overtly, the CellValue from the
evaluator returns 0 and the errorCode is now set to a non-zero value.
When I debug this, it's falling in the NPV.evaluate(ValueEval[] args, int
srcRowIndex, int srcColumnIndex) method. This will eventually end up in the
OperandResolver.chooseSingleElementFromAreaInternal(AreaEval ae, int srcCellRow,
int srcCellCol) which has this if statement in it:
if (ae.isColumn()) {
if(ae.isRow()) {
return ae.getRelativeValue(0, 0);
}
if(!ae.containsRow(srcCellRow)) {
throw EvaluationException.invalidValue();
}
return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
}
My error is coming from the throw clause. Interestingly, if I comment that out,
I get a better error message:
java.lang.IllegalArgumentException: Specified row index (5) is outside the
allowed range (0..3)
So my question is, is this a bug or is this user error? Separating that out: is
my usage of NPV user error on my part and is that throw statement a bug?
This example of the NPV function is used heavily in the spreadsheet I am trying
to use POI with (and I don't know Excel nearly well enough to have known about
or how to use the NPV function). I've tried changing it so that the range of
cells includes B5 but this doesn't change the behavior (meaning that guessing
about what the proper usage of NPV isn't helping me).
Thanks in advance for any advice or suggestions.
Jon
----- Original Message ----
From: Jon Svede <js...@yahoo.com>
To: POI Users List <us...@poi.apache.org>
Sent: Tue, December 7, 2010 7:17:17 AM
Subject: Re: Using FinanceLib
Is this function, the FinanceLib.npv() method, the one used internally by POI
when a cell in a spreadsheet specifies the NPV function?
Thanks,
Jon
----- Original Message ----
From: Edward Ivanovic <ed...@gmail.com>
To: POI Users List <us...@poi.apache.org>
Sent: Mon, December 6, 2010 4:24:58 PM
Subject: Re: Using FinanceLib
That's great. Thanks Yegor!
On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
> OK, I got the request. The visibility of the FinanceLib class will be
> raised from default to public.
>
> Thanks for raising this topic.
>
> Regards,
> Yegor
>
>
> That's a shame. Why hide a perfectly good Java financial library?
>>
>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>
>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>
>>> That's interesting - it no longer comes up in the classes list on
>>>> http://poi.apache.org/apidocs/index.html
>>>> I wonder if the direct page link that you can find via a Google search
>>>> is
>>>> an
>>>> orphaned page.
>>>>
>>>>
>>>> I updated the online apidocs to reflect current POI trunk.
>>>
>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>
>>>> defined as public:
>>>> final class FinanceLib {...
>>>>
>>>>
>>>>
>>>>http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>
>>>>a
>>>>
>>>> I think this is why I can't access any methods out of it even though the
>>>> methods themselves are declared as public.
>>>>
>>>>
>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>> only. This class can be renamed / re-packaged in future versions of POI
>>> or
>>> access
>>> to its methods can be changed from 'public' to 'default' or less.
>>>
>>>
>>> Yegor
>>>
>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>
>>>> declared as a public class. Can it be reverted to public again?
>>>>
>>>> Thanks
>>>>
>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>> wrote:
>>>>
>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>> in
>>>>
>>>>> the
>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>> available
>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>
>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>
>>>>> fine:
>>>>>
>>>>>
>>>>>
>>>>>http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>
>>>>>l
>>>>>
>>>>> Note that in 3.8, the package will change to
>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Using FinanceLib
Posted by Jon Svede <js...@yahoo.com>.
Is this function, the FinanceLib.npv() method, the one used internally by POI
when a cell in a spreadsheet specifies the NPV function?
Thanks,
Jon
----- Original Message ----
From: Edward Ivanovic <ed...@gmail.com>
To: POI Users List <us...@poi.apache.org>
Sent: Mon, December 6, 2010 4:24:58 PM
Subject: Re: Using FinanceLib
That's great. Thanks Yegor!
On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
> OK, I got the request. The visibility of the FinanceLib class will be
> raised from default to public.
>
> Thanks for raising this topic.
>
> Regards,
> Yegor
>
>
> That's a shame. Why hide a perfectly good Java financial library?
>>
>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>
>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>
>>> That's interesting - it no longer comes up in the classes list on
>>>> http://poi.apache.org/apidocs/index.html
>>>> I wonder if the direct page link that you can find via a Google search
>>>> is
>>>> an
>>>> orphaned page.
>>>>
>>>>
>>>> I updated the online apidocs to reflect current POI trunk.
>>>
>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>
>>>> defined as public:
>>>> final class FinanceLib {...
>>>>
>>>>
>>>>
>>>>http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>a
>>>>
>>>> I think this is why I can't access any methods out of it even though the
>>>> methods themselves are declared as public.
>>>>
>>>>
>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>> only. This class can be renamed / re-packaged in future versions of POI
>>> or
>>> access
>>> to its methods can be changed from 'public' to 'default' or less.
>>>
>>>
>>> Yegor
>>>
>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>
>>>> declared as a public class. Can it be reverted to public again?
>>>>
>>>> Thanks
>>>>
>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>> wrote:
>>>>
>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>> in
>>>>
>>>>> the
>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>> available
>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>
>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>
>>>>> fine:
>>>>>
>>>>>
>>>>>
>>>>>http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>l
>>>>>
>>>>> Note that in 3.8, the package will change to
>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Using FinanceLib
Posted by Edward Ivanovic <ed...@gmail.com>.
That's great. Thanks Yegor!
On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
> OK, I got the request. The visibility of the FinanceLib class will be
> raised from default to public.
>
> Thanks for raising this topic.
>
> Regards,
> Yegor
>
>
> That's a shame. Why hide a perfectly good Java financial library?
>>
>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>>
>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>
>>> That's interesting - it no longer comes up in the classes list on
>>>> http://poi.apache.org/apidocs/index.html
>>>> I wonder if the direct page link that you can find via a Google search
>>>> is
>>>> an
>>>> orphaned page.
>>>>
>>>>
>>>> I updated the online apidocs to reflect current POI trunk.
>>>
>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>
>>>> defined as public:
>>>> final class FinanceLib {...
>>>>
>>>>
>>>>
>>>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>
>>>> I think this is why I can't access any methods out of it even though the
>>>> methods themselves are declared as public.
>>>>
>>>>
>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>> only. This class can be renamed / re-packaged in future versions of POI
>>> or
>>> access
>>> to its methods can be changed from 'public' to 'default' or less.
>>>
>>>
>>> Yegor
>>>
>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>
>>>> declared as a public class. Can it be reverted to public again?
>>>>
>>>> Thanks
>>>>
>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>>> wrote:
>>>>
>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>> in
>>>>
>>>>> the
>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>> available
>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>
>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>
>>>>> fine:
>>>>>
>>>>>
>>>>>
>>>>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>
>>>>> Note that in 3.8, the package will change to
>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Using FinanceLib
Posted by Yegor Kozlov <ye...@dinom.ru>.
OK, I got the request. The visibility of the FinanceLib class will be
raised from default to public.
Thanks for raising this topic.
Regards,
Yegor
> That's a shame. Why hide a perfectly good Java financial library?
>
> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<ye...@dinom.ru> wrote:
>
>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>
>>> That's interesting - it no longer comes up in the classes list on
>>> http://poi.apache.org/apidocs/index.html
>>> I wonder if the direct page link that you can find via a Google search is
>>> an
>>> orphaned page.
>>>
>>>
>> I updated the online apidocs to reflect current POI trunk.
>>
>> Looking in the svn code repository, the FinanceLib class is no longer
>>> defined as public:
>>> final class FinanceLib {...
>>>
>>>
>>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>
>>> I think this is why I can't access any methods out of it even though the
>>> methods themselves are declared as public.
>>>
>>>
>> It was done intentionally. FinanceLib is intended for POI internal use
>> only. This class can be renamed / re-packaged in future versions of POI or
>> access
>> to its methods can be changed from 'public' to 'default' or less.
>>
>>
>> Yegor
>>
>> As you can see, the javadoc you send me shows the previous FinanceLib
>>> declared as a public class. Can it be reverted to public again?
>>>
>>> Thanks
>>>
>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>>> wrote:
>>>
>>> Hi, I'd like to use the pv() function that used to be a static method in
>>>>> the
>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>> available
>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>
>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>> fine:
>>>>
>>>>
>>>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>
>>>> Note that in 3.8, the package will change to
>>>> org.apache.poi.ss.formula.functions for this class
>>>>
>>>> Nick
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Using FinanceLib
Posted by Edward Ivanovic <ed...@gmail.com>.
That's a shame. Why hide a perfectly good Java financial library?
On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov <ye...@dinom.ru> wrote:
> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>
>> That's interesting - it no longer comes up in the classes list on
>> http://poi.apache.org/apidocs/index.html
>> I wonder if the direct page link that you can find via a Google search is
>> an
>> orphaned page.
>>
>>
> I updated the online apidocs to reflect current POI trunk.
>
> Looking in the svn code repository, the FinanceLib class is no longer
>> defined as public:
>> final class FinanceLib {...
>>
>>
>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>
>> I think this is why I can't access any methods out of it even though the
>> methods themselves are declared as public.
>>
>>
> It was done intentionally. FinanceLib is intended for POI internal use
> only. This class can be renamed / re-packaged in future versions of POI or
> access
> to its methods can be changed from 'public' to 'default' or less.
>
>
> Yegor
>
> As you can see, the javadoc you send me shows the previous FinanceLib
>> declared as a public class. Can it be reverted to public again?
>>
>> Thanks
>>
>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com>
>> wrote:
>>
>> Hi, I'd like to use the pv() function that used to be a static method in
>>>
>>>> the
>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>> available
>>>> (seems the entire FinanceLib class is no longer public).
>>>>
>>>> The methods on FinanceLib are all public static, so you ought to be
>>> fine:
>>>
>>>
>>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>
>>> Note that in 3.8, the package will change to
>>> org.apache.poi.ss.formula.functions for this class
>>>
>>> Nick
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Using FinanceLib
Posted by Yegor Kozlov <ye...@dinom.ru>.
On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
> That's interesting - it no longer comes up in the classes list on
> http://poi.apache.org/apidocs/index.html
> I wonder if the direct page link that you can find via a Google search is an
> orphaned page.
>
I updated the online apidocs to reflect current POI trunk.
> Looking in the svn code repository, the FinanceLib class is no longer
> defined as public:
> final class FinanceLib {...
>
> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>
> I think this is why I can't access any methods out of it even though the
> methods themselves are declared as public.
>
It was done intentionally. FinanceLib is intended for POI internal use only. This class can be renamed / re-packaged in future versions of POI or access
to its methods can be changed from 'public' to 'default' or less.
Yegor
> As you can see, the javadoc you send me shows the previous FinanceLib
> declared as a public class. Can it be reverted to public again?
>
> Thanks
>
> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<ni...@alfresco.com> wrote:
>
>> Hi, I'd like to use the pv() function that used to be a static method in
>>> the
>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer available
>>> (seems the entire FinanceLib class is no longer public).
>>>
>> The methods on FinanceLib are all public static, so you ought to be fine:
>>
>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>
>> Note that in 3.8, the package will change to
>> org.apache.poi.ss.formula.functions for this class
>>
>> Nick
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Using FinanceLib
Posted by Edward Ivanovic <ed...@gmail.com>.
That's interesting - it no longer comes up in the classes list on
http://poi.apache.org/apidocs/index.html
I wonder if the direct page link that you can find via a Google search is an
orphaned page.
Looking in the svn code repository, the FinanceLib class is no longer
defined as public:
final class FinanceLib {...
http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
I think this is why I can't access any methods out of it even though the
methods themselves are declared as public.
As you can see, the javadoc you send me shows the previous FinanceLib
declared as a public class. Can it be reverted to public again?
Thanks
On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch <ni...@alfresco.com> wrote:
> Hi, I'd like to use the pv() function that used to be a static method in
>> the
>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer available
>> (seems the entire FinanceLib class is no longer public).
>>
>
> The methods on FinanceLib are all public static, so you ought to be fine:
>
> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>
> Note that in 3.8, the package will change to
> org.apache.poi.ss.formula.functions for this class
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Using FinanceLib
Posted by Nick Burch <ni...@alfresco.com>.
> Hi, I'd like to use the pv() function that used to be a static method in the
> FinanceLib class. I just upgraded to POI 3.7 and it's no longer available
> (seems the entire FinanceLib class is no longer public).
The methods on FinanceLib are all public static, so you ought to be fine:
http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
Note that in 3.8, the package will change to
org.apache.poi.ss.formula.functions for this class
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org