Hacker News Reader

Comment by benjaminjackman | Hacker News Reader

Does anyone have any recommendations on ways to automatically download data from bank accounts / brokerages etc onto disk for easy / scripted importing into GnuCash / Spreadsheets etc (instead of having to click around on the websites and download csvs or whatever daily?).

I have seen some products aimed at developers that will bucket various banks into APIs but not anything aimed at individuals. I have experimented with automating it with puppeteer, but surely there has to be a better way?

Maybe it's a matter of finding and using a modern set of financial insitutions with decent APIs. Does that even exist (in the US)?

View on HN

Plaid's Bank API [0]

Its free for 25 accounts and connects to most banks [1]. The only bank I've found it doesn't work for is Barclays. I have most of my accounts (credit cardd and banks) hooked up to pull transaction data.

It returns JSON including category of spend. I don't know GNUCash, but you could format that any which way with a little scripting.

0: https://plaid.com 1: https://pastebin.com/WBKLMb1H

I can second Plaid. I use it with a read-only account on my bank, and it works great. I have a daily cron that grabs the data and pulls it into a database on my home network.

It's free (since I only have one use -- me), which is great.

what do you use to manage the transaction data? do you go through to categorize them? have a frontend for viewing?

I pull the transactions into SQLite, and have a custom web frontend I use to categorize them. Then for reporting, I export the whole database into a Ledger (https://www.ledger-cli.org/) file. Works great, although it's a lot of moving pieces.

I was just thinking about this this week. I've been trying to keep better track of where I spend money for budgeting purposes, so I've been downloading transactions and putting them into a spreadsheet. Currently I export the transactions from Mint, but I'd really like to stop using it if at all possible (for security, privacy, and UX reasons).

I've thought about creating an open source project with scrapers for the major banking institutions so you can export your transaction data into a common format.

Does anything like this exist?

https://github.com/captin411/ofxclient is a good place to start, if your bank supports it. Some banks (like Bank of America) charge you a monthly fee for OFX downloads. In those cases, you can write up a selenium script or something similar to login for you and scrape transactions once a day/week/whatever. That's obviously a fragile solution, however.

Quicken has the Web Connect bank connection option. I have a theory that this is similar to a selenium script where you can download a QFX file, but don't need 2FA to do it. Unfortunately, I haven't found any documentation on how this actually works, though.

I also use the referenced ofxclient which is analogous to Quicken's Direct Connect. BofA is inconsistent with applying fees for using Direct Connect.

If anyone can share, I am very interested!

At my bank, the best way is to run a cronjob to download a CSV formatted report. Probably needs phantomjs or similar. I think this is the general situation. Banks don't have (open) APIs and I don't think they have any intention of changing that. There's a few startups trying to but none of them are based in the US.

Wait, without an API, how do you automate downloading the CSVs? Specifically, how do you handle authentication?

He stated phantomjs, basically GUI Automation.

But if you run it via a cron job, don't you have to store your login, 2fa etc on the computer you run it on? That's what I was wondering about.

You would have to store login data on your PC somewhere. 2FA wouldn't be possible unless you had some kind of interaction with the script at runtime.

Check out Tiller! https://www.tillerhq.com/

I tried going down the route of dealing with OFX in GNUCash, and it's just a huge never-ending hassle. Tiller dumps the raw data from Yodlee (the backend that does the gnarly interfacing and cleaning for most PFM sites, besides Mint) into Google Sheets. From there you can use Apps Script or the Sheets API to push the data wherever you want. Sure, it's $5/mo, but well worth it for not ever dealing with bank websites or APIs or OFX formats again.

Looks cool, but wow, you're really handing all your banking data to Google? Does Tiller support raw CSV downloads?

I'm using Plaid's API with a read-only user on my bank account to download the data locally and enter it into SQLite. Then when I want to run reporting on it, I generate a Ledger file. Having it in SQLite lets me run a web interface over it and change the category for each item (Amazon purchases, for instance, which are seemingly impossible to categorize automatically).

OFX is supported by GNUCash, but there's a long long tail of UI issues. GNUCash uses aqbanking to speak OFX to banks, but you still need to know a lot of data your bank doesn't publish in any obvious place.

And the aqbanking setup UI is completely separate from GNUCash, and really, really clunky. The good news is once it's set up, the normal import window UI is used. The bad news is there's no zero click process you can background. Some of that is because you may have conflicts between transactions you create automatically and the data source.

I’ve slowly built exactly this as one of my side projects over the last few years. An OSX menu bar app that uses a combination of ofxclient and reverse engineered APIs to download balance/transaction info directly to an Excel spreadsheet. This way I get to leverage Excel as my UI for categorization, analysis, graphing, etc.

I’ve been debating for a while what to do with the app next but didn’t think many people would be interested. Is this the the kind of thing you’re looking for?

It may not be philosophically compatible with GNUCash, but adding all of your accounts to Mint would bring it all into one site that you can then script against. Mint used to use Yodlee’s APIs to access data from financial institutions, do you could try getting developer access to that if you wanted something more direct.

Reply on news.ycombinator.com