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
30 Responses to “Great News: Chandoo becomes MVP”
Congratulations! It's well deserved. 🙂
This is amazing. Hearty congratulations and a rocking new year ahead!!!
Congrats! I have learnt alot from your site... and the most important is learning how to achieve the most with the simplest concepts.
Thanks for the excel calendar. Is it possible to get a simple big fonted calendar printable on an A4 size paper without any distracive notes or visuals? BTW, I have already signed up for your newsletter. With warm regards and
Gratefully yours
50+ year old CHarish.
Hey Chandoo,
Great to hear that. Congratulations! The best new year gift, I would say. Keep it up, u've been doing extraordinary work for the excel users community.
Regards,
Pankaj Verma
Congrats dude... fantastic news!
congratulations! your site is great, this is well deserved
Rich
I recently found your site, I visit many. The tips that you provide are in the top 1% of all the sites I visit. Keep on Excelling.
Arnold
South Africa
Congratulations, Chandoo! That's a great way to start the year and make the PHD even better.
Congratulations, Chandoo.
Your site is one of most useful on the net. Happy new year and lot of ideas you will present for us.
Congrats.
Just read your name in an email from Abhishek. Well deserved.
Congratulations, and Happy New Year.
Greetings from Rio de Janeiro my friend! You trully deserve it!
Nive way to start 2009! Keep up the good work!
FC
That was quite forseeable , so you have now really got your PhD in excel.
Anyways Chandoo you have made excel a real Fun doo
I will like you to write some more on INDEX and MATCH function in near future.
@Hey Chandoo ! Congrates....
Ab to treat mangta hai !
Well deserved Chandoo!!
Congrat's!! Very well deserved 🙂
i always browsed mr.excel and used to see MVP writtne below names of people who used to solve queries in excel forum there......i just used to admire as to what they have special in them that they are MVP......
but now i got my answer...............u deserve it man..........
@All: thanks everyone 🙂
Congratulations Chandoo, nice job!
Chandoo,
A well deserved recognition and a good start to the New Year. Continue your good work.
Subbu
Many Congratulations.
You deserve a Ph.D. 🙂
congrats.....
Congrats dude. Rock on!
[...] charting community in 2007 and has been growing strongly ever since. In year 2009, I have received the MVP award from Microsoft. Just few days back I have become a dad [...]
Respected sir,
I am impressed!.... Good job done.. Keep it up...
Sir, How to be a MVP certified person. What level of knowledge is required for it? send me links if possible.
Please reply...
Regards,
Dipak Khalasi.
Dipak -
The first thing you need to cultivate is the ability to search the web effectively. You could start by Googling "Microsoft MVP".
[...] boy and girl which has been made hectic and incredibly fun ever since to their life.He has been awarded MVP status in 2009 by Microsoft(and renewed in 2010,2011 & 2012).His MVP profile is here.If you want to contact him direct then [...]
Congrats Chandoo!!
[…] Chandoo becomes MVP […]