Power consumed per day

Hi All,

I am feeding readings from my smart-power-meter into HA using the DSMR component, and it works nicely. For some time now I have sent myself a notification at midnight to send the meter readings, so that I can add those to a spreadsheet keeping track of daily power usage totals and do some calculations with them.

However, I am lazy and/of forgetful, so the spreadsheet remains largely empty. It must be possible to do something like this in home automation completely and write a line to file or a google doc, or some other form of output, but I do not know how to keep track of the meter readings as they were at 00:00:00 of a given day and compare/calculate the difference with the meter readings 24 hours later.

An example:
I have two meters,
Meter 1: measures from 7 am until 11 pm.
Meter 2: measures from 11pm until 7 am
There are different tariffs for each meter.

So, I am thinking:
At everyday at midnight, store the values of meter 1 and meter 2 in separate variable. Then, at 23:59:59 read those meter values again.

Then, subtract the two values for each meter so that whats left is the consumed power in the past 24 hours.

And finally I would like to end up with a file or spreadsheet or something where every day a new line is appended in the following format:
<consumed pwr meter 1><cost consumed power meter 1><consumed pwr meter 2><cost consumed power meter 2>

Is that possible in an automation, or should I make a script or something? Once I have all those values, I can figure out a way to use ifttt to write it to a google sheet, but I do not know how to keep track of those readings for 24 hours…

Any tips in the right direction is welcome!

3 Likes

I’m pretty sure you posted to this reddit. I never got around to responding to you a second time, my bad. Hello again! Anyways, this level of automation may push you towards appdaemon. Especially if you want to create a text file with the information appended in it. I don’t believe HA can create text files without using python, but I could be wrong because HA has so many components in it.

To store values, you will need a sensor to store the value, this will need to be tested because I have no idea what the polling frequency would be:

- platform: template
  sensors:
    mysensor1:
      value_template: >
        {% if now().hour == 23 and now().minute == 59 %}
          {{ states.<SENSOR>.attributes.<Whateverattribute> | float }}

Then your automation would just happen at 00:00:00 and you perform your math.

This will not log the information to a file. I’m 60% sure you’ll need to make a component or use appdaemon.

1 Like

Hi Petro,

Must be someone else on Reddit as I don’t have an account there I think. Although I have been walking around with this for a while, maybe I forgot something? :slight_smile:

Anyway, thanks for the reply! I never thought about making a template sensor, but something like that sound like a great idea, thanks for sharing!

I am keeping a manual google sheet right now, but with the ifttt component I could use the webhook and google sheet applets in ifttt to add a row to a spreadsheet… I think :slight_smile:

I’ll give it a go :slight_smile: Thanks again!

Quick update:

I have managed to get it working by performing an automation at midnight that reads the meter readings from an input_number that was filled with the then-current meter readings, 24 hours ago. Subtract the most current reading with the 24-h old reading and voila. Final step is fill the input_number value with the new current meter reading, so that can be used 24 hours later.

Additionally, I was able to setup an influx database with grafana connected to it and use the sensor readings as input to graphing that same stuff:

It is in dutch, so quick explain: top line/bar graph is electricity usage in the past hour. The two gauges top-right are current (most recent readings) electricity usage and gas usage (in m3/h).

Below that (the middle graph) is showing past 24h gas and electricity usage.

Bottom left: electricity used per day, past 7 days. The two colors represent the two different tariff meters.
Bottom right: gas used per day (bars in m3), with the line graph for the outside temperature and min/max that day.

7 Likes

that’s pretty awesome. What devices are you using to view your power consumption?

1 Like

None, currently, I am only getting data from the smart meter, which has a so-called “p1 port”, for which I bought a cable with rj-11 on one side (the p1 port) and usb on the other and plug that into the home assistant machine (an odroid-c2 in my case).

The dsmr component makes it possible to get that data available as sensors in HASS.

I have a few zwave devices that provide power info, but that it not in there yet. Additionally, I have a Sonoff Pow in a drawer somewhere that should be able to do something similar, but not operational yet.

@aetjansen could you share your automation and configuration for this power-by-day-sensor? I just want do the same with my Homematic power meter. Thanks!

I would love to, but I am just in the middle of updating it, and I have made a mistake somewhere in the config, but cannot figure out whats wrong. When I fixed it, I will give you full details.

