Import old energy readings for use in energy dashboard

fversteegen · September 25, 2021, 4:45am

I am a homeassistant enthusiast since about 4 years and have been using Emoncms now for about 7 years. As homeassistant is now able to show historic energy usage it would be great if I could import historic data into the energy dashboard so that I can kill all legacy systems and have 1 holistic view of my energy consumption. Couldn’t care too much about the way it would allow me to import that data (excel, CSV, SQL) as long as it would be possible.

gijbelsy · September 25, 2021, 7:50am

Same request here for the CSV data of the Belgian energy grid operator.
Their CSV is as follows:

From-Date;From-Time;Until-Date;Until-Time;EAN;Meter;Metertype;From/To-Grid;Volume;Unit;Validity

CChris · October 1, 2021, 8:09am

I am also interested in this feature for the SolarEdge API.
My SolarEdge installation was done in April - at that time, I already had HomeAssistant running - but since then, it was required to do a fresh installation of the System.

When the Solar was installed, the Energy Dashboard was not yet rolled out… and since HA was newly installed, I only have data available for a few days now.

I know, the API can deliver a complete history of production data - collected every 15min.
It would be great, if there’s a way to import the old data to the dashboard …

ahochsteger · November 16, 2021, 7:26am

Same here, I’m using Home Assistant for a few years and now my energy provider allows me to download historic energy data as simple CSV files with , e.g.:

Date;Time;Data [kWh]
2021-09-04;00:15:00.000;0,152;
2021-09-04;00:30:00.000;0,150;
2021-09-04;00:45:00.000;0,150;

It would be great to import this historic data to make it available for use in the energy dashboard.

flixlix · November 16, 2021, 7:42am

I would also like this. I have solar panels since Sep’ 20 and have always monitored them with Shelly’s. I also have a Shelly EM that measures the “Grid Import” / “Grid Export”, so I have all data needed for this.

The Shelly Cloud has the possibility to download all history into a .csv file. This file contains accumulated energy with 10 minute intervals (in my grid Shelly: import and export are separated).

ahochsteger · January 14, 2022, 10:20am

@fversteegen you might be interested in this feature request as well, since it is strongely related to this one:

trung · February 14, 2022, 10:16pm

I support the request to be able to import historical values since energy consumption on hourly basis is only available from last day in Quebec.

robertpa · March 19, 2022, 10:20am

from me!
I am getting my readings (imported & exported energy + gas readings) from my supplier’s Restful API (Octopus Energy).
These readings are for 30 minutes blocks, however they are only available up to a previous day (so effectively they are always delayed by up to a day)
That would be very handy if I could use import these data in Home-Assistant!

CentralCommand · June 1, 2022, 4:24pm

Just wanted to note for any interested developers it seems there is a way for an integration to add data in the past to history without changing the current state of entities. It can be done by using async_add_external_statistics:

github.com

home-assistant/core/blob/74e2d5c5c312cf3ba154b5206ceb19ba884c6fb4/homeassistant/components/recorder/statistics.py#L1335-L1362

    
      
          def async_add_external_statistics(
              hass: HomeAssistant,
              metadata: StatisticMetaData,
              statistics: Iterable[StatisticData],
          ) -> None:
              """Add hourly statistics from an external source.
          
          
    This inserts an add_external_statistics job in the recorder's queue.
              """
              # The statistic_id has same limitations as an entity_id, but with a ':' as separator
              if not valid_statistic_id(metadata["statistic_id"]):
                  raise HomeAssistantError("Invalid statistic_id")
          
          
    # The source must not be empty and must be aligned with the statistic_id
              domain, _object_id = split_statistic_id(metadata["statistic_id"])
              if not metadata["source"] or metadata["source"] != domain:
                  raise HomeAssistantError("Invalid source")
          
          
    for statistic in statistics:
                  start = statistic["start"]

This file has been truncated. show original

There are currently two integrations in core making use of this if you’re looking for an example, Demo:

github.com

home-assistant/core/blob/74e2d5c5c312cf3ba154b5206ceb19ba884c6fb4/homeassistant/components/demo/init.py#L236

    
      
          metadata = {
              "source": DOMAIN,
              "statistic_id": f"{DOMAIN}:temperature_outdoor",
              "unit_of_measurement": "°C",
              "has_mean": True,
              "has_sum": False,
          }
          statistics = _generate_mean_statistics(
              yesterday_midnight, yesterday_midnight + datetime.timedelta(days=1), 15, 1
          )
          async_add_external_statistics(hass, metadata, statistics)
          
          
