Excel - Scrape Weather Data from Weather Underground Webpages Using Power Query - Episode 2056

Microsoft Excel Tutorial: Get Weather Data from Weather Underground to Excel.

Welcome to the MrExcel netcast, where we bring you the latest and greatest tips and tricks for mastering Excel. In this episode, we will be diving into the world of Power Query and showing you how to take your web scraping skills to the next level. This technique, inspired by Ken Puls and Miguel Escobar's book "M is for (DATA) MONKEY", will allow you to run one query multiple times, saving you time and effort.

I stumbled upon this trick while preparing for a seminar at the Excelapalooza conference in Dallas. As soon as I saw it, I knew I had to share it with all of you. So, let's get started! First, we will take a look at how to create a query to pull data from a webpage. In this example, we will be using Weather Underground to gather precipitation and temperature data for a specific date and location. But don't worry, this podcast is not about creating the query itself, so I won't go into detail here.

Now, here's where the magic happens. We will take this query and turn it into a function, allowing us to run it multiple times with different parameters. This means we can pull data from multiple webpages without having to create a new query each time. Trust me, it's awesome. And don't worry, I'll walk you through the steps and show you how to make it work for your own queries.

But wait, there's more! We will also show you how to use some Excel trickery to create a table of URLs and then use our newly created function to pull data from all of them at once. This means you can scrape data from hundreds or even thousands of webpages in just a few clicks. No more writing macros or spending hours manually pulling data. Power Query has got you covered.

So, if you're ready to take your web scraping game to the next level, join me in this episode of the MrExcel netcast. And don't forget to check out the Excelapalooza conference in Dallas every September for more amazing Excel tips and tricks. Thank you for watching and I hope to see you in Orlando for my Power Excel seminar on November 4th, 2016. Don't miss out on this opportunity to learn even more about Power Query, Power BI, Power Pivot, and Power Map. See you next time for another netcast from MrExcel.

#excel
#microsoft
#microsoftexcel
#exceltutorial
#microsoft365
#walkthrough
#webscraping
#powerquery

This video answers these common search terms:
how to create a web excel query
how to create a web query in excel
how to i use power query for excel web
how to add weather excel
how to add quicklink for weather reports in excel
how to add weather excel
how to import weather data into excel

Table of Contents:
(00:00) Introduction to MrExcel Podcast, Episode 2056
(00:23) Credit to Ken Puls and Miguel Escobar for their book on Power Query
(00:33) Introduction to the main trick: running one power query multiple times
(00:51) Example of pulling data from a web page using this technique
(01:01) Explanation of the URL used in the example
(01:15) Importance of the URL structure for this technique
(01:25) Explanation of the data being extracted from the web page
(01:35) Introduction to the power query being used
(01:55) The point is to adapt this technique for different queries
(02:05) Editing the query to make it a function
(02:30) Explanation of the function and its purpose
(03:11) Creating a table in Excel to use with the function
(03:21) Building a query from the table
(04:05) Adding a custom column using the function
(04:29) Explanation of the function's syntax and its importance
(05:01) Results of the function for each row in the table
(05:22) Importance of this technique for large datasets
(06:08) Invitation to attend a Power Excel seminar in Orlando
(06:44) Recap of the trick and its steps
(07:27) Clicking Like really helps the algorithm

Getting Web Data to Microsoft Excel:
Write one query to load one web page. Then, turn that query into a function and use Power Query to load many web pages.
Today's trick is adapted from the M is for Data Monkey book
Build a query to get data from one web page
Edit the query to change it into a function with VariableName before Let
Change the hard-coded URL to VariableName
Rename the query to fxWeather
Close & Load. The data will disappear.
Use Excel Trickery to create a table of all URL's
Create a query from that table.
Add a new column of Weather = fxWeather
Expand the column. Uncheck Prefix
Amazing!

[kennethvela]

Somebody left in my company, and I'm in charge to deliver information for 750 dayli records from the web, this totally simplified my work, just what I was looking for, thank you very much

[dougmphilly]

somebody should be awarded a nobel prize for thinking of this.

[Mrdayz]

Thanks, this was super useful. Thank you.

[excelisfun]

Thanks for the Power Query Radness!!! $74 bucks, that is a great deal!

[laviedandre]

In the words of the great Mike "ExcelIsFun" Girvin - "You have GOT to be kidding me!" This was an amazing video - thank you!

[binhdo]

Amazing video!! This is what I am looking for

[MySpreadsheetLab]

I was wondering if power query could do this easier than vba. Now I know! Thanks Bill !

[TroyJScott]

Wow. Thank you.

Thank you. Thank you

Keep putting the good word out.

From FTW, Troy

[sadinenim]

Hi Bill in real time scenarios it will ask for a sign in page before opening any office portal. can you help me how we can do web scrapping in that cases.

[sheetsack]

Any chance to scrap data from Vidiq or some sites that require login?

[thomasmason]

This is great, is there a way to combine 2 different queries into one table? For example there are 2 separate tables on each page that i want to extract data from. i Have been able to suceed using you method to get 1 table extracted over multple pages. (is there a way to join the second table?

[niueankid]

Fantastic One question

Why only 15 days ? I am so interested to know how you can work with dynamic months with different max min days

[faisalshaikh]

Please let me know ..how can I attend your seminars?

[krishnakishorepeddisetti]

Hi Bill,

What if the last page is not known....how can i dynamically define the last page.

Kindly help me with your inputs.

Amazing video bill :)

Thanks and Regards
Kishore

[grossepioche]

Great explanation Thanks, what if there is a LOG In to the weksite? Take care and thanks again!

[hariyantowidjaja]

Hi, how if the website required user & password?

[thomasmason]

Thanks for this its fantastic just subscribed. one question,
It works perfect when the table header of the original query is exactly the same as the new table in the different URL. My problem is the example I'm trying to do, the second URL sometimes changes the headers slightly depending on the site. Therefore when i run the query it comes up with the "[Expression.Error] The column of the table wasn't found".


I was wondering if there was was a way to have Power query search for a second header if there is an error on the first.


It would be great if you could do a vid on this thanks

[wfbane]

This is very powerful. However I sometimes get a Power Query error "Operation is not valid due to the state of the object" (for whatever that's worth!)

[grahambeyer]

What about Timeout error after 100 seconds?

[kevinlabore]

Thanks great video as always -- have you done any examples with "Scraping" data from Website that are note using tables? Cam you still use M / Power Query or do you have to use R-Script or similar?