xref: /trunk/audit/openformula-function-gaps/function-implementation-roadmap.md (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
1<!--
2 Licensed to the Apache Software Foundation (ASF) under one
3 or more contributor license agreements.  See the NOTICE file
4 distributed with this work for additional information
5 regarding copyright ownership.  The ASF licenses this file
6 to you under the Apache License, Version 2.0 (the
7 "License"); you may not use this file except in compliance
8 with the License.  You may obtain a copy of the License at
9
10   http://www.apache.org/licenses/LICENSE-2.0
11
12 Unless required by applicable law or agreed to in writing,
13 software distributed under the License is distributed on an
14 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 KIND, either express or implied.  See the License for the
16 specific language governing permissions and limitations
17 under the License.
18-->
19
20OpenFormula Function Compatibility Gap Audit — Implementation Roadmap
21=====================================================================
22
23Branch: openformula
24Starting commit: 6a2f742073
25Audit type: Read-only source inspection (roadmap is a proposal, not committed work)
26
27This document proposes an implementation sequence for the 15 audited
28functions, grouping related work where appropriate. No production
29code changes have been made.
30
31Updated 2026-11-18 to match the code after the post-merge review: the branch
32tip did not compile and several H.1-H.9 changes were wrong; the fixes are
33noted per group. The H.x regression rows were replaced by 99 rows checked
34against Excel 16 (end of test/odf-validate/calcval-cases14.tsv), which match
35on macOS and Linux builds.
36
37Group 1 — Byte-Oriented Text Functions (FINDB, REPLACEB, SEARCHB) — COMPLETED
38--------------------------------------------------------------------------------
39
40Status: Implemented in H.2.
41
42Changes:
43
44  1. Added SC_OPCODE_FINDB=412, SC_OPCODE_SEARCHB=413, SC_OPCODE_REPLACEB=414
45     to compiler.hrc. Existing opcodes (BITAND=404 through IFERROR=411) were
46     NOT renumbered — new opcodes were added at the end of the function range.
47     (The earlier note that XLS files store 8192 + opcode was wrong:
48     TokenPool::nScTokenOff in tokstack.cxx is an in-memory offset.)
49  2. Added ocFindB, ocSearchB, ocReplaceB to opcode.hxx enum.
50  3. Registered function names in core_resource.src (ODF and Excel sections,
51     plus localized en-US text).
52  4. Added HID_FUNC_FINDB, HID_FUNC_SEARCHB, HID_FUNC_REPLACEB to helpids.h
53     and hidother.src.
54  5. Added dispatch cases in interpr4.cxx.
55  6. Implemented ScFindB(), ScSearchB(), ScReplaceB() in interpr1.cxx
56     using DBCS byte semantics (consistent with existing LENB/LEFTB/RIGHTB/
57     MIDB). Added lcl_BytePosToCharIndex() and lcl_CharIndexToBytePos()
58     helpers for byte↔character position conversion.
59     2026-11-18: ScFindB() and ScSearchB() popped the two text arguments in
60     the wrong order (FINDB("b";"abc") searched "b" for "abc"); fixed.
61     xls: now BIFF 205-207 both ways (xlformula.cxx saFuncTable_2), and
62     Excel 16 calculates FINDB from an AOO xls; xlsx: read
63     (formulabase.cxx, ODF names FINDB/SEARCHB/REPLACEB).
64  7. Regression rows in calcval-cases14.tsv (FINDB.*, SEARCHB.*,
65      REPLACEB.*; rewritten 2026-11-18).
66  8. Added Function Wizard documentation in main/sc/source/ui/src/scfuncs.src
67      (SC_OPCODE_FINDB, SC_OPCODE_SEARCHB, SC_OPCODE_REPLACEB resource
68      blocks with parameter names and descriptions).
69
70Byte semantics: DBCS model (1 byte for ASCII/non-DBCS chars, 2 bytes for
71  DBCS chars per IsDBCS()). Consistent with existing LENB/LEFTB/RIGHTB/
72  MIDB implementations. Byte positions are 1-based. Partial DBCS character
73  boundaries are avoided in REPLACEB by scanning forward byte-by-byte.
74
75Rationale: These three functions were entirely missing from the byte-
76  oriented text function family. Now implemented following the existing
77  B-function patterns.
78
79Group 2 — Date/Time Compatibility (DATEDIF, ISOWEEKNUM) — DATEDIF COMPLETED
80--------------------------------------------------------------------------------
81
82DATEDIF Status: Implemented in H.3.
83
84Changes:
85
86  1. Added SC_OPCODE_DATEDIF = 415 to compiler.hrc. Existing opcodes
87     unchanged. (STOP_2_PAR is now 420 and LAST_OPCODE_ID 419.)
88  2. Added ocDateDif to opcode.hxx enum.
89  3. Registered "DATEDIF" name in core_resource.src (ODF and Excel sections,
90     plus localized en-US text).
91  4. Added HID_FUNC_DATEDIF to helpids.h and hidother.src.
92  5. Added dispatch case in interpr4.cxx.
93  6. Implemented ScInterpreter::ScDateDif() in interpr2.cxx
94     implementing ODF 1.4 §6.10.3 with all six formats (y, m, d, md, ym, yd).
95     2026-11-18: rewritten to Excel's behavior where ODF is silent (Jim's
96     decision): start after end is an error, the format ignores case, "md"
97     and "yd" follow Excel; "yd" was wrong before. See
98     datedif-semantic-design.md.
99  7. Added Function Wizard entry in scfuncs.src (Start_date, End_date, Format
100     parameters, ID_FUNCTION_GRP_DATETIME group).
101  8. Regression rows DATEDIF.* in calcval-cases14.tsv (rewritten
102     2026-11-18).
103  9. Fixed BIFF5/BIFF7 import mapping in xlformula.cxx:
104     changed `ocNoName` → `ocDateDif` for function ID 351 (DATEDIF).
105     2026-11-18: the import-only flag was dropped, so DATEDIF is written
106     to xls too (Excel 16 calculates it); xlsx reads DATEDIF.
107
108Semantic design: See datedif-semantic-design.md for detailed algorithm
109specification including reverse-date policy, time fraction handling, and
110month/year boundary semantics.
111
112ISOWEEKNUM: COMPLETED in H.5 (commit on openformula branch).
113
114Changes:
115
116  1. Added SC_OPCODE_ISOWEEKNUM = 416 (compiler.hrc, opcode.hxx).
117  2. Added ScIsoWeekNum() in interpr2.cxx using Date::GetWeekOfYear(MONDAY, 4).
118  3. Added dispatch case ocIsoWeekNum in interpr4.cxx.
119  4. Registered ISOWEEKNUM name in core_resource.src (ODF and Excel namespaces).
120     The ODF list also has ISOWEEKNUM for ocWeek, which is how AOO writes
121     WEEKNUM(d;mode); on reading, the first entry wins. 2026-11-18:
122     FormulaCompiler::Factor turns an ODF ISOWEEKNUM with one argument into
123     ocIsoWeekNum, so files from other applications load; two arguments
124     stay WEEKNUM.
125  5. Added function wizard resource in scfuncs.src (1 argument, no mode;
126     its ExtraData had one number too many, fixed 2026-11-18).
127  6. Added HID_FUNC_ISOWEEKNUM in helpids.h and hidother.src.
128  7. Added help topic func_isoweeknum.xhp, registered in makefile and 04060102.xhp.
129  8. Calcval rows ISOWEEKNUM.* (rewritten 2026-11-18; several SG.ISO
130     expectations were wrong, e.g. week 1 for Sunday 2024-12-29).
131     xls: written as `_xlfn.ISOWEEKNUM` (2026-11-18). xlsx `_xlfn.ISOWEEKNUM`
132     is read too (2026-11-18).
133  9. WEEKNUM (ScGetWeekOfYear) unchanged: retains 2-arg SUN/MON mode.
134
135Rationale: ISOWEEKNUM needed separate opcode and evaluator for ISO 8601
136  compliance (1 arg, Monday start, week 1 = first Thursday). Now resolved.
137
138Group 3 — Financial Odd-Period Functions (ODDFPRICE, ODDFYIELD)
139---------------------------------------------------------------
140
141Status: native opcodes DEFERRED — pending financial function registration
142policy. The Analysis add-in functions were implemented on 2026-11-18 (Jim's
143decision).
144
145Rationale: H.6.1 opcode registration was reverted (f4f3de4f92).
146  A broader financial function registration policy must be established
147  before allocating native opcodes to ODDFPRICE/ODDFYIELD. This includes:
148
149  1. Inventory of native vs AddIn-only financial functions.
150  2. Consistent criteria for opcode allocation, import/export,
151     Function Wizard, and help registration.
152  3. Decision on whether ODDFPRICE/ODDFYIELD should receive native
153     implementations or remain AddIn-only.
154
155Current state: Implemented as Analysis AddIn only
156  (financial.cxx getOddfprice/getOddfyield, analysishelper.cxx
157  GetOddfprice/GetOddfyield). On trunk both threw (THROW_RTE, #87380#);
158  8ce50c3485 brought in an unfinished version. The 2026-11-18 code follows
159  Excel's short and long odd first period formulas, requires the first coupon
160  to be on the maturity's coupon schedule (as Excel), and solves the yield
161  with a bracketed Illinois iteration. Against Excel 16 on 4524 random cases:
162  14 differences, all classified (4 where DFC equals E and Excel gives #NUM!,
163  5 annual 30/360 bonds with Feb-end coupons off by 2/360 of a coupon, 5
164  where Excel's yield is less exact or wrong). No native opcode; Function
165  Wizard and English help (04060118.xhp) come from the add-in.
166
167PRIORITY: P2 (Excel compatibility gap). DEFERRED until policy defined.
168
169Group 4 — RRI (Rate of Return)
170------------------------------
171
172Recommended sequence:
173
174  1. Review ScZGZ() (interpr2.cxx:1198) for missing validation:
175     zero/negative present value, zero periods, overflow/underflow.
176  2. Add input validation (PushIllegalArgument for invalid inputs).
177  3. English help exists (04060118.xhp, HID_FUNC_ZGZ).
178  4. Verify Excel import/export mapping (`_xlfnodf.RRI` may cause
179     Excel compatibility issues — Excel has RRI natively).
180  5. Add test cases.
181
182Rationale: Implementation exists but lacks validation (ScZGZ() checks
183  nothing; ODF requires N > 0). Export naming may cause Excel compatibility
184  issues.
185
1862026-11-18: the ODF and English names are RRI (ZGZ is still read, through
187  OpCodeMap::putOpCodeAlias). The RRI commit had also renamed ocZGZ,
188  SC_OPCODE_ZGZ, ScZGZ() and HID_FUNC_ZGZ and reworded the help; that was
189  undone, because translations are keyed on SC_OPCODE_ZGZ and the German
190  function name ZGZ would have been lost.
191
192Group 5 — ERROR.TYPE
193--------------------
194
195Status: COMPLETED in H.4, redone 2026-11-18.
196
197H.4 changed ScErrorType() itself, so ERRORTYPE (ODF name
198  ORG.OPENOFFICE.ERRORTYPE) stopped returning the internal error numbers its
199  help documents (Err:518 gives 518). Jim's decision: ERRORTYPE is back to
200  the trunk behavior and ERROR.TYPE is its own opcode.
201
202Changes now:
203
204  1. SC_OPCODE_ERROR_TYPE_ODF = 419, ocErrorTypeODF, names "ERROR.TYPE"
205     (ODF, English, UI). ScErrorType( bool bODF ) with lcl_ErrorTypeODF()
206     in interpr1.cxx; the mapping is that of XclTools::GetXclErrorCode, so
207     Err:502 gives 3, anything not listed 7. Listed in CASE_OCERRFUNC.
208  2. xls: BIFF 261 reads as ERROR.TYPE; ERRORTYPE is still written as 261.
209     xlsx: ERROR.TYPE reads as ERROR.TYPE.
210  3. Help: func_errortype.xhp with its own help ID HID_FUNC_ERROR_TYPE_ODF
211     (it had reused ERRORTYPE's), embedded in 04060109.xhp next to
212     ERRORTYPE instead of the date functions page.
213  4. Calcval rows `ERROR.TYPE.*` and ERRORTYPE.legacy*.
214
215Group 6 — NUMBERVALUE (Critical Gap) — COMPLETED
216------------------------------------------------
217
218Status: Implemented in H.1.1 (commit on openformula branch); rewritten
2192026-11-18 to the ODF step order and moved to interpr1.cxx next to VALUE.
220
221Changes:
222
223  1. Added case ocNumberValue : ScNumberValue(); to the interpr4.cxx
224     dispatch switch.
225  2. ScInterpreter::ScNumberValue() in interpr1.cxx, following ODF 1.2/1.4
226     §6.13.28: group separators removed only before the decimal separator,
227     the first decimal separator becomes '.', whitespace removed, trailing
228     percent signs divide by 100, and the rest must be a plain xsd:float
229     (lcl_IsPlainFloat()). LEN(DecimalSeparator) must be 1 and it must not
230     occur in GroupSeparator. Omitted separators come from the locale; the
231     locale group separator is dropped when it equals the given decimal
232     separator.
233  3. Regression rows NUMBERVALUE.* in calcval-cases14.tsv (rewritten
234      2026-11-18).
235  4. Function Wizard resource in scfuncs.src: the trunk strings kept,
236      unsuppressed, a third parameter added, parameters 2 and 3 optional
237      (the H.1 block had one flag too few).
238   Differences from Excel 16 (checked): empty text is an error (Excel 0); an
239   empty GroupSeparator is accepted (Excel error); a two-character
240   DecimalSeparator is an error (Excel uses the first).
241
242Rationale: CRITICAL gap resolved — opcode (392) and name registered
243  but no evaluator existed. Function now compiles and evaluates
244  correctly. P1 priority resolved.
245
246Group 7 — GETPIVOTDATA
247----------------------
248
249Recommended sequence:
250
251  1. Review ScGetPivotData() (interpr2.cxx:2972) for correctness.
252  2. XLSX import exists (formulabase.cxx GETPIVOTDATA row, import-only);
253     there is no xlsx export at all. Not run.
254  3. Verify DataPilot dependency handling (refresh, multiple tables).
255  4. Add test cases.
256
257Rationale: Implementation present. DataPilot-dependent application-level
258  function.
259
260Group 8 — FDIST, FINV (Legacy Statistical)
261------------------------------------------
262
2632026-11-18: H.8/H.8.1 turned ocFDist/ocFInv into the ODF left-tail
264functions and added ocLegacyFDist/ocLegacyFInv, which changed the meaning of
265FDIST/FINV typed in every UI language, in macros and in .sxc files; the H.6.1
266revert also dropped the new opcode defines, so the branch did not build.
267Jim's decision: FDIST and FINV stay as on trunk (right tail; ODF
268LEGACY.FDIST/LEGACY.FINV; xls 281/282), and the ODF left-tail functions are
269new opcodes F.DIST (SC_OPCODE_F_DIST_LEFT 417, ScFDistLeft()) and F.INV
270(SC_OPCODE_F_INV_LEFT 418, ScFInvLeft()), with ODF names FDIST and FINV.
271F.DIST takes an optional Cumulative (default TRUE), returns 0 for x < 0 as
272ODF says, and gives the density through GetBetaDistPDF(). F.DIST matches
273Excel 16 on 300 random cases; F.INV on 298 of 300, the other two within
2743e-10 where the distribution is almost flat (p = 0.999, r2 = 1). xls: `_xlfn.F.DIST`/`_xlfn.F.INV`
275(Excel calculates them; F.DIST needs four arguments there), and `_xlfnodf.FDIST`/`_xlfnodf.FINV` from older files import as FDIST/FINV. Help: F.DIST and F.INV sections in
27604060182.xhp.
277
278Original recommended sequence:
279
280  1. Review ScFDist() (interpr3.cxx:1588) — enforces exactly 3
281     params; ODF allows 3–4 with cumulative flag.
282  2. Review ScFInv() (interpr3.cxx:2154) — numerical inversion via
283     iteration.
284  3. Consider adding optional 4th cumulative parameter to FDIST.
285  4. Review export naming: `_xlfnodf.FDIST`/`_xlfnodf.FINV` — Excel has
286     these natively without _xlfn prefix; may cause compatibility
287     issues.
288  5. English help exists for both (04060182.xhp; FVERT is only the help ID
289     name).
290  6. Add test cases.
291
292Rationale: Implementations present but export naming and parameter
293  count are gaps. P2 priority.
294
295Group 9 — HARMEAN
296-----------------
297
298Recommended sequence:
299
300  1. Review ScHarMean() (interpr3.cxx:2712) — counts only x>0,
301     errIllegalArgument for x<=0. Verify against ODF definition.
302  2. English help exists (04060182.xhp; HARMITTEL is only the help ID name).
303  3. BIFF 320 both ways (xlformula.cxx saFuncTable_4) and xlsx 320
304     (formulabase.cxx).
305  4. Add test cases.
306
307Rationale: Implementation present and appears correct. No gap left in
308  this audit's terms.
309
310Group 10 — DDE (Deferred)
311-------------------------
312
313Recommended sequence:
314
315  1. Evaluate platform support (Linux, Windows, macOS).
316  2. Address security concerns (external application access,
317     user confirmation).
318  3. Consider whether DDE should be available in headless/server mode
319     (currently disabled).
320  4. If implemented: add Excel import/export mappings, test cases.
321
322Rationale: DEFER — requires substantial platform/application-level
323  work; unresolved security and platform-appropriateness questions.
324  Not a pure formula function.
325
326Group 11 — HYPGEOMDIST Cumulative (H.9, added 2026-11-18)
327---------------------------------------------------------
328
329H.9/H.9.1 added the optional fifth argument Cumulative (default FALSE). The
330optional argument was popped after the others, one line of the trunk PMF
331algorithm was changed (wrong in 12240 of 135750 exhaustive small cases), and
332the function header was lost in a later merge. 2026-11-18: the trunk
333algorithm itself gave wrong results for some large arguments
334(HYPGEOMDIST(900;1828;2033;4167) gave 3.2E+181 instead of 0.0219), so
335GetHypGeomDist() now multiplies the C(n,x) factors and the probability ratios
336in an order that keeps the product near 1 (same size limit as before).
337Cumulative sums the smaller tail with term ratios. Checked: all 635375 small
338cases against exact values (worst relative error 9e-14), 400 large cases
339against exact big-integer values, and Excel 16 HYPGEOM.DIST/HYPGEOMDIST on 895 cases.
340xls cannot hold five arguments (written as NA()).
341
342Prioritization Summary
343----------------------
344
345P1 (Critical — ODF-required behavior missing):
346
347- ERROR.TYPE: RESOLVED in H.4
348- NUMBERVALUE: RESOLVED in H.1
349- FINDB/REPLACEB/SEARCHB: RESOLVED in H.2
350- DATEDIF: RESOLVED in H.3/H.3.1
351
352P1 (High — semantic gaps):
353
354- ISOWEEKNUM: RESOLVED in H.5
355
356P2 (Excel compatibility):
357
358- ODDFPRICE/ODDFYIELD: add-in implemented 2026-11-18; native opcodes
359    DEFERRED — pending financial registration policy
360- RRI: no validation of N > 0; xls export as `_xlfnodf.RRI`
361- GETPIVOTDATA: xlsx import present, not run
362- F.DIST: xls export with three arguments shows #N/A in Excel
363
364P3 (Minor):
365
366- HARMEAN: nothing open
367
368DEFER (Platform/application-level):
369
370- DDE: Requires DDE subsystem; security questions
371
372Confidence Levels
373-----------------
374
375Run and checked against Excel 16 (2026-11-18): FINDB, REPLACEB, SEARCHB
376(ASCII; CJK counts 2 bytes in AOO always), DATEDIF, ISOWEEKNUM, NUMBERVALUE,
377ERROR.TYPE, F.DIST, F.INV, FDIST, FINV, HYPGEOMDIST, ODDFPRICE, ODDFYIELD,
378RRI (one case). Source only: GETPIVOTDATA, HARMEAN, DDE.
379