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.

How to Scrape Data from a Website into Excel: 2 Useful Methods

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 see how to 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 pop-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 learn how to scrape data from a website into Excel using VBA code. 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.

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.

Download Practice Workbook

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

Scrape Data from a Website.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

AMANZ Nov 27, 2023 at 1:08 PM

How to scrape data when website requires authentication❓

I do have a VALID user name and password; not trying to bypass security❗
- Reply
  Shamima Sultana Nov 30, 2023 at 9:08 AM
  
  Hello Amanz,
  
  We shared our ideas with you in your ExcelDemy Forum post.