kostal-parse-actuals

-kostal-parse-actuals.md

First version: 2024.38.1
Current version: 2024.38.1

Description

I'm sharing my research and development script, the knowledge could be used to create a more mature script using Perl or Python, or your preferred development language.

The purpose of this script is to parse log data from Kostal PV inverters. The primary objective is to learn about the data available in the log and how it can be used, and to summerise the data, and provide data insights. The secondary objective is to produce summerised results and compare them with the Kostal Solar Portal or other PV analytics portals to verify the correctness of the script logic and mathematics. I've hosted an intro video on YouTube.

Screenshots

Here is a sample screenshot of the summary fields the awk script produces:

Here is a sample screeshot of the fields the awk script produces:

Usage

# invoke gawk with the script and parse 2024-07.csv
gawk -f kostal-parse-actuals.awk 2024-07.csv | column -Lt | less -S

# alt using stdin redirection
< 2024-07.csv gawk -f kostal-parse-actuals.awk | column -Lt | less -S

# parse multiple files, chronological order is important
gawk -f kostal-parse-actuals.awk 2024-07.csv 2024-08.csv | column -Lt | less -S

# example of viewing inverter raw data
# LINES env var controls at which height (terminal lines) the header should repeat
# try to remove the LINES override on your terminal, column had issues detecting terminal height on my terminal
file=2024-07.csv; < $file tail -n+8| LINES=41 column --separator $'\t' -t --table-columns "$(< $file sed -n 's/\t/,/g;7,7p;7q')" --table-header-repeat |less -S

Calculating kWh

A number of fields in the Kostal log data use the unit watt. The first field in each record type is a Unix timestamp (elapsed time in seconds), which can be used to calculate how many seconds have elapsed between one record and another.

A Joule: The work required to produce one watt of power for one second, or one watt-second (W⋅s) (compare kilowatt-hour, which is 3.6 megajoules). This relationship can be used to define the watt.

With this knowledge, given X elapsed seconds and Y watts, one can calculate kW seconds, minutes and hours (kWh) and so on for a given period of time.

1 J is 0.001 kW s (kilowatt seconds)
1 J is 1.667×10^-5 kW min (kilowatt minutes)
1J is 2.778×10^-7 kW h (kilowatt hours)

The script uses the constant kWh_in_joules = 2.778×10^-7

The formula for calculation of kWh looks like this:
Z = Y * kWh_in_joules * X
Where Z is kWh, Y is watts, X is seconds

See the docs in the script for further info and the implementation details.

Alternative

An alternative to using the kWh_in_joules constant is to divide watts by 1000, then by 3600 joules aka 3.6 kJ (kilojoules) and multiply by ts_delta. However in testing, this formula yields slightly less precision aka a smaller sum ( Watt / 1000 / 3600 ) * ts_delta I went with the formula that yields the larger sum.

How can I run the script?

You need access to a terminal with GNU awk (gawk) installed.

Linux

You probably already have gawk installed. If you don't, use your distro's package manager to install it.

Windows

You can use Cygwin [INSTALL] or a WSL distro [HOWTO].
Naturally, you can use your preferred windows ssh client to ssh to a remote host that has gawk available.

OSX / Mac

I didn't test the awk script on OSX but AFAIK Mac have their own flavour of utilities based on BSD versions. I think my script won't work with the default OSX version of awk.
One can use [MacPorts] or [Homebrew] to install gawk. There may be other methods too. DYOR and YMMV!
💡💬 Please leave a comment with what works for you, so others may benefit.

Intro video

I've hosted an intro video on YouTube, as its larger than the gist max supported file size.

kostal-parse-actuals.awk

# Released under MIT License
#
# Copyright (c) 2024 kyle0r
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.
#
# ----
#
# This is a research and development script, the knowledge could be used to
# create a more mature perl or python script.
#
# The purpose of this script is to parse log data from Kostal PV inverters. The
# primary objective is to learn about the data available in the log and how it
# can be used, and to summerise the data, and provide data insights.
# The secondary objective is to produce summerised results and compare them with
# the Kostal Solar Portal or other PV analytics portals to verify the
# correctness of the script logic and mathematics.
#
# This script was developed with log data from a Kostal PLENTICORE plus 8.5
# MC and IOC version 01.75
# HW version 0201
# The inverter and local grid is 3-phase and there are multiple DC and AC phase
# fields for the relevant dimensions and metrics in the data. Some inverters may
# have fewer phases and therefore fewer data fields.
# 
# 💡 This script would need to be adapted to handle an inverter with a different
# field layout, as awk uses an integer index to reference the fields.
#
# Logic:
#
# The log data records (rows) are snapshots in time (actuals) of the inputs and
# outputs of the inverter. The data log output from Kostal inverters provides
# numerous fields (columns) of data in the unit of watts.
#
# It is possible to calculate the kWh unit by inputting the watt fields into a
# formula: watts multiplied by joules per kWh multiplied by the time between the
# previous snapshot (record row). 
#
# The log data contains 2 types of records
# 1) records that capture snapshots of the actual inputs and outputs of the unit
# 2) records that capture running totals
#
# This script parses the first record type
#
# For the inverter model mentioned, record type 1 is logged at intervals of
# circa 300 seconds (5 mins). A number of the fields in the data use the unit of
# watts. The first field in any type record is a unix timestamp (in seconds)
# which can be used to calculate how many seconds have elapsed between one
# record and another.
#
# With this knowledge, given X elapsed seconds and Y watts, one can calculate 
# kW seconds, minutes and hours (kWh) and so on for a given period of time.
#
# The script logic assumes that for a given record (row), the kWh for a given
# field can be calculated from the time elapsed between the current and previous
# record.
# For example, if a record/row had a field with an actual value of 100 watts
# (Y), one can determine the number of elapsed seconds between the current and
# previous record (X), then use the kWh formula to calculate the kWh (Z) within
# the X time interval.
#
# The script uses a constant named kWh_in_joules. See the code for more details.
#
# The formula for calculation of kWh looks like this:
#
# Z = Y * kWh_in_joules * X
#
# Where Z is kWh, Y is watts, X is seconds

BEGIN {
  # input and output fields are seperated by tabs
  FS="\t"
  OFS="\t"
  
  # output the header row (field names)
  print "timestamp", "ts_delta", "sum_DC1and2_W", "DC3_W", "sum_AC_W", "sum_ext_pow_sensor", "sum_self_consumption", "HC1-BAT", "HC2-PV", "HC3-GRID", "SOC"

  matches=0
  ts_delta=300 # used for the first match, where no previous record exists

  # kWh_in_joules constant used to calculate kWh
  # https://www.wolframalpha.com/input?i=1+joule+to+kWh
  # A Joule: The work required to produce one watt of power for one second, or
  # one watt-second (W⋅s) (compare kilowatt-hour, which is 3.6 megajoules).
  # This relationship can be used to define the watt.
  # cite: https://en.wikipedia.org/wiki/Joule
  kWh_in_joules=2.778*10^-7
  
  # An alternative to using kWh_in_joules constant is to divide watts by 1000,
  # then by 3600 joules aka 3.6 kJ (kilojoules) and multiply by ts_delta.
  # However in testing, this formula yields slightly less precision aka a
  # smaller sum
  # ( Watt / 1000 / 3600 ) * ts_delta
  # I went with the formula that yields the larger sum
}

# regex to match only records of type 1
/^[0-9]{10}\t[0-9]/ {
  # after the first match we can calculate the ts_delta
  if (matches > 0) {
    ts_delta = $1 - ts
  } else {
    first_ts = $1
  }

  ts=$1 # unix timestamp
  sum_DC1and2_W=$4+$9 # sum of DC1 and DC2 watts, the PV DC generation (watts)
  DC3_W=$14 # the battery DC charge and discharge (watts)
  sum_AC_W=$19+$23+$27 # sum of the 3 AC phases (watts)
  sum_ext_pow_sensor=$39+$40+$41 # sum of the external power monitor phases (watts)
  sum_self_consumption=$42+$43+$44 # sum of self consumption each phase (watts)
  HC1=$45 # battery consumption (watts)
  HC2_PV=$46 # PV consumption (watts)
  HC3_GRID=$47 # Grid consumption (watts)
  SOC=$48 # Battery State of Charge (SOC) (percentage)

  # kWh sum of PV DC power generation metrics
  DC1and2_kWh = ( sum_DC1and2_W * kWh_in_joules * ts_delta )
  sum_DC1and2_kWh+=DC1and2_kWh
  
  # kWh sum of battery metrics
  # the inverter is using DC3 for the battery
  # positive values represent battery consumption
  # negative values represent battery charging
  if (DC3_W > 0) { 
    DC3_kWh = ( DC3_W * kWh_in_joules * ts_delta )
    sum_DC3_kWh+=DC3_kWh
  } else { 
    DC3_neg_kWh = ( DC3_W * kWh_in_joules * ts_delta )
    sum_DC3_neg_kWh+=DC3_neg_kWh
  }

  # kWh sum of AC power generation
  # sum_AC_W captures the watts inverted from DC to AC 
  if (sum_AC_W > 0) { 
    AC_kWh = ( sum_AC_W * kWh_in_joules * ts_delta )
    sum_AC_kWh+=AC_kWh
  # I have observed very minor negative values
  # I don't know exactly the condition in which negative AC occurs?
  # It might be related to the inverter switching and balancing between PV
  # and battery inversion, and grid consumption
  } else { 
    AC_neg_kWh = ( sum_AC_W * kWh_in_joules * ts_delta )
    sum_AC_neg_kWh+=AC_neg_kWh
  }
  
  # kWh sum of external power sensor
  ext_pow_sensor_kWh = ( sum_ext_pow_sensor * kWh_in_joules * ts_delta )
  sum_ext_pow_sensor_kWh+=ext_pow_sensor_kWh
  
  # kWh sum of self consumption
  self_consumption_kWh = ( sum_self_consumption * kWh_in_joules * ts_delta )
  sum_self_consumption_kWh+=self_consumption_kWh
  
  # kWh sum of battery consumption
  HC1_kWh = ( HC1 * kWh_in_joules * ts_delta )
  sum_HC1_kWh+=HC1_kWh

  # kWh sum of PV consumption
  HC2_PV_kWh = ( HC2_PV * kWh_in_joules * ts_delta )
  sum_HC2_PV_kWh+=HC2_PV_kWh

  # kWh sum of grid consumption
  HC3_GRID_kWh = ( HC3_GRID * kWh_in_joules * ts_delta )
  sum_HC3_GRID_kWh+=HC3_GRID_kWh
  
  print strftime("%FT%H:%M:%S_%Z", ts), ts_delta, sum_DC1and2_W, DC3_W, sum_AC_W, sum_ext_pow_sensor, sum_self_consumption, HC1, HC2_PV, HC3_GRID, SOC

  matches++
}

