There might be some cases where we might need to extract data from a website into Excel and update it automatically in the future. Excel has a functionality to do that.
Let’s have a detailed look at how to achieve this. But, one thing you need to keep in mind: the web page must have data in collectible formats like Table or similar format. Otherwise, it’s altogether a different challenge to solve for. So, for this article, we’ll consider a website where the data is already formatted as a table.
We’ll consider this example to extract data from Google Finance web page.
Extracting the Data:
- Go to the web page with the address http://www.google.com/finance using any web browser
- You’ll see something similar to the snapshot below:
- Open a new blank Excel file, navigate to the Data ribbon and select From Web from the Get External Data section
- In the pop-up, type in the URL in the Address box as shown in the snapshot below
- As shown in the top left of the popup, you need to click on the small arrow symbol next to the tables you want to select and then click on Import
- You can select the table which is named as the World markets on the right
- Once selection (one or multiple tables) is done. Click on Import
- Upon clicking on Import, you get an option to select where to import the data to
- You can either select a cell in the existing worksheet or you can select New Worksheet. You also have an option to add the info added to the data model which can be used in Power Pivot or the Excel Data Model
- The data will be fetched from the website and will be loaded into Excel as shown in the snapshot below
- You may note that there won’t be any Headers added, so you might want to add them manually and update the location so that the data loaded in the correct place.
The advantage of an import like this is that there won’t be a necessity to do this import every time, you can setup a refresh or trigger an update whenever necessary.
Refreshing the Data:
Now we can have a look at what are the different types of refreshes we can setup
- For a one-time refresh of all the data connections created in the Excel workbook, use the Refresh All option
- You can also control the way refresh is done or set up a custom refresh method. You need to choose Properties from the Connections section of the data ribbon
- You can rename the connection name if you would like to and the Properties also lets you change the following things:
- In the refresh control, you can select if you want to refresh at every few minutes, by default it is 60 minutes
- You can select to refresh the file every time it’s opened
- There are options on how to handle the cases where new rows are added to the data
- Click OK to save the modified settings
With this, we managed to extract data from a website and also set up an automatic refresh for the same. Hope this saves a lot of time copying data from the website to Excel manually.
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - 2019-09-27
- FOR EACH Loop in Excel VBA - 2018-12-15