Uploaded image for project: 'ZK Spreadsheet'
  1. ZK Spreadsheet
  2. ZSS-269

error evaluating some financial formulas

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: 2.5.0, 3.0.0 RC
    • Fix Version/s: 3.0.0 RC
    • Component/s: ZSS Component
    • Labels:

      Description

      problem description:

      not found formula:
      ISPMT, LOGEST, MIRR, VDB

      java.lang.AssertionError: =ISPMT(B71/12,C71,D71*12,E71) failed, at row 70 in xsheet: formula-financial
      Expected: "-64814.81"
           but: was "#NAME?"
      	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
      





      error in evaluating PV(0.08/12,20*12,500, ,0)

      java.lang.AssertionError: =PV(0.08/12,20*12,500, ,0) failed, at row 99 in xsheet: formula-financial
      Expected: "-59777.15"
           but: was "Unexpected arg eval type (org.zkoss.poi.ss.formula.eval.MissingArgEval)"
      	at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:20)
      	at org.junit.Assert.assertThat(Assert.java:865)
      	at org.junit.rules.ErrorCollector$1.call(ErrorCollector.java:65)
      	at org.junit.rules.ErrorCollector.checkSucceeds(ErrorCollector.java:78)
      	at org.junit.rules.ErrorCollector.checkThat(ErrorCollector.java:63)
      	at zss.test.FormulaTest.testFormulaByRangesInSheet(FormulaTest.java:158)
      	at zss.test.FormulaTest.testFinancial(FormulaTest.java:96)
      	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      

      Exception stack trace:

      嚴重的: >>java.lang.RuntimeException: Unexpected arg eval type (org.zkoss.poi.ss.formula.eval.MissingArgEval)
      >>	at org.zkoss.poi.ss.formula.eval.OperandResolver.coerceValueToDouble(OperandResolver.java:233)
      >>	at org.zkoss.poi.ss.formula.functions.NumericFunction.singleOperandEvaluate(NumericFunction.java:38)
      >>	at org.zkoss.poi.ss.formula.functions.FinanceFunction.evaluate(FinanceFunction.java:53)
      >>	at org.zkoss.poi.ss.formula.functions.FinanceFunction.evaluate(FinanceFunction.java:69)
      >>	at org.zkoss.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
      >>	at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:539)
      >>	at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:316)
      >>	at org.zkoss.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:255)
      >>	at org.zkoss.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:266)
      >>	at org.zkoss.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluate(XSSFFormulaEvaluator.java:119)
      >>	at org.zkoss.zss.model.sys.impl.BookHelper.evaluate(BookHelper.java:476)
      >>	at org.zkoss.zss.model.sys.impl.BookHelper.getFormatText(BookHelper.java:1018)
      >>	at org.zkoss.zss.ui.impl.XUtils.getFormatText(XUtils.java:355)
      >>	at org.zkoss.zss.ui.impl.CellFormatHelper.getHtmlStyle(CellFormatHelper.java:89)
      >>	at org.zkoss.zss.ui.Spreadsheet$ExtraCtrl.getCellAttr(Spreadsheet.java:3066)
      >>	at org.zkoss.zss.ui.Spreadsheet$ExtraCtrl.getRangeAttrs(Spreadsheet.java:2940)
      >>	at org.zkoss.zss.ui.Spreadsheet.responseUpdateCell(Spreadsheet.java:2609)
      >>	at org.zkoss.zss.ui.Spreadsheet.updateCell(Spreadsheet.java:2557)
      >>	at org.zkoss.zss.ui.Spreadsheet.access$12(Spreadsheet.java:2506)
      >>	at org.zkoss.zss.ui.Spreadsheet$InnerDataListener.onContentChange(Spreadsheet.java:2266)
      >>	at org.zkoss.zss.ui.Spreadsheet$InnerDataListener.access$6(Spreadsheet.java:2256)
      >>	at org.zkoss.zss.ui.Spreadsheet$InnerDataListener$7.onEvent(Spreadsheet.java:2015)
      >>	at org.zkoss.zss.engine.event.EventDispatchListener.onEvent(EventDispatchListener.java:43)
      





      some formula evaluation results are slightly different from expected (one more space character than expected):
      DDB, FV, NPV, SLN, SYD, PMT, PPMT

      java.lang.AssertionError: =DDB(B43,C43,D43*365,1) failed, at row 42 in xsheet: formula-financial
      Expected: "1.32"
           but: was "1.32 "
      
      java.lang.AssertionError: =FV(B58/12,C58,D58,E58,F58) failed, at row 57 in xsheet: formula-financial
      Expected: "2581.40"
           but: was "2581.40 "
      
      java.lang.AssertionError: =NPV(0.08,8000,9200,10000,12000,14500) failed, at row 87 in xsheet: formula-financial
      Expected: "41922.06"
           but: was "41922.06 "
      
      java.lang.AssertionError: =PMT(0.08/12,10,10000) failed, at row 89 in xsheet: formula-financial
      Expected: "-1037.03"
           but: was "-1037.03 "
      
      
      java.lang.AssertionError: =PPMT(0.1/12,1,2*12,2000) failed, at row 91 in xsheet: formula-financial
      Expected: "-75.62"
           but: was "-75.62 "
      

      reproducing steps

      run automatic test case FormulaTest of the project zss.test of zkspreadsheet or attached file

        Activity

        Hide
        hawk hawk added a comment -

        For missing arguments problem:

        Root Cause:

        Missing optional arguments (MissingArgEval) causes throwing a runtime exception.

        Solution:

        When an error happens during evaluating a formula, we shall throw EvaluationException with a proper message.
        Because callers will handle EvaluationException properly.

        We also catch all exceptions and set the evaluation result to #VALUE! instead of throwing them.

        Show
        hawk hawk added a comment - For missing arguments problem: Root Cause: Missing optional arguments ( MissingArgEval ) causes throwing a runtime exception. Solution: When an error happens during evaluating a formula, we shall throw EvaluationException with a proper message. Because callers will handle EvaluationException properly. We also catch all exceptions and set the evaluation result to #VALUE! instead of throwing them.
        Hide
        hawk hawk added a comment - - edited

        For those formulas that have slightly different evaluation result with expected:

        Root Cause:

        Excel cell format setting causes the difference.

        Solution:

        Change test file's format setting. Because the evaluation value is correct. We treat the slight difference on format as a testing case design issue. Just change the cell format.


        Change to the 1st format.
        The 4th format causes the result with one space character more.

        Show
        hawk hawk added a comment - - edited For those formulas that have slightly different evaluation result with expected: Root Cause: Excel cell format setting causes the difference. Solution: Change test file's format setting. Because the evaluation value is correct. We treat the slight difference on format as a testing case design issue. Just change the cell format. Change to the 1st format. The 4th format causes the result with one space character more.
        Hide
        hawk hawk added a comment - - edited

        For those not found formulas : ISPMT, LOGEST, MIRR, VDB

        Root Cause:

        not supported in specification.

        Show
        hawk hawk added a comment - - edited For those not found formulas : ISPMT, LOGEST, MIRR, VDB Root Cause: not supported in specification.

          People

          • Assignee:
            hawk hawk
            Reporter:
            hawk hawk
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: