Octopus Energy Integration: Display graph of usage?

[Biscuit (Geoff)]

It is all down to the graph span and offset. If you try and plot today in a window spanning yesterday the graph will show nothing.
The current accumulative consumption holds values for today from midnight to the current time, so the graph window (span 24 hours, start at day, no offset) needs to reflect this.

The following works for me.

type: custom:apexcharts-card
header:
  show: true
  title: Octopus Electricity Import - Current (Today)
show:
  last_updated: true
graph_span: 24h
span:
  start: day
series:
  - entity: >-
      sensor.octopus_energy_electricity_xxxxx_xxxxx_current_accumulative_consumption
    data_generator: |
      let array = entity.attributes.charges;
      let final = [];
      let i=0;
      let j=0;
      for (i=0; i<array.length-1; i=i+2){
        final[j] = [new Date(array[i].start).getTime(), array[i].consumption + array[i+1].consumption];
        j++;
      };
      return final;
    type: column
    color: purple

[andyblac (Andy Blackburn)]

I was talking about the data for yesterday (not today), my entity holds data going back ayear, so why is not able to use that data

[Biscuit (Geoff)]

You can, you just need a different approach.

Since the sensors for current and previous accumulative consumption hold an array of consumption figures in an attribute for the current day and previous 24 hours respectively, I use the ‘data-generator’ approach in the Apex Chart and generate the time-data array required that way, directly from the consumption values for each half hour.

If you want to go back into the history record, then we need to use the historical state value of the current accumulative consumption, which requires the normal ‘entity-state’ approach in the Apex Chart.

Since the state value is monotonically increasing, we can use the ‘group_by’ option, and a grouping transform function of ‘diff’. By default, the standard grouping bucket is time-based and defaults to hourly, so we don’t need to specify the duration, just the function. This will then plot bucket values by hour, each bucket calculated as the difference between the start and end values of the sensor state for each bucket time period.

Then all it needs is ensuring the graph span window is correctly set.

Here is the configuration for my electricity meter - current accumulative consumption (ie from mini) but for yesterday not today.

type: custom:apexcharts-card
header:
  show: true
  title: Octopus Electricity Import - Current (Yesterday)
show:
  last_updated: true
graph_span: 24h
span:
  start: day
  offset: '-1d'
series:
  - entity: >-
      sensor.octopus_energy_electricity_XXXXX_XXXXX_current_accumulative_consumption
    group_by:
      func: diff
    type: column
    color: purple
yaxis:
  - min: 0
    max: '|+0.5|'

[andyblac (Andy Blackburn)]

thanks that seemed to work

just need the bottom graphs to do accumulative data like bottom left today graph, any ideas how to do that one.

[Biscuit (Geoff)]

I’m sure if you read up on the Apex Charts configuration documentation you can work it out for yourself.

Decide what data you have to start with, are you plotting an entity from history, or need to compute (data generator) the data yourself. Then go from there…

[1471]

Hi, looking for a bit of help, i’ve recently started going down the Octopus energy rabbit hole. Posting in this thread as it’s been really useful to get me this far, appologies if its not the right place.

I’m using the graph below to show energy usage for the last 7 days using this:

type: custom:apexcharts-card
show:
  loading: false
header:
  show: true
  show_states: false
  colorize_states: false
chart_type: radialBar
apex_config:
  dataLabels:
    enabled: true
  legend:
    show: true
    position: bottom
    markers:
      fillColors:
        - orange
        - "#5296D5"
  chart:
    height: 250px
  update_interval: 5m
series:
  - entity: >-
      sensor.octopus_energy_electricity_xxxxxx_current_accumulative_cost
    name: Electric
    group_by:
      func: sum
      duration: 7d
    show:
      in_header: false
      legend_value: false
    min: 0
    max: 20000
  - entity: >-
      sensor.octopus_energy_gas_xxxxxx_current_accumulative_cost
    name: Gas
    group_by:
      func: sum
      duration: 7d
    show:
      in_header: false
      legend_value: false
    min: 0
    max: 20000

