Ever wonder how you could easily strip 'illegal' characters from a URL? How about return the contents of an entire webpage to a Single Cell in Excel? Are you familiar with 'xPAth'? Today, in Episode #1599, Bill continues his review of Excel 2013 by looking at new ways to find, filter and work with Web-Based Data. Follow along with Bill as his review of Microsoft Excel 2013 continues today!
...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.
Hey, this is the Excel 2013 Preview: ENCODE URL, WEBSERVICE and FILTERXML.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Hey, I’m still working on all those books for Excel 2013.
I'm up to 994 pages done on my way to 2000 and countered a few cool things which I felt I had to pass along to you.
Three new functions we're going to talk about today: ENCODE URL, =WEBSERVICE and =FILTERXML.
Okay, ENCODEURL - this is a real simple one.
It just takes all of the illegal characters and a hyperlink and it encodes them to their percent equivalents.
Now, you don't have to use this all the time, you don't even have to use it in today's example, but just know that it's there in case your web service isn't working.
So, here's what we're going to do - we're going to go out and find a web service that returns either XML as an atom feed or JSON; J-S-O-N results.
And so I found here a great little free search.twitter.com API.
If you go to the web, you can get the instructions on how to use this and I determined that what I'm trying to do is I'm trying to build a URL with search.twitter.com/search.atom?lang=en, and then my query.
So, I put the ability to put the query up here -- pivot table -- and then this concatenates C2 - the first part of the URL - with whatever they type there.
Now here is the absolute magic: =WEBSERVICE of this URL will go out and return the entire webpage to this one cell.
Now it is actually -- let's take a look here - =LEN of that is returning 17,000 characters to that cell, which is below the 32,000 limit that Excel places on a cell.
But what's funny is Excel cannot actually display all of those characters.
Look, it just kind of stops out there you know after what?
About 100 and some columns worth of data?
So, what I found is really really helpful is ctrl+C to copy that, go to notepad and ctrl+V to notepad where we can start to take a look at what is going on.
I’m going to look for the fields that are getting returned.
Alright, and this is XML here, so now you're saying “Well, what good is this?
What do I do with 17,000 characters in a cell?” Microsoft thought of that.
They gave us a new function called FILTERXML.
Alright, and so I actually named the cell to be called “Result” so we point to the cell that has the 17,000 characters of XML and then call out the field that we want.
This is something called xPath.
I haven't used xPath a lot.
There's lots of xPath tutorials out there.
I encourage you to read those tutorials.
Right now, I'm just kind of going from a first example and figuring out how to adapt it for what I want.
We'll talk more about xPath tomorrow.
So this is actually -- I realize that it's returning 15 tweets and so I selected a nice big 15 row area, enter this formula =FILTERXML(Result,”//title”), control+shift+enter and I got the 15 results.
I also came over here and did the same thing asking for the name and then I went and checked and I realized – oh – oh, no - they're giving me this title all the time so it's actually the first name and the second title that match up.
Alright, so, you know the last 15 tweets that have mentioned a pivot table, isn't that kind of funny – “what's a pivot table anyway?”; “Oh Pivot Table how I hate thee #financerage – hilarious, right?
Now, we should be able to come up here.
Let's try this - we'll go up here and change our search term from “pivot table”, come up here and type “VLOOKUP” and it'll take a few seconds to get our results but now here is the same query, this time going out to Twitter and getting the last 15 instances where someone tweeted something about VLOOKUP.
And so that one happened to be in the same VLOOKUP and PivotTable – “Why hello, VLOOKUP-with-an-ugly-spreadsheet.
We meet again.” Alright, so just kind of interesting here how to go out and pull data into Excel.
Alright, now let's talk about the “gotchas”.
Oh, this is going to be slow, right?
Pulling 17,000 characters.
And one nice thing is they made this function not be volatile so it only gets calculated when I come here and press F2 enter, or when I enter something new as a search term here so it's not getting recalced all the time.
If you wanted it to get recalced all the time, well, you're going to run into trouble.
You could, of course, come here and say &REPT(“”,RAND*0).
That will make it volatile and it’ll keep recalcing over and over and over and very quickly, you're going to learn the Twitter throttles.
You're not allowed to update this.
I don't know what the number is but you're going to hit it and then you're not going to be able to update until an hour goes by.
Also, Microsoft throttles you - they don't want you to do any dialup service attacks - so there's good reasons why that is not volatile and you should leave it as not volatile.
Cool, cool.
Way to go!
Tomorrow, we'll take a look at a weather example where we actually use the FILTERXML in some more instances.
Bye everyone.
Thank you and see you next time!
Hey, this is the Excel 2013 Preview: ENCODE URL, WEBSERVICE and FILTERXML.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen. Hey, I’m still working on all those books for Excel 2013.
I'm up to 994 pages done on my way to 2000 and countered a few cool things which I felt I had to pass along to you.
Three new functions we're going to talk about today: ENCODE URL, =WEBSERVICE and =FILTERXML.
Okay, ENCODEURL - this is a real simple one.
It just takes all of the illegal characters and a hyperlink and it encodes them to their percent equivalents.
Now, you don't have to use this all the time, you don't even have to use it in today's example, but just know that it's there in case your web service isn't working.
So, here's what we're going to do - we're going to go out and find a web service that returns either XML as an atom feed or JSON; J-S-O-N results.
And so I found here a great little free search.twitter.com API.
If you go to the web, you can get the instructions on how to use this and I determined that what I'm trying to do is I'm trying to build a URL with search.twitter.com/search.atom?lang=en, and then my query.
So, I put the ability to put the query up here -- pivot table -- and then this concatenates C2 - the first part of the URL - with whatever they type there.
Now here is the absolute magic: =WEBSERVICE of this URL will go out and return the entire webpage to this one cell.
Now it is actually -- let's take a look here - =LEN of that is returning 17,000 characters to that cell, which is below the 32,000 limit that Excel places on a cell.
But what's funny is Excel cannot actually display all of those characters.
Look, it just kind of stops out there you know after what?
About 100 and some columns worth of data?
So, what I found is really really helpful is ctrl+C to copy that, go to notepad and ctrl+V to notepad where we can start to take a look at what is going on.
I’m going to look for the fields that are getting returned.
Alright, and this is XML here, so now you're saying “Well, what good is this?
What do I do with 17,000 characters in a cell?” Microsoft thought of that.
They gave us a new function called FILTERXML.
Alright, and so I actually named the cell to be called “Result” so we point to the cell that has the 17,000 characters of XML and then call out the field that we want.
This is something called xPath.
I haven't used xPath a lot.
There's lots of xPath tutorials out there.
I encourage you to read those tutorials.
Right now, I'm just kind of going from a first example and figuring out how to adapt it for what I want.
We'll talk more about xPath tomorrow.
So this is actually -- I realize that it's returning 15 tweets and so I selected a nice big 15 row area, enter this formula =FILTERXML(Result,”//title”), control+shift+enter and I got the 15 results.
I also came over here and did the same thing asking for the name and then I went and checked and I realized – oh – oh, no - they're giving me this title all the time so it's actually the first name and the second title that match up.
Alright, so, you know the last 15 tweets that have mentioned a pivot table, isn't that kind of funny – “what's a pivot table anyway?”; “Oh Pivot Table how I hate thee #financerage – hilarious, right?
Now, we should be able to come up here.
Let's try this - we'll go up here and change our search term from “pivot table”, come up here and type “VLOOKUP” and it'll take a few seconds to get our results but now here is the same query, this time going out to Twitter and getting the last 15 instances where someone tweeted something about VLOOKUP.
And so that one happened to be in the same VLOOKUP and PivotTable – “Why hello, VLOOKUP-with-an-ugly-spreadsheet.
We meet again.” Alright, so just kind of interesting here how to go out and pull data into Excel.
Alright, now let's talk about the “gotchas”.
Oh, this is going to be slow, right?
Pulling 17,000 characters.
And one nice thing is they made this function not be volatile so it only gets calculated when I come here and press F2 enter, or when I enter something new as a search term here so it's not getting recalced all the time.
If you wanted it to get recalced all the time, well, you're going to run into trouble.
You could, of course, come here and say &REPT(“”,RAND*0).
That will make it volatile and it’ll keep recalcing over and over and over and very quickly, you're going to learn the Twitter throttles.
You're not allowed to update this.
I don't know what the number is but you're going to hit it and then you're not going to be able to update until an hour goes by.
Also, Microsoft throttles you - they don't want you to do any dialup service attacks - so there's good reasons why that is not volatile and you should leave it as not volatile.
Cool, cool.
Way to go!
Tomorrow, we'll take a look at a weather example where we actually use the FILTERXML in some more instances.
Bye everyone.
Thank you and see you next time!
