Web Scraping with MSXML for Etrade values - close but not there.

[wornhall]

The following code is my attempt to glean my data from my account at Etrade. The site is meant to confuse, but the code sample has copies of the relevant divisions and id's. Any help or advice will be greatly appreciated. There are six such amounts.

Sub FetchET()
' get Etrade values, Warren Hall, 4/7/2017


    Dim XMLPage  As New MSXML2.XMLHTTP60
    Dim HTMLdoc  As New MSHTML.HTMLDocument
    Dim HTMLRows As MSHTML.IHTMLElementCollection
    Dim HTMLRow  As MSHTML.IHTMLElement
    Dim HTMLCell As MSHTML.IHTMLElement
    Dim eValue   As Double
    Dim URL      As String
    Dim RowNum   As Integer, ColNum As Integer
    Dim intLength As Double
    Dim tempData As String


    Sheets("Etrade").Select '   the worksheet in which to apply the fetched values.
    Range("A1", "z10").Delete
    Range("A1") = Now
    RowNum = 2: ColNum = 1

    ' less-than signs have been transformed to "%" in order to show html:
    '%td class="text-right secondary ng-scope ng-binding"
    'ng-if="!account.unAvailable">$9,538.34%!-- ngIf: parseInt(account.accountValue)
    '> 0 && account.instType=='GTADP' -->%/td>


    ' When I "inspect" the amount, after F12, on the page I seek on the screen, the above lines appear.
    ' Can you please help me determine the optimum methods/procedures to complete this task?
    
    URL = "https://us.etrade.com/etx/hw/accountshome" ' this works for me but won't for you
        '   because a password manager completes the input automagically for my personal account.
    XMLPage.Open "GET", URL, False
    XMLPage.send
    HTMLdoc.body.innerHTML = XMLPage.responseText
    
    Set HTMLdoc = HTMLdoc.getElementsByid("accountsdata")   '   six of these values are present
    Cells(3, 1) = HTMLRows
    On Error GoTo errOut
    Set HTMLRow = HTMLRows.Item
    
    'intLength = InStr(1, HTMLRow, "account", 0)
    
    'If intLength > 1 Then
    '    MsgBox "foundit " & intLength
    'End If
     
    RowNum = 2
    'intLength = HTMLDoc.Length - 1
    
    'Debug.Print intLength
    '=================
    For Each HTMLRow In HTMLRows
        ColNum = 1
        For Each HTMLCell In HTMLRow.Children
            Cells(RowNum, ColNum) = HTMLCell.innerText
            ColNum = ColNum + 1
        Next HTMLCell
        RowNum = RowNum + 1
    Next HTMLRow
    
    Exit Sub
errOut:
    Debug.Print "Error of some sort. "
End Sub

Thanks for looking.

 

[wornhall]

Here is another mystery: I am attempting to scrape my credit card balance from Capital One's web page. I am not succeeding. Is it my VBA coding or is it inherent to the site? I need help and advice.

Sub FetchCapOne()

    Dim XMLpage  As New MSXML2.XMLHTTP60
    Dim HTMLDoc  As New MSHTML.HTMLDocument
    Dim HTMLrows As MSHTML.IHTMLElementCollection
    Dim HTMLrow  As MSHTML.IHTMLElement
    Dim HTMLCell As MSHTML.IHTMLElement
    Dim eValue   As String
    Dim URL      As String
    Dim RowNum   As Integer, ColNum As Integer
    
    Sheets("Capital One").Select
    Range("A1", "z10").Delete
    Range("A1") = Now
    
    URL = "https://www.capitalone.com/"
    XMLpage.Open "GET", URL, False  ' banking on "Sticky Password" to slip in the pwd,etc.
    XMLpage.send
    Application.Wait (Now + TimeValue("0:00:07")) ' but he takes a little time.
    HTMLDoc.body.innerHTML = XMLpage.responseText
    ' %div class="amount">%span id="acct0_current_balance_amount"> $752.43
    ' the above is seen when "inspecting" on-line, and when looking at source BUT they
    ' have %div id="accountsdata" style="display:none;"> before the area where the above
    ' id's and classes are found. Nothing is returned in the following lines.
    ' INSTR commands return zero.
    Set HTMLrows = HTMLDoc.getElementsByClassName("amount") ' the first of five is wanted.
    Set HTMLrow = HTMLrows.getElementById("acct0_current_balance_amount") 'single id.
    Range("A2").Select
    Range("A2") = HTMLrow(0).innerText ' That is the value on the credit card.


End Sub

 

[wornhall]

Yet another question: when in MSXML2.XMLHTTP60, is there a method/procedure to show the page in a browser window?