AI Solar Panel
019 AI Tools, Prompts and Data Pipelines 1,942 words · 9 min

Building a Repeatable Data Pipeline from the Octopus and n3rgy APIs

One-off CSV downloads are a trap. You export a month from your Octopus dashboard, drop it into a spreadsheet, build a lovely little battery-savings model, and then three weeks later the model is stale and you have to do the whole dance again. Worse, the second CSV overlaps the first, the column ordering has changed, and you now have two files that disagree about 2026-03-29 because one was exported before the clock change and one after.

The fix is a scheduled pipeline with four stages: fetch, validate, append, archive. Nothing clever. The cleverness is all in handling the specific ways these two APIs will break a naive script, and there are about a dozen of those.

Why both sources

Octopus gives you octopus energy api consumption data for the period you’ve been their customer, plus the tariff rates to price it. That second half matters: nobody else will hand you half-hourly Agile prices.

n3rgy reads from the DCC directly, which holds 13 months of half-hourly data regardless of who supplied you. If you switched to Octopus in May, n3rgy can still give you last November. It also acts as an independent check: two services reading the same meter should agree to within rounding, and when they don’t, you have found a real bug rather than assuming your numbers are fine. (n3rgy’s consumer terms have shifted more than once, so read their current signup page rather than trusting a blog from 2023, including this one.)

Bootstrapping: stop hand-typing your MPAN

Get an API key from the developer page in your Octopus account. It looks like sk_live_ followed by 24 characters. Then ask the API what meters you actually have instead of squinting at a bill:

curl -u "sk_live_xxxxxxxxxxxxxxxxxxxxxxxx:" \
  https://api.octopus.energy/v1/accounts/A-1A2B3C4D/

That returns your properties, each with electricity_meter_points (MPAN, plus is_export: true for the SEG meter if you have solar) and a meters array with serial numbers and valid_from / valid_to dates. If you’ve had a meter exchange, the old serial holds the old readings and the new serial holds nothing before its install date. Scripts that hardcode one serial go quietly blank for six months of history.

Note the trailing colon in -u "key:". The password is genuinely empty. Omit the colon and curl will prompt for a password on stdin, which in a cron job means the process hangs until something kills it. That failure mode has cost people entire weeks of data.

Fetching Octopus without losing 95% of your rows

Here is the consumption fetch, with the three defaults that will burn you marked inline:

import os, gzip, json, datetime as dt
from pathlib import Path
import requests

BASE = "https://api.octopus.energy/v1"
RAW = Path("raw/octopus"); RAW.mkdir(parents=True, exist_ok=True)

def fetch_consumption(mpan, serial, period_from, period_to):
    url = f"{BASE}/electricity-meter-points/{mpan}/meters/{serial}/consumption/"
    params = {
        "period_from": period_from.isoformat(),
        "period_to": period_to.isoformat(),
        "page_size": 25000,     # default is 100 = 50 hours of half-hours
        "order_by": "period",   # default is NEWEST first
    }
    rows, page = [], 0
    with requests.Session() as s:
        s.auth = (os.environ["OCTOPUS_API_KEY"], "")
        while url:
            r = s.get(url, params=params, timeout=30)
            r.raise_for_status()
            body = r.json()
            stamp = dt.datetime.now(dt.timezone.utc).strftime("%Y%m%dT%H%M%SZ")
            with gzip.open(RAW / f"{mpan}-{stamp}-p{page}.json.gz", "wt") as f:
                json.dump(body, f)
            rows += body["results"]
            url, params, page = body["next"], None, page + 1  # next carries its own query
    return rows

Three things in there deserve calling out. page_size defaults to 100, so a script that asks for a year and never paginates gets 100 rows and no error. order_by defaults to descending, so appending page after page to a CSV gives you a file that runs backwards in chunks of 25,000. And body["next"] is an absolute URL that already contains page, page_size and your date window; pass your own params alongside it and requests will append a duplicate query string. Setting params = None after the first iteration is the whole fix. Keeping the Session is what carries basic auth onto the next URL, because that URL has no credentials in it and a fresh requests.get on it returns 401.

A 200 response with "results": [] is not the same as “no data”. It usually means the readings haven’t landed yet. Octopus consumption typically lags 24 to 48 hours, and late half-hours get backfilled days afterwards. So never fetch only yesterday. Fetch a rolling window:

now = dt.datetime.now(dt.timezone.utc)
fetch_consumption(mpan, serial, now - dt.timedelta(days=10), now)

Ten days of re-fetching is 480 rows. It costs nothing and it means backfilled values correct themselves without you noticing.

Skip group_by=day entirely. It buckets on local midnight and hands you a number you can no longer re-derive. Store half-hours; aggregate in SQL.

Prices are a separate, differently-shaped job

Unit rates need no authentication at all:

GET /v1/products/AGILE-24-10-01/electricity-tariffs/E-1R-AGILE-24-10-01-C/standard-unit-rates/
    ?period_from=2026-09-01T00:00Z&period_to=2026-10-01T00:00Z&page_size=1500

The trailing -C is your grid supply point. Look it up once from /v1/industry/grid-supply-points/?postcode=SW1A%201AA, which returns group_id: "_C". Get the letter wrong and your prices will be plausible, consistently wrong, and very hard to spot: regional Agile spreads are often only 1p to 3p/kWh.

Agile publishes tomorrow’s 48 rates at roughly 16:00, sometimes closer to 17:00, occasionally later. Schedule the rates job at 16:30 with retries at 17:30 and 18:30, treating “fewer than 48 rows for tomorrow” as retry rather than failure. Rates come back newest-first too, with the same 100-row default. Each row has value_exc_vat and value_inc_vat: use inc-VAT for anything you intend to compare against a bill, and expect valid_to: null on open-ended standard tariffs, which will crash any parser that assumes a timestamp.

If you export on Outgoing Agile, that’s AGILE-OUTGOING-19-05-13 and a second fetch against your export MPAN. Same physical meter, same serial, different MPAN, and a completely separate pipeline row.

n3rgy: correct code that looks wrong

BASE = "https://consumer-api.data.n3rgy.com"
HEADERS = {"Authorization": os.environ["N3RGY_MPAN"]}  # yes: the bare MPAN

def fetch_n3rgy(resource, start, end):
    out = []
    cursor = start
    while cursor < end:
        chunk_end = min(cursor + dt.timedelta(days=80), end)   # hard 90-day limit
        r = requests.get(
            f"{BASE}/{resource}",
            headers=HEADERS,
            params={"start": cursor.strftime("%Y%m%d%H%M"),
                    "end": chunk_end.strftime("%Y%m%d%H%M"),
                    "output": "json"},
            timeout=60,
        )
        r.raise_for_status()
        out.append(r.json())
        cursor = chunk_end
    return out

No bearer token, no key, no scheme prefix. The Authorization header is your MPAN, and consent is proved separately at signup by entering the MAC address printed on your In-Home Display. That flow is not instant: the DCC grant can take a day, and the 13-month backfill then trickles in over several more. Your first week of runs will return short payloads. Check availableCacheRange in the response before you conclude a gap is real, and point early testing at sandboxapi.data.n3rgy.com so you’re not debugging your parser and your onboarding simultaneously.

Ask for 13 months in one request and you get an error, not a truncated result, so the 80-day chunking above is mandatory. Timestamps come back as "2026-09-30 00:30" with no offset and they are UTC, whereas Octopus returns "2026-09-30T01:30:00+01:00". Normalise both to UTC on the way in, or you will spend an evening wondering why your summer peak moved an hour.

Gas from a SMETS2 meter arrives in cubic metres. The conversion is volume correction times calorific value over 3.6: 1.02264 × 39.5 / 3.6 = 11.22 kWh/m³. Your bill prints the actual CV used, and it moves between about 38.5 and 40.5, which is a 5% swing on your gas cost. SMETS1 meters often report kWh already, so branch on what you have rather than converting blindly.

Validate before you append, always

This is the stage people skip, and it’s the stage that makes the dataset trustworthy enough to build on. Run these five checks per day, per series:

