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

Indian Market Data Download (NSE & BSE)

Recently while browsing the Indian stock market web sites (NSE and BSE), a thought came by to build a automated data downloader for the historical equity prices to be analyzed in excel.

Henceforth the following tool was built to do the same. This tool extracts the data from the exchanges (NSE and BSE) website (Historical Data Section) for the equity and the time period requested (Currently the tool downloads the daily data only but easily configurable via code to monthly/yearly).

The data downloaded via tool is placed on separate sheets for both NSE and BSE, upon which any post download analysis can be conducted..

Hope this tool helps you in outperform the markets better. Happy trading..

 


modNse.bas

Option Explicit

Private Const NSE_URL As String = "http://www.nseindia.com/products/dynaContent/common/productsSymbolMapping.jsp?symbol={0}&segmentLink=3&symbolCount=1&series=EQ&dateRange=+&fromDate={1}&toDate={2}&dataType=PRICEVOLUME"

Public Sub GetNseData()

    Dim request As WinHttp.WinHttpRequest
    Dim htmlDocument As MSHTML.htmlDocument
    Dim rowCollection As Variant
    Dim htmlRow As Variant
    Dim rowSubContent As Variant
    Dim rowSubData As Variant
    Dim rowCount As Integer, colCount As Integer
    Dim anchorRange As Range
    Dim prepUrl As String
    Dim timeOut As Long

    On Error GoTo errHandler
    
    timeOut = 10000
    DataDumpNse.Cells.Clear
    Set anchorRange = DataDumpNse.Range("A1")

    Set htmlDocument = New MSHTML.htmlDocument

    Set request = New WinHttp.WinHttpRequest

    prepUrl = Replace(NSE_URL, "{0}", modCommonFunctions.URLEncode(ThisWorkbook.Names("NSE_TICKER").RefersToRange.Value))
    prepUrl = Replace(prepUrl, "{1}", Format(ThisWorkbook.Names("NSE_FROM_DATE").RefersToRange.Value, "dd-mm-yyyy"))
    prepUrl = Replace(prepUrl, "{2}", Format(ThisWorkbook.Names("NSE_TO_DATE").RefersToRange.Value, "dd-mm-yyyy"))
    request.Open "GET", prepUrl
    request.SetTimeouts timeOut, timeOut, timeOut, timeOut
    request.Send
    htmlDocument.body.innerHTML = request.ResponseText

    rowCount = 0
    colCount = 0

    Set rowCollection = htmlDocument.getElementsByTagName("tr")
    For Each htmlRow In rowCollection
        Set rowSubContent = htmlRow.getElementsByTagName("th")
        If rowSubContent.Length <> 0 Then
            For Each rowSubData In rowSubContent
                anchorRange.Offset(rowCount, colCount).Value = rowSubData.innerText
                colCount = colCount + 1
            Next rowSubData
        Else
            Set rowSubContent = htmlRow.getElementsByTagName("td")
            For Each rowSubData In rowSubContent
                anchorRange.Offset(rowCount, colCount).Value = rowSubData.innerText
                colCount = colCount + 1
            Next rowSubData
        End If

        colCount = 0
        rowCount = rowCount + 1
    Next htmlRow
    
    Exit Sub
    
errHandler:
    MsgBox Err.Description, vbExclamation, "Download Error"
    On Error GoTo 0
    Err.Clear
End Sub

modBse.bas

Option Explicit

Private Const BSE_URL As String = "http://www.bseindia.com/stockinfo/stockprc2_excel.aspx?scripcd={0}&FromDate={1}&ToDate={2}&OldDMY=D"

Public Sub GetBseData()

    Dim request As WinHttp.WinHttpRequest
    Dim lineSplit As Variant
    Dim rowCount As Long
    Dim line As Variant
    Dim anchorRange As Range
    Dim url As String
    
    DataDumpBse.Cells.Clear
    Set anchorRange = DataDumpBse.Range("A1")
    
    Set request = New WinHttp.WinHttpRequest
    
    url = Replace(BSE_URL, "{0}", ThisWorkbook.Names("BSE_TICKER").RefersToRange.Value)
    url = Replace(url, "{1}", Format(ThisWorkbook.Names("BSE_FROM_DATE").RefersToRange.Value, "mm/dd/yyyy"))
    url = Replace(url, "{2}", Format(ThisWorkbook.Names("BSE_TO_DATE").RefersToRange.Value, "mm/dd/yyyy"))
    
    request.Open "GET", url
    request.Send
    lineSplit = Split(request.ResponseText, vbCrLf)
    
    rowCount = 0
    For Each line In lineSplit
        anchorRange.Offset(rowCount, 0).Value = line
        rowCount = rowCount + 1
    Next line
    
    DataDumpBse.Range("A:A").TextToColumns DataDumpBse.Range("A1"), xlDelimited, Comma:=True
    
End Sub

Download solution


References:
Link1: http://www.nseindia.com/
Link2: http://www.bseindia.com/