BrightBot.co.uk
GitHubLinkedIn ProfileBuy Me a CoffeeRSS Feed
Personal finance aggregator: Hargreaves Lansdown, with no API - Part 14

Personal finance aggregator: Hargreaves Lansdown, with no API - Part 14

Building Finance System

~9 min read

The project's very first description listed a stretch goal: Hargreaves Lansdown, for the investment side of the picture. Post 8 assumed that meant a CSV import. It didn't turn out that way. This post covers the commits that made HL a real provider, and mostly it's about how an integration with no API, no OAuth and a real lockout risk changes the design you'd copy from TrueLayer.

What's different about HL

TrueLayer gave me an OAuth flow, a refresh token, and an API. Hargreaves Lansdown offers retail customers none of that. The provider module's docstring states the situation without ceremony: HL "has no public API for retail customers, so hl_client logs into the web portal directly." hl_client is a separate library (atownsend247/hl-api-client-python, per the docstring) that scrapes the portal; this app is its consumer.

That one fact reshapes everything:

  • There's no refresh token. Every sync submits the real username, password, date of birth and security number. (HL's login also locks the account after too many failed attempts, which the library documents.)
  • So syncing must be cautious. HL can't just ride along on the nightly TrueLayer timer. It got its own, which started out weekly - OnCalendar=Sun *-*-* 04:00:00 - on the reasoning that portfolio values "don't move on the timescale spending does", and that every run sends real credentials. It later went daily (OnCalendar=*-*-* 04:00:00), an hour after the TrueLayer run. The unit's comment still draws the line: daily is as often as this should go, and if the stored login stops working the fix is to correct it on the Accounts page promptly "rather than letting the timer keep retrying."
  • There's no browser redirect for linking, so "link" can only mean "store what the user typed."

First version: credentials in environment variables

The enable HL API client commit was the straightforward implementation. The credentials came from HL_USERNAME, HL_PASSWORD, HL_BIRTHDAY and HL_SECURITY_NUMBER in .env, consistent with the rule in CLAUDE.md that credentials come from environment variables only. fetch() logs in, lists accounts, then lists each account's holdings, and writes JSON for the storage layer to load - the same two-step shape as TrueLayer.

It added a holdings table (units, price, value, cost, gain in pounds and percent) and, importantly, changed how the shared tables get refreshed. Accounts, connections and holdings are all full-replace caches, and a blanket DELETE FROM accounts would let one provider's sync wipe the other's rows. So each save is scoped to the banks in its own batch:

banks = {a["bank"] for a in accounts}
if banks:
    placeholders = ", ".join("?" * len(banks))
    conn.execute(f"DELETE FROM accounts WHERE bank IN ({placeholders})", tuple(banks))

Each provider only ever passes its own accounts, so a TrueLayer sync can't touch HL's rows. It's an easy invariant to break by accident, and it only exists because a second provider arrived.

One more habit carried over from post 7: if one account's holdings page fails to parse, fetch() logs it and moves on, so the account's own valuation - already recorded - and every other account survive.

Second version: credentials move into the app

The environment-variable approach had a problem that only showed once there was more than one user (post 10): env vars are global, so they can only hold one person's HL login. The migrate HL to user based commit moves the credentials to a per-user file written through the Accounts page.

This is the one place the project deliberately breaks its own rule, and the rule changed in writing in the same commit. CLAUDE.md now says credentials come from environment variables only, "except Hargreaves Lansdown's: it has no OAuth token to exchange them for, so the real username/password/DOB/ security-number are entered once via the Accounts page and stored per-user in storage/users/{user_id}/secrets/hl_credentials.json" - and then adds that this is "not a drift from the rule above, a deliberate exception to it". I like that the exception is written down next to the rule rather than quietly practised. (It also adds that file to the list of things never to read.)

The file is treated as more sensitive than TrueLayer's token file, and the code says why:

os.chmod(path, 0o600)

The docstring: this file "holds a real password and full security number, materially more sensitive than truelayer.py's revocable refresh token (which tokens.json doesn't bother chmod-ing), so it's worth the extra hardening here." A refresh token can be revoked at the provider; a password can't be un-leaked.

The new link endpoint contains a decision I'd defend: it doesn't try to log in. It just stores what was typed. The docstring explains: "a typo'd password would otherwise cost an extra attempt against HL's lockout counter beyond the one 'Sync now' will trigger anyway." Validating eagerly feels like the responsible thing and is actually the riskier one, because the validation is itself a login attempt. Unlinking deletes the credentials file and the cached accounts and holdings, and, unlike TrueLayer's unlink, takes no "also delete data" option - HL has no transaction history here, only a current snapshot, which goes stale the moment the login is removed either way.

Making the scheduler multi-user

With credentials per user, the HL unit had the same problem the nightly one had. The first version of the service unit needed a __HL_USER_ID__ placeholder substituted by hand, and its comment admitted it wasn't wired into remote-setup.sh yet. A later commit replaced that with deploy/fetch-hl-all-users.sh, mirroring fetch-all-users.sh with one important difference, spelled out in the script's header: unlike TrueLayer, where every provisioned user is expected to have a bank linked, "most provisioned users won't have HL linked, and fetch() would just raise MissingCredentials for them." So the script skips any user without a hl_credentials.json before even trying - which also means it never submits a login for someone who hasn't asked for one.

Small things that got done along the way

Another gives HL a logo, hardcoded as provider_logo_url, since a scraped portal has no connection metadata to take one from. And two dependency bumps are worth a line because of what they were for: both update hl-client so failures are diagnosable. The first surfaces the HTTP status and landed path in the "Unable to submit Stage" error; the second surfaces HL's own rejection reason on a failed login. When your only integration is a scraper of someone else's website, the quality of its error messages is the quality of your debugging.

Each holding becomes an account

Holdings started life as a table of their own, hanging off each HL account. That recorded what I owned, but the balance-history charts from post 13 only knew about accounts, so there was no history per holding. A later change gave every holding a row in the accounts table as well, so it gets its own balance, its own history and its own chart for free, exactly like one of a TrueLayer bank's accounts.

The part worth getting right is the arithmetic. HL reports an account's total_value, which already includes every holding, so adding a row per holding would count the same money twice. fetch() therefore subtracts the holdings from the parent:

parent["balance"] = round(parent["balance"] - sum(float(stock.value) for stock in stocks), 2)

The parent row then holds only what's left - cash and anything pending - and the rows still sum to HL's total. The holding rows have an account_id of {account_id}:{stock.id}, a name like Some Fund (ISA), and an is_holding flag. That flag exists because "how many accounts did we sync?" now has two answers, and both fetch() and the storage loader's counts skip holding rows, so the number reported back to the Accounts page is still the number of accounts HL actually has.

The pattern

If I were adding a third provider, the HL work is the better template than TrueLayer, because it's the less friendly case: write down how this provider authenticates and what that means for sync frequency; store its credentials with the sensitivity they deserve; don't spend a rate-limited login on validation; scope every shared-table refresh to the provider doing it; and skip, rather than fail, users who've never opted in.

Previous: ← Periods, charts, and a phone-sized UI · Next: Budgets →