"Export to csv" feature for usage data of the online account

Fantastic to hear this, @joanx. Was the spike at the beginning of December this year during the cold snap?

The spike started with the cold snap when I finally relented on the storage heater. It ended when I turned it off, for good. The last couple of days looked like this…

...similar to last year, which I basically spent £10 on electricity, and my room never got above 13°

I’ve got a 20 or 30 year old storage heater, that warms up nicely, but does nothing for the room temperature.

PS. The only device on the OP circuit is a single storage heater.

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 - one sample included - if yours isn’t the same the code will fail.

The code will output a CSV to the same directory as stated in the cursor entry point.

# Just CTRL + A and then CRTL + C the whole page 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.

$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"}]}'

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

$Rawdata = $gas.split("},{",[system.StringSplitOptions]::RemoveEmptyEntries)
$rawdata |% `
{
    if ($_ -match 'readingDateTime')
    {
        $dateString = ($_.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
    }
    elseif ($_ -match 'readingType')
    {
        $obj.Type =  $_.split(':')[1]
    }
    elseif ($_ -match 'gasVolume')
    {
        $obj.value = $_.split(':')[1]
        $obj
    }
} | export-csv Gas.csv

$Rawdata = $electric.split("},{",[system.StringSplitOptions]::RemoveEmptyEntries)
$rawdata |% `
{
    if ($_ -match 'readingDateTime')
    {
        $dateString = ($_.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
    }
    elseif ($_ -match 'readingType')
    {
        $obj.Type =  $_.split(':')[1]
    }
    elseif ($_ -match 'meterRegisterReading')
    {
        $obj.value = $_.split(':')[1]
        $obj
    }
} | export-csv Electric.csv

Wow @Yinmeout, 

Assuming this code does as you’ve described, what a fantastic hack to get around the current absence of this feature. Impressive!

I would bet that my OVO colleagues would suggest a warning on using code snippets like this just in case they lead to unexpected outcomes. For example if you run this code in a PowerShell terminal, and you’re not sure of the code yourself, are you exposing your machine, and any files or folders in it, to the result of the code being executed?  

Potentially, yes. Which is why it shouldn’t be run as Admin - standard privileges are more than sufficient for this particular code.

It appears that this code is also written in such a way that it doesn’t attempt to access anything other than stdin and spits out a CSV file. But a warning about being cautious nonetheless is appropriate.

Yeah what he said - the script is entirely self contained - which is why you need to paste the data into it and save before execution. Once its finished processing it will attempt to write to disk, so you need to be pointing to a directory you can write to as yourself (as opposed to admin which can write anywhere. But Blastoise186 is right, you shouldn’t run anything as admin.

I’ve re-written the script to cope with missing data and a KW conversion factor as well as calculating the actual daily usage as opposed to reporting what the meter says each day and I split the data in a different way which makes the code parse the data in logical structures as opposed to needing to parse each string and figure out what it is.

I’ll post it up in a bit.

# Just CTRL + A and then CRTL + C the whole page 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.

$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

Fancy writing this up as a new topic, @Yinmeout? I will add my disclaimer to it. 

Hi Tim, what does that involve?