Key Word Web Parser Tool (Selenium Vs WinHttp)

With this post I wanted to highlight two interesting techniques for web text parsing via VBA using Selenium framework and WinHttp.

Task:

Given the set of key words (comma separated list) we want to count the occurrence of the key word in the html body of the web page (giving us the popularity of the key word within the set of web pages).

Solution One:

“Selenium” as popularly known in the market, is a very robust web testing framework and used in writing automated testing for Web UI. So recently I stumbled upon a very interesting project which is a wrapper written for using selenium via VBA exposing very strong integration of selenium functionalities with VBA. The project home page is very good in describing upon the features of the same.

 

Solution Two:

Using the native WinHttp solution in VBA, we are downloading the HTML text of the web page via “GET” command and thus counting the key word occurrence in the same.

 

Comparison:

For the task in hand I found that using the WinHttp solution wins over the Selenium based methodology purely by the performance and over head the Selenium incurs for the same.

The Selenium framework has its own very important place to automate the Web UI task in hand when JavaScript is also in play with modern day web pages. Selenium plays a very important role where one can script all the user actions that have to be performed on the web, like filling forms, clicking buttons, checking options and more with the power of VBA.

Note: There is a large difference in count values between the Selenium and WinHttp based tools, for the reason that for former I am using Instr function to get the key word count, where as for the later I a using custom written function for the key word count.


Download solution


References:
Link1: http://code.google.com/p/selenium-vba/

Excel VBA Data Warehouse Generator Tool

Recently I was observing our database development team, and I observed that much of my colleagues were struggling with lots of boiler plate code. In the process of generating Dimension and Fact tables (in snow flaked schema), I thought it would be nice to create an easy to use tool to generate database schema with the following rules in place:

1. Each dimension table would have a primary key, combined with identity auto generation.
2. A dimension table may have referential constraint upon another dimension table
3. A fact table will have multiple referential constraints from multiple dimension tables but not from any other factual table.
4. A fact table will not have a primary key nor an identity column.

This tool is primarily an assisting tool for repetitive process occurred in the data warehouse table structure generation, though for any other design requirements developer intervention is very much required to meet the special conditions.



To use the tool, there is a main page where all the relevant information needs to be set like connection string, script folder path and updated database on which the operation needs to be performed once the list is refreshed.

Control Page:


Dimension Page:


Fact Page:


Then there is a SQL Server Management Studio style dimension and fact table generation worksheets where user can set the desired columns required according to the business needs along with integrated drop down list on the Table Link column where the refrence dimension tables can be selected(The table name for dimension and fact wil be appended by keywords "Dim" and "Fact" respectivly. And also for the dimension tables the key columns as they will be auto generated via VBA and appended terms like "Id").


Download solution

File - Directory (Tree Map) in Excel

Being a software developer and in my quest of achieving an organised methodology for saving my personal files, I many times end up saving same file in multiple locations. Ahh…

So many times we can’t keep track of how our file structure if expanding underlying the cover of multiple nested folders. So, I thought to develop a tool in Excel/VBA which could visually represent me the file directory structure in the form of tree and also could provide me an easy to navigate mechanism for the nodes I wish to explore.

Some thing like this …



This tool request for the root node location to start its parsing and with the condition provided by the user to layout the sub folder (recursively) by a check box you will achieve the above result.

Public Sub FileLister(rootPath As String, exploreSubFolder As Boolean)

    Dim file As Scripting.file, folder As Scripting.folder, subfolder As Scripting.folder

    If fso Is Nothing Then
        Set fso = New FileSystemObject
    End If

    Set folder = fso.GetFolder(rootPath)

    If (folder.SubFolders.Count > 0) And exploreSubFolder Then
    
        For Each subfolder In folder.SubFolders
            
            nestLevel = nestLevel + 1
            ReDim Preserve navigated(nestLevel)
            
            If navigated(nestLevel) = 0 Then
                For Each file In folder.Files
                    'Record files in folder
                    anchorRange.Offset(printRow, nestLevel + 1).Value = file.Name
                    anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), file.path, , , "Link"
                    printRow = printRow + 1
                Next file
            End If
            
            anchorRange.Offset(printRow, nestLevel + 1).Value = subfolder.Name
            anchorRange.Offset(printRow, nestLevel + 1).Interior.Color = 10092543  'Light Yellow
            anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), subfolder.path, , , "Link"
            printRow = printRow + 1

            navigated(nestLevel) = 1
            FileLister subfolder.path, exploreSubFolder
            nestLevel = nestLevel - 1
            
        Next subfolder
        
    Else
    
        nestLevel = nestLevel + 1
        For Each file In folder.Files
            'Record files in folder
            anchorRange.Offset(printRow, nestLevel + 1).Value = file.Name
            anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), file.path, , , "Link"
            printRow = printRow + 1
        Next file
        For Each subfolder In folder.SubFolders
            anchorRange.Offset(printRow, nestLevel + 1).Value = subfolder.Name
            anchorRange.Offset(printRow, nestLevel + 1).Interior.Color = 10092543  'Light Yellow
            anchorRange.Offset(printRow, 0).Hyperlinks.Add Control.Range("A12").Offset(printRow, 0), subfolder.path, , , "Link"
            printRow = printRow + 1
        Next subfolder
        nestLevel = nestLevel - 1
        
        If exploreSubFolder Then
            navigated(nestLevel) = 0
        End If
        
    End If
End Sub

Download:


Download solution

Analytics on Analytics Hmm..?? (Google Analytics to Excel)

Recently I came across a project requirement which interested me quite a lot; the project actually did analytics on top of Google analytics data (though Google Analytics does indeed provide a lot of interesting analytics, via its feature rich platform). Interestingly I don’t understand where the human quest for analytics will end, but it keeps me on my toes to build some exciting tools.

So the challenge presented to me was to download the Google analytics data to excel on demand by the user. This presented me with the following technical challenges:

1. User Authentication for access to Google API
2. Google analytics API mechanism for data download



Thus the major key to the excel model is in the authentication subroutine which retrieves the authentication key for the future request authentication based on user credentials as follows:

Private Function getGAauthenticationToken(ByVal email As String, ByVal password As String)
    Dim authResponse As String
    Dim authTokenStart As Integer
    Dim URL As String
    Dim authtoken As String

    If email = "" Then
        getGAauthenticationToken = ""
        Exit Function
    End If

    If password = "" Then
        getGAauthenticationToken = "Input password"
        Exit Function
    End If
    password = modCommonFunctions.uriEncode(password)

    On Error GoTo errhandler
    Dim objhttp As Object
    Set objhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    URL = "https://www.google.com/accounts/ClientLogin"
    objhttp.Open "POST", URL, False
    objhttp.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objhttp.setTimeouts 1000000, 1000000, 1000000, 1000000
    objhttp.send ("accountType=GOOGLE&Email=" & email & "&Passwd=" & password & "&service=analytics&Source=Excel based analytics. (c) Amol Pandey 2012")
    authResponse = objhttp.responseText
    If InStr(1, authResponse, "BadAuthentication") = 0 Then
        authTokenStart = InStr(1, authResponse, "Auth=") + 4
        authtoken = Right(authResponse, Len(authResponse) - authTokenStart)
        getGAauthenticationToken = authtoken
    Else
        getGAauthenticationToken = "Authentication failed"
    End If
    Exit Function
errhandler:
    getGAauthenticationToken = "Authentication failed"
End Function

Rest of the request are appended by the authentication id obtained from the previous module for request authentication for the data download from API, leaving us with the XML formatted data to lay out as desired on to excel worksheet.

