Bug Hunting Session
Bug 63536 - Add Accounting format code to currency list
Summary: Add Accounting format code to currency list
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsDevEval
: 78542 92121 (view as bug list)
Depends on:
Blocks: Number-Format
  Show dependency treegraph
 
Reported: 2013-04-15 04:50 UTC by PhilC2K
Modified: 2019-05-29 14:24 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description PhilC2K 2013-04-15 04:50:12 UTC
Now that the Accounting format has been resolved (see 45780), add the following accounting code format to the list of per-defined formats under Currency:

[$$-409]* #,##0.00_);[RED][$$-409]* (#,##0.00);[$$-409]* -_)
Comment 1 ign_christian 2013-10-09 13:36:56 UTC
Hi Phil, please explain the purpose of this report. What is the difference with Bug 45780?
Comment 2 PhilC2K 2013-10-10 03:36:41 UTC
A fair question.  The original bug report (Bug 45780) was to address a fundamental technical deficiency that prevented the basic ability to even create an accounting format, and the implied (though not explicitly stated) addition of an "Accounting" format to the list of pre-defined number formats.  That technical issue has now been solved but not the addition of the Accounting format entry.  That is left to the user to 1) build the format, 2) create a new template 3) make the new template the default.  Hardly an adopter friendly process.  As I have bellowed from my soap-box before, we must make this "work" for the adopters and leaving this other piece undone creates a barrier to them.

This bug report is an effort to drop the other shoe and bring that remaining piece to the fore to be addressed. (And does not get lost in the "completion" of the primary bug.)

It's not a very challenging fix unlike the technical underpinnings.  The biggest issue would be what the formatting code should be (I would defer to a professional accountant for that).  But I would amend my original suggestion to correct the right-indent of the negative value:

[$$-409]* #,##0.00_);[RED][$$-409]* (#,##0.00)_);[$$-409]* -_)


Separately, I feel LO should add the ability of a user to define number formats and then have [Add] and [Delete] buttons to manage their User Defined formats in the default template
Comment 3 Robinson Tryon (qubit) 2014-02-04 15:51:54 UTC
(In reply to comment #2)
> ...As I have bellowed from my soap-box
> before, we must make this "work" for the adopters and leaving this other
> piece undone creates a barrier to them.

Smells like an enhancement.

> 
> This bug report is an effort to drop the other shoe and bring that remaining
> piece to the fore to be addressed. (And does not get lost in the
> "completion" of the primary bug.)
> 
> It's not a very challenging fix unlike the technical underpinnings.  The
> biggest issue would be what the formatting code should be (I would defer to
> a professional accountant for that).  But I would amend my original
> suggestion to correct the right-indent of the negative value:
> 
> [$$-409]* #,##0.00_);[RED][$$-409]* (#,##0.00)_);[$$-409]* -_)

Feel free to submit a patch for this.

> 
> Separately, I feel LO should add the ability of a user to define number
> formats and then have [Add] and [Delete] buttons to manage their User
> Defined formats in the default template

Feel free to submit this as a separate enhancement request.
Comment 4 m.a.riosv 2015-06-16 21:58:11 UTC
*** Bug 92121 has been marked as a duplicate of this bug. ***
Comment 5 Laurent BP 2015-12-26 23:20:59 UTC
*** Bug 78542 has been marked as a duplicate of this bug. ***
Comment 6 Laurent BP 2015-12-31 17:10:02 UTC
In en_US, three last currency formats have currency symbol left aligned. If that not fit your needs, you may suggest new format with some reference.
Comment 7 Cor Nouws 2016-08-06 20:21:59 UTC
waiting in silence.. maybe relatively easy, Jani/dev x ?
Comment 8 jani 2016-08-07 06:18:12 UTC
There seems to be missing consensus about what should be added (like having an accounting look at it), I would suspect too that this would be country or at least region specific.

So first we need to have 1 (or more) fixed definitions, then we can add them.
Comment 9 Laurent BP 2016-08-07 13:45:37 UTC
LibO, with en-US and en_GB locales, already contains two currency formats with left justify currency symbol and right justify decimal number:
[$$-409]* #,##0;-[$$-409]* #,##0
[$$-409]* #,##0.00;-[$$-409]* #,##0.00

These formats are defined in
http://opengrok.libreoffice.org/xref/core/i18npool/source/localedata/data/en_US.xml#113
    <FormatElement msgid="CurrencyFormatskey7" default="false" type="short" 
       <FormatCode>[CURRENCY]* #,##0;-[CURRENCY]* #,##0</FormatCode>
    </FormatElement>
    <FormatElement msgid="CurrencyFormatskey8" default="false" type="medium" usage="CURRENCY"  formatindex="83">
       <FormatCode>[CURRENCY]* #,##0.00;-[CURRENCY]* #,##0.00</FormatCode>
    </FormatElement>

This was added 4 years ago:
https://cgit.freedesktop.org/libreoffice/core/commit?id=84c54990c0dbd3385a4a653afe63d0fa7b1c435b
We may add some more formats but we need some references: currency unit can be on left or right, negative numbers can be in red or not, with a minus sign or with parentheses, zeros can be displayed as --, etc. There are many possibilities depending on locales acceptance rules.

As I have no accounting capacities, I looked to other spreadsheet for fr-FR:
Gnumeric proposes:
_(0,00*  [$€-1]_);_((0,00)*  [$€-1];_("-"??*  [$€-1]_);_(@_)
Excel 2010:
_-* # ##0,00 €_-;-* # ##0,00 €_-;_-* "-"?? €_-;_-@_-
I would prefer Excel proposition as there is thousand separator and decimal are aligned.
According to help
https://support.office.com/en-US/article/Format-numbers-as-currency-0A03BB38-1A07-458D-9E30-2B54366BC7A4
Accounting format for en-US in Excel may look like:
[$$-409]* #,##0.00_);[$$-409]* (#,##0.00);[$$-409]* -_);_(@_)

This has to be defined for each locale which needs an accounting format. There are 198 XML files in http://opengrok.libreoffice.org/xref/core/i18npool/source/localedata/data to be modified. But they can be modified one after one, when localization teams decide what is the best format for each.
Comment 10 QA Administrators 2017-03-01 10:41:37 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2017-03-28 08:13:38 UTC Comment hidden (obsolete)
Comment 12 Cor Nouws 2017-04-12 13:53:00 UTC
(In reply to jani from comment #8)
> There seems to be missing consensus about what should be added (like having
> an accounting look at it), I would suspect too that this would be country or
> at least region specific.

IMO comment #0 and comment #2 are clear enough > NEW