I would like to extract the total costs for the past 7 days from the sensor and display in a markdown card above.

Easy enough for todays and yesterdays, but tricky with historical data. I’ve confirmed the sensor data there, it’s stored for at least 30 days, looking for some help extracting the sum for the last 7 days, probably using a template or history_stats sensor?

My ablitys run out and could do with some pointers!

Markdown card im using for today and yesterday for info:

<font size="3">Energy cost today</font>

<font color=orange>Electric: £{{ '{:.2f}'.format(states('sensor.octopus_energy_electricity_xxxxxx_current_accumulative_cost') | float(0)) }}</font>&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;<font color=5296d5>Gas: £{{ '{:.2f}'.format(states('sensor.octopus_energy_gas_xxxxxx_current_accumulative_cost') | float(0)) }}</font>
Total: {{ states('sensor.octopus_combined_current_energy_usage') }}

and a pic of what it looks like:

[FarleighRed]

I want to be able to display my data in a similar way to the Linky-card but am not clever enough to be able to do it. Wondered if anyone might be willing to help.

GitHub - royto/linky-card: Linky custom card for Home Assistant

[Biscuit (Geoff)]

History is stored in the database for the default 10 days, and it can be extracted. The ApexCharts card does this for you (from the history tables) and does the ‘group by’ summation required.

If you otherwise want to display the costs for the past 7 days, you have to get the data from history by other means.

It appears to be quite possible to create an SQL sensor using the SQL integration. This will perform an SQL query on the HA database, and can be used to return just one value into a sensor state. In your case, you are looking for the summation of the past week of sensor.octopus_energy_electricity_xxxxxx_current_accumulative_cost state value.

I recently worked out the required SQL query to get at the history for these sensors, once you have first worked out the meta-id value.

If you use the SQLiteWeb add-on in HA, you can access the HA database, and run the SQL query on the statistics_meta table:

SELECT * FROM "statistics_meta" where "statistic_id" LIKE 'sensor.octopus_energy%previous%' AND "unit_of_measurement" = "GBP"

which will give the sensors for just the Octopus energy previous cost figures (use ‘current’ for the current sensors)

You then need to pick out the meta-id of your sensor of choice (in my case this is 115) and then create an SQL sensor (use the SQL integration) with the following query. Change the 115 value!

SELECT SUM(state) AS total FROM
(SELECT id, state, udate FROM
    (SELECT metadata_id AS id, state, date(created_ts, "unixepoch") AS udate, time(created_ts, "unixepoch") AS utime
    FROM "statistics"
    WHERE metadata_id = 115 AND unixepoch()-created_ts < 86400 * 7 AND utime > "20" AND utime < "23"
    ORDER BY udate, utime DESC)
GROUP BY udate)

This returns the sum in ‘total’ so use ‘total’ as the query table column name. Nothing else, apparently, is required and it, apparently, works.

You will need to set up one SQL sensor for each of electricity and gas, and then use these new sensors in your display.

I am no expert in SQL, so there may be someone who can simplify this as I am just using what worked for me in a bit of Node-RED code.

[Biscuit (Geoff)]

The Linky-card you refer to is specific for showing the output from a custom Linky sensor for a Linky utility meter device and EDF account in France.

If you are looking to show something similar, but using the Octopus Energy information, then all this data will have to be collated manually. I use Node-RED for doing stuff like this, including pulling data from an API call or out of history and manipulating it into tabular format.

A starting point could be to use this Linky-Card directly, perhaps with slight modifications (branch) to change the day formatting from French to English. Then all that would be required is a sensor providing the correct data in the schema used by the custom Linky sensor. The card I think only uses the main sensor, which is:

State

• The yesterday consumption in kWh.

Attributes

• daily : array of daily consumption.
• halfhourly : array of yesterday consumption per 30 min.
• peak_hours: yesterday consumption in peak hours
• offpeak_hours: yesterday consumption in off peak hours
• peak_offpeak_percent: percentage of yesterday consumption in off peak
• daily_cost: yesterday cost
• monthly_evolution: percentage of consumption compare to same month of previous year

