Yeah, I should have read that f’n manual of GNOME’s standard terminal and I would not have to get creative to select a block of text from some program’s output¹. Now, I feel embarrassed for not having pressed Ctrl while dragging the mouse all these years.

I just finished upgrading this machine from Ubuntu 14.04.5 to 16.04.1. I always have a slight uneasy feeling upgrading a machine remotely but – knock on wood – it has been a pretty straightforward process even though I had a minor boot issue which I did not had when going from 12.04 to 14.04.

Besides the nginx web server and postfix mail server, I run Gogs, Syncthing and my commenting system. To start the latter three I used to use Supervisor but again I had the uneasy feeling that it is too hackish and resource intensive on that small VPS for 24/7 operation¹. Now that Ubuntu moved from Upstart to systemd, I converted the Supervisor configurations to systemd .service files which is straightforward because both file formats are pretty similar. I added those to my Ansible playbook and here we go, everything integrates nicely and smoothly.

Before you wonder: yes, I am still alive and I just reviewed all pending comments. Sorry, for letting you wait so long but in this part of the world, it was summer.

Anyway, I will resume blogging but will shift topics slightly. For once, I started investigating Rust. I implemented netcat for fun, which took half an hour to implement and two hours to make alright, i.e. replace all those .unwrap()s with proper error handling. In fact, I had to rewrite that little program three times in order to have a somewhat sane structure. Besides, Rust, I was pretty involved with playing bass in different groups. You won’t see me writing about them but I probably will write a bit about transcriptions and theory. That’s about that.

Disclaimer: The main idea of this post might only be interesting for ten other people in the entire world but the general notion of keeping a double-entry booking journal might also appeal to you. Note though that I am not a finance expert, so your mileage may vary.

Double-entry bookkeeping with ledger

For a very long time, I have not taken particular interest in my finances and my main strategy has been to spend less than I earned. This has worked quite well, however, with regular income and a large variety of expenses such as insurances, retirement funds, daily things etc., I got overwhelmed a bit and felt losing control. To get rid of this uneasy feeling, I had to record whatever I am gaining or spending. As a layperson one has two options to record this: keep running totals in a spreadsheet – also known as single-entry bookkeeping – or use a double-entry bookkeeping software to model the flow of money between different accounts representing assets, liabilities and equities. Although the former is probably easier to grasp at the beginning, you will run into limitations such as difficult error detection soon enough. As a CLI person, I was thus soon attracted to the ledger-like family of double-entry bookkeeping systems.

The idea of ledger is simple: you record transactions between accounts in a simple journal text file and then let ledger report balances. Unlike GUI programs such as GnuCash, ledger never touches your data. This separation of concerns eases editing (e.g. Vim), revision control (e.g. Git) and greatly improves interoperability. The latter is also the main reason for the many different ledger clones of which I want to highlight hledger. Contrary to ledger that is written in C++, hledger is based on Haskell and is in my experience a lot more stable. To get more information about accounting with ledger-likes, please refer to the very extensive documentation of both ledger and hledger.

Data import with hledger

Now, here’s the actual piece covering the title of this post. I have a checking account with Deutsche Bank¹ that provides export of all transactions in CSV format. As with probably all corporate data formats, the CSV transaction format has many quirks and pecularities that need to be fixed before appending everything to a ledger journal. I used to use a homegrown Python script to fix up the CSV format and output ledger format, however hledger provides a fantastic csv command that already does the last step perfectly fine. So if you are in the same boat and want to import Deutsche Bank data into your ledger journal here is what you need to do:

tail -n +6 $1 | head -n -1 | \
iconv -f ISO8859-1 -t utf8 | \
sed -E 's/([0-9]+)\.([0-9]+),([0-9]+)/\1\2\.\3/' | \
sed -E 's/([0-9]+),([0-9]+)/\1\.\2/' | \
sed -E 's/"//g' | \
sed -E 's/([A-Z][a-z]+), ([A-Z][a-z]+)/\2 \1/' | \
sed -E 's/,/./g' | \
sed -E 's/;/,/g' | \
hledger -f - --rules-file=db.csv.rules print | \
sed -E '/^$/d' | \
sed -E 's/(.*) ; (.*)/\1\n    ; \2/' | \
sed -E 's/^(20[0-9][0-9]\/[0-9][0-9]\/[0-9][0-9]) (.*)/\1  \2/' | \
sed -E 's/ *$//'

Let’s go through this line-by-line in case you want to add your own adaptions. First we skip the funny six line header and remove the totaling sum at the bottom. Then we need to convert the encoding from ISO 8859 to UTF-8, otherwise hledger would give up reading the data. In the next lines, we convert the amount format from German 1234.00,50 EUR to 1234.50 EUR, remove quotes, replace “Doe, John” with “John Doe”, commas with dots and semicolons with commas. Then we push the cleaned data to hledger which uses a db.csv.rules file to interpret the CSV file and determine account transformations based on some heuristics. Here is the shortened db.csv.rules file that should get you running:

account1 Assets:Checking
account2 ReplaceMe

fields date,,,description,type,,,,,,,,,,out,in,
date-format %d.%m.%Y

comment %type
amount %in%out EUR

# ... patterns to determine accounts

Because I like to specify amounts as “1234 EUR” rather than “EUR1234”, I have to construct them manually with the amount statement. After letting hledger do its job the shell script takes over for final post-processing, including removal of empty lines between transactions², date formatting and trailing white space removal. Quite a bit of upfront work, but tremendously helpful for day-to-day operations.