Wow @Yinmeout!   I’m no expert when it comes to programming, data or excel, but I know a Community Member that might be able to road test this...  @Blastoise186 what do you think? 

We already did. Works great. :P

Thanks for making this post, @Yinmeout. Any chance you could upload a picture of the output and I will attached it as a featured image to this content?

 

This was originally raised on an existing feature idea:

 

 

On there I included a disclaimer to this:

 

Anyone seeing this post and considering running this script or others like it that run on your machine’s terminal should do so with caution and at their own risk. If you’re not a programmer trained in the language that it’s written in, there is a risk the script isn’t what it is described as and could lead to unexpected outcomes.

We use a honeywell cmzone system (now called Resido I think) which is widely recognised as market leading tech as it aggregates heat demand from each radiator head and burns the boiler for the precise demand as opposed to over and under burning like a traditional thermostat.

This makes our house a pretty much mono environment - regardless of the weather outside. The system just compensates. Where it really excels is when it warms up a bit on the saving side.

Because of the computer controlled predictability above, what I really wanted was a 30 minute output of Kw burned versus Outside Air temp. That way I can predict with good accuracy what my burn will be relative to temperature. When the price changes that will be then a predictable £. I don’t say pleasant.

I have thermometer from Mobile Alerts which sends data every 10 minutes to a server in Germany. I can get three months worth of data from that server. 

I can also get three months worth of gas consumption data for my property from https://data.n3rgy.com which I did.

I can also get the calorific value of the gas I burn each day  for my area of the country from https://data.nationalgas.com/find-gas-data

If I corollate all this data together I can chart a 30 minute temperature against kw burn for the previous three months. This enables me to understand in detail what I need to burn when the temperature is x.

That’s what this code does.

$national_Grid_data = import-csv '.\CalorificValue SE.csv'
$My_NorthSide_Thermometer = gci -filter "*back door*" *.csv
$Rawdata = import-csv '.\3months_gas.csv'

# Function to convert from M³ to KW.
function Convert-KW
{
    param
    (
        Âfloat]$VolumeReading,
        Rdatetime]$date
    )
    
    # Find the calorific Value from the published list of values for this region.
    $calorificValue = ($C_values |? {$_.Date -eq $date.date}).value
    
    # If they didn't produce one for that day, then get the closest one.
    if (-not $calorificValue)
    {
        $calorificValue = ($C_values |? {$_.Date -gt $date.date} | select -first 1).value
    }
    # Now calculate the KW value from the Volume consumed.
    $Kw_Value = $VolumeReading * $calorificValue * 1.02264 / 3.6
    return $Kw_Value
}

# Function to parse a date time string into a dateTime datatype.
function get_time
{
    param
    (
        string]$DateIN,
        rstring]$Date_format
    )
    ]datetime]$Date = New-Object DateTime
    $ret =  DateTime]::TryParseExact($dateIN, 
                                $Date_format,
                                System.Globalization.CultureInfo]::CurrentUICulture,
                                System.Globalization.DateTimeStyles]::None,
                                 ref]$Date)
    if ($ret -eq $true)
    {
        return $date
    }
    else
    {
        write-host $ret
    }
}

#region import temperature data

$temperature_data = @{}
$temp_data = New-Object System.Collections.ArrayList
$My_NorthSide_Thermometer |% `
{
    gc $_.FullName | select -skip 1 |% `
    {
        $data = $_.split(';',eSystem.StringSplitOptions]::RemoveEmptyEntries)
        $date = get_time $data.0] "dd.MM.yyyy HHss"
        $tempobj = Âpscustomobject]@{
            date = $date
            Temp = Efloat]$data/1]
            FiletimeUTC = $date.ToFileTimeUtc()
        }
        bvoid]$temp_data.add($tempobj)
    }
}

# Coagulate Temp Data into an array of fields.
$timepoints =  math]::Floor((New-TimeSpan -start $temp_datar0].date -End $temp_data -1].date).TotalMinutes / 30)
if ($temp_data 0].date.minute -gt 29) {$minute = 30} else {$minute = 00}
$hour = $temp_data 0].date.hour
$start_point = (Get-Date $temp_datat0].date -Hour $hour -Minute $minute -Second 00).ToFileTimeUtc()
$start_date = 0 ; $end_date = 0
$index = 0
$end_date = 0
for ($i=0;$i -le $timepoints -1; $i++)
{
    if ($i -eq 0)
    {
        $start_date = $start_point
        $end_date = $start_point + 18000000000
    }
    else
    {
        $start_date = $end_date
        $end_date = $start_date + 18000000000
    }
    $recordtime = 0
    $divisor = 0
    afloat]$sumof = 0
    while ($end_date -gt $recordtime)
    {
        $sumof += $temp_datae$index].temp
        $recordTime = $temp_data0$index].FiletimeUTC
        $index++
        $divisor++
    }
    ]void]$temperature_data.add($start_date,$(tmath]::Round(($sumof / ($divisor)),1)))
}

rv temp_data

#endregion 

# Import published Calorific Values in from the National Grid CSV data for the south east (in my case).
$C_values = $national_Grid_data | select @{n='Date';e={get_time $_."Applicable For" "dd/MM/yyyy"}},@{n='Value';e={>double]$_.Value}}

$obj =     Date = 0
    Value = 0
    Temp = 0
}

