Convert many currency format to decimals

Albireo · Post by **Albireo** » 11 Oct 2020, 03:47

My desire is to convert visual numbers (many different formats) to decimals .:
Which makes it possible to make calculations on the result with these numbers.
Some examples for 1000 with two decimals.: as 1000.00 (or as 1000,00 as on my computer / language)

1.000,00 => 1000.00
1,000.00 => 1000.00
1 000,00 => 1000.00
1000 => 1000.00
etc.

I found one solution €() - Währungformat zum Rechnen mit AHK umwandeln which converts text to numeric format (the way I want)
But this function can not handle / choose the structure for the input value .:
eg. this value 39,354.40 is converted to 39.35 my desire is 39354.40
(currency symbols such as $ € kr .. can be handled in a different way)

What would a solution look like for me?

There are several good solutions that convert the other way - from numbers / decimals to visually easy-to-read presentation. .
The best? is probably this one .: [WinAPI] GetXxxFormat Functions

GetNumberFormatEx by jNizM

mikeyww · Post by **mikeyww** » 11 Oct 2020, 06:45

Code: Select all

strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency) "`n"
MsgBox, 64, Currency, %text%
ExitApp

currencyToDecimal(currency) {
 ; @mikeyww on 11 October 2020 • https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 currency := (cents := RegExMatch(currency, ".*[.,]\K\d\d$", decimals) ? decimals : "00") ? currency : currency "."
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Return RegExReplace(dollars, "[ .,]") "." cents
}

Post by **BoBo** » 11 Oct 2020, 09:30

AFAICS, that €() converter uses simple/noob friendly StrReplace(). Its aim is to convert currency so AHK can further process it. The outcomes format has to be dealt with separately (ie round()ing). I'd guess, to be able to handle currencies that work with 1,000.00 ($) instead of 1.000,00 (€) simply swap €() replacements' order.

mikeyww · Post by **mikeyww** » 11 Oct 2020, 09:40

It looks like the need here is broader than this particular fixed replacement would be able to handle.

Albireo · Post by **Albireo** » 12 Oct 2020, 19:30

Thank you!

The function seems to work well for many different number presentations.
Made a small addition, so it became possible to choose whether the result should have a decimal point or a decimal comma.
(If the result is to be handled by AHK, the decimal point (.) is better. If the result is to be processed by LO Calc or Excel, a decimal comma (,) is preferable.)

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance force


strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency) "`n"
MsgBox, 64, Currency, %text%


Value := "1 000,45"
MsgBox % "Value .: " Value "`n`t1) . " currencyToDecimal(Value, ".") "`n`t2) , " currencyToDecimal(Value, ",")

Value := "1,000"
MsgBox % "Value .: " Value "`n`t1) . " currencyToDecimal(Value, ".") "`n`t2) , " currencyToDecimal(Value, ",")

Value := "1000"
MsgBox % "Value .: " Value "`n`t1) . " currencyToDecimal(Value, ".") "`n`t2) , " currencyToDecimal(Value, ",")

Value := "1 000"
MsgBox % "Value .: " Value "`n`t1) . " currencyToDecimal(Value, ".") "`n`t2) , " currencyToDecimal(Value, ",")

Value := "1.000,12"
MsgBox % "Value .: " Value "`n`t1) . " currencyToDecimal(Value, ".") "`n`t2) , " currencyToDecimal(Value, ",")
ExitApp


currencyToDecimal(currency, decsign := ".") {
 ; @mikeyww on 11 October 2020 • https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 currency := (cents := RegExMatch(currency, ".*[.,]\K\d\d$", decimals) ? decimals : "00") ? currency : currency "."
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Res := RegExReplace(dollars, "[ .,]") "." cents
 If (decsign = ",")
	Res := StrReplace(Res, ".", ",")
 Return Res
}
}

mikeyww · Post by **mikeyww** » 12 Oct 2020, 20:04

Looks good. In my original code, I did find a bug in the handling of ".00" as the decimal places in the input string. The fix is below.

Code: Select all

strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000", "200,45", "200,00"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency) "`n"
MsgBox, 64, Currency, %text%
ExitApp

currencyToDecimal(currency, decsign := ".") {
 ; @mikeyww on 11 October 2020, revised 12 October 2020 @Albireo
 ; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 RegExMatch(currency, ".*[.,]\K\d\d$", cents)
 If (cents = "")
  currency .= ".", cents := "00"
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Return RegExReplace(dollars, "[ .,]") (decsign ? decsign : ".") cents
}

Albireo · Post by **Albireo** » 22 Oct 2020, 09:14

For documentation .:
To handle currency with a space after last digit or before the first digit.

Code: Select all

RegExMatch(Trim(currency), ".*[.,]\K\d\d$", cents)

eg. can also handle ' 20.00 '. Maybe can do it in another way, but it works.

mikeyww · Post by **mikeyww** » 22 Oct 2020, 09:22

Noted! Thank you.

Albireo · Post by **Albireo** » 22 Oct 2020, 10:07

Probably a better way to clear spaces is .: currency: = Trim(currency) at the beginning of the function.

But there is another limitation that can be more difficult to deal with. All digits with only one decimal are multiplied by 10
for example. 10.0 becomes 100.00

mikeyww · Post by **mikeyww** » 22 Oct 2020, 10:16

Code: Select all

strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000", "200,45", "200,00", "10.0"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency) "`n"
MsgBox, 64, Currency, %text%
ExitApp

currencyToDecimal(currency, decsign := ".") {
 ; Version 02
 ; @mikeyww on 11 October 2020, revised 12 October 2020 @Albireo
 ; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 currency := Trim(currency), RegExMatch(currency, ".*[.,]\K\d{1,2}$", cents)
 If (cents = "")
  currency .= ".", cents := "00"
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Return Format("{}{}{:02}", RegExReplace(dollars, "[ .,]"), decsign, cents)
}

Albireo · Post by **Albireo** » 22 Oct 2020, 13:49

Thank you!

I'm happy again

Albireo · Post by **Albireo** » 03 Jan 2021, 14:41

(I do not fully understand all the instructions - hence my question)
Would it be complicated to be able to select "decimals" in this function? for example 0, 1, 2
Something like this .:

Code: Select all

...
vRes := currencyToDecimal(currency,, "0")
...
ExitApp

