Yesterday I shared an article about Bing News, and showed you how easy it is to turn a Bing News result page into an RSS feed.

It’s this easy. Here’s a Bing News search:

http://www.bing.com/news/search?q=Microsoft&qft=sortbydate%3d%221%22

And here’s a Bing News search saved as an RSS feed:

http://www.bing.com/news/search?q=Microsoft&qft=sortbydate%3d%221%22&format=RSS

All you do is add &format=RSS to the end of the result. Easy peasy.

Now, I like this because, combined with Bing News’ loc: syntax (which lets you narrow search results by country) this is an easy way for me to search for international news.

You may have noticed that there’s a lot more international happenings in ResearchBuzz these days. It’s a natural evolution – it’s important to know how other countries are handling things like social media access restrictions, or how countries are actually using Google resources for political reasons or reasons of governance.

It’s also easier to restrict certain kind of searches to one country. For example, if I tried to monitor Bing News for the word Google, I would quickly get overwhelmed. On the other hand, if I tried to monitor news from New Zealand with the word Google, I would get a more manageable list of results.

In this case however I want to monitor Bing News from non-US countries for the word database, since online database seems to be turning less effective as a search term. Does that mean I have to manually put together an RSS feed for every single country in which I’m interested? Pfft. There’s a much easier way to do it. I will not only make a bunch of RSS feeds, I’ll put them into a format where they can be easily imported into an RSS feed reader.

In this article I’m going to show you how to create a large collection of Bing News RSS feeds by country, and then by keyword. Then I’ll show you how to turn that list of feeds into an OPML file that you can import into an RSS feed reader very quickly.

You’ll need a spreadsheet (I’m using Google Sheets) and a nifty function called concatenate. Concatenate lets you group strings together in a spreadsheet. When an URL follows a specific pattern this is very handy.

Set Up an Example URL

The first thing I need to figure out is what my Bing News URL should look like. So I go to Bing News and do a search for database loc:ad . The loc:ad specifies that I want to limit my search for news sources from a specific country, in this case the small country of Andorra. You won’t get anything useful in the search results, but don’t worry about that. You want the result URL:

http://www.bing.com/news/search?q=database+loc%3aad&qft=sortbydate%3d%221%22

To turn that into an RSS feed, just add &format=rss to the end of the URL:

http://www.bing.com/news/search?q=database+loc%3aad&qft=sortbydate%3d%221%22&format=rss

(If none of this sounds familiar, please check out yesterday’s article on Bing News.)

Wondering what all those %22 things are in the URL? That’s how quote marks  are encoded in URLs. The %3a is encoding the colon we used in loc:ad. That’s important to know because we’ll have to be sure to reproduce them accurately as we create our spreadsheet.

We now have an example URL result from Bing. We’ll use Google Sheets to make a list of URLs that address the following parts of the URL:

http://www.bing.com/news/search? (the base URL)

q=database+ (the query)

loc%3aad (the query modifier: the country news sources I want searched)

&qft=sortbydate%3d%221%22 (putting the results in order by date)

&format=RSS (getting the results as an RSS feed)

Laying This Out On a Google Sheet

I’m starting with a spreadsheet called Bing News RSS Feeds. For the first set of feeds I’m leaving the query alone and changing the query modifier with different country codes. I’ll need to lay out the different parts of the URL in different columns. It ends up looking like this:

There are more elegant ways to do this, but this “shows my work” and is easy to follow along. (I’ve shared this spreadsheet if you want to make a copy and play with it yourself.) See how every part of the URL has its own column, and the country code part is blank?

Next thing you’ll need to do is get a list of country codes. I used the list at https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2 as it was the most current I could find. I pasted that list into column D, where the country codes go.

That looks kind of weird, doesn’t it? Now the fun part starts. We’re going to make a Bing News query for every last one of those country codes using the function CONCATENATE.

Concatenate

Concatenate lets you put all the functions together in one string, and it works like this:

=CONCATENATE(a,b,c,d,e)

… where a,b,c etc are the cells you want to put together. In the case above, my CONCATENATE function would look like this:

=CONCATENATE($A$3,$B$3,$C$3,$D3,$E$3,$F$3)

In this case, I’m telling Google Sheets “Put together in one string A3,B3,C3,D3,E3, and F3”. But what’s with all the dollar signs? Those tell Google Spreadsheets that those cells are absolute placements; in other words, even if I put this function somewhere other than where I originally did on Google Sheets (in this case, cell H3) it should not try to update those cells relative to the string’s new location. You’ll see why this is important in a moment.

I put the function in cell H3 and here’s what I get:

You can see the concatenate function is in the function bar, but the output string is in cell H3. I have made my first RSS feed.

Look back at that function. Notice how there isn’t a dollar sign in front of the 3 in $D3? That means that if I move the concatenate function, it’s going to update that 3 relative to the new position. In practical terms, that means that creating another feed is as simple as clicking the blue box in the lower right corner of cell H3 and dragging it down one row:

The concatenate function picked up the original URL parts in row 3, except for the country code, which it picked up from row 4. Now we have another RSS feed for another country.

Now let’s go wild: click that blue box at the bottom right corner of cell H4, and drag it ALL the way down to the end of the list of country codes:

I have almost 250 country-specific queries for Bing News in RSS format, and it took far more time to write up how to do it than to actually do it.

But what now? Do you have to manually put every single one of those feeds into a feed reader by hand? Good heavens, no. You make an OPML file.

OPML

OPML stands for Outline Processor Markup Language. It’s a way to pack RSS feeds into a group so they can easily be imported into a feed reader. Long ago when the Internet was new (well, newer than it is now), there were lots and lots of ways to create OPML files. There aren’t as many now, but there is a generator at http://opml-gen.ovh/ .

Copy all the URLs from the spreadsheet, paste them into the box, and click generate. You’ll get an .xml file in a few seconds.

Now I’m going to my settings in Digg Reader and picking Import from the bottom of the screen. I specify the XML file I just got from the OPML tool and Digg Reader gets busy.

And a few minutes later I have 249 new feeds in my feed reader:

With all the new content, will I have to go through an additional 249 RSS feeds a day? Of course not. Look at the screenshot for the country of Ghana. There are only six items in that feed and it goes back over two years. This is not a feed that will update every day or even every month. But when there is a story about a database in Ghana, I’ll know about it, and I won’t have to go hunting it down.

Maybe you’re not interested in searching for news in different countries. Maybe you’ve got a ton of keywords for which you’d like to make RSS feeds. We can do that.

Monitoring Minerals in Bing News

Say you’re a geologist and you want to monitor news feeds for stories about minerals. You don’t care where the stories are, you just want to know about particular minerals.

I will start a new query on my Google Sheet. It looks a little different:

In this case I don’t need to add a location code, but I still want to sort my results by date. I’ve got a list of minerals from Wikipedia, so I paste those in the keyword column.

You’ll notice Google Sheets retained the hyperlink to the Wikipedia articles about the minerals; it won’t impact your RSS feeds.

The concatenate function looks a little different:

=concatenate($A$255,$B$255,$C255,$E$255,$F$255)

Because you’re not adding a location code to your search, you can skip column D entirely. What you end up with after using this, however, is another list of Bing News RSS feeds, this time keyword-based and not location-based:

And with this list, you can create an OPML file and import it to an RSS reader just like you did the other one.

Sometimes when you have lists of things you want to search, or you have to use country codes as the only useful way to narrow down your search query, it can seem like too much trouble to create feeds for those search results. But it can be done, with the help of Google Sheets and a little URL hacking.