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.


Download Practice Workbook

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


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.

how to scrape data from a website into excel

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

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

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

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

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

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

  • 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.

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

  • 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.

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

  • 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.

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

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.

Application of VBA Macro to Scrape Data from a Website

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.

Application of VBA Macro to Scrape Data from a Website

  • 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.

Application of VBA Macro to Scrape Data from a Website

  • After some formatting, the table looks like this.

Application of VBA Macro to Scrape Data from a Website

  • 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

Application of VBA Macro to Scrape Data from a Website

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.

Aniruddah Alam

Aniruddah Alam

Hi, I am Md. Aniruddah Alam from Dhaka, Bangladesh. I have my Bachelor's degree in Naval Architecture from BUET. I love to read books, listen to podcasts, explore new things, walking randomly in unknown places. Currently, I am working as an Excel and VBA Content Developer. I try to present solutions of problems that are faced in excel in an easy and straightforward manner.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo