Hi all,
Can someone get me started on the following:
I have the DSMR binding running on my openhab 3 to get the meter readings from my digital electricity and gas meter.
I would like to export these values to an excel file daily at a certain time.
I have created a page where these values are visible.
The values are stored in the events.log files so I suspect my question is possible.
Only I have no idea how to get this done 
Thanks in advance!
Michael
What do you hope to do with it once itâs in Excel?
I ask because in all likelihood what ever you want to use Excel to do might be easier to do by operating on the raw data itself than it will be convert that data to something Excel can read.
At a high level, you can configure the event bus logger to log out in CSV format which Excel can read, but beware that you will get all events that occur in OH, not just that one Item you care about.
You can create a rule to write out to a CSV file when ever this Item changes or at the end of the day pull all the records for the day from persistence and write them to a CSV file.
You can use the API Explorer to query for all the records in persistence for the past day and load that into Excel directly (use the Data menu in Excel).
But depending on what you want to do with the spreadsheet might be easier to just do in a rule.
I currently have an Excel sheet where I keep track of my meter readings.
In that sheet I have incorporated some formulas to calculate averages so I can see what my consumption is per day/month/year.
Very handy to have everything in 1 overview.
But now I have to enter all the data manually every day.
And since the data is also just saved via openhab I thought I would get it from there and link it with excel.
Why not have OH present this information to you?
Every value retreived by the DSMR is saved to a database. Persistence offers averageSince and similar type queries and summation functions on that data. And OH has built in charting. It will potentially require some new Items and a couple of relatively simple rules but then it all just takes care of itself with no manual steps what-so-ever.
I know of no way that youâll be able to import the data into an existing Excel spreadsheet that isnât going to be as much work as manually entering the data. Youâll have to copy/paste or import or something like that every time manually. As far as I know Excel isnât that dynamic about data sources without getting into some heavy macros and the like.
If you use soemthing like MariaDB or MySQL to save the data from OH you might be able to use that Data â Import form Database to pull the raw readings from the database into a sheet.
There have been a ton of enhancements to the persistence features of OH in 4.2 but even with whatâs in OH 3 this should be possible to do in OH itself.
I thought it would be simpler and possible to run the export from openhab to Excel automatically.
Not so
I know there are many possibilities with openhab, but donât know enough about this.
Can you help me on my way to realize this in openhab?
Not with the little bit of information youâve provided so far. âsome forumlas to calculate averagesâ isnât detailed enough to give you any concrete advice. What are the forumlas? Averages of what data ranges? How often is it calculated/updated?
If you upgrade to OH 4 you might be able to just use Actual energy consumption [4.0.0.0;4.9.9.9] and Historical energy consumption [4.0.0.0;4.9.9.9] not need to do anything else.
If you stay on OH 3 Iâm afraid youâll have to build the equivalent of these rules yourself. I can help somewhat but itâs been years since Iâve writting rules for OH 3 at this point so it wonât be easy.
Below is an example of how my excel is structured.
I enter the meter reading every day.
It is then calculated relative to the day before how much was consumed that day itself. This for the day and night consumption separately and a total consumption for that day.
At the bottom of the table the consumptions are then added together and an average calculated based on the number of days that month.
Also, a graph is made of the consumption per month.
And I have a total overview of the whole year.
Updating to openhab 4 I donât really feel up to it.
Updating to 3 already took a lot of work to get everything working again
Check this thread for an idea via Python:
For tha vast majority of users the upgrade to OH 4 was relatively painless and easy with very few breaking changes that you need to deal with yourself. Iâm willing to be itâll be less work that itâll be to code this stuff up either to extract the data to make it available in Excel or to write rules to do it in OH itself.
If it isnât a smooth upgrade thatâs what backups are for.
All your requirements are handle by the rule templates I posted above.
To implement this yourself you need:
var meterItem = items.getItem('MeterReading');
var totalDay= items.getItem('TotalDay_Usage');
// ... repeat for all the relevant Items
var delta = event.itemState.floatValue - event.oldItemState.floatValue;
if(time.toZDT().isBetween("07:00", "07:00") day.postUpdate(day.numericState + delta);
else totalNight.postUpdate(night.numericState + delta);
totalDay.postUpdate(totalDay.numericState + delta);
currMonth.postUpdate(currMonth.numericState + delta);
var meterItem = items.getItem('MeterReading');
var totalDay= items.getItem('TotalDay_Usage');
// ... repeat for all the relevant Items
// Reset the daily values
totalDay.postUpdate(0);
day.postUpdate(0);
night.postUpdate(0);
// Reset the curr month values
if(time.toZDT().dayOfMonth() == 1) {
items.getItem('Months_Usage').postUpdate(currMonth.state);
currMonth.postUpdate(0);
}
dailyAverage.postUpdate(totalDay.history.averageSince(time.toZDT().withDayOfMonth('1'); // average for the month
// .. and so on
Charting just involes choosing the right Item and choosing the duration.
Ultimately, the way it works is every time the reading changes update the variaous running totals. Then at the start of the day reset the approrpiate Items back to zero. Use persistence to calculate averages at this time.
There are all sorts of variations that can be taken about how and when the averages are taken, when data points are persisted, etc.
But once you have the Items representing all of the values you care about and a rule to calcualte them as you go, everything else falls into place for charting.
As an excel geek, I wonât go into great detail, but like the code above, I get a midnight reading of KWH from all my meters (which basically cover everything), then use mysql DB (running in a docker container) to capture. Then I use the import data capability of excel where I do whatever (like compare with the utility bill). With one click all the data is imported. Have 2.5+ years of daily data at this point. Iâm sure there are other options, but after some early struggles this works for me.
Screen shot of the component compare to the house meter.
This wonât mean much, but I do custom graphs too.
Thank you all for the responses.
I am going to check out all your different solutions.
Thanks for the quick replies!
Although the DSMR binding is build just fine, I moved away from it as primary source for getting the data out the meter(s).
I made a powershell script that runs that between P1 and a mySQL server. at the same time, it pushes some relevant data to OH (both 4.1.1 and 2.5.x).
I started this with zero knowledge of SQL, but today, I can export to excel tons of (relevant !) data from my meter to excel (duh !) to work with.
If intrested, send a message.