From:
[email protected]
Date: 2020-01-19
Subject: Sharing Expenses with Ledger
My SO, Kim, and I share expenses for things like groceries, rent,
car maintenance, and meals out. Up to now, we've been using a
spreadsheet I found on moneyunder30.com[1]. We've started to out-
grow it, so I decided to roll my own solution using ledger-cli[2]
and a custom, email-based interface.
The spreadsheet was falling short in a couple ways. It wasn't easy
to capture transaction detail. We could put it into the comments
on a given cell, but it wasn't convenient and we didn't use it con-
sistently. I also needed to determine how to create what we called
"pass-through" transactions. These represented instances when Kim
or I would purchase something on the other's behalf. Unlike our
shared expenses, we each needed to pay the other back for the full
amount of the purchase. I created a place for this on the spread-
sheet, but it wasn't very transparent.
Using a more sophisticated solution should resolve these issues.
The path forward was pretty clear:
1. Learn the basics of accounting
2. Learn ledger-cli
3. Build an email interface to ledger
Learning Accounting
Historically, I've had a hard time with the concept of double-entry
bookkeeping. So, I knew I would need help to wrap my head around
the concept. I ran a quick search on YouTube and found a series of
videos on accounting basics by Accounting Stuff[3]. In the chan-
nel's "about" section, channel host James writes,
I created this channel in 2018 in order to share the
knowledge and experience that I have built up over the
past 6+ years working as a Qualified Accountant at the
Big 4 and in Industry - at large Listed and Private Com-
panies.
The channel had a whole playlist dedicated to accounting basics; I
jumped into it immediately. I found his videos very easy to con-
sume, and I learned all the pieces I had been missing. As I
watched James' videos, I worked out a number of test scenarios on
paper.
One concept I struggled with was defining the *entity* to which my
journal entries belonged. I was trying to track expenses for Kim
and myself, so is "Kim and myself" the entity? That was a bit
tricky to think about, so I picked an entity that, at first, would
have no existing financial relationship to Kim or myself -- our
cat, Abner.
Abner was going to start a business. The purpose of the business
would be to purchase things that Kim and I needed using investment
we provided. Each purchase would be represented by a transfer of
equity from Kim or me to Abner's cash account, and then a transfer
from cash to the expense account. As purchases were made, the Ex-
pense account would be balanced by equity accounts assigned to Kim
or me.
At the end of the month, Abner's income would be calculated by sub-
tracting expenses from revenue. Since Abner doesn't have any way
to generate revenue, he always operates at a loss. Abner's nega-
tive income would then be distributed back to Kim's and my equity
accounts. These steps constitute the period-end close process.
The balances of our equity accounts would indicate how much money
needed to be transferred to even things out.
Learning Ledger CLI
So far, I had been doing all of my exercises on paper[4], but I
needed to move to a computer-based solution. ledger-cli[5] has
been on my radar for a while, and I was excited to dig into it.
From the ledger-cli page,
Ledger is a powerful, double-entry accounting system that
is accessed from the UNIX command-line. Ledger, begun in
2003, is written by John Wiegley and released under the
BSD license.
Having learned the basics of double-entry bookkeeping, learning
ledger was pretty straight-forward. It uses a plain-text data for-
mat that is easy to manage by hand with an editor. Ledger doesn't
categorize accounts as normal debit accounts or normal credit ac-
counts. I was glad that I learned about account types from Ac-
counting Stuff so that I could apply these rules myself.
I created a test file and ran a few scenarios using the ledger re-
porting tool. Soon, I was replacing my test data with real data
from the spreadsheet for the previous month. I created a month-end
process to capture the expense totals for a target month, close ex-
penses to income, and distribute the negative income back to our
equity accounts. My month-end close process produced the same re-
sults as our spreadsheet.
To handle our pass-through transactions, I considered using a com-
bination of A/R and A/P accounts. I decided to simplify things by
representing it as a transfer from one equity account to another.
Building an Email Interface
To make it easy for Kim and me to interact with the ledger, adding
transactions and pulling reports, I created an email-based user in-
terface. We would need to be able to add transactions and pull
balance and register reports.
Setting up an email server is pretty complicated and I'm not going
to cover it here. In short, I set up Postfix to receive email at a
given domain -- let's call it example.com. When Postfix receives
an email, it needs to decide what to do with it. It looks at a
file, `/etc/aliases`, for information on what to do with a message
based on who it's been sent to. In most cases, it will copy the
message to the mailbox of the intended recipient on the system.
However, it's possible to specify that a program should process the
message, instead. This is how mailing lists and those email ad-
dress verification tools work. The content of the message contains
data that the program will use to complete its task. I've written
about this before, in Email as an Application Interface[6].
In my case, there were three steps in processing an incoming email
message and providing a response to the sender. First, I needed to
trim off all the headers and other content that came with the
email, but that I didn't need. Second, I needed to parse the pay-
load and update files or run ledger reports and capture the output.
Finally, I needed to get the output back to the sender by email.
To better encapsulate related functionality, I updated my email
parsing script so that it could be used in a pipeline and output a
consistent format. I called it `mail_trim` and it lives in
/usr/local/bin where Postfix can get to it.
#!/usr/bin/awk -f
BEGIN {
boundary_seen = 0
data_seen = 0
boundary = "^$"
line = ""
}
# If we've started reading data and we hit a blank line, exit
(data_seen == 1) && ($0 ~ /^$/) {
exit
}
# We've seen the boundary and can start collecting data
(boundary_seen == 1) && ($0 !~ /^$/) {
line = line $0 "\n"
data_seen = 1
# reset the data gathering if we see this. Fragile.
if (line ~ "text/plain" ) {
line = ""
data_seen = 0
}
}
/^From:/ {
$1 = ""; from = $0
sub(/^.*</,"",from)
sub(/>.*$/,"",from)
}
$0 ~ boundary {
boundary_seen = 1
}
END {
printf "%s\n%s", from, line
}
From `mail_trim`, the payload is sent to my new, ledger-compatible
`expenses` script. This script consumes the payload and runs re-
ports or adds transactions.
#!/usr/bin/awk -f
# take output from mail_trim and act on contents
BEGIN{
expfile = "/path/to/expenses.txt"
from = ""
qw = "\047"
date = strftime("%Y-%m-%d")
bash = "/bin/bash"
msmtp = "msmtp -t"
}
# mail_trim puts the from address in the first line.
NR == 1 {
from = $0;
if (from !~ /..*@..*/) exit;
next;
}
# we can process multiple requests
/^[Bb]alance/ {
cmd = "ledger -p " qw "this month" qw " -f " expfile " --balance-format " qw "%A\\t%(display_total)\\n" qw " balance"
report = "";
while ((cmd | getline ln) >0 ){ report = report ln "\n"}
close(cmd)
print "To: " from | msmtp
print "Subject: Balance" | msmtp
print "" | msmtp
print report | msmtp
close(msmtp);
next;
}
/^[Rr]egister/ {
cmd = "ledger -p " qw "this month" qw " -f " expfile " --register-format \"%D\\t%t\\t%A\\t%P\\n\" register Kim Dave"
report = "";
while ((cmd | getline ln) >0 ){ report = report ln "\n"}
close(cmd)
print "To: " from | msmtp
print "Subject: Balance" | msmtp
print "" | msmtp
print report | msmtp
close(msmtp);
next;
}
/^[0-9]/ {
amt = $1
payee = $0
sub(/\S+\s+/,"",payee);
if (from ~ /Kim/) {
acct = "Equity:Kim"
} else {
acct = "Equity:Dave"
}
printf "\n%s %s\n %s %s\n %s\n", date, payee, "Expenses", amt, acct >> expfile
next;
}
/^[Pp] *[0-9]/ {
amt = $1
sub(/^[Pp] */,"",amt);
payee = $0
sub(/\S+\s+/,"",payee);
if (from ~ /Kim/) {
cacct = "Equity:Kim"
dacct = "Equity:Dave"
} else {
cacct = "Equity:Dave"
dacct = "Equity:Kim"
}
printf "\n%s %s\n %s %s\n %s\n", date, payee, dacct, amt, cacct >> expfile
next;
}
I ran into an issue where some characters in the text/plain portion
of the email were being encoded. This caused transactions in my
expense file to have invalid data. After a little searching, I
found that this was a transport encoding known as quoted-printable,
or QP. At first, I was concerned that I would have to create my
own decoder, but I found a simple decoder, `qprint`, in the Debian
package collection. After adding that to the pipeline in
/etc/aliases, transactions came in perfectly. The entry in
/etc/aliases now looks like this:
expenses: "|/usr/local/bin/mail_trim |/usr/bin/qprint -d -n |/usr/local/bin/expense"
The default output format for ledger targets a display using a
fixed-width font. Because these reports would be viewed in an
email client using a proportional-width font, I had to develop cus-
tom output formats. You'll see them as arguments to the `--bal-
ance-format` and `--register-format` options.
I'm happy that I've finally been able to make use of ledger-cli,
and I'm glad that I took the time to learn the basics of accounting
from Accounting Stuff. Now I have a solution that is more conve-
nient and can be extended in the future.
References
[1]:
https://www.moneyunder30.com
[2]:
https://www.ledger-cli.org/
[3]:
https://www.youtube.com/channel/UCYJLdSmyKoXCbnd-pklMn5Q
[4]:
https://www.doanepaper.com/
[5]:
https://www.ledger-cli.org/
[6]:
https://davebucklin.com/play/2018/08/26/email-application-interface.html
