pig-monkey.com

You are currently viewing all posts tagged with plaintextaccounting.

Tracking My Phone Bill with Ledger

Back in 2013 I bought my first smartphone and signed up for a T-Mobile prepaid plan, referred to at the time as the “Walmart Plan”. The plan cost $30 per month, was intended for new customers only, and was supposed to only be available to those who purchased the SIM card at a Walmart. It offered a small amount of voice minutes and a large amount of data, which struck me as what one would want with one of these newfangled computer-phones. I bought a SIM card, figured out how to get T-Mobile to sign me up on the plan despite not stepping foot into a Walmart, and haven’t looked back since.

One of the things that appeals to me about the setup is the level of separation it gives me from the service provider. I purchased the phone from the manufacturer and the SIM card I bought with cash. I fund the plan by purchasing refill cards from third-party vendors. I have never provided T-Mobile with any financial information. They have no ability to take any money from me, except what I give them when trading in the refill cards. Obviously, the primary business of any mobile communications provider is location tracking, so I can’t refer to my relationship with them as “privacy preserving”, but I like to think it does allow me to retain some level of agency that is lost in a more traditional relationship.

If there is a downside to this setup, it is that it can be difficult to understand what I actually pay per month. The plan costs $30. There is some limit to the number of SMS messages, but I have no idea what it is. Data is “unlimited”, which means throttled over 4GB, but I don’t think I’ve ever approached even half that limit. Minutes are limited, and if I go over the allotment I’m charged a higher rate, but the service continues as long as the balance of the account remains positive.

I always want to keep more than $30 in the account, in case I do go over the allotted minutes. So I buy $50 refill cards. They have $50 of value, and are supposed to cost me $50. But the vendor I tend to by them from charges a $1 service fee, offers a points program that sometimes results in a discount being applied, and frequently has sales that offer a couple dollars off. So I end up paying something like $48-51. If I do exceed the limits of the plan, I may end up buying a $50 card one month and the next. More often, I buy a $50 card one month and have enough left over in the account that I do not need to refill it the following month. My plan renews on the 5th of the month, so some months I may end up spending $100 by buying one refill card on the first day of the month and another on the last in anticipation of the following month’s renewal.

All of that is to say that it is difficult to have an intuitive feel for what my average monthly phone expense is, but it’s important that I can get that number so that I can determine if the plan is still working or if I should look for a better offer. Fortunately, this is a thing that Ledger makes extremely simple.

Whenever I purchase a refill card, I log the transaction in the Expenses:Utilities:Phone account. With that done, I can ask Ledger to report on all transactions in that account, grouped by month, with a running average in the final column.

$ ledger register utilities:phone --monthly --average --begin 2019-08
2019-09-01 - 2019-09-30     Expenses:Utilities:Phone    $48.50      $48.50
2019-10-01 - 2019-10-31     <None>                      0           $24.25
2019-11-01 - 2019-11-30     Expenses:Utilities:Phone    $46.50      $31.67
2019-12-01 - 2019-12-31     <None>                      0           $23.75
2020-01-01 - 2020-01-31     Expenses:Utilities:Phone    $51.00      $29.20
2020-02-01 - 2020-02-29     <None>                      0           $24.33
2020-03-01 - 2020-03-31     Expenses:Utilities:Phone    $48.50      $27.79
2020-04-01 - 2020-04-30     <None>                      0           $24.31
2020-05-01 - 2020-05-31     Expenses:Utilities:Phone    $48.50      $27.00
2020-06-01 - 2020-06-30     Expenses:Utilities:Phone    $46.50      $28.95
2020-07-01 - 2020-07-31     <None>                      0           $26.32
2020-08-01 - 2020-08-31     Expenses:Utilities:Phone    $51.00      $28.38

Over the past 12 months, I have spent an average of $28.38 per month on phone service. I’m ok with that.

Adding Metadata to Ledger

As mentioned previously, two of the primary reasons I use Ledger are the intimate awareness it provides of how money is moving, and its timely representation of current balances. The third primary reason I use the tool is for the activity history it provides.

Recording almost every transaction I make (excepting only some small petty cash) allows me to look at years past and build an accurate picture of what I was doing. Most activities leave some sort of financial record, even if the transaction is only tangential to the activity itself. This is the sort of information that I would be extremely uncomfortable providing to a third-party, but is quite useful to have myself.

Storing receipts provides an additional layer of detail to the history created in the Ledger journal. As an intermediate between the full receipt and the basic journal entry, I find it is extremely valuable to use comments and tags within the journal. Almost every transaction in my Ledger journal that is not for food has a comment describing what goods or service was purchased.

In Ledger, comments begin with a semicolon. I store them below the first line of the transaction. When placed here, Ledger refers to these comments as notes. When purchasing goods, I add one comment – or note – for each unique item on the transaction. For instance, I might buy socks:

2018-07-02=2018-07-01 * Socks Addict
    ; Darn Tough Light Cushion No Show Tab, Black/Grey, Large, 2x
    Expenses:Clothing:Footwear                $33.40
    Liabilities:Bank:Visa

The comment tells me exactly what was purchased. More importantly, it tells me the model name, the color, and the size of the item. If I want to purchase another pair of identical socks, I can do so easily. This seems like a minor thing, but when it is applied to everything I buy, it is hard to overstate how greatly this ability has improved my quality of life over the past decade.

In Ledger, comments are searchable. Say I want to list every transaction where I purchased a pair of Darn Tough socks. I buy them from different retailers, so I can’t filter by the payee. All the transactions are in the Expenses:Clothing:Footwear category, but that category includes socks from other manufacturers and well as other things that go on my feet, so I can’t filter by that. But I can query all transactions with a note which includes the string “darn tough”.

$ ledger register note darn tough

A tag is a special kind of comment. Tags are useful if you have the foresight to realize that a particular transaction should be grouped with other transactions, but that transactions within the group will likely have different payees or accounts. Tags start and end with a :. Multiple tags can be chained together.

I use tags for vacations. Expenses related to any vacation are tagged with two tags: a generic :vacation: tag, and a tag specific to the vacation. This allows me to easily see what I spend on vacations in general, or any one vacation specifically. For example, in 2018 I took a 24-hour trip to Las Vegas to see Nine Inch Nails. That trip included numerous transactions in unrelated accounts: concert tickets, airline tickets, accommodations, ground transport, food, etc. All transactions were tagged with :vacation: and :nin-vegas-2018:. As an example, here is the transaction for purchasing the concert tickets:

2018-03-30=2018-03-28 * AXS
    ; Nine Inch Nails at The Joint, Las Vegas
    ; :nin-vegas-2018:vacation:
    Expenses:Entertainment:Performance        $95.50
    Assets:Bank:Checking

Ledger makes it easy to see all the expenses associated with that trip, both in total and broken out into different expense accounts.

$ ledger balance expense and %nin-vegas-2018

I also use tags to indicate transactions that occur via the same merchant system but have different payees. Specifically, things I buy on Etsy are tagged :esty: and things I buy on eBay are tagged :ebay:. Without these tags I would have no way to list all the Etsy purchases I have made, since I send the money to individual sellers and not Etsy itself.

I also use tags to indicate transactions related to keeping my apartment. Rent goes to the Expenses:Rent account. Electricity charges go to Expenses:Utilities:Electric. Gas charges go to Expenses:Utilities:Gas. My cell phone payment goes to Expenses:Utilities:Phone. The first three charges I consider apartment expenses. My cell phone is not. By tagging the first three with :apt: I can easily see the total monthly cost of keeping my apartment, without a bunch of complicated querying to exclude things like the cell phone bill.

My final use for tags is indicating transactions that I think should be deducted from my taxes. Some people do this with accounts, but I find that with the account structure that makes sense to me I often end up with a mix of deductible and non-deductible transactions within a single account. Tagging deductible transactions with :deduct: makes it easy to dump a list of all transactions that should be considered when completing yearly taxes.

Reconciling Ledger

For me, one of the primary appeals of using Ledger – or really any accounting system – is that it let’s me know how much money I have (or don’t have) right now. Other people’s records lag behind reality.

If I submit an order for a $50 widget on a website today, I consider that $50 gone, even though the vendor likely didn’t collect the money immediately. They probably placed a hold on the funds, but will not actually capture the money until they ship the order, which may take a couple days. When they do capture the funds, it may still be another day or two until that action posts to my online bank statement. Were I dependent on the bank, I could go a week thinking that I have $50 more than I actually have.

It is important to me that I log transactions without waiting for them to appear elsewhere, but at some point I do want to reconcile my records against what my bank believes. I accomplish this with auxiliary dates.

Were I to submit that order for a $50 widget today, I would create the entry using today’s date. Even if it takes me a few days to actually record the entry, I will still use the date the order was submitted.

2020-08-06 Acme, Inc
    Expenses:Misc                             $50.00
    Assets:Bank:Checking

