When Data Types first debuted in Excel, the most-requested additions were Exchange Rates and Weather. Well, Wolfram Alpha didn't mention weather, but there is Weather History for many locations in the new data types.
Transcript of the video:
[ ♫ ] It's a cool feature... [ ♫♫ ].
Learn Excel from MrExcel podcast episode 2345.
There is weather data in Excel. Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well the Wolfram Alpha people talked about hundreds of new things that they're adding to Excel and weather was not mentioned. But, holy smokes weather is awesome.
Let's take a look! Alright weather. Here is Merritt Island Florida.
I've converted that to a Location. Not Geography.
That's the old one. Location is the new Wolfram Alpha data type.
I initially came here and I thought they had something called dot weather.
I 'm like whoa that's cool! But I was initially disappointed that it just came up as "Weather for Merritt Island".
But, there is a little icon there. That is the good stuff. So let's take a look.
I am going to click on the card. Then I am going to scroll down to Weather.
Open this up.
And it says "Weather for Merritt Island. You can't on the words.
You have have to click on the icon. Then Weather History for Merritt Island.
Click the icon again.
We have a choice of either daily, weekly, or monthly. I choose Daily. Click on Extract Daily to the Grid.
Now I have 365 weather items.
Let's see what we have there. Maybe 366 - let's see if they have February 29th.
Yes - they would have to have February 29th. Yep so 366.
So we come over here and say =B5 dot. We can get the Max. That's the record high.
The Min is the record low.
Probably what makes more sense is the "mean or average minimum temperature".
So this is the low. The mean maximum temperature.
Cool. This is the high. We have to double click to copy that down...
No! Just change B5 to B5#. That says give me the whole array.
We get that back. Same thing here B5 to B5#.
That is really weird.
That the edit box seems to be a little bit too far to the left. I can't explain that.
The hassle is that I am sitting here in Merritt Island and I know it never gets down to 15 degrees.
At least not what I call degrees.
This is Celsius because Wolfram Alpha is a scientific knowledge base.
So of course they are going to use Celsius. So let's see. A couple of things we want to do here.
If i would want to chart this i would want to add the date so we come out here and do =B5.Day.
And it's TEXT. Oh my gosh what the heck were they thinking?
So asked for the =DATEVALUE() of January 1st. That works.
That gives me January 1st of this year. And if i want all of those records, B5#.
It gives me the whole year. That's great.
All right that's good and then my chart here. I will call this Low and High.
Use =CONVERT(C5,"C","F").
CONVERT is that great old function from the Analysis ToolPak.
I am going to convert that data which is in Celsius to Fahrenheit. Beautiful. Fine. Right here. Same thing - change C5 to C5#.
No! No deal.
Hey Joe McDaid and the Calc Team.
I bet you thought no one would ever have to use CONVERT on a 366-row array! We never did before. But now we do.
So this formula is one that i have to copy down. Can I double click to copy?
Yes. That works great. And then same thing here.
Actually, I should just be able to copy it right across. Right?
Yes. There we are. Double-click and copy that down. I choose all of that data.
Control+Backspace to come back up to the top.
Insert a nice little line chart showing the two lines like that.
So there is our high and low for every day of the year.
Now this is working for Merritt Island. Let's change it to somewhere else.
So i will come up here. Type Anchorage Alaska. It resolves that location.
And then we get the #BUSY! once downloading all of that data.
And there we are.
So now Anchorage is a lot colder than Merritt Island. Weather Data! When the new data types came out with Stocks and Geography.
I was doing live Excel seminars.
I would always ask "Well what would you like to see next?" Exchange rates was number one. Which they've added.
And then weather was number two. Hooray they've added it. It is just awesome.
Here is another one.
Remember that historical weather is available daily, weekly, or monthly.
So if we look at =TODAY() and then =ISOWEEKNUM of TODAY().
That tells us that we're in week 33.
So I have a series of cities here and when I ask for the weekly data. I get 53 weeks going down.
But then check this out! What I did is i said i want the INDEX of A5.weather.history.weekly. And which one do I want?
I had hard-coded it to 38 but it'd make a lot more sense to use this current week.
Double-click and copy that down. Add the dollar signs.
Oh, geez that confused me.
Alright so now we have week 33 weather weekly for all of these cities.
Let's say we had stores in these cities and we're trying to figure out if we are going to advertise next week or the week after that we are going to start to sell fall jackets.
Well only in the places where it's getting down in the 50s is that going to be appropriate.
All right so this is all cities pulling out one week. How am I doing that?
The INDEX of A5.weather.history.weekly comma 33. Next week this changes to 34.
Then 35, 36.
If i want to plan four weeks ahead of time just change that to plus four and that data updates.
Weather data in Excel this is so cool.
These new Wolfram Alpha data types.
You have to be on a Home or Student subscription right now.
I've been podcasting from this old laptop where i managed to get a Home Edition.
They are so so cool.
I realize that I've just been going on and on and on about them for several videos in a row now.
Well that's just how cool they are. There we are. I love that weather data.
If you like this video please down below click Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments down in the YouTube comments below.
My new book MrExcel 2020 Seeing Excel Clearly.
The "i" in the top right-hand corner will get you more information about that book.
I want to thank you for stopping by we'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks get your spreadsheet fix at MrExcel.com [ ♫♫ ]
Learn Excel from MrExcel podcast episode 2345.
There is weather data in Excel. Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well the Wolfram Alpha people talked about hundreds of new things that they're adding to Excel and weather was not mentioned. But, holy smokes weather is awesome.
Let's take a look! Alright weather. Here is Merritt Island Florida.
I've converted that to a Location. Not Geography.
That's the old one. Location is the new Wolfram Alpha data type.
I initially came here and I thought they had something called dot weather.
I 'm like whoa that's cool! But I was initially disappointed that it just came up as "Weather for Merritt Island".
But, there is a little icon there. That is the good stuff. So let's take a look.
I am going to click on the card. Then I am going to scroll down to Weather.
Open this up.
And it says "Weather for Merritt Island. You can't on the words.
You have have to click on the icon. Then Weather History for Merritt Island.
Click the icon again.
We have a choice of either daily, weekly, or monthly. I choose Daily. Click on Extract Daily to the Grid.
Now I have 365 weather items.
Let's see what we have there. Maybe 366 - let's see if they have February 29th.
Yes - they would have to have February 29th. Yep so 366.
So we come over here and say =B5 dot. We can get the Max. That's the record high.
The Min is the record low.
Probably what makes more sense is the "mean or average minimum temperature".
So this is the low. The mean maximum temperature.
Cool. This is the high. We have to double click to copy that down...
No! Just change B5 to B5#. That says give me the whole array.
We get that back. Same thing here B5 to B5#.
That is really weird.
That the edit box seems to be a little bit too far to the left. I can't explain that.
The hassle is that I am sitting here in Merritt Island and I know it never gets down to 15 degrees.
At least not what I call degrees.
This is Celsius because Wolfram Alpha is a scientific knowledge base.
So of course they are going to use Celsius. So let's see. A couple of things we want to do here.
If i would want to chart this i would want to add the date so we come out here and do =B5.Day.
And it's TEXT. Oh my gosh what the heck were they thinking?
So asked for the =DATEVALUE() of January 1st. That works.
That gives me January 1st of this year. And if i want all of those records, B5#.
It gives me the whole year. That's great.
All right that's good and then my chart here. I will call this Low and High.
Use =CONVERT(C5,"C","F").
CONVERT is that great old function from the Analysis ToolPak.
I am going to convert that data which is in Celsius to Fahrenheit. Beautiful. Fine. Right here. Same thing - change C5 to C5#.
No! No deal.
Hey Joe McDaid and the Calc Team.
I bet you thought no one would ever have to use CONVERT on a 366-row array! We never did before. But now we do.
So this formula is one that i have to copy down. Can I double click to copy?
Yes. That works great. And then same thing here.
Actually, I should just be able to copy it right across. Right?
Yes. There we are. Double-click and copy that down. I choose all of that data.
Control+Backspace to come back up to the top.
Insert a nice little line chart showing the two lines like that.
So there is our high and low for every day of the year.
Now this is working for Merritt Island. Let's change it to somewhere else.
So i will come up here. Type Anchorage Alaska. It resolves that location.
And then we get the #BUSY! once downloading all of that data.
And there we are.
So now Anchorage is a lot colder than Merritt Island. Weather Data! When the new data types came out with Stocks and Geography.
I was doing live Excel seminars.
I would always ask "Well what would you like to see next?" Exchange rates was number one. Which they've added.
And then weather was number two. Hooray they've added it. It is just awesome.
Here is another one.
Remember that historical weather is available daily, weekly, or monthly.
So if we look at =TODAY() and then =ISOWEEKNUM of TODAY().
That tells us that we're in week 33.
So I have a series of cities here and when I ask for the weekly data. I get 53 weeks going down.
But then check this out! What I did is i said i want the INDEX of A5.weather.history.weekly. And which one do I want?
I had hard-coded it to 38 but it'd make a lot more sense to use this current week.
Double-click and copy that down. Add the dollar signs.
Oh, geez that confused me.
Alright so now we have week 33 weather weekly for all of these cities.
Let's say we had stores in these cities and we're trying to figure out if we are going to advertise next week or the week after that we are going to start to sell fall jackets.
Well only in the places where it's getting down in the 50s is that going to be appropriate.
All right so this is all cities pulling out one week. How am I doing that?
The INDEX of A5.weather.history.weekly comma 33. Next week this changes to 34.
Then 35, 36.
If i want to plan four weeks ahead of time just change that to plus four and that data updates.
Weather data in Excel this is so cool.
These new Wolfram Alpha data types.
You have to be on a Home or Student subscription right now.
I've been podcasting from this old laptop where i managed to get a Home Edition.
They are so so cool.
I realize that I've just been going on and on and on about them for several videos in a row now.
Well that's just how cool they are. There we are. I love that weather data.
If you like this video please down below click Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments down in the YouTube comments below.
My new book MrExcel 2020 Seeing Excel Clearly.
The "i" in the top right-hand corner will get you more information about that book.
I want to thank you for stopping by we'll see you next time for another netcast from MrExcel.
[ ♫ ] Tips and tricks get your spreadsheet fix at MrExcel.com [ ♫♫ ]
