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

Thanks everyone, good to know.

Can someone set this up as a separate feature idea so we can keep this request distinct from the overall CSV download feature?

People seem to be downloading my “Power move” usage calculator, so I’ve updated it (details here)

As requested by Tim, I have now added this as a separate idea here:

https://forum.ovoenergy.com/ideas/download-a-month-s-worth-of-half-hourly-usage-data-16147

Upvote please! 

Voted

Thanks to everyone contributing here.  This is so helpful to me. Using this discussion I was able to confirm I am actually being billed correctly for my tariff, E10.

And I thought my data from last winter was lost because it was SSE, I never thought I could download it (nor wanted to), but when I saw Ovo let me compare usage to a year ago, but I couldn’t and was frustrated. But Bright/glowmrkt has given me all that data back to the day the meter was installed. And their Temperature/Humidity sensor looks like exactly what I was trying to find on Amazon last winter.

Anyway, it’s enabled me to draw this pretty chart, which visualises what I thought was happening. Though last December was 3° cooler on average than this year, I am still keeping my home much warmer with a convection heater than with my 30 year old storage heater and for a lot less power. I turned on the storage heater on 25/11, and off again 3/12. The convection heater is getting the house 

So thanks!

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? 

Follow this link to post this as a new topic thread, a discussion rather than an idea:

Ah I see - I can do that if you want.

Hi all, I appreciate this is a very old thread, however it still pops up as the top result on google when searching for getting the raw CSV data as it’s not available on the OVO website still.

I wondered if the information here is still accurate?

I ask because:

1. When trying to access the raw JSON data through the links suggested by ​@Firedog here https://smartpaymapi.ovoenergy.com/orex/api/meter-readings/3198692 and here  https://smartpaymapi.ovoenergy.com/rlc/rac-public-api/api/v5/supplypoints/electricity/{MPAN}/meters/{MSN}/readings I just get the response

message    "OAuth plugin - No refresh_token present"

2. Guy Lipman’s site for Smart Meter Data is no longer active, although the source for it is available on GitHub, I wondered if there was anywhere else that had a simliar easy access way to get this?

Many thanks!

​@X10 You said it! 
    

On the other hand, that is a very new feature of OVO’s APIs and the subject of the last few posts in this thread:

It’s also a long time since Guy Lipman’s handy page was taken down. I still use his source (data.n3rgy.com) for my own data, though, but it’s not so straightforward. You may want to follow the thread I referred to: some have cracked the new authorization code, and I hope ​@MikeWilliams is on the verge of releasing his updated utility. Watch that space!

I have just finished the changes to my app for the new login and authentication methods.

I have some more testing to do before I release it.

I have today downloaded all of my data, which goes back to 2017 in a single pass.

​@Firedog - Thanks for that. Yes it seems that depending on the question you ask Google this old thread is the most relevant result. Having said that, while doing some more rabbit-holing I discovered the other thread you just mentioned and saw Mike's link to GitHub and his most recent post shortly after he posted it.

​@MikeWilliams - Thank you for the update and your work on this project.

Thanks both!

I have just released V1.0.4

You can download a copy of it at
https://github.com/MikeWilliams-UK/My-Ovo-Data/releases

Regards

Mike Williams