Rob Hammond

Excel’s built-in web features are pretty frustrating when you want to do more with the web than import a static HTML table to a predefined set of cells.

I’ve often wanted to be able to update the contents of a cell based on dynamic parameters passed into a URL, and not found a decent, easy way of doing this. The official Office website shows you how to do this the Microsoft way, but lo and behold that doesn’t actually translate to real-world uses very well.

Say for example you want to fill a column of cells with the ranking for a given list of keywords, a function similar to that shown below (where the URL could potentially be defined in the E column) would be very useful:

Provided you had an API into ranking data (see Perl code below), this should be an easy operation, but this doesn’t seem to be something Excel does out of the box.

I’m sure there are tons of xll’s and paid for solutions out there to do the same thing, but I want something that’s flexible, and ideally free. Hence the user-defined function below can replace Excel’s awkward built-in parameter handling quite easily and for lots of different uses:

<br />
 &#8216; Name the function anything you like &#8211; the variable parameters to pass in this instance are keyword, domain, number of results<br />
 Public Function getURL(kw As String, domain As String, num As Integer)</p>
<p>&#8216; Build the URL from the parameters passed in<br />
 URL = &quot;http://www.example.com/api/script.pl?kw=&quot; &amp; kw &amp; &quot;&amp;domain=&quot; &amp; domain &amp; &quot;&amp;num=&quot; &amp; num</p>
<p>&#8216; Uncomment to debug the request URL<br />
 &#8216;Debug.Print &quot;Request URL: &quot; &amp; URL</p>
<p>Dim objhttp As Object</p>
<p>Set objhttp = CreateObject(&quot;MSXML2.ServerXMLHTTP.6.0&quot;)</p>
<p>objhttp.Open &quot;GET&quot;, URL, False<br />
 objhttp.setRequestHeader &quot;Content-type&quot;, &quot;application/x-www-form-urlencoded&quot;<br />
 &#8216; If your URL uses basic HTTP authentication (like mine did), uncomment the line below and replace the contents of [] with your base 64 encoded credentials<br />
 &#8216;objhttp.setRequestHeader &quot;Authorization&quot;, &quot;Basic &quot; &amp; &quot;[base64_encode[user:pass]]&quot;<br />
 objhttp.send (&quot;&quot;)</p>
<p>Dim Response As String<br />
 Response = objhttp.responseText</p>
<p>&#8216; To debug the output into Excel&#8217;s error console, uncomment the line below<br />
 &#8216;Debug.Print &quot;GA data feed response: &quot; Response</p>
<p>&#8216; Load contents of URL into the cell that&#8217;s called the function<br />
 getURL = Response</p>
<p>End Function<br />
 

I’m no VBA whiz or anything so I’m sure the above code could be tweaked vastly to make it faster, more robust, and more flexible for variable numbers of query, different query types etc (open to suggestions ), but hey, it’s a quick & easy way to solve a problem.

The Perl code: as mentioned above, the VBA code was written specifically to grab ranking data from an API. Hence there’s no fancy XPath expressions in the VBA, or even any attempt to parse the output. This is another potential improvement, but I try and keep Excel’s interactions with the web & text processing to an absolute minimum because Excel + teh internets = sloooow. In this instance, the script output is literally just the numeric data, so any processing is done by the faster Perl code.

The code below relies on a custom scraping library (SearchMarketing::Crawl::GoogleNatural) – I’ll leave that code up to you to re-create

It’ll only check the top 100 results but for most purposes that’s more than enough. If there is more than one result for the given domain, it’ll print the numbers separated by an ampersand and can then be manipulated in Excel if necessary (I’ll just about trust Excel for that…)

<br />
#!/usr/bin/perl -wT<br />
use strict;<br />
use WWW::Mechanize;<br />
use SearchMarketing::Crawl::GoogleNatural;<br />
use CGI qw(:standard);</p>
<p>my $kw          = param(&quot;kw&quot;);<br />
my $num_results = param(&quot;num&quot;);<br />
my $domain      = param(&quot;domain&quot;);</p>
<p>print header;</p>
<p>getPos($kw,$num_results,$domain);</p>
<p>sub getPos {</p>
<p>  my $kw          = $_[0];<br />
  my $num_results = $_[1];<br />
  my $domain      = $_[2];</p>
<p>  my %results = googleUK($kw, $num_results);<br />
  my $count = 0;<br />
  foreach my $position ( keys %results ) {<br />
    my $title    = $results{$position}[0];<br />
    my $snippet  = $results{$position}[1];<br />
    my $dest_url = $results{$position}[2];<br />
    my $disp_url = $results{$position}[3];</p>
<p>    if ($dest_url =~ m{^https?://[^/]*$domain/?.*$}i) {</p>
<p>      if ($count &gt; 0) {<br />
        print &quot;&amp;&quot;;<br />
      }<br />
      print $position;<br />
      $count++;<br />
    }<br />
  }<br />
  if ($count == 0) {<br />
    print &quot;N/A&quot;;<br />
  }<br />
}<br />