/Home-Assistant-Import-Energy-Data

Import historical energy data into Home Assistant so that it can be used in the Energy Dashboard

Primary LanguagePython

Issues


Logo

Home Assistant Import Historical Energy Data

Import historical energy/water data from external datasources into Home Assistant so that it can be used in the Energy Dashboard.

Report Bug · Request Feature

Table of Contents
  1. About The Project
  2. Getting Started
  3. Authors
  4. Roadmap
  5. Contact

About The Project

2019

Background

I have been enjoying the Home Assistant Energy Dashboard feature since it came out. The only downside was that I could not import my historical energy/water data. I was using "Toon" from my Dutch energyprovider Eneco until the Home Assistant Energy Dashboard came out. This led me to write an import script that could import Toon data into Home Assistant. After I got it working I made this specific import script as is available on GitHub.

Since then the import script has been used and adapted by several people so it could be used with other energy providers. Their feedback led me to the idea to rewrite my initial script and make it more generic and robust so that it can be used easier with other energy providers. The latest version of the script is independent from the energy provider and makes it possbile to import historical exported energy data into Home Assistant. It adds the statistics data that is missing in Home Assistant and adjusts the existing data.

The generic import script requires the data to be in a specific simple CSV file format (Epoch Unix Timestamp, sensor value). For several energy providers conversion scripts exist that convert the energy provider specific format to the needed format. To make live easier a generic conversion script is available that can handle formats like CSV, XLS, XLSX and JSON and can deal with headers, footers, date formats, data filtering and data recalculation.

Latest data still correct after import (short_term_statistics work) 2023

Data of 2019 - Imported using high resolution interval data (hourly) - statistics work 2019

Data of 2015 - Imported using low resolution interval data (daily) - statistics work 2015

Features:

  • Imports correctly historical energy and water data into Home Assistant
  • Supports combination of low and high resolution data
  • Supports electrical feed in, electrical feed out, solar power and gas data
  • Supports data feeds with double tariffs (normal tariff / low tariff)
  • One line configuration in case a sensor is not needed
  • Possibility to provide a conversion factor per sensor (for instance conversion between Wh/kWh or L/m³)
  • Supports reset of sensors (for instance replacement of energy meter)
  • Generic data conversion script for "unsupported" energy providers
  • Growing list of data conversion scripts for different energy providers (most using generic data conversion script)

(back to top)

Getting Started

Disclaimer

Importing historical energy data into Home Assistant is not simple and requires some technical knowledge. It alters the database of Home Assistant so be sure that you always have a recent backup of your Home Assistant data!

How-to

Tooling

Source data preparation

  • Check whether a script/how-to exists for your provider (datasources directory)
    • Script/how-to exists:
      • Follow how-to to prepare the needed CSV files
    • Script/How-to does not exist:
      • Determine how to get the data from your energy provider (download/API etc.)
      • Get the data from the energy provider using the identified method
      • Convert the data in the needed CSV files. The generic data conversion script TemplateDataPrepare.py can be used in most cases. In case the CSV files are created manually the CSV files should follow the following simple definition where each row contains: Epoch Unix Timestamp, sensor value
        • Example:
          • 1540634400, 8120605
          • 1540638000, 8120808
          • 1540641600, 8120993
          • 1540645200, 8121012
      • Depending on the used sensors determine which CSV data files need to be created:
        • elec_feed_in_tariff_1_high_resolution.csv
          • Contains the highest resolution usage data available (for instance: hour resolution)
          • Used in case there is only one tariff
        • elec_feed_in_tariff_1_low_resolution.csv
          • Contains the lowest resolution usage data available (for instance: day resolution)
          • Used in case there is only one tariff
          • Not needed in case that there is only one resolution available.
        • elec_feed_in_tariff_2_high_resolution.csv
          • Contains the highest resolution usage data available (for instance: hour resolution)
          • Not needed in case that there is only one tariff available.
        • elec_feed_in_tariff_2_low_resolution.csv
          • Contains the lowest resolution usage data available (for instance: day resolution)
          • Not needed in case that there is only one tariff available.
          • Not needed in case that there is only one resolution available.
        • elec_feed_out_tariff_1_high_resolution.csv
          • Contains the highest resolution production data available (for instance: hour resolution)
          • Used in case there is only one tariff
          • Not needed in case that there is no production (for instance: no solar panels, no battery export)
        • elec_feed_out_tariff_1_low_resolution.csv
          • Contains the lowest resolution production data available (for instance: day resolution).
          • Used in case there is only one tariff
          • Not needed in case that there is no production (for instance: no solar panels, no battery export)
          • Not needed in case that there is only one resolution available.
        • elec_feed_out_tariff_2_high_resolution.csv
          • Contains the highest resolution production data available (for instance: hour resolution).
          • Not needed in case that there is no production (for instance: no solar panels, no battery export)
          • Not needed in case that there is only one tariff available.
        • elec_feed_out_tariff_2_low_resolution.csv
          • Contains the lowest resolution production data available (for instance: day resolution).
          • Not needed in case that there is no production (for instance: no solar panels, no battery export)
          • Not needed in case that there is only one tariff available.
          • Not needed in case that there is only one resolution available.
        • elec_solar_high_resolution.csv
          • Contains the highest resolution production data available (for instance: hour resolution)
          • Not needed in case that there are no solar panels
        • elec_solar_low_resolution.csv
          • Contains the lowest resolution production data available (for instance: day resolution)
          • Not needed in case that there are no solar panels
          • Not needed in case that there is only one resolution available.
        • gas_high_resolution.csv
          • Contains the highest resolution production data available (for instance: hour resolution).
          • Not needed in case that there is no gas usage
        • gas_low_resolution.csv
          • Contains the lowest resolution production data available (for instance: day resolution).
          • Not needed in case that there is no gas usage
          • Not needed in case that there is only one resolution available.
        • water_high_resolution.csv
          • Contains the highest resolution production data available (for instance: hour resolution).
          • Not needed in case that there is no water usage
        • water_low_resolution.csv
          • Contains the lowest resolution production data available (for instance: day resolution).
          • Not needed in case that there is no water usage
          • Not needed in case that there is only one resolution available.

