I recently started a project which allows foreclosure companies to verify the status of various properties they are interested in with the click of a button. The client wanted the ability to upload a spreadsheet with a list of properties. The app searches various real estate foreclosure websites for each property in the sheet and extracts information regarding the status of the auction.
Ruby was my language of choice for the project due to the amazing scraping framework called Mechanize. I used Slicehost to host the app.
The app needed 3 major components:
- A web interface for users to upload a spreadsheet and access the latest spreadsheet with status data
- A parser to read in the Excel sheet with a list of properties
- A crawling system to find property auction status on a variety of foreclosure websites.
By far the most difficult part was dealing with crawling sites which were written in ASP (the majority of real estate websites).
Building the web interface
I went with a standard LAMP stack for the interface. The ruby code that does the crawling is called through a Webrick servlet. So in the end I have two servers running: a webserver on port 80 and Webrick on another (internal) port.
When users send a request through the front end PHP forwards the request to the internal webrick server.
Unfortunately webrick and the crawler have a tendency to be somewhat unstable … so whenever the front end is loaded I use php to ensure that the webrick process running. This is quite easy to do using PHP’s nifty shell_exec function:
function isProcessLaunched(){
if(shell_exec("ps -e | grep ruby")!="")
return true;
else
return false;
}
Parsing Excel Files
Ruby 1.9 now has an improved CSV processing framework but I decided to go with Ruby 1.85 so I decided to use the FasterCSV gem which worked quite well.
My clients don’t usually use CSV format and I didn’t want to have to deal with inconsistencies in how they did the CSV exports so I decided that having native support for XLS files would be best. I decided to do the conversion from xls to csv myself on the server side . I tried using the roo gem but it just froze… in the end I decided to use a very handy little perl script called xls2csv
I simply used Rubys system function to execute the perl script which outputs the converted csv file. Using FasterCSV to parse this csv file was extremely straightforward.
Scraping ASP .NET web pages
The scraper was the hardest part primarily because ASP webpages submit all information through javascript:
<input type=”submit” id=”btnsearch” onclick=”return validate();” value=”Submit” name=”btnsearch”/>
We can’t use Mechanize directly as it will have no idea how to submit the webform. So I needed to get crafty.
The easiest thing to do is look at the outgoing HTTP post request which is pretty straightforward to do with a tool like LiveHTTP headers
Once you have this post request its pretty smooth sailing for most sites — just resend the post data you observed but change the parameter associated with an id for the property you’re searching for.
Use Mechanize to send the post data and everything should work fine — when you send the login post request the Mechanize system will store all the cookies and session ids properly.
