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.