Blog

Graphing your energy usage in excel charts or similar by converting usage data into a CSV format

  • 30 January 2024
  • 12 replies
  • 228 views
Y
Rank
Userlevel 1

I was interested to understand how our energy usage fluctuates related to temperature to enable greater accuracy of cost prediction. Houses are just leaky heat boxes when all said and done.

So use data in excel requires importing it as a comma delimited file or CSV. To obtain the RAW data in the first place this thread explains what do to get it.

Once you have the data, it will come in the format of a series of meter readings on a given day. Really though, the data wants to be in a format where you can see how many kilowatts were consumed on a given day.

For electricity this pretty straight forward, since that is measured in KW anyway. You just need to subtract yesterdays reading from todays reading and the remainder is what you used. 

For gas though we need to convert m³ into KW for which there is a conversion factor which includes a correction. The correction varies each day depending on the potency of the gas you are being supplied. Also my meter sometimes skips reporting and then I get a colossal figure because the next report has missed / aggregated all the missing days use.

 

What’s really needed then, is a program to take the raw data and massage it into two csv outputs both in kw where one line entry pertains to one days use. This is what the program below does.

 

If you use a Windows 10 PC and understand any PowerShell this code can be used to generate CSV’s from the RAW data as posted earlier in this thread. The data is expected in a given format - a single day sample of the expected data type is included - if yours isn’t the same the code will not be able to understand it.

 

# Just CTRL + A and then CRTL + C the whole html page generated from the attached post and paste it between the $Gas = ‘’ and $Electric = '' apostrophes with no spaces - then delete the leading and trailing square bracket the web wraps the data within.

Save the whole file (now including your pasted readings) and save it.

Then run it. If all went well you will get a gas.csv and an electric.csv in the same folder as the powershell cursor location.

 

$gas = '{"readingDateTime":"2024-01-25T00:00:00","readingType":"SmartReading","gasVolume":4080.959}’

$electric = '{"readingDateTime":"2024-01-25T00:00:00","readingType":"SmartReading","tiers":[{"meterRegisterReading":13585.247,"timeOfUseLabel":"anytime"}]}'

[int]$calorificValue = 38.5

function Convert-KW
{
    param
    (
        [double]$in
    )
    $out = $in * $calorificValue * 1.02264 / 3.6
    return $out
}


[int]$totalDays = 0
[bool]$nonConsec = $false
[datetime]$PrvDate = 0
$PrvValue = 0

$obj = [pscustomobject]@{
    Date = $formatDate
    Value = $vol
}

$Rawdata = $gas -split '},{'
$fixedMember = $Rawdata[-1].TrimEnd('}')
$Rawdata = $Rawdata | select -SkipLast 1
$Rawdata += $fixedMember
$rawdata |% `
{
    $e = $_.split(',')
    $dateString = ($e[0].split(':',2)[1]).trim('"')
    [datetime]$formatDate = New-Object DateTime
    [DateTime]::TryParseExact($dateString, 
                                "yyyy-MM-ddTHH:mm:ss",
                                [System.Globalization.CultureInfo]::CurrentUICulture,
                                [System.Globalization.DateTimeStyles]::None,
                                [ref]$formatDate) | out-null
    $obj.Date = $formatDate.Date.ToShortDateString()
    if ($PrvDate -ne 0)
    {
        $span = New-TimeSpan -Start $formatDate -End $prvdate
        if ($span.TotalDays -gt 1)
        {
            $nonConsec = $true
            $totaldays = $span.TotalDays
        }
        else
        {
            $nonConsec = $false
        }
    }
    
    $prvdate = $formatDate

    $value = Convert-KW $($e[2].split(':')[1])
    if ($PrvValue -ne 0)
    {
        $UseValue = $PrvValue - $Value
        if ($nonConsec)
        {
            $valueday = $UseValue / $totaldays
            $refDate = get-date $obj.Date
            for ($i = $totaldays;$i -ge 0;$i--)
            {
                if ($i -eq $totaldays)
                {
                    $obj.value = $valueday
                }
                else
                {
                    $obj.date = (($refDate).AddDays($i)).ToShortDateString()
                    $obj.value = $valueday
                    $obj
                }
            }
        }
        else
        {
            $obj.value = $UseValue
            $obj
        }
        $PrvValue = $value
    }
    else
    {
        $PrvValue = $value
    }
} | export-csv Gas.csv

$Rawdata = $electric -split '},{'
$i = 0
$rawdata |% `
{
    $e = $_.split(',')
    $dateString = ($e[0].split(':',2)[1]).trim('"')
    [datetime]$formatDate = New-Object DateTime
    [DateTime]::TryParseExact($dateString, 
                                "yyyy-MM-ddTHH:mm:ss",
                                [System.Globalization.CultureInfo]::CurrentUICulture,
                                [System.Globalization.DateTimeStyles]::None,
                                [ref]$formatDate) | out-null
    $obj.Date = $formatDate.Date.ToShortDateString()
    
    if ($i -ne 0)
    {
        $obj.value = $PrvValue - $e[2].split(':')[2]
        $obj
    }
    $PrvValue = $e[2].split(':')[2]
    $i++
} | export-csv Electric.csv

 

