AI Tools, Prompts and Data Pipelines

Most people who try to use an LLM on their own energy data start in the wrong place. They paste a screenshot of the Octopus app into a chat window, ask “should I get a battery?”, and get back 600 words of confident nonsense with a made-up payback figure. The model isn’t the problem. The problem is that there was no data pipeline, so there was nothing for the model to reason over except vibes.

An ai energy data analysis tool worth the name is three layers stacked: a data layer that pulls half-hourly readings out of your meter and inverter, a compute layer that does the arithmetic deterministically, and a language layer that writes the SQL, spots the anomaly, and explains the result. Get the first two right and the third becomes genuinely useful. Skip them and you have an expensive random number generator.

This page covers how to build all three, with the tool names, the API endpoints, the gotchas that will eat a weekend, and worked numbers from a real 4.2 kWp system in southern England.

The three layers, and why the order matters

Layer one is raw data. Half-hourly import and export from your smart meter, generation and battery state-of-charge from your inverter, tariff prices from your supplier’s API, and weather from somewhere reputable. All of it timestamped, all of it in kWh or kW with the distinction written down.

Layer two is deterministic compute. DuckDB, pandas, or even a well-built spreadsheet. This is where sums happen. An LLM should never be the thing that multiplies 5.2 by 0.07 by 365, because it will occasionally get it wrong and it will always sound equally sure either way.

Layer three is the model. Claude, ChatGPT, or a local Llama if you like. Its job is to write the query, interpret the shape of the result, propose the next test, and flag the thing you didn’t think to look for. It is a very good analyst with no calculator and a slightly unreliable memory of UK tariff rules.

People invert this stack constantly. They ask the model to be the calculator and then use their own judgement to write the SQL, which is exactly backwards.

Five UK data sources, and what each one actually gives you

The Octopus Energy REST API is the best documented consumer energy API in the UK, and it is free if you’re a customer. You need your MPAN, your meter serial, and an API key from the dashboard.

GET https://api.octopus.energy/v1/electricity-meter-points/{mpan}/meters/{serial}/consumption/
    ?period_from=2025-10-01T00:00Z&page_size=25000&order_by=period

That returns half-hourly kWh. A full year is 17,520 rows, which fits comfortably in one page. Two traps: page_size caps at 25,000, and the default ordering is newest-first, so if you forget order_by=period your cumulative sums come out reversed. Timestamps carry a real offset, so BST rows arrive as +01:00 and GMT rows as Z. Sort those as strings and October will land before September.

n3rgy gives you DCC data regardless of supplier. You consent with your MPAN, wait a day or so, then pull half-hourly consumption. The DCC retains roughly 13 months of half-hourly history, so this is your backfill route if you’ve only just started caring. No supplier lock-in, which matters when you switch.

A Hildebrand Glow CAD (about £70) clips onto the smart meter’s Zigbee HAN and publishes to MQTT every 10 seconds. Half-hourly is fine for billing analysis, useless for finding out what your dishwasher’s heating element actually draws. If you’re an Octopus customer, the Home Mini is the free equivalent and has a local HTTP endpoint that Home Assistant polls directly.

Your inverter’s API is the generation side. GivEnergy’s cloud API is a straightforward bearer-token REST affair with per-device half-hourly data. SolarEdge’s monitoring API gives 15-minute granularity but rate-limits you to 300 requests per day per site, so batch your date ranges. Solis and Sunsynk go through Solarman, which uses HMAC request signing and will waste an hour of your life before the first 200 response. Enphase owners can hit the Envoy locally at /ivp/meters/readings and skip the cloud entirely.

Weather is the one people cheap out on and regret. Open-Meteo is free, has no key, and serves historical hourly GHI and DNI back to 1940 via its ERA5 archive. Solcast’s hobbyist tier gives 10 API calls a day of site-specific PV forecast, which is enough for a day-ahead battery plan.

Building the pipeline: 40 lines, not 400

The temptation is to build a proper ETL framework. Don’t. A single Python script on a cron job, writing Parquet files to a folder, will serve you for years.

import duckdb, requests, pandas as pd

con = duckdb.connect("energy.duckdb")

# One table per source, one row per half hour, always UTC internally
con.execute("""
CREATE TABLE IF NOT EXISTS hh (
  ts TIMESTAMPTZ,      -- interval start, UTC
  import_kwh DOUBLE,
  export_kwh DOUBLE,
  gen_kwh DOUBLE,
  soc_pct DOUBLE,
  price_p DOUBLE       -- import unit rate inc. VAT
);
""")

Store everything in UTC and convert to Europe/London only at the point of display. This single decision removes an entire category of bug. Your peak-window analysis needs local clock time (16:00 to 19:00 is a human construct), but your arithmetic needs monotonic UTC.

Key the table on interval start, not interval end. Suppliers disagree on this and the resulting 30-minute offset is invisible in a daily total while quietly destroying any peak/off-peak split.

Once the table exists, the analysis is SQL:

SELECT strftime(ts AT TIME ZONE 'Europe/London', '%H:%M') AS hh,
       AVG(import_kwh) * 2 AS avg_kw,
       AVG(price_p)          AS avg_price
FROM hh
WHERE ts >= '2026-01-01'
GROUP BY 1 ORDER BY 1;

This is the query you’ll run twenty variations of, and it’s exactly the kind of thing to hand to a model rather than typing by hand.

Worked example: does a 5.2 kWh battery actually pay?

Here is the arithmetic that LLMs reliably get wrong, done properly. The house: 3,400 kWh annual import, Intelligent Octopus Go at 7p/kWh between 23:30 and 05:30 and 25.4p the rest of the day, standing charge 55p/day.

The naive model answer, which you will get almost every time if you ask casually: 5.2 kWh × 18.4p spread × 365 days = £349/year.

Now the real version. Round-trip efficiency on an AC-coupled retrofit is 86% to 88%, so delivering 5.2 kWh to the house costs 5.2 / 0.87 = 5.98 kWh of import. That’s 41.8p in, displacing 5.2 × 25.4p = 132.1p out. Net 90.3p on a day where you fully cycle it.

You don’t fully cycle it 365 days a year. From April to September the solar covers most daytime load, so the battery is charging from the roof rather than from the cheap window, and the saving there is against export value (15p on Octopus Outgoing Fixed) not against the 25.4p day rate. Pull the actual half-hourly import from the pipeline and count days where post-05:30 consumption exceeded 5.2 kWh: on this house it was 247.

Winter arbitrage (247 days × 90.3p)        £223.04
Summer self-consumption uplift              £61.40
Total annual benefit                       £284.44
Retrofit cost (5.2 kWh, AC-coupled)      £3,200.00
Simple payback                            11.2 years
Warranty                        10 yr / 6,000 cycles

The honest conclusion is that the battery pays back roughly when it goes out of warranty. That is a materially different decision from the £349 figure, and the difference came entirely from having 17,520 real rows instead of an average.

Note what the model was good for here. It wrote the SQL that counted qualifying days, it asked whether the inverter was AC or DC coupled (which changes efficiency by two points), and it caught that Outgoing Fixed at 15p means summer surplus has a floor value rather than being free. It was bad at the multiplication and it did not volunteer the efficiency loss until asked.

Modelling generation before you buy anything

If the array doesn’t exist yet you have no data, so you model. Three sources, and you should run all three because the spread tells you something.

PVGIS via its web tool or API. For a 4.2 kWp array at 35° tilt, due south, near Reading, PVGIS-SARAH3 with the default 14% system loss returns about 4,030 kWh/year.

pvlib-python if you want hourly output rather than a single number. pvlib.iotools.get_pvgis_tmy() fetches a typical meteorological year, ModelChain runs it through your module and inverter parameters. This is the only route that gives you the hourly generation profile you need to size a battery properly, because annual totals tell you nothing about whether your surplus arrives in 2 kW dribbles or 3.8 kW spikes.

The installer’s MCS figure. For the same array, expect something like 3,720 kWh. MCS uses the Kk lookup tables plus a shading factor, and it’s deliberately conservative.

The measured first-year output on this array was 3,850 kWh. PVGIS overshot by 4.7%, MCS undershot by 3.4%. That’s the range to plan within, and if a quote claims a number outside it, ask which irradiance dataset they used.

One correction worth applying: PVGIS assumes the panels are clean and the inverter never clips. If your string voltage puts you close to the inverter’s DC limit on a cold bright March afternoon, you will clip, and pvlib will show you exactly when if you give it real module temperature coefficients.

Where language models lie to you about energy

Four failure modes, all of which I’ve hit repeatedly.

Arithmetic drift. Any chain of more than three multiplications will occasionally come out wrong, and the error is usually plausible rather than absurd. Fix: make the model write code, then run the code. Never accept a number that didn’t come out of an interpreter.

Unit collapse. kW and kWh get used interchangeably, and half-hourly data makes this worse because a 0.5 kWh interval is 1 kW of demand. Fix: put the unit in the column name. import_kwh not import.

Hallucinated tariff rules. Models will confidently describe SEG rates that don’t exist, or claim Agile is capped at 35p when the cap was raised to 100p/kWh in October 2023. They will also tell you about Feed-in Tariff rates as though FiT were still open to new applicants. Fix: fetch the tariff from the API and paste it in. Never let the model supply a price from memory.

Regional blindness. Agile has 14 regional variants, and the tariff code embeds the letter: E-1R-AGILE-24-10-01-C is London, -H is Southern, -N is Scotland. A model asked about “Agile prices” will pick one, usually without telling you which.

Prompt patterns that survive contact with real data

The single highest-leverage pattern is schema-first. Before asking any question, give the model the table structure and three real rows. Not a description of the data, the actual rows.

Table `hh`, 17,520 rows, DuckDB.
ts TIMESTAMPTZ (interval START, UTC) | import_kwh | export_kwh | gen_kwh | soc_pct | price_p

2026-01-14 23:30:00+00 | 2.412 | 0.0   | 0.0   | 12.0 | 7.00
2026-01-15 00:00:00+00 | 2.388 | 0.0   | 0.0   | 31.5 | 7.00
2026-01-15 12:30:00+00 | 0.041 | 0.612 | 0.981 | 98.0 | 25.40

Write DuckDB SQL only. No prose. If the schema can't answer the
question, say which column is missing.

That last instruction earns its place. A model with permission to say “you don’t have the data for this” is much more useful than one that invents a proxy.

The second pattern is adversarial self-check: after any result, ask “what would make this number wrong?” On the battery analysis above, that prompt surfaced the round-trip efficiency omission and a daylight-saving double-count in March.

I’ve collected the ones that hold up across tools, including a full battery-sizing chain, a tariff-comparison prompt that handles standing charges properly, and an anomaly-triage prompt, in A Prompt Library for Household Energy Analysis. They’re written to be pasted with your own schema block substituted in.

Monitoring: the weekly loop that catches real faults

Modelling is a one-off. Monitoring is the part that pays, because a failed string or a battery stuck at 40% SoC costs you money silently for months.

The check that works: clear-sky ratio per string, week over week. Take your best five clear days in the window, sum generation per string, and compare against the pvlib clear-sky model for the same hours.

String   Panels   Expected(kWh)   Actual(kWh)   Ratio   Δ vs 4wk
A            7           58.2          56.9     0.98     -0.01
B            7           58.2          41.3     0.71     -0.26

String B lost roughly a quarter of its output over four weeks. On the array this came from, the cause was a shaded panel after a neighbour’s leylandii grew, not a fault, but the alert was correct and it took three minutes to read.

Two tools do this properly inside Home Assistant. Predbat plans battery charging against Agile or IOG rates using your own load history and a solar forecast, and it will tell you when its plan diverges from reality. EMHASS solves the same problem as a mixed-integer linear program via PuLP, which is heavier to configure but lets you express constraints like “never discharge below 20% on a weekday morning”.

Neither is an LLM. Both are better than an LLM at the optimisation, because the optimisation is a solved problem in operations research and a language model approximating a MILP solver is a party trick.

Picking tools without buying anything you don’t need

The minimum viable stack is Octopus API, DuckDB, and one LLM with code execution. That’s enough for tariff comparison, battery sizing, and fault detection. Everything else is refinement.

The gotchas, ranked by how much time they cost

Daylight saving is first, and it’s not close. The March transition drops two half-hourly rows and the October one duplicates two. A naive GROUP BY date gives you a 23-hour day and a 25-hour day, which shows up as a phantom 4% dip and a phantom 4% spike. Group on UTC, label in local time.

Meter register resets happen after a firmware update or a meter exchange, and cumulative readings go back to zero. If your pipeline computes consumption as a difference between consecutive readings, you’ll get a single enormous negative value. Clamp at zero and log the event rather than silently dropping it.

Export data requires an export MPAN, and the SEG requires an MCS-certified install plus a smart meter capable of half-hourly export readings. Plenty of people discover after commissioning that their meter reports import only, at which point the export figures in their app are the inverter’s numbers, not the meter’s, and the two differ by the house’s own consumption.

Inverter cloud APIs lag. GivEnergy’s half-hourly data is typically complete within 15 minutes but can be hours behind after a cloud outage, and a backfill will rewrite rows you’ve already ingested. Upsert on timestamp, don’t append.

Finally, the inverter and the meter will never agree. A 2% to 4% gap between inverter-reported export and meter-recorded export is normal, because they measure at different points with different accuracy classes. If yours is 15% apart, something is genuinely wrong, and that’s a question worth taking to your installer with the numbers attached.

Pick one thing from this page and build it this week. If you do nothing else, pull twelve months of half-hourly consumption from the Octopus API or n3rgy and drop it into DuckDB. Every other analysis here becomes a twenty-minute job once that file exists, and the prompt library assumes you have it.