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

wornhall

Board Regular
Joined
Feb 16, 2016
Messages
67
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.
Code:
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

wornhall

Board Regular
Joined
Feb 16, 2016
Messages
67
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.
Code:
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

Board Regular
Joined
Feb 16, 2016
Messages
67
Yet another question: when in MSXML2.XMLHTTP60, is there a method/procedure to show the page in a browser window?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top