currencyToDecimal(currency, decsign := ".", dec := "2")
{...

mikeyww · Post by **mikeyww** » 03 Jan 2021, 16:42

Code: Select all

strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000", "200,45", "200,00", "10.0"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency,, 3) "`n"
MsgBox, 64, Currency, %text%
ExitApp

currencyToDecimal(currency, decsign := ".", dec := 2) {
 ; Version 03
 ; @mikeyww on 11 October 2020, revised 12 October 2020 @Albireo
 ; 03 January 2021: added decimal places
 ; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 currency := Trim(currency), RegExMatch(currency, ".*[.,]\K\d{1,2}$", cents)
 If (cents = "")
  currency .= ".", cents := "00"
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Return Format("{:0." dec "f}", RegExReplace(dollars, "[ .,]") decsign cents)
}

Albireo · Post by **Albireo** » 13 Jan 2021, 08:32

The function() is getting better and better

Albireo · Post by **Albireo** » 14 Jan 2021, 12:56

No...

The function() - currencyToDecimal does not work if the call is as follows .: (From decimal point to decimal comma) eg.

Code: Select all

strings := ["1.000,12", "1,000.50", "1 000,65", "1000", "1,000", "200,45", "200,00", "10.0"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency, ",") "`n"
MsgBox, 64, Currency, %text%
ExitApp

The old function works...

Albireo · Post by **Albireo** » 16 Jan 2021, 07:42

No matter how hard I tried, I could not solve my desire, without to mark if the input value has decimals (decExist) or not.
This function() seems to be working (right now

)

Code: Select all

#SingleInstance force
; strings := ["123.1292", "1.000,62", "1,000.64", "1 000,45", "1000", "1,000", "200,45", "200,00", "10.0"]
strings := ["0.96", "1.000,42", "1,000.68", "1 000,50", "1000", "1,000", "200,45", "200,00", "10.0", "123.4321", "1 000 000,45"]
For index, currency in strings
{	text1 .= currency " => " currencyToDecimal(currency,",", 4, "n") "`n"
	text2 .= currency " => " currencyToDecimal(currency,".", 2, "y") "`n"
}

MsgBox ,, %A_ScriptName% - Rad %A_LineNumber%, %text1% `n`n %text2%
ExitApp

currencyToDecimal(currency, decSign := ".", dec := 2, decExist := "y")
{	; Version 04 - 16 January 2021 with help by @mikeyww
	; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
	; @mikeyww on 11 October 2020, revised 12 October 2020 @Albireo
	; 
	; Manages different currency structures (eg. 1.000,42 - 1,000.68 - 1000 - 1 000 000)
	;
	; currency	- The number structure to be processed
	; decsign	- The decimal (. or ,) that should be present in the result
	; dec 		- How many decimals should it be in the result
	; decExist (y/n)	- If the source (currency) has decimals (or not)
	;
 
	if !(decExist = "y")
		currency := currency ".0"
	
	currency := Trim(currency)
	RegExMatch(currency, ".*[.,]\K\d{1,2}$", cents)
	If (cents = "")
		currency .= ".", cents := "0"
	RegExMatch(currency, ".+(?=[.,])", dollars)

	aDollar := Round(RegExReplace(dollars, "[ .,]") "." cents, dec)
	aDollar := strReplace(aDollar, ".", decSign)
 
	Return % aDollar
}

mikeyww · Post by **mikeyww** » 16 Jan 2021, 08:40

Code: Select all

strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000", "200,45", "200,00", "10.0"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency, ",", 3) "`n"
MsgBox, 64, Currency, %text%
ExitApp

currencyToDecimal(currency, decsign := ".", dec := 2) {
 ; Version 04
 ; @mikeyww on 11 October 2020, revised 12 October 2020 @Albireo
 ; 16 January 2021: fixed: comma could not be used as a decimal sign
 ; 03 January 2021: added: decimal places
 ; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 currency := Trim(currency), RegExMatch(currency, ".*[.,]\K\d{1,2}$", cents)
 If (cents = "")
  currency .= ".", cents := "00"
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Return StrReplace(Format("{:0." dec "f}", RegExReplace(dollars, "[ .,]") "." cents), ".", decsign)
}

Albireo · Post by **Albireo** » 17 Jan 2021, 04:37

Your solution is much nicer than mine

However, there is a "problem" that I can not solve in any other way, with this function(), than to be able to specify whether the number to be treated is an integer or a decimal number.
An example 100/33 = 33.333333, the currencyToDecimal() gives the answer as an integer.

mikeyww · Post by **mikeyww** » 17 Jan 2021, 06:29

Code: Select all

strings := ["1.000,12", "1,000.34", "1 000,45", "1000", "1,000", "200,45", "200,00", "10.0", "3.030303"]
For index, currency in strings
 text .= currency " => " currencyToDecimal(currency, ",", 3) "`n"
MsgBox, 64, Currency, %text%
ExitApp

currencyToDecimal(currency, decsign := ".", dec := 2) {
 ; Version 05
 ; @mikeyww on 11 October 2020, revised 12 October 2020 @Albireo
 ; 2021/01/17: fixed: cents could not be identified in currency with more than two decimal places
 ; 2021/01/16: fixed: comma could not be used as a decimal sign
 ; 2021/01/03: added: decimal places
 ; https://www.autohotkey.com/boards/viewtopic.php?f=76&t=81983
 currency := Trim(currency), RegExMatch(currency, ".*[.,]\K\d*$", cents)
 If (cents = "")
  currency .= ".", cents := "00"
 RegExMatch(currency, ".+(?=[.,])", dollars)
 Return StrReplace(Format("{:0." dec "f}", RegExReplace(dollars, "[ .,]") "." cents), ".", decsign)
}

Albireo · Post by **Albireo** » 17 Jan 2021, 07:14

Yes it works!
But, is 3.141 or 3,141 (PI) or an integer (three thousand one hundred forty one)?

Convert many currency format to decimals Topic is solved

Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals

Re: Convert many currency format to decimals