fbpx
Search
Close this search box.

How to import web data to Excel using Power Query

Share

Facebook
Twitter
LinkedIn

Power Query offers many ways to get data to Excel. One of them is to Web Data import feature. Let’s understand how this works by importing world stock exchange closing data from Google Finance website.

[Related: Introduction to Power Query]

Importing web data in to Excel – Step by step tutorial

Note: You need Power Query for this tutorial. Install Power Query on Excel 2013 (how to) and continue reading.

  1. Open a blank workbook. Go to Power Query ribbon & click on From Web button. Enter the URL of the webpage from which you want to import the data.
    web-data-button-power-query-ribbon
  2. Your web page will be loaded in to Navigator pane. If there are any tables or other sections of the page that can be readily embedded in Excel, they will show up in the navigation tree structure. Hover on any table to see if that is the data you want. Once you identify what you need, click on Load to get this data in to Excel. If you want to pre-process the data before loading in to Excel, click on Edit.
    preview-of-webdata-power-query
  3. Let’s say you have taken Table 0, which contains stock market closing data around the world. When this is edited in Power Query window, it looks like this.
    webdata-loaded-to-pq-window
  4. As you can see, there are 2 problems with this data. (1) Column headers are missing (2) Column 3 should be splitted in to 2 columns.
  5. Renaming columns: Simply double click on column headers and write whatever header you want.
  6. Splitting a column: Select column 3 and Click on home > split column button in Power Query window. Specify the delimiter (in our case space should work) and click ok.
    split-column-space-as-delimiter-power-query
  7. Once column is spitted, our new set up looks like below. Column 3.2 needs further cleaning. We need to remove the brackets ( ).
    data-after-splitting-column-power-query
  8. Removing the brackets: Select column 3.2 and click on Home > Replace values button. Replace ( with nothing. Repeat the replacement, but this time replace ) with nothing.
    replace-brackets
  9. Almost done. Our data is clean. Just change the column titles and we get this:
    processed-web-data-power-query
  10. Finally load this data to Excel by clicking on “Close & Load” button. Instantly, all this web data will be loaded to Excel as a new table.
    load-data-to-Excel

How to refresh the imported data?

Simple. Do one of the below:

  • Click on “Refresh all” button in Data ribbon of Excel
  • Right click on Excel table with web data and choose “Refresh”
  • Activate workbook queries pane (from Power Query ribbon) and refresh the query by clicking on the refresh icon at right.

Download Power Query web data – Example workbook

Please click here to download the workbook with Power Query web data extraction example. Right click on the query in workbook queries pane and edit it to understand the pre-processing steps better.

What awesome things can you do with web data in Excel?

Integrating your own data with publicly available sources can lead to interesting analysis situations. Power Query, Power BI & Power Pivot offer several ways to connect to web data (Facebook, Azure market place, Google Analytics etc.) and analyze it in Excel.

Have you tried importing web data to Excel? What has been your experience like? Please share your tips & thoughts in the comments section.

More ways to analyze web data in Excel:

Learn more about analyzing web data in Excel

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

6 Responses to “How to import web data to Excel using Power Query”

  1. Thanks, Chandoo.
    How to import data from the web using Power Query.....nice.
    Thank you.

  2. Anonymous says:

    this is nice

  3. Muhammad Abdullahi says:

    very very good and nice. contribute a lot to my studies.

  4. marius says:

    hi there,
    thanks that's very useful indeed! Just one question.
    When u have imported a rate (example the exchange rate for the USD to the EURO) on a date (say the 15th of April 2016) that is fine and all, come the 16th of April 2016 that rate has changed from the 15th. how do you keep both the 15th and the 16th without copy paste etc, have power query append the 16th to the 15th of April for that rate?

  5. Tim says:

    Hi, question. I have a spreadsheet from the web that I would like to connect PQ to. However the web page provides me with a button which is a download, not a link to the spreadsheet. So right now I have to download the sheet and then run my PQ on it. Do you know of a way to connect directly to it. I copy the web URL into PQ but the data isn't available.

    Thanks

  6. Ron says:

    Hi,
    When importing table data from a website, via Import from Web (ie not using Power Query), I am unable to select the table data, as I do not see any yellow squares with red arrow to select the table. Do you I have turn something on in options to see these yellow boxes ? or has that functionality been removed ?

Leave a Reply