Have you ever been in a situation where you need to collect product information and pricing data from multiple websites and organize them into Excel sheets? Manually navigating and organizing this information is not only time-consuming and laborious but also prone to errors. To solve this problem, we’ll explore some of the easiest and most effective methods to pull website data directly into Excel, with options tailored for both beginners and advanced users.
Using No-Code Web Scraping Tools
For users who prefer not to code, no-code web scraping tools like can help you scrape data from websites to Excel without any coding. These tools are designed to simplify the process by allowing users to define data extraction rules visually without the need for programming skills.
Thunderbit uses advanced natural language models to understand website layouts and content without relying on fixed selectors. Most tools, however, use drag-and-drop functionality and customizable templates, requiring users to learn for hours before they can set up and automate web scraping tasks.
What’s more, you can export data to Google Sheets, Airtable, or Notion with one click, not just Excel.
3 Steps to collect product data with Thunderbit
Step 1: Open the website and click on “AI Web Scraper” in the sidebar.
After you’ve easily installed Thunderbit on your browser, open the website you want to scrape and find the “AI Web Scraper” option in the Thunderbit sidebar.
Step 2: Customize the data fields you want to extract with AI assistance.
A scraper template will automatically generate when you use the “AI Suggest Columns” feature. You can then edit this template with AI support to suit your needs. You’ll have options for where to save your data—either outputting it as a table to copy or saving it to other platforms.
Step 3: Export the scraped data to Excel
Once you’ve reviewed all data fields, run the scraper. You can copy or download the scraped data and import it into Excel.
Using Excel Web Queries
Excel has a built-in feature called Web Query that allows users to extract data directly from web pages. This method is particularly helpful for importing structured, tabular data directly from websites, and it allows you to maintain the data’s relevance with periodic updates. Please note, however, that this feature is available only on Windows versions of Excel.
Steps to collect product data with Excel Web Queries:
- Open Excel: Start a new workbook and navigate to the Data tab.
- Select "From Web": Click on "Get External Data" and choose "From Web."
- Enter URL: Input the website address you wish to scrape and click OK.
- Choose Data: Excel will display tables found on the page. Select the desired table and click Load.
- Check the Output: You can specify where to load the data in your workbook and see if it meets your needs.
Explore how Thunderbit can do more than just scrape data. Use AI to scrape, summarize, and autofill web pages. .
Using Excel VBA for Custom Scraping
For those with programming knowledge, using VBA (Visual Basic for Applications) allows for more complex scraping tasks. VBA is a programming language integrated into Microsoft Office applications, including Excel. This feature is only available in Excel for Windows.
Steps to Collect Product Data with Excel VBA:
- Enable Developer Tab:
- Open Excel, click on "Excel" > "Preference" > "View".
- Check the "Developer" option in the bottom pane.
- Add References:
- In the Developer tab, click on "Visual Basic" to open the VBA editor.
- Go to "Tools" > "References" and check the following:
- Microsoft Internet Controls
- Microsoft HTML Object Library
- Write VBA Code for Data Extraction
- Click on "Insert" > "Module"
- Write VBA code in the editor.
Here's a basic example of how to extract product title, price, and availability from an Amazon product page:
Sub ScrapeAmazonPage()
Dim IE As Object
Dim html As Object
Dim productTitle As String
Dim productPrice As String
Dim productRating As String
' Create Internet Explorer object
Set IE = CreateObject("InternetExplorer.Application")
' Navigate to the Amazon product page
IE.Visible = True
IE.navigate "Put Your URL Here"
' Wait for the page to load
Do While IE.Busy Or IE.readyState <> 4
DoEvents
Loop
' Get the HTML document
Set html = IE.document
' Extract product title
On Error Resume Next
productTitle = html.getElementById("productTitle").innerText
On Error GoTo 0
' Extract product price
On Error Resume Next
productPrice = html.getElementsByClassName("a-price-whole")(0).innerText
On Error GoTo 0
' Extract product rating
On Error Resume Next
productRating = html.getElementsByClassName("a-icon-alt")(0).innerText
On Error GoTo 0
' Output the extracted data to Excel
With ThisWorkbook.Sheets(1)
.Cells(1, 1).Value = "Product Title"
.Cells(1, 2).Value = "Price"
.Cells(1, 3).Value = "Rating"
.Cells(2, 1).Value = productTitle
.Cells(2, 2).Value = productPrice
.Cells(2, 3).Value = productRating
End With
' Clean up
IE.Quit
Set IE = Nothing
Set html = Nothing
End Sub
- Run and Test the VBA Script
- Run your script in the Visual Basic Editor or assign the script to a button in your Excel sheet.
- Check the data output in your Excel sheet to ensure it’s formatted as expected.
Let AI handle your data scraping without the hassle of coding. Free up your time to focus on what’s important. Discover the power of our !
FAQs
-
What is web scraping, and why is it useful?
Web scraping collects data from websites and organizes it in formats like tables. It’s beneficial for gathering information from multiple sources, such as product pricing, enabling easy comparison and analysis.
-
How does Thunderbit work?
Thunderbit allows data extraction without coding. Using visual interfaces, users can select data fields for scraping, which the tool then automates, simplifying data collection.
-
Can I export data to other platforms besides Excel with Thunderbit?
Yes, Thunderbit supports exporting data to platforms like Google Sheets, Airtable, and Notion, allowing flexibility in data management beyond Excel.
-
Why Web Query data isn’t refreshing automatically?
Make sure the “Refresh All” option is enabled under the Data tab in Excel. You can set up an automatic refresh interval by selecting “Connection Properties” under Queries & Connections.
-
Why VBA Code Doesn’t Run Properly?
Ensure you’ve enabled the Developer tab and selected the required references (Microsoft Internet Controls and HTML Object Library).
Learn More: