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

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.