We’ve recently started using You Need A Budget (YNAB) to plan our finances. Unfortunately, like most finance apps it’s focussed squarely on the north american market: its automatic transaction importing only works for some US and Canadian banks – and we live and bank in Spain…
YNAB does support a manual process, where you upload QFX or OFX files – or CSVs (in their required format). However, our bank (Sabadell) supports neither QFX nor OFX, and their CSV format is markedly different from that which YNAB expects in the upload. We were stuck with the prospect of importing every single transaction by hand, or manually downloading a CSV from Sabadell and munging it into a shape that YNAB can understand.
Surely we can do better?
So became ynabadell – a tool to automatically import transactions from Sabadell into your YNAB ledger.
Technically speaking, ynabadell resembles an ETL system in many ways:
If you just want to use the tool, feel free to look at the instructions. If you’re looking for more information on how it works – or how you might tackle creating a similar tool for other banks – keep reading:
This component grabs transactions from Sabadell’s online banking, in their CSV format. I use Puppeteer as a headless browser to sign into Sabadell and grab the transaction data.
The main things which stood out to me while creating this component is that Sabadell’s website is extremely painful to navigate in a headless browser. There is very little semantic markup, lots of jumbled-up table layouts sat alongside – or inside – div-based layouts, and mixed natural languages in the structure (e.g. Spanish and English class- and input-names).
As such, I’m expecting to need to regularly tweak and fix the selector rules – they are perilously fragile. A few other caveats:
The end result of the extractor is a list of
SabadellTransaction value objects, which will be passed into…
Probably the simplest component: all the transformer does is create a
YNABTransaction value object for each
SabadellTransaction it is given.
You can take a look at the code if you’d like, but there’s not too much to it.
YNABTransaction that the transformer emits is passed into…
In the extraction section above, I noted that duplicate transactions weren’t a concern. This is because the YNAB API conveniently allows for idempotency through an
import_id which you can include alongside every transaction. If you upload two transactions with the same
import_id, the latter one is ignored as a duplicate – even if the data are different.
YNAB uses this approach to generate
… this field is a unique (by account) import identifier. If this transaction was imported through File Based Import or Direct Import and not through the API, the import_id will have the format: ‘YNAB:[milliunit_amount]:[iso_date]:[occurrence]‘. For example, a transaction dated 2015-12-30 in the amount of -$294.23 USD would have an import_id of ‘YNAB:-294230:2015-12-30:1’. If a second transaction on the same account was imported and had the same date and same amount, its import_id would be ‘YNAB:-294230:2015-12-30:2’.
I chose to follow this same approach for
import_id generation in ynabadell so that you can make a smooth transition from manual CSV uploads – the two systems will work properly in conjunction with one another.
Apart from the
import_id, really the only interesting thing which isn’t supplied in the
YNABTransaction is the access token – it’s easy to get your own YNAB personal access token by following the README instructions.
Currently, ynabadell is designed to be run as a
launchctl service on OS X.
A plist file is supplied which will need to be customised to your needs. It just runs the top-level script which is responsible for reading in configuration, and then stringing together the extractor, transformer, and loader.