Anyone have a good Excel macro for parsing ComEd's interval data files? I'm dealing with these massive CSV exports and manually cleaning them is eating up hours. The format keeps changing slightly and my current process is too manual. Would love to hear what others are using to streamline this.
Excel macro for parsing ComEd interval data?
Derek, I feel your pain on this one. We've been dealing with similar issues with Duquesne Light data. I built a VBA macro that handles most of the common format variations. It's not perfect but cuts my processing time by about 80%. I can share the code if you're interested. Main thing is building in error handling for when the utility switches column order without warning.
Walt's right about the format changes. FirstEnergy does the same thing - drives me nuts. I actually switched to using R for data processing last year and haven't looked back. Bit of a learning curve but worth it for the consistency. Can handle any CSV format they throw at you and the data visualization capabilities are fantastic for spotting anomalies.
For Georgia Power interval data, I use a combination of Power Query and pivot tables. Power Query is built into newer Excel versions and handles the data transformation much better than VBA. You can set up refresh routines that automatically clean and format new data files. Saved me probably 10 hours a week on large commercial accounts.
Thanks everyone! Walt, I'd love to see that macro if you don't mind sharing. Rachel, I'll look into Power Query - hadn't heard of that feature. Frank, R might be overkill for what I need right now but definitely something to consider down the road. Amazing how much time we all spend just getting data into usable format.
No problem Derek, I'll email you the macro. Fair warning though - it's pretty specific to Duquesne's format so you'll need to modify the column references for ComEd. The main logic should transfer over though. Key is using arrays to batch process the data instead of looping through individual cells.
One more tip - if you're doing this regularly, consider investing in a dedicated data analysis tool like Tableau or even just upgrading to Excel Professional Plus. The advanced data tools pay for themselves quickly when you're processing interval data for multiple clients. Georgia Power alone sends me files with 35,000+ rows per account.