How To Extract Data from Website to Excel Automatically

In this era of big data, people often need to work with data scraped from websites. But copying and pasting data from websites is not an efficient method to do it. Fortunately, it is possible to extract data from websites to excel automatically. In this article, we will learn three ways on how to extract data from websites to excel automatically.

Although that was a quick and easy way to do that, there are actually some advanced ways to do the job as well. Whether you are a beginner or an expert, this article will teach you some ways to do the job you did not know before. Without further ado, let’s begin.

Generally, you would want to import data from a webpage where there is a table, as that data is easy to parse in excel. In this article, we will be importing data from the wikipedia page of Microsoft Excel itself. Other than the table, we will also learn how to import generic unstructured HTML data.

Microsoft Excel already includes a method to extract data from websites. This method is easy to use, formats the tables properly, and can be updated from the websites whenever you want.

Steps:

➤ Head to the Data From the left, click on From Web. A new dialog box will open.
➤ Paste the website link in the URL field, and click OK. In our case, we will be using this URL:
https://en.wikipedia.org/wiki/Microsoft_Excel
➤ If it’s the first time for you doing this, excel might ask how to connect to the website, just click OK.

➤ Depending on your computer and internet speed, you have to wait a moment.
➤ A new window will open with tables and data from the web page. On the left side, select the table you want to open. You can see a preview on the right side. Click Load to load the extracted data from the website.

➤ If you ever need to update the data, click on the Refresh All button on the Queries & Connections section of the Data

If you are not afraid to use a bit of coding, I have a method for you that you can use. Microsoft Excel supports Visual Basic code by default. You can use some code to retrieve the data from online quickly and easily.

Steps:

➤ Press Alt+F11 to open the code editor.
➤ From the Insert menu, select Module. A new window will open inside, allowing you to code right there.
➤ Insert this code:

Sub ScrapeWebContent()    
Dim httpRequest As Object
Set httpRequest = CreateObject("MSXML2.XMLHTTP")

Dim url As String
url = "https://en.wikipedia.org/wiki/Microsoft_Excel"

If FetchWebPage(httpRequest, url) Then
ProcessHtmlContent httpRequest.responseText
Else
MsgBox "Failed to retrieve the webpage. Status: " & httpRequest.Status & " - " & httpRequest.statusText, vbCritical
End If
Set httpRequest = Nothing
End Sub

Function FetchWebPage(ByRef httpRequest As Object, ByVal url As String) As Boolean
On Error GoTo ErrorHandler
httpRequest.Open "GET", url, False
httpRequest.send
FetchWebPage = (httpRequest.Status = 200)
Exit Function

ErrorHandler:
FetchWebPage = False
End Function

Sub ProcessHtmlContent(ByVal htmlContent As String)
Dim htmlDoc As Object
Set htmlDoc = CreateObject("htmlfile")
htmlDoc.body.innerHTML = htmlContent

Dim links As Object
Set links = htmlDoc.getElementsByTagName("a")

Dim i As Integer
For i = 0 To links.Length - 1
ThisWorkbook.Sheets(1).Cells(i + 1, 1).Value = links(i).href
ThisWorkbook.Sheets(1).Cells(i + 1, 2).Value = links(i).innerText
Next i
End Sub

➤ Replace the url = “https://en.wikipedia.org/wiki/Microsoft_Excel” section with your URL inside the quotation.
➤ Select ScrapeWebContent from the dropdown menu on the right of the window.

➤ From the Run menu, select Run Sub/UserForm.

➤ Now go back to the window with your workbook. Your data will be imported, including the URLs that those fields redirect to. However, you will need to sort the data yourself.

There are a lot of browser extensions that allow you to export data from websites. In this article, we have chosen the easiest one for you to use. The extension is free, and allows you to export data from websites to an excel file.

Steps:

➤ Open your chromium based browser. It could be Microsoft Edge, Google Chrome, Opera, Brave, etc.
➤ Go to the link below:
https://chromewebstore.google.com/detail/instant-data-scraper/ofaokhiedipichpaobibbnahnkdoiiah
➤ Click on Add to Chrome. A pop-up will come up to confirm. Click on Add extension.

➤ Open the webpage from which you want to get the data.
➤ From the extension icon on the top right, click on the extension icon.

➤ A new window will open with preview data. There would be options to download as a CSV and XLSX file. Download whichever file would be convenient for you.

Frequently Asked Questions

How to automatically update data in Excel from a website?

Select the cell where you want the data to update automatically. Then head to the Data tab, and from the Queries & Connections section, select Properties. From the new window, press the Queries button, and check the Refresh every checkbox. You can change the timer according to your requirements.

How to extract data from HTML to Excel?

From the Data tab, go to Get Data > From File. Select From XML. It would work for HTML as well as XML, as both are markup languages.

How do I automatically pull data from a website into Google Sheets?

Use this formula:
=IMPORTHTML(“URL”,”table”,1)
Replace the URL part with your desired URL. The parameter TABLE can be changed to list if that’s the kind of data you want to import. 1 is basically the index value.

How do I automatically parse data in Excel?

From the Data tab, select Text to Columns. The Convert Text to Columns Wizard will walk you through the process.

How to extract data from a website automatically?

There are a lot of web scraping tools to do so. The premium tools can properly parse websites and provide you with the kind of file you want to work with.

Wrapping Up

In this article, we have covered some popular ways on how to extract data from websites to excel automatically. We hope you learned something from reading this article. The practice file used in this article can be downloaded for your convenience. Comment down below which method you chose for your usage, and keep learning.