In case you (or anyone) would like to help figure out what I messed up :slight_smile: The repo is at: https://github.com/aetjansen/HomeAssistant-Config/ and the travis build log is at: https://travis-ci.org/aetjansen/HomeAssistant-Config I updated the energy.yaml package

1 Like

Hi Arno,
Thanx for sharing your respository. Must say I’m impressed by your configuration and solutions.
Did you managed to fix the misstake?

I want to implement your smart meter settings :slight_smile:
Thanx again!

1 Like

Hi @rtenklooster, thanks!

No, I did not manage to get the issues fixed. In fact, I started to have more and more issues with my HA setup, so I have started to move over to an Intel NUC and that works really nicely. But, I have also moved my personal setup to gitlab, hence no recent commits to the github repo. Then, life happened, so no HA updates for a few months, and now I am getting back into it.

I am planning to redo the whole power meter thing in Nodered soon and if there is interest I am happy to share that!

@aetjansen, @rtenklooster: Any of you can help me with the data I’m getting from my smart meter?

I have already in place a system which reads the P1 DSMR (2.2) from my meter and I get all the info in a MariaDB10 database on the same machine (a Synology NAS) where HASS is running.
Do you see a simple way to get this data imported?

I know this might be a noob question but frankly I do not even know where to start from.
Any advice would be really appreciated, even RTFM but please give me some pointers.

Thank you
Mircea

I would use node-red.
Query the last measurement from the MariaDB, publish it over MQTT.
In HomeAssistant add a MQTT sensor.

This way it would be easy to intergrate your measurements into HA.

2 Likes

Not sure if I fully understand you, but I assume you want the data that you are getting in the database, also in home assistant?

if so, would it not just suffice to add the DSMR component to your HASS config. Since, both your MarioDB and HASS are running on the same machine, I assume you have a cable from the P1 port to the synology nas USB right? In which case I would just add the DSMR component.

Mind you, if you do, you have the data both in the MariaDB as well as the database you use with HASS.

Does that answer your question?

Thats a nice way to do things, prevents you from storing the data twice. But, does require Nodered to be setup. Not sure if @dmircea has that done yet. But if you are running hassio, it is just as simple as adding the nodered add-on. Having said that, I did run hassio with nodered for a while, and sometimes nodered would loose connection for a while (up to a few hours) after a restart of home assistant. But those issues may have been gone by now. (I am now running it in a docker-compose stack)

Guys, really appreciate the feedback given! Many thanks!
These are great starters for me as the documentation is pretty wide and frankly I had no clue even where to start.

I would like to explain a bit better the setup that I have:

An arduino UNO (with an Ethernet shield) is connected with an inverter circuit to the smart meter (on the P1 port). Arduino is streaming the data every minute towards the Synology NAS by calling a PHP which is saving the data in the MariaDB.
At the moment of the inception there was nothing else but the database and I was still assessing what would be the best way to use the data later.
To be honest the first thing I found of a simple search on the Internet was to use domoticz and push the data using JSON towards the internal DB. That gave me some graphs straight out of the box without too much effort.

But looking more and digging into it I learned that HASS is a better system hence the attempt to switch and see how I can get something similar.
I have to admit that I have seen the dashboard in this thread and that really caught my attention hence I will try to replicate that in my setup.

For now I would like to keep MariaDB in the middle even if it is causing some duplication (I’m having some 20MB of data each year, which is not a big issue). At some point I can probably decide to change the PHP script and call directly towards HASS without storing it in MariaDB.
As for now I’m not familiar with HASS I do not even know if I can access the internal database and do some other data manipulation (at my will) and how exactly the aging works etc etc.

Here is a picture of my setup:

On the original topic: I have gotten a few more questions from friends on how to make that dashboard. I have moved everything over to an Intel NUC and recreated the dashboard and decided to do a little write up on it as a first post on my new site: https://www.clevercrib.net/visualise-energy-consumption-with-grafana/

I hope it helps a few people (@nicx ?) and please let me know if anything needs clarification :smiley: or what you would like to see next

2 Likes

@dmircea then the solution @rtenklooster suggested is your best bet I think. Are you running hassio? If so, you need to install the nodered addon to get going, assuming it is supporting mariaDB. I will look into it a bit more later this week, can’t do much from where I am now though


My setup: Just a simple ESP8266 as serial bridge.

and @rtenklooster, you wrote a sketch to read serial data and whenever something is received post it as mqtt topic or something?