Re: org-tables with monetary amounts

[Christian Moe <mail@christianmoe.com>]

Hi,

Parsing numbers followed by currencies is sort of supported already
through Calc's operations on units. (Calc is the built-in emacs
calculator that powers the spreadsheet function of Org tables). I
haven't used this or explored it much, but my impression is that it
could be made more useful than it currently is. Here are a couple of
things you can do fairly easily.

If you're only working with one currency and just want it to show up
automatically, the following calculation will give you 59.97 USD in the
third column right out of the box, without even requiring you to define
it as a unit first.

  | 50 USD | 8.97 USD | 59.97 USD   |
  #+TBLFM: $3=$1+$2

Adding undefined currencies together will result in an expression like
"3 USD + 4 EUR". However, if you define the currencies as units based on
exchange rates, you can get conversion as part of arithmetic operations.

E.g. in Calc,
- put `1' on top of the stack
- then do `u d EUR' to define the euro as your unit currency (for
example)
- then define e.g. US dollars: Today's rate is 1 USD = 0.86 EUR.
- hit apostrophe to enter algebraic mode and enter `0.86 EUR'
  (today's rate: 1 USD = 0.86 EUR)
- now do `u d USD' to define the rate

Now, with the Calc command to simplify units, you can add dollars to
euros and get the result in whichever currency comes first in the
algebraic expression

  | 3 USD | 4 EUR | 6.58 EUR |
  #+tblfm: $3=usimplify($2+$1)

  | 3 USD | 4 EUR | 7.6511628 USD |
  #+tblfm: $3=usimplify($1+$2)

I don't use this functionality, so I don't have answers to all the
questions you'll now have -- including how to get the desired precision
without lopping off the currency unit in the last example!

There are ways to enter user-defined units permanently. But exchange
rates change, so to use this functionality on a daily basis, you'll want
to have some kind of function to pull exchange rates and update the
currency unit definitions in the Calc init file.

Apart from `usimplify', most Calc functions on units appear (?) to be
missing corresponding algebraic versions that you can use in Calc
expressions in Org tables, which limits the usefulness.

Org tables don't seem to have any specific formula syntax for leveraging
Calc unit operations apart from what happens to work out of the
box. This might be an area for improvement, though I'm not sure what to
ask for.

Yours,
Christian

Daniele Nicolodi writes:

> Hello,
>
> I often use org-tables to work with monetary amounts. It would be very
> nice to have a couple of functionalities common in this domain:
>
> - fixed precision arithmetic, namely derive the precision of the results
> from the precision of the arguments (I think that calc can do this),
>
> - support for parsing numbers followed by currencies,
>
> - correct alignment for monetary values.
>
> I had a quick look around, but I haven't found anything that implements
> those things. Has anyone some secret code that they would like to share?
>
> Thank you!
>
> Cheers,
> Dan