$ python validate.py --day 2026-09-27
rows            48 / 48 expected              ok
nulls           0                             ok
day total       14.62 kWh                     ok   (30d mean 12.90, z = 0.81)
peak half-hour  2.41 kWh = 4.82 kW            ok   (below 8.5 kW fuse headroom)
cross-source    octopus 14.62 / n3rgy 14.65   ok   (delta 0.21%)

Row count is the cheap one and it catches most things, provided you special-case the clock changes: 2026-03-29 has 46 half-hours and 2026-10-25 has 50. Hardcode 48 and your pipeline fails twice a year with a message that tells you nothing.

The cross-source check is the reason to run both APIs. Anything over about 0.5% on a daily total means a real problem, usually a timezone offset or a duplicated page. On a normal day the two will differ by a few watt-hours of rounding.

Failed days go to a quarantine table with the reason attached, not to the main table and not to /dev/null.

Append idempotently, archive immutably

DuckDB handles this in a single file with no server:

CREATE TABLE IF NOT EXISTS consumption (
  source     VARCHAR     NOT NULL,   -- 'octopus' | 'n3rgy'
  mpxn       VARCHAR     NOT NULL,
  direction  VARCHAR     NOT NULL,   -- 'import' | 'export'
  ts_utc     TIMESTAMPTZ NOT NULL,
  kwh        DOUBLE      NOT NULL,
  fetched_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (source, mpxn, direction, ts_utc)
);

INSERT INTO consumption SELECT * FROM staging
ON CONFLICT (source, mpxn, direction, ts_utc)
DO UPDATE SET kwh = excluded.kwh, fetched_at = excluded.fetched_at;

Because the key includes the source, the rolling 10-day overlap is free: re-running the same day fifty times produces the same table. That property is what turns a script into a pipeline, and it’s what lets you fix a parser bug and replay from the archive instead of losing the affected weeks.

Archiving means writing every raw response to disk, gzipped, named by fetch timestamp, and never touching it again. A day of half-hourly JSON across four series compresses to roughly 60 KB, so a year of raw archive is about 22 MB. The curated Parquet is smaller still: 70,000 rows a year lands around 250 KB with zstd. Storage is not your constraint. Being unable to reconstruct why last March looked odd is.

COPY (SELECT *, strftime(ts_utc, '%Y-%m') AS ym FROM consumption)
TO 'warehouse' (FORMAT PARQUET, PARTITION_BY ym, OVERWRITE_OR_IGNORE);

Scheduling, and the UTC trap

Two jobs, not one. Consumption at 05:15 (data has settled overnight) and rates at 16:30 with retries. On a Raspberry Pi use a systemd timer with Persistent=true so a reboot doesn’t silently skip a run. On Windows, schtasks /create /sc daily /st 05:15 /tn OctopusPull /tr "C:\pipeline\.venv\Scripts\python.exe C:\pipeline\fetch.py" is enough.

GitHub Actions cron is tempting and it has one nasty edge: it runs in UTC year-round. A cron: '30 16 * * *' job fires at 17:30 British Summer Time, an hour after Agile publishes, which looks fine all summer and then shifts under you in October. Either run two cron lines and guard on local time in code, or keep the scheduler on hardware you control.

Log every run to a one-line-per-attempt file with the HTTP status, row count and duration. When something breaks in four months, that file is the only thing that will tell you whether the API changed or your token expired.

Once 90 days are in, the queries stop being bookkeeping and start being decisions. Join consumption to Agile rates on ts_utc, sum kwh × value_inc_vat, and you have your actual cost per half-hour. Then simulate a 5 kWh battery charging in the cheapest four contiguous half-hours and discharging against your evening peak, and you get a number in pounds rather than a vendor’s brochure figure. That modelling work, including how to get an LLM to write and stress-test the simulation without hallucinating your tariff structure, is covered in AI Tools, Prompts and Data Pipelines.

The first genuinely surprising thing most people find is not the battery arbitrage. It’s a 180 W standing load at 03:00 that nobody can account for, visible only because you now have 4,320 consecutive half-hours instead of a screenshot.