Time Of Use Data Analysis (Electricity) / Retailer Comparison

[naigy]

Hi I have my electricity usage data in the following format (written in csv style for simplicity)

12:00 - 1:00, 1:00 - 2:00, 2:00 - 3:00.......... 23:00 - 0:00, date(dd/mm/yy), yyyy-mm, mm, weekend/weekday, day of week.

As such each day takes a single row and has 24 usage data points.

Here in Australia we have time of use metering which is further complicated by different retailers have their own time periods for when is peak, shoulder and offpeak rates and can differ at different times of the year. For example one retailer may have peak periods 2PM - 8PM Monday to Friday all year round where as another retailer has peak periods of 2PM to 7PM Monday to Friday during December, January and February and 5PM - 9PM during May, June, July and August and then no peak rates for the rest of the year. They do however on weekends where rates are peak during the week in those hours it is classed as shoulder rates.

As such I had the plan to have two other tables / worksheets which get referenced
Worksheet 1 - List each retailer I want to compare and the various costings
ie. ProviderName, Peak Rate, Shoulder Rate, Offpeak Rate

Worksheet 2 - List of Providers I want to compare and list the hours by month and put here when it is peak, shoulder, offpeak etc. (somethings similar to attached image

In the past data like this I have often gone with a complex VBA script that I make to resolve it but in learning more about SUMIFS, INDEX, MATCH, Crosstab and a bunch of other inbuilt functions I am thinking there is a far better way to do this by utilising inbuilt functions instead of reinventing the wheel as I always tend to do.

The final output I am thinking would be best in PivotTable / PivotChart format as I could choose what retailers I wanted to see and could see breakdowns by Qtr, Month etc. but only way I would currently think to do that is to in essence transpose the data into another sheet first (so that each day had 24 rows of data) which I would do via script (as I will want to do this on about a yearly basis).

Any pointers on the logical and easier way to do this would be appreciated. As stated quite proficient in VBA so if this is the best way let me know and if there is a particular way/ function call so I don't make it more complicated then this needs to be.


"::

 

[Skybluekid]

Do the Off peak, Shoulder and peak rates according to the time of year, ie more expensive in winter than summer?

 

[naigy]

Do the Off peak, Shoulder and peak rates according to the time of year, ie more expensive in winter than summer?

No, cost of each tariff stays the same at all times of the year. It is just what hours of the weekdays a tariff applies that changes throughout the year. Hope that clears that up.

 

[naigy]

I got close to resolving this with the Power Query editor which I haven't used before but couldn't get it quite right or to a stage that would require minimal scripting. In the end I have gone with my usual method of a few VBA scripts to do the whole thing. Takes about 15 minutes for it to run (which is a lot longer than I would expect given the approx 660 rows of source data being expanded to approx 16000 and the relatively simple scripting but have it all working as I wanted and I only need to run it at most maybe twice a year. So will leave it at that as a solution. Thanks.