How to import web data to Excel using Power Query
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.
- 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.
- 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.
- 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.
- 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.
- Renaming columns: Simply double click on column headers and write whatever header you want.
- 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.
- Once column is spitted, our new set up looks like below. Column 3.2 needs further cleaning. We need to remove the brackets ( ).
- 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.
- Almost done. Our data is clean. Just change the column titles and we get this:
- 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.
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
- Find nearby zipcodes using WEBSERVICE() Excel function
- Import stock quotes to Excel using VBA
- Import mutual fund data in to Excel and build a tracker [case study]
Leave a Reply
|Dashboard best practice: Highlight user selection [video]||Filter as you type [Quick VBA tutorial]|