Today Bill takes a closer look at =FilterXML and xPATH, how it works and how to manipulate the retrieved data to reflect the results you are looking for. Using Weather.Yahoo.com, Bill works out a fine example of these functions in use. Follow along with Episode 1600 to see these tools in action and begin to build your ideas!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast.
Excel 2013 preview: FilterXML and xPath.
Hey, welcome back to the MrExcel netcast.
Now, yesterday we talked about using the equal Web service and encode URL; and briefly talked about filter XML.
Now, this example-- this is actually from the Excel 2013 Help file.
They had a nice little Web service out here that goes out to xml.weather.yahoo.com and pulls in a certain ZIP code.
First thing that I want to show you is, when you have a file; and you save the file, close the file, and reopen the file, WEBSERVICE functions have automatically been disabled.
You're going to have to turn that back on.
So, it doesn't want it just running out there to the Web and pulling in, you know, thousands of characters of data; especially if it's going into your WEBSERVICE quota.
Okay, so, here is where the =WEBSERVICE is pulling the data back in.
And remember, yesterday I showed you this great trick; we're going to copy that cell and we're going to come out to Notepad and paste -- and paste, and we can start to take a look through the various fields here.
Now, in the Microsoft example, they had pulled in something called weather:location/@City and @region; something else called weather:condition/@Temp and weather:condition/@text; and, they had pulled in weather:forecast/@day and weather:forecast/@text.
I said, oh well, let's see what else we have out there.
So I'm going to go out and look for this yweather:forecast.
So, we’ll come here to Notepad-- so, here in Notepad, let's make sure we go to the top, because their “Find” only goes in one direction.
Look for yweather:forecast, like that.
Okay, there we go; and we can start to just take a look at the data that's coming back.
So, there's the day, that's Tuesday.
There's actually a date field; there's a low field; a high field; and then, the text field and code, alright.
So everything but code, I understand exactly what that means; the -- you know, so we have day and date.
So instead of actually getting the day like Tuesday, if I wanted to get the date, I know that I want to change day to date; so let's come back here and take a look at this formula.
This is a rate formula, so I'll select all those cells and instead of @day, change it to @date.
Ctrl+Shift+Enter.
All right.
And then format that as a day.
We can even use long date if we wanted.
There we go.
So, very possible to, by taking a look in Notepad, you can figure out the xPath that you need.
So, the Microsoft example had given us day and text.
I copied that example and used low to get the low temperature; high to get the high temperature; and it all seems to be working fairly well.
Let's see if we can update this.
So, there's a new ZIP code; Merritt Island, Florida; 76, Fair; and the weather for the rest of the week.
So yet another example of how to use the WEBSERVICE; this time, though, taking a look at the FilterXML function and how we can, using Notepad, figure out the various fields and pull different data in.
Oh hey, I want to thank you for stopping by.
See you next time, for another netcast.
Learn Excel from MrExcel podcast.
Excel 2013 preview: FilterXML and xPath.
Hey, welcome back to the MrExcel netcast.
Now, yesterday we talked about using the equal Web service and encode URL; and briefly talked about filter XML.
Now, this example-- this is actually from the Excel 2013 Help file.
They had a nice little Web service out here that goes out to xml.weather.yahoo.com and pulls in a certain ZIP code.
First thing that I want to show you is, when you have a file; and you save the file, close the file, and reopen the file, WEBSERVICE functions have automatically been disabled.
You're going to have to turn that back on.
So, it doesn't want it just running out there to the Web and pulling in, you know, thousands of characters of data; especially if it's going into your WEBSERVICE quota.
Okay, so, here is where the =WEBSERVICE is pulling the data back in.
And remember, yesterday I showed you this great trick; we're going to copy that cell and we're going to come out to Notepad and paste -- and paste, and we can start to take a look through the various fields here.
Now, in the Microsoft example, they had pulled in something called weather:location/@City and @region; something else called weather:condition/@Temp and weather:condition/@text; and, they had pulled in weather:forecast/@day and weather:forecast/@text.
I said, oh well, let's see what else we have out there.
So I'm going to go out and look for this yweather:forecast.
So, we’ll come here to Notepad-- so, here in Notepad, let's make sure we go to the top, because their “Find” only goes in one direction.
Look for yweather:forecast, like that.
Okay, there we go; and we can start to just take a look at the data that's coming back.
So, there's the day, that's Tuesday.
There's actually a date field; there's a low field; a high field; and then, the text field and code, alright.
So everything but code, I understand exactly what that means; the -- you know, so we have day and date.
So instead of actually getting the day like Tuesday, if I wanted to get the date, I know that I want to change day to date; so let's come back here and take a look at this formula.
This is a rate formula, so I'll select all those cells and instead of @day, change it to @date.
Ctrl+Shift+Enter.
All right.
And then format that as a day.
We can even use long date if we wanted.
There we go.
So, very possible to, by taking a look in Notepad, you can figure out the xPath that you need.
So, the Microsoft example had given us day and text.
I copied that example and used low to get the low temperature; high to get the high temperature; and it all seems to be working fairly well.
Let's see if we can update this.
So, there's a new ZIP code; Merritt Island, Florida; 76, Fair; and the weather for the rest of the week.
So yet another example of how to use the WEBSERVICE; this time, though, taking a look at the FilterXML function and how we can, using Notepad, figure out the various fields and pull different data in.
Oh hey, I want to thank you for stopping by.
See you next time, for another netcast.
