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

In this article, I will show how to scrape data from a Website into Excel. Sometimes while working in Excel, we may need to scrape data from a website. As Excel is a fine tool to analyze and process data, we may want to bring data from a website and then further analyze it in Excel.

Table of Contents hide

Download Practice Workbook

2 Useful Methods to Scrape Data from a Website into Excel

1. Utilizing Get Data From Web Feature for Scraping Data from a Website

2. Application of VBA Macro to Scrape Data from a Website

Things to Remember

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Scrape Data from Website.xlsm

2 Useful Methods to Scrape Data from a Website into Excel

In this section, we will demonstrate 2 effective methods to scrape data from a website into Excel. For illustration purposes, we choose a table in a webpage (see the screenshot below) that we want to export into Excel for further analysis.

So, let’s look at our first method for scraping the data table into Excel.

1. Utilizing Get Data From Web Feature for Scraping Data from a Website

In this method, we will scrape data from a website into Excel by using a built-in feature named Get Data From Web. To know more, follow the steps below.

Steps:

First, open the Excel file where you want to extract data from a website. Here, I have opened an Excel file named “Scraping Data from Website.xlsx”.
Then, go to the Data tab and then click on From Web in the Get & Transform Data

Now, on the popped-up window, write down the webpage address from where you want to scrape data. Then, click OK.

Consequently, you will see that a Navigator window will open up like this.

Now, on the left panel, you will see a list of options; on the right side, you will see a Table View tab and a 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 from the webpage.
If we click on any of the listed tables, the table will be shown on 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 on the table to make it cleaner and suitable for our use. To do that, click on Transform Data.
Consequently, a Power Query Editor window will open like this.

Now, after doing the necessary editing, press Close & Load. As a result, the table will be loaded into the Excel sheet. After some modifications and changes in formatting, in my case, the table looked like this.

In this way, we can easily get a table from any webpage.

2. Application of VBA Macro to Scrape Data from a Website

In this method, we will use VBA code to scrape data from a website into Excel. 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 stock prices of some companies.

Now, to run the code for scraping data from the webpage, follow the steps below.

Steps:

First, press Alt+F11 to open the VBA editor.

Now, go to Insert > Module.

As a result, a blank module will open up like this.

Now, copy-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

🎓How Does the Code Work?

Here, we used Private Sub Procedure UseQueryTable() to identify tabular data from the website of our interest. A With Statement helps to arrange those data in the Sheet5 sheet which we named as Result (see in the workbook). The Private Sub Procedure ClearSheet deletes previously used tables and thus helps to scrape fresh data from the URL. Finally, the Public Sub Procedure (ExtractStockData) uses the commands from these Private Sub Procedures to scrape the tabular data from the website.

Now, if you run this code by clicking the F5 button, you will see that the table has been 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.

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

🎓How Does the Code Work?

Here, we used Private Sub Procedure UseQueryTable() to identify tabular data from the website of our interest. A With Statement helps to arrange those data in the Sheet6 sheet which we named as Entire Page (see in the workbook). The Private Sub Procedure ClearSheet deletes previously used tables and thus helps to scrape fresh data from the URL. Finally, the Public Sub Procedure (ScrapeFullPage) uses the commands from these Private Sub Procedures to scrape the entire webpage from the website.

If you run this code by pressing F5, you will see that the entire webpage has been extracted into sheet 6 named Entire Page.

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

This is how we can scrape the whole webpage by VBA code into Excel.

Things to Remember

The VBA code approach is not universally applicable to every webpage. Rather, we need to modify the code each time according to the webpage formatting. Hence, it can be time-consuming and cumbersome.
In the 1st method, we can scrape multiple objects from a webpage at a time.

Conclusion

That is the end of this article about how to scrape data from a website into Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy.com for more exciting articles on Excel.