This can all be collected into a new sensor generated from Node-RED. The Octopus-Energy integration has for yesterday the consumption, half-hourly array, cost. Only needs a bit of code to sum for a defined peak / off-peak periods. The only bit that requires work is the array of daily consumption (but this probably only needs to be for a week) and for the monthly-evolution. To get that requires an API call to Octopus to get account historic consumption figures, but even that is entirely doable.

[FarleighRed]

Wow. Thanks for that but all seems well beyond my current skill levels. I have tried looking at node red but to be honest it was too much for me at at this point was ready to spend the time learning.

If you fancy helping me learn then great but may have to think of a different way to display the information in my dashboard.

[Biscuit (Geoff)]

I have been working on this stuff (solar, energy, consumption and tariff) for almost three years and yes it does take time and effort to learn.

A bit of poking around gets the linky-card you referenced modified to English, and a very simple Node-RED sensor with static values shows that it does indeed work.

I don’t know quite what values the original linky-sensor returned, but assume that as today is Monday and ‘ongoing’, the top figures (peak/off peak, cost) are for ‘yesterday’ [Sunday] and that the array is to show the prior five days before that [Tuesday-Saturday].

This just needs some Node-RED to pull the data together and pick out the peak / off peak periods, most of which can come directly from the history of the integration consumption / cost entities. The only ‘challenge’ is the back-compare to the prior year, which either has to come from long-term history (if it exists) or pulled from Octopus by API call.

I don’t have time to look at this at the moment, but perhaps in a month or two…

[sexysaxon]

Hi, I’m not 100% certain this is directly related but I have some observations that may be pertinent as one of my systems seems to provide the info that Sentur raised in his original post. The problem is that this only works on one of my two “identical” systems.

I have two HA systems, one I use as a “live” environment and another one for test purposes. I’m using the energy dashboard of both systems and using the Octopus Energy Integration for the sensor (sensor.octopus_energy_gas_e6f20278172200_3210156301_previous_accumulative_consumption_kwh) to show my Gas Usage on both systems. The issue I have is that my “test” system shows usage at a granular level throughout the day which is great.

Here is the display on the test system for 26th October

However despite using the same sensor on the “live” system I get the display below with no granularity.

The issue is the same every day. I am running the same versions of OS, Core and the Octopus Integration and the configs are identical so am totally puzzled.

The granular data appears to be present in state attributes on both systems

Thoughts welcome

[Biscuit (Geoff)]

Gas consumption, for Octopus, is usually updated very late in the day. I would expect that the ‘one column’ graph is the total consumption being the state value of a sensor, which changes at 19:00 when updated and is for the previous day accumulated total. The other graph is showing the current consumption throughout the day, and looks more like the current rather than the previous consumption, and from the Octopus Mini which is updated periodically through the day.

With the very long names used (including the meter and account names) it is easy to get the current and previous sensors mixed up, and without knowing just how these graphs are generated, there could be other reasons for the difference…

[FarleighRed]

That looks really good. Have been trying to learn a little Node Red over the weekend but having no joy getting my head around around it.

I think I have tinkered with the card to get the references in English but having trouble with the node red as never used it before.

I am struggling to work out how to get the node to put together the array for the days of data.

[sexysaxon]

Yes the Live system seems to regularly show usage around 19:00.

I’m definitely using the same sensor on both systems, interestingly the reason I use the “previous” sensor is that the only usage sensors are for kWh and M3 on both systems.

BTW I’m not using Octopus Mini

[Biscuit (Geoff)]

The Integration sensor sensor.octopus_energy_gas_xxxxxx_xxxxxx_previous_accumulative_consumption_kwh

provides the total kWh consumption for yesterday in the state value, which gets updated around 19:00. The hourly figures are only provided in the attribute.Charges.consumption_kwh so clearly one graph is using the entity state, the other is using the consumption array.

Clearly something is different…

[Biscuit (Geoff)]

Node-RED is easy, but only once you have got your head around how it all works. This takes time.

