Using Bright data to check bills
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?
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?
0
Comments

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.com1 
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.0 
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 betterthanaverage 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.Ofgem cap table, Ofgem cap explainer. Economy 7 cap explainer. Gas vs E7 vs peak elec heating costs.
2.72kWp PV facing SSW installed Jan 2012. 11 x 247w panels, 3.6kw inverter. 30MWh generated, longterm average 2.6 Os.0 
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 betterthanaverage with Excel formulas.If you can post an example of the time stamp, I can (probably!) come up with a formula to parse it.epochtimestamp  1679274000
kwH  0.159
dateTime  20230320T01:00:00.000Z
I am assuming that the Z means zulu time UTC so doesn't take into account DST changes.0 
I download from n3gry and use formulas like this in excel
n3gry raw CSVtimestamp (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....1 
Pat38493 said:dateTime  20230320T01:00:00.000ZThanks. Do you want the date and time in separate cells?
 To extract the date from a text field "20230320T01: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 "20230320T01: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 "20230320T01: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.Ofgem cap table, Ofgem cap explainer. Economy 7 cap explainer. Gas vs E7 vs peak elec heating costs.
2.72kWp PV facing SSW installed Jan 2012. 11 x 247w panels, 3.6kw inverter. 30MWh generated, longterm average 2.6 Os.1 
QrizB said:Pat38493 said:dateTime  20230320T01:00:00.000ZThanks. Do you want the date and time in separate cells?
 To extract the date from a text field "20230320T01: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 "20230320T01: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 "20230320T01: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.
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.0 
Pat38493 said:QrizB said:Pat38493 said:dateTime  20230320T01:00:00.000ZThanks. Do you want the date and time in separate cells?
 To extract the date from a text field "20230320T01: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 "20230320T01: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 "20230320T01: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.
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 copyandpaste 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) timeofuse 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.Ofgem cap table, Ofgem cap explainer. Economy 7 cap explainer. Gas vs E7 vs peak elec heating costs.
2.72kWp PV facing SSW installed Jan 2012. 11 x 247w panels, 3.6kw inverter. 30MWh generated, longterm average 2.6 Os.1 
QrizB said:Pat38493 said:QrizB said:Pat38493 said:dateTime  20230320T01:00:00.000ZThanks. Do you want the date and time in separate cells?
 To extract the date from a text field "20230320T01: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 "20230320T01: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 "20230320T01: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.
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 copyandpaste 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) timeofuse 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?0 
QrizB said:Pat38493 said:QrizB said:Pat38493 said:dateTime  20230320T01:00:00.000ZThanks. Do you want the date and time in separate cells?
 To extract the date from a text field "20230320T01: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 "20230320T01: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 "20230320T01: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.
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 copyandpaste 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) timeofuse 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?
"This tariff is ‘time of use’ which means there are two electricity unit rates. One during peak time and a different unit rate at offpeak time. The peak rate is between the hours of 5.01 am to 11.59 pm and the offpeak 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.0
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