Multi-step ledger org-capture template

Multi-step ledger org-capture template

[Stefan Huchler]

I want to insert a table from a email (gnus) into org-mode but before it
gets saved it should first be converted.

I can do it manually, copy the ascii table to a org-file:
mark it then use that command:

#+begin_src emacs-lisp
(defun my-make-table ()
      "docstring"
      (interactive)
      (org-table-convert-region (region-beginning)
                                (region-end) 2))
#+end_src
   
to convert it to a org-table (2 spaces or more is a seperator)
then use the following to create ledger style transaction:

#+begin_src emacs-lisp :var mapping=food-name-mapping data=import-table
  (let* ((rev-mapping
          (mapcar (lambda (x)
                    (list (nth 1 x) (car x)))
                  mapping)))
    (mapconcat
     (lambda (line)
       (format "%s \t\t%s St {=€%s}"
               (car (assoc-default (nth 1 line) rev-mapping))
               (nth 0 line)
               (nth 4 line)))
     data "\n"))
#+end_src

it uses this mapping table:

#+NAME: food-name-mapping
| expenses:food:vegetable:bananas | Bananas 125g |
|                                 |              |

To map the names of the items of the shop to the ones I use in ledger.
First step would be to create a capture that at least imports it as
org-table something like that:

#+begin_src emacs-lisp
  ("K" "import Transaction" plain
   (file+function "~/notes/finanzen.org"
                  (lambda () ""
                    (progn (org-babel-goto-named-src-block "balance")   
                           (org-babel-goto-src-block-head)(forward-line))))
   "%(copy-region-as-kill) %(switch-buffer \"temp-ledger-import\") %(yank) %(mark-buffer)
  %(org-table-create-or-convert-from-region) %(buffer-string)" 
   :jump-to-captured t
   :empty-lines-after 1
   :immediate-finish nil)
#+end_src

But I would want to do both convertions before I want to capture it in
the end.  Can I do that or do I need to work with org-capture-hooks?
Any suggestions?

Re: Multi-step ledger org-capture template

[Stefan Huchler]

Just in case somebody tries to come up with a solution, I made some
progress, so I can import the ascii table as org-table:

%(progn
   (with-current-buffer (org-capture-get :original-buffer)
     (call-interactively 'copy-region-as-kill))
   (with-temp-buffer
     "*import-temp*"
     (erase-buffer)
     (yank)
     (org-table-convert-region (buffer-end -1) (buffer-end 1) 2)
     (buffer-string)))

next step would be to extract the data from the columns replace the name
strings and format it in the ledger style.

Re: Multi-step ledger org-capture template

[Stefan Huchler]

Hmm I seem to be unable to access the data from the org-table.

org-element-map seems to be pretty poorly documented, I get the data but
in a strange format, (length row) returns 8 despite the table having
only 6 rows.

Also somehow I can't get to the data with cdr or nth or similar sequence
functions.

Is there some magic trick to get data (cells) from a org-table?


%(progn
   (with-current-buffer (org-capture-get :original-buffer)
     (call-interactively 'copy-region-as-kill))
   (with-temp-buffer
     "*deissenhof-import-temp*"
     (erase-buffer)
     (yank)
     (org-table-convert-region (buffer-end -1) (buffer-end 1) 2)
     (org-element-map (org-element-parse-buffer) 'table-row
       (lambda (row)
	 (print "ROW {{{{")
	 (print (length row))
	 (print "}}}} ROW"))
       nil t)
     (buffer-string)))

Re: Multi-step ledger org-capture template

[Stefan Huchler]

In case somebody wants something similar, I post my solution I came up
with here:

%(progn
   (defun get-table-content (&optional start end)
     (let* ((start (or start (buffer-end -1)))
	    (end (or end (buffer-end 1)))
	    (lines
	     (s-split "\n" (buffer-substring-no-properties
			    start end))))
       (remove nil
	       (mapcar
		(lambda (line)
		  (mapcar
		   (lambda (elem)
		     (string-trim elem))
		   (remove "" (split-string
			       line "|"))))
		lines))))
   (with-current-buffer (org-capture-get :original-buffer)
     (call-interactively 'copy-region-as-kill))
   (let* (mapping)
     (with-temp-buffer
       "*shop-name-mapping*"
       (erase-buffer)
       (insert-file-contents "capture-templates/products-mapping.org")
       (let* ((start (progn (goto-char (point-min))
			    (forward-line 1) (point))))
	 (setq mapping (mapcar (lambda (x) (cons (nth 1 x) (car x)))		       
		       (get-table-content start)))))
     (with-temp-buffer
       "*shop-import-temp*"
       (erase-buffer)
       (yank)
       (org-table-convert-region (buffer-end -1) (buffer-end 1) 2)    
       (let* ((data (get-table-content)))
	 (erase-buffer)
	 (insert
	  (concat "  " (org-read-date nil nil) " * Shop-name\n"
		  (mapconcat
		   (lambda (line) (format "      %s \t\t%s St {=€%s}"
			     (assoc-default (nth 1 line) mapping)
			     (nth 0 line) (nth 4 line)))
		   data "\n")
		  "\n      assets:bank:chequing"))
	 (ledger-mode)
	 (let ((start (progn
			(goto-char (point-min))
			(forward-line 1) (point)))
	       (end (progn
		      (goto-char (point-max))
		      (forward-line -1) (point))))
	   (ledger-post-align-postings start end))	 
	 (buffer-string)))))

products-mapping would like like that:

#+NAME: food
| expenses:food:spice:Basilikum | Basilikum bio Sonnentor, 15g |
| expenses:food:spice:Oregano   | Oregano bio Sonnentor, 20g   |

Of course it asumes that you have some html or email bill with a table
selected and you would have to change the (nth x line) depending where
in the bill the name, amount and price is listed, and maybe change the
currency.

Re: Multi-step ledger org-capture template

[ST]

Thank you for sharing!

It would be nice to be able to generate a .pdf invoice based on a
predefined template (in .org/.tex/etc formats; maybe utilizing org-mode
exporter) for certain ledger transaction (by default the current one,
highlighted by cursor in ledger-mode).

Did anybody wrote something similar in the past?

On Sun, 2018-08-26 at 23:53 +0200, Stefan Huchler wrote:
> In case somebody wants something similar, I post my solution I came up
> with here:
> 
> %(progn
>    (defun get-table-content (&optional start end)
>      (let* ((start (or start (buffer-end -1)))
> 	    (end (or end (buffer-end 1)))
> 	    (lines
> 	     (s-split "\n" (buffer-substring-no-properties
> 			    start end))))
>        (remove nil
> 	       (mapcar
> 		(lambda (line)
> 		  (mapcar
> 		   (lambda (elem)
> 		     (string-trim elem))
> 		   (remove "" (split-string
> 			       line "|"))))
> 		lines))))
>    (with-current-buffer (org-capture-get :original-buffer)
>      (call-interactively 'copy-region-as-kill))
>    (let* (mapping)
>      (with-temp-buffer
>        "*shop-name-mapping*"
>        (erase-buffer)
>        (insert-file-contents "capture-templates/products-mapping.org")
>        (let* ((start (progn (goto-char (point-min))
> 			    (forward-line 1) (point))))
> 	 (setq mapping (mapcar (lambda (x) (cons (nth 1 x) (car x)))		       
> 		       (get-table-content start)))))
>      (with-temp-buffer
>        "*shop-import-temp*"
>        (erase-buffer)
>        (yank)
>        (org-table-convert-region (buffer-end -1) (buffer-end 1) 2)    
>        (let* ((data (get-table-content)))
> 	 (erase-buffer)
> 	 (insert
> 	  (concat "  " (org-read-date nil nil) " * Shop-name\n"
> 		  (mapconcat
> 		   (lambda (line) (format "      %s \t\t%s St {=€%s}"
> 			     (assoc-default (nth 1 line) mapping)
> 			     (nth 0 line) (nth 4 line)))
> 		   data "\n")
> 		  "\n      assets:bank:chequing"))
> 	 (ledger-mode)
> 	 (let ((start (progn
> 			(goto-char (point-min))
> 			(forward-line 1) (point)))
> 	       (end (progn
> 		      (goto-char (point-max))
> 		      (forward-line -1) (point))))
> 	   (ledger-post-align-postings start end))	 
> 	 (buffer-string)))))
> 
> products-mapping would like like that:
> 
> #+NAME: food
> | expenses:food:spice:Basilikum | Basilikum bio Sonnentor, 15g |
> | expenses:food:spice:Oregano   | Oregano bio Sonnentor, 20g   |
> 
> Of course it asumes that you have some html or email bill with a table
> selected and you would have to change the (nth x line) depending where
> in the bill the name, amount and price is listed, and maybe change the
> currency.
> 
> 
> 
> 
> 
> 

Re: Multi-step ledger org-capture template

[Neil Jerram]

On 27 August 2018 09:05:32 BST, ST <smntov@gmail.com> wrote:
>Thank you for sharing!
>
>It would be nice to be able to generate a .pdf invoice based on a
>predefined template (in .org/.tex/etc formats; maybe utilizing org-mode
>exporter) for certain ledger transaction (by default the current one,
>highlighted by cursor in ledger-mode).
>
>Did anybody wrote something similar in the past?

In this general area, I have been developing a combination of org mode and Scheme code whose aim is to help me define budgets, and then track whether my actual spending is in accordance with those budgets.

I'm not using ledger here. I hope I'm not missing something, but I looked at ledger and it didn't seem to help with the main thing I thought I needed, which was to read in transaction dump files from my bank and present those in a summarised form according to my budget categories. I use org tables to define what the categories are, and how particular transaction entries are mapped into those categories (or else show up as "Unknown").

It's a work in progress... In case anyone else is interested in this particular itch, I'd be interested to hear your thoughts.

Best wishes,
      Neil