Airtable Expense Logging with Ledger Mode
summary = """Sometimes, we want to post individual ledger postings to some external API. In this article, we explore ledger-mode's source and figure out how to prepare the data for uploading to Airtable, a general-purpose user-friendly database."""
My PR implementing
ledger-xact-dategot accepted, so had to rewrite a bunch.
- Merged payee and date sections into new
- Added utility function to get transaction amount value.
- Changed implementation of final airtable exporting function to make use of
- Added metadata to display a custom summary, since the default doesn’t translate well.
- Merged payee and date sections into new
- 2019/4/11 First Release
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. [fn:airtable]
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.
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 PHP
You 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
Definition of Terms
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.”
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.
A line item detailing a change in the amount stored in an
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 PHP
xact is a group of
transactions contextualized by a date and a
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.
(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"
Conveniently, we have
ledger-xact-date. Since both
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")
There are always at least two amounts in every
xact because of double-entry
bookkeeping. [fn:bookkeeping] 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)))
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))
We’ll get the absolute value of this number later on, because we never want to submit negative expenses.
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 https://airtable.com/api and selecting the base we just made.
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
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"))
We can now create a command to post expenses! Don’t forget to make it
interactive, so we can invoke it from
(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) )
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
because we want this function to run asynchronously.
By now, you probably realized why I didn’t turn this into a library: the
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.
[fn:airtable] 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 unfortnately Notion doesn’t have an API. [fn:bookkeeping] An ancient technique that Ledger is all about. This article greatly helped me understand this thing.