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. Contributions
5. Roadmap
6. Contact

About The Project

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 import process consists of three straightforward steps:

1. Data preparation
   Prepare the data in the correct 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. Additionally, a generic conversion script is available that can handle various formats such as CSV, XLS, XLSX, SQLite database and JSON and can deal with headers, footers, date formats, data filtering and data recalculation.

2. Import CSV files
   Use the generic import script ImportData.py to import the CSV files generated from step 1 into a temporary working table within the Home Assistant database.

3. Process and import data
   Execute the database-specific version of the SQL script Import Energy data into Home Assistant.sql to process the temporary table, import the data into the appropriate Home Assistant tables, and adjust and integrate it with existing data.

Latest data correct after import (short term statistics work)

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

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

Features

• Correctly imports historical energy, gas, and water data into Home Assistant
• Supports a combination of low- and high-resolution data
• Supports electrical feed-in, electrical feed-out, solar power, battery feed-in, battery feed-out, gas, and water data
• Also supports other non-energy sensors, but no data preparation scripts currently exist
• Supports data feeds with double tariffs (normal tariff / low tariff)
• Rollback support that reverts changes if an import fails
• One-line configuration per sensor
• Option to provide a conversion factor per sensor (for instance, conversion between Wh/kWh or L/m³)
• Supports sensor resets (for instance, replacement of an energy meter)
• Support for SQLite (standard Home Assistant database) and MariaDB
• Generic data conversion script for "unsupported" energy providers
• Growing list of data conversion scripts for different energy providers (most using generic data conversion script)
• Import script to automate importing prepared CSV files

(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

Source data preparation

• Check the Datasources documentation for more information about the data preparation scripts.
• 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. The filename must include both the sensor identifier and the resolution. The sensor identifier is used by the generic SQL script to map the data to the corresponding sensor defined in Home Assistant.
      • Example: elec_feed_in_tariff_1_high_resolution.csv
        • Sensor ID: elec_feed_in_tariff_1
        • Resolution: HIGH
        • Data
          • 1540634400, 8120605
          • 1540638000, 8120808
          • 1540641600, 8120993
          • 1540645200, 8121012
    • Depending on the used energy 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.
      • elec_battery_feed_in_high_resolution.csv
        • Contains the highest resolution battery charging data available (for instance: hour resolution)
        • Not needed in case that there is no battery
      • elec_battery_feed_in_low_resolution.csv
        • Contains the lowest resolution battery charging data available (for instance: day resolution)
        • Not needed in case that there is no battery
        • Not needed in case that there is only one resolution available.
      • elec_battery_feed_out_high_resolution.csv
        • Contains the highest resolution battery discharging data available (for instance: hour resolution)
        • Not needed in case that there is no battery
      • elec_battery_feed_out_low_resolution.csv
        • Contains the lowest resolution battery discharging data available (for instance: day resolution)
        • Not needed in case that there is no battery
        • 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.

Database

• Determine the type of database that the Home Assistant installation uses and continue with that specific how-to. The standard installation of Home Assistant uses SQLite.
  • SQLite how-to
  • MariaDB how-to

(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)

(back to top)

Contributions are welcome

If you want to contribute to this please read the Contribution guidelines

All contributors

• Nick de Wijer (https://github.com/ndewijer)
  • Initial implementation: GreenChoice
  • Sample files for: SolarEdge
• vGelder (https://github.com/vGelder)
  • Sample files for: Oxxio
  • Sample files for: Liander
  • Sample files for: Engie
• Jaap P. (https://github.com/AJediIAm)
  • Implementation: P1Mon (ZTATZ)
• Minze Tolsman (https://github.com/miezie)
  • Implementation: VanOns
• Slyoldfox (https://github.com/slyoldfox)
  • Implementation: Fluvius
• TylonHH (https://github.com/TylonHH)
  • Implementation: EnergyControl (app)
• Nikolaj Hendel (https://github.com/nikolajhendel)
  • How to for: iSolarCloud (Sungrow)
  • Sample files for: iSolarCloud (Sungrow)

(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)