org-table, fetch a specific cell from all tables in a file

Question

Summary of the problem

Hey guys, I am currently keeping track of the stocks I buy and sell in org mode using tables. I have N different tables in a given file, each table representing a transaction. Each file spans a time period of one month. At the end of the file, I want to have a table that summarizes the grand total. So far I have been manually writing the remote links to all the different tables manually, but this clearly does not scale well.

How I am doing it now

#+NAME: 1
| Company 1 |  Pcs | Cost | Brokerage|    Total  |       Date |
|-----------+------+------+----------+-----------+------------|
| BUY       | 100  |  9.1 |   1.365  |   911.365 | 26.11.2021 |
| SELL      | 100  | 9.30 |   1.395  |   928.605 | 29.11.2021 |
|           |      |      |          |     17.24 |            |

#+NAME: 2
| Company 2 |  Pcs | Cost | Brokerage|    Total  |       Date |
|-----------+------+------+----------+-----------+------------|
| BUY       | 100  |  9.1 |   1.365  |   911.365 | 26.11.2021 |
| SELL      | 100  | 9.30 |   1.395  |   928.605 | 29.11.2021 |
|           |      |      |          |     17.24 |            |

... Many more tables with more companies, etc..

| GRAND TOTAL | 34.48 |
#+TBLFM: $2=remote(1,@4$5)+remote(2,@4$5)+...remote(N,@4$5)...+remote(56,@4$5)

As can be seen, I have to manually write each remote statement, which does not scale well.

What I have discovered so far

I have discovered a function named org-table-get-remote-range, which I believe is a step in the right direction.

How I want it to be

#+NAME: 1
| Company 1 |  Pcs | Cost | Brokerage|    Total  |       Date |
|-----------+------+------+----------+-----------+------------|
| BUY       | 100  |  9.1 |   1.365  |   911.365 | 26.11.2021 |
| SELL      | 100  | 9.30 |   1.395  |   928.605 | 29.11.2021 |
|           |      |      |          |     17.24 |            |

#+NAME: 2
| Company 2 |  Pcs | Cost | Brokerage|    Total  |       Date |
|-----------+------+------+----------+-----------+------------|
| BUY       | 100  |  9.1 |   1.365  |   911.365 | 26.11.2021 |
| SELL      | 100  | 9.30 |   1.395  |   928.605 | 29.11.2021 |
|           |      |      |          |     17.24 |            |

... Many more tables with more companies, etc..

| GRAND TOTAL | 34.48 |
#+TBLFM: $2=vsum(*some function that fetches all of the cells*)

As can be seen, I am looking for a function that automates the process of fetching/collecting all the tables and enables me to compute the sum of them.

I would appreciate any feedback/input that puts me on the right track here, I am fairly new to org-mode and emacs (around 6 months). I would love if I was able to get emacs to solve this problem for me, as I tend to live in emacs anyways. Thanks guys!

Answer 1

You can use org-element-map on a parsed buffer to select elements or objects of a given type to operate on with a function. Here's an example for getting the names of all the named tables in the buffer as a list:

(org-element-map (org-element-parse-buffer) 
                 'table
                 (lambda (tbl) (plist-get (cadr tbl) :name)))

If you evaluate this in a buffer which consists of your summary section e.g., it will return ("1" "2").

See the documentation of org-element-map (C-h f org-element-map) for more details. You will just have to give it a more complicated function to get the items that you desire.

I don't have time to get a complete solution together, but I hope this will help you along.