how to get bing api search results in MsOffice VBA

For the loyal VBA fans a quick snippet to grab search results from Bing in Access or Excel. There are few articles on the web about that, and that is a shame. So lets add some content on it to the web : first in MsAccess VBA and then in Excel, with a sample workbook added.

Working with the BING API requires an Application Id you can get at the Bing website.

Accessing the Bing Api in MsAccess VBA

In the the VBA Ide, through the menu Tools References I add a reference to the XML object library that contains the XMLHTTPRequest object, enabling me to make HTTP requests from VBA in the MsAccess database.

The simple part is the http request string :

        Dim MyKeyword As String
        MyKeyword = "seo"
        Dim requestString As String
        requestString = ""

        Dim AppId As String
        AppId = "(get one at the bing website)"

       ' Common request fields (required)
        requestString = requestString & _
        "AppId=" & AppId & "&Query=" & MyKeyword & "&Sources=Web"

        ' Common request fields (optional)
        requestString = requestString & _
              "&Version=2.0" _
            & "&Market=en-us" _
            & "&Adult=Moderate" _
            & "&Options="

        ' Web-specific request fields (optional)
        requestString = requestString & _
              "&Web.Count=10" _
            & "&Web.Offset=0" _
            & "&Web.Options=DisableHostCollapsing+DisableQueryAlterations"

Then comes the working part, sending the actual request and receiving the XML response (objSvrHTTP.responseText) containing the search data. I use a DomDocument object and XPath to get at the XML data.

First sending the request

    objSvrHTTP.Open "GET", requestString, False
    objSvrHTTP.send requestString

then creating a domdocument and receiving the response in it

    'Create the DomDocument Object
    Dim oDoc As MSXML2.DOMDocument
    Set oDoc = CreateObject("MSXML2.DOMDocument")
    oDoc.async = False
    oDoc.validateOnParse = False
    'Load the response in the DomDocument Object
    Dim fSuccess As Boolean
    fSuccess = oDoc.loadXML(objSvrHTTP.responseText)
    If Not fSuccess Then
        MsgBox "failed"
        Exit Sub
    End If

The response uses two namespaces and the data we actually want is in the “web” namespace, so before we start selecting nodes in the xml-tree we first indicate what namespace we want to access :

    oDoc.SetProperty "SelectionNamespaces", "xmlns:web=''"

The total search result pages is a top level node in the namespace :

    Set xmlnode = oDoc.selectSingleNode("//web:Total")
    Debug.Print xmlnode.Text

…but the good stuff is in the “WebResult” nodes in the xml tree, I can use an XPath reference //web:Webresult to access that node-collection and with a simple for-next iterate through the collection, selecting the single nodes containing the actual data.

    Set oChildren = oDoc.selectNodes("//web:WebResult")
    For Each oResult In oChildren
        Debug.Print oResult.selectSingleNode("./web:Title").Text
        Debug.Print oResult.selectSingleNode("./web:Description").Text
        Debug.Print oResult.selectSingleNode("./web:Url").Text
        Debug.Print oResult.selectSingleNode("./web:DisplayUrl").Text
        Debug.Print vbCrLf

I output to the Immediate window aka Debug (under View, Immediate Window or Ctrl+G)

What can we do with it ? Let’s make a table for date, keyword, url, title, description, position

and add some code to store the results we retrieve

    Dim table1 As DAO.Recordset
    Set table1 = CurrentDb.OpenRecordset("Table1", dbOpenTable)
    'counter for position    
    i = 0
    Set oChildren = oDoc.selectNodes("//web:WebResult")
    For Each oStruct In oChildren

       i = i + 1
       With table1
            'data from bing
            !TITLE = oStruct.selectSingleNode("./web:Title").Text
            !Description = oStruct.selectSingleNode("./web:Description").Text
            !url = oStruct.selectSingleNode("./web:Url").Text
            'and some additional data
            !MYDATE = Now()
            !POSITION = i
            !KEYWORD = MyKeyword
            'store the record
        End With



There now that scratches the seo itch :

MsAccess stuff is a bit hard to put on the web as sample, so I’ll make a quick one in Excel and put it up for download.

Accessing the Bing Api in Excel VBA

there you go : on sheet 1 you can fill in your AppID and keyword, click the butt(on…

…and on sheet 2 you get the top 10 search results

It uses the cells B1 and B2 as named ranges for MyKeyword and AppID, and has the code in the enclosed module. It also contains one of my AppId’s so no querying nasty shit, please.

google suggest scraper (php & simplexml)

Today’s goal is a basic php Google Suggest scraper because I wanted traffic data and keywords for free.

Before we start :

google scraping is bad !

Good People use the Google Adwords API : 25 cents for 1000 units, 15++ units for keyword suggestion so they pay 4 or 5 dollar for 1000 keyword suggestions (if they can find a good programmer which also costs a few dollars). Or they opt for SemRush (also my preference), KeywordSpy, Spyfu, and other services like 7Search PPC programs to get keyword and traffic data and data on their competitors but these also charge about 80 dollars per month for a limited account up to a few hundred per month for seo companies. Good people pay plenty.

We tiny grey webmice of marketing however just want a few estimates, at low or better no cost : like this :

data num queries
google suggest 57800000
google suggestion box 5390000
google suggest api 5030000
google suggestion tool 3670000
google suggest a site 72700000
google suggested users 57000000
google suggestions funny 37400000
google suggest scraper 62800
google suggestions not working 87100000
google suggested user list 254000000

Suggestion autocomplete is AJAX, it outputs XML :

< ?xml version="1.0"? >
       <suggestion data="senior quotes"/>
       <num_queries int="30000000"/>
       <suggestion data="senior skip day lyrics"/>
       <num_queries int="441000"/>

Using SimpleXML, the PHP routine is as simple as querying g00gle.c0m/complete/search?, grabbing the autocomplete xml, and extracting the attribute data :

        if ($_SERVER['QUERY_STRING']=='') die('enter a query like http://host/filename.php?query');
	$contentstring = @file_get_contents("http://g00gle.c0m/complete/search?output=toolbar&q=".urlencode($kw));  
  	$content = simplexml_load_string($contentstring );

        foreach($content->CompleteSuggestion as $c) {
            $term = (string) $c->suggestion->attributes()->data;
            //note : traffic data is sometimes missing   
            $traffic = (string) $c->num_queries->attributes()->int;
            echo $term. " ".$traffic . "
" ;

I made a quick php script that outputs the terms as a list of new queries so you can walk through the suggestions :

The source is as text file up for download overhere (rename it to suggestit.php and it should run on any server with php5.* and simplexml).

oauth twitter posting

I noticed Twitter do not support Basic HTTP Auth but oAuth, so I had a look around at the available libraries and EPITwitter by Jaisen Mathai works fine as replacement for cUrl http auth.

I noticed when testing it with Twitter the whole key set is stored on the dev.twitter subdomain and not in the application directory where you register an application initially. When my first tests failed I think my keys were marked invalid, one working solution is revoking the application access and renewing the keys, then test again and it works. Once it works, it works flawless.

After registering an app at Twitter and getting the keys, it is a nobrainer.

$status = 'some bogus text';

require_once(WP_PLUGIN_DIR . '/myplugin/epitwitter/EpiCurl.php');	
require_once(WP_PLUGIN_DIR . '/myplugin/epitwitter/EpiOAuth.php');
require_once(WP_PLUGIN_DIR . '/myplugin/epitwitter/EpiTwitter.php');
$consumer_key = '';
$consumer_secret = '';
$oauth_token ='';
$oauth_token_secret = '';
$twitterObj = new EpiTwitter($consumer_key, $consumer_secret, $oauth_token, $oauth_token_secret);
$twitterObjUnAuth = new EpiTwitter($consumer_key, $consumer_secret); 
	   //$creds = $twitterObj->get('/account/verify_credentials.json');
$status = $twitterObj->post('/statuses/update.json', array('status' => $status));