org-tables with monetary amounts

org-tables with monetary amounts

[Daniele Nicolodi]

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

Re: org-tables with monetary amounts

[Nicholas Savage]

I don't have anything to answer your questions, but I wanted to chime in that this is a feature I'd also like to exist, or find out more about if it already does exist.

I'd be definitely willing to help out too code-wise to the extent I can.

Thanks,
Nick

On Tue, Sep 22, 2020, at 10:57, Daniele Nicolodi wrote:
> 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
> 
>

Re: org-tables with monetary amounts

[Russell Adams]

I often use this for summing columns.

| Column | Column |
|--------+--------|
| a      |    1.2 |
| b      |      3 |
| c      |   4.56 |
|--------+--------|
|        |   8.76 |
#+TBLFM: @>$2=vsum(@-I..@-II);%0.2f

This does last row (@>$2), and the sum between dividers, and outputs
to fixed precision.

On Tue, Sep 22, 2020 at 04:57:42PM +0200, Daniele Nicolodi wrote:
> 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
>


------------------------------------------------------------------
Russell Adams                            RLAdams@AdamsInfoServ.com

PGP Key ID:     0x1160DCB3           http://www.adamsinfoserv.com/

Fingerprint:    1723 D8CA 4280 1EC9 557F  66E8 1154 E018 1160 DCB3

Re: org-tables with monetary amounts

[Eric S Fraga]

Not answering your question directly but, depending on why and how you
are using org tables for monetary calculations, you may wish to look at
ledger [1].  There is support in org for ledger via babel (ob-ledger.el,
distributed with org).

HTH,
eric

Footnotes:
[1]  https://www.ledger-cli.org/

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4-18-gaea110

Re: org-tables with monetary amounts

[Daniele Nicolodi]

On 23/09/2020 18:55, Eric S Fraga wrote:
> Not answering your question directly but, depending on why and how you
> are using org tables for monetary calculations, you may wish to look at
> ledger [1].  There is support in org for ledger via babel (ob-ledger.el,
> distributed with org).

Hello Eric,

I use beancoount (another text-based accounting software, very similar
to ledger) for accounting purposes and I am very happy with it. I use
org-mode for planning and management tasks or simply to compare quotes,
things better handled in a a spreadsheet table.

I think it is time to have a look at how org-tables can be extended to
handle monetary quantities.

Cheers,
Dan

Re: org-tables with monetary amounts

[Alan Schmitt]

[-- Attachment #1: Type: text/plain, Size: 747 bytes --]

Hello,

On 2020-09-25 13:20, Daniele Nicolodi <daniele@grinta.net> writes:

> I use beancoount (another text-based accounting software, very similar
> to ledger) for accounting purposes and I am very happy with it. I use
> org-mode for planning and management tasks or simply to compare quotes,
> things better handled in a a spreadsheet table.

I use ledger, and I wrote a small page about how I use org with it:
http://alan.petitepomme.net/tips/ledger_and_org.html

> I think it is time to have a look at how org-tables can be extended to
> handle monetary quantities.

Yes! I'm currently writing a tutorial about envelope budgeting (using
org-mode, of course), and it would help to have my table examples with
monetary quantities.

Best,

Alan

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 528 bytes --]

Re: org-tables with monetary amounts

[Nick Dokos]

For fixed precision, you can either use an explicit format as Russell Adams points out,
or you can modify (globally or per file) the variable org-calc-default-modes. That's a
complicated variable and you only need to change one part of it for this:

    (plist-put org-calc-default-modes 'calc-float-format '(fix 2))

See e.g. https://emacs.stackexchange.com/a/59181/14825 for some discussion.
-- 
Nick

"There are only two hard problems in computer science: cache
invalidation, naming things, and off-by-one errors." -Martin Fowler

Re: org-tables with monetary amounts

[Christian Moe]

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

Re: org-tables with monetary amounts

[Eric S Fraga]

Christian,

