Getting data in

Associated directory: 02-getting-data-in

Where to begin?

The easiest place to start is with your main account. Normally it would be the account that you use to fund most of your day-to-day expenses. It is probably a checking/debit/credit card account at the bank of your choice. If you have several of them -- just pick one for now, we will deal with the rest shortly.

You would want to get CSV statements for your account covering a reasonable period of time. You can start small: get a statement for the last month or a calendar/fiscal year-to-date. You can always import older statements later (this would be covered in later chapters). For now, let's save that single statement in ./import/<institution name>/in. For example, my current account was once with Lloyds and so their statements will go into ./import/lloyds/in/{filename}.csv.

From now on I will keep using Lloyds as an example, and we will start with a simple CSV statement:

$ cat import/lloyds/in/99966633_20171223_1844.csv

Transaction Date,Transaction Type,Sort Code,Account Number,Transaction Description,Debit Amount,Credit Amount,Balance,
01/05/2017,BP,'12-34-56,99966633,AVIVA,100,,986.93
07/04/2017,BP,'12-34-56,99966633,OASIS COFFEE ,2.76,,1086.93
07/04/2017,DEB,'12-34-56,99966633,WAITROSE,5.24,,1089.69
01/04/2017,,'12-34-56,99966633,INTEREST (NET) ,,1.21,1094.93
31/03/2017,BGC,'12-34-56,99966633,HSBC,100,,1093.72
30/03/2017,BGC,'12-34-56,99966633,EMPLOYER INC,,1093.72,1193.72

Downloading files and statement periods

If you are lucky, your bank would allow you to export an arbitrary range of transactions, and you would be able to pick the convention that suits you best. For example, you might want to export at most one file per year or per quarter or per month.

However, financial institutions could be rather arbitrary in what they provide. You might be getting monthly statements, but generated on 15th of the month (so the inconveniently cross year boundary). Or maybe the maximum amount of rows in the statement will be limited (I saw this in real life).

Or you might want to just download transactions from the last date you previously grabbed files from this financial institution, however many days that might be.

This should not matter much. Save as many or as few files as you want, combine them or split them. Multiple files do not mean additional processing overhead.

Conversion scripts

Quite often these CSV files would not be directly suitable for hledger's CSV import facility. You might want or need to do some data scrubbing. Let's say that in our example I want to remove extra spaces from the description field and replace numeric account names with human-readable ones -- even though it is not necessary to import my sample file, I want to demonstrate how clean-up script could be written and ran. Lets place the clean-up script in ./import/lloyds/in2csv:

$ cat import/lloyds/in2csv

#!/bin/bash
sed -e 's/  +/ /g; s/,99966633,/,assets:Lloyds:current,/' < "$1"

Then you need to write conversion rules for hledger's CSV import command (place them in ./import/lloyds/lloyds.rules) and a script that can use these rules to print out converted journal file. Place it in ./import/lloyds/csv2journal. Usually, it is as simple as this:

$ cat import/lloyds/csv2journal

#!/bin/bash
hledger print --rules-file lloyds.rules -f "$1"

Automating the conversion

Build rules (from ./export/export.hs) will automatically invoke conversion scripts if you call them in2csv and csv2journal and indicate that you want conversion for this particular directory -- this is better than automatically try to discover and convert all the in/*.csv files in the import directory.

Let's enroll our lloyds directory into automatic conversion by editing export/export.hs:

   -- Enumerate directories with auto-generated cleaned csv files
   [ "//import/lloyds/csv/*.csv" ] |%> in2csv
 
   -- Enumerate directories with auto-generated journals
   [ "//import/lloyds/journal/*.journal" ] |%> csv2journal

Shake operate with absolute pathnames, so the path to import in the example above starts with // to indicate that import could be anywhere on your disk.

Your conversion scripts will run from the directory where they reside, so you could refer to other files (like lloyds.rules) by their relative names.

Initial Balances

Most probably your account was not opened in 2017. If your account already had some balance on 1st Jan 2017, you will need to record this in your journal. As with all other manual transactions, you will put this in the top-level yearly file, in this case, it is 2017.journal:

$ hledger -f 2017.journal print 'desc:opening'

2017-01-01 opening balances
    assets:Lloyds:current                   = £100.00
    equity:opening balances

Make sure you use equity:opening balances as this is an account that hledger equity uses to generate end-of-year equity carry-over transactions (we will cover them later).

Tying it all in

Finally, everything is in place to convert the downloaded file and to .journal.

Here is a crucial bit: instead of copying the contents of that file into your journal for the current year, lets just include it, so now your 2017.journal will look like this:

$ cat 2017.journal

;; This journal is for the current year

;; Opening balances
2017/01/01 opening balances
  assets:Lloyds:current    = £100.00
  equity:opening balances
  
include ./import/lloyds/journal/99966633_20171223_1844.journal

Note that build rules would not automatically convert all .csv files in import to .journal.

You have to write an include line for the newly-downloaded statement in your yearly journal, which will add it to the list of files that would be built/updated from now on.

Now you can run ./export.sh. This will automatically run in2csv and csv2journal scripts with the right set of arguments and regenerate all your reports. If you are keeping reports under version control you should be able to diff them against the previous version and see exactly what has changed where at a glance.

You will notice that import rules put all expenses in the single expenses:unknown account. That's fine, let's assume that you do not have time to sort them out just now, we will do this later.

Recap

I've included a sample data file and conversion scripts in 02-getting-data-in:

import
└── lloyds
    ├── in
    │   └── 99966633_20171223_1844.csv       - sample of the original downloaded file
    ├── csv
    │   └── 99966633_20171223_1844.csv       - cleaned up file, ready to be consumed by
    │                                          'hledger print'
    ├── journal
    │   └── 99966633_20171223_1844.journal   - generated journal
    ├── in2csv                               - conversion script to produce ./csv/*
    ├── csv2journal                          - conversion script to produce ./journal/*
    └── lloyds.rules                         - CSV conversion rules

The full list of changes done from the previous step could be seen in diffs/01-to-02.diff. There is a single bit of change there not covered so far: CSV conversion rules in lloyds.rules are added as a dependency for autogenerated Lloyds journals so that changes in the rules files will cause all journals to be regenerated:

extraDeps file
  | "//lloyds//*.journal" ?== file   = ["lloyds.rules"]
  | otherwise = []

Build rules automatically add in2csv and csv2journal as dependencies to the files they produce, so any changes in those scripts will cause all relevant outputs to be regenerated.

Where we are now

At this point, our little project looks like this:

$ tree

.
├── 2017.journal
├── all.journal
├── export
│   ├── 2017-all.journal
│   ├── 2017-balance-sheet.txt
│   ├── 2017-cash-flow.txt
│   ├── 2017-income-expenses.txt
│   ├── export
│   ├── export.hi
│   ├── export.hs
│   └── export.o
├── export.sh
├── import
│   └── lloyds
│       ├── csv
│       │   └── 99966633_20171223_1844.csv
│       ├── csv2journal
│       ├── in
│       │   └── 99966633_20171223_1844.csv
│       ├── in2csv
│       ├── journal
│       │   └── 99966633_20171223_1844.journal
│       └── lloyds.rules
└── README.md

6 directories, 18 files

Next steps

Now that you have converted a single statement, let's get the full history of a single account, save and convert all of them.