How to Scrape Data from a Website into Excel (2 Easy Methods)

To illustrate our methods, we’ll use a table on a webpage (see the screenshot below) that we want to export into Excel for further analysis.

Method 1 – Using the Get Data From Web Feature

Steps:

• Open the Excel file into which you want to extract data from a website. Here, “Scraping Data from Website.xlsx”.
• Go to the Data tab.
• Click on From Web in the Get & Transform Data group.

The From Web pop-up window opens,

• Enter the URL from where you want to scrape data.
• Click OK.

A Navigator window will open.

On the left panel are a list of options; on the right side, tabs for Table View and Web View. If we click on the Web View tab, we will be able to see the web version of this webpage.

The best thing about this feature is that it can automatically detect any table or document in the webpage. If we click on any of the listed tables, the table will be shown in the preview window.

• Now, if you view the table in Table View, you will see how the data will look in the Excel sheet.

Before loading a table, we can make some transformations to clean it up and make it suitable for our use.

• Click on Transform Data.

A Power Query Editor window will open like this:

• After doing the necessary editing, press Close & Load. 

The table will be loaded into the Excel sheet.

After some modifications and changes in formatting, the table looked like this:

2. Using a VBA Macro

Here, we will not only scrape a data table from a webpage but also an entire webpage. The screenshot of the webpage is below. This website contains information on stock prices of some companies.

Steps:

• Press Alt+F11 to open the VBA editor.

• Go to Insert > Module.

A blank module will open up like this:

• Copy and paste the following code into the editor:

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.cse.com.bd/market/current_price"
    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

• Run this code by clicking the F5 button.

The table will be inserted into Sheet 5.

After some formatting, the table looks like this:

• To scrape the entire webpage, open a new module and paste the code below into it:

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.cse.com.bd/market/current_price"
    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

If you run this code by pressing F5, the entire webpage will be extracted into Sheet 6 named Entire Page.

If you scroll down, you will see the table that we scraped.

Things to Remember

• The VBA code provided is not universally applicable to every webpage. The code will need to be modified each time according to the webpage formatting. Hence, this method can be time-consuming and cumbersome.
• In Method 1, we can scrape multiple objects from a webpage at a time.

Download Practice Workbook

Related Articles

• How to Automate Copy and Paste from Website to Excel
• How to Import Data from Secure Website to Excel
• How to Extract Data from Website to Excel Automatically
• How to Extract Data from Multiple Web Pages into Excel
• How to Import Table from Website to Excel
• How to Import Data into Excel from Web

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