thank you for this mini-tutorial.  Very helpful!  Calc is so powerful
but also has such a steep learning curve that I don't use it as much as
I should.
-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4-29-gbc9664

Re: org-tables with monetary amounts

[Daniele Nicolodi]

Hello Christian,

thank you for your input. calc support for units indeed covers the
basics of working with currencies. I gave it a spin on some notes I have
been working on recently. While some things work very nicely, other are
a bit ugly. It is a good start and I'll explore this approach more.

On 24/09/2020 11:17, Christian Moe wrote:

> 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)

Having to explicitly use usimplify() is a bit too verbose. It would be
ideal if this could be somehow be implicit.

> 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!

Having the desired fixed precision is quite important for this to be
useful. In y recent tests I had to drop the units (currencies) to
achieve this. It would be nice to find a way to avoid it.

> 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.

Computations usually refer to a specific point in time, thus this is npt
a big problem in practice. I already have scripts to pull exchange rates
at a given date. These can the be inserted as a property that can be
easily references from the computations.

> 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.

I don't understand what you mean in the two paragraphs above. Can you
please clarify, maybe with examples?

Thank you.

Cheers,
Dan

Re: org-tables with monetary amounts

[Christian Moe]

Hi, Daniele,

Good that it's working for you. I'll try to explain the unclear parts.

Daniele Nicolodi writes:

> On 24/09/2020 11:17, Christian Moe wrote:
>
>> 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)
>
> Having to explicitly use usimplify() is a bit too verbose. It would be
> ideal if this could be somehow be implicit.

Yes, or at least with a less obtrusive syntax, like a mode flag,
something like

  $3=$2+$1;u

(here using "u" for "units" -- unfortunately, capital "U" is already
taken)

>> 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!
>
> Having the desired fixed precision is quite important for this to be
> useful. In y recent tests I had to drop the units (currencies) to
> achieve this. It would be nice to find a way to avoid it.

[...]

>> 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.

I may be missing something, but it looks like handy Calc commands like
simple unit conversion currently can't be used in an Org spreadsheet,
making Calc units less useful in the spreadsheet. And that this is due
to a lacuna in Calc, not in Org.

When using Calc directly, there are two ways to enter calculations. The
default is reverse Polish notation on the stack (like an old HP
calculator), with various keys bound to all the available commands. The
alternative is algebraic notation.

