VBA - scraping websites videos | Excel VBA Part 57.4 - Finding Web Elements in Selenium

Posted by Andrew Gould on 20 January 2021

This video is a reference for the web element selection strategies in Selenium Basic. You'll learn how to refer to elements by ID, Name, Tag, Class, Link Text and Partial Link Text. You'll also learn the basics of writing CSS Selectors and XPath expressions.

This video has the following accompanying files:

File name Type Description
Pt57_4 Finding Web Elements in Selenium.xlsm Excel workbook with macros

Click to download a zipped copy of the above files.

Making a  video bigger

You can increase the size of your video to make it fill the screen like this:

View full screen

Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.

 

When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.

Improving the quality of a video

To improve the quality of a video, first click on the Settings icon:

Settings icon

Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.

 

Choose to change the video quality:

Video quality

Click on Quality as shown to bring up the submenu.

 

The higher the number you choose, the better will be your video quality (but the slower the connection speed):

Connection speed

Don't choose the HD option unless you have a fast enough connection speed to support it!

 

Is your Wise Owl speaking too slowly (or too quickly)?  You can also use the Settings menu above to change your playback speed.

This page has 3 threads Add post
15 Jun 21 at 05:58

Hi Andrew,

I would like to down download of element with you.

WebPage: https://sg.finance.yahoo.com/quote/MSFT/key-statistics?p=MSFT

Data: Market cap (intra-day) : 1.96T

I manage to download as 1.96 but how do I get the full underlying data of 1,957,379,833,856.0

 

Thanks

15 Jun 21 at 06:12

Hi Ronnie,

The underlying number doesn't appear to be on the page so I don't know, sorry!

14 Jun 21 at 03:54

Hi Andrew,

 

Thanks for the reply.  I wrote some macro in excel VBA using Selenium. but the download seems very slow, anyway to solve the issue?

 

Sub GetCurrency()

Call ClearCurrency

    Dim ws As Worksheet
    Dim SingleRow As Range
    Dim ListofRows As Range
    Dim SingleColumn As Range
    Dim ListofColumns As Range
    Dim MyRange As Range
    Dim RowNum As Integer
    Dim ColNum As Integer

    Set ws = ThisWorkbook.Worksheets("Currency")

'=================================================================
'Setting Range
'=================================================================
    With ws
        Set ListofRows = Range("A3", Range("A2").End(xlDown))
        Set ListofColumns = Range("B1", Range("B1").End(xlToRight))
        Set MyRange = ws.Range(ListofRows, ListofColumns)
    End With
'=================================================================
'Loop Through RIC
'=================================================================
    RowNum = 3
    
    For Each SingleRow In ListofRows

        Dim URL As String: URL = "https://sg.finance.yahoo.com/quote/SGD=X"
        Set cd = New Selenium.ChromeDriver
        cd.AddArgument "--headless"
        cd.Start
        cd.Get URL
        Application.Wait Now + TimeValue("00:00:20")
            
'=================================================================
'Loop Through XPATH
'=================================================================
        
        Dim PathInput As Selenium.WebElement
        Dim PathResult As Variant
        
        ColNum = 2
        
        For Each SingleColumn In ListofColumns
            Set PathInput = cd.FindElementByXPath(SingleColumn)
            cd.Wait 1000
            PathResult = cd.FindElementByXPath(SingleColumn).Text
            
'=================================================================
'Output to Excel
'=================================================================
           
            Cells(RowNum, ColNum).Value = PathResult
            ColNum = ColNum + 1
        Next SingleColumn
        
        RowNum = RowNum + 1
    
    Next SingleRow
    
    Range("A1").CurrentRegion.Font.Size = 9
    Range("A1").CurrentRegion.Font.Name = "Calibri"
    Range(Range("c3", Range("c2").End(xlDown)), Range("C3", Range("C3").End(xlToRight))).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
        

End Sub

14 Jun 21 at 06:19

Hi Ronnie,

The part that stands out to me is that you have a wait of 20 seconds for each row in your list - I think this would be a good place to start.

I hope it helps!

10 Jun 21 at 06:57

Hi,

 

I am trying to download some specific data from Yahoo finance, I would like to check, how do I copy the data into excel using Selenium

 

Sub GetData()

    Dim myrng As Range
    Dim lastRow As Long
    Dim row_count As Long
    Dim ws As Worksheet
    Dim ticker As Variant
        
    Set ws = Sheets("Main")
   
'=================================================================
'Find Last Row
'=================================================================
    With ws
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    End With
'=================================================================
'Find Ticker Range
'=================================================================
    Set myrng = ws.Range(Cells(2, 1), Cells(lastRow, 1))
'=================================================================
'Loop Through Tickers
'=================================================================
  
        Set cd = New Selenium.ChromeDriver
        cd.Start
        cd.Get https://finance.yahoo.com/quote/MSFT?p=MSFT&.tsrc=fin-srch
    
'=================================================================
'Finding Elements
'=================================================================
    
        Dim SearchInput As Selenium.WebElement
                
        Set SearchInput = cd.FindElementByXPath("/html/body/div[1]/div/div/div[1]/div/div[2]/div/div/div[5]/div/div/div/div[3]/div[1]/div/span[1]")

 

 

 

Thanks       

10 Jun 21 at 08:48

Hi Ronnie,

You might find this video useful as it uses Yahoo Finance as an example https://www.wiseowl.co.uk/vba-macros/videos/vba-scrape-websites/selenium-multiple-tabs/

I hope it helps!

11 Jun 21 at 07:24

Hi Andrew,

 

Thanks for the reply. As I am quite new at this, would appreciate if you could help me outh with this.

I manage to get to the URL & get the a particular Element, would like to check how i could export it to excel sheet cell b3

 

Option Explicit

Private cd As Selenium.ChromeDriver

Sub GetData()

    Dim ws As Worksheet
    Dim SingleRow As Range
    Dim ListofRows As Range
    Dim SingleColumn As Range
    Dim ListofColumns As Range
    Dim MyRange As Range
    Dim RowNum As Integer
    Dim ColNum As Integer

    Set ws = ThisWorkbook.Worksheets("Main")

'=================================================================
'Setting Range
'=================================================================
    With ws
        Set ListofRows = Range("A3", Range("A2").End(xlDown))
        Set ListofColumns = Range("B1", Range("B1").End(xlToRight))
        Set MyRange = ws.Range(ListofRows, ListofColumns)
    End With
'=================================================================
'Loop Through RIC
'=================================================================

        Dim URL As String: URL = "https://sg.finance.yahoo.com/quote/MSFT/"
        Set cd = New Selenium.ChromeDriver
        cd.Start
        cd.Get URL
        cd.Wait "1000"
            
'=================================================================
'Loop Through XPATH
'=================================================================
        
        Dim SearchInput As Selenium.WebElement

        Set SearchInput = cd.FindElementByXPath("/html/body/div[1]/div/div/div[1]/div/div[2]/div/div/div[5]/div/div/div/div[2]/div[1]/div[1]/h1")
        
        Debug.Print SearchInput.Text

'        SearchInput.Value = ThisWorkbook.Worksheets("Mains").Range("B3")

End Sub
 

 

Andrew G  
11 Jun 21 at 08:34

Hi Ronnie,

You can reverse the last line of code in your subroutine to change the contents of cell B3. If your Debug.Print statement is returning the correct information using the Text property then something like this should do the trick:

ThisWorkbook.Worksheets("Mains").Range("B3").Value = SearchInput.Text

I hope it helps!