Periodically, I will manually reconcile every transaction in my journal against my bank’s online statements. Usually this happens every other week or so. Many people will simply dump a CSV from their bank and use a script to parse and import the results into their journal. I prefer to do it manually. This rarely takes more than 30 minutes, and for me it is one of the other primary appeals of using Ledger: it forces me to be intimately aware of how money is flowing in my life, and makes it extremely easy to spot problems or errors. It is difficult to accomplish that reliably without manually touching every transaction.

So a week or two later I’ll login to the bank and make sure there is a $50 payment to Acme, Inc. If the date listed by the bank for that transaction is different from what I recorded – which for online transactions is almost always the case – I’ll prepend that date to my entry.

2020-08-10=2020-08-06 Acme, Inc
    Expenses:Misc                             $50.00
    Assets:Bank:Checking

When there are two dates, Ledger considers the date to the left of the = to be the primary date. The other date is the auxiliary date. Previously, when only one date was listed, it was the equivalent to 2020-08-06=2020-08-06. By using the bank’s date as the primary date, I know that the default output from a report like ledger register assets:bank:checking will match the bank’s statements. If I want to report on what I consider to be the real dates of the transactions, I can pass the --aux-date flag.

The journal files themselves I edit manually in vim, with a little help from vim-ledger. I have <leader>t mapped to set today’s date as the primary date of the current transaction. Because I usually only reconcile two or three times a month, today’s date is often not the date I actually want, but this shortcut allows me to immediately insert a new date in the correct format. I can then quickly change the day number to whatever it should actually be.

I consider the transaction to be cleared once the bank statement and the Ledger journal file agree on the date, payee, and dollar amount. In Ledger, transactions are marked as cleared by inserting an * between the date and the payee. In vim I map this to <leader>c. A complete transaction thus looks like this:

2020-08-10=2020-08-06 * Acme, Inc
    Expenses:Misc                             $50.00
    Assets:Bank:Checking

Ledger defaults to including all transactions in its reporting and calculations. By passing the --cleared flag it will only include transactions that have been marked as cleared. So after I think I’m done reconciling all transactions against my bank statement, I will run ledger balance assets:bank:checking --cleared and verify that the resulting balance matches what the bank shows. If I want to know how much money I actually have in that account, I will run ledger balance assets:bank:checking. The output of this will likely include transactions that the bank hasn’t caught on to yet.

If for some reason Ledger’s output of the cleared balance does not agree with the bank, my first step will be to make sure that all transactions are sorted by their primary date. If I already have the file open in vim, I have a shortcut for that. Otherwise I keep a one-liner script called ledger-sort that takes the name of a ledger file as input, sorts it, and shows me the diff.

1
2
3
#!/bin/sh

ledger -f $1 print --sort d > $1.sort && mv $1{.sort,} && git diff

Checking the diff of the sorted output before continuing is important. Sometimes I’ll make a silly mistake like typing the wrong year when entering the transaction. This sort of mistake is obvious when looking at the diff.

Once the file is sorted, I’ll run ledger register assets:bank:checking --cleared. The final column in this output is a running total of the account balance. The only difference between the output and my bank statement should be the ordering of transactions within a day, which I do not care about.

I’ll know that I last cleared my journal around a certain date, and that the account balance was accurate as of that date.1 If I was doing this on 2020-08-06, I might say to myself “I know I cleared Ledger sometime in mid-July, so let’s look at my account balance at end of day on 2020-07-15”. If the bank and Ledger both agree on the balance at the end of day on that date (regardless of the ordering of the multiple transactions that may have occurred on the day), I’ll know that the error is somewhere between 2020-07-16 and 2020-08-06. Next I’ll jump to another random day in that period – maybe 2020-07-22 – and perform the same exercise. This is repeated until I find the first day where the two systems disagree on the ending balance. Then I look at every transaction on that day and find the error. Usually it will be a simple data entry error, such as me typing $12.10 instead of $12.01. Having my receipts stored allows me to verify that I did indeed agree to pay $12.10 and that the error is mine rather than the payee’s. This whole process of finding errors rarely requires more than two minutes, and occurs maybe once every three months.

Though I don’t use CSV exports from my banks for anything, I do like to periodically download and archive them. When I generate the exports I ask for one file per month. I refer to these as “bank dumps” and store them in directory named dump. Each file is named for the account and month it represents. For example, an account named Assets:Bank:Savings will have last month’s dump stored at dump/assets-bank-savings-202007.csv. I pretty much never open these files, but I like having them archived. They allow me to see how much money a bank thought I had at any point in time, even if the account is no longer open, and provide assurance that I can always true my records if I somehow mess them up.