You can then import these to excel and chart the data as normal.

Something like that.

12 replies

Emmanuelle_OVO
Rank
Userlevel 7

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? 

Renewable Energy? Big Fan.
Blastoise186
Rank
Userlevel 7
Badge +1

We already did. Works great. :P

Securing energy by zapping security bugs... For that is The Blastoise Way!
Tim_OVO
Rank
Userlevel 7

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.

Carbon neutral - we need a community to get there! My green tech: Aclara SGM 1411-B smart meter, Chameleon IHD6
Y
Rank
Userlevel 1

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,
        [datetime]$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,
        [string]$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(';',[System.StringSplitOptions]::RemoveEmptyEntries)
        $date = get_time $data[0] "dd.MM.yyyy HH:mm:ss"
        $tempobj = [pscustomobject]@{
            date = $date
            Temp = [float]$data[1]
            FiletimeUTC = $date.ToFileTimeUtc()
        }
        [void]$temp_data.add($tempobj)
    }
}

# Coagulate Temp Data into an array of fields.
$timepoints = [math]::Floor((New-TimeSpan -start $temp_data[0].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_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_data[$index].temp
        $recordTime = $temp_data[$index].FiletimeUTC
        $index++
        $divisor++
    }
    [void]$temperature_data.add($start_date,$([math]::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 = [pscustomobject]@{
    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

Something like that.
Y
Rank
Userlevel 1

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

 

Gas and Electric use in KW per day chart.

 

Something like that.
BPLightlog
Rank
Userlevel 7
Badge +5

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. 

Independent volunteer - Sitting in a Faraday cage, waiting for a signal
Y
Rank
Userlevel 1

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

Something like that.
Firedog
Rank
Userlevel 7
Badge

That’s what this code does.

 

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,

        [datetime]$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,

        [string]$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(';',[System.StringSplitOptions]::RemoveEmptyEntries)

        $date = get_time $data[0] "dd.MM.yyyy HH:mm:ss"

        $tempobj = [pscustomobject]@{

            date = $date

            Temp = [float]$data[1]

            FiletimeUTC = $date.ToFileTimeUtc()

        }

        [void]$temp_data.add($tempobj)

    }

}

# Coagulate Temp Data into an array of fields.

$timepoints = [math]::Floor((New-TimeSpan -start $temp_data[0].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_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_data[$index].temp

        $recordTime = $temp_data[$index].FiletimeUTC

        $index++

        $divisor++

    }

    [void]$temperature_data.add($start_date,$([math]::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 = [pscustomobject]@{

    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 …

 

Noel | I have no official status; I'm just a volunteer who comes here to help other customers. My gear: Aclara SGM 1416-B Electricity-only E7 meter; Chameleon IHD3-PPMID-AAA
Y
Rank
Userlevel 1

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 !

Something like that.
J
Rank
Badge

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?

Blastoise186
Rank
Userlevel 7
Badge +1

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...

Securing energy by zapping security bugs... For that is The Blastoise Way!
Y
Rank
Userlevel 1

This is the PowerShell editor chap.

 

 

Something like that.

Reply


Terms & ConditionsCookie settings