Octopus data analysis guide - with template spreadsheet

If you'd like to analyse your Octopus Energy half hourly import and export data and didn't know where to start, this video might help. I walk you through the steps for downloading the data from your Octopus account, then show you a simple template for doing some basic processing of the data. From here you can take your analysis wherever you like!

00:00 Introduction
00:28 Spreadsheet introduction
01:01 Getting the Octopus data
02:52 Adding the data to the spreadsheet
06:44 Calculation tab
09:12 Setting up the tariff
11:11 Summing up into half-hours
15:24 Car savings
18:29 Instructions for downloading the spreadsheet
19:05 Closing thoughts

Link to the spreadsheet:

To make a copy go to File - Make a copy, then save that to your Google Drive.

Please note that Tim is not a professional consultant, just an enthusiastic amateur, so cannot reply to requests for advice or opinions on specific systems or green investment opportunities. Thank you for your understanding.

Support the channel and get £50 of credit by switching to Octopus Energy using my referral code:

Or buy us a coffee:

[Trenchfoot]

Solar and spreadsheet instruction! Rapidly catching Gary Does Solar as my go to guide channel.

[johngreenaway]

Had our GiveEnergy All-in-One system installed this week so very interested to record our data using this sheet but with a tweak to the tariff data as we use Agile. Thanks Tim for sharing your work and the excellent explanation.

[MrRojo]

Thank you so much for sharing this. I had been going crazy trying to extract the data from the bizarrely-formatted Octopus file!

[lodgegatewood]

Many thanks Tim, brilliant video and exactly the sort of thing I was after. Just a couple of tips that might help Excel users: Once you have downloaded the copy and opened it in Sheets you can use File->Download to export an Xlsx format file to your local drive. Although the graph appears correctly, Excel puts the X-axis legend along the zero line. This can be moved back to the bottom by right-clicking on the time labels, then selecting Format Axis. In the Axis Options tab of the format window, change the Axis Labels to Low.

My pv array is due to be installed next month, so I can't wait to see how my bills change :) All the best, Graham

[Sean_S]

Brilliant spreadsheet Tim, well done and thank you for all your time and effort!

[briandavis]

Tim, Great video - thanks. Some useful information. I have produced my own, admittedly less sophisticated, spreadsheet for my set up. I found that you also have to allow for the clock changes - I had devil's own job trying to work out why my formulae were failing in March, until I realised.

[geoffreycoan]

Thanks Tim for sharing the spreadsheet, and for creating it in g-sheets which makes it accessible to far more people than Excel.
I only swapped to Octopus 2 days ago (initially on the standard tariff ahead of Flux), but interesting to see that Octopus is already getting half hourly meter downloads whereas Eon only ever managed monthly. At the moment lots and lots of zeros in my data as the solar and battery means I’m basically running off-grid 🎉
Waiting for the day when I get paid for my export, looks like it’ll be about £5 a day 👍

[MrDantam]

Fantastic sheet, thanks for sharing ❤

[williamdriver]

Thanks. Just been 'transferred' to Octopus from Shell without any choice in the matter. Have a Smart meter which is too smart for my liking, so it's nice to have a look under the hud so to speak.

[appletreeslightsbymichaeld]

I was recently moved over to Octopus Energy from Bulb Energy EV tariff on the 12th April. I immediately changed to the Flux Tariff on the 13th April. At present I can only see my import data and no export, but, I have been told they can retrieve the information once the Smart meter is set up properly (Bulb never did it properly). I have had no electric bills since moving over to Octopus but I have been keeping a log of my export reading from my smart meter. It's a bit nerdy but I write down the export readings at 5:00, 16:00 and 19:00 (not getting up at 2:00 haha) so I should have a pretty accurate export account for them if they can't. They have told me end of the month it should be sorted, fingers crossed. Love the spreadsheet and hope to use when I'm fully up and running.

[lestaylor]

Great work and nice explanation - many thanks. FYI. For me on Excel I selected from A4 -> last populated cell on the calculation workpage and then CTRL + T, this creates a Table. selected 'Total Row'. Lots of benefits with creating a table but the main one is the Total row, each cell on the that row can be changed to Min, Max, Average and a lot more. Its very quick to do. I don't know if this is an option in Google sheets.

[vincentnolan]

spreadsheet doesn't have all the calculations ??

[ifgow]

Excellent spreadsheet - just what I was looking for trying to figure out "dark" import when no solar generation to get an idea of battery size needed. One odd thing is that when I downloaded it as an Excel file, the import and export columns show #Value! when there are no data in the in the D and F, or E and G columns. It's fine in Google sheets, am I missing something?? Many thanks for constructing this!

[Stickneytube]

Hi Tim, I'm finding your spreadsheet really useful but wondering how you plan to include income from winter saving sessions. Just add a new section before the final summary bit? Also, in case you know, if we export during the saving session do we get income from export rate AND saving session rate per kWh? Thanks.

[dougbamford]

Thanks for this - I've downloaded your spreadsheet.
Do you have a smart plug to get the data on your hot water usage? I've got a T-Smart but it doesn't seem to provide a record of the amount of kWH that have been used (that I can find).

[bev-chrisleeson-jones]

Such a brilliant spreadsheet; your Excel knowledge is far beyond mine, but to be honest that's not difficult! May I ask a question? Can you tell me why my import figures match the figures on my GivEnergy desktop app, but the export figures are way off; almost half of what GivEnergy tells me they are for "Grid Out". For example, your amazing spreadsheet with the data copied in from my Octopus account for the month of September 2023 is showing that we exported 148kWh, but the Grid Out data from the GivEnergy desktop app for the same period says 219kWh was exported from the battery and solar to the grid. Am I mistakenly using the wrong export data from the GivEnergy app?

[iancollyer]

Amazing spreadsheet - thanks so much! I’d been trying to guess my average pence per KWh exported over 24hrs until now.

Could there be another tab that accumulates the income vs expense for each month with a ‘year total’ page too? I’m sure clever people could achieve this in 20 seconds flat!!! 😂😂😂

[JohnR]

I do wish they’d add a tariff data column to the downloads… would be a really useful addition.

[MrKlawUK]

is that +1:00 accounting for BST? Will it work when the clocks change back? Lovely spreadsheet btw

[pmannion]

Any idea why I have far more records in my export CSV from Octopus than I do for import?
Seems to throw off the spreadsheet