[FEATURE] Convert Currencies stored as text during formula calculations

[fdjohnston]

This is:

- [ ] a bug report
- [x] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

Hi, it's me again. Just ran into a situation similar to the percentage case we just worked through, but this time with currencies. My specific case from the Formula Parser logs is:

Evaluating 1.07 + "$0.00"
Evaluation Result is a a #VALUE! error

My initial thoughts are we could do something similar to what we just did for percentages, but for currencies. I did some playing around in Excel and it seems that it is much more restrictive about what is considered to be a currency than it is with percentages, though I wonder how localization settings are playing into this. I expect one would have to handle cases with the currency symbol before or after the value, probably very similar to what we ran into with percentages. I haven't thought this all the way through, but I wonder if we could even get away with turning the symbol into a capture group in the regexp and depending on if it's a currency symbol or a % have the convertToNumberIfPercent() function omit the division by 100. There's no way it could be that simple, but I wonder if that is a direction that bears exploring.

I going to assume that I'm underestimating the complexity of this like I did with percentages, but I thought it was worth pointing out.

As before, I'd be happy to contribute a PR if this is something the maintainers think is worth fixing, or would this just be opening a can of worms?

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

[MarkBaker]

We would certainly take a similar approach (have a look at PR #3164 to see how I've extracted that logic into a Calculation Engine Helper).

Excel is a lot stricter about what does and doesn't qualify as a currency; and that is very much locale-based: I'm based in Europe, and not in the United States, so....
!
But it doesn't take locale decimal and thousands separators into consideration

It would take a lot more effort to identify all the variations that are accepted, and under which circumstances.

---

Excel also handles strings containing recognised date and time formats

And again, there are a lot of idiosyncracies in the precise formats that are recognised as date/time values

---

With any of these changes, we would want it to work for a majority of people, rather than with a very specific format that would trigger those developers who used different formats to raise errors.

[fdjohnston]

I agree, we would want a solution that worked for as many locales as possible (ideally all of them). To further complicate things, I'm in English speaking Canada, so our currency notation is the same as the US, but in Quebec the dollar sign is displayed after the value. Even though we're in the same country, my Excel thinks a trailing dollar sign is invalid:

I did a bit of experimenting with the regular expression from my previous PR. By leveraging StringHelper::getCurrencyCode() we could do something like this:

        $match = [];
        $currencyCode = StringHelper::getCurrencyCode();
        $STRING_REGEXP_PERCENT = '~^(?:(?: *(?<PrefixedSign>[-+])? *(?<PrefixSymbol>[\%\\' . $currencyCode .']) *(?<PrefixedSign2>[-+])? *(?<PrefixedValue>[0-9]+\.?[0-9*]*(?:E[-+]?[0-9]*)?) *)|(?: *(?<PostfixedSign>[-+])? *(?<PostfixedValue>[0-9]+\.?[0-9]*(?:E[-+]?[0-9]*)?) *(?<PostfixedSymbol>[\%\\' . $currencyCode .']) *))$~i';
        if (preg_match($STRING_REGEXP_PERCENT, $operand, $match, PREG_UNMATCHED_AS_NULL)) {
            //Calculate the percentage
            $sign = ($match['PrefixedSign'] ?? $match['PrefixedSign2'] ?? $match['PostfixedSign']) ?? '';
            $symbol = $match['PrefixSymbol'] ?? $match['PostfixedSymbol'];
            $operand = (float) ($sign . ($match['PostfixedValue'] ?? $match['PrefixedValue'])) / ($symbol === '%' ? 100 : 1);

            return true;
        }

The big issue with this approach is that it will match some false positives. We might be able to use the p_cs_precedes and n_cs_precedes values from localeconv() to manipulate the regex before it runs. Then the big question will be how closely return values from localeconv() match what Excel does internally.

[MarkBaker]

You wouldn't be able to concatenate the $currencyCode value directly into the regexp, because there's at least one very common currency code that has a specific meaning when it appears in a regexp; you'd have to preg_quote() it first.

The Euro symbol can occur before or after the value; and for some Euro countries, it can be before for currency and after for accounting formats:

However, even in the Netherlands there is a difference in the placement of the sign in relation to the currency symbol between currency and accounting formats:

and both are handled correctly when used in a formula
For France:

and for the Republic of Ireland

[fdjohnston]

In my example regexp I threw a \ in before the concatenation to handle $ but your point is well taken; safer to use preg_quote() for sure.

I hadn't considered accounting formats. Unfortunately localeconv() only gives us information about currency formats, not accounting formats. For the Netherlands:

>>> localeconv()
=> [
     "decimal_point" => ",",
     "thousands_sep" => ".",
     "int_curr_symbol" => "EUR",
     "currency_symbol" => b"€",
     "mon_decimal_point" => ",",
     "mon_thousands_sep" => ".",
     "positive_sign" => "",
     "negative_sign" => "-",
     "int_frac_digits" => 2,
     "frac_digits" => 2,
     "p_cs_precedes" => 1,
     "p_sep_by_space" => 1,
     "n_cs_precedes" => 1,
     "n_sep_by_space" => 1,
     "p_sign_posn" => 4,
     "n_sign_posn" => 4,
     "grouping" => [
       3,
     ],
     "mon_grouping" => [
       3,
     ],
   ]

Without doing some kind of exhaustive enumeration process with Excel I can't think of a way to list all the different formats by locale.
One option would be to accept a currency symbol before or after the number, and acknowledge that this will create some false positives in some cases... but that feels dirty.

Looking around in the PHPSpreadsheet code I noticed that in src/PhpSpreadsheet/Style/NumberFormat.php only USD and EUR accounting formats are currently available. Perhaps a good starting place would be handle those two cases for now, and expand on them later?

[MarkBaker]

The reason that only USD and EUR formats are available is because those were pre-defined formats in MS Excel in the early years; Excel 95 didn't store them as a string mask in the file, but as an id for an entry in a pre-defined table.

Earlier this year, I looked at using PHP's Intl extension to see if I could build a Number Format Wizard based around that. The ICU database holds all the information for both accounting and currency (as well as date, and other formats); but Intl may not be available in PHP if the build didn't enable it; and even then accounting is only available from PHP 7.4.1, and if built against ICU >= 53.0
Open/LibreOffice keeps this much simpler: it handles the percentages in the same way as Excel, but doesn't seem to handle currency strings at all

[fdjohnston]

That makes sense. Thanks for the context.

Well, I'll leave this one up to you. As I mentioned above, I'd be happy to build out a PR using a regular expression that handles a general case, and then we can go back and forth a bit tightening it up around certain edge cases. Or do you think it's not worth perusing? The idea of a number format Wizard is interesting, but as you point out, would only work for certain builds. Perhaps a more general regexp based approach could be used to start, and then replaced with a more precise Intl based approach later?
Let me know.

[MarkBaker]

I'd say go ahead with the more simplistic approach, submit a PR with the code that you posted earlier using the currency code from locale, and the before and after position regexp,and we'll experiment to see how many false positives we can generate.

Given how long it took before anybody even noticed that string percentages weren't being converted to numbers, I don't think it affects many people, so I don't think that false positives will be too much of a problem.

Make sure that you do a pull from master first, so that you have the refactoring that I did, and do the changes on a feature branch

[fdjohnston]

Sounds good. I'll submit a PR later this week. I'll use a new form from master so that I get your changes.
Thanks again.