RSS Online News Articles Information

The Google News RSS Feed / Google Reader Mashup

My current employer is an executive search firm recruiting candidates for the real estate development and construction industries. I lead their internet marketing efforts; I also am in charge of market research. If I can deliver competitive industry intelligence to the recruiters in our office, they can utilize it to match executive job candidates with employers who need them but can´t find them on their own.

The Challenge

There are a very large number of companies worthy of being tracked by my company. ENR (Engineering News & Record) creates different authoritative topic-specific lists containing names of the biggest commercial construction firms. Builder Online lists the largest residential homebuilding firms. Between these two sources, I´ve identified about 1,700 companies where it would be valuable to track each & every news story about them. Fortunately, on any given day, only a small percentage of these companies will make noteworthy news…however, in order to make sure I catch all the valuable information, I need to monitor all of them.

Most logically, I would have to implement some sort of RSS solution in order to track 1,700 companies. Manual entry of 1,700 feeds into a feedreader isn´t an option; I don´t have any programming skills; and I don´t have a budget to find someone to program a solution.

So what did I do?

The Solution: Part 1

When I examined a typical Google News RSS URL, I can determine that the only variable information within each lengthy URL is the term being searched:

http://news.google.com/news?sourceid=navclient&ie=UTF-8&rls=GGLJ,GGLJ:2006-37,GGLJ:en&oe=UTF-8&tab=wn&q=builder+magazine&output=rss

From this information, I can quickly and simply reproduce a Google News RSS URL for each item in my list quickly and easily in Excel.

I then do the following (which is more clearly illustrated in the attached spreadsheet that I strongly encourage you to download by clicking this link):

1) Once I open Excel, I place my entire list of relevant companies into Column B.

2) In Column A, I place the first portion of the Google RSS URL (from http:// up to the variable portion).

3) In Column C, I place the final portion of the URL (&output=rss).

4) I also know from experience that in order to make my feed as relevant as possible, I want to use exact match wherever I can. “%22” is the symbol used for “quote”. I append “%22” as the last characters in Column A and the first characters in Column C.

5) I make sure that the code in Columns A & C is copied onto every row containing a relevant company in Column B.

I also need to perform the following global steps in Column B:

1) Replace “space” and “&” with “+”
2) Replace “++” with “+”
3) Delete all instances of “apostrophe”.

Here comes the fun part:

1) I widen the columns so that there is plenty of white space to the right of the text in each column.
2) I save the document as a Formatted Text Space Delimited (.prn) document.
3) I reopen the document, choose “Delimited”, click “Next”, and then click “Finish”.
4) I then globally replace “space” with nothing, generating the list of long RSS URL´s.