Scenario 4: Loading import & export data from Octopus Energy
In situations where your inverter has been offline from the internet for an extended period of time then there simply isn’t the data in the GivEnergy Portal (scenarios 2 and 3) to re-load back into Home Assistant. For Octopus Energy customers they provide the ability to download import and export meter data in half-hour blocks which at least means you can recover that data into Home Assistant.
As well as the meter reading data there is estimated cost figures in the Octopus data download - data which of course isn’t in the GivEnergy Portal. Its this latter (import cost) data that I wanted to restore into Home Assistant from when my statistics went wild between about 21:00 on 19th January through to 22:00 on 20th January, but the process also covers recovering import or export readings as well.
(20th January was reporting 35kWh imported for £0.16 of cost - I wish !)
- Download the required data from Octopus Energy
Login to your Octopus account (in a web browser, the app doesn’t have the data download capability).
Click ‘My energy’, scroll down to ‘Get your energy geek on’
Choose the data, either Electricity or Electricity Export, select the date range required and click download.
This will produce a CSV file looking like this:
Consumption (kwh), Estimated Cost Inc. Tax (p), Start, End
0.407000, 8.79, 2025-01-18T23:30:00+00:00, 2025-01-19T00:00:00+00:00
2.408000, 47.65, 2025-01-19T00:00:00+00:00, 2025-01-19T00:30:00+00:00
0.339000, 7.97, 2025-01-19T00:30:00+00:00, 2025-01-19T01:00:00+00:00
- Calculate the Unix timestamps for the date/times of the first and last hours adjustments we are going to make.
As per scenario 2, I am going to update import data from 21:00 on 19/1/25 through to 22:00 on 20/1/25 - times that slightly overlap the ‘missing/bad statistics’ time period in HA.
However since cost is a ‘today’ entity whose state resets to zero at the start of each day, we will need to update the entity values (state) up to the end of the day so the end time will need to be 23:00 on 20/1/25 (the last statistics entry in the day that covers 23:00-23:59).
Likewise if retrieving energy import or energy export data from Octopus and loading it into a ‘today’ entity (such as sensor.givtcp_xxxx_import_energy_today_kwh) rather than loading it into a 'total entity (e.g. sensor.givtcp_xxx_export_energy_total_kwh) then the end time will also need to be extended to 23:00 on the impacted day.
Note: the date and time must be precisely formatted as shown, its only the hours that is required to be filled in, the minutes are always zero and the milliseconds are not required.
Developer tools/template
{% set ds="19.01.2025 21:00" %}
{{ strptime(ds,'%d.%m.%Y %H:%M')|as_timestamp()|int }}
{% set de="20.01.2025 23:00" %}
{{ strptime(de,'%d.%m.%Y %H:%M')|as_timestamp()|int }}
Gives the Unix timestamps for the start and end as 1737320400 and 1737414000 respectively.
- Identify the import/export/cost entities to be updated in HA.
This is not entirely straightforward.
Import and export entities being used in the Energy dashboard are easy enough, Energy Dashboard / three dots (top right) / Energy configuration
And the import entity is under ‘Grid consumption’ and the export under ‘Return to grid’
The financial entities used to track import costs and export income though are not shown in the dashboard and are auto-created behind the scenes by the Energy dashboard.
Go to Developer Tools / Statistics and search for ‘sensor Cost’ this will find the import cost entity, which will be named something like ‘sensor.[import entity friendly name with underscores]_cost’
My import entity is shown on the dashboard as ‘Grid Import Today Day’ and the cost entity id is sensor.grid_import_today_day_cost_2 - the ‘_2’ I guess because somehow in the past I changed things in the Energy dashboard configuration.
The export income entity can be similarly found by searching for ‘sensor Compensation’, my entity id is sensor.grid_export_today_day_compensation_2 (as I use today sensors for import/export in the Energy dashboard).
If you’ve used the import and export total entities in the dashboard then the cost and compensation entities will have total rather than today in the entity id.
- Query the existing LTS for the range of records we are going to be updating.
SQLweb add-on. Paste in the following query, substituting your own entity id, start and end timestamps (from step 2):
SELECT * FROM statistics
LEFT JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
WHERE statistics_meta.statistic_id = 'sensor.grid_import_today_day_cost_2' -- entity id
AND start_ts >= 1737320400 -- start time stamp
AND start_ts <= 1737414000 -- end time stamp
ORDER BY start_ts
You should get a result set looking something like this:
id created created_ts metadata_id start start_ts mean min max last_reset last_reset_ts state sum id statistic_id source unit_of_measurement has_mean has_sum name
3500222 NULL 1737324010.166711 7 NULL 1737320400.0 NULL NULL NULL NULL 1737244800.092167 14.3084 1850.3616 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3139356 NULL 1738535943.4376893 7 NULL 1737324000.0 NULL NULL NULL NULL 1734048000.016701 11.3379 1851.5636 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3139632 NULL 1738535948.3159373 7 NULL 1737327600.0 NULL NULL NULL NULL 1734048000.016701 11.3379 1852.7736 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3139908 NULL 1738535951.3488548 7 NULL 1737331200.0 NULL NULL NULL NULL 1734048000.016701 11.3379 1852.7736 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3140184 NULL 1738535953.4722095 7 NULL 1737334800.0 NULL NULL NULL NULL 1734048000.016701 11.3379 1852.7736 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3140460 NULL 1738535955.0181875 7 NULL 1737338400.0 NULL NULL NULL NULL 1734048000.016701 11.3379 1852.7736 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
...
3500847 NULL 1737403210.1605432 7 NULL 1737399600.0 NULL NULL NULL NULL 1737389883.640885 0.09999 1852.76999 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3501147 NULL 1737406810.1452768 7 NULL 1737403200.0 NULL NULL NULL NULL 1737389883.640885 0.09999 1852.76999 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3501447 NULL 1737410410.151534 7 NULL 1737406800.0 NULL NULL NULL NULL 1737389883.640885 0.17606 1852.84606 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3501751 NULL 1737414010.1274874 7 NULL 1737410400.0 NULL NULL NULL NULL 1737389883.640885 0.22060 1852.89060 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3502053 NULL 1737417610.1580782 7 NULL 1737414000.0 NULL NULL NULL NULL 1737389883.640885 0.26514 1852.9351 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
What we should see is state incrementing by the £ import value each hour until it gets to 23:00, and then the next record (midnight) it starts back from zero again. Sum should increment forever from when the sensor was first created.
This doesn’t happen for my data, state increases, gets stuck at 11.3379, then resets to zero in the middle of a day. Sum looks like it is incrementing correctly.
We need to make a note of the ‘state’ and ‘sum’ values corresponding to the start time of the first record (which we know is OK). In my case they are 14.3084865 and 1850.361639000002 respectively.
If you want to convert the date/time of any of the records to printable format, copy the start_ts into this developer tools/template code fragment:
{% set x=1737320400.0 %}
{{x|timestamp_custom('%Y-%m-%d %H:%M')}}
- Reformat the CSV file to format required by the Import Statistics integration.
To make it easier and repetitive I have created a Google Sheets spreadsheet to do this.
Open the spreadsheet and click File/Make a Copy to save a copy in your own Google account.
Navigate to the ‘Octopus CSV’ tab.
Open the CSV file created in step 1, select all and copy the entire file contents, then paste it into column B of ‘Octopus CSV’ tab on the spreadsheet.
If you have more input CSV rows than are pre-populated in the spreadsheet, simply copy cells C3 through to Y3 into the matching cells of any additional rows you need in the spreadsheet.
Whilst this contains the data we need, it contains all the import (or export) data for every half hour of the selected date range, so column E (Start) currently looks like this:
2025-01-19T00:00:00+00:00
2025-01-19T00:30:00+00:00
2025-01-19T01:00:00+00:00
2025-01-19T01:30:00+00:00
2025-01-19T02:00:00+00:00
- Edit the spreadsheet and remove the time slots we don’t need.
The Octopus download contains import/export kWh and cost/compensation £ for every half hour slot in the selected date range.
Delete all rows from row 2 downwards before the start hour of the time period you wish to correct, remember that there are two records for each hour, one from xx:00 to xx:30 and one from xx:30 to xy:00 and both need to be deleted.
An easy way to do this to delete the first row (right mouse click, delete row), then click anywhere in the next row to remove and press control Y to repeat the last command. Keep positioning the cursor and pressing control Y to repeat the delete row command for each row in turn.
Only delete any trailing records after the last (30 minute) record in the time range being updated if loading import/export kWh into a total entity. For any loading into a today entity (of either import/export kWh or cost/compensation into the day GBP entity that underpins the Energy dashboard), the file must include all records up to the end of the day.
So column E now looks something like this:
2025-01-19T21:00:00+00:00
2025-01-19T21:30:00+00:00
2025-01-19T22:00:00+00:00
...
2025-01-20T22:30:00+00:00
2025-01-20T23:00:00+00:00
2025-01-20T23:30:00+00:00
- Next we reformat the spreadsheet data into the precise format that the Import Statistics integration requires.
Find the column of data you want to extract (i.e. kWh Consumption - column C, or £ Estimated cost - column D) and copy the entire column into column M. If you are loading Consumption (import or export kWh) into HA then you’ll need to do Edit/Paste special/Values only to copy the values (not formulae) from Column C into column M.
The cells you are pasting data into are coloured pale red.
Column N is automatically populated in the spreadsheet with the hourly summation of the half-hour records, and column O contains the reformatted date/time.
Paste the entity id we are going to correct into cell Q1, e.g. sensor.grid_import_today_day_cost_2
Type the unit of measurement for the entity in cell S1, e.g. kWh (or Wh) for import/export or GBP for cost/compensation.
Paste the ‘state’ value for the first record obtained from the SQL query in step 4 into cells U1, e.g. 14.3084865
Paste the ‘sum’ value for the first record obtained from the SQL query in step 4 into cell W1, e.g. 1850.361639000002
If loading an import cost or export compensation GBP ‘today’ or ‘total’ entity:
a. Paste the following formulae in cell U2:
=round((N2/100)+if(mid(O2,12,2)=“00”,0,U1),4)
b. Paste the following formulae in cell W2:
=round(W1+(N2/100),4)
The formulae is the same for today and total cost entities because Home Assistant resets the cost entity state to zero at the start of each day, even on the total cost entity, so this formula loads the entity in a consistent manner to how HA operates.
Column D cost figures in the Octopus file include VAT for import costs and exclude VAT for export compensation.
If loading an import or export kWh ‘today’ entity:
a. Paste the following formulae in cell U2:
=round(N2+if(mid(O2,12,2)=“00”,0,U1),3)
b. Paste the following formulae in cell W2:
=round(W1+N2,3)
If loading an import or export kWh ‘total’ entity:
a. Paste the following formulae in cell U2:
=round(N2+U1,3)
b. Paste the following formulae in cell W2:
=round(W1+N2,3)
Then (for all types of entity loading), copy cells U2 and W2 and paste them into cells U3/U4/U5 and W3/W4/W5 etc all the way to the bottom of the data set.
Optional: the spreadsheet is setup to sum all half-hour Octopus meter readings into hourly values for storing in long term statistics.
If you are loading historical GBP import cost or export compensation figures into LTS then you could save a bit of database space and only load daily cost/compensation figures into LTS. The Energy dashboard only shows a single daily £ value so there’s no real value in storing the hourly breakdown.
To adapt the spreadsheet to daily rather than hourly data loads:
a. in cell N2 replace the formula:
=if(mid(O2,15,2)=“00”,M2+M3,“”)
with the new formula:
=if(mid(O2,12,2)=“00”,sum(M2:M49),“”)
b. in call O2 replace the formula:
=if(mid(E2,16,2)=“00”,mid(E2,10,2)&“.”&mid(E2,7,2)&“.”&mid(E2,2,4)&" “&mid(E2,13,2)&”:00",“”)
with the new formula:
=if(mid(E2,13,5)=“00:00”,mid(E2,10,2)&“.”&mid(E2,7,2)&“.”&mid(E2,2,4)&" “&mid(E2,13,2)&”:00",“”)
And then copy cells N2 and O2 and paste them into cells N3/N3/N5 and O3/O4/O5 etc all the way to the bottom of the data set.
(don’t forget to change the formula back when you want to load hourly kWh data)
The spreadsheet will reformat the CSV data, producing the correctly formatted output data in column Y (coloured green).
Select and copy cells Y downwards to the end of your data, and paste the reformatted data into a new text file which will look something like this:
statistic_id|unit|start|state|sum
sensor.grid_import_today_day_cost_2|GBP|19.01.2025 21:00|14.370405|1850.446311
sensor.grid_import_today_day_cost_2|GBP|19.01.2025 22:00|15.58914|1851.665046
sensor.grid_import_today_day_cost_2|GBP|19.01.2025 23:00|16.79664|1852.872546
sensor.grid_import_today_day_cost_2|GBP|20.01.2025 00:00|0.722505|1853.595051
(every alternate line will be blank, and this is OK as they are ignored on loading; this is a consequence of there being two Octopus half-hour records that have been summed into a single hourly record for loading into statistics).
Save this file and name it appropriately.
Important! Check the unit of measurement (cell S1) is set correctly for the entity being loaded. I have accidentally left the UoM set to GBP when loading entity history for a ‘total kWh’ entity.
The load proceeds as normal but an error appears in Developer Tools/statistics and any further statistics collection is stopped until the error is cleared.
-
Copy the file created and saved in step 7 into the /config folder of your Home Assistant server (use Samba share, HA file editor, SSH, or whatever you prefer).
-
Recommended that you take a backup of HA before you load the new LTS. Just in case !
-
In HA, go to Developer Tools/ Actions and search for ‘import_statistics’
Fill in the UI as follows:
filename: name of the file copied into /config in step 8
timezone_identifier: Europe/London
delimiter: | (the vertical pipe symbol)
And click on Perform Action
- View the updated statistics by re-running the query from step 4 above, viewing the energy dashboard and looking at the entity state history in the history graph for the selected date/time period.
Can also check the import/export kWh and cost/compensation figure in the Energy dashboard against what is shown in the Octopus app for the days you loaded. Note that there is likely to be small variations in part-loaded days as the inverter import/export meter is not a perfect mirror of the physical electricity meter readings that Octopus uses.
- Dealing with additional Energy dashboard spike after the loaded history
After loading the missing statistics history from Octopus, its likely that there will be a new spike showing in the Energy dashboard immediately after where the LTS has been loaded. This is due to the sum value not incrementing correctly across the transition from the replaced history to the existing history records.
This is best seen by looking at the data through SQL.
Repeat the SQL query from step 4 but expand the ‘start_ts where clauses’ to include a few more records either side, i.e.:
SELECT * FROM statistics
LEFT JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id
WHERE statistics_meta.statistic_id = 'sensor.grid_import_today_day_cost_2' -- entity id
AND start_ts >= 1737320400 - (3*3600) -- start time stamp less 3 hours
AND start_ts <= 1737414000 + (3*3600) -- end time stamp plus 3 hours
ORDER BY start_ts
(The +/- 3*3600 adds 3 extra records before the start time and 3 after the end time as each start_ts is 3600 apart from the next record)
This shows the LTS records before the history restore, the records loaded by the import_statistics integration, and a few afterwards. We need to focus on the records at the end.
e.g.:
id created created_ts metadata_id start start_ts mean min max last_reset last_reset_ts state sum id statistic_id source unit_of_measurement has_mean has_sum name
[LTS records loaded by the integration:]
3501447 NULL 1737410410.151534 7 NULL 1737406800.0 NULL NULL NULL NULL NULL 11.530365 1864.318239 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3501751 NULL 1737414010.1274874 7 NULL 1737410400.0 NULL NULL NULL NULL NULL 11.580345 1864.368219 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3502053 NULL 1737417610.1580782 7 NULL 1737414000.0 NULL NULL NULL NULL NULL 11.629905 1864.417779 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
[existing LTS records that are immediately after the history load:]
3502356 NULL 1737421210.1547968 7 NULL 1737417600.0 NULL NULL NULL NULL 1737417600.051562 0.115878 1853.051024 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3502659 NULL 1737424810.1311069 7 NULL 1737421200.0 NULL NULL NULL NULL 1737417600.051562 0.246036 1853.1812 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
3502962 NULL 1737428410.154108 7 NULL 1737424800.0 NULL NULL NULL NULL 1737417600.051562 0.793947 1853.7291 7 sensor.grid_import_today_day_cost_2 recorder GBP 0 1 NULL
As explained in scenario 5 below, sum is initialised to zero when the entity is first created and increments forever upwards as state increments, whereas on a ‘today’ entity, state is reset to zero at the start of each day. Because there has been a problem with the statistics, this consistency is broken.
Looking at the records before and during the inserted history, state increments each hour, gets up to 11.629905 (at midnight), then at the end of the next hour (1am) having been reset to zero at midnight, is 0.115878.
Sum increments each hour by the difference between the state value and the previous state value (ignoring the reset to zero) but at the point of transition sum drops from 1864.417779 (loaded by the integration) to 1853.051024 (original LTS value) whereas it should be 1864.417779 (prior record value) + 0.115878 (state change in the first hour).
This jump in value causes a spike in the Energy dashboard on the day after the history load (21/1/24). In my case, as its the import cost that spikes, showing that for 49.6kWh of import it only cost £1.78 - as if!
On subsequent records after the transition point state and sum increment together properly, the next record has state 0.246036 and sum is 1853.1812, both 0.130176 more than the prior record.
The fix we need to apply (borrowing from the techniques in scenarios 5 and 7) is to adjust sum to bring it into line with state by adding 1864.417779 - 1853.051024 + 0.115878 = 11.482633 to the first record after the history insertion and every record thereafter (because they will all be wrong by this delta amount):
You must update statistics_short_term first as the statistics table is built from aggregating statistics_short_term, and if statistics_short_term isn’t corrected then future statistics records written will have the wrong sum value.
UPDATE statistics_short_term
SET sum = sum + 11.482633 -- add adjustment factor to correct for missing sum history
WHERE metadata_id = 7 -- metadata_id is the entity being corrected
AND start_ts > 1737414000 -- start_ts is the end time of the history load, we adjust every record after then
and statistics:
UPDATE statistics
SET sum = sum + 11.482633 -- add adjustment factor to correct for missing sum history
WHERE metadata_id = 7 -- metadata_id is the entity being corrected
AND start_ts > 1737414000 -- start_ts is the end time of the history load, we adjust every record after then
Repeat step 11 to check the entity history and Energy dashboard for the day after the loaded history and it should all match without any more spikes.
