Using Bright data to check bills

Pat38493
Pat38493 Forumite Posts: 1,908
Ninth Anniversary 1,000 Posts Name Dropper Combo Breaker
Forumite
I have downloaded the detailed 30 minute data from the bright app.

It seems like the 30 minute electric readings are only provided with a UTC time stamp in a text field.

Has anyone been able to successfully convert this into a usable data in a spreadsheet that shows 
a) Proper date fields
b) Allows you to determine peak/off peak readings based on BST/GMT across a time period where it changed from one to the other?

Ultimately I guess I want to get a column that says "Peak" or "Off peak" for each reading based on the UTC time, which of course means that the UTC times will not be the same between summer and winter.

If so can you give me some hints on the Excel formulas / approach to use - ideally there is a canned formula for this but I doubt it?
«1

Comments

  • Dolor
    Dolor Forumite Posts: 7,670
    1,000 Posts Third Anniversary Name Dropper
    Forumite
    If you are an Octopus customer then apps such as Octopus Watch do all that is required without spreadsheets. Data including tariff information is taken via API directly from the online account.



    Alternatively, I suggest that you look at N3rgy.com
  • Swipe
    Swipe Forumite Posts: 4,660
    Part of the Furniture 1,000 Posts Name Dropper
    Forumite
    Try this if you are signed up to n3rgy.com  https://energy.guylipman.com/sm

    You can generate your own bills on any custom period and it works with E7 too.
  • QrizB
    QrizB Forumite Posts: 11,502
    10,000 Posts Second Anniversary Photogenic Name Dropper
    Forumite
    Pat38493 said:
    I have downloaded the detailed 30 minute data from the bright app.
    It seems like the 30 minute electric readings are only provided with a UTC time stamp in a text field.
    ...
    If so can you give me some hints on the Excel formulas / approach to use - ideally there is a canned formula for this but I doubt it?
    I don't have a sample of the BRIGHT app time stamp, but I'm better-than-average with Excel formulas.
    If you can post an example of the time stamp, I can (probably!) come up with a formula to parse it.
    N. Hampshire, he/him. Octopus Go elec & Tracker gas / Shell BB / Lyca mobi. Ripple Kirk Hill member.
    2.72kWp PV facing SSW installed Jan 2012. 11 x 247w panels, 3.6kw inverter. 30MWh generated, long-term average 2.6 Os.
    Ofgem cap table, Ofgem cap explainer. Economy 7 cap explainer. Gas vs E7 vs peak elec heating costs.
  • Pat38493
    Pat38493 Forumite Posts: 1,908
    Ninth Anniversary 1,000 Posts Name Dropper Combo Breaker
    Forumite
    QrizB said:
    Pat38493 said:
    I have downloaded the detailed 30 minute data from the bright app.
    It seems like the 30 minute electric readings are only provided with a UTC time stamp in a text field.
    ...
    If so can you give me some hints on the Excel formulas / approach to use - ideally there is a canned formula for this but I doubt it?
    I don't have a sample of the BRIGHT app time stamp, but I'm better-than-average with Excel formulas.
    If you can post an example of the time stamp, I can (probably!) come up with a formula to parse it.
    The file you can download just has 3 columns for each 30 minute reading:

    epochtimestamp - 1679274000
    kwH - 0.159
    dateTime - 2023-03-20T01:00:00.000Z

    I am assuming that the Z means zulu time UTC so doesn't take into account DST changes.



  • michaels
    michaels Forumite Posts: 27,515
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    Forumite
    edited 7 July at 10:30AM
    I download from n3gry and use formulas like this in excel

    n3gry raw CSV
    timestamp (UTC) energy Consumption (kWh)
    01/06/2023 00:30 2.791

    =DAY((01/06/2023 00:30)-0.01) gives the day of the month adjusted to BST
    I think....
  • QrizB
    QrizB Forumite Posts: 11,502
    10,000 Posts Second Anniversary Photogenic Name Dropper
    Forumite
    edited 7 July at 10:38AM
    Pat38493 said:
    dateTime - 2023-03-20T01:00:00.000Z
    Thanks. Do you want the date and time in separate cells?
    • To extract the date from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
    • To extract the time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    If you'd rather have date and time in the same cell, add the two functions together:
    • To extract the date and time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    It works for me in Excel 2016. Please let me know if it works for you too.
    N. Hampshire, he/him. Octopus Go elec & Tracker gas / Shell BB / Lyca mobi. Ripple Kirk Hill member.
    2.72kWp PV facing SSW installed Jan 2012. 11 x 247w panels, 3.6kw inverter. 30MWh generated, long-term average 2.6 Os.
    Ofgem cap table, Ofgem cap explainer. Economy 7 cap explainer. Gas vs E7 vs peak elec heating costs.
  • Pat38493
    Pat38493 Forumite Posts: 1,908
    Ninth Anniversary 1,000 Posts Name Dropper Combo Breaker
    Forumite
    QrizB said:
    Pat38493 said:
    dateTime - 2023-03-20T01:00:00.000Z
    Thanks. Do you want the date and time in separate cells?
    • To extract the date from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
    • To extract the time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    If you'd rather have date and time in the same cell, add the two functions together:
    • To extract the date and time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    It works for me in Excel 2016. Please let me know if it works for you too.
    Thanks that is very helpful.  Is there any way that you are aware of to easily change the times to local time instead of UTC time?

    I guess I can do it manually by using an if... then based on the known dates of time changes, but I just want to make sure there is no canned function available for this.
  • QrizB
    QrizB Forumite Posts: 11,502
    10,000 Posts Second Anniversary Photogenic Name Dropper
    Forumite
    edited 7 July at 11:20AM
    Pat38493 said:
    QrizB said:
    Pat38493 said:
    dateTime - 2023-03-20T01:00:00.000Z
    Thanks. Do you want the date and time in separate cells?
    • To extract the date from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
    • To extract the time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    If you'd rather have date and time in the same cell, add the two functions together:
    • To extract the date and time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    It works for me in Excel 2016. Please let me know if it works for you too.
    Thanks that is very helpful.  Is there any way that you are aware of to easily change the times to local time instead of UTC time?
    I guess I can do it manually by using an if... then based on the known dates of time changes, but I just want to make sure there is no canned function available for this.
    I don't think Excel natively knows when it's GMT and when it's BST. The internet is full of people using nested IF statements to work out if a given day is GMT or BST, for example (this is a copy-and-paste from here):
    =IF(AND(AND(B2>=EOMONTH(DATE(YEAR($B2),3,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),3,1),0),11),7),C2>(1/24)),B2<=EOMONTH(DATE(YEAR($B2),10,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),10,1),0),11),7),C2<(2/24)),C2+(1/24),C2)
    However you want hourly data and the GMT/BST change doesn't happen at midnight, so it gets more complicated. The GMT>BST day is 23 hours long then when you go BST>GMT you have 25 hours - an hour repeated.
    Most (but not all) time-of-use tariffs don't change the switching times for BST anyway, so it's simpler to work in UTC all year round.
    Whicch electricity supplier and tariff do you have? Do the peak/offpeak times adjust for BST, or do they stay on GMT all year round?
    N. Hampshire, he/him. Octopus Go elec & Tracker gas / Shell BB / Lyca mobi. Ripple Kirk Hill member.
    2.72kWp PV facing SSW installed Jan 2012. 11 x 247w panels, 3.6kw inverter. 30MWh generated, long-term average 2.6 Os.
    Ofgem cap table, Ofgem cap explainer. Economy 7 cap explainer. Gas vs E7 vs peak elec heating costs.
  • Pat38493
    Pat38493 Forumite Posts: 1,908
    Ninth Anniversary 1,000 Posts Name Dropper Combo Breaker
    Forumite
    QrizB said:
    Pat38493 said:
    QrizB said:
    Pat38493 said:
    dateTime - 2023-03-20T01:00:00.000Z
    Thanks. Do you want the date and time in separate cells?
    • To extract the date from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
    • To extract the time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    If you'd rather have date and time in the same cell, add the two functions together:
    • To extract the date and time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    It works for me in Excel 2016. Please let me know if it works for you too.
    Thanks that is very helpful.  Is there any way that you are aware of to easily change the times to local time instead of UTC time?
    I guess I can do it manually by using an if... then based on the known dates of time changes, but I just want to make sure there is no canned function available for this.
    I don't think Excel natively knows when it's GMT and when it's BST. The internet is full of people using nested IF statements to work out if a given day is GMT or BST, for example (this is a copy-and-paste from here):
    =IF(AND(AND(B2>=EOMONTH(DATE(YEAR($B2),3,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),3,1),0),11),7),C2>(1/24)),B2<=EOMONTH(DATE(YEAR($B2),10,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),10,1),0),11),7),C2<(2/24)),C2+(1/24),C2)
    However you want hourly data and the GMT/BST change doesn't happen at midnight, so it gets more complicated. The GMT>BST day is 23 hours long then when you go BST>GMT you have 25 hours - an hour repeated.
    Most (but not all) time-of-use tariffs don't change the switching times for BST anyway, so it's simpler to work in UTC all year round.
    Whicch electricity supplier and tariff do you have? Do the peak/offpeak times adjust for BST, or do they stay on GMT all year round?
    Currently British Gas but just about to switch to Octopus now that fixed rate deals are starting to be available again and competition might return.
  • Pat38493
    Pat38493 Forumite Posts: 1,908
    Ninth Anniversary 1,000 Posts Name Dropper Combo Breaker
    Forumite
    QrizB said:
    Pat38493 said:
    QrizB said:
    Pat38493 said:
    dateTime - 2023-03-20T01:00:00.000Z
    Thanks. Do you want the date and time in separate cells?
    • To extract the date from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
    • To extract the time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    If you'd rather have date and time in the same cell, add the two functions together:
    • To extract the date and time from a text field "2023-03-20T01:00:00.000Z" in cell A1, use =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
    It works for me in Excel 2016. Please let me know if it works for you too.
    Thanks that is very helpful.  Is there any way that you are aware of to easily change the times to local time instead of UTC time?
    I guess I can do it manually by using an if... then based on the known dates of time changes, but I just want to make sure there is no canned function available for this.
    I don't think Excel natively knows when it's GMT and when it's BST. The internet is full of people using nested IF statements to work out if a given day is GMT or BST, for example (this is a copy-and-paste from here):
    =IF(AND(AND(B2>=EOMONTH(DATE(YEAR($B2),3,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),3,1),0),11),7),C2>(1/24)),B2<=EOMONTH(DATE(YEAR($B2),10,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($B2),10,1),0),11),7),C2<(2/24)),C2+(1/24),C2)
    However you want hourly data and the GMT/BST change doesn't happen at midnight, so it gets more complicated. The GMT>BST day is 23 hours long then when you go BST>GMT you have 25 hours - an hour repeated.
    Most (but not all) time-of-use tariffs don't change the switching times for BST anyway, so it's simpler to work in UTC all year round.
    Whicch electricity supplier and tariff do you have? Do the peak/offpeak times adjust for BST, or do they stay on GMT all year round?
    To elaborate, the tariff terms document says :

    "This tariff is ‘time of use’ which means there are two electricity unit rates. One during peak time and a different unit rate at off-peak time. The peak rate is between the hours of 5.01 am to 11.59 pm and the off-peak unit rate is between the hours of 12.00 am to 5.00 am. The gas has a single unit rate."

    I assumed that this was according to local time rather than GMT but it doesn't specify one way or the other.
Meet your Ambassadors

Categories

  • All Categories
  • 338.9K Banking & Borrowing
  • 248.7K Reduce Debt & Boost Income
  • 447.6K Spending & Discounts
  • 230.8K Work, Benefits & Business
  • 601K Mortgages, Homes & Bills
  • 171.1K Life & Family
  • 244K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards