I live in Illinois and participate in ComEd's Residential Real-Time Pricing Program, which means I pay the hourly spot price for each kwH of electricity my family uses. Prices usually follow predictable patterns from season-to-season and hour-to-hour, but can, and do, spike unexpectedly as a result of power outages, equipment failures, weather and other factors, so it is useful to keep an eye on predicted and actual prices.

ComEd provides hourly price tracking on its web site that looks something like this:

Screenshot 2014-01-09 16.45.44.png

ComEd's site works fine, but I wanted to automate the process of tracking prices so that the price data would be pushed to me at regular intervals instead of requiring me to pull it from ComEd's site when I think of it. With a little web scraping, string parsing, Hazel and Status Board running on an old iPad 1 that sits in my kitchen, it is now easy for me to keep up with ComEd's predicted and actual electricity prices throughout the day.

Although there are probably not too many of you who are interested in Chicago-area electricity prices, the process described below is adaptable to other similarly-structured data you might find on the web and want to monitor yourself.


The Data Source

A good way to isolate a data source is with Safari's developer tools. If you do not see a "Develop" menu item in Safari, go to the Advanced tab in Safari's preferences and check off the "Show Develop menu in menu bar" option at the bottom of the preference pane to activate it.

Screenshot 2014-01-12 19.03.14.png

By isolating a popup table of predicted and actual prices in its own tab (https://rrtp.comed.com/pricing-table-today/), studying how the site is structured (namely that all the pages had a root of https://rrtp.comed.com) and digging around the resources for the page, I discovered the following:

<script>  
    $().ready(function() {  
    $(".prices tbody").load("/rrtp/ServletFeed?type=pricingtabledual&date=20140113");  
    });     
</script>

Appending "/rrtp/ServletFeed?type=pricingtabledual&date=20140113" from the foregoing to https://rrtp.comed.com -- https://rrtp.comed.com//rrtp/ServletFeed?type=pricingtabledual&date=20140113 -- results in output in Safari similar to the following:

12:00 AM2.9¢2.7¢1:00 AM2.7¢2.8¢2:00 AM2.6¢2.8¢3:00 AM2.6¢2.6¢4:00 AM2.5¢2.7¢5:00 AM2.5¢2.8¢6:00 AM2.6¢2.7¢7:00 AM2.8¢2.9¢8:00 AM3.0¢3.0¢9:00 AM3.2¢3.1¢10:00 AM3.2¢3.5¢11:00 AM3.2¢3.4¢12:00 PM3.1¢3.0¢1:00 PM3.0¢3.0¢2:00 PM3.0¢3.0¢3:00 PM2.9¢2.9¢4:00 PM3.0¢3.7¢5:00 PM3.9¢3.3¢6:00 PM3.8¢3.4¢7:00 PM3.5¢3.2¢8:00 PM3.3¢3.1¢9:00 PM3.0¢2.9¢10:00 PM2.9¢2.8¢11:00 PM2.8¢2.8¢.

It may not be pretty, but that long concatenated string includes the time, predicted price and actual price of electricity as available. The last eight digits of the URL are the date in yyyyMMdd format. As the hours advance for the current date, the actual price data for each hour is added to the predicted price data generated by ComEd's site. Thus, by updating the URL daily and polling the site at least hourly, the predicted and actual electricity prices for each day can be logged.


The Code

I created a Command Line Application in XCode to fetch the ComEd price data, parse it and write it to CSV file.

Building the URL

The only part of the URL that changes is the date, which is at the end of the URL in yyyyMMdd format.

NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];  
[dateFormatter setDateFormat:@"yyyyMMdd"];

NSDate *date = [NSDate date];
NSString *formattedDateString = [dateFormatter stringFromDate:date];
//NSLog(@"formattedDateString: %@", formattedDateString);

//today's prices string
NSString *fullURL = [NSString stringWithFormat:@"http://rrtp.comed.com/rrtp/ServletFeed?type=pricingtabledual&date=%@", formattedDateString];

This code creates an NSDateFormatter object called dateFormatter with the yyyyMMdd format. The code then instantiates an NSDate object called date that is set to the current date. The final step is to generate a NSString called formattedDateString using the date and dateFormatter objects, which is appended to the static portion of the URL, a NSString called fullURL.

Capturing the Data

Using NSURL, the code fetches the electricity pricing data from the date-specific URL generated above and dumps it into an NSString called contents:

NSURL *hourlyPriceURL = [NSURL URLWithString:fullURL];
NSError *error = nil;
NSString *contents = [[NSString alloc] initWithContentsOfURL:hourlyPriceURL encoding:NSUTF8StringEncoding error:&error];

The output is not nearly as clean as is displayed in Safari:

<tr><td>12:00 <small>AM</small></td><td>3.4&cent;</td><td>3.2&cent;</td></tr><tr><td>1:00 <small>AM</small></td><td>3.0&cent;</td><td>3.0&cent;</td></tr><tr><td>2:00 <small>AM</small></td><td>3.0&cent;</td><td>3.0&cent;</td></tr><tr><td>3:00 <small>AM</small></td><td>3.0&cent;</td><td>3.0&cent;</td></tr><tr><td>4:00 <small>AM</small></td><td>3.0&cent;</td><td>3.0&cent;</td></tr><tr><td>5:00 <small>AM</small></td><td>3.0&cent;</td><td>3.0&cent;</td></tr><tr><td>6:00 <small>AM</small></td><td>3.1&cent;</td><td>2.9&cent;</td></tr><tr><td>7:00 <small>AM</small></td><td>3.3&cent;</td><td>2.9&cent;</td></tr>...<td></td></tr>

Parsing the Data

Panic's Status Board iPad app takes CSV files as input so the rest of the code is simply a multi-step search and replace routine that inserts commas and line breaks where needed, and deletes the unnecessary HTML tags using the stringByReplacingOccurrencesOfString method:

contents = [contents stringByReplacingOccurrencesOfString:@"&cent;</td><td>" withString:@","];  
contents = [contents stringByReplacingOccurrencesOfString:@"&cent;</td></tr><tr>" withString:@"\n"];  
contents = [contents stringByReplacingOccurrencesOfString:@"<tr>" withString:@""];  
contents = [contents stringByReplacingOccurrencesOfString:@"<td>" withString:@""];
contents = [contents stringByReplacingOccurrencesOfString:@"</tr>" withString:@"\n"];
contents = [contents stringByReplacingOccurrencesOfString:@"</td>" withString:@""];
contents = [contents stringByReplacingOccurrencesOfString:@"<small>" withString:@""];
contents = [contents stringByReplacingOccurrencesOfString:@"</small>" withString:@""];
contents = [contents stringByReplacingOccurrencesOfString:@";" withString:@""];
contents = [contents stringByReplacingOccurrencesOfString:@"M" withString:@"M,"];

What remains looks like this:

12:00 AM,3.4,3.2
1:00 AM,3.0,3.0
2:00 AM,3.0,3.0
3:00 AM,3.0,3.0
4:00 AM,3.0,3.0
5:00 AM,3.0,3.0
6:00 AM,3.1,2.9
7:00 AM,3.3,

Writing to File

The final step is to write the newly-formatted string to a CSV file.

NSString *csvContents = [NSString stringWithFormat:@"Electricity Prices,Predicted,Actual\n%@", contents];  
//NSLog(@"%@", csvContents);
[csvContents writeToFile:@"/Users/johnvoorhees/DropBox/dailyPrice.csv" atomically:NO encoding:NSUTF8StringEncoding error:&error];

The first line prepends the file with "Electricity Prices,Predicted,Actual", which serve as the title for the Status Board chart and labels for the predicted and actual prices. The remainder of the code simply writes the data to a CSV file in my Dropbox named dailyPrice.csv.


Dropbox, Status Board and Hazel

With the script-created CSV file in hand, the only remaining tasks are to run the script at regular intervals so that it captures the actual electricity prices as they update and to get the data into Panic's Status Board app where it can be displayed in a glanace-able graphical format. There a number of ways to run a script on your Mac at regular intervals, but I chose to useHazel because I already rely on it for a number of file-related automation tasks and it can run command line tools, which makes it perfect for the job. I simply pointed Hazel at the dailyPrice.csv file and created a rule to run my command line tool every ten minutes or so.

Dropbox makes it similarly simple to host the dailyPrice.csv file where it can be seen by Status Board. Place the file in your Dropbox and copy a sharable link to the file. In Status Board all you need to do is to authorize it to access Dropbox and then create a DIY module (PDF link) that uses the Dropbox link as its data source. And with that, you have a beautiful, automatically updating graph of predicted and actual electricity prices.

Limitation

A limitation of this script is that the actual price graph falls off to 0 for time periods in the future because, unlike the predicted price, there is no value for those hours. If anyone has any ideas on how to avoid this, please get in touch using the contact page link above.

Download

You can download this project on GitHub here.