FormatNumber() with integers, and with cents implied

[Oso]

The scope of FormatNumber() seems a bit odd to me, coming from a financial software background, unless I'm not understanding it correctly. According to the documentation, the function is intended to output monetary values, but it specifies a 'double' value.

Result$ = FormatNumber(Number.d [, NbDecimals [, DecimalPoint$ [, ThousandSeparator$]]])

Code: Select all

Debug FormatNumber(125400.25) ; Will display: 125,400.25

For financials obviously we cannot sensibly use floats or doubles... viewtopic.php?p=589875#p589875 ... and normal practice is therefore to use integers, with the cents, pence, satang... whatever, being represented by the tens and units of the integer.

That's all fine, but FormatNumber() converts the integer 1000 to 1000.00, instead of 10.00. Shouldn't there be an option to scale it? I thought I'd run this past others before posting to the wishlist on this.

As the example in the documentation shows, the .25 is not going to be an integer, so I don't see how it can be workable like that. The other thing that would be useful and is almost essential in financial reports, is to be able to specify the field width, then have it justified (and truncated if necessary). RSet() is not suitable for this, as it truncates the end of the string, exactly like LSet() in that respect, instead of the usual financial reporting practice, which is to drop the most-significant digits, if the value exceeds the width.

[STARGÅTE]

The documentation is misleading in this point. FormatNumber() has nothing to do with financial mathematics.
The only similarity is that it formats a number with thousand-sign like it is usually used for financial values.
This function is useless if you want to work with financial amounts as it requires a double value.

The only thing you can do is write such a function yourself.

[Little John]

The only thing you can do is write such a function yourself.

Yep. See for instance here:
viewtopic.php?t=48275

[Oso]

The documentation is misleading in this point. FormatNumber() has nothing to do with financial mathematics.

It certainly seems like that was its intention from this...

Description
Format a number into money-like format.
https://www.purebasic.com/documentation ... umber.html

It already accepts integers, so could it be extended to provide a scaling factor? It seems it's almost there, in that respect, absenting that missing option.

We can write our own function, as you say STARGÅTE, but performance is always a concern. I suppose we could write a wrapper function for FormatNumber, which shouldn't impact performance too much though, so yes maybe a reasonable option.

[juergenkulow]

Code: Select all

; FormatCent The penny problems with presition start at 90 trillion, about the Earth's annual gross national product.
Procedure.s FormatCent(centquad.q,NbDecimals.i=2,DecimalPoint$=".",ThousandSeparator$=",")
  Protected centdouble.d=centquad/100.0
  ProcedureReturn FormatNumber(centdouble,NbDecimals,DecimalPoint$,ThousandSeparator$)
EndProcedure

Debug FormatCent(12540025)
Debug FormatCent(25)
Debug FormatCent(1000)
Debug FormatCent(9007199254740992)
Debug FormatCent(9007199254740993)
Debug FormatCent(12540025,2,",",".")
; 125,400.25
; 0.25
; 10.00
; 90,071,992,547,409.92
; 90,071,992,547,409.94
; 125.400,25

[Oso]

The accuracy was the aspect that concerned me last night when writing about this.

Code: Select all

; FormatCent The penny problems with presition start at 90 trillion, about the Earth's annual gross national product.

Yes, and that's assuming usage under the major currencies. In some, the limit comes into effect more readily. We ought to be able to just pick up the decimal point and reposition it two places to the left. I know... ÷ 100 "is" moving two places to the left, but not quite, because computers are not good at maths

[Crusiatus Black]

I don't know how inefficient or bad this is, but my tests are passing. There's probably many other better ways, but the original quad value is never converted to a double in this case.

Code: Select all

