🗳️ ELECTION 2024: THE MSE LEADERS' DEBATE Got a burning question you want us to ask the party leaders ahead of the general election? Submit your suggestions via this form or post them on our dedicated Forum board where you can see and upvote other users' questions. Please note that the Forum's rules on avoiding general political discussion still apply across all boards.

Formulas or macros for Octopus Smart Meter download

Options
Qyburn
Qyburn Posts: 2,360 Forumite
First Post First Anniversary Name Dropper
Hi,
I was wondering if anyone has already made a tool for crunching the smart meter data that can be downloaded from Octopus. I made a start in Excel with a formula to convert the text into date form. But before I spent any more time I thought I'd check to see if it's already been done. Ideally it would just process the CSV file, rather than need formulas pasted in manually.
Or any other way of downloading and reporting on SM data
Thanks

Comments

  • [Deleted User]
    Options
    Buy a Hildebrand IHD/CAD and you access your data via MQTT, or try N3rgy.com.
  • FreeBear
    FreeBear Posts: 14,887 Forumite
    First Anniversary First Post Name Dropper Photogenic
    edited 4 March 2023 at 2:50PM
    Options
    sed with and a regex or two would be my tools of choice. But if you are wanting t use Excel, I'm guessing the Gnu toolkit isn't going to work too well.
    Getting data from a Hildebrand IHD/CAD via MQTT is still going to need processing so that it can be read in to a spreadsheet. So I don't think that would be an answer for you. If you just want to generate graphs, pushing the data in to a database (InfluxDB ?) and using Grafana might work - A steep learning curve though..

    edit - Looks like Excel has a live data import, so with the use of a macro or two, it might be possible to grab MQTT messages as they are published. Would I want to do it... Probably not. Pretty sure you could use a regex or two within excel if you know which cells are going to be populated with the data from a CSV file.
    Her courage will change the world.

    Treasure the moments that you have. Savour them for as long as you can for they will never come back again.
  • Qyburn
    Qyburn Posts: 2,360 Forumite
    First Post First Anniversary Name Dropper
    Options
    Thanks. To explain a bit I don't need live data, I just want to be able to view historical data other than as a flat list of 13 months of 1/2 hourly readings. Something like a Pivot Table to summarise by month, then drill down by day etc.

    I'll press on a little more when I get round to it, but I'll also look at N3rgy.com as I assume that will be supplier independent in case I leave Octopus.
  • DingerUK
    DingerUK Posts: 66 Forumite
    Name Dropper First Anniversary First Post
    Options
    https://www.guylipman.com/octopus/api_guide.html#s5f

    Make a copy of the Google sheets doc and configure based on your api key
Meet your Ambassadors

Categories

  • All Categories
  • 7 Election 2024: The MSE Leaders' Debate
  • 343.8K Banking & Borrowing
  • 250.3K Reduce Debt & Boost Income
  • 450K Spending & Discounts
  • 236K Work, Benefits & Business
  • 609.1K Mortgages, Homes & Bills
  • 173.4K Life & Family
  • 248.6K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards