Updated Again: Parse Google Search Results for First URL

This is pretty much a regurgitation of my post from back in February of 2011. Google slightly changed how they build their search results pages again sometime between then and now, and I had a new Insiders subscriber sign-up looking for that very solution. When I checked to see if the file still worked, it did not, so I made a few tweaks to get it running again.

Here's a copy-paste of the last post on this topic:

Those of you who've been following me for some time may know that I've done quite a few projects related to parsing Google search results for various data -- URLs, number of results, etc. Those of you using the code likely know that some time ago Google apparently made some changes to how they put together their site causing my stuff to stop working. Well, recently one of the requesters of one of these projects - one that returns the first non-paid URL returned - came back and had me update it to work with how Google's site currently displays search results.


This time I made it into a user-defined function. You simply enter the function...


=getFirstGoogleResultURL(A2), where the value of cell A2 is the search term.


It works pretty slick, but know that Google will not allow this sort of thing to be used on large volumes of searches. I'm pretty sure they'd much rather have you using their APIs for this sort of usage.

Excel_Geek Insiders subscribers, your copy of this file is on its way.

Later,

Excel_Geek

Updated: Parse Google Search Results for First URL

Those of you who've been following me for some time may know that I've done quite a few projects related to parsing Google search results for various data -- URLs, number of results, etc. Those of you using the code likely know that some time ago Google apparently made some changes to how they put together their site causing my stuff to stop working. Well, recently one of the requesters of one of these projects - one that returns the first non-paid URL returned - came back and had me update it to work with how Google's site currently displays search results.



This time I made it into a user-defined function. You simply enter the function...



=getFirstGoogleResultURL(A2), where the value of cell A2 is the search term.


It works pretty slick, but know that Google will not allow this sort of thing to be used on large volumes of searches. I'm pretty sure they'd much rather have you using their APIs for this sort of usage.

Excel_Geek Insiders subscribers, your copy of this file is on its way.

Later,

Excel_Geek

$50 Project - Parse Google Search Results within a Domain for First Result

I recently completed a $50 Project for a client who wanted a spreadsheet where he could have a list of domains in which to search and a list of search terms and return the URL for the first returned search result from Google while searching withing the specific domain. It took a little modification of some other stuff I've done with parsing Google search pages, but it seems to work nicely.

Excel_Geek Insiders, your file is on it's way.

Later,

Excel_Geek

Parsing Google Search Results for URLs

I had a visitor on the blog yesterday who was interested in my old Google search results parsing code. Apparently he found Goohackle's Gooparser and liked what it does -- i.e. return just a clean list of URLs from the search results of Google -- but wanted a way around the limit for the number of results and a way to pull them directly into Excel.

Voila!

I cleaned up the old code for him. The result is a nice, clean worksheet that returns just the URLs from Google search results pages. You can set how many results per page and pages you'd like to return. Be careful, though, as if you hit Google too many times too quickly, they'll start to block you, asking you for your API key, etc. I've built in a random pause (3 to 7 seconds) between the pages in a multi-page query in an effort to prevent this from happening, but no guarantees. Google is a pretty sophisticated outfit, so they may still be able to detect an automated harvesting of data without using the API.

Geoff, enjoy the file. Insiders, the file's on the way.

Later,

Excel_Geek

Filling in the blanks - Interesting $50 Project

Recently I completed a $50 Project for a client who had what could be a fairly common data problem -- missing data points.

This client has large tables of data over a series of dates, but for some reason he's missing data points for some of the dates. To fill them in, his plan was to simply average the values of the four prior dates. In cases where the blanks occurred within the first four dates' data points, he was just going to put zeros.

The formulas needed to do this were simple, but there was a problem: His data table was very large. It would take him a long time to copy-paste that formula over and over.

"Isn't there a way to use to Excel's replace function to find blanks and replace them with these formulas?" my client asked. "Oh, and it'd be nice if when blanks were replaced, the font color was changed, so I can see which points are averages and which ones are authentic data values."

Here's kinda how my thought process went as I tried to solve this problem:

Hmmmm.

Well, the replace function idea wasn't really a go, because even though you can use it to replace blank cells with formulas (simply leave the "find what" field blank, put the formula -- e.g. "=1+4" -- in the "replace with" field and select the "match entire cell contents" option) there's no way to specify which cells to reference in the formulas. Plus the replace function, by itself, couldn't change the font color. You could use conditional formatting and change the font color if the cell contains a formula, but wait...there's no built in Excel formula to test if a cell contains a formula...and you can't just look to see if the leftmost character in the cell is a "=", since that evaluates the value in the cell (the result of the formula) not the text of the formula. I could have just wrote a custom function in VBA -- say, called ISFORMULA -- to evaluate whether or not the cell contains a formula. That VBA might have looked something like this:

Function ISFORMULA(CELL_REF as Range)

If Left(CEL_REF.Formula, 1) = "=" Then

     ISFORMULA = True

Else

     ISFORMULA = False

End If

End Function

But none of that mattered because, again, the replace function path wasn't going to work, so I instead wrote a relatively simple macro that would allow the user to select the range of cells he or she wanted to check for blanks and replace them with either zeros (if they were in the first four date columns) or formulas to average the prior four date columns' values. Then if the blank was replaced with a zero the font would be changed to green, and if the blank was replaced with the average formula, the font would be changed to red.

In the end, the result was a solution that didn't make any material changes to what the client was already doing, but solved his problem with the click of a button.

Insiders Subscribers, your file's on the way. I also made some explanatory notes in the spreadsheet in a comment and put lots of explanatory comments in the code itself.

Enjoy.

Excel_Geek

Oh, and did anyone notice two shiny new features (one temporary, one permanent) on the blog?

1. An image and link on the left side promoting a charity fundraiser my wife and I are involve with in the near future, and
2. A Google search feature on the right side. Now you can easily look for those oldie but goodie posts from way way back...

UPDATE II on Google API Project

Thought I'd pass along an interesting development with the Google API project requested by S_ _ _ _:

I have been contacted by another party who has alerted me to potential intellectual property issues surrounding this project. I'll not be identifying this party, nor will I make any statements relative to the exact nature of this party's claims, nor my perception of the validity of their claims.

Suffice it to say that I'll not be pursuing this project for hire at this time.

Sorry, S_ _ _ _. $50 isn't worth the risk.

Excel_Geek

UPDATE on Google API Project

I fired off an email to S_ _ _ _, trying to get some clarification, and with an update. S_ _ _ _ shot back an answer, showing that he/she's already got quite a bit of insight on this project. Further proof that my readers are, in fact, some of the brightest excel users already...they're just looking to me to help them tweak the masterpieces they've already built! (Hope I can live up to that.) Anyway, I thought all of you might find the subject matter interesting, so here're the emails:

Excel_Geek to S_ _ _ _ :

I have a question and an update:

Question: When you say, "# of competing websites for the search term" do you mean the total number of search results for a given search term, or do you mean the number of Google Adwords client advertisers who have purhcased that particular search term? Obviously the two are vastly different, and getting the latter, if even possible, would require me to be an Adwords client (which I'm not) so I could get access to the Adwords API. What I don't know is if I did have access to the Adwords API if it gives out that sort of information.

Update: Backlinks? Totally doable with the Google Search API. URL? Totally doable with the Google Search API (even without it). PageRank? Hmmmm.....This is what seemingly everyone is trying to achieve (so I've found). Google doesn't make this data readily available via API, and they stopped placing PageRank in their search results pages long ago, but there is the Google Toolbar, which displays the PageRank. I found one application in DOS that returns the PageRank, but it isn't useful to me. I have some ideas on how we might get this data via the toolbar....I'll keep you posted.

Excel_Geek

S_ _ _ _ to Excel_Geek :

Hi,

Answer to the question : I mean the total number of websites for the search term, which google displays in the SERP top left corner. (such as Results 1 - 10 of about 1,720,000,000 for web sites. (0.23 seconds) )

Side Info : I already have a program called keyword analyzer (http://www.keywordsanalyzer.com) which can fetch the number of google campaigns and top bids (exact, broad and phrase match options seperately). I guess this also handled with SOAP, similar to what you have done.

PageRank : There are a few sites which can fetch the pagerank (in text or in graphics) for a given keyword, most of them use ASP or PHP. Here are a few references. It might help.

http://www.thinkbling.com/serps/serps.php
http://www.mcdar.net/KeywordTool/keywordtool.asp
http://www.webuildpages.com/cool-seo-tool/
http://www.splutweb.com/Tools/Tools.asp

Interesting New Google API Related $50 Project

Just before Christmas (Merry late Christmas, by the way...) I received a request for an interesting new Google API-related $50 project.

S_ _ _ _ requested the following data be returned into an excel spreadsheet for each of a list of up to 300 or so keyword phrases:

• The number of websites competing for the search term keywords, as well as intitle:keywords and allinachor:keywords;


• The number of backlinks of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;


• The pageranks of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;


• The URLs of the 1st, 3rd, 5th, and 10th ranked competing sites for the search term keywords;

And some bonus optional data S_ _ _ _ would like are the number of searches in Overture Database and the number of searches in Wordtracker Database (using the WordTracker API, with which I'm not terribly familiar).

I'll get right on it, S_ _ _ _, and I'll see what I can do.

Excel_Geek

Google Search Results Page Parsing Code BIG in Russia?

Here's an interesting little tidbit: That Google search page parsing code I posted about way back when...it apparently was a hot topic in Russia! Wow.

Anatoliy Alizar wrote an article on www.webplanet.ru in which he said this about me...

"Generally, the amateurs of program Excel already wrote considerable code for processing of results of Google. Recently one of them published the special the parser, which is processed HTML- page with the results of search Google imports list URL into the cells of sheet Excel. This parser is completely suitable as the basic element for the set of useful macros."

"...amateurs of program Excel..." Indeed, I've been revealed as a fraud! Ha! Seriously, though, I'll humbly confess that "amateur" is likely closer than "expert."

Basically, Anatoliy is holding me out as an example of stuff programmers can do with Google even without subscribing to and using their available APIs. Fair enough... His article has been picked up by many other Russian blogs and websites, so he must have some credibility on this sort of subject matter.

After realizing (through Google Analytics) that considerable traffic was flowing from Russia (man, I love their geo targeting fatures!), I found a nice little website/text translator that handles Russian: www.worldlingo.com. Babel Fish, which I've used loyally for years, claims to do the same, but it errored out on me every time I tried to translate Russian websites to English.

So, to my new-found Russian readers, "Добро пожаловать к Excel_Geek."

взгляд на русском языке.

Here's the Google page parser

Here's v1.0 of the code...

Sub Parse_Google_Search_Results_Pages_For_URLs()

'**********************************************************
'****************** Code by Excel_Geek ********************
'**********************************************************

' This Excel macro calls up search results pages of a Google search
' using a search term specified in a cell named "SEARCH_TERM". Then it uses a
' regular expression to glean from this page all of the webpage addresses (URLs)
' of the search results and places them in a list in the spreadsheet.

' Setting up the regular expression that recognizes search result URLS on Google pages --
' The regular expression also ingores case, which by the way, i convert all to lower in the end.
Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = """http://([^6""]+)"""

' Now I reformat the search term as typed into the search term as used in Google search query URLs.
' i.e. I replace spaces with pluses and quote marks with %22s.
Range("SEARCH_TERM").Select
Selection.Replace What:="""", Replacement:="%22", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=" ", Replacement:="+", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

' Set the top limit on the Google page of results to parse.
PAGES = Range("GOOGLE_PAGE").Value

' Need this, apparently, to generate random numbers...
' which we'll need at the bottom of this next FOR_NEXT...
Math.Randomize

' Create a FOR_NEXT loop to get all pages.
For x = 1 To PAGES
' Set the GOOGLE_PAGE value to the page to parse.
Range("GOOGLE_PAGE").Select
ActiveCell.FormulaR1C1 = x

' Selected the SEARCH_URL, as this will need to be passed to the objHTTP.Open command.
Range("SEARCH_URL").Select

' Get the page using the objHTTP object.
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "GET", Selection.Value, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

' Define the pageToParse as teh response text of the "GET" method.
pageToParse = objHTTP.responseText
' Clear the objHTTP object.
Set objHTTP = Nothing
' Set expressionmatch as the strings matched using the regular expression defined above.
Set expressionmatch = regEx.Execute(pageToParse)

For Each expressionmatched In expressionmatch

' Got to get rid of any results on Google's servers.
If InStr(1, expressionmatched, "google", 1) > 0 Then

GoTo MOVE_ON

Else

Range("FIRST_URL_HERE").Select
ActiveCell.FormulaR1C1 = LCase(Left(Mid((expressionmatched), 2, 300), Len(expressionmatched) - 2))
Selection.Offset(1, 0).Select
ActiveWorkbook.Names.Add Name:="FIRST_URL_HERE", RefersToR1C1:=Selection

End If
MOVE_ON:

Next

' Setting up code to pause by a random amount of time between 4 and 15 seconds between pages of results.
Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + Int((15 - 4 + 1) * Math.Rnd + 4)
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Next x

End Sub

New stuff coming

I've been working on some code that will help me generate a list of URLS from which to parse email addresses. Using Google as a search engine, i'm able to generate a targeted list of URLs using keywords relevant to my audience.

I'll show you once i've got a decent V1.0 ready...