END {
  print ""
  print "---SUMMARY---"
  print ""
  printf "Elapsed_days:%s%.0f%s", OFS, ($1 - first_ts) * 1.157*10^-5, ORS
  print "Start_Month:", strftime("%Y-%B", first_ts)
  print "End_Month:", strftime("%Y-%B", $1)
  print ""
  print "sum_DC1and2_kWh: ", sum_DC1and2_kWh
  print "sum_DC3_kWh: ", sum_DC3_kWh
  print "sum_DC3_neg_kWh: ", sum_DC3_neg_kWh
  print "DC3_delta_kWh",  sum_DC3_neg_kWh + sum_DC3_kWh
  print "sum_AC_kWh: ", sum_AC_kWh
  print "sum_AC_neg_kWh: ", sum_AC_neg_kWh
  print "DC-AC_loss_%:", 100 * ( sum_DC1and2_kWh - sum_AC_kWh ) / sum_DC1and2_kWh
  home_consumption_kWh = sum_HC1_kWh + sum_HC2_PV_kWh + sum_HC3_GRID_kWh
  printf("home_consumption_kWh:%s%.2f%s", OFS, home_consumption_kWh, ORS)
  print "sum_ext_pow_sensor_kWh: ", sum_ext_pow_sensor_kWh
  print "sum_self_consumption_kWh: ", sum_self_consumption_kWh
  print "sum_self_consumption_kWh_alt: ", sum_HC1_kWh + sum_HC2_PV_kWh
  print "sum_HC1_kWh: ", sum_HC1_kWh 
  print "sum_HC2_PV_kWh: ", sum_HC2_PV_kWh 
  print "sum_HC3_GRID_kWh: ", sum_HC3_GRID_kWh
  feed_in_kWh = sum_AC_kWh - ( sum_HC1_kWh + sum_HC2_PV_kWh )
  print "feed_in_kWh: ", feed_in_kWh
  print "feed_in_kWh_alt_calc: ", sum_AC_kWh - sum_self_consumption_kWh
  print ""
  printf("home_consumption_grid_%%:%s%.0f%s", OFS, ( sum_HC3_GRID_kWh * 100 / home_consumption_kWh), ORS)
  printf("home_consumption_battery_%%:%s%.0f%s", OFS, ( sum_HC1_kWh * 100 / home_consumption_kWh), ORS)
  printf("home_consumption_PV_%%:%s%.0f%s", OFS, ( sum_HC2_PV_kWh * 100 / home_consumption_kWh), ORS)
  print ""
  yield_kWh = sum_DC3_neg_kWh * -1 + sum_HC2_PV_kWh + feed_in_kWh
  print "yield_kWh:", yield_kWh 
  yield_MWh = yield_kWh / 1000
  printf("yield_MWh:%s%.2f%s", OFS, yield_MWh, ORS)
  print ""
  printf("self_consumption:%s%.2f%s", OFS,  yield_kWh - feed_in_kWh, ORS)
  printf("self_consumption_%%:%s%.0f%s", OFS,  ( yield_kWh - feed_in_kWh ) * 100 / yield_kWh, ORS)
  printf("feed_in_kWh:%s%.2f%s", OFS, feed_in_kWh, ORS)
  printf("feed_in_%%:%s%.0f%s", OFS, feed_in_kWh * 100 / yield_kWh, ORS)
  print ""
  printf("Autarky_%%:%s%.2f%s", OFS, (sum_HC1_kWh + sum_HC2_PV_kWh) * 100 / home_consumption_kWh, ORS)
}

LICENSE

Released under MIT License

Copyright (c) 2024 kyle0r

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.