To help you, the following shows how to use the WebSocket History node to get the history state value of the energy sensor (use your own previous_accumulative_consumption entity) for the past 7 days. It should ignore today, and if there are multiple updates in a day for any reason, it only gets the latest history from the day.

The Change node needs to be configured as

Set msg.payload to J: expression - then enter the following JSONata

(
    $today:=$substringBefore($now(),"T");
    $history:=payload.(
        $lc:=last_changed;
        {"state": $number(state),
        "changed": $lc,
        "date": $substringBefore($lc, "T"),
        "time": $substringBefore($substringAfter($lc, "T"),".")}
    )[date<$today];
    $array:=$distinct($history.date).(
        $d:=$;
        ($history[date=$d])[-1]
    );
    $array
)

This generates an array of objects, so you can check the state values and the dates. To get just an array of state values, change the final line of the code to

   $array.state

This may need a bit of work to match to the card, perhaps sorting in reverse, but it should get you started.

Good luck

[FarleighRed]

Thanks I have nearly got there, even managed to get a successful API call but my only issue is retrieving the information for the same period last year.

Have got my API call successfully setting the dates dynamically but for some reason it only brings back this years data?

https://{{Hidden}}:@api.octopus.energy/v1/electricity-meter-points/{{mpan}}/meters/{{meter}}/consumption?start_date=2023-09-30T23:00:00.000Z&end_date=2023-10-31T23:59:59.000Z&group_by=day

But an example of the results I get back

{“consumption”:20.142,“interval_start”:“2024-10-28T00:00:00Z”,“interval_end”:“2024-10-29T00:00:00Z”}

Any ideas where I am going wrong?

[Biscuit (Geoff)]

Documentation for the Octopus API (electricity meter consumption) is here

The period parameters required are period_from and period_to. The following is working for me.

?pagesize=32&order_by=period&group_by=day&period_from=2023-09-30T23:00:00.000Z&period_to=2023-11-01T00:00:00.000Z

There are, of course, issues in the month of October since this includes DST and the returned results are not straightforward. The period parameter must be a string in ISO UTC format, and represents the starting point of the half-hour period (grouped by day) as required. However, as the front of the month is BST, midnight local time is actually 23:00:00 the day before (as you have it set). The group by appears to use the UTC day, so the results stay on 00:00 BST (+1:00) or 23:00 UTC. When this meets the DST change day, the time then shifts again, since there are 25 hours in the day as the clocks have gone back.

Note that, to add to the confusion, the period start dictates the interval included from the interval start, and the period_end also includes the interval starting at the period end. Yes, it really is that simple.

  {
    "start": "2023-10-01T00:00:00+01:00",
    "endat": "2023-10-02T00:00:00+01:00",
    "used": 3.428
  },
  {
    "start": "2023-10-02T00:00:00+01:00",
    "endat": "2023-10-03T00:00:00+01:00",
    "used": 10.067
  },

 ...

  {
    "start": "2023-10-28T00:00:00+01:00",
    "endat": "2023-10-29T00:00:00+01:00",
    "used": 9.799
  },
  {
    "start": "2023-10-29T00:00:00+01:00",
    "endat": "2023-10-30T00:00:00Z",
    "used": 12.688
  },
  {
    "start": "2023-10-30T00:00:00Z",
    "endat": "2023-10-31T00:00:00Z",
    "used": 9.325
  },
  {
    "start": "2023-10-31T00:00:00Z",
    "endat": "2023-11-01T00:00:00Z",
    "used": 11.232
  },
  {
    "start": "2023-11-01T00:00:00Z",
    "endat": "2023-11-01T00:30:00Z",
    "used": 0.148
  }

I still find this very confusing and difficult to work with, however if you are comparing like with like, and on the assumption that an hour missing or extra around midnight or 01:00 in the morning is not that important, I just ignore the issue and go with what I get back.

[FarleighRed]

Thanks for all your help doing this Geoff. I have now managed to produce a card displaying the information I want and at the same time given me an introduction to Node Red.

As with all things Home Assistant, just as you think you are happy with how everything is working and displayed, you discover something new that makes you start all over again!

I had so much spare time before I started dabbling with Home Assistant.