Notes

  1. Ledger has the ability to record assertions. I have never used this feature, but I probably should.

Receipts and Ledger

As previously mentioned, I store receipts in the same repository as my Ledger data files.

Purchasing something online automatically generates a receipt; it’s just a simple matter of saving the confirmation web page or email as a PDF and moving it into the proper directory with the proper filename. That’s a low enough bar that I cannot justify not storing them. I have a receipt for every digital transaction I have made since I began using Ledger in 2012.

I’m less strict about storing receipts for transactions that occur in meatspace. These transactions usually result in a paper receipt (which may not be initially even be offered), which then requires scanning or photographing. That is just enough effort that I don’t always want to do it. I’ll store the receipt for a meatspace transaction if the amount is greater than $100, or if I think it might be tax deductible, or if I think I might make a warranty claim. But if the transaction is a $10 sandwich for lunch, I probably won’t bother storing the receipt.

My naming scheme for the receipt files consists of the date of the transaction, the amount, and the payee. This is enough information to go from a Ledger journal entry to a receipt, or from a receipt to a Ledger journal entry. For example, consider a transaction with a journal entry like this:

2020-07-14 Acme Widgets
    Expenses:Bicycle:Parts                    $23.72
    Assets:PayPal

The receipt for that transaction will be at receipts/20200714_23.72-acme_widgets.pdf.

When I first began using Ledger and came up with this file naming scheme, I figured I would write some sort of script that would parse the components of a journal entry and generate the expected receipt filename so that I could quickly jump from one to the other. What I found is that the scheme is simple enough to make such a script unnecessary. When I look at a journal entry I intuitively know what the receipt filename is, and when I look at a receipt filename I intuitively know what the journal entry is. Writing a simple script to generate one from the other would take more energy than I have collectively spent over the past eight years solving the problem manually. So I never wrote it.

I also store invoices in the same repository, in a directory creatively named invoices. These are invoices that vendors send me – primarily things like rent statements and utility bills – not invoices that I send to people. The naming scheme is the same as what I use for receipts, except in this case the date in the filename is the date the vendor included on the invoice rather than the date I received it or paid it. I chose this because if I ever need to dispute a charge or a payment, this date is an easy way for both the vendor and I to quickly verify that we are discussing the same thing. Each invoice will of course have an associated receipt in the receipts directory after I pay it, and the date on this file will be the date on which I submitted the payment.

Every now and then I need to write someone an old-fashioned paper check. These get scanned and stored in a checks directory. The file naming scheme for these is similar to that of receipts, except that in addition to the date, amount and payee, I also include the check number. Ledger supports including the check number in the journal entry, so including it in the filename provides another way to link the two things. For example, back in 2015 I renewed my passport book and bought a passport card:

2015-11-09=2015-10-31 * (#1047) US Department of State
    ; Passport Book Renewal
    ; Passport Card Purchase
    Expenses:Travel:Documentation            $140.00
    Assets:Bank:Checking

The scan of the check from that transaction lives at checks/20151031_140.00-1047-us_department_of_state.pdf.

Tracking Cash with Ledger

In a double-entry accounting system like Ledger, money always moves from one account to another. Money cannot be magicked in or out of existence.

Previously I mentioned that I sometimes do not track cash transactions. If the transaction is less than $20 and is not related to a category of expense I care about, I may not bother with it. At first glance, this may seem like it conflicts with the inability to magically disappear money. If I take cash out of my checking account, I have to represent that withdrawal or else the balance of the account will be screwy and I’ll never be able to reconcile it against the bank’s statement. Transforming money from electrons in a bank account to Federal Reserve Notes in my pocket has no impact on its value, so one’s initial thought may to represent the cash as an asset account.

2020-07-13 ATM
    Assets:Cash                               $40.00
    Assets:Bank:Checking

This would be technically correct, and the best way to do things if every last penny of that cash was going to be tracked. But logging the withdrawal that way will not work if, over some period of time, I spend the $40 cash in a serious of small, untracked transactions. In that case, my Assets:Cash account would show that I have $40 more than reality. When asset accounts are not accurate, the world ends.

The solution is to treat cash as an expense account. The balance of an expense account is less important. It tells you how much money you’ve spent on that category of thing, but it doesn’t represent money that you hold. Effectively, this is saying that the $40 was spent when it was withdrawn at the ATM. It no longer contributes to assets or net worth.

2020-07-13 ATM
    Expenses:Cash                             $40.00
    Assets:Bank:Checking

The transaction would look similar if I asked for cash back while buying groceries.

2020-03-20 Lucky Dragon Markets
    Expenses:Food:Groceries                   $58.89
    Expenses:Cash                             $40.00
    Assets:Bank:Checking

But the trick is that money doesn’t have to move between an expense account and an asset account. Money can move between any accounts, including two expense accounts. I am strict about tracking food related expenses, so if I buy a burrito with that cash, I’ll want to log it. I can do that like this:

2020-07-18 A Taqueria
    Expenses:Food:Dining:Lunch                 $8.23
    Expenses:Cash

If part of your savings are in cash – in a safe deposit box, stuffed in your mattress, buried in the hills, whatever – you would want to treat it as an asset so that you can track the balance.

2020-08-30 ATM
    Assets:Mattress                          $100.00
    Assets:Bank:Checking

To illustrate the degree to which I do track cash: Ledger currently reports the balance of Expenses:Cash to be $3797.29. I’ve certainly dealt with significantly more cash than that since 2012. The balance of that account is simply the amount of cash I have acquired but failed to log as spent.

Organizing Ledger

Ledger is a double-entry accounting system that stores data in plain text. I began using it in 2012. Almost every dollar that has passed through my world since then is tracked by Ledger.1

Ledger is not the only plain text accounting system out there. It has inspired others, such as hledger and beancount. I began with Ledger for lack of a compelling argument in favor of the alternatives. After close to a decade of use, my only regret is that I didn’t start using earlier.

My Ledger repository is stored at ~/library/ledger. This repository contains a data directory, which includes yearly Ledger journal files such as data/2019.ldg and data/2020.ldg. Ledger files don’t necessarily need to be split at all, but I like having one file per year. In January, after I clear the last transaction from the previous year, I know the year is locked and the file never gets touched again (unless I go back in to rejigger my account structure).

The root of the directory has a .ledger file which includes all of these data files, plus a special journal file with periodic transactions that I sometimes use for budgeting. My ~/.ledgerrc file tells Ledger to use the .ledger file as the primary journal, which has the effect of including all the yearly files.

$ cat ~/.ledgerrc
--file ~/library/ledger/.ledger
--date-format=%Y-%m-%d

$ cat ~/library/ledger/.ledger
include data/periodic.ldg
include data/2012.ldg
include data/2013.ldg
include data/2014.ldg
include data/2015.ldg
include data/2016.ldg
include data/2017.ldg
include data/2018.ldg
include data/2019.ldg
include data/2020.ldg

Ledger’s include format does support globbing (ie include data/*.ldg) but the ordering of the transactions can get weird, so I prefer to be explicit.

The repository also contains receipts in the receipts directory, invoices in the invoices directory, scans of checks (remember those?) in the checks directory, and CSV dumps from banks in the dump directory.

$ tree -d ~/library/ledger
/home/pigmonkey/library/ledger
├── checks
├── data
├── dump
├── invoices
└── receipts

5 directories

The repository is managed using a mix of vanilla git and git-annex.2 It is important to me that the Ledger journal files in the data directory are stored directly in git. I want the ability to diff changes before committing them, and to be able to pull the history of those files. Every other file I want stored in git-annex. I don’t care about the history of files like PDF receipts. They never change. In fact, I want to make them read-only so I can’t accidentally change them. I want encrypted versions of them distributed to my numerous special remotes for safekeeping, and someday I may even want to drop old receipts or invoices from my local store so that they don’t take up disk space until I actually need to read them. That sounds like asking a lot, but git-annex magically solves all the problems with its largefiles configuration option.

$ cat ~/library/ledger/.gitattributes
*.ldg annex.largefiles=nothing

This tells git-annex that any file ending with *.ldg should not be treated as a “large file”, which means it should be added directly to git. Any other file should be added to git-annex and locked, making it read-only. Having this configured means that I can just blindly git annex add . or git add . within the repository and git-annex will always do the right thing.

I don’t run the git-annex assistant in this repository because I don’t want any automatic commits. Like a traditional git repository, I only commit changes to Ledger’s journal files after reviewing the diffs, and I want those commits to have meaningful messages.

Notes

  1. I do not always track miscellaneous cash transactions less than $20. If a thing costs more than that, it is worth tracking, regardless of what it is or how it was purchased. If it costs less than that, and it isn't part of a meaningful expense account, I'll probably let laziness win out. If I buy a $8 sandwich for lunch with cash, it'll get logged, because I care about tracking dining expenses. If I buy a $1 pencil erasure, I probably won't log it, because it isn't part of an account worth considering.
  2. I bet you saw that coming.