Format ING direct exported XLS to YNAB compliant CSV

format_xls_for_YNAB.sh

#!/bin/bash

if [ $# -eq 0 ]
  then
    echo "Usage: $0 filename.xls (where the xls is exported from ING direct website)"
    exit 1;
fi

result=$(echo $1 | cut -d . -f1);
result+=".csv";

export HOME=/tmp && libreoffice --headless --convert-to csv $1 --outdir . # convert to csv
sed -i '/,,,,/q' $result # remove everything after ,,,,
sed -i '1d' $result # remove first line
sed -i '$d' $result # remove last line
sed -i 's/\x80//g' $result # remove € symbol
awk 'BEGIN    { FS="\""; } { gsub(",", ".", $2); gsub("-", "", $2); print }' $result > temp.csv # replace , with . in amounts
mv temp.csv $result
# format final csv as needed by YNAB
awk 'BEGIN { FS=","; print "Date,Payee,Memo,Outflow,Inflow" } { if( $5 ~ "-") {print $2, ",", $4, ",", $3, ",", $5} else {print $2, ",", $4, ",", $3, ",", "," $5} }' $result > temp.csv
mv temp.csv $result