Import public data from a website table

 

by Kara Hiltz

Application: Microsoft Excel 2010/2013/2016

Operating System: Microsoft Windows

Sometimes the data you need is already available in the public domain—it’s just a matter of finding it and importing it into Excel so you can work with it. Using the Power Query feature, finding and accessing public data is an easy task.

To find public data online and import that data into our spreadsheet, we’ll:

  • Search for the public data we need online so we have the URL ready.
  • Preview the data table from the website to make sure everything looks the way you’d expect.
  • Import that online data using Power Query.

Excel data is best when it’s timely, and the most up-to-date information is usually found on the Internet. As Microsoft Office becomes more focused on collaboration and online services, Microsoft is also making it easier to import online data from a website right into an Excel workbook, as shown in Figure A.

In this article, we’ll show you how to pull information from a website table so you can spare yourself the retyping and know that you’re using the most updated information.

IME18405A1 IME18405A2

A:

Don’t re-enter the latest data from a website’s data table into Excel; let Power Query do the work for you.

Adapt for Excel 2010/2013

Excel 2016/365 has the Power Query feature built in, but Excel 2010 and 2013 users can also use Power Query by installing it as an add-in. Follow these simple steps to get Power Query for Excel 2010/2013:

  1. Open your web browser and go to https://www.microsoft.com/en-us/download/details.aspx?id=39379.
  2. Click the Download button.
  3. Select the check box for the Power Query file you’d like depending on whether you need the 64-bit or 32-bit file. Click the Next button.
  4. Click the Save File button in the pop-up window.
  5. When the file downloads, open the file and follow the installation instructions.

After you’ve downloaded and installed Power Query, Excel should have a new Power Query tab in its Ribbon.

What data can Power Query import?

When it comes to online data, you must understand what data will import smoothly into Excel and what data won’t. For instance, importing web data using this method is not meant for downloadable files of data. Many sources make .XLS files available for download, and for those files you don’t need to use Power Query. You’ll simply download the file and open it in Excel.

Power Query’s ability to import web data is applicable for data found in tables on a website. For instance, if you are looking for stock market data, there are many websites that include helpful stock-related data in web tables, as shown in Figure B. If you find yourself wishing you could copy and paste information from a website right into an Excel spreadsheet, then Power Query might be a good option.

When you find a website with data tables you want to import, copy the URL from your browser. You’ll need the URL on your Clipboard when you access Power Query. (You’ll paste the URL, as instructed in step 4 below.) For our example, we’ll copy the URL http://money.cnn.com/data/markets/sandp/index.html.

IME18405B 

Join NowClose
Return to the ExcelSkillsSociety's homepage