Using Bright data to check bills

Pat38493

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?

[Deleted User]

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

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

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.

Pat38493

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

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

QrizB

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.

Pat38493

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

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?

Pat38493

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

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.