Website Login and scrape via VBA

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
I am wanting to scrape some data from the following website ETF Research Center after clicking the padlock icon (to right) and logging in via the pop up window that appears when the padlock icon is clicked.

Once logged in I need to get some text from the performance tab (the Description tab is the default when the page loads)
1605266822184.png



Then some values on the Fundementals tab (this requires a login) from a chart that displays the series value when the mouse is hovered over the column
The images below are blurred as a login is needed to see unblurred

Mouse not hovered over column on the right Mouse hovered over the column on the right
1605266934859.png
1605267003745.png


Rather than opening the webpage via IE I believe the page can be scraped using the Microsoft.xmlHTTP object.


Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Bump

Struggling to find something that will get me where I need to be......
 
Upvote 0
Some progress has been made but it is painfully slow and I am basically doing trial and error
VBA Code:
Sub WebScrape()

Dim Internet_Explorer As InternetExplorer

Dim objClass1 As IHTMLElementCollection
Dim objClass2 As IHTMLElementCollection
Dim objLogin As IHTMLElementCollection

Set Internet_Explorer = New InternetExplorer

Internet_Explorer.Visible = True

Internet_Explorer.Navigate ("https://www.etfrc.com/index.php")

Do While Internet_Explorer.ReadyState <> READYSTATE_COMPLETE

Loop

Set objClass1 = Internet_Explorer.Document.getElementsByClassName("nav navbar-nav navbar-right")
Set objClass2 = objClass1(0).getElementsByClassName("pe-7s-unlock")

objClass2(0).Click

Set objLogin = Internet_Explorer.Document.getElementsByClassName("modal fade in")

'Need code to complete the task

End Sub
 
Upvote 0
Finally managed to get the login working.

If anyone wants to provide feedback for a more efficient way of doing this then feel free!
VBA Code:
Sub WebScrape()

Dim appIE As InternetExplorer

Dim docIE As HTMLDocument

Dim objLogin1 As IHTMLElementCollection
Dim objLogin2 As IHTMLElementCollection
Dim objLogin3 As IHTMLElementCollection

Dim objUserName As IHTMLInputElement
Dim objPassword As IHTMLInputElement

Dim objLogin As Object

Set appIE = New InternetExplorer

appIE.Visible = True

appIE.Navigate ("https://www.etfrc.com/index.php")

Do While appIE.ReadyState <> READYSTATE_COMPLETE

Loop

Set docIE = appIE.Document

Set objLogin1 = docIE.getElementsByClassName("nav navbar-nav navbar-right")
Set objLogin2 = objLogin1(0).getElementsByClassName("pe-7s-unlock")

objLogin2(0).Click

Set objLogin3 = appIE.Document.getElementsByClassName("modal fade in")

Application.Wait (Now + TimeValue("00:00:03"))

Set objUserName = docIE.getElementById("Email")
Set objPassword = docIE.getElementById("Password")

objUserName.Value = "username"
objPassword.Value = "password"

For Each objLogin In docIE.getElementsByClassName("btn btn-primary")
   If objLogin.innerText = " Log In " Then
      objLogin.Click
      
      Exit For
      Else
   End If
Next

End Sub
 
Upvote 0
you might consider XMLHTTP. here is the code to log in to that site

VBA Code:
    Dim MyResult As String, MyRequest, XMLhttp
   
    Set XMLhttp = CreateObject("MSXML2.XMLHTTP.6.0")
    MyURL = "https://www.etfrc.com/accounts/auth.php"
    MyRequest = "email=XXXXX&password=XXXXXXX" ' insert email and password
    With XMLhttp
        .Open "POST", MyURL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send MyRequest
        MyResult = .responseText
    End With

MyResponse is the info on the page after login
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top