Private Sub getDetailedGoogleAnalyticsData(authtoken As String, profileNumber As Long, startDate As Date, endDate As Date, metrics() As String, Optional dimensions As Variant)

    Dim httpUrl As String
    Dim request As MSXML2.ServerXMLHTTP60
    Dim requstTimeOut As Long
    Dim requestReponseText As String
    Dim startDateString As String, endDateString As String
    Dim item As Variant, subItem As Variant, responseXml As MSXML2.DOMDocument
    Dim rowCount As Integer
    'clear Range
    RawData.Range("F5:F6").ClearContents
    RawData.Range("H2").Resize(MAX_ROWS, 4).ClearContents
    rowCount = 0

    startDateString = Year(startDate) & "-" & Right("0" & Month(startDate), 2) & "-" & Right("0" & Day(startDate), 2)
    endDateString = Year(endDate) & "-" & Right("0" & Month(endDate), 2) & "-" & Right("0" & Day(endDate), 2)

    httpUrl = "https://www.google.com/analytics/feeds/data?ids=ga:" & profileNumber & "&start-date=" & startDateString & "&end-date=" & endDateString & "&max-results=10000&metrics="

    For Each item In metrics
        httpUrl = httpUrl & "ga:" & item & ","
    Next item
    httpUrl = Left(httpUrl, Len(httpUrl) - 1)


    'Aggreagted values
    Set request = New MSXML2.ServerXMLHTTP60
    requstTimeOut = 1000000
    request.Open "GET", httpUrl, False
    request.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    request.setRequestHeader "Authorization", "GoogleLogin Auth=" & authtoken
    request.setRequestHeader "GData-Version", "2"
    request.setTimeouts requstTimeOut, requstTimeOut, requstTimeOut, requstTimeOut
    request.send ("")
    Set responseXml = request.responseXml
    For Each item In responseXml.ChildNodes(1).ChildNodes
        If item.nodeName = "dxp:aggregates" Then
            For Each subItem In item.ChildNodes
                If subItem.Attributes.Length > 0 Then
                    RawData.Range("F5").Offset(rowCount, 0).Value = subItem.Attributes(3).Value
                    rowCount = rowCount + 1
                End If
            Next subItem
        End If
    Next item
    If Not (IsError(dimensions)) Then
        httpUrl = httpUrl + "&dimensions="
        For Each item In dimensions
            httpUrl = httpUrl & "ga:" & item & ","
        Next item
        httpUrl = Left(httpUrl, Len(httpUrl) - 1)
        Set request = New MSXML2.ServerXMLHTTP60
        requstTimeOut = 1000000
        request.Open "GET", httpUrl, False
        request.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
        request.setRequestHeader "Authorization", "GoogleLogin Auth=" & authtoken
        request.setRequestHeader "GData-Version", "2"
        request.setTimeouts requstTimeOut, requstTimeOut, requstTimeOut, requstTimeOut
        request.send ("")
        Set responseXml = request.responseXml
        rowCount = 0
        For Each item In responseXml.ChildNodes(1).ChildNodes
            If item.nodeName = "entry" Then
                RawData.Range("H2").Offset(rowCount, 0).Value = item.ChildNodes(4).Attributes(1).Value    'Country
                RawData.Range("H2").Offset(rowCount, 1).Value = item.ChildNodes(5).Attributes(1).Value    'region
                RawData.Range("H2").Offset(rowCount, 2).Value = item.ChildNodes(6).Attributes(3).Value    'visits
                RawData.Range("H2").Offset(rowCount, 3).Value = item.ChildNodes(7).Attributes(3).Value    'pageviews
                rowCount = rowCount + 1
            End If
        Next item
    End If
End Sub

The attached excel model takes in the user details, then fetches the list of web sites monitored by the user account, and then on second step downloads the country wise detailed data of page visit and views for the selected site via drop down.

Happy analytics…

Download:


Download solution


Refrences:
Link1: http://www.automateanalytics.com/2009/08/excel-functions-for-fetching-data.html
Link2: https://developers.google.com/analytics/
Link3: https://developers.google.com/analytics/devguides/reporting/core/dimsmets