Extract statistics to help size home battery

Im helping a friend. We installed HA a few months ago, and we are monitoring solar panel production and most power consumption - no smart meter yet, but tons of smart sockets, and most of his consumption is the heat pump. By some time next year, he will need to invest in a home battery as the extremely generous regime of a utility meter turning backwards when he produces more than he consumes, will end, and feed-in tariffs are plummeting and will probably go towards zero eventually. We want to figure out /simulate how big the battery ought to be.

I have begun making a simulated battery, using template sensors, whenever there is excess production “charging” the simulated battery etc. Then I found someone already made an integration for that; but that isnt the right approach, because I would have to guess a battery size, and would not have any meaningful data on how other battery sizes would compare. The battery sim doesnt use historical data either.

Ideally Id end up with a system (an excel sheet would be fine) where I can input the battery size, and based on historical per hour solar production and consumption data, it will calculate how much energy could have been stored in the battery and later used vs how much power would have been pulled from the net.

The data is (or will be) all there, but Im not sure where to begin :). Any thoughts, pointers, hints… has anyone done something like that?

What sort of hardware does he have to handle conversion to and from stored energy? By this I mean, is he using a separate inverter and charge controller, or an integrated unit? if the latter, they generally include a minimum recommended configuration, which for my Growatt SPF5000ES (US model) is a minimum of 200AH, though I would suggest somewhere around 600AH minimum.

I think you misread my post. He doesnt have a battery yet. He doesnt need it (yet), because under the current rules, he can use the grid as an infinite battery. I want to figure out how big the battery will have to be to maximize ROI once those rules change.

Hi, I just had the same question and used my PostgreSQL database to answer it:

Simulate home battery from Home Assistant data (github.com)

I was very disappointed that even a DIY solution with a Victron Multiplus inverter and cells+case from China (for 4200 Euro in total for 14.4 kWh) will only redeem after about 9 years.
So I scrapped the idea for now and will check again once batteries get cheaper or power from grid gets more expensive.

That looks interesting… excuse my ignorance, but could I just paste that in phpmyadmin (using mariadb) after making the required changes? Or do I really need postgressSQL?

As for the actual numbers; our electricity is already quite a bit more expensive than yours, but with our current pricing, I suspect at least half the “profit” would come from storing off-peak electricity and using it during peak hours. Currently off-peak is at night and in the weekend; Im sure that will change, as there will be increasingly excess during the day rather than the night, but I also think the price difference will go up, and we will get almost free electricity whenever there is a lot of sun and wind, and (even more) expensive electricity when there is neither.

But yeah, running the batterysim for a few weeks now, ROI doesnt look as promising as I had first imagined. But there are so many new energy storage solutions coming to the market, I hope that will change quickly. Salt water batteries in particular.

Sure, you can use it with any database, you just need to adapt the SQL to fit for MariaDB.
The start of an anonymous block is probably different (BEGIN NOT ATOMIC if I remember correctly) and the column types will be different (timestamp, numeric vs datetime, double).

Maybe someone proficient in MariaDB/MySQL can assist, I don’t have a MariaDB with Home Assistant data in it handy right now.

I heard about energy prices changing during the day in other countries, in Germany this is not the case (yet). I pay 0.3085 Euro per kWh 24 hours a day.
My SQL does not consider this, so you have to adapt his as well, maybe via looking at the hours of each record and assign a respective price to it.

Variable tariffs indeed make this a whole lot more complicated (but potentially also more lucrative). Im noticing that now with the simulator. For instance, I can charge the battery at night using off peak tariff, and then use that in the morning to reduce peak tariff consumption. Even using an assumed 90% conversion efficiency, thats very helpful in winter or on rainy days (price difference is like 40%), however, if I dont use all that energy in the morning and its a sunny day, I may not drain the battery before my solar production exceeds my consumption, and thus have paid-for energy in my battery and no capacity left to store my “free” energy.

I think simulating this will take more than a sql query. And thats ignoring our “capacity tariff”, which is a fee we pay for peak consumption (based on 15 min peak every month). It doesnt make too much of a difference on less than 5KW, but still, a battery could help flatten those peaks, by providing energy when consumption is abnormally high, but that again needs a strategy. Im unsure if or how commercial home batteries do all this.

I seriously think this is something where AI could be helpful. Or a complicated algorithm that looks at historical data, weather prediction, EV charge state, maybe even takes next day agendas in to account.

1 Like

Would you be willing on write a post on how to use your SQL query? I’m using the default database for Home Assistant but I’m inexperienced on how to run this query on the database.