For example, the command for square root is `calc-sqrt', entered with
the `Q' key. To get the square root of two, you would enter `2 Q' or `2
RET Q'. The corresponding function to use in algebraic notation is
`sqrt'. To use it, you would press apostrophe for algebraic notation,
and enter `sqrt(2) RET'.

But while all Calc commands have keys assigned, it seems not all have
corresponding algebraic notation (?). The Calc manual says that the
command `u s' (`calc-simplify-units') has the algebraic expression
`usimplify'. But it does not list such expressions for other handy Calc
commands, like the `u c' (`calc-convert-units') command, the `u r'
(`calc-remove-units') command or the `u x' (`calc-extract-units')
command.

The Org spreadsheet can also use Lisp formulas, so I've tried to use
calc-simplify-units directly, but this leads to #ERROR.


>> 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.

I think I was thinking about adding mode flags for unit computations,
like in the imagined example above (`$3=$2+$1;u'), similar to what has
been done for duration computations. See the manual, 3.5.2 Formula
syntax for Calc. Might be useful, but I'm not sure how to specify a
request.

> I don't understand what you mean in the two paragraphs above. Can you
> please clarify, maybe with examples?

Hope this helps.

Yours,
Christian

Re: org-tables with monetary amounts

[Eric S Fraga]

On Monday, 12 Oct 2020 at 10:22, Christian Moe wrote:
> I think I was thinking about adding mode flags for unit computations,
> like in the imagined example above (`$3=$2+$1;u'), similar to what has
> been done for duration computations. 

This would be very useful, actually.  I use embedded calc all the time
with units; being able to use some of the same expressions in a table
would be highly welcome.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4-38-g16f505

Re: org-tables with monetary amounts

[Derek Feichtinger]

Hi,

On Mon, Oct 12 2020, Eric S Fraga <e.fraga@ucl.ac.uk> wrote:

> On Monday, 12 Oct 2020 at 10:22, Christian Moe wrote:
>> I think I was thinking about adding mode flags for unit computations,
>> like in the imagined example above (`$3=$2+$1;u'), similar to what has
>> been done for duration computations. 
>
> This would be very useful, actually.  I use embedded calc all the time
> with units; being able to use some of the same expressions in a table
> would be highly welcome.

The functions that one can use in calc formulas can be extended using
the 'defmath' lisp command. I paste here from my notes (not about
monetary units, but easily adapted):

+++++++++++++++++++++++++++++++
Defining a new calc function for unit conversion with defmath
#+BEGIN_SRC emacs-lisp
(defmath uconv (expr target-units &optional pure)
  (math-convert-units expr target-units pure))
#+END_SRC

#+RESULTS:
: calcFunc-uconv

| km     | ft           |
|--------+--------------|
| 2.5 km | 8202.0997 ft |
#+TBLFM: $2=uconv($1, ft)

Using the units from the table header (if the 3rd arg is given to
uconv, the output is stripped of the unit):

|  km |        ft |
|-----+-----------|
| 2.5 | 8202.0997 |
#+TBLFM: $2 = uconv($1 * @<$1, @<$2, t)

The standard calc function usimplify also works for this use
case:

|  km |        ft |
|-----+-----------|
| 2.5 | 8202.0997 |
#+TBLFM: $2 = usimplify($1 * @<$1 / @<$2)

A lisp equivalent of the above
#+begin_src elisp
(calc-eval "usimplify(2.5 km / ft)")
#+end_src

#+RESULTS:
: 8202.09973753

Let's define a function that converts to base units
#+begin_src elisp
  (defmath ustd (expr) (math-simplify-units (math-to-standard-units expr nil)))
#+end_src

#+RESULTS:
: calcFunc-ustd

| distance | time   | speed       | std unit speed   | speed in ft/s    |
|----------+--------+-------------+------------------+------------------|
| 3 km     | 2.5 hr | 1.2 km / hr | 0.33333333 m / s | 1.0936133 ft / s |
#+TBLFM: @2$3=$1/$2::@2$4=ustd($3)::@2$5=uconv($-1, ft/s)
++++++++++++++++++++++++++++++++++++


-- 
Paul Scherrer Institut
Dr. Derek Feichtinger                   Phone:   +41 56 310 47 33
Group Head HPC and Emerging Technologies  Email: derek.feichtinger@psi.ch
Building/Room No. OHSA/D17
Forschungsstrasse 111
CH-5232 Villigen PSI 

Re: org-tables with monetary amounts

[Christian Moe]

Hi, Derek,

Very useful, thanks!

Yours,
Christian

Derek Feichtinger writes:

> Hi,
>
> On Mon, Oct 12 2020, Eric S Fraga <e.fraga@ucl.ac.uk> wrote:
>
>> On Monday, 12 Oct 2020 at 10:22, Christian Moe wrote:
>>> I think I was thinking about adding mode flags for unit computations,
>>> like in the imagined example above (`$3=$2+$1;u'), similar to what has
>>> been done for duration computations.
>>
>> This would be very useful, actually.  I use embedded calc all the time
>> with units; being able to use some of the same expressions in a table
>> would be highly welcome.
>
> The functions that one can use in calc formulas can be extended using
> the 'defmath' lisp command. I paste here from my notes (not about
> monetary units, but easily adapted):
>
> +++++++++++++++++++++++++++++++
> Defining a new calc function for unit conversion with defmath
> #+BEGIN_SRC emacs-lisp
> (defmath uconv (expr target-units &optional pure)
>   (math-convert-units expr target-units pure))
> #+END_SRC
>
> #+RESULTS:
> : calcFunc-uconv
>
> | km     | ft           |
> |--------+--------------|
> | 2.5 km | 8202.0997 ft |
> #+TBLFM: $2=uconv($1, ft)
>
> Using the units from the table header (if the 3rd arg is given to
> uconv, the output is stripped of the unit):
>
> |  km |        ft |
> |-----+-----------|
> | 2.5 | 8202.0997 |
> #+TBLFM: $2 = uconv($1 * @<$1, @<$2, t)
>
> The standard calc function usimplify also works for this use
> case:
>
> |  km |        ft |
> |-----+-----------|
> | 2.5 | 8202.0997 |
> #+TBLFM: $2 = usimplify($1 * @<$1 / @<$2)
>
> A lisp equivalent of the above
> #+begin_src elisp
> (calc-eval "usimplify(2.5 km / ft)")
> #+end_src
>
> #+RESULTS:
> : 8202.09973753
>
> Let's define a function that converts to base units
> #+begin_src elisp
>   (defmath ustd (expr) (math-simplify-units (math-to-standard-units expr nil)))
> #+end_src
>
> #+RESULTS:
> : calcFunc-ustd
>
> | distance | time   | speed       | std unit speed   | speed in ft/s    |
> |----------+--------+-------------+------------------+------------------|
> | 3 km     | 2.5 hr | 1.2 km / hr | 0.33333333 m / s | 1.0936133 ft / s |
> #+TBLFM: @2$3=$1/$2::@2$4=ustd($3)::@2$5=uconv($-1, ft/s)
> ++++++++++++++++++++++++++++++++++++

Re: org-tables with monetary amounts

[Daniele Nicolodi]

On 12/10/2020 10:22, Christian Moe wrote:
> 
> Hi, Daniele,
> 
> Good that it's working for you. I'll try to explain the unclear parts.
> 
> Daniele Nicolodi writes:
> 
>> On 24/09/2020 11:17, Christian Moe wrote:
>>
>>> 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)
>>
>> Having to explicitly use usimplify() is a bit too verbose. It would be
>> ideal if this could be somehow be implicit.
> 
> Yes, or at least with a less obtrusive syntax, like a mode flag,
> something like
> 
>   $3=$2+$1;u

I had a look and unfortunately the implementation of mode flags is not
easily extensible, thus this cannot easily implemented in independent code.

Also, it would probably make sense to have this being a Calc mode
activated with an org-table formula mode flag, rather than something
implemented in org-table. I don't know how easy it would be to extend Calc.


>>> 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!
>>
>> Having the desired fixed precision is quite important for this to be
>> useful. In y recent tests I had to drop the units (currencies) to
>> achieve this. It would be nice to find a way to avoid it.

Turns our this is actually easy. There are two ways to specify
visualization format for table cells: a format specifier like

$3=$1+$2;%.2f

which results in the units being stripped, and a Calc formatting mode
flag, like

$3=$1+$2;f2

which does not strip the units.

Cheers,
Dan

Re: org-tables with monetary amounts

[Neil Jerram]

[-- Attachment #1.1: Type: text/plain, Size: 2029 bytes --]

On Tue, 22 Sep 2020 at 17:05, Daniele Nicolodi <daniele@grinta.net> wrote:

> Hello,
>

Hi Daniele...


>
> I often use org-tables to work with monetary amounts.


Me too.  I use Org mode plus Scheme code to try to analyze my bank
statements and compare them against a budget.  Org is a convenient form for
specifying the inputs - e.g. the names of OFX files to read, and string
matches for how I want to categorize the transactions - and for displaying
the results.

Aside: Perhaps I'm misunderstanding them, but none of the open source
tools, including (h)ledger, seem to be of much help here.
- They focus on data entry and reconciliation, which I don't need as I'm
happy to download and use OFX files from my bank.
- They don't offer anything intelligent and automated for automatically
categorizing transactions.
- They don't have a sophisticated representation of a budget, and reporting
against that.
Do you know of a good forum (other than this!) for discussing such points?


> 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),
>

In my Scheme code, I convert between strings and pence:

    ;; In this file, an amount at rest is always represented as a string
    ;; with 2 decimal places.  Convert from that to an integer number of
    ;; pence:

    (define (amount->pence amount)
      (inexact->exact (round (* 100 (string->number amount)))))

    ;; And the reverse:

    (define (pence->amount pence)
      (format-2dp (/ (exact->inexact pence) 100)))


>
> - 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?
>

I've attached mine, in case you read Scheme and there's more detail in
there that is of interest.

Best wishes,
    Neil

[-- Attachment #1.2: Type: text/html, Size: 3109 bytes --]

[-- Attachment #2: nationwide.scm --]
[-- Type: text/x-scheme, Size: 8749 bytes --]


(add-to-load-path (in-vicinity (getenv "HOME") "ossaulib"))

(use-modules (ice-9 format)
	     (ice-9 regex)
	     (ossau ofx)
	     (srfi srfi-1)
	     (sxml simple)
	     (sxml match)
	     (srfi srfi-19))

;; (ossau ofx) provides 'get-transactions' to read transactions from a
;; single OFX file.  Let's build on that to read transactions from
;; multiple OFX files, assuming that the files given are already
;; ordered by date, so that the transactions in them follow on from
;; each other.

(define (read-transactions . files)
  (apply append (map get-transactions files)))

;; Return a date that is 00:00 UTC on the day of the given transaction.

(define (tx-date tx)
  (let ((d (string->date (tx:date tx) "~Y~m~d")))
    (make-date 0 0 0 0			; nsec sec min hr
	       (date-day d)
	       (date-month d)
	       (date-year d)
	       0			; zone offset, i.e. UTC
	       )))

;; Given a date, return a date that is the start of the next month.

(define (start-of-following-month d)
  (if (= (date-month d) 12)
      (make-date 0 0 0 0		; nsec sec min hr
		 1			; day of month
		 1			; month of year
		 (+ (date-year d) 1)
		 0			; zone offset, i.e. UTC
		 )
      (make-date 0 0 0 0		; nsec sec min hr
		 1			; day of month
		 (+ (date-month d) 1)
		 (date-year d)
		 0			; zone offset, i.e. UTC
		 )))

;; Given a date, return a date that is exactly N days later.

(define (n-days-later d n)
  (julian-day->date (+ (date->julian-day d) n)))

;; Compare two dates.

(define (date-before? d1 d2)
  (< (date->julian-day d1) (date->julian-day d2)))

;; Given a series of transactions, partition them into an alist of
;; smaller series according to time periods calculated from START-DATE
;; and NEXT-START-DATE-PROC: the start of the first period is
;; START-DATE, the start of the second period is (NEXT-START-DATE-PROC
;; START-DATE), the start of the third period is (NEXT-START-DATE-PROC
;; (NEXT-START-DATE-PROC START-DATE)), and so on.  In the returned
;; alist, each entry is (DATE . TX-LIST), where DATE is the exclusive
;; period end date (== the start date of the following period) for the
;; transactions in TX-LIST.

(define (partition-by-period txs start-date next-start-date-proc)
  (let loop ((txs txs)
	     (partition-end-date-exclusive (next-start-date-proc start-date))
	     (previous-partitions '())
	     (current-partition '()))
    (if (null? txs)
	(reverse (acons partition-end-date-exclusive current-partition previous-partitions))
	(let* ((tx (car txs)))
	  (if (date-before? (tx-date tx) partition-end-date-exclusive)
	      ;; This transaction is within the current partition.
	      (loop (cdr txs)
		    partition-end-date-exclusive
		    previous-partitions
		    (cons tx current-partition))
	      ;; This transaction is after the current partition.  But
	      ;; bear in mind that it might not be in the immediate
	      ;; next partition either.  The safest thing to do is to
	      ;; close out the current partition, advance the limit
	      ;; date, then loop round to look at the transaction in
	      ;; hand again.
	      (loop txs
		    (next-start-date-proc partition-end-date-exclusive)
		    (acons partition-end-date-exclusive current-partition previous-partitions)
		    '()))))))

;; Given a series of transactions, use SORT-FUNCTION to partition them
;; into an alist of smaller series.  We call SORT-FUNCTION on each
;; transaction, and it returns a string indicating the name of the
;; partition that that transaction should belong to.  In the result
;; alist, each entry is (PARTITION-NAME . TX-LIST).

(define (partition-by-sort-function txs sort-function)
  (let loop ((txs txs)
	     (partitions '()))
    (if (null? txs)
	(map (lambda (name-list-pair)
	       (cons (car name-list-pair) (reverse (cdr name-list-pair))))
	     (sort partitions
		   (lambda (x y)
		     (string<? (car x) (car y)))))
	(loop (cdr txs)
	      (let* ((tx (car txs))
		     (partition-name (sort-function tx)))
		(assoc-set! partitions
			    partition-name
			    (cons tx (or (assoc-ref partitions partition-name) '()))))))))

;; Given an alist of regexps and partition names, build a
;; SORT-FUNCTION that partitions transactions by matching the
;; transaction description against the regexps.

(define (regexp-alist->sort-function regexp-alist)
  (lambda (tx)
    (let ((description (tx:description tx)))
      (let loop ((regexp-alist regexp-alist))
	(cond ((null? regexp-alist)
	       "")
	      ((string-match (caar regexp-alist) description)
	       (cdar regexp-alist))
	      (else
	       (loop (cdr regexp-alist))))))))

;; In this file, an amount at rest is always represented as a string
;; with 2 decimal places.  Convert from that to an integer number of
;; pence:

(define (amount->pence amount)
  (inexact->exact (round (* 100 (string->number amount)))))

;; And the reverse:

(define (pence->amount pence)
  (format-2dp (/ (exact->inexact pence) 100)))

;; Given a series of transactions, return the sum of their amounts.

(define (sum-transactions initial-pence txs)
  (pence->amount (fold (lambda (tx previous-total-pence)
			 (+ previous-total-pence
			    (amount->pence (tx:amount tx))))
		       initial-pence
		       txs)))

;; Given a series of transactions, return an array suitable for Org
;; display that shows their total followed by the constituent
;; transactions and amounts.

(define (display-txs-with-initial-sum txs partition-name)
  (cons (list (string-append "\"" partition-name "\"") (sum-transactions 0 txs) "" "")
	(map (lambda (tx)
	       (list "" "" (tx:description tx) (tx:amount tx)))
	     txs)))

;; Examples to put the whole thing together.

(define (categorize-transactions-by-period sources next-start-date-proc regexp-alist show-all-txs)
  (let ((txs (apply read-transactions (map cadr sources)))
	(categorizer (regexp-alist->sort-function regexp-alist)))
    (apply append
	   (map (lambda (period-partition)
		  (cons* 'hline
			 (list (string-append "Period ending "
					     (date->string (car period-partition) "~1"))
			      "" "" "")
			(apply append
			       (map (lambda (name-list-pair)
				      (let ((detailed-display
					     (display-txs-with-initial-sum (cdr name-list-pair)
									   (car name-list-pair))))
					(if show-all-txs
					    detailed-display
					    (list (car detailed-display)))))
				    (partition-by-sort-function (cdr period-partition)
								categorizer)))))
		(partition-by-period txs
				     (tx-date (car txs))
				     next-start-date-proc)))))

(define (categorize-transactions-by-week sources regexp-alist show-all-txs)
  (categorize-transactions-by-period sources
				     (lambda (start-date)
				       (n-days-later start-date 7))
				     regexp-alist
				     show-all-txs))

(define (categorize-transactions-by-month sources regexp-alist show-all-txs)
  (categorize-transactions-by-period sources
				     start-of-following-month
				     regexp-alist
				     show-all-txs))

(define (periodic-balance sources initial-date initial-balance next-start-date-proc)
  (let* ((txs (apply read-transactions (map cadr sources))))
    (let loop ((partitions (partition-by-period txs
						(tx-date (car txs))
						next-start-date-proc))
	       (balance-pence (amount->pence initial-balance))
	       (output (list (list initial-date initial-balance))))
      (if (null? partitions)
	  (reverse output)
	  (let ((partition-end-balance (sum-transactions balance-pence (cdar partitions))))
	    (loop (cdr partitions)
		  (amount->pence partition-end-balance)
		  (cons (list (date->string (caar partitions) "~1")
			      partition-end-balance)
			output)))))))

(define (weekly-balance sources initial-date initial-balance)
  (periodic-balance sources
		    initial-date
		    initial-balance
		    (lambda (start-date)
		      (n-days-later start-date 7))))

(define (monthly-balance sources initial-date initial-balance)
  (periodic-balance sources
		    initial-date
		    initial-balance
		    start-of-following-month))

(define (two-column-table->alist table)
  (map (lambda (row)
	 (cons (car row) (cadr row)))
       table))

;; Comparison against a budget.

(define (sum-transactions-by-month-and-category sources regexp-alist)
  (let ((txs (apply read-transactions (map cadr sources)))
	(categorizer (regexp-alist->sort-function regexp-alist)))
    (let ((period-category-alist
	   (map (lambda (period-partition)
		  (cons (date->string (car period-partition) "~1")
			(map (lambda (name-list-pair)
			       (cons (car name-list-pair)
				     (sum-transactions 0 (cdr name-list-pair))))
			     (partition-by-sort-function (cdr period-partition) categorizer))))
		(partition-by-period txs
				     (tx-date (car txs))
				     start-of-following-month))))
      period-category-alist)))

Re: org-tables with monetary amounts

[Daniele Nicolodi]

On 25/09/2020 11:25, Neil Jerram wrote:
> Aside: Perhaps I'm misunderstanding them, but none of the open source
> tools, including (h)ledger, seem to be of much help here.
> - They focus on data entry and reconciliation, which I don't need as I'm
> happy to download and use OFX files from my bank.

I don't think the focus of ledger (or hledger, or beancount) is on data
entry. Actually beancount even has an ingest framework to deal with
importing data from bank statements and other sources.

> - They don't offer anything intelligent and automated for automatically
> categorizing transactions.

For beancount there is smart_importer
https://github.com/beancount/smart_importer and it is not difficult to
hack your own. I indeed use my own tool based on a simple machine
learning algorithm implemented by scikit-learn (Python).

> - They don't have a sophisticated representation of a budget, and
> reporting against that.

I am not sure what you mean by "sophisticated representation of a
budged". Once you categorize expenses in a (hierarchical) set of
expenses accounts it is trivial to use something like beancount to check
the balances against a budget.

> Do you know of a good forum (other than this!) for discussing such points?

The ledger-cli and the beancount mailing lists may be the right places
to discuss the use of these tools.

> I've attached mine, in case you read Scheme and there's more detail in
> there that is of interest.

My use case is very different, thus something external to org-mode is
not really useful.

Maybe an example would clarify what my use case is. I am working on a
research proposal where I need to provide details about funding
allocation in a few different categories over a certain amount of years.
In my document this is an org-table. Right now I have two choices to
edit the table:

- have monetary amounts in the cells (example "123.00 EUR") and do the
match externally,

- do the math in the org-table, specify the right formatting for all
computed cells, and still have to post-process it to add the currency
symbols to the input cells.

Either solution is sub-optimal. It would be nice if org-tables could
support monetary fields as they support time fields.

Cheers,
Dan

Re: org-tables with monetary amounts

[Neil Jerram]

[-- Attachment #1: Type: text/plain, Size: 4372 bytes --]

On Fri, 25 Sep 2020 at 12:36, Daniele Nicolodi <daniele@grinta.net> wrote:

> On 25/09/2020 11:25, Neil Jerram wrote:
> > Aside: Perhaps I'm misunderstanding them, but none of the open source
> > tools, including (h)ledger, seem to be of much help here.
> > - They focus on data entry and reconciliation, which I don't need as I'm
> > happy to download and use OFX files from my bank.
>
> I don't think the focus of ledger (or hledger, or beancount) is on data
> entry. Actually beancount even has an ingest framework to deal with
> importing data from bank statements and other sources.
>

Fair enough.  But I think what I really meant is that I would prefer tools
that can work directly on the data sources that I already have - i.e. OFX
files from my bank - without needing to convert those first into their own
file format.  To put it another way, if the file format per se is not an
important feature, what are the ways that the plain text accounting tools
add the most value?  The value I'm interested in is in (a) categorizing and
(b) reporting; but I haven't yet seen any of these tools having the kind of
intelligence, out of the box, that I'm looking for in those areas.


> > - They don't offer anything intelligent and automated for automatically
> > categorizing transactions.
>
> For beancount there is smart_importer
> https://github.com/beancount/smart_importer and it is not difficult to
> hack your own. I indeed use my own tool based on a simple machine
> learning algorithm implemented by scikit-learn (Python).
>

Thanks, but if I'm using a tool, I'd rather it had the intelligence
built-in (with appropriate configurability).  If I have to code...  well, I
have already done it myself.


>
> > - They don't have a sophisticated representation of a budget, and
> > reporting against that.
>
> I am not sure what you mean by "sophisticated representation of a
> budged". Once you categorize expenses in a (hierarchical) set of
> expenses accounts it is trivial to use something like beancount to check
> the balances against a budget.
>

I'm still thinking this through.  The simplest kind of budget would be a
number for each category, for the whole year.  But then you don't have a
very detailed evaluation if, say, you're looking at your actual expenditure
and it's only February.  Some bits of budget might only be expected on a
few specific dates; others are regular every month; others could be monthly
but varying in amount; others are allowances that could happen any time.
It seems to me that when you can predict particular expenses, it's better
if you can express those precisely and that an "expenditure against budget"
report can take those into account as well as the allowance pots.


>
> > Do you know of a good forum (other than this!) for discussing such
> points?
>
> The ledger-cli and the beancount mailing lists may be the right places
> to discuss the use of these tools.
>

Thank you.  I wish there was also a unified plaintextaccounting.org mailing
list, as I don't think my questions are for a specific tool.


>
> > I've attached mine, in case you read Scheme and there's more detail in
> > there that is of interest.
>
> My use case is very different, thus something external to org-mode is
> not really useful.
>
> Maybe an example would clarify what my use case is. I am working on a
> research proposal where I need to provide details about funding
> allocation in a few different categories over a certain amount of years.
> In my document this is an org-table. Right now I have two choices to
> edit the table:
>
> - have monetary amounts in the cells (example "123.00 EUR") and do the
> match externally,
>
> - do the math in the org-table, specify the right formatting for all
> computed cells, and still have to post-process it to add the currency
> symbols to the input cells.
>
> Either solution is sub-optimal. It would be nice if org-tables could
> support monetary fields as they support time fields.
>

Yes indeed.  The answer about Calc units sounded as though it could address
this - is there still a gap in applying it to your case?

Alternatively I believe an org-table formula can invoke arbitrary Lisp, so
in principle you could code a function to parse all the inputs (including
units), do the math needed, and then format the output with the desired
unit.

Best wishes,
    Neil



>
> Cheers,
> Dan
>

[-- Attachment #2: Type: text/html, Size: 6006 bytes --]