I built a ToU calculator in R rather than Excel, because it seemed actually easier… Not sure how your ToU or billing cycles work, but with my utility, there are 6 different rates - 3 per season, but the schedule is different between weekdays and weekends/vacation days.
The easiest way to do it in Excel or R is to do things in a few stages:
- Start with an hourly usage download from Sense for whatever time period you want to use
- Write an equation that converts the DateTime (day plus hour) into the name for the rate schedule for that hour. For me that was complicated because:
- I had to mark HOWE (holidays and weekend) days “TRUE”
- I had to also mark days as WINSUM (winter or summer) depending on my utilities calendar
- Here are the two equations that did the TOU logic for each hour, once I knew HOWE and WINSUM for each hour of the download. The end result is a text name in TOUPeriod column.
# Mark each hour with TOU tags
DownloadEnergy$PERIOD <- ifelse (DownloadEnergy$HOLWE,
ifelse ((hour(DownloadEnergy$DateTime) > 14 & hour(DownloadEnergy$DateTime) < 19), "On", "Off"),
ifelse ((hour(DownloadEnergy$DateTime) > 13 & hour(DownloadEnergy$DateTime) < 21), "On",
ifelse (((hour(DownloadEnergy$DateTime) > 6 & hour(DownloadEnergy$DateTime) < 14) | (hour(DownloadEnergy$DateTime) > 20 & hour(DownloadEnergy$DateTime) < 23)) ,
"Partial", "Off")))
DownloadEnergy$TOUPeriod <- paste (DownloadEnergy$WINSUM, DownloadEnergy$PERIOD, sep='')
- Once you have tagged every hour with the rate schedule for that hour, you can aggregate that column by doing a pivot table in Excel for that block of data, doing a count of entries in the TOUPeriod column.