undefined
Blog > Knowledge > Post

Scraping Data from Website to Excel (Tutorial 2021)

Monday, August 2, 2021

Whether a digital native or immigrant, you probably know the basic functions of Excel inside out. With Excel, it is easy to accomplish simple tasks like sorting, filtering and outlining data and making charts based on them. When the data are highly structured, we can even perform advanced data analysis using pivot and regression models in Excel. But the problem is, how can we extract scalable data and put them into Excel efficiently? This would be an extremely tedious task if done manually by repetitive typing, searching, copying, and pasting. So how can we achieve automated data extraction and scraping from websites to excel?

 

In this article, I will introduce several ways to save your time and energy to extract data from websites to excel through web scraping.

 

Table of Contents

Getting web data using Excel Web Queries

Getting web data using Excel VBA

Automated Web Scraping Tools

Outsource Your Web Scraping Project

 

Disclaimer:

There are many ways to scrape data from websites using programming languages like PHP, Python, Perl, Ruby and etc. But here we'll just talk about how to scrape data from websites into excel for non-coders.

 

Getting web data using Excel Web Queries

Except for transforming data from a web page manually by copying and pasting, Excel Web Queries is used to quickly retrieve data from a standard web page into an Excel worksheet. It can automatically detect tables embedded in the web page's HTML. Excel Web queries can also be used in situations where a standard ODBC (Open Database Connectivity) connection gets hard to create or maintain. You can directly scrape a table from any website using Excel Web Queries.

 

The process boils down to several simple steps (Check out this article):

1. Go to Data > Get External Data > From Web

2. A browser window named “New Web Query” will appear

3. In the address bar, write the web address

 

scrape data from websites to excel

 (picture from excel-university.com)

 

4. The page will load and will show yellow icons against data/tables.

5. Select the appropriate one

6. Press the Import button.

Now you have the web data scraped into the Excel Worksheet - perfectly arranged in rows and columns as you like.

 

yahoo finance data in excel vba

  

Getting web data using Excel VBA

Most of us would use formula's in Excel(e.g. =avg(...), =sum(...), =if(...), etc.) a lot, but less familiar with the built-in language - Visual Basic for Application a.k.a VBA. It’s commonly known as “Macros” and such Excel files are saved as a **.xlsm. Before using it, you need to first enable the Developer tab in the ribbon (right-click File -> Customize Ribbon -> check Developer tab). Then set up your layout. In this developer interface, you can write VBA code attached to various events. Click HERE (https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx) to getting started with VBA in excel 2010.

 

excel vba interface

 

Using Excel VBA is going to be a bit technical - this is not very friendly for non-programmers among us. VBA works by running macros, step-by-step procedures written in Excel Visual Basic. To scrape data from websites to Excel using VBA, we need to build or get some VBA script to send some requests to web pages and get returned data from these web pages. It’s common to use VBA with XMLHTTP and regular expressions to parse the web pages. For Windows, you can use VBA with WinHTTP or InternetExplorer to scrape data from websites to Excel.

 

With some patience and some practice, you would find it worthwhile to learn some Excel VBA code and some HTML knowledge to make your web scraping into Excel much easier and more efficient for automating the repetitive work. There’s a plentiful amount of material and forums for you to learn how to write VBA code.  

 

Automated Web Scraping Tools

If you are looking for a quick tool to scrape data off pages to Excel but do not bother setting up the VBA code yourself, I strongly recommend that you try automated web scraping tools like Octoparse. With these tools, you can scrape data and export them into Excel worksheets either directly or via API. There is no need for you to learn to code. Pick one of the web scraping freeware from the list, and get started extracting website data immediately! Different web scraping tool has their pros and cons and you can choose one that best fits your needs. The below video shows how to leverage an automated web scraping tool to extract web data to Excel efficiently.

  

extract data from any websites to excel

 

 

Check out this post and try out these TOP 30 free web scraping tools

 

Outsource Your Web Scraping Project

 

If time is your most valuable asset and you want to focus on your core businesses, outsourcing such complicatedwork to a proficient web scraping team that has experience and expertise might be the best option. Data scraping is difficult to scrape data from websites due to the fact that the presence of anti-scraping bots will restrain the practice of web scraping. A proficient web scraping team would help you get data from websites in a proper way and deliver structured data to you in an Excel sheet, or in any format you need.

 

Read Latest Customer Stories: How Web Scraping Helps Business of All Sizes

Japanese eBay Seller Gains an Edge Over Local Competition

Unique Datasets to Empower Academic Research

Appraising Property Value Easier with Web Scraping

Online Service Provider Turns Web Data into Sales Leads

 

日本語記事:Webデータを活用!WebサイトからデータをExcelに取り込む方法
Webスクレイピングについての記事は 公式サイトでも読むことができます。
Artículo en español: Scraping de Datos del Sitio Web a Excel
También puede leer artículos de web scraping en el Website Oficial

Artikel auf Deutsch: Scrapen Daten aus Website mit Excel
Sie können unsere deutsche Website besuchen.

 

 

More Resources:

Top 20 Web Crawling Tools for Extracting Web Data

Top 30 Big Data Tools for Data Analysis

Top 5 Social Media Data Extraction Tools

Shopify Product Scraper to Crawl Shopify Stores for Free

 

Video: Create Your First Scraper with Octoparse 8.X

Download Octoparse to start web scraping or contact us for any
question about web scraping!

Contact Us Download
We use cookies to enhance your browsing experience. Read about how we use cookies and how you can control them by clicking cookie settings. If you continue to use this site, you consent to our use of cookies.
Accept decline