Website Login and scrape via VBA

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,477
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,477
Bump

Struggling to find something that will get me where I need to be......
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,477
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
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,477
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,707
Messages
5,573,725
Members
412,549
Latest member
ThomDubya
Top