From: dbucklin@sdf.org 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) } $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