Thursday, May 11, 2006

Return Online Yellow Page Listings to Excel

For a recently requested $50 Project I created a spreadsheet that allows the user to type in the name of a company and an optional state to return a list of online yellow page listings, including company name, address, city, state, zip, and telephone number.

I also created a spreadsheet for this person enabling him to automatically sort by company name, address, city, state, zip, and phone number, as well as automatically apply filters by each of those values, and filter by ranges of zip codes and phone numbers.

How do I get the listing information from the internet to Excel? Well, I wrote a script (using the objHTTP object and regular expressions) that went out to one of the many web-based yellow page services and returned the first page of matches for each company name, along with each associated address and telephone number. After running the code through a list of around 1,800 companies, it retrieved potential matches on roughly half. Not too bad. Beats the heck out of looking up each one manually and copy-pasting the contact info by hand.

All of this took me about 4 hours (programming time and run time included). See the value some of my $50 Project requesters receive!

The script I wrote to get these addresses and phone numbers could be modified to also retrieved websites for those companies listing them, which seemed like around 15-20% of them or so. Also, I could probably also tweak the code to retrieve all company matches for searches done, not by company name, but by business category. That could be valuable, I'd say,

Once a list of company matches were identified, I set up a second spreadsheet to allow this user to automatically sort and apply filters by each of the columns in the list. This could be a simple and effective means of analyzing groups and subgroups of clients, assigning sales or service territories, etc.

Thanks for the project, Ian. I hope you like it.

Excel_Geek