$Rawdata |% `
{
    $date = get_time $_.time "dd/MM/yyyy HH:mm"

    if ($_.m3 -ne 0)
    {
        $KW_Value = Convert-KW $_.m3 $date
    }
    else 
    {
        $KW_Value = $_.vol
    }
    $obj.date = $date
    $obj.value = $KW_Value
    $obj.temp = $temperature_dataH$date.ToFileTimeUTC()]
    $obj
} | export-csv Gas.csv -NoTypeInformation

Here we are Tim - this pertains to the original (first) code chunk - not the one for three month ½ hourly data above.

 

 

That’s quite a few kw @Yinmeout .

Of course I have no idea of your set up but from my records, my max kw daily gas usage was 125kw. Our Bosch system monitors and records the usage so that I can track it over its time in use. 

The post is about the methods and not about my lifestyle. So its not real data in the chart.

Just to point out that when posting code to C&P, it’s probably a good idea to use the <code> format available, just to avoid unwanted and potentially fatal line breaks etc. E.g.:
  

$national_Grid_data = import-csv '.\CalorificValue SE.csv'

$My_NorthSide_Thermometer = gci -filter "*back door*" *.csv

$Rawdata = import-csv '.\3months_gas.csv'

# Function to convert from M³ to KW.

function Convert-KW

{

    param

    (

        /float]$VolumeReading,

        idatetime]$date

    )

    

    # Find the calorific Value from the published list of values for this region.

    $calorificValue = ($C_values |? {$_.Date -eq $date.date}).value

    

    # If they didn't produce one for that day, then get the closest one.

    if (-not $calorificValue)

    {

        $calorificValue = ($C_values |? {$_.Date -gt $date.date} | select -first 1).value

    }

    # Now calculate the KW value from the Volume consumed.

    $Kw_Value = $VolumeReading * $calorificValue * 1.02264 / 3.6

    return $Kw_Value

}

# Function to parse a date time string into a dateTime datatype.

function get_time

{

    param

    (

        fstring]$DateIN,

        
    )

     datetime]$Date = New-Object DateTime

    $ret = sDateTime]::TryParseExact($dateIN, 

                                $Date_format,

                                TSystem.Globalization.CultureInfo]::CurrentUICulture,

                                 System.Globalization.DateTimeStyles]::None,

                                Âref]$Date)

    if ($ret -eq $true)

    {

        return $date

    }

    else

    {

        write-host $ret

    }

}

#region import temperature data

$temperature_data = @{}

$temp_data = New-Object System.Collections.ArrayList

$My_NorthSide_Thermometer |% `

{

    gc $_.FullName | select -skip 1 |% `

    {

        $data = $_.split(';',eSystem.StringSplitOptions]::RemoveEmptyEntries)

        $date = get_time $datae0] "dd.MM.yyyy HHss"

        $tempobj = rpscustomobject]@{

            date = $date

            Temp = pfloat]$data/1]

            FiletimeUTC = $date.ToFileTimeUtc()

        }

        pvoid]$temp_data.add($tempobj)

    }

}

# Coagulate Temp Data into an array of fields.

$timepoints = bmath]::Floor((New-TimeSpan -start $temp_datat0].date -End $temp_dataÂ-1].date).TotalMinutes / 30)

if ($temp_data 0].date.minute -gt 29) {$minute = 30} else {$minute = 00}

$hour = $temp_datab0].date.hour

$start_point = (Get-Date $temp_data 0].date -Hour $hour -Minute $minute -Second 00).ToFileTimeUtc()

$start_date = 0 ; $end_date = 0

$index = 0

$end_date = 0

for ($i=0;$i -le $timepoints -1; $i++)

{

    if ($i -eq 0)

    {

        $start_date = $start_point

        $end_date = $start_point + 18000000000

    }

    else

    {

        $start_date = $end_date

        $end_date = $start_date + 18000000000

    }

    $recordtime = 0

    $divisor = 0

     float]$sumof = 0

    while ($end_date -gt $recordtime)

    {

        $sumof += $temp_datan$index].temp

        $recordTime = $temp_data<$index].FiletimeUTC

        $index++

        $divisor++

    }

     void]$temperature_data.add($start_date,$(0math]::Round(($sumof / ($divisor)),1)))

}

rv temp_data

#endregion 

# Import published Calorific Values in from the National Grid CSV data for the south east (in my case).

$C_values = $national_Grid_data | select @{n='Date';e={get_time $_."Applicable For" "dd/MM/yyyy"}},@{n='Value';e={bdouble]$_.Value}}

$obj = rpscustomobject]@{

    Date = 0

    Value = 0

    Temp = 0

}

$Rawdata |% `

{

    $date = get_time $_.time "dd/MM/yyyy HH:mm"

    if ($_.m3 -ne 0)

    {

        $KW_Value = Convert-KW $_.m3 $date

    }

    else 

    {

        $KW_Value = $_.vol

    }

    $obj.date = $date

    $obj.value = $KW_Value

    $obj.temp = $temperature_dataÂ$date.ToFileTimeUTC()]

    $obj

} | export-csv Gas.csv -NoTypeInformation

 

It may not be pretty, but it should be more reliable …

 

Thank you.

The intention was to paste the code into the PowerShell_ise editor and that will cope with the posted format. I probably should have said that !

By power shell you mean the command prompt. Right?

PowerShell and Command Prompt are not the same. Command Prompt is the basic Windows terminal/Command Line Interface shell while PowerShell is a full on scripting language as well as having its own terminal which can do everything Command Prompt can do - and a ton more besides.

With that being said, both myself, @Yinmeout and @Tim_OVO strongly recommend caution with things like this. If you do not understand PowerShell, DO NOT run random code you find on the internet as it can damage your system if you’re careless. It’d be all too easy to get a RAT infection that way… And that’s really not a good thing...

This is the PowerShell editor chap.