How to Do Web Scraping in Excel (2 Methods)

Method 1 – Using the “Get Data from Web” Feature

Go to the Data tab and select From Web under the Get & Transform Data section.

Insert the URL of the website you want to scrape.

Click OK and choose Anonymous for web access.
The website data will be linked to Excel. If there are multiple tables, select the one you want and click Transform Data.

In the Power Query Editor, remove unnecessary columns and make adjustments.
Load the table into a specific sheet using Close & Load To.

Insert the cell reference where you want to extract the table to in the Import Data dialog box and click OK.

The data table will be imported to the desired cell of the sheet.

To update the table, select any cell and go to Data > Refresh All > Connection Properties.

The Query Properties window will appear. Check the Refresh every option and set a suitable time. Set the timer to 10 so that the data table will update every 10 minutes.

Method 2 – VBA for Web Scraping

2.1 Scraping Data from Web as Table

Press Alt+F11 or select Developer > Visual Basic to open the VBA editor.

Create a VBA Module and insert the provided code:

Option Explicit
Public Sub ExtractStockData()
    Call ClearSheet
    Call UseQueryTable
End Sub
Private Sub ClearSheet()
    Dim aA_table As QueryTable
    For Each aA_table In Sheet5.QueryTables
        aA_table.Delete
    Next aA_table
    Sheet5.Cells.Clear
End Sub
Private Sub UseQueryTable()
    Dim aA_URL As String
    aA_URL = "https://www.investing.com/equities/"
    Dim aA_table As QueryTable
    Set aA_table = Sheet5.QueryTables.Add("URL;" & aA_URL, Sheet5.Range("A1"))
    With aA_table
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "1"
        .WebFormatting = xlWebFormattingNone
        .Refresh
    End With
End Sub

How Does the Code Work?

In this case, we utilized the Private Sub Procedure UseQueryTable() to extract tabular data from the website of interest.
A With Statement helps to organize the data on the Sheet5 sheet, which we titled Result (see the worksheet).
The Private Sub Procedure ClearSheet deletes previously used tables, making it easier to scrape new data from the URL.
Finally, the Public Sub Procedure (ExtractStockData) takes the commands from these Private Sub Procedures to scrape the tabular data from the website.

You can use the code; you just need to change the website link if needed.

Press Alt + F8 to run the macro and scrape the table.

The table will be scraped to the sheet named Result.

Format the data table for better presentation.

2.2 Scraping Entire Page Data from Web

Press Alt+F11 or select Developer > Visual Basic to open the VBA editor.
Create a VBA Module and insert the provided code.

Option Explicit
Public Sub ScrapeFullPage()
    Call ClearSheet
    Call UseQueryTable
End Sub
Private Sub ClearSheet()
    Dim aA_table As QueryTable
    For Each aA_table In Sheet6.QueryTables
        aA_table.Delete
    Next aA_table
    Sheet6.Cells.Clear
End Sub
Private Sub UseQueryTable()
    Dim aA_URL As String
    aA_URL = "https://www.investing.com/equities/"
    Dim aA_table As QueryTable
    Set aA_table = Sheet6.QueryTables.Add("URL;" & aA_URL, Sheet6.Range("A1"))
    With aA_table
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .Refresh
    End With
End Sub

How Does the Code Work?

We utilized the Private Sub Procedure UseQueryTable() to retrieve tabular data from the website of our choice.
A With Statement helps in organizing the data on the Sheet6 page, which we labeled Entire Page (see the worksheet).
The Private Sub Procedure ClearSheet deletes previously used tables, allowing new data to be scraped from the URL.
Finally, the Public Sub Procedure (ScrapeFullPage) scrapes the full webpage from the website using the commands from these Private Sub Procedures.

The code scrapes data from a specified URL and organizes it on a sheet named Entire Page (Sheet6).
Customize the URL as needed.
Press Alt + F8 to run the macro and scrape the entire webpage, including hyperlinks.
After running the code, you’ll find all the data (including links) in your Excel sheet.

The following picture shows the first parts of the data from the website.

And here is the image after a bit of scrolling, showing the data table we scraped earlier using the Macro named ExtractStockData.

Things to Remember

Remember that VBA code may need adjustments for different webpage layouts.
In the 1st method, we can scrape multiple objects from a webpage at a time.

Download Practice Workbook

You can download the practice workbook from here:

Web Scraping.xlsm

Frequently Asked Questions

1. How does web scraping in Excel work?

Web scraping in Excel typically involves using Excel’s built-in web query functionality or external tools and libraries.
Specify the URLs you want to scrape and define the data elements to extract.
Configure the scraping process according to your requirements.

2. Are there any limitations or legal considerations when web scraping in Excel?

Some websites may prohibit scraping via terms of service or robots.txt files.
Comply with applicable laws and regulations.
Be aware of rate limits and anti-scraping measures.

Web Scraping in Excel: Knowledge Hub

<< Go Back to Importing Data in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!