Home Assistant preparation

  • Create a backup of the Home Assistant database
    • Disable recorder while making the backup -> Developer tools/Services/Call service: Recorder:disable
  • Download the created backup
  • Stop the Home Assistant core
    • Developer tools/Services/Call service: Home Assistant Core Integration: Stop
  • Home Assistant data:
    • extract: home-assistant_v2.db (from backup.tar extract homeassistant.tar.gz from data folder). As an alternative it is also possible to download the home-assistant_v2.db directly from the Home Assistant config directory (For example: use WinSCP in combination with the Home Assistant SSH addon). In case of this method make sure that you didn't skip the step to create a backup so that you can always restore this version of the database!

Import the data

  • Start DB Browser for SQLite
  • Open project Import Energy data into Home Assistant.sqbpro.
    • If the database is not loaded directly you have to open the home-assistant_v2.db database manually ("Open Database").
  • Validate the schema version of the database (Browse Data -> Table: schema_version)
    • The script has been tested with schema version 42. With higher versions you should validate if the structure of the statistics and short_term_statistics tables have changed.
      • Used fields in table statistics: metadata_id, state, sum, start_ts, created_ts
      • Used fields in table short_term_statistics: sum
  • Import, one at a time, all the created CSV data elec*, gas* and water* files (File -> Import -> Table from CSV file...)
    • It is possible to load data from multiple CSV's with the same name. The data of the second import is than added to the existing tables. This can be used in case there are multiple energy source providers for different timeperiods. In this case you first import the files from the first energy provider and than then second etc.
  • Lookup in the statistics_meta table the ID's of the sensors (Browse Data -> Table: statistics_meta; You can use "filter" to find the id of the sensor)
    • The names of the sensors can be looked up in the Home Assistant Energy dashboard (Settings -> Dashboards -> Energy).
      Example:
        id  statistic_id                                source      unit_of_measurement
        6   sensor.gas_meter                            recorder    m³
        7   sensor.electricity_meter_feed_in_tariff_1   recorder    kWh
        8   sensor.electricity_meter_feed_in_tariff_2   recorder    kWh
        9   sensor.electricity_meter_feed_out_tariff_1  recorder    kWh
        10  sensor.electricity_meter_feed_out_tariff_2  recorder    kWh
        352 sensor.solar_energy_produced_today          recorder    kWh
        653 sensor.watermeter_quantity_m3               recorder    m³
  • Change the script and remove/comment out the lines of the sensors that are not needed. They can be found at the top of the script by looking up the lines where /* Change */ has been added in the SQL statement.
  • Change the script and update the ID's according to the found ID's in the statistics_meta table. They can be found at the top of the script by looking up the lines where /* Change */ has been added in the SQL statement. Determine also the correction factor in case the unit_of_measurement of the sensor differs from the used datasource. The unit of measurement of the datasource can be found in the readme of the datasource.
  • Execute the SQL and wait for it to complete. (Please be patient because this can take some time!)
  • Commit the changes by selecting "Write changes" in the toolbar, if the script ends without errors. In case of an error select Revert changes and correct the error and execute the script again.

Replace Home Assistant database

  • Make sure that the Home Assistant core is still stopped (Home Assistant UI does not respond)
  • Upload home-assistant_v2.db to the Home Assistant config directory (For example: use WinSCP in combination with the Home Assistant SSH addon).
  • Restart/reboot Home Assistant (physically reboot Home Assistant or login using PUTTY-SSH and execute the reboot command)
  • Validate the imported data in the Energy Dashboard
  • Enjoy :-)

(back to top)

Authors

The project initially began as a tool for importing historical data for Toon (Eneco) into Home Assistant. In early 2024, the project scope expanded, enabling the import of data from various energy providers.

Hopefully, together with the community, the number of supported energy providers (datasources) can be expanded. Making it easier to import data from various energy providers.

Please share scripts or how-to guides if you have built an integration with a new energy provider. Your name will be added to the list below as a contributor.

Project technical leads:

  • Patrick Vorgers (the Netherlands)

All other contributors:

(back to top)

Roadmap

  • Support more datasources
  • Support MariaDB or provide workaround

See the open issues for a full list of proposed features (and known issues).

(back to top)

Contact

Project Link: https://github.com/patrickvorgers/Home-Assistant-Import-Energy-Data

(back to top)