# Fake yesterday's energy consumption
          metadata = {
              "source": DOMAIN,
              "statistic_id": f"{DOMAIN}:energy_consumption",
              "unit_of_measurement": "kWh",
              "has_mean": False,
              "has_sum": True,
          }
          statistic_id = f"{DOMAIN}:energy_consumption"

And tibber:

github.com

home-assistant/core/blob/74e2d5c5c312cf3ba154b5206ceb19ba884c6fb4/homeassistant/components/tibber/sensor.py#L642

    
      
          else:
              unit = home.currency
          metadata = StatisticMetaData(
              has_mean=False,
              has_sum=True,
              name=f"{home.name} {sensor_type}",
              source=TIBBER_DOMAIN,
              statistic_id=statistic_id,
              unit_of_measurement=unit,
          )
          async_add_external_statistics(self.hass, metadata, statistics)

To be clear, this isn’t a solution to this FR. But if anyone is interested in building an integration to contribute to core or make available in HACS which does import of historical data I think it can be done using this API in core without the need to write a lot of custom SQL.

The_Nox · September 8, 2022, 7:42am

It seems that there is an import api through websocket events api recorder/import_statistics

janmolemans · September 11, 2022, 11:53pm

When playing with the demo-integration, it seems that the async_add_external_statistics always creates a new entity and thus it seems to be unable to link the historical data to the already existing sensor entity. As a result, after importing the historical data, you will have two entities (one containing the imported data, one containing the measured-by-HASS-data) which is annoying.

The_Nox · September 12, 2022, 6:50am

yes, but websocket events api recorder/import_statistics
switches import method based on the id (xyz:something = external, xyz.somehting = existing sensor)

if valid_entity_id(metadata["statistic_id"]):
        async_import_statistics(hass, metadata, stats)
    else:
        async_add_external_statistics(hass, metadata, stats)

I successfully imported external data to an external entity, and can update it. But having some problems with continuity. Only way I was able to have the right charts is to get the sum of the last inserted value and continue inserting increasing sums without ever resetting. The last_rest does not seem to work, at least for yearly energy graph.

janmolemans · September 12, 2022, 10:45pm

Thanks, I am now using async_import_statistics to import my energy statistics, which seems to work perfectly.

AndyMcK · September 12, 2022, 11:54pm

Any chance you might do a mildly detailed run through of how you do this? Dominion recently began offering detailed statistics for my area, though there’s no way to hoover them into HA that I can see. I’d love to be able to track them in HA if possible.

Thanks.

o0larry0o · September 14, 2022, 7:37am

same request here
Could you please give us some details on the procedure ?

Thx a lot !!!

The_Nox · September 14, 2022, 1:16pm

I am still in the process of improving my script.

I have one part which parses my energy supplier’s Dashboard and stores them in a database.
I then have additional scripts to aggregate and send the data to influxdb or/and home assistant.
For home assistant I aggregate the data to hourly values, since this is the smallest unit supported in the statistics history screen.

The websocket api upload can be done with any programming language. I used Javascript/Node here the relevant function node.js script to upload external data to home assistant via the websocket api · GitHub

It retrieves the last current value for a date and appends the new values by adjusting the sum based on this existing stat. It is the only way I found to have graphs without errors, because any combination of reset with last_reset did not work.

I use long lived access token which can be generated in your profile page in home assistant.

janmolemans · September 15, 2022, 10:44pm

My integration is still a work in progress, but the part that is doing the import of the statistics in the existing sensor is here: huawei_fusionsolar/sensor.py at ea2b58ee8a537b02ab1a367107f77c5960ac9f7a · janmolemans/huawei_fusionsolar · GitHub

slyoldfox · September 16, 2022, 11:42am

So this script wouldn’t exactly work if I need to prepend data?

I am looking to fill my historical energy values from my energy supplier, from before I was monitoring my home meter.

The_Nox · September 16, 2022, 6:06pm

The websocket api can be used to add data to existing entites you must use the “type.name” syntaxe instead of “external:name”. But you might get problems with sum changes between imported data and the one already inserted.

o0larry0o · September 17, 2022, 10:11am

Wow, this looks like far beyond my skills …

On my side I have a year of data to import that i can manipulate easily (CSV file)

Would one of these scripts be able to do that ?