Predict the amount of energy the house will use for the remainder of the day

What I have achieved so far is set up a template sensor using sensors from my PV/battery inverter which keeps track of total house energy usage. (Total increasing)
Basically this template sensor gives me same value that the built-in Energy Dashboard shows in its Energy Distribution flowchart for “Home”

I thought I would be able use the history of this sensor to work this out, but it looks like it can’t get detailed enough.

I want to predict the amount of energy the house will use for the remainder of the day (or until sunset or a given time) I would also like to calculate this seperately for each day of the week.

For example if the current day is Monday, I want to see an average of energy used between current time and end of day on previous Mondays

But haven’t even been able to work out a simpler daily average total for previous Mondays.

I have been trying various things to acheive something like this all day and I keep running into dead ends

history_stats doesnt seem to allow detailed enough filtering

I thought maybe I can export data to google sheets, work with the data there and bring it back. But the bringing it back part doesnt seem doable.

maybe i could make a sensor that only counts energy usage on mondays. but i dont know if that’s possible

appreciate any ideas or different ways to approach this, thanks!

You can use the sql sensor to query the stats table directly. This maybe a better option.

This might be a useful starting point for that, although it doesn’t delve into long-term statistics:

Reference for the long-term stats: