PV forecast: purely with weather and historical data

Table of Contents

[Deutsch]

➦ Home Assistant ➨ Home Assistant News / planned features

Most PV forecasts are based on standard weather models. However, these do not know whether a tree is shading your panels or how efficient your system really is in diffuse light. The solution: Historical comparison. We look at how much electricity your system has delivered in the past under similar weather conditions: cloud cover, UV index, temperature and precipitation are compared with the forecast for today or the coming days. I have created an HACS extension for a quick setup. Alternatively, the forecast also works with Home Assistant built-in tools: just the standard weather forecast and an SQL sensor with value template. This SQL-only variant remains useful as a minimal version, while the HACS integration contains the current extended model. Optionally, a markdown card can be created to understand the calculation:

The problem with traditional PV forecasts

External PV forecasting services (Forecast.Solar, Solcast & Co.) estimate the yield on the basis of weather data and configured system output. This works for rough planning, but is often too inaccurate for daily operation:

The actual shading caused by trees, buildings or snow is not taken into account
The effective output of the system (ageing, soiling) is unknown
Local weather phenomena such as ground fog or rapidly changing cloud cover are incorrectly assessed

Better: The knowledge about the yield, cloud cover and other weather conditions is already in the Home Assistant database - you just have to read it out.

HACS integration

Software	Ha_pv_history_forecast
GitHub	https://github.com/LiBe-net/ha_pv_history_forecast
current version	0.3.3
found	2026-07-17

With PV History Forecast I have created a custom integration for Home Assistant (installable via HACS) that directly accesses the SQLite database of Home Assistant. Since version 0.3 the model also includes precipitation and temperature and can automatically retune its forecast parameters from historical data. The SQL query used with it looks for historical days with similar cloud cover, UV index, temperature and precipitation for the whole day and the rest of the day.

These comparison days are then:

Seasonally scaled - a summer day is normalized to the current autumn day, taking into account day length and sun position (astronomically correct formula by latitude)
Dynamically UV-weighted - the distance metric combines cloud cover and UV index distance; in the case of heavy cloud cover (≥60 %), the UV weighting automatically increases from 30 % to up to 70 % because the UV index is then the more precise discriminator
Recency-weighted - days younger than 30 days receive up to 30% more weighting (current plant behavior counts more)
Top-N weighted - the most similar historical days are weighted by weather distance, season and recency
Temperature and precipitation matching - version 0.3 also uses outdoor temperature and precipitation where available
Retune by backtest - optional nightly/on-demand optimization tests forecast parameters against historical days and keeps better weights

The result: A residual forecast for today, based on real data from your own system and optionally adjusted from historical forecast quality.

Generated sensors

After installation and configuration (default prefix: `pv_hist`), the following sensors are available:

Sensor	Meaning
sensor.pv_hist_remaining_today	Expected remaining yield today in kWh (main sensor)
sensor.pv_hist_remaining_today_min	Pessimistic remaining yield today
sensor.pv_hist_remaining_today_max	Optimistic remaining yield today
sensor.pv_hist_tomorrow	Weighted forecast for the total yield tomorrow in kWh
sensor.pv_hist_weather_forecast	Internal auxiliary sensor: hourly weather forecast as JSON
sensor.pv_hist_cloud_coverage	Auto-cloud sensor: mirrors cloud_coverage of the weather entity (if no external sensor selected); usable from day 1, LTS history (>10 days) builds up automatically
sensor.pv_hist_uv	Auto UV sensor: reflects the UV index of the weather entity (if no external UV sensor is selected); builds LTS history automatically
sensor.pv_hist_temperature	Auto temperature sensor: mirrors the weather temperature and builds LTS history automatically
sensor.pv_hist_precipitation	Auto precipitation sensor: mirrors precipitation as a percentage scale for the forecast model

Version 0.3 deliberately exposes fewer helper entities than older releases. The former cloud, UV and method forecast helper sensors are no longer created; their details are shown inside the ready-made Markdown attributes of the main sensor instead.

Removed helper sensors from older versions: `sensor.pv_hist_cloud_remaining_today`, `sensor.pv_hist_cloud_tomorrow`, `sensor.pv_hist_method_remaining_today`, `sensor.pv_hist_method_tomorrow`, `sensor.pv_hist_uv_remaining_today`, `sensor.pv_hist_uv_tomorrow`.

The main sensor `sensor.pv_hist_remaining_today` also contains the attributes `lovelace_card_remaining_today` and `lovelace_card_tomorrow` - ready-rendered Markdown cards that can be integrated directly into the dashboard.

Simplified function:

weather_history: Reads historical cloud cover, UV, temperature and precipitation values from the HA statistics (LTS)
matching_days: Searches for days on which the remaining weather profile is similar to the current forecast
final_data: Calculates the scaled yield for each comparison day and returns the result as JSON

The scaling between the comparison day and today's day is carried out using an astronomically correct day length formula:

dl = 24/π · arccos(−tan(φ) · tan(δ))

δ = −0.4093 · cos(2π · (day + 10) / 365)

In addition, there is a seasonal snow detection (December-February): If yesterday's yield was conspicuously low given the available solar potential, a snow factor is applied.

Installation

Add via this link:

Or alternatively installed as a custom repository: Open HACS → ⋮ → Add custom repository

URL:

LiBe-net/ha_pv_history_forecast
In HACS: Search for and install "PV History Forecast"
Restart Home Assistant
Settings → Devices & Services → Integrations → Add "PV History Forecast"

Configuration

Configuration is carried out completely via the HA user interface in two steps.

Step 1 - Prefix & database

Field	Default	Description
Sensor prefix	pv_hist	Basis for all sensor names
Database path	(empty)	Leave empty = standard HA database

Step 2 - Sensors

Field	Required	Description
Weather entity	Yes	`weather.*` entity, e.g. `weather.forecast_home`
PV energy sensors	Yes	Sensor with `device_class: energy` and active statistics; Wh is automatically converted to kWh
History days	Default: 90	Number of historical days for comparison
Retune	Optional	Automatic optimization of the forecast parameters from historical data

Tip: The dropdowns in the configuration step only show suitable sensors - PV sensors are filtered by `device_class: energy` and active statistics.

The Lovelace Dashboard

The finished markdown cards are available as an attribute of the main sensor and show

The calculated remaining forecast in kWh as a headline
The current cloud, UV, temperature and precipitation assumptions and the calculation details
A table of historical comparison days with weather values, yield and percentage influence weight

Integration into a Lovelace Markdown-Card today:

type: markdown
content: |
  {{ state_attr('sensor.pv_hist_remaining_today', 'lovelace_card_remaining_today') }}

tomorrow:

type: markdown
content: |
  {{ state_attr('sensor.pv_hist_remaining_today', 'lovelace_card_tomorrow') }}

Requirements

Home Assistant 2024.1.0 or newer
Standard database (sqlite)
HACS installed and set up
PV sensor: state_class: total_increasing, unit kWh or Wh, active statistics
Weather entity with cloud_coverage in the forecast response (e.g. weather.forecast_home)

Without HACS: purely with SQL integration

Before I created the HACS integration on Github, I implemented the prediction purely using the SQL integration. This section remains as a minimal version and does not include all current 0.3 features such as temperature, precipitation and automatic retune:

Requirements Sensor for weather forecast

Sensor name normally: weather.forecast_home

Save forecast data

Configuration: configuration.yaml: Save weather forecast to a sensor: weather.forecast_hourly

[+]

template:
  - trigger:
      - platform: time_pattern
        minutes: /15
    action:
      - service: weather.get_forecasts
        data:
          type: hourly
        target:
          entity_id: weather.forecast_home
        response_variable: hourly
    sensor:
      - name: weather.forecast_hourly
        unique_id: weather.forecast_hourly
        state: "{{ now().isoformat() }}"
        attributes:
          forecast:  "{{ hourly['weather.forecast_home'].forecast }}"

Optional: Use more than 10 days for the calculation.

For more than 10 days we need an auxiliary sensor so that the cloud cover values are written to the long-term statistics.

Sensor name: weather.cloud_coverage

And for the UV index:

Sensor name: weather.uv_index

{{ state_attr("weather.home",'uv_index') | float}}

SQL Sensor with Value Template

The following source sensors are required for the SQL sensor:

weather.forecast_home – liefert historische Bewölkungsgrade (cloud_coverage) und UV-Index direkt aus den State-Attributen
sensor.weather_forecast_hourly – Stundenvorhersage (Anlage in configuration.yaml wie oben beschrieben)
sensor.pv_panels_energy – Gesamtzähler für den PV-Ertrag (state_class: total_increasing, Einheit kWh)

The three sensor names are adjusted in the SQL's vars-CTE. The query follows the logic of the HACS integration and returns the same JSON format: cloud cover, UV index, and PV yields per comparison day.

[+]

WITH vars AS (
    SELECT 
        'weather.forecast_home' as sensor_clouds,   -- Weather Entity direkt: cloud_coverage aus state_attributes
        'weather.forecast_home' as sensor_uv,   -- Weather Entity direkt:  uv_index aus state_attributes
        'sensor.pv_panels_energy' as sensor_pv,
        'sensor.weather_forecast_hourly' as sensor_forecast,
        -- Berechnet den Versatz zwischen Lokalzeit und UTC (z.B. '+3600 seconds')
        -- Wird genutzt, um den Datumswechsel (00:00 Uhr) lokal zu triggern
        (strftime('%s', 'now', 'localtime') - strftime('%s', 'now')) || ' seconds' as offset
),

ids AS (
    /* Holt alle benötigten internen IDs für Statistiken und States aus der HA-Datenbank */
    SELECT 
        (SELECT id FROM statistics_meta WHERE statistic_id = (SELECT sensor_clouds FROM vars)) as w_id_stats,
        (SELECT metadata_id FROM states_meta WHERE entity_id = (SELECT sensor_clouds FROM vars)) as w_id_states,
        (SELECT id FROM statistics_meta WHERE statistic_id = (SELECT sensor_uv FROM vars)) as uv_id_stats,
        (SELECT metadata_id FROM states_meta WHERE entity_id = (SELECT sensor_uv FROM vars)) as uv_id_states,
        (SELECT id FROM statistics_meta WHERE statistic_id = (SELECT sensor_pv FROM vars) LIMIT 1) as p_id,
        (SELECT metadata_id FROM states_meta WHERE entity_id = (SELECT sensor_pv FROM vars) LIMIT 1) as p_id_states,
        (SELECT metadata_id FROM states_meta WHERE entity_id = (SELECT sensor_forecast FROM vars) LIMIT 1) as f_id,
        (SELECT metadata_id FROM states_meta WHERE entity_id = 'sun.sun') as sun_id
),

pv_activity AS (
    /* Sonnenaufgang = erster 'above_horizon'-Eintrag gestern (UTC-Epoche, direkt korrekt)       */
    /* Sonnenuntergang = erster 'below_horizon'-Eintrag NACH dem Sonnenaufgang gestern           */
    /* sun_start/sun_end = UTC HH:MM  → verwendet für BETWEEN mit UTC-Forecast-Datetimes (+00:00) */
    /* sun_start_local/sun_end_local = lokal HH:MM → nur für Phasenerkennung (vor/nach Auf/Unt)  */
    SELECT
        COALESCE((
            SELECT strftime('%H:%M', last_updated_ts, 'unixepoch')
            FROM states
            WHERE metadata_id = (SELECT sun_id FROM ids)
              AND date(last_updated_ts, 'unixepoch', (SELECT offset FROM vars)) = date('now', (SELECT offset FROM vars), '-1 day')
              AND state = 'above_horizon'
            ORDER BY last_updated_ts ASC LIMIT 1
        ), '05:30') as sun_start,
        COALESCE((
            SELECT strftime('%H:%M', last_updated_ts, 'unixepoch')
            FROM states
            WHERE metadata_id = (SELECT sun_id FROM ids)
              AND state = 'below_horizon'
              AND last_updated_ts > (
                  SELECT last_updated_ts FROM states
                  WHERE metadata_id = (SELECT sun_id FROM ids)
                    AND date(last_updated_ts, 'unixepoch', (SELECT offset FROM vars)) = date('now', (SELECT offset FROM vars), '-1 day')
                    AND state = 'above_horizon'
                  ORDER BY last_updated_ts ASC LIMIT 1
              )
            ORDER BY last_updated_ts ASC LIMIT 1
        ), '17:30') as sun_end,
        COALESCE((
            SELECT strftime('%H:%M', last_updated_ts, 'unixepoch', (SELECT offset FROM vars))
            FROM states
            WHERE metadata_id = (SELECT sun_id FROM ids)
              AND date(last_updated_ts, 'unixepoch', (SELECT offset FROM vars)) = date('now', (SELECT offset FROM vars), '-1 day')
              AND state = 'above_horizon'
            ORDER BY last_updated_ts ASC LIMIT 1
        ), '06:30') as sun_start_local,
        COALESCE((
            SELECT strftime('%H:%M', last_updated_ts, 'unixepoch', (SELECT offset FROM vars))
            FROM states
            WHERE metadata_id = (SELECT sun_id FROM ids)
              AND state = 'below_horizon'
              AND last_updated_ts > (
                  SELECT last_updated_ts FROM states
                  WHERE metadata_id = (SELECT sun_id FROM ids)
                    AND date(last_updated_ts, 'unixepoch', (SELECT offset FROM vars)) = date('now', (SELECT offset FROM vars), '-1 day')
                    AND state = 'above_horizon'
                  ORDER BY last_updated_ts ASC LIMIT 1
              )
            ORDER BY last_updated_ts ASC LIMIT 1
        ), '18:30') as sun_end_local
    FROM ids
),

forecast_val AS (
    /* Berechnet die durchschnittliche Bewölkung + UV-Index für den verbleibenden Teil des aktuellen Tages */
    SELECT COALESCE(
        (SELECT AVG(CAST(json_extract(f.value, '$.cloud_coverage') AS FLOAT)) 
         FROM states s 
         JOIN state_attributes a ON s.attributes_id = a.attributes_id, 
         json_each(a.shared_attrs, '$.forecast') f 
         WHERE s.metadata_id = (SELECT f_id FROM ids) 
           AND s.last_updated_ts = (SELECT MAX(last_updated_ts) FROM states WHERE metadata_id = (SELECT f_id FROM ids)) 
           -- Match forecast date against local "today" (via UTC offset)
           AND substr(json_extract(f.value, '$.datetime'), 1, 10) = date('now', (SELECT offset FROM vars))
           AND substr(json_extract(f.value, '$.datetime'), 12, 5) 
               BETWEEN CASE
                         -- Forecast slots are UTC: compare against UTC sun_start/sun_end.
                         -- Only the window START shifts: during local day use current UTC time
                         -- (remaining today); before/after local daylight use full-day window
                         -- (midnight use-case: forecast for the whole coming day).
                         WHEN strftime('%H:%M', 'now', (SELECT offset FROM vars))
                              BETWEEN (SELECT sun_start_local FROM pv_activity)
                                  AND (SELECT sun_end_local   FROM pv_activity)
                             THEN strftime('%H:%M', 'now')
                         ELSE (SELECT sun_start FROM pv_activity)
                       END
               AND (SELECT sun_end FROM pv_activity)
        ), 50.0) as f_avg,
        COALESCE(
        (SELECT AVG(CAST(json_extract(f.value, '$.uv_index') AS FLOAT)) 
         FROM states s 
         JOIN state_attributes a ON s.attributes_id = a.attributes_id, 
         json_each(a.shared_attrs, '$.forecast') f 
         WHERE s.metadata_id = (SELECT f_id FROM ids) 
           AND s.last_updated_ts = (SELECT MAX(last_updated_ts) FROM states WHERE metadata_id = (SELECT f_id FROM ids)) 
           AND substr(json_extract(f.value, '$.datetime'), 1, 10) = date('now', (SELECT offset FROM vars))
           AND substr(json_extract(f.value, '$.datetime'), 12, 5) 
               BETWEEN CASE
                         WHEN strftime('%H:%M', 'now', (SELECT offset FROM vars))
                              BETWEEN (SELECT sun_start_local FROM pv_activity)
                                  AND (SELECT sun_end_local   FROM pv_activity)
                             THEN strftime('%H:%M', 'now')
                         ELSE (SELECT sun_start FROM pv_activity)
                       END
               AND (SELECT sun_end FROM pv_activity)
        ), 0.0) as uv_avg
),

forecast_next_day AS (
    /* Berechnet die durchschnittliche Bewölkung + UV-Index für den gesamten nächsten Tag */
    SELECT COALESCE((
        SELECT AVG(CAST(json_extract(f.value, '$.cloud_coverage') AS FLOAT)) 
        FROM states s 
        JOIN state_attributes a ON s.attributes_id = a.attributes_id, 
        json_each(a.shared_attrs, '$.forecast') f 
        WHERE s.metadata_id = (SELECT f_id FROM ids) 
          AND s.last_updated_ts = (SELECT MAX(last_updated_ts) FROM states WHERE metadata_id = (SELECT f_id FROM ids)) 
          AND substr(json_extract(f.value, '$.datetime'), 1, 10) = date('now', (SELECT offset FROM vars), '+1 day') 
          AND substr(json_extract(f.value, '$.datetime'), 12, 5) BETWEEN (SELECT sun_start FROM pv_activity) AND (SELECT sun_end FROM pv_activity)
    ), 50.0) as f_avg_tomorrow,
    COALESCE((
        SELECT AVG(CAST(json_extract(f.value, '$.uv_index') AS FLOAT)) 
        FROM states s 
        JOIN state_attributes a ON s.attributes_id = a.attributes_id, 
        json_each(a.shared_attrs, '$.forecast') f 
        WHERE s.metadata_id = (SELECT f_id FROM ids) 
          AND s.last_updated_ts = (SELECT MAX(last_updated_ts) FROM states WHERE metadata_id = (SELECT f_id FROM ids)) 
          AND substr(json_extract(f.value, '$.datetime'), 1, 10) = date('now', (SELECT offset FROM vars), '+1 day') 
          AND substr(json_extract(f.value, '$.datetime'), 12, 5) BETWEEN (SELECT sun_start FROM pv_activity) AND (SELECT sun_end FROM pv_activity)
    ), 0.0) as uv_avg_tomorrow
),

cloud_history AS (
        /* Kombiniert Langzeit-Statistiken und kurzfristige States der Bewölkung für den historischen Vergleich */
        SELECT start_ts as ts,
                     CAST(COALESCE(mean, state) AS FLOAT) as val
        FROM statistics 
        WHERE metadata_id = (SELECT w_id_stats FROM ids) 
            AND start_ts > strftime('%s', 'now', '-60 days')
        UNION ALL
        SELECT s.last_updated_ts as ts, 
            CASE WHEN (SELECT sensor_clouds FROM vars) LIKE 'weather.%' 
                     THEN CAST(json_extract(a.shared_attrs, '$.cloud_coverage') AS FLOAT) 
                     ELSE CAST(s.state AS FLOAT) 
            END as val
        FROM states s 
        LEFT JOIN state_attributes a ON s.attributes_id = a.attributes_id 
        WHERE s.metadata_id = (SELECT w_id_states FROM ids) 
            AND ((SELECT sensor_clouds FROM vars) LIKE 'weather.%' OR NOT EXISTS (SELECT 1 FROM statistics WHERE metadata_id = (SELECT w_id_stats FROM ids)))
            AND s.last_updated_ts > strftime('%s', 'now', '-10 days') 
            AND s.state NOT IN ('unknown', 'unavailable', '')
),

uv_history AS (
        /* Kombiniert Langzeit-Statistiken und kurzfristige States des UV-Index für den historischen Vergleich */
        SELECT start_ts as ts,
                     CAST(COALESCE(mean, state) AS FLOAT) as uv_val
        FROM statistics 
        WHERE metadata_id = (SELECT uv_id_stats FROM ids) 
            AND start_ts > strftime('%s', 'now', '-60 days')
        UNION ALL
        SELECT s.last_updated_ts as ts, 
            CASE WHEN (SELECT sensor_uv FROM vars) LIKE 'weather.%' 
                     THEN CAST(json_extract(a.shared_attrs, '$.uv_index') AS FLOAT) 
                     ELSE CAST(s.state AS FLOAT) 
            END as uv_val
        FROM states s 
        LEFT JOIN state_attributes a ON s.attributes_id = a.attributes_id 
        WHERE s.metadata_id = (SELECT uv_id_states FROM ids) 
            AND ((SELECT sensor_uv FROM vars) LIKE 'weather.%' OR NOT EXISTS (SELECT 1 FROM statistics WHERE metadata_id = (SELECT uv_id_stats FROM ids)))
            AND s.last_updated_ts > strftime('%s', 'now', '-10 days') 
            AND s.state NOT IN ('unknown', 'unavailable', '')
),

matching_days AS (
    /* Findet vergangene Tage, deren Bewölkungs- und UV-Profil dem heutigen Forecast am nächsten kommt */
    SELECT 
        date(c.ts, 'unixepoch') as day, 
        AVG(CASE WHEN strftime('%H:%M', c.ts, 'unixepoch') BETWEEN (SELECT sun_start FROM pv_activity) AND (SELECT sun_end FROM pv_activity) THEN c.val END) as h_avg_total_val,
        AVG(CASE WHEN strftime('%H:%M', c.ts, 'unixepoch') >= strftime('%H:00', 'now') AND strftime('%H:%M', c.ts, 'unixepoch') <= (SELECT sun_end FROM pv_activity) THEN c.val END) as h_avg_rest_val,
        AVG(CASE WHEN strftime('%H:%M', u.ts, 'unixepoch') BETWEEN (SELECT sun_start FROM pv_activity) AND (SELECT sun_end FROM pv_activity) THEN u.uv_val END) as uv_avg_total_val,
        AVG(CASE WHEN strftime('%H:%M', u.ts, 'unixepoch') >= strftime('%H:00', 'now') AND strftime('%H:%M', u.ts, 'unixepoch') <= (SELECT sun_end FROM pv_activity) THEN u.uv_val END) as uv_avg_rest_val
    FROM cloud_history c
    JOIN uv_history u ON date(c.ts, 'unixepoch') = date(u.ts, 'unixepoch')
    -- Filtert die Historie: Alles vor dem heutigen lokalen Tag (Offset-gesteuert)
    WHERE date(c.ts, 'unixepoch') < date('now', (SELECT offset FROM vars)) 
    GROUP BY 1 
    HAVING h_avg_total_val IS NOT NULL AND h_avg_total_val > 0
    ORDER BY ABS(
        COALESCE(h_avg_rest_val, h_avg_total_val) -- Fallback auf Gesamt-Schnitt wenn Rest null ist
        - (SELECT f_avg FROM forecast_val)
    ) ASC
),

final_data AS (
    /* Ermittelt die realen PV-Erträge der passendsten historischen Tage */
    SELECT 
        md.*,
        (SELECT MAX(state) FROM statistics WHERE metadata_id = (SELECT p_id FROM ids) AND date(start_ts, 'unixepoch') = md.day) as day_max,
        (SELECT MIN(state) FROM statistics WHERE metadata_id = (SELECT p_id FROM ids) AND date(start_ts, 'unixepoch') = md.day AND state > 0) as day_min,
        COALESCE((SELECT state FROM statistics WHERE metadata_id = (SELECT p_id FROM ids) AND date(start_ts, 'unixepoch') = md.day AND strftime('%H', start_ts, 'unixepoch') = strftime('%H', 'now') LIMIT 1), (SELECT MIN(state) FROM statistics WHERE metadata_id = (SELECT p_id FROM ids) AND date(start_ts, 'unixepoch') = md.day AND state > 0)) as h_hour_curr,
        COALESCE((SELECT state FROM statistics WHERE metadata_id = (SELECT p_id FROM ids) AND date(start_ts, 'unixepoch') = md.day AND strftime('%H', start_ts, 'unixepoch') = strftime('%H', 'now', '-1 hour') LIMIT 1), (SELECT MIN(state) FROM statistics WHERE metadata_id = (SELECT p_id FROM ids) AND date(start_ts, 'unixepoch') = md.day AND state > 0)) as h_hour_prev
    FROM matching_days md
)

/* Generiert das finale JSON-Objekt für Home Assistant */
SELECT json_group_array(
    json_object(
        'date', day,
        'f_avg_today_remaining', (SELECT ROUND(f_avg, 1) FROM forecast_val),        
        'f_avg_tomorrow', (SELECT ROUND(f_avg_tomorrow, 1) FROM forecast_next_day),
        'uv_avg_today_remaining', (SELECT ROUND(uv_avg, 1) FROM forecast_val),
        'uv_avg_tomorrow', (SELECT ROUND(uv_avg_tomorrow, 1) FROM forecast_next_day),
        'h_avg_total', ROUND(h_avg_total_val, 1),
        /* COALESCE: before sunrise h_avg_rest_val is NULL (UTC window '23:xx'..'17:xx' empty) */
        /* Fall back to h_avg_total_val so Jinja cloud-matching works correctly at midnight.   */
        'h_avg_remaining', ROUND(COALESCE(h_avg_rest_val, h_avg_total_val), 1),
        'uv_avg_total', ROUND(uv_avg_total_val, 1),
        'uv_avg_remaining', ROUND(COALESCE(uv_avg_rest_val, uv_avg_total_val), 1),
        'yield_day_total', ROUND(day_max - day_min, 2),
        'yield_day_remaining', ROUND(CASE
            WHEN strftime('%H:%M', 'now', (SELECT offset FROM vars)) > (SELECT sun_end_local   FROM pv_activity)
                THEN 0.0
            WHEN strftime('%H:%M', 'now', (SELECT offset FROM vars)) < (SELECT sun_start_local FROM pv_activity)
                THEN (day_max - day_min)
            ELSE MAX(0, 
                ((h_hour_curr - h_hour_prev) * (1.0 - (CAST(strftime('%M', 'now') AS FLOAT) / 60.0)) * 
                  CASE 
                    WHEN strftime('%H', 'now') = strftime('%H', (SELECT sun_start FROM pv_activity)) THEN 0.85
                    WHEN strftime('%H', 'now') = strftime('%H', (SELECT sun_end FROM pv_activity)) THEN 0.70
                    ELSE 1.0 
                  END)
                + (day_max - h_hour_curr)
            )
        END, 2),
        'pv_start', (SELECT sun_start FROM pv_activity),
        'pv_end', (SELECT sun_end FROM pv_activity)
    )
) as json FROM final_data WHERE day_max > 0;

If you have created your own template sensor for cloud cover as described earlier, it must first collect data for a few days. Afterward, the variable 'weather.forecast_home' can be replaced with the template sensor as 'sensor_clouds ':

...
 'sensor.weather_cloud_coverage' as sensor_clouds,
 'sensor.weather_uv_index' as sensor_uv,
...

This allows more than 10 days to be used for calculating the PV forecast.

The SQL query returns a JSON object for each comparison day containing cloud cover, UV index, and PV yield. The matching process combines cloud cover and UV distance metrics with dynamic weighting (using the uv_w formula) and prioritizes more recent days through a recency bonus (+30% for days <30 days). The top 15 matches are used as the basis for calculation.

Value Template (Main Sensor – Remaining Today)

The SQL sensor name is: sensor.pv_remaining_states (attribute: json). The template reads this attribute and calculates the remaining forecast in 10 steps:

[+]

{# PV FORECAST: Remaining yield today – weighted avg + LOO + trend damping + back-test + cloud-gated penalty #}
{% set raw = value if value is defined else state_attr('sensor.pv_remaining_states', 'json') %}

{% if raw and raw != '[]' and raw is not none %}
  {% set data = raw | from_json %}

  {# --- 0. NIGHT-CHECK --- #}
  {% set offset_min = (now().utcoffset().total_seconds() / 60) | int %}
  {% set pv_end_utc = data[0].pv_end | default('17:30') %}
  {% set end_min_local = ((pv_end_utc.split(':')[0] | int) * 60 + (pv_end_utc.split(':')[1] | int) + offset_min) % 1440 %}
  {% if (now().hour * 60 + now().minute) > end_min_local %}
    0.0
  {% else %}

    {# --- 1. BASE DATA --- #}
    {% set f_avg = data[0].f_avg_today_remaining | float(default=50.0) %}
    {% set current_month = now().month %}
    {% set snow_factor_today = 1.0 %}

    {# --- 2. SEASONAL SNOW DETECTION (Dec/Jan/Feb) --- #}
    {% if current_month in [12, 1, 2] %}
      {% set yesterday_date = (now() - timedelta(days=1)).strftime('%Y-%m-%d') %}
      {% set yesterday_data = data | selectattr('date', 'equalto', yesterday_date) | list | first %}
      {% if yesterday_data is defined %}
        {% set yesterday_perf = yesterday_data.yield_day_remaining | float(0) / ([105 - yesterday_data.h_avg_remaining | float(0), 5] | max) %}
        {% if yesterday_perf < 0.02 %}{% set snow_factor_today = 0.1 %}{% endif %}
      {% endif %}
    {% endif %}

    {# --- 3. ASTRONOMICAL BASE DATA --- #}
    {% set doy = now().strftime('%j') | int(default=1) %}
    {% set latitude = latitude if latitude is defined else state_attr('zone.home', 'latitude') | float(48.0) %}
    {% set lat_rad = latitude * pi / 180 %}
    {% set decl = -0.4093 * cos(2 * pi * (doy + 10) / 365) %}
    {% set dl_today = 24 / pi * acos([[(-tan(lat_rad) * tan(decl)), -1.0] | max, 1.0] | min) %}
    {% set sun_today = 0.80 + 0.20 * cos((doy - 172) * 2 * pi / 365) %}

    {# --- 4. BUILD DATA POOL (dynamic UV weight, Recency-Bonus, Top 15) --- #}
    {% set f_uv_avg = data[0].uv_avg_today_remaining | float(default=0.0) %}
    {# uv_w scales with forecast cloud: 0% clouds → 30%, 100% clouds → 70% #}
    {% set uv_w = [0.3 + 0.4 * (f_avg / 100.0), 0.7] | min %}
    {% set ns_pool = namespace(items=[], total_w=0) %}
    {% for item in data %}
      {% set yield_raw = item.yield_day_remaining | float(default=0) %}
      {% set clouds = item.h_avg_remaining | float(default=0) %}
      {% set uv_hist = item.uv_avg_remaining | float(default=0) %}
      {% set dt_item = as_datetime(item.date) %}
      {% if dt_item is not none %}
        {% set item_day = dt_item.strftime('%j') | int(default=1) %}
        {% set decl_i = -0.4093 * cos(2 * pi * (item_day + 10) / 365) %}
        {% set dl_item = 24 / pi * acos([[(-tan(lat_rad) * tan(decl_i)), -1.0] | max, 1.0] | min) %}
        {% set sun_item = 0.80 + 0.20 * cos((item_day - 172) * 2 * pi / 365) %}
        {% set s_korr = (sun_today / sun_item) * (dl_today / dl_item) %}
        {% set y_korr = yield_raw * s_korr %}
        {% set diff_c = (clouds - f_avg) | abs %}
        {% if f_uv_avg > 0 %}
          {% set diff = diff_c * (1.0 - uv_w) + (uv_hist - f_uv_avg) | abs * 8.0 * uv_w %}
        {% else %}
          {% set diff = diff_c %}
        {% endif %}
        {% set days_ago = ((now().timestamp() - dt_item.timestamp()) / 86400) | int(0) %}
        {% set w = (1 / ([diff, 0.5] | max)) * (1.0 + 0.3 * ([1.0 - days_ago / 30.0, 0.0] | max)) %}
        {% if yield_raw > 0.05 or clouds > 95 or current_month in [12, 1, 2] %}
          {% set ns_pool.total_w = ns_pool.total_w + w %}
          {% set ns_pool.items = ns_pool.items + [{'date': item.date, 'h_avg': clouds, 'y_korr': y_korr, 'w': w, 'days_ago': days_ago}] %}
        {% endif %}
      {% endif %}
    {% endfor %}

    {# --- 5. FORECAST CALCULATION (Top 15, decision logic) --- #}
    {% set top15 = (ns_pool.items | sort(attribute='w', reverse=True))[:15] %}
    {% set ns_top = namespace(total_w=0) %}
    {% for item in top15 %}{% set ns_top.total_w = ns_top.total_w + item.w %}{% endfor %}
    {% set pool = top15 %}
    {% set brighter = pool | selectattr('h_avg', 'le', f_avg) | list %}
    {% set darker = pool | selectattr('h_avg', 'ge', f_avg) | list %}
    {% set res = 0 %}
    {% if brighter | count > 0 and darker | count == 0 %}
      {% set worst_day = brighter | sort(attribute='y_korr') | first %}
      {% set res = worst_day.y_korr * ([120 - f_avg, 5.0] | max / [120 - worst_day.h_avg, 5.0] | max) %}
    {% elif darker | count > 0 and pool | selectattr('h_avg', 'le', f_avg) | list | count == 0 %}
      {% set res = darker | map(attribute='y_korr') | max %}
    {% elif pool | count > 0 %}
      {% set ns_mix = namespace(ws=0) %}
      {% for item in pool %}{% set ns_mix.ws = ns_mix.ws + (item.y_korr * item.w) %}{% endfor %}
      {% set res = ns_mix.ws / (ns_top.total_w if ns_top.total_w > 0 else 1) %}
    {% endif %}

    {# --- 6. LOO CROSS-VALIDATION: down-weight outlier pool days --- #}
    {% set ns_cv = namespace(items=[]) %}
    {% for item_i in pool %}
      {% set ns_loo = namespace(w=0, wy=0) %}
      {% for item_j in pool %}
        {% if item_j.date != item_i.date %}
          {% set ns_loo.w  = ns_loo.w  + item_j.w %}
          {% set ns_loo.wy = ns_loo.wy + item_j.w * item_j.y_korr %}
        {% endif %}
      {% endfor %}
      {% set acc = ((item_i.y_korr / (ns_loo.wy / ns_loo.w)) * 100) | round(0) | int if (ns_loo.w > 0 and ns_loo.wy > 0) else 100 %}
      {% set ns_cv.items = ns_cv.items + [{'date': item_i.date, 'acc': acc}] %}
    {% endfor %}
    {% if pool | count > 1 %}
      {% set ns_corr = namespace(w=0, wy=0) %}
      {% for item_i in pool %}
        {% set cv = ns_cv.items | selectattr('date', 'equalto', item_i.date) | list %}
        {% set acc_factor = 1.0 / (1.0 + ((cv[0].acc - 100) | abs) / 100.0) if cv | length > 0 else 1.0 %}
        {% set ns_corr.w  = ns_corr.w  + item_i.w * acc_factor %}
        {% set ns_corr.wy = ns_corr.wy + item_i.y_korr * item_i.w * acc_factor %}
      {% endfor %}
      {% if ns_corr.w > 0 and ns_corr.wy > 0 %}{% set res = ns_corr.wy / ns_corr.w %}{% endif %}
    {% endif %}

    {# --- 7. TREND DAMPING: recent ≤14d avg >15% above older → dampen 50% of excess --- #}
    {% set ns_rec = namespace(w=0, wy=0) %}
    {% set ns_old = namespace(w=0, wy=0) %}
    {% for item_i in pool %}
      {% if item_i.days_ago <= 14 %}
        {% set ns_rec.w = ns_rec.w + item_i.w %}{% set ns_rec.wy = ns_rec.wy + item_i.y_korr * item_i.w %}
      {% else %}
        {% set ns_old.w = ns_old.w + item_i.w %}{% set ns_old.wy = ns_old.wy + item_i.y_korr * item_i.w %}
      {% endif %}
    {% endfor %}
    {% if ns_rec.w > 0 and ns_old.w > 0 %}
      {% set avg_rec = ns_rec.wy / ns_rec.w %}
      {% set avg_old = ns_old.wy / ns_old.w %}
      {% if avg_old > 0 and (avg_rec / avg_old) > 1.15 %}
        {% set res = res / (1.0 + 0.5 * ((avg_rec / avg_old) - 1.0)) %}
      {% endif %}
    {% endif %}

    {# --- 8. BACK-TEST: data-driven carry-through from consecutive shortfall pairs --- #}
    {% set ns_all = namespace(sum_y=0.0, count_y=0) %}
    {% for item in pool %}{% if item.y_korr > 0 %}{% set ns_all.sum_y = ns_all.sum_y + item.y_korr %}{% set ns_all.count_y = ns_all.count_y + 1 %}{% endif %}{% endfor %}
    {% set mean_y = ns_all.sum_y / ([ns_all.count_y, 1] | max) %}
    {% set ns_bt = namespace(total=0, useful=0, trigger_sum=0.0, carry_sum=0.0) %}
    {% for item_i in pool %}
      {% if item_i.y_korr >= 0.40 * mean_y and item_i.y_korr < 0.85 * mean_y %}
        {% set next_items = pool | selectattr('date', 'equalto', (as_datetime(item_i.date) + timedelta(days=1)).strftime('%Y-%m-%d')) | list %}
        {% if next_items | length > 0 %}
          {% set ns_bt.total = ns_bt.total + 1 %}
          {% set ns_bt.trigger_sum = ns_bt.trigger_sum + (1.0 - item_i.y_korr / mean_y) %}
          {% set ns_bt.carry_sum = ns_bt.carry_sum + ([1.0 - next_items[0].y_korr / mean_y, 0.0] | max) %}
          {% if next_items[0].y_korr < mean_y %}{% set ns_bt.useful = ns_bt.useful + 1 %}{% endif %}
        {% endif %}
      {% endif %}
    {% endfor %}
    {% set effective_carry = (ns_bt.carry_sum / ns_bt.trigger_sum) * (ns_bt.useful / ns_bt.total) if (ns_bt.total > 0 and ns_bt.trigger_sum > 0) else 0.3 %}

    {# --- 9. CLOUD-GATED YESTERDAY PENALTY (both days ≥60% cloudy) --- #}
    {% set yesterday_date_yp = (now() - timedelta(days=1)).strftime('%Y-%m-%d') %}
    {% set yest_cv = ns_cv.items | selectattr('date', 'equalto', yesterday_date_yp) | list %}
    {% set yest_item = pool | selectattr('date', 'equalto', yesterday_date_yp) | list %}
    {% set yest_clouds = yest_item[0].h_avg if yest_item | length > 0 else 0 %}
    {% if yest_cv | length > 0 %}
      {% set yest_acc = yest_cv[0].acc %}
      {% if yest_acc >= 40 and yest_acc < 85 and f_avg >= 60 and yest_clouds >= 60 %}
        {% set res = res * ([1.0 - effective_carry * (1.0 - yest_acc / 100.0), 0.5] | max) %}
      {% endif %}
    {% endif %}

    {# --- 10. FINAL SCALING --- #}
    {{ (res * snow_factor_today) | round(2) }}

  {% endif %}
{% else %}
  0.0
{% endif %}

Explanation of the 10 steps:

0. Night Check: From local sunset until midnight, 0.0 is returned; from midnight until sunrise, SQL returns full-day data.
1–3. Basic Data / Snow / Astronomy: Month, snow factor (Dec–Feb), day length, and relative sun position are calculated.
4. Pool construction with dynamic UV weighting: uv_w = min(0.3 + 0.4 × cloud cover/100, 0.7) —at 0% cloud cover, UV counts as 30%; at 100%, it counts as 70%. Recency bonus of up to +30% for days within the last 30 days.
5. Decision logic (Top 15): Weighted average (default), Light reduction (today is brighter than all reference days), Max assumption (today is darker than all reference days).
6. LOO cross-validation: Each pool day is validated against the consensus of the others. Outliers are downweighted using acc_factor = 1 / (1 + |acc−100| / 100).
7. Trend damping: If the most recent 14 days are more than 15% above the older pool average, half of the excess is subtracted.
8. Back-Test: A data-driven carry-through factor is derived from consecutive pairs of moderate underperformance events in the pool.
9. Cloud-gated penalty: The carry-through factor is applied to the forecast only if cloud cover was ≥60% yesterday and today, and yesterday’s LOO-Acc was in the range of 40–84%.
10. Scaling: Multiply by the snow factor; output in kWh.

Markdown Lovelace Card (Debugging Table)

This Lovelace Markdown Card displays all top 15 comparison days with cloud cover, UV index, seasonal correction factor, LOO accuracy, and weighting—ideal for understanding the forecast calculation:

[+]

{% set raw_json = state_attr('sensor.pv_remaining_states', 'json') %}
{% if raw_json and raw_json != '[]' and raw_json is not none %}
  {% set data = raw_json | from_json %}

  {% if data | length > 0 %}
    {% set f_avg = data[0].f_avg_today_remaining | float(default=50.0) %}
    {% set f_uv_avg = data[0].uv_avg_today_remaining | float(default=0.0) %}

    {# 0. NIGHT-CHECK #}
    {% set offset_min = (now().utcoffset().total_seconds() / 60) | int %}
    {% set pv_end_utc = data[0].pv_end | default('17:30') %}
    {% set end_min_local = ((pv_end_utc.split(':')[0] | int) * 60 + (pv_end_utc.split(':')[1] | int) + offset_min) % 1440 %}
    {% set is_night = (now().hour * 60 + now().minute) > end_min_local %}

    {# 1. SEASONAL SNOW DETECTION (Dec / Jan / Feb) #}
    {% set current_month = now().month %}
    {% set snow_factor_today = 1.0 %}
    {% if current_month in [12, 1, 2] %}
      {% set yesterday_date = (now() - timedelta(days=1)).strftime('%Y-%m-%d') %}
      {% set yesterday_data = data | selectattr('date', 'equalto', yesterday_date) | list | first %}
      {% if yesterday_data is defined %}
        {% set yesterday_perf = yesterday_data.yield_day_remaining | float(0) / ([105 - yesterday_data.h_avg_remaining | float(0), 5] | max) %}
        {% if yesterday_perf < 0.02 %}{% set snow_factor_today = 0.1 %}{% endif %}
      {% endif %}
    {% endif %}

    {# 2. ASTRONOMICAL BASE DATA #}
    {% set latitude = state_attr('zone.home', 'latitude') | float(48.0) %}
    {% set doy = now().strftime('%j') | int(default=1) %}
    {% set lat_rad = latitude * pi / 180 %}
    {% set decl = -0.4093 * cos(2 * pi * (doy + 10) / 365) %}
    {% set dl_today = 24 / pi * acos([[(-tan(lat_rad) * tan(decl)), -1.0] | max, 1.0] | min) %}
    {% set sun_today = 0.80 + 0.20 * cos((doy - 172) * 2 * pi / 365) %}

    {# 3. POOL (dynamic UV weight, Recency-Bonus, Top 15) #}
    {% set uv_w = [0.3 + 0.4 * (f_avg / 100.0), 0.7] | min %}
    {% set ns_pool = namespace(items=[], total_w=0) %}
    {% for item in data %}
      {% set yield_raw = item.yield_day_remaining | float(default=0) %}
      {% set clouds = item.h_avg_remaining | float(default=0) %}
      {% set clouds_total = item.h_avg_total | float(default=0) %}
      {% set uv = item.uv_avg_remaining | float(default=0) %}
      {% set item_dt = as_datetime(item.date) %}
      {% if item_dt is not none %}
        {% set item_day = item_dt.strftime('%j') | int(default=1) %}
        {% set decl_i = -0.4093 * cos(2 * pi * (item_day + 10) / 365) %}
        {% set dl_item = 24 / pi * acos([[(-tan(lat_rad) * tan(decl_i)), -1.0] | max, 1.0] | min) %}
        {% set sun_item = 0.80 + 0.20 * cos((item_day - 172) * 2 * pi / 365) %}
        {% set s_korr = (sun_today / sun_item) * (dl_today / dl_item) %}
        {% set diff_c = (clouds - f_avg) | abs %}
        {% if f_uv_avg > 0 %}
          {% set diff = diff_c * (1.0 - uv_w) + (uv - f_uv_avg) | abs * 8.0 * uv_w %}
        {% else %}
          {% set diff = diff_c %}
        {% endif %}
        {% set days_ago = ((now().timestamp() - item_dt.timestamp()) / 86400) | int(0) %}
        {% set w = (1 / ([diff, 0.5] | max)) * (1.0 + 0.3 * ([1.0 - days_ago / 30.0, 0.0] | max)) %}
        {% if yield_raw > 0.05 or clouds > 95 or current_month in [12, 1, 2] %}
          {% set ns_pool.total_w = ns_pool.total_w + w %}
          {% set ns_pool.items = ns_pool.items + [{'date': item.date, 'h_avg': clouds, 'h_avg_total': clouds_total, 'uv_avg': uv, 'y_korr': yield_raw * s_korr, 's_fakt': s_korr, 'w': w, 'yield_day_total': item.yield_day_total, 'days_ago': days_ago, 'filtered': false}] %}
        {% else %}
          {% set ns_pool.items = ns_pool.items + [{'date': item.date, 'h_avg': clouds, 'h_avg_total': clouds_total, 'uv_avg': uv, 'y_korr': yield_raw * s_korr, 's_fakt': s_korr, 'w': 0, 'yield_day_total': item.yield_day_total, 'days_ago': days_ago, 'filtered': true}] %}
        {% endif %}
      {% endif %}
    {% endfor %}

    {# Top 15 selection #}
    {% set top15 = (ns_pool.items | sort(attribute='w', reverse=True))[:15] %}
    {% set ns_top = namespace(total_w=0) %}
    {% for item in top15 %}{% if not item.filtered %}{% set ns_top.total_w = ns_top.total_w + item.w %}{% endif %}{% endfor %}
    {% set pool = top15 | selectattr('filtered', 'equalto', false) | list %}

    {# 4. Decision logic #}
    {% set brighter = pool | selectattr('h_avg', 'le', f_avg) | list %}
    {% set darker = pool | selectattr('h_avg', 'ge', f_avg) | list %}
    {% set res = 0 %}
    {% set method = "No data" %}
    {% if brighter | count > 0 and darker | count == 0 %}
      {% set method = "Light reduction" %}
      {% set worst_day = brighter | sort(attribute='y_korr') | first %}
      {% set res = worst_day.y_korr * ([120 - f_avg, 5.0] | max / [120 - worst_day.h_avg, 5.0] | max) %}
    {% elif darker | count > 0 and pool | selectattr('h_avg', 'le', f_avg) | list | count == 0 %}
      {% set method = "Max assumption" %}
      {% set res = darker | map(attribute='y_korr') | max %}
    {% elif pool | count > 0 %}
      {% set method = "Weighted average" %}
      {% set ns_mix = namespace(ws=0) %}
      {% for item in pool %}{% set ns_mix.ws = ns_mix.ws + (item.y_korr * item.w) %}{% endfor %}
      {% set res = ns_mix.ws / (ns_top.total_w if ns_top.total_w > 0 else 1) %}
    {% endif %}

    {# 5. LOO cross-validation #}
    {% set ns_cv = namespace(items=[]) %}
    {% for item_i in pool %}
      {% set ns_loo = namespace(w=0, wy=0) %}
      {% for item_j in pool %}
        {% if item_j.date != item_i.date %}
          {% set ns_loo.w = ns_loo.w + item_j.w %}{% set ns_loo.wy = ns_loo.wy + item_j.w * item_j.y_korr %}
        {% endif %}
      {% endfor %}
      {% set acc = ((item_i.y_korr / (ns_loo.wy / ns_loo.w)) * 100) | round(0) | int if (ns_loo.w > 0 and ns_loo.wy > 0) else 100 %}
      {% set ns_cv.items = ns_cv.items + [{'date': item_i.date, 'acc': acc}] %}
    {% endfor %}
    {% if pool | count > 1 %}
      {% set ns_corr = namespace(w=0, wy=0) %}
      {% for item_i in pool %}
        {% set cv = ns_cv.items | selectattr('date', 'equalto', item_i.date) | list %}
        {% set acc_factor = 1.0 / (1.0 + ((cv[0].acc - 100) | abs) / 100.0) if cv | length > 0 else 1.0 %}
        {% set ns_corr.w = ns_corr.w + item_i.w * acc_factor %}{% set ns_corr.wy = ns_corr.wy + item_i.y_korr * item_i.w * acc_factor %}
      {% endfor %}
      {% if ns_corr.w > 0 and ns_corr.wy > 0 %}{% set res = ns_corr.wy / ns_corr.w %}{% endif %}
    {% endif %}

    {# 6. Trend damping #}
    {% set ns_rec = namespace(w=0, wy=0) %}{% set ns_old = namespace(w=0, wy=0) %}
    {% for item_i in pool %}
      {% if item_i.days_ago <= 14 %}{% set ns_rec.w = ns_rec.w + item_i.w %}{% set ns_rec.wy = ns_rec.wy + item_i.y_korr * item_i.w %}
      {% else %}{% set ns_old.w = ns_old.w + item_i.w %}{% set ns_old.wy = ns_old.wy + item_i.y_korr * item_i.w %}{% endif %}
    {% endfor %}
    {% set trend_damped = false %}
    {% if ns_rec.w > 0 and ns_old.w > 0 %}
      {% set avg_rec = ns_rec.wy / ns_rec.w %}{% set avg_old = ns_old.wy / ns_old.w %}
      {% if avg_old > 0 and (avg_rec / avg_old) > 1.15 %}
        {% set res = res / (1.0 + 0.5 * ((avg_rec / avg_old) - 1.0)) %}{% set trend_damped = true %}
      {% endif %}
    {% endif %}

    {# 7. Back-test + Cloud-gated penalty #}
    {% set ns_all = namespace(sum_y=0.0, count_y=0) %}
    {% for item in pool %}{% if item.y_korr > 0 %}{% set ns_all.sum_y = ns_all.sum_y + item.y_korr %}{% set ns_all.count_y = ns_all.count_y + 1 %}{% endif %}{% endfor %}
    {% set mean_y = ns_all.sum_y / ([ns_all.count_y, 1] | max) %}
    {% set ns_bt = namespace(total=0, useful=0, trigger_sum=0.0, carry_sum=0.0) %}
    {% for item_i in pool %}
      {% if item_i.y_korr >= 0.40 * mean_y and item_i.y_korr < 0.85 * mean_y %}
        {% set next_items = pool | selectattr('date', 'equalto', (as_datetime(item_i.date) + timedelta(days=1)).strftime('%Y-%m-%d')) | list %}
        {% if next_items | length > 0 %}
          {% set ns_bt.total = ns_bt.total + 1 %}{% set ns_bt.trigger_sum = ns_bt.trigger_sum + (1.0 - item_i.y_korr / mean_y) %}
          {% set ns_bt.carry_sum = ns_bt.carry_sum + ([1.0 - next_items[0].y_korr / mean_y, 0.0] | max) %}
          {% if next_items[0].y_korr < mean_y %}{% set ns_bt.useful = ns_bt.useful + 1 %}{% endif %}
        {% endif %}
      {% endif %}
    {% endfor %}
    {% set effective_carry = (ns_bt.carry_sum / ns_bt.trigger_sum) * (ns_bt.useful / ns_bt.total) if (ns_bt.total > 0 and ns_bt.trigger_sum > 0) else 0.3 %}
    {% set yest_date = (now() - timedelta(days=1)).strftime('%Y-%m-%d') %}
    {% set yest_cv = ns_cv.items | selectattr('date', 'equalto', yest_date) | list %}
    {% set yest_item = pool | selectattr('date', 'equalto', yest_date) | list %}
    {% set yest_clouds = yest_item[0].h_avg if yest_item | length > 0 else 0 %}
    {% set penalty_applied = false %}
    {% if yest_cv | length > 0 %}
      {% set yest_acc = yest_cv[0].acc %}
      {% if yest_acc >= 40 and yest_acc < 85 and f_avg >= 60 and yest_clouds >= 60 %}
        {% set res = res * ([1.0 - effective_carry * (1.0 - yest_acc / 100.0), 0.5] | max) %}
        {% set penalty_applied = true %}
      {% endif %}
    {% endif %}

    {% set final_val = res * snow_factor_today %}

**Forecast:**
## {{ (0.0 if is_night else final_val | round(2)) }} kWh
*Basis: **{{ f_avg }}%** clouds · **{{ f_uv_avg | round(1) }}** UV · uv_w={{ (uv_w * 100) | round(0) | int }}% | **{{ method }}***
{% if snow_factor_today < 1.0 %}⚠️ **Schnee erkannt! (Faktor {{ (snow_factor_today * 100) | round(0) }}%)**{% endif %}
{% if trend_damped %}📉 Trend-Dämpfung aktiv{% endif %}
{% if penalty_applied %}☁️ Cloud-gated Penalty aktiv (carry={{ (effective_carry * 100) | round(0) | int }}%){% endif %}

| Datum | Tag-Bew. | Tag-Ertrag | Rest-Bew. | UV | Rest-Ertrag | LOO-Acc | Gewicht |
| :--- | :---: | :---: | :---: | :---: | :---: | :---: | :---: |
{%- for item in top15 %}
  {%- set cv = ns_cv.items | selectattr('date', 'equalto', item.date) | list %}
  {%- set acc_str = cv[0].acc | string + "%" if cv | length > 0 else "–" %}
| {{ item.date }} | {{ item.h_avg_total }}% | {{ item.yield_day_total }} | **{{ item.h_avg }}%** | {{ item.uv_avg | round(1) }} | **{{ (item.y_korr * snow_factor_today) | round(2) }} ({{ item.s_fakt | round(2) }}×)**{% if item.filtered %}❌{% endif %} | {{ acc_str }} | {{ (((item.w / ns_top.total_w) * 100) if ns_top.total_w > 0 else 0) | round(1) }}% |
{%- endfor %}

  {% else %}
**Keine Daten im SQL-Ergebnis.**
  {% endif %}
{% else %}
**Warte auf SQL-Daten...**
{% endif %}

Morning Sensor

Forecast for tomorrow: Helper Template Sensor (dynamic UV weight, LOO, trend smoothing, back-test, cloud-gated penalty):

[+]

{# PV FORECAST TOMORROW: weighted avg + LOO + trend damping + back-test + cloud-gated penalty #}
{% set raw_json = state_attr('sensor.pv_remaining_states', 'json') %}

{% if raw_json and raw_json != '[]' and raw_json is not none %}
  {% set data = raw_json | from_json %}

  {% set f_avg_tomorrow = data[0].f_avg_tomorrow | float(default=50.0) %}
  {% set f_uv_avg_tomorrow = data[0].uv_avg_tomorrow | float(default=0.0) %}

  {# ASTRONOMICAL BASE DATA FOR TOMORROW #}
  {% set latitude = latitude if latitude is defined else state_attr('zone.home', 'latitude') | float(48.0) %}
  {% set doy_tomorrow = (now() + timedelta(days=1)).strftime('%j') | int %}
  {% set lat_rad = latitude * pi / 180 %}
  {% set decl_tomorrow = -0.4093 * cos(2 * pi * (doy_tomorrow + 10) / 365) %}
  {% set dl_tomorrow = 24 / pi * acos([[(-tan(lat_rad) * tan(decl_tomorrow)), -1.0] | max, 1.0] | min) %}
  {% set sun_tomorrow = 0.80 + 0.20 * cos((doy_tomorrow - 172) * 2 * pi / 365) %}

  {# POOL: dynamic UV weight, Recency-Bonus, Top 15 #}
  {% set uv_w = [0.3 + 0.4 * (f_avg_tomorrow / 100.0), 0.7] | min %}
  {% set ns_pool = namespace(items=[], total_w=0) %}
  {% for item in data %}
    {% set yield_total = item.yield_day_total | float(default=0) %}
    {% set clouds_hist = item.h_avg_total | float(default=0) %}
    {% set uv_hist = item.uv_avg_total | float(default=0) %}
    {% set dt_item = as_datetime(item.date) %}
    {% if dt_item is not none %}
      {% set item_day = dt_item.strftime('%j') | int(default=1) %}
      {% set decl_i = -0.4093 * cos(2 * pi * (item_day + 10) / 365) %}
      {% set dl_item = 24 / pi * acos([[(-tan(lat_rad) * tan(decl_i)), -1.0] | max, 1.0] | min) %}
      {% set sun_item = 0.80 + 0.20 * cos((item_day - 172) * 2 * pi / 365) %}
      {% set s_korr = (sun_tomorrow / sun_item) * (dl_tomorrow / dl_item) %}
      {% set y_korr = yield_total * s_korr %}
      {% set diff_c = (clouds_hist - f_avg_tomorrow) | abs %}
      {% if f_uv_avg_tomorrow > 0 %}
        {% set diff = diff_c * (1.0 - uv_w) + (uv_hist - f_uv_avg_tomorrow) | abs * 8.0 * uv_w %}
      {% else %}
        {% set diff = diff_c %}
      {% endif %}
      {% set days_ago = ((now().timestamp() - dt_item.timestamp()) / 86400) | int(0) %}
      {% set w = (1 / ([diff, 0.5] | max)) * (1.0 + 0.3 * ([1.0 - days_ago / 30.0, 0.0] | max)) %}
      {% if yield_total > 0.05 %}
        {% set ns_pool.total_w = ns_pool.total_w + w %}
        {% set ns_pool.items = ns_pool.items + [{'date': item.date, 'h_avg': clouds_hist, 'w': w, 'y_korr': y_korr, 'days_ago': days_ago, 'filtered': false}] %}
      {% else %}
        {% set ns_pool.items = ns_pool.items + [{'date': item.date, 'h_avg': clouds_hist, 'w': 0, 'y_korr': y_korr, 'days_ago': days_ago, 'filtered': true}] %}
      {% endif %}
    {% endif %}
  {% endfor %}

  {% set top15 = (ns_pool.items | sort(attribute='w', reverse=True))[:15] %}
  {% set ns_top = namespace(total_w=0) %}
  {% for item in top15 %}{% if not item.filtered %}{% set ns_top.total_w = ns_top.total_w + item.w %}{% endif %}{% endfor %}
  {% set pool = top15 | selectattr('filtered', 'equalto', false) | list %}
  {% set brighter = pool | selectattr('h_avg', 'le', f_avg_tomorrow) | list %}
  {% set darker = pool | selectattr('h_avg', 'ge', f_avg_tomorrow) | list %}
  {% set res = 0 %}
  {% if brighter | count > 0 and darker | count == 0 %}
    {% set worst_day = brighter | sort(attribute='y_korr') | first %}
    {% set res = worst_day.y_korr * ([120 - f_avg_tomorrow, 5.0] | max / [120 - worst_day.h_avg, 5.0] | max) %}
  {% elif darker | count > 0 and pool | selectattr('h_avg', 'le', f_avg_tomorrow) | list | count == 0 %}
    {% set res = darker | map(attribute='y_korr') | max %}
  {% elif pool | count > 0 %}
    {% set ns_mix = namespace(ws=0) %}
    {% for item in pool %}{% set ns_mix.ws = ns_mix.ws + (item.y_korr * item.w) %}{% endfor %}
    {% set res = ns_mix.ws / (ns_top.total_w if ns_top.total_w > 0 else 1) %}
  {% endif %}

  {# LOO CROSS-VALIDATION #}
  {% set ns_cv = namespace(items=[]) %}
  {% for item_i in pool %}
    {% set ns_loo = namespace(w=0, wy=0) %}
    {% for item_j in pool %}
      {% if item_j.date != item_i.date %}{% set ns_loo.w = ns_loo.w + item_j.w %}{% set ns_loo.wy = ns_loo.wy + item_j.w * item_j.y_korr %}{% endif %}
    {% endfor %}
    {% set acc = ((item_i.y_korr / (ns_loo.wy / ns_loo.w)) * 100) | round(0) | int if (ns_loo.w > 0 and ns_loo.wy > 0) else 100 %}
    {% set ns_cv.items = ns_cv.items + [{'date': item_i.date, 'acc': acc}] %}
  {% endfor %}
  {% if pool | count > 1 %}
    {% set ns_corr = namespace(w=0, wy=0) %}
    {% for item_i in pool %}
      {% set cv = ns_cv.items | selectattr('date', 'equalto', item_i.date) | list %}
      {% set acc_factor = 1.0 / (1.0 + ((cv[0].acc - 100) | abs) / 100.0) if cv | length > 0 else 1.0 %}
      {% set ns_corr.w = ns_corr.w + item_i.w * acc_factor %}{% set ns_corr.wy = ns_corr.wy + item_i.y_korr * item_i.w * acc_factor %}
    {% endfor %}
    {% if ns_corr.w > 0 and ns_corr.wy > 0 %}{% set res = ns_corr.wy / ns_corr.w %}{% endif %}
  {% endif %}

  {# TREND DAMPING #}
  {% set ns_rec = namespace(w=0, wy=0) %}{% set ns_old = namespace(w=0, wy=0) %}
  {% for item_i in pool %}
    {% if item_i.days_ago <= 14 %}{% set ns_rec.w = ns_rec.w + item_i.w %}{% set ns_rec.wy = ns_rec.wy + item_i.y_korr * item_i.w %}
    {% else %}{% set ns_old.w = ns_old.w + item_i.w %}{% set ns_old.wy = ns_old.wy + item_i.y_korr * item_i.w %}{% endif %}
  {% endfor %}
  {% if ns_rec.w > 0 and ns_old.w > 0 %}
    {% set avg_rec = ns_rec.wy / ns_rec.w %}{% set avg_old = ns_old.wy / ns_old.w %}
    {% if avg_old > 0 and (avg_rec / avg_old) > 1.15 %}
      {% set res = res / (1.0 + 0.5 * ((avg_rec / avg_old) - 1.0)) %}
    {% endif %}
  {% endif %}

  {# BACK-TEST + CLOUD-GATED YESTERDAY PENALTY #}
  {% set ns_all = namespace(sum_y=0.0, count_y=0) %}
  {% for item in pool %}{% if item.y_korr > 0 %}{% set ns_all.sum_y = ns_all.sum_y + item.y_korr %}{% set ns_all.count_y = ns_all.count_y + 1 %}{% endif %}{% endfor %}
  {% set mean_y = ns_all.sum_y / ([ns_all.count_y, 1] | max) %}
  {% set ns_bt = namespace(total=0, useful=0, trigger_sum=0.0, carry_sum=0.0) %}
  {% for item_i in pool %}
    {% if item_i.y_korr >= 0.40 * mean_y and item_i.y_korr < 0.85 * mean_y %}
      {% set next_items = pool | selectattr('date', 'equalto', (as_datetime(item_i.date) + timedelta(days=1)).strftime('%Y-%m-%d')) | list %}
      {% if next_items | length > 0 %}
        {% set ns_bt.total = ns_bt.total + 1 %}{% set ns_bt.trigger_sum = ns_bt.trigger_sum + (1.0 - item_i.y_korr / mean_y) %}
        {% set ns_bt.carry_sum = ns_bt.carry_sum + ([1.0 - next_items[0].y_korr / mean_y, 0.0] | max) %}
        {% if next_items[0].y_korr < mean_y %}{% set ns_bt.useful = ns_bt.useful + 1 %}{% endif %}
      {% endif %}
    {% endif %}
  {% endfor %}
  {% set effective_carry = (ns_bt.carry_sum / ns_bt.trigger_sum) * (ns_bt.useful / ns_bt.total) if (ns_bt.total > 0 and ns_bt.trigger_sum > 0) else 0.3 %}
  {% set yesterday_date = (now() - timedelta(days=1)).strftime('%Y-%m-%d') %}
  {% set yest_cv = ns_cv.items | selectattr('date', 'equalto', yesterday_date) | list %}
  {% set yest_item = ns_pool.items | selectattr('date', 'equalto', yesterday_date) | selectattr('filtered', 'equalto', false) | list %}
  {% set yest_clouds = yest_item[0].h_avg if yest_item | length > 0 else 0 %}
  {% if yest_cv | length > 0 %}
    {% set yest_acc = yest_cv[0].acc %}
    {% if yest_acc >= 40 and yest_acc < 85 and f_avg_tomorrow >= 60 and yest_clouds >= 60 %}
      {% set res = res * ([1.0 - effective_carry * (1.0 - yest_acc / 100.0), 0.5] | max) %}
    {% endif %}
  {% endif %}

  {{ res | round(2) }}

{% else %}
  0.0
{% endif %}

Optimistic Sensor (max)

Returns the highest corrected yield from the 15 most similar days—the upper limit of the forecast (no LOO/penalty, since the upper limit is desired here):

[+]

{% set raw_json = state_attr('sensor.pv_remaining_states', 'json') %}
{% if raw_json and raw_json != '[]' and raw_json is not none %}
  {% set data = raw_json | from_json %}
  {# Nacht-Check #}
  {% set offset_min = (now().utcoffset().total_seconds() / 60) | int %}
  {% set pv_end_utc = data[0].pv_end | default('17:30') %}
  {% set end_min_local = ((pv_end_utc.split(':')[0] | int) * 60 + (pv_end_utc.split(':')[1] | int) + offset_min) % 1440 %}
  {% if (now().hour * 60 + now().minute) > end_min_local %}
    0.0
  {% else %}
    {% set f_avg = data[0].f_avg_today_remaining | float(default=50.0) %}
    {% set f_uv_avg = data[0].uv_avg_today_remaining | float(default=0.0) %}
    {% set doy = now().strftime('%j') | int(default=1) %}
    {% set latitude = latitude if latitude is defined else state_attr('zone.home', 'latitude') | float(48.0) %}
    {% set lat_rad = latitude * pi / 180 %}
    {% set decl = -0.4093 * cos(2 * pi * (doy + 10) / 365) %}
    {% set dl_today = 24 / pi * acos([[(-tan(lat_rad) * tan(decl)), -1.0] | max, 1.0] | min) %}
    {% set sun_today = 0.80 + 0.20 * cos((doy - 172) * 2 * pi / 365) %}
    {% set uv_w = [0.3 + 0.4 * (f_avg / 100.0), 0.7] | min %}
    {% set ns_pool = namespace(items=[]) %}
    {% for item in data %}
      {% set dt_item = as_datetime(item.date) %}
      {% if dt_item is not none %}
        {% set item_day = dt_item.strftime('%j') | int(default=1) %}
        {% set decl_i = -0.4093 * cos(2 * pi * (item_day + 10) / 365) %}
        {% set dl_item = 24 / pi * acos([[(-tan(lat_rad) * tan(decl_i)), -1.0] | max, 1.0] | min) %}
        {% set sun_item = 0.80 + 0.20 * cos((item_day - 172) * 2 * pi / 365) %}
        {% set s_korr = (sun_today / sun_item) * (dl_today / dl_item) %}
        {% set yield_korr = item.yield_day_remaining | float(default=0) * s_korr %}
        {% set uv_hist = item.uv_avg_remaining | float(default=0) %}
        {% set diff_c = (item.h_avg_remaining | float(default=0) - f_avg) | abs %}
        {% if f_uv_avg > 0 %}
          {% set diff = diff_c * (1.0 - uv_w) + (uv_hist - f_uv_avg) | abs * 8.0 * uv_w %}
        {% else %}
          {% set diff = diff_c %}
        {% endif %}
        {% set ns_pool.items = ns_pool.items + [{'diff': diff, 'y_korr': yield_korr}] %}
      {% endif %}
    {% endfor %}
    {% set top15 = (ns_pool.items | sort(attribute='diff'))[:15] %}
    {% set max_yield = top15 | map(attribute='y_korr') | max if top15 | count > 0 else 0 %}
    {{ max_yield | round(2) }}
  {% endif %}
{% else %}
  0
{% endif %}

Pessimistic Sensor (min)

Returns the lowest corrected yield from the 15 most similar days—lower bound of the forecast (no LOO/penalty, since a lower bound is desired here):

[+]

{% set raw_json = state_attr('sensor.pv_remaining_states', 'json') %}
{% if raw_json and raw_json != '[]' and raw_json is not none %}
  {% set data = raw_json | from_json %}
  {# Nacht-Check #}
  {% set offset_min = (now().utcoffset().total_seconds() / 60) | int %}
  {% set pv_end_utc = data[0].pv_end | default('17:30') %}
  {% set end_min_local = ((pv_end_utc.split(':')[0] | int) * 60 + (pv_end_utc.split(':')[1] | int) + offset_min) % 1440 %}
  {% if (now().hour * 60 + now().minute) > end_min_local %}
    0.0
  {% else %}
    {% set f_avg = data[0].f_avg_today_remaining | float(default=50.0) %}
    {% set f_uv_avg = data[0].uv_avg_today_remaining | float(default=0.0) %}
    {% set current_month = now().month %}
    {% set doy = now().strftime('%j') | int(default=1) %}
    {% set latitude = latitude if latitude is defined else state_attr('zone.home', 'latitude') | float(48.0) %}
    {% set lat_rad = latitude * pi / 180 %}
    {% set decl = -0.4093 * cos(2 * pi * (doy + 10) / 365) %}
    {% set dl_today = 24 / pi * acos([[(-tan(lat_rad) * tan(decl)), -1.0] | max, 1.0] | min) %}
    {% set sun_today = 0.80 + 0.20 * cos((doy - 172) * 2 * pi / 365) %}
    {% set uv_w = [0.3 + 0.4 * (f_avg / 100.0), 0.7] | min %}
    {% set ns_pool = namespace(items=[]) %}
    {% for item in data %}
      {% set dt_item = as_datetime(item.date) %}
      {% if dt_item is not none %}
        {% set item_day = dt_item.strftime('%j') | int(default=1) %}
        {% set decl_i = -0.4093 * cos(2 * pi * (item_day + 10) / 365) %}
        {% set dl_item = 24 / pi * acos([[(-tan(lat_rad) * tan(decl_i)), -1.0] | max, 1.0] | min) %}
        {% set sun_item = 0.80 + 0.20 * cos((item_day - 172) * 2 * pi / 365) %}
        {% set s_korr = (sun_today / sun_item) * (dl_today / dl_item) %}
        {% set yield_korr = item.yield_day_remaining | float(default=0) * s_korr %}
        {% set uv_hist = item.uv_avg_remaining | float(default=0) %}
        {% set diff_c = (item.h_avg_remaining | float(default=0) - f_avg) | abs %}
        {% if f_uv_avg > 0 %}
          {% set diff = diff_c * (1.0 - uv_w) + (uv_hist - f_uv_avg) | abs * 8.0 * uv_w %}
        {% else %}
          {% set diff = diff_c %}
        {% endif %}
        {% set ns_pool.items = ns_pool.items + [{'diff': diff, 'h_avg': item.h_avg_remaining | float(0), 'y_korr': yield_korr}] %}
      {% endif %}
    {% endfor %}
    {% set top15 = (ns_pool.items | sort(attribute='diff'))[:15] %}
    {% set brighter = top15 | selectattr('h_avg', 'le', f_avg) | list %}
    {% set darker = top15 | selectattr('h_avg', 'gt', f_avg) | list %}
    {% set res = 0 %}
    {% if top15 | count > 0 %}
      {% if brighter | count > 0 and darker | count == 0 %}
        {% set worst = brighter | sort(attribute='y_korr') | first %}
        {% set res = worst.y_korr * ([120 - f_avg, 5.0] | max / [120 - worst.h_avg, 5.0] | max) %}
      {% elif darker | count > 0 and brighter | count == 0 %}
        {% set res = darker | map(attribute='y_korr') | min %}
      {% else %}
        {% set res = top15 | map(attribute='y_korr') | min %}
      {% endif %}
    {% endif %}
    {{ res | round(2) }}
  {% endif %}
{% else %}
  0
{% endif %}

The result

You don't get a theoretical estimate, but a forecast based on the real performance of your hardware under your individual site conditions.

Advantage: The longer the system runs, the more precise it becomes, as the "pool" of historical twin days is constantly growing.

Version 0.3 update

Temperature, precipitation and automatic parameter retuning are now part of the HACS integration. The SQL-only example above remains a compact minimal variant for users who want to rebuild the basic idea manually.

Conclusion

PV History Forecast is the smart alternative to external forecasting services for anyone who uses Home Assistant and already has historical PV yield data. The integration learns from the past of your own system and thus provides a significantly more system-specific residual forecast - without an external API, without registration, directly from your own HA database.

^{({{pro_count}})}

Rate Post:
{{percentage}} % positive

^{({{con_count}})}

THANK YOU for your review!

➨ Home Assistant Database MySQL vs. SQLite | ➦ Home Assistant | Home Assistant News / planned features ➨

Questions / Comments

[6 Comments in Deutsch]