Procedure.s FormatCents(value.q, decimals.i = 2, point.c = '.', separator.c = ',')
  If (value = 0)
    ; no need to process anything, easy.
    ProcedureReturn "0" + Chr(point) + "00"
  EndIf 
  
  ; remember that we have a sign, but we do the math in non-negative space
  Protected sign = #False 
  If (value < 0)
    value * -1
    sign = #True 
  EndIf 
  
  Protected decimals_mod.q = Pow(10, decimals)                        ; calcualte the rhs for the modulo
  Protected cents.q = value % decimals_mod                            ; extract what we put as cents later
  Protected segment.q = 0
  
  value / decimals_mod                                                ; remove the cents we just extracted
  
  Protected int_digits = Int(Round(Log10(value), #PB_Round_Down) + 1) ; the number of digits, at least. log10 fails at a certain point, but it's never lower.
  If value <= 0 : int_digits = 1 : EndIf                              ; if the remaining value was 0, log10 is -Inf, but we know there will be 1 digit: 0
  Protected segments = Int(Round(int_digits / 3.0, #PB_Round_Up))     ; the number of thousands segments
  
  ; calculate the memory we need
  Protected required_characters = (int_digits + (segments - 1) + (1 + decimals))
  If (sign) : required_characters + 1 : EndIf   
  
  ; put that memory on the stack, as it is (probably) never that much.
  Protected Dim stack_memory.c(required_characters + 1)
  stack_memory(required_characters) = 0
  
  ; initialize our cursor, we work from the right to left.
  Protected *current.Character = @stack_memory() + required_characters * SizeOf(Character)
  
  ; insert the decimal point and the decimals
  *current - (1 + decimals) * SizeOf(Character)
  *current\c = point
  
  PokeS(*current + SizeOf(Character), RSet(Str(cents), decimals, "0"), -1, #PB_String_NoZero)
  
  If (value = 0)
    ; while loop will never enter, just put '0'
    *current - SizeOf(Character)
    *current\c = '0'
  EndIf 
  
  While (value)
    segment = value % 1000  ; get thousandth segment
    value / 1000            ; remove thousandth segment from value
    
    If value 
      ; there is still a value remaining after this, so we create a ,NNN segment in the string
      *current - 4 * SizeOf(Character)
      *current\c = separator
      
      PokeS(*current + SizeOf(Character), RSet(Str(segment), 3, "0"), -1, #PB_String_NoZero)
    Else 
      ; put whatever is left at the beginning
      Protected last_segment.s = Str(segment)
      *current - StringByteLength(last_segment)
      PokeS(*current, last_segment, -1, #PB_String_NoZero)
    EndIf 
  Wend 
  
  If (sign)
    ; there was a sign, so add that as a last operation
    *current - SizeOf(Character)
    *current\c = '-'
  EndIf 
  
  ; peek from where we left off, which will usually be at the start of the allocated
  ; memory. In some cases, though, i.e. with 999999999999999999 and -999999999999999999
  ; more memory than needed will be allocated because log10 actually returns the number
  ; of digits for those values. 
  
  ProcedureReturn PeekS(*current)
EndProcedure

Structure TestCase
  value.q
  expected.s
  line_number.i
EndStructure

NewList TestCases.TestCase()
Macro AddTestCase(_V_, _E_)
  ; no sign
  AddElement(TestCases())
  TestCases()\value = _V_
  TestCases()\expected = _E_
  TestCases()\line_number = #PB_Compiler_Line
  
  ; sign
  If (_V_ <> 0)
    AddElement(TestCases())
    TestCases()\value = -_V_
    TestCases()\expected = "-" + _E_
    TestCases()\line_number = #PB_Compiler_Line
  EndIf 
EndMacro

AddTestCase(000, "0.00")
AddTestCase(087, "0.87")
AddTestCase(100, "1.00")
AddTestCase(001, "0.01")
AddTestCase(150087, "1,500.87")
AddTestCase(150001, "1,500.01")
AddTestCase(100, "1.00")
AddTestCase(250, "2.50")
AddTestCase(12345, "123.45")
AddTestCase(987654, "9,876.54")
AddTestCase(5000, "50.00")
AddTestCase(888888, "8,888.88")
AddTestCase(999999, "9,999.99")
AddTestCase(1000000, "10,000.00")
AddTestCase(1234567, "12,345.67")
AddTestCase(98765, "987.65")
AddTestCase(123, "1.23")
AddTestCase(456, "4.56")
AddTestCase(789, "7.89")
AddTestCase(2501, "25.01")
AddTestCase(87654321, "876,543.21")
AddTestCase(8888, "88.88")
AddTestCase(54321, "543.21")
AddTestCase(111111, "1,111.11")
AddTestCase(999, "9.99")
AddTestCase(123456, "1,234.56")
AddTestCase(98765432, "987,654.32")
AddTestCase(987654321, "9,876,543.21")
AddTestCase(1000000000, "10,000,000.00")
AddTestCase(123456789, "1,234,567.89")
AddTestCase(500, "5.00")
AddTestCase(3210, "32.10")
AddTestCase(9876543210, "98,765,432.10")
AddTestCase(55555, "555.55")
AddTestCase(666666, "6,666.66")
AddTestCase(7777777, "77,777.77")
AddTestCase(1234, "12.34")
AddTestCase(7890, "78.90")
AddTestCase(987654321987654321, "9,876,543,219,876,543.21")
AddTestCase(123456789012345678, "1,234,567,890,123,456.78")
AddTestCase(999999999999999999, "9,999,999,999,999,999.99")
AddTestCase(1111111111111111111, "11,111,111,111,111,111.11")
AddTestCase(8446744073709551616, "84,467,440,737,095,516.16")
AddTestCase(5555555555555555555, "55,555,555,555,555,555.55")
AddTestCase(6666666666666666666, "66,666,666,666,666,666.66")
AddTestCase(7777777777777777777, "77,777,777,777,777,777.77")
AddTestCase(8888888888888888888, "88,888,888,888,888,888.88")
AddTestCase(1234567890123456789, "12,345,678,901,234,567.89")
AddTestCase(1234567890987654321, "12,345,678,909,876,543.21")
AddTestCase(1111111100000000000, "11,111,111,000,000,000.00")
AddTestCase(987654321000000000, "9,876,543,210,000,000.00")
AddTestCase(1230000000, "12,300,000.00")
AddTestCase(111, "1.11")
AddTestCase(8765432100000, "87,654,321,000.00")
AddTestCase(987654321987654321, "9,876,543,219,876,543.21")

#ONLY_GOOD = #False 
#ONLY_FAIL = #False 

ForEach (TestCases())
  result.s = FormatCents(TestCases()\value, 2)
  If (result = TestCases()\expected)
    CompilerIf #ONLY_GOOD Or Not #ONLY_FAIL 
      Debug "[GOOD](" + RSet(Str(TestCases()\line_number), 4, "0") + ") " + Str(TestCases()\value) + ": " + TestCases()\expected + " = " + result 
    CompilerEndIf 
  Else
    CompilerIf #ONLY_FAIL Or Not #ONLY_GOOD 
      Debug "[FAIL](" + RSet(Str(TestCases()\line_number), 4, "0") + ") " + Str(TestCases()\value) + ": " + TestCases()\expected + " != " + result 
    CompilerEndIf 
  EndIf 
Next 

[BarryG]

For Windows only, you can try this:

Code: Select all

Procedure.s MoneyFormat(amount.d,cents=0)
  If cents
    amount$=StrD(amount)
    amount$=Left(amount$,Len(amount$)-2)+"."+Right(amount$,2)
    amount=ValD(amount$)
  EndIf
  text$=Space(99)
  GetCurrencyFormat_(0,0,StrD(amount,2),0,@text$,90)
  ProcedureReturn text$
EndProcedure

Debug MoneyFormat(1234567890)   ; $1,234,567,890.00
Debug MoneyFormat(1234567890,1) ; $12,345,678.90
Debug MoneyFormat(123)          ; $123.00
Debug MoneyFormat(123,1)        ; $1.23
Debug MoneyFormat(12)           ; $12.00
Debug MoneyFormat(12,1)         ; $0.12
Debug MoneyFormat(1)            ; $1.00
Debug MoneyFormat(1,1)          ; $0.10

[Oso]

Thanks for those examples, Crusiatus and Barry. The first is accurate but complex and we have to bear in mind that this code would be called thousands of times. As impressive as it is, I'm mindful that it sets up a dimensioned array, a loop, a pointer, and a PokeS() for each character.

Financial reports are usually very straightforward but accuracy is a given — we don't require a currency indicator in reports, or even on invoices, since they are assumed to be in a native currency and sometimes marked separately on the document. We don't generally include commas either, as they occupy what is often quite limited space. These are normal practices I've seen over the years and use myself. In turn, reports are often exported to Excel and that's where accounts staff can do whatever they want with them, that thankfully being outside our remit.

The second example, which I've simplified, given the limited requirements, inherently suffers inaccuracy from the StrD() because as soon as you introduce a double, we lose accuracy.

Code: Select all

Procedure.s MoneyFormat(amount.d)
  
    amount$ = StrD(amount)
    amount$ = Left(amount$, Len(amount$) - 2) + "." + Right(amount$, 2)
    amount = ValD(amount$)

    ProcedureReturn StrD(amount,2)
    
EndProcedure


Debug MoneyFormat(1234567890) ; 12345678.90
Debug MoneyFormat(123)        ; 1.23
Debug MoneyFormat(12)         ; 0.12
Debug MoneyFormat(1)          ; 0.10

Debug MoneyFormat(9007199254740990)   ; 90071992547409.91  <---- Error
Debug MoneyFormat(9007199254740992)   ; 90071992547409.92
Debug MoneyFormat(9007199254740993)   ; 90071992547409.92  <---- Error
Debug MoneyFormat(9007199254740994)   ; 90071992547409.94
Debug MoneyFormat(9007199254740995)   ; 90071992547409.95

I then changed it, using a quad. It works but requires additional zeros to be inserted in some cases, because we see results like ".1" instead of the preferred "0.10", but at least in principle it appears to be correct.

Code: Select all

Procedure.s MoneyFormat(amount.q)
  
  amount$ = Str(amount)
  amount$ = Left(amount$, Len(amount$) - 2) + "." + Right(amount$, 2)
  
  ProcedureReturn amount$
    
EndProcedure


Debug MoneyFormat(1234567890) ; 12345678.90
Debug MoneyFormat(123)        ; 1.23
Debug MoneyFormat(12)         ; .12
Debug MoneyFormat(1)          ; .1

Debug MoneyFormat(9007199254740990)   ; 90071992547409.90
Debug MoneyFormat(9007199254740992)   ; 90071992547409.92
Debug MoneyFormat(9007199254740993)   ; 90071992547409.93
Debug MoneyFormat(9007199254740994)   ; 90071992547409.94
Debug MoneyFormat(9007199254740995)   ; 90071992547409.95

It would seem appropriate to me that monetary values represented as cents/pence are accommodated by a new function, similar to FormatNumber(). At present it won't do it, which I accept, because it assumes a double —

Code: Select all

Debug FormatNumber(9007199254740990, 0, ".", "")   ; 9007199254740990
Debug FormatNumber(9007199254740991, 0, ".", "")   ; 9007199254740991
Debug FormatNumber(9007199254740992, 0, ".", "")   ; 9007199254740992
Debug FormatNumber(9007199254740993, 0, ".", "")   ; 9007199254740992  <---- Error

[BarryG]

At the end of the day, you should just use integers (not floats or doubles) internally for all monetary values/calculations, and just cosmetically separate the last 2 digits for the cents when presenting the values. That is, 100023 for $1000.23 and 123 for $1.23. I've seen that in financial software before and it means accuracy is never lost.

[mk-soft]

Work internally with 3 decimal places. Otherwise there will be trouble with the results.