How to format table fields as currency in org-mode

Question

I'd like to format fields in an org-mode table as currency - meaning with currency symbol ($) and commas as thousands separators. I've been using $%.2f to get e.g. $1000.00 but how to get the comma separators e.g. $1,000.00 ? I've RTFM but perhaps I am too dense to get it. Either calc or elisp formula is fine. See sample table below:

| Item     | Quantity |  Price |      Ext |
|----------+----------+--------+----------|
| Widget 1 |       10 | 100.00 |  1000.00 |
| Widget 2 |        5 |  50.00 |   250.00 |
| Widget 3 |        1 |   5.00 |     5.00 |
|----------+----------+--------+----------|
|          |          |  Total | $1255.00 |
#+TBLFM: $4=($2*$3);%.2f::@5$4=vsum(@2..@4);$%.2f

Answer 1

I found no way of doing it consistently, such that you get numbers with thousands separators, and these numbers instead are correctly interpreted for further calculations. So this is not an answer, just to record my research so far.

The following example steals code to format numbers with thousands separators. C-c C-c on the code to define the function, or add to your init file.

Then, the grand total is calculated using elisp, and transformed with the new formatting function.

#+begin_src elisp :results none
(defun my-thousands-separate (num)
  "Formats the (possibly floating point) number with a thousands
separator."
  (let* ((nstr (number-to-string num))
         (dot-ind (string-match "\\." nstr))
         (nstr-no-decimal (if dot-ind
                               (substring nstr 0 dot-ind)
                             nstr))
         (nrest (if dot-ind
                    (substring nstr dot-ind)
                  nil))
         (pretty nil)
         (cnt 0))
    (dolist (c (reverse (append nstr-no-decimal nil)))
      (if (and (zerop (% cnt 3)) (> cnt 0))
          (setq pretty (cons ?, pretty)))
      (setq pretty (cons c pretty))
      (setq cnt (1+ cnt)))
    (concat pretty nrest)))
#+end_src

| Item     | Quantity |      Price |          Ext |
|----------+----------+------------+--------------|
| Widget 1 | 10       | 1001001.00 |  10010010.00 |
| Widget 2 | 5        |  501001.00 |   2505005.00 |
| Widget 3 | 1        |   51001.00 |     51001.00 |
|----------+----------+------------+--------------|
|          |          |      Total | 12,566,016.0 |
#+TBLFM: $4=($2*$3);%.2f::@5$4='(my-thousands-separate (apply '+ '(@2..@4)));N

Note that if you do the same for the row totals, then the comma-separated numbers will not be interpreted correctly for the grand total.

The correct way should be to set the numeric locale and let printf do the trick, but I don't know how to set this for emacs.