Created my longest Template Sensor Yet... a Water Bill Estimator

Configuration
1

I figured I’d share it for others to ogle and appreciate, and possibly critique or be inspired by. I used my city’s breakdown of how they calculate my bill… and made my own bill estimator.

My water bill is calculated in blocks, there are base charges that are the same every month like the stormwater charge and base-fees for water/sewer, there is static rate charges like the sewer charges, then there’s the actual water usage that’s based on blocks. So the first 6000 gallons are charged at .536 cents a gallon, then gallon 6001-9000 at .697 cents per gallon, and all gallons over 9000 are charged at .858 cents per gallon.

So I set up some variables so I can easily tweak the block break points, or the rates in the future, and then just made three different formulas, and used some if statements to use the particular formula needed for the amount of water I’m projected to use.

    {% set blocks = {
      "first": 6000,
      "second": 9000
    } %}
    {% set rates = {
      "first": 0.00536,
      "second": 0.00697,
      "third": 0.00858,
      "sewer": 0.00562,
      "waterbase": 4.04,
      "sewerbase": 3.39,
      "stormwater": 3.1
    } %}
    
    {% if ((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30) < blocks.first %}
    {{ ((rates.waterbase + rates.sewerbase + rates.stormwater)+(((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30) * rates.sewer) + ((((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30)*rates.first))) | round(2) }}
    {% elif ((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30) < blocks.second %}
    {{ ((rates.waterbase + rates.sewerbase + rates.stormwater)+(((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30) * rates.sewer) + ((blocks.first * rates.first)+((((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30)-6000)*rates.second))) | round(2) }}
    {% elif ((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30) > blocks.second %}
    {{ ((rates.waterbase + rates.sewerbase + rates.stormwater)+(((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30) * rates.sewer) + ((blocks.second * rates.second)+(blocks.first * rates.first)+((((states('sensor.my_house_water_monitor_monthly_water') | float / utcnow().day)*30)-9000)*rates.third))) | round(2) }}
    {% else %}
    ERROR 
    {% endif %}

And then I removed all the line breaks and threw it into my config… and it’s been working well so far!

Just sharing for sharing’s sake. I couldn’t find an example of this anywhere else, and spent over an hour getting it to work, so I figured I’d share it with others. If you want to use this, just replace sensor.my_house_water_monitor_monthly_water with a sensor you have that gives how many gallons of water you’ve used so far this month, and set the variables in the begining of the template with whatever your water provider charges.

2 Likes
2

Hi!!, First of all thanks for this template sensor, i’ve been looking this for long time and i ended up finding it by chance hehehe. I tried to adapt it to my Bill because we use cubic meters not Gallons and also costs and charges are little bit different, bills are bimonthly, but doesn’t look to calculate right, do you mind to help me to make it work?

image
image1608×744 120 KB

I was doing some test in the developer section, using variable meter: as my sensor to do some tests, and you will see that I divided by 1000 because my sensor is in liters so 1 m3 is 1 liter. Here it is my template test:

    {% set blocks = {
      "first": 12000,
      "second": 18000,
      "tsecond": 6000,
      "third":  22000,
      "tthird":  4000
    } %}
    {% set rates = {
      "first": 0.5941,
      "second": 0.4936,
      "tsecond": 1.1883,
      "third": 0.4936,
      "tthird": 1.1370,
      "waterbase": 14.72,
      "sewerbase": 9.69,
      "meter": 18000,
    } %}
    
    {% if ((rates.meter | float / utcnow().day)*60) < blocks.first %}
    {{ (((rates.waterbase + rates.sewerbase + ((((rates.meter | float / utcnow().day)*60)*rates.first)))) / 1000) | round(2) }}
    {% elif ((rates.meter | float / utcnow().day)*60) < blocks.second %}
    {{ (((rates.waterbase + rates.sewerbase + ((blocks.first * rates.first)+((((rates.meter | float / utcnow().day)*60)-12000)*rates.second)))) / 1000) | round(2) }}
    {% elif ((rates.meter | float / utcnow().day)*60) > blocks.second %}
    {{ (((rates.waterbase + rates.sewerbase) + (((blocks.tsecond * rates.tsecond)+(blocks.second * rates.second)+(blocks.first * rates.first)+((((rates.meter | float / utcnow().day)*60)-18000)*rates.second)))) / 1000) | round(2) }}
    {% else %}
    ERROR 
    {% endif %}
3

I reply my self , i had to create a counter to simulate the date, because my bill is every 2 month, so the counter starts in 1 til 61 then the automation is trigged by 61 and reset the counter.

            {% set blocks = {
              "first": 12000,
              "second": 18000,
              "tsecond": 6000,
              "third":  22000,
              "tthird":  4000
            } %}
            {% set rates = {
              "first": 0.5941,
              "second": 0.4936,
              "tsecond": 1.1883,
              "third": 1.1370,
              "tthird": 1.7548,
              "waterbase": 14.72,
              "sewerbase": 9.69,
              "sewer": 0
            } %}


            {% if ((states('sensor.bimonthly_water') | float / states('counter.bimonth')| float)*60) < blocks.first %}
            {{ (((((((blocks.first * rates.first)+((((states('sensor.bimonthly_water') | float / states('counter.bimonth')| float)*60)-12000)*rates.first))))/1000) + (rates.waterbase))* 1.10 + (rates.sewerbase)) | round(2) }}
            {% elif blocks.first < ((states('sensor.bimonthly_water') | float / states('counter.bimonth')| float)*60) <= 19000 %}
            {{ (((((((blocks.first * rates.first)+(blocks.tsecond * rates.tsecond)+(blocks.second * rates.second)+((((states('sensor.bimonthly_water') | float / states('counter.bimonth')| float)*60)-18000)*rates.tsecond))))/1000) + (rates.waterbase))* 1.10 + (rates.sewerbase)) | round(2) }}
            {% elif ((states('sensor.bimonthly_water') | float / states('counter.bimonth')| float)*60) > 19000 %}
            {{ (((((((blocks.first * rates.first)+(blocks.tsecond * rates.tsecond)+(blocks.second * rates.second)+(blocks.tthird* rates.third)+(blocks.tthird * rates.tthird)+((((states('sensor.bimonthly_water') | float / states('counter.bimonth')| float)*60)-22000)*rates.tthird))))/1000) + (rates.waterbase))* 1.10 + (rates.sewerbase)) | round(2) }}
            {% else %}
            ERROR 
            {% endif %}
1 Like
4

Awesome! Glad that worked for you!