Sum values are wrong

[nikolajhendel]

I've spent a few hours now to try and find the issue, but can't see where it's going wrong - but sum values are being calculated wrong. This might be because I'm using a newer version of HA and therefore a different schema_version = 47.

When I look at the existing energy values in statistics (recorded directly), state and sum seem to be used different compared to how imported data is processed with your script.

Happy to supply examples if anyone has time to look.

By the way, I created the csv files manually as my inverter (sungrow) isn't supported.

[patrickvorgers]

Sure, no problem to help. Can you share your script and input data files? First check would be to check whether the csv files are generated correctly.

Can you also share some screenshots of the imported data vs the original data created by the Home Assistant integration (I presume that you have a Home Assistant integration running for the inverter)

[nikolajhendel]

Never mind - found my error (in the clear light of morning).

I ran the script again and commented out deletion of the temp tables to see what was going on in the STATS_NEW table. Reading through that I saw that the import requires accumulated values instead of daily values. I recreated my import csv files with accumulated values - imported - and everything worked!

Looking through the documentation, I can't see this being specified (it is somewhat implied with the csv fields - but I didn't catch it the first time).

All in all - appreciate the routine - only a small hickup to get it working.

IGNORE BELOW (kept if anyone else runs into this):

Thank you! Happy to share files - where do you want me to send it (I could probably attach here - nothing secret?

As for screenshots (in HAOS): Connected data:

Imported data (only have daily, that's why it's stacked): This screenshot is a good example of how the data is wrong. The imported value for solar generated is 69.9 kwh - but in HAOS it's shown as 62.5 kwh (which equals that the previous days generated pv has been subtracted, as the day before I generated 7.4 kwh)

[nikolajhendel]

and here is the underlying data screenshots from statistics db (from 2 different ha installations as I ran the import on a clone of my production environment):

Connected data (same day as above):

Imported data (same as above + day before and several days after) - again only have daily for imported data:

[patrickvorgers]

Great to read that the issue is resolved!

Could you share the script/input data files for Sungrow so that I can add is as a supported datasource?

[nikolajhendel]

of course:

To get most granular well-formed data:

1. login to isolarcloud.com
2. Select plant
3. Scroll down to statistics section (default is day) and select month
4. click export and select csv
5. This will deliver a report with daily stats containing:

• Time (dd/mm/yyyy)
• PV (kWh) - this is the PV produced
• Purchased Energy (kWh) - this is the feed-out
• Feed-in (kWh) - feed in
• Load (kWh) - total energy load

Export file: Monthly Report_PLANT_NAME_20241011100248.csv

To transform data ready for import I combined all monthly lists in one excel sheet and created 3 derived tables:

• As a note, the epoch date/time equates to UTC midnight, so change calc if the daily historical "spike" is preferred at some other time. For me in Australia it equals 10 or 11 am, so I just kept it.

Feed in: epoch field = ([time]-25569)*86400 value field = [previous day feed-in value] + [current day feed-in value]

Feed out: epoch field = ([time]-25569)*86400 value field = [previous day Purchased Energy value] + [current day Purchased Energy value]

Solar produced: epoch field = ([time]-25569)*86400 value field = [previous day PV value] + [current day PV value]

File: Full.data.converter.-.isolarcloud.export.files.to.import.csv.format.-.accumulated.values.xlsx

I then copied the derived tables into the required csv files (as per naming convention).

DB import config changes As for import config, I changed the following values (not including the sensor id's): Correction = 1.0 Cutoff new meter = 100.0 (this has nothing to do with the data format of course, but my PV output can hit 80kwh per day)

[patrickvorgers]

Thank you for the detailed information. I created the script for iSolarCloud based on the provided information. Could you verify whether it produces the correct csv files?

iSolarCloud.zip