How to Use Webtables to Import Specific Data to Excel

Hello everyone, welcome back to the videos about the value of time, so in this video I want to show you how to get information from web pages, but maybe not all the information from the web page, so before I finish, I did some videos, actually one. where you can do the web query where you can navigate in the internal browser within Excel. I'm going to go here and show you so that inside of Excel you can pull

data

from external

data

from the web to show you how to do it. do it from text if you can copy the file link and I've shown you how to directly write the code so you can get information from a site like Yahoo so you can get historical data or any of these key metrics or anything. like that, but if all the ways I've shown you so far include the full web page, every time you enter, let's say you're going to get key statistics for Microsoft, you're going to enter everything that's listed here and it's there's a lot of really good information, but it's not always you know you don't want it all, sometimes you don't care about dividends, you're not a dividend investor, you're not a value investor or anything like that, maybe you just want to get a couple of your ratios because you want to make sure that it meets with your criteria or you only care about your beta because you're a technical trader or something and you want something more volatile or something, so I'll show you how you only input limited information, so at first glance it seems like this wouldn't be very useful because you'd rather get more information than less, but it will actually help you extract data a little faster, especially if you're going from a website, maybe it's not Yahoo, it's some other website where there's a ton of information and you're getting it all at once. and that could take a long time, but when you really just need one line or something, you can reduce it, I'll show you how to do it.

We'll just use Excel here and I'll go ahead and open in developer. I'll open the Visual Basic window. I only have them. side by side so you can see inside Visual Basic. I can press Ctrl R to open my project window and double click on this workbook because that's where we want to write my code. I can go ahead and get rid of that window because I don't need it anymore and I'll just create a sub and then call it a macro. It doesn't really matter what you call it. I'll just show you how to incorporate things and so normally when we do it.

When putting or fetching data, we're going to

import

it, you know, anywhere you want, but just to be an absolutely generic macro type here to show you what we're doing, I'm going to clear the cells and that's just going to delete them. everything, in case I get more overlapping data, I'll just use unclear cells to clear everything that's there and then give me a URL to work with, but leave it blank for now while I type the rest, so what I have What to do is do it with the active sheet dot query lookup tables that add two endpoints, then URL quotes, semicolon quotes, and then whatever variables we're using to hold our URL.

I'm using Q URL, which is the query URL and then the destination comma and then the colon is equal and we'll give it a range to

import

. I'll just set it to 1, go to the capital, there you have it. I put in a 1 and then I close the trailing parenthesis so we have that and then we need a trailing with because we have a width so we need to go in there and then we do the background all the one word background query equals true and we update . query in the background and then a colon equals false great so we have them and this is our basic like bringing in information from a URL this is where we start from and any of the videos that I've shown you in the past where they are simply writing.

The code, this is what you're going to start with, but I'm going to show you how to run it with just that, so I'm going to take the Yahoo Finance URL and just say, press Ctrl C to copy it and go back to Excel. and then fill that in with our you are for our URL, so our URL will be pulled from here and if I run it like this, it will pull everything from the web page except I'm not connected to the Internet, here we go, okay. now let's do it there we go so it pulls everything from the web page and you may not want everything from the web page so what I can do is inside my width box right there I can do equal point web tables and then, oops, press enter there, I'm an accident, we go the same and I can put one in quotes, so I want you to be able to put any number you want and the table associated with that number will appear, so with the number one the first table is search and that's pretty useless, so I'm going to get rid of that.

I'm going to start with two, so if I run it with just two, it just pulls them in, that little chunk doesn't pull in all at once if I do that. run it with three, they don't get a different piece, see, you just go in at the end of the fiscal year. I run it with four. I think those are the margins, a profit margin, operating margin. I run it with five. Will pull. something else in there you get ROA and roee so if you want the DuPont formula or something like that you can use those so basically you can fill this number with any number you want and they will get a certain table so if you don't want to extract everything at a time what you can work with, you can work with that table and this should work for a bunch of different websites.

It doesn't work for every website because not every website has all of their information divided into different tables like that, but Yahoo Finance does that with pretty much everything they have, so if you want to change that, let's go ahead and show you, let's go to the options and if we want to get the option ladders and all that kind of stuff, we can take that. URL come back here fill in the URL below our reference URL variable run it with just this is just web table five there may not be a web table file on that page yeah so let's go ahead and go back and do just two , so rent with two that should just pull it just pulling the call option so see that they all have C there they are linked right there so it's just the call options and see that ends and the last one to see if I run it for three.

I ran it for three, so I should just pull in the put options, so if I just wanted to see the type of put calls one or the other and not both, then I can use two and three to get my web tables to get those options. contracts, so Yahoo Finance is really good for getting data like that, it's a super easy way to do this, you want to get an estimate from an analyst, so do the same thing, just grab the URL, go here, fill that in control V, so here we go. I can run it and this is what shows up in Table Three, so it's just used for this, it's a revenue estimate.

If I run Table Four web, instead of the revenue estimate, it could be this: earnings history, so they got earnings history four and this is at Microsoft we're using Microsoft data right here, I take the Number five, it can be whatever the next table is on that website, earnings per share trends, then how your trends for the other earnings per share are changing over time, from three months to one month. week so far to get estimates, so you can really go to any website and you can do all kinds of different sites. I think the profile was the summary information, the summary information right here, take that one that's fun to do because you can get information really quickly that way, so if I just do Table Two and actually put it in, it just shoots up, yeah, you're open, you've got your clothes from yesterday, your bid-ask spread, you've got your day range, which is like the key information you just want to see what happened today, you've got it right there and you're ready to hit the web. .

Table three for that same URL does the same thing, but it's the other side, so it's the market cap, you have the company size. Do you have your PE ratio so you can see what its rating is? You can look at your beta to see how volatile it is and what kind of volume you should be looking at in four stock trades in one day. You know all that kind of stuff. to set up right there is very easy you could actually do multiple web tables at once so you could do two and three so let's go ahead and do it that way then run it and then we have two and three right there so they are there, one after the other, if you want to add more, you can make two, three and four, you can get them, so if you want to make a

specific

set four, it's not useful, it's just additional information. that was at the bottom of that web page, but if you were doing this for a different URL than two, three, and four might be useful things that you want, so it's just a quick video on how to get segmented data, so if not You don't want to have everything, you just want to get simple information so that it doesn't clutter your page and therefore makes it easier to work with, so I hope that helped you if you have any questions about how to do this go ahead and put comments in the comments section below and I'll be happy to try and help any of the awesome people who watch my videos.

I hope you guys can help you too, but that's all and thanks for watching, if you like this video let me know in the comments and you'd probably like these too to automate visual tasks that you normally do well so first thing what we are going to want to do is automate