Changelog
- 2019-04-13 My PR implementing
ledger-xact-dategot accepted, so had to rewrite a bunch.- Merged payee and date sections into new
xactsection. - Added utility function to get transaction amount value.
- Changed implementation of final airtable exporting function to make use of
ledger-xact-dateandledger-transaction-amount-value - Added metadata to display a custom summary, since the default doesn’t translate well.
- Merged payee and date sections into new
- 2019-04-11 First Release
Introduction
Many companies and organizations subsidize certain things like transit, food, and lodging for their employees, provided the latter log these expenses via some system defined by the former. My company uses Airtable.[1]
If you use Ledger to maintain your personal accounting, or really any other method for that matter (but seriously. Use Ledger), it will be tedious to log your subsidized/reimbursable expenses on both books. Despite the inconvenience, this data is valuable to you, and you should keep track of it to better understand your lifestyle.
Luckily for us, Airtable has an API, and emacs is emacs. This article details my efforts in building a set of emacs functions for posting expenses from a Ledger file onto Airtable. Because I want this article to be as accessible and as informative as possible, I will go through the derivation of this script including exploring ledger-mode’s implementation.
Ledger
A Typical Receipt
Let’s say that earlier in the day, you needed to gas up, so you passed by Richard Stallman’s Gas Station. It just so happens that the Philippine Peso (PHP), despite all odds, has become the dominant currency of the United States. The acronym is no longer associated with PHP: Hypertext Processor, and the latter is now nothing more than a bad memory.
2019/03/29 Richard Stallman's Gas Station
expenses:transport:fuel 2000 PHP
expenses:food 200 PHP
assets:cash -2200 PHPYou also spent 200 PHP on food because it was there, just begging to be bought, and human nature doesn’t change much between parallel realities.
In ledger, we call the snippet above an xact as a whole, rather than the more
obvious “transaction”. To avoid confusion, we’ll have to define a few more
terms.
Definition of Terms
payee
A description of the xact. Could be just as simple as “John” or “McDonald’s”, but sometimes it’s more detailed like “Martine, for that gallon of mayonnaise.”
account
A “bucket” of money. In Ledger, accounts can have a sort of tree structure where
“tags” of increasing specificity are joined together by colons to give structure
and grouping. e.g. expenses:transport:fuel, assets:cash, assets:savings:php
transaction
A line item detailing a change in the amount stored in an account. The
account is separated from the amount by at least two spaces. Referred to in
the Ledger source as acct-transaction. For example:
expenses:transport:fuel 2000 PHPAn xact is a group of transactions contextualized by a date and a payee.
Utility
Before we start tinkering, we have to first realize that since we’re using
org-mode and babel, we can execute whatever code we write. We’re referring to
that gas receipt above, so we need to find some way to easily refer to various
parts of that receipt easily. org-babel-goto-named-src-block exists.
(save-excursion
(org-babel-goto-named-src-block "Gas Receipt")
(org-element-at-point))(src-block
(:language "ledger" :switches nil :parameters ":results silent" :begin 1643 :end 1922 :number-lines nil :preserve-indent nil :retain-labels t :use-labels t :label-fmt nil :value "2019/03/29 Richard Stallman's Gas Station\n expenses:transport:fuel 2000 PHP\n expenses:food 200 PHP\n assets:cash -2200 PHP\n" :post-blank 1 :post-affiliated 1663 :name "Gas Receipt" :parent nil))Yup, that looks right. If we go down one row, we should get both the date and payee.
(save-excursion
(org-babel-goto-named-src-block "Gas Receipt")
(next-line)
(thing-at-point 'line t))"2019/03/29 Richard Stallman's Gas Station\n"Booyeah. Let’s wrap this all up into a function.
(defun goto-gas-receipt (line-offset)
(org-babel-goto-named-src-block "Gas Receipt")
(next-line line-offset))Let’s test it.
(save-excursion
(goto-gas-receipt 2)
(thing-at-point 'line t))" expenses:transport:fuel 2000 PHP\n"We’re ready!
The xact
Conveniently, we have ledger-xact-payee and ledger-xact-date. Since both
work with point, we get to use our cool utility function!
(save-excursion
(goto-gas-receipt 1)
(list (ledger-xact-payee)
(ledger-xact-date)))("Richard Stallman's Gas Station" "2019/03/29")Sweet.
The Amount
There are always at least two amounts in every xact because of double-entry bookkeeping.[2] Because ledger entries can get more complicated than this, we can’t just assume the simplest case. Instead, we can let the user specify it for us!
(save-excursion
(goto-gas-receipt 4)
(let ((point-context (ledger-context-at-point)))
(ledger-context-field-value point-context 'commoditized-amount)))"-2200 PHP"This is good, but we can do better. On its own, a string isn’t very usable. We can make a function that simply returns the value and discards the currency. For now, we don’t want to support multiple currencies.
(defun ledger-transaction-amount-value ()
"Returns the value of the amount of a transaction without its attached currency."
(let ((amount (ledger-context-field-value (ledger-context-at-point)
'commoditized-amount)))
(string-to-number (car (split-string amount)))))
(save-excursion
(goto-gas-receipt 4)
(ledger-transaction-amount-value))-2200We’ll get the absolute value of this number later on, because we never want to submit negative expenses.
Airtable
Setup
This section is tricky, because this involves secrets: the “project id” included in the URL, and the API key. Mine are… just kidding. Let’s load the encrypted secrets.
(load-file "../secrets/airtable-secrets.el")
(require 'airtable-secrets)We’ll need the excellent emacs-request library.
(use-package request :ensure t)Each Airtable base has its own tables and schema, so it will be up you to figure out the right table to interact with, and exact fields to use in the JSON payload. Let’s created a blank airtable base from the expense tracking template. We can figure out the api for this particular base by going to and selecting the base we just made.
Experimentation
I’m wary about running POST requests right off the bat without being sure about
our requests. We’ll use request.el for our http needs. Let’s try to get the
list of receipts, and since we’re only doing this for confirmation, we can set
(1) maxRecords to 1. We need to make the request synchronous (2) so that org
mode can capture the returned value.
(request-response-data
(request airtable-secrets-url
:type "GET"
:params '(("maxRecords" . 1) ;; (1)
("view" . "Main View"))
:sync t ;; (2)
:parser 'json-read
:headers `(("Content-Type" . "application/json")
("Authorization" . ,(format "Bearer %s" airtable-secrets-auth-token)))))((records .
[((id . "recvM8nBwdDtki4vo")
(fields
(Receipt\ Photo .
[((id . "attRl2O8I67NQBQXo")
(url . "https://dl.airtable.com/0cWfA9hiTOWP5QfdUNij_cactuscastle.jpg")
(filename . "cactuscastle.jpg")
(size . 16064)
(type . "image/jpeg")
(thumbnails
(small
(url . "https://dl.airtable.com/MBqpodIaQJWI62Q5t1iq_cactuscastle.jpg")
(width . 48)
(height . 36))
(large
(url . "https://dl.airtable.com/WQVXLIuiQTGYIgDoAIxY_cactuscastle.jpg")
(width . 256)
(height . 191))))])
(Category . "Interior Decor")
(Short\ Description . "Cactus")
(Total . 11.5)
(Date\ &\ Time . "2015-11-06T14:22:00.000Z")
(Notes . "A cute blue cactus with golden spines, will go great in the dining room.")
(Who\ Paid\? . "Maritza"))
(createdTime . "2015-08-03T23:10:03.000Z"))]))Great, we got a response! Now let’s try to POST a new entry. We’re setting the payor as “Quinns” because that’s one of two values allowed by the template.
(request-response-data
(request airtable-secrets-url
:type "POST"
:sync t
:parser 'json-read
:data (json-encode `(("fields" . (("Short Description" . "Testing")
("Who Paid?" . "Quinns") ;; (1)
("Date & Time" . "2019-04-09T14:22:00.000Z")
("Total" . 10)
))))
:headers `(("Content-Type" . "application/json")
("Authorization" . ,(format "Bearer %s" airtable-secrets-auth-token)))
))((id . "recFyrCYKAAwjELUr")
(fields
(Short\ Description . "Testing")
(Total . 10)
(Date\ &\ Time . "2019-04-09T14:22:00.000Z")
(Who\ Paid\? . "Quinns"))
(createdTime . "2019-04-09T11:25:12.000Z"))Packaging
We can now create a command to post expenses! Don’t forget to make it
interactive, so we can invoke it from M-x.
(defun ledger-airtable-post-expense ()
"Post an expense to airtable."
(interactive)
(let* ((xact-date (ledger-xact-date))
(xact-payee (ledger-xact-payee))
(xact-amount (ledger-transaction-amount-value))
(amount (abs xact-amount))
(date (replace-regexp-in-string (regexp-quote "/") "-" xact-date))
(date-time (format "%sT12:00:00.000Z" date)))
(request blog--ledger-airtable-secrets-base-url
:type "POST"
:sync t
:parser 'json-read
:data (json-encode `(("fields" . (
("Date & Time" . ,date-time)
("Who Paid?" . "Quinns")
("Total" . ,amount)
("Short Description" . ,xact-payee)
))))
:headers `(("Content-Type" . "application/json")
("Authorization" . ,(format "Bearer %s" blog--ledger-airtable-secrets-auth-token)))
:success (cl-function
(lambda (&key data &allow-other-keys)
(print "Expense Posted!")))
:error (cl-function
(lambda (&key error-thrown &allow-other-keys)
(print error-thrown))))))
(save-excursion
(goto-gas-receipt 4)
(ledger-airtable-post-expense)
)"Expense Posted!"A quick trip to Airtable tells me that indeed, the entry has been posted. Now all I have to do is take a picture of the receipt, but that’s out of our scope because the Airtable mobile app makes that easy.
Notice that we have filled up the various callback functions in the request,
because we want this function to run asynchronously.
Conclusion
By now, you probably realized why I didn’t turn this into a library: the
implementation of ledger-airtable-post-expense is too dependent on the schema
of the Airtable base it wants to talk to. I could factor this out to accept some
function that allows one to build out the "fields" data structure, but at that
point, we go back to the implementation we came up with here.
That being said, feel free to copy this function and modify it to suit your needs. Just remember, you do so at your own risk. I am not responsible for you messing up your whole company’s database. 😉
Lastly, you may look at the source for this entire blog here. Load it up in emacs. If you have org, babel, etc…, then you can probably hit C-c C-c and evaluate the src-blocks. Of course, you’ll have to modify the bits with secrets.
Footnotes
[1]: My company also uses Airtable for other administrative tasks like tracking leaves of absence and many other things. We’ve mostly switched over to Notion because it performs better as a knowledge base, but unfortunately Notion doesn’t have an API.
[2]: An ancient technique that Ledger is all about. This article greatly helped me understand this thing.
Leave a Reply