BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
Choose to import data from a website.
Paste in your website address:
Paste in the URL from which you want to import data.
Tick the tables on the webpage that you want to import:
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:
Choose to edit how you bring in the table's data.
Editing the Table
You can now delete and rename columns:
To start with, I might delete these first two columns.
Here's a suggestion for what you could end up with:
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:
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:
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.