Managing my finances

I’m pretty bad at managing my finances, and remembering to save some cash for when I need to pay taxes isn’t exactly high on my priority list. I use YNAB now to plan out how much I can actually spend each month. The good thing about YNAB is that it can hook into most banks and automatically track your spending. The bad thing about my bank is that I think they built their systems in the early 2000s and decided “Well that’s done” and then never went back and updated it.

The Problem

So I have 2 options do get download my statements. I can download an excel sheet with them on, or a CSV. YNAB quite likes CSV files so this seems like an easy choice right? Wrong. The CSV format my bank uses is generated using what I believe is an old format, and I can’t import it directly to YNAB as the columns don’t match up with what YNAB wants (which is to be expected). I’ve attempted to use Dart and Python to import and parse this CSV file, but there’s a bunch of hidden characters in there messing up the formatting and breaking any attempt at a parse.

This leaves me with the excel sheet which I can download and then save as a UTF-8 csv file.

The Data

Now I have a CSV file that I can read in an app and manipulate. Originally I built a small python app to do this, but I’m a Flutter dev and I wanted to have an app I can stick on my start menu and run in a couple of clicks to convert from my bank’s Excel/CSV to a YNAB compatible CSV, instead of using command line.

Let’s see what the CSV file looks like before conversion, with any details obfuscated:

millenniumbcp.pt;;;;;
Balances and Transactions;;;;;
STATEMENT FOR CURRENT ACCOUNT NO. ;;###########;;;
Network:;;Millennium BCP;;;
Base currency:;;EUR;;;
Book balance:;;#,###.##;;;
Available balance:;;#,###.##;;;
Authorized balance:;;#,###.##;;;
Value balance:;;#,###.##;;;
Date from:;;22/06/2022;;;
Date till:;;22/07/2022;;;
;;;;;
Transaction record date ;Value Date;Description;Amount;Type;Balance
22/07/2022;22/07/2022;WORK PAYMENT;#,###.##;Credit;#,###.##
22/07/2022;22/07/2022;COMPRA 2795 thelotter +############ML;-#.##;Debit;#,###.##
22/07/2022;22/07/2022;COMPRA 2795 UBER EATS HELP.UBER.COMNL;-##.##;Debit;#,###.##
22/07/2022;22/07/2022;COMPRA 2795 UBER EATS HELP.UBER.COMNL;-#.##;Debit;#,###.##
22/07/2022;22/07/2022;COMPRA 2795 UBER EATS HELP.UBER.COMNL;-##.##;Debit;#,###.##

So this isn’t really a good CSV file, the headers are on line 13 and the first 12 lines are info about the account itself, so we’ll need to clean this up.

Let’s see what YNAB wants the file to look like:

Date,Payee,Memo,Amount
06/22/21,Payee 1,Memo,-100.00
06/22/21,Payee 2,Memo,500.00

YNAB wants 4 columns, which seems simple enough. I just need to strip out the first 13 lines, and take the first, second and fourth columns of my bank’s CSV and slot them in.

The Solution

So now I know what needs to happen let’s make it work. I’ve built a small 1-page flutter app for desktop with a button to select a file and another button to open the resulting csv’s location.

Select and read the file

First we need the user to select the file, simple enough. Show a FilePicker using file_picker package, and display an error if it’s not a csv.

// Select File
FilePickerResult? result = await FilePicker.platform.pickFiles();


// Check file is not null and is a csv
if (result != null) {
    final inputPath = result.files.single.path!;
    if (inputPath.split('.').last != 'csv') {
        // Show error and bail out
        ...
    }

    // Read file
    File file = File(result.files.single.path!);
    final lines = await file.readAsLines();
    ...
}

Sanitising the data

Next we need to remove any empty lines, the account information at the top of the data, and the bank information at the bottom of the data.

// Sanitised file
final List<String> sanitisedLines =
    lines.where((line) => line.trim().isNotEmpty).toList();

// First 13 lines are account info and headers and not formatted for YNAB CSV
sanitisedLines.removeRange(0, 13); 

// Last line is bank info
sanitisedLines.removeLast();

Generating the new data

Now we have our data sanitised, we’re ready to pull out the relevant parts of the data which YNAB wants. This is the date of the payment, who the payment was from/to, a memo about the payment which we will leave blank, and the amount that the payment was for. We’ll store this in a StringBuffer so we can iterate and append and later write directly out to a file.

var buffer = StringBuffer();
for (var line in sanitisedLines) {
    var cols = line.split(';');
    buffer.writeln([cols[0], cols[2], '', cols[3]].join(';'));
}

Saving the new csv file

Now we simply create a file and save the contents of the StringBuffer to it, allowing us to upload our correctly formatted CSV file to YNAB.

final directory = (await getApplicationDocumentsDirectory()).path;
final outputPath = '$directory/output.csv';
final outputFile = File(outputPath);
if (!await outputFile.exists()) {
    await outputFile.create();
}

var savedFile = await outputFile.writeAsString(buffer.toString());

The Point Of This

The main thing I’m getting at here is that having a skill that allows me to build a quick tool to format data for me instead of doing this by hand is probably a skill I think most people should learn. Writing this in any language isn’t hard, and most people could learn how to do some basic programming to automate other tasks in their life pretty quick. Maintaining a budget? Easy. Collating some data for work? Same concept. Repetitive tasks? Automate them!

I’d recommend anyone to look into how to write scripts in languages like python, ruby, dart, javascript, etc, just to be able to throw something together quick that in future will save you a lot of time and headaches and remove human error from the process.

The Repo

The full code for this small app is available on my GitHub.