Bug Hunting Session
Bug 100749 - ODDFYIELD and ODDFPRICE function not working
Summary: ODDFYIELD and ODDFPRICE function not working
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 100769 (view as bug list)
Depends on:
Blocks: Calc-Function-missingODFF1.2
  Show dependency treegraph
 
Reported: 2016-07-04 06:37 UTC by raal
Modified: 2018-09-10 10:22 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
test file (9.03 KB, application/vnd.openxmlformats-officedocument.spreadsheetxml)
2016-07-04 06:37 UTC, raal
Details

Note You need to log in before you can comment on or make changes to this bug.
Description raal 2016-07-04 06:37:49 UTC
Created attachment 126049 [details]
test file

open attached file 
ctrl+shift+F9 - recalculation
see the cell B2 - #VALUE!


formula =ODDFYIELD(D1,E1,F1,G1,H1,I1,J1,K1,L1)  works in excel, but in Calc B2=#VALUE!
Comment 1 Winfried Donkers (retired) 2016-07-07 09:29:08 UTC
*** Bug 100769 has been marked as a duplicate of this bug. ***
Comment 2 Winfried Donkers (retired) 2016-07-07 09:36:21 UTC
All calls to ODDFYIELD -and ODDFPRICE- will return an error.
These functions have been disabled at 26 September 2001, because of 'faulty algorithm'.

1. It seems that these functions have not been used since then, given the absence of bug reports.
2. Revitalising these functions may mean that an Add-in function (compatible with Excel) and an ODFF1.2 function will be needed. I have not studied possible differences in definitions (yet).
3. I cannot find what is supposed to be faulty with the algorithms.

@Eike: do you remember anything about these functions and have a suggestion with regard to fixing?
Comment 3 Eike Rathke 2016-07-12 15:56:32 UTC
I don't have details about what or how faulty the algorithm was, and the bug mentioned in the original source code unfortunately was in the StarDivision/Sun internal bug tracker and not public. We could only reimplement from scratch.
My guess is that once a correct algorithm is found then it would not be necessary to have separate functions for the Add-In and ODFF purposes.

ODDFPRICE maths are given in
https://support.office.com/en-US/article/ODDFPRICE-function-D7D664A8-34DF-4233-8D2B-922BCF6A69E1
For ODDFYIELD see
https://support.office.com/en-US/article/ODDFYIELD-function-66BC8B7B-6501-4C93-9CE3-2FD16220FE37
that claims to use Newton interval search on ODDFPRICE function results.
Comment 4 Winfried Donkers (retired) 2016-07-13 06:11:08 UTC
I will add new ODFF functions ODDFYIELD and ODDFPRICE.

Don't know yet if the current Add-In functions are best renamed (ODDFYIELD_EXCEL2003, ODDFPRICE_EXCEL2003), removed (and loosing the xls-interoperabiltity) or other.

I will study the mathematics of these financial functions first, in ODFF1.2, MS support and in the mathematical/financial 'world'.
Comment 5 Winfried Donkers (retired) 2017-07-18 07:18:42 UTC
The documentation on these functions is sparse and ambiguous, the functions are implemented as Add-In functions for interoperability with Excel, Excel has known issues with these functions, these functions have been deactivated in 2004 and nobody has complained about it yet, so I work on it with a low priority.
Comment 6 Winfried Donkers (retired) 2018-09-03 10:03:03 UTC
I can't get it to produce the same results as Excel produces, see comment#5 for reasons.
Also, the odd first period calculations differ between countries/regions. The calculation Excel uses is useful only in the USA.

I suggest to remove these 2 functions from Calc.
No user filed a bug since the code was removed in 2001.
Making the functions fully compatible with Excel means that we have to produced documented incorrect results in some cases; I don't think we want this.
Comment 7 Xisco Faulí 2018-09-10 09:48:57 UTC
> I suggest to remove these 2 functions from Calc.
> No user filed a bug since the code was removed in 2001.
> Making the functions fully compatible with Excel means that we have to
> produced documented incorrect results in some cases; I don't think we want
> this.

@Raal, do you agree with this solution ?
Comment 8 raal 2018-09-10 10:22:38 UTC
Yes, of course. Thank you Winfried for the analyze.