Loading live web data into Excel without writing VBA code to scrape HTML
Need to get tables of data into Excel from a website? There's no need to write lots of VBA code to scrape the site - just use Power Query to create a live link to the relevant page(s).

Posted by Andy Brown on 21 August 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Getting data in Excel without coding VBA to scrape a website

This question was prompted by Blackfire's post on this page.  Let's suppose that you want to scrape the BBC website to download the latest Premier League table:

Premier League table

Part of the Premier League table in the UK at the time of writing. Huddersfield's time at the top is surely limited?

You could write reams and reams of VBA code ... or you could just import the data, using a connection which will refresh whenever you use it.

I've used Excel 2016 below, but the process (if not the initial menu option) is the same in Excel 2013, and even 2010.  The software that you are using is often called Power Query.

Importing web data

Start by choosing this option in Excel:

Importing data from the web

Choose to import data from a website.

 

Paste in your website address:

Paste in a URL

Paste in the URL from which you want to import data.

Tick the tables on the webpage that you want to import:

Ticking tables

Here there's only one useful table to import, but sometimes you may tick several boxes.

You can then click to edit how you import the data:

Editing the table

Choose to edit how you bring in the table's data.

 

Editing the Table

You can now delete and rename columns:

Deleting columns

To start with, I might delete these first two columns.

Here's a suggestion for what you could end up with:

Renamed columns

I've deleted lots of columns, and renamed others.

I haven't included the steps to follow (although they're pretty obvious), as I want to keep the length of this blog down. 

Returning to Excel

When you've finished, you can return to Excel:

Close and load

Choose this option to close Query Editor and return to Excel.

 

Not only will you see your data, but you'll see on the right the query which created it:

Query list

You can double-click on this query to edit its steps.

 

The amazing thing is that when you open your workbook next time, you'll be able to refresh your query and bring in the latest version of the data!

Obviously this method only works when importing tables of data from websites, but it has to be quicker than scraping websites using VBA.

This blog has 0 threads Add post