Working on a large ComEd commercial account and getting buried in 15-minute interval data. Trying to identify demand spikes and correlate with billing cycles. Does anyone have an Excel macro that can automatically flag intervals above a certain threshold and calculate rolling averages? This client has 18 months of data and doing it manually is killing me.
Excel macro for ComEd demand analysis - anyone have something?
Yuri - I built something similar for Georgia Power accounts last year. It's not perfect but handles the rolling averages and can flag spikes above whatever percentage you set. The macro also color-codes the cells which helps visualize the patterns. I can email it to you if you want to take a look and modify it for ComEd's format.
Rachel's macro is solid. I used it on an MLGW account here in Memphis and saved probably 20 hours of work. Just make sure you adjust the demand window - ComEd uses 15-minute intervals but the billing demand calculation might be different than what we see with TVA territory utilities. Also watch out for power factor penalties in the macro logic.
Thanks both! Rachel, definitely interested in seeing what you built. Reggie makes a good point about the demand windows - ComEd does some funky stuff with their coincident peak calculations that I need to account for. This particular client got hit with a $47,000 demand charge last summer and they want to understand exactly when and why.
I work with a lot of National Grid and NYSEG accounts up here in Albany, so I feel your pain on the interval data. One thing I've learned is to build in a check for meter read dates because sometimes the utility billing cycle doesn't align perfectly with calendar months. The macro should flag any discrepancies so you don't miss billing errors.
Excellent point Gail. I've seen ComEd shift their read dates by a few days and it throws off the whole analysis. Will definitely build that check into whatever solution I end up with. This is why I love this forum - you guys always think of the details I miss!
Just sent you the macro Yuri. Fair warning - the code is a bit messy because I built it in chunks over several months. But it works and has saved me countless hours. Let me know if you need help adapting it for ComEd's specific tariff structure.