TITLE: Converting a bank .csv statement to ledger
DATE: 2018-08-12
AUTHOR: John L. Godlee
====================================================================
I use ledger to keep track of my expenses and for a long time I had
to manually copy in all my expenses manually from the csv output
that my bank provides of my transactions. Then recently I read
about ledger convert which can take a csv file and convert it to
the ledger journal format.
[ledger]:
https://www.ledger-cli.org/
y bank's csv format looks like this:
Date, Type, Description, Value, Balance, Account Name, Account
Number
10/08/2018,POS,"'1062 09AUG18 C , TESCO STORES
",-11.68,6355.61,"'STUDENT ACCOUNT","'260204-20408582",
10/08/2018,POS,"'1062 09AUG18 C , SAINSBURYS S/MKTS ,
GB",-3.80,2367.29,"'STUDENT ACCOUNT","'260204-20408582",
10/08/2018,POS,"'1062 09AUG18 C , WINES , NTMG
GB",-17.00,2371.09,"'STUDENT ACCOUNT","'260204-20408582",
10/08/2018,POS,"'1062 09AUG18 , MOBILE APP
",-30.00,2388.09,"'STUDENT ACCOUNT","'260204-20408582",
Notice that there are blank lines above and below the header row,
so that is one of the first things to deal with.
I can fix the file with a few sed commands, noting that I use the
macOS version of sed rather than gnused:
sed -i "" '/^[[:space:]]*$/d' $1
sed -i "" '/\d{4}\s\d{2}\D{3}\d{2}(\sC\s)?\s?,\s/g' $1
sed -i "" '1s/.*/date,,payee,amount,,,/' $1
The first command removed any lines that are blank or contain
spaces, which gets rid of the empty header lines. The second line
removes some useless filler text in the notes column of the csv
file, so for instance, ,"'1062 09AUG18 C , TESCO STORES ", gets
contracted to ,"TESCO STORES ",. Finally, the header row is
replaced with headers which ledger convert recognises.
ledger convert needs some inputs:
- --input-date-format "%d/%m/%Y" tells ledger convert the format
of the date column in the csv file.
- --account assets:bank:student_acc denotes the bank account the
csv file is for.
- --rich-data extracts unnamed columns from the csv and adds them
as notes to each ledger entry.
- -f ~/.ledger.journal denotes the path to the ledger journal
file to use for reference.
- --invert inverts the sign of the transactions, which I have to
do for this style of csv to stop ledger thinking that expenses are
incomes and vice versa.
Then I can wrap all of this into a neat shell script which takes
inputs of the input csv and output ledger journal:
#!/bin/bash
touch $2
sed -i "" '/^[[:space:]]*$/d' $1
sed -i "" '/\d{4}\s\d{2}\D{3}\d{2}(\sC\s)?\s?,\s/g' $1
sed -i "" '1s/.*/date,,payee,amount,,,/' $1
ledger convert $1 --input-date-format "%d/%m/%Y" --account
assets:bank:student_acc --invert --rich-data -f ~/.ledger.journal >
$2
For now, I'll copy in the compiled journal entries manually, I
don't want to accidentally copy over my existing journal file by
writing directly to it.
It's not a perfect system, I still have to manually fill in the
type of expense in the ledger journal, but I really don't see any
way around that as my expenses don't come from a finite list of
sources, so categorising all of them would be impossible.
Update 2019_08_26
I had some problems with CSV files from one of my accounts. It
turns out they were being stupid with the CSV formatting and had
open double quotes which weren't closed in a description field,
meaning that any commas in that field were interpreted as column
delimiters. I took it as an excuse to improve the ledger convert
bash scripts I'd set up previously. I wanted to clean them up and
use some shell scripting techniques I've learned since last year
when I wrote the original script.
#!/bin/bash
# Make temp. file
temp=$(mktemp)
# Format file depending on account
if [ "$2" = "assets:bank:student" ] || [ "$2" =
"assets:bank:isa" ] || [ "$2" = "assets:bank:res" ]
then
# Prepare file
## Remove blank lines | Replace column headers | Remove
single quotes > send to temp
sed '/^[[:space:]]*$/d' $1 | sed
'1s/.*/date,,payee,amount,,,/' | sed "s/'//" > $temp
date="%d/%m/%Y"
elif [ "$2" = "assets:bank:monzo" ]
then
echo "TEST"
# Prepare file
## Replace column headers | Change tag | Remove time from
date > send to temp
sed '1s/.*/transid,date,amount,,,,payee,,,,,/' $1 | sed
's/general/misc/g' | sed 's/T[0-9][0-9]:[0-9][0-9]:[0-9][0-9]Z//g'
> $temp
date="%Y-%m-%d"
else
echo "Choose an account"
echo " assets:bank:student"
echo " assets:bank:isa"
echo " assets:bank:res"
echo " assets:bank:monzo"
fi
# Run ledger
ledger convert $temp --input-date-format ${date} --account $2
--invert --rich-data --auto-match -f ~/.ledger.journal
# Remove temp
rm ${temp}
I combined the scripts I had for my two banks into one, hinging on
an if-else statement acting on the account name given as an
argument to the script. It first cleans up the csv files and saves
them to a $temp file, then runs ledger convert with certain options
and sends output to STDOUT.
