How to automate Key Statistics from Yahoo! Finance

Hello everyone, welcome back to the videos on the value of time. In this video I'm going to show you how to get the stats, the key Yahoo Finance stats, that describe a lot of really interesting things about stocks so you can get the profit margins that you need. get your PE ratios, your market cap and, uh, Roa and Roe, you'll get all kinds of stuff, all these really important figures and numbers about different stocks, all you have to do is put in AAPL, msft or G. Go Home Depot, any stock you want. put them all in there, press get data and it will automatically get all the data you want, so wait a second to run it, done, and now you have all the data we need and we can compare different actions with each other.

So Apple, Microsoft, Google are kind of competitors, so you can see if you're trying to trade one over the other. You can see which ones you would like to be long or short, keep or whatever you want to do with them. So I'm going to show you how to do this. It's pretty simple and not too difficult to do. So for this video I went ahead and made a little split screen so you can see Yahoo Finance on one side and you can see Microsoft Excel on the other and that way when I go back and forth between them, it's easy to keep track of and I don't have to keep minimizing and opening other windows, so we have Yahoo

finance

here, everything.

What I did was look for a stock, you can get any stock you want. I always use Microsoft as my example just because it's convenient, so I opened up Microsoft in Yahoo Finance and then I went and just clicked on the stats tab so I can have all the data here, this is all the data that we're going to get, um you can see, we have our business information and our valuations, we have some financial data, we got their dividends, we have all the things that we need, so really everything that we really need. What we need from this is the URL where this data is because we're going to pull it from the Internet anyway, so we don't need to see all of this now because we're going to import it into Excel so that The first thing I'm going to do is right click here and copy it.

You can control C uh, but I want to make it very clear what I'm doing, so I right click and copy U. to go back to Excel and we'll have it available to paste in a minute, but first I want to create the layout of the data that we're going to get, so in the sheets one in the first cell. I'm just going to put tickers and then I'm going to put some tickers and so we can go to Google uh we already have Microsoft but we'll put it there um apple and we can do Home Depot and Lowe's um and then we can Do Target and Walmart also just for Caterpillar, it doesn't matter , you can put in as many actions as you want, we'll make it expandable, so if you just put in like two actions, um, like that, uh.

It will work for just two or if you want to go again and complete as many as you want, it will work for as many actions as you want, we'll set it up to keep it that way. that's our outline right now, that's all we need, the rest will be done in just a minute, so let's go ahead and open up Visual Basic and I've set it to open on the other side so you can see both at the same time time, so inside Visual Basic it appears grayed out, so I'll press control and that will open my project window.

I'm going to double click on this book and it will open. where I can actually edit my VBA um and then I'm going to get rid of that window now that I don't need it and then here I'm going to make sub and I'm going to call this uh fundamentals and then give myself some space. I press Enter a few times, so now we have space to type some things in here, so the first thing we're going to want to do is, to loop through all of these tickers that we have. I'm going to need a for loop uh the for loop is the easiest for me anyway, it's more convenient.

I like to use for loops. There's also a while loop, but I don't use it that often, so four and then you can give any variable. you want, I just like to use n or I, so I'm going to put I for I equals and now we're going to go, we're going to go through column B, we're going to start at B and we're going to continue until we get to the end and that end could be as immediate as C or it could go all the way to I or it can go on, you know, go on as far as we want, um, but we're.

For this case, we need it to start at B and we need to go until we get to the end of our list, so column B is the second column, so I'm going to say two, so we're going to start at I equals 2 because will be column B and then continue until we go to and then we'll start um to find out what the last row is, we're going to say start at cell one, which is A1 and then just go to the end and continue until we get to the end and the code for doing it is uh we'll say sheets one because we're on sheet one do cells one and that says column A. and row one um finish go to the end XL to type and then that says start in cell one and the sheet one and go all the way to the right, go all the way to the right, you could say Xcel down and I'll say go all the way down, but there's nothing down, so we're just going to say go to the right, so end XL to write and then what do we want when we get there?

We want to know which column we are in. make column, then we press enter and you can see that all of that is capitalized, so we know we entered it the right way and then every time you have a for loop at the end, you should have a next and then any variables. you used and then I used I, so I'm going to say next I, if you said you know the next ticker, then you just have to if you had the ticker up here, you could say that the ticker is equal to two, but I'm going to use the variable ticker later, so I don't want to use it now.

I'm going to use it. Actually, next time, after, once I go from two to the end, whatever it is, in this case it's me, which is nine. from two to nine we're going to go, the ticker that we're going to use is going to be in any cell, it's going to be in row one all the time, but it's going to be a different column every time we do it. So we're going to say it's equal to sheet one and I'm using sheets before all of my cell references because I'm actually going to use sheet two later in this workbook, so I'm going to reference sheet one every It's the moment when I reference a cell because later on, if I say um cells one, but I mean sheet two, then it's going to mess up if I'm not on the right sheet, so I say sheets one. cells and then I'm going to, in this case, we're going to assign the ticker value, so the ticker is going to be, uh, sheets one and then row one, so the cells are going to be a comma and then what column are we going to be on.

I'll be in whatever column I'm currently in, wow, whatever column I'm currently representing, so let's say a comma I and then that's it, so the ticker will be the sheets one sells an I, so whatever. let it be our column, that's our row, that's our column, so it will fill in as we go and if it did, I'll do it. I'll show you real quick. I'm going to press control G and this immediate window will appear at the end. down and right here I'm going to do the debugging. It prints the ticker and then if I run this, you'll see that it runs and it just gets all the tickers, so now we have all the tickers, um at this point and now we just need to use the URL that we pulled from Yahoo.

finance

to get the ticker data and I'll have them put it on sheet two so all the data is here and we can move it to paste it here, so what we're going to do is I'm going to say URL Q.

This is another variable . You can make it any variable you want, but it will be our query URL, so I'll call it Q URL because it's easy to know and remember. means I'm going to then press control V and paste all the data that we got from Yahoo Finance, well, not the data, we'll paste the URL, but that's the reference for the data that we're going to pull. and it needs to be a string, so I'm going to put it in quotes so now they have q URL, which is ours, our variable is going to be this string and that's the URL that we're going to extract from, but we need to make sure that we're not always extracting Microsoft data, we're going to replace the instance of Microsoft here and Microsoft here with whatever ticker we're using to insert a variable into a string. quotes and quotes and then between those and you can put a couple of spaces and I put the ticker and then I'll scroll down to where Microsoft is.

The second time I'm going to remove msf, I'm going to put quotes and quotes and then between those and I'm just going to put the ticker again and this is how you insert a variable into a string by doing quotes and then the variable and then and quotes and what is doing is this first quote Clos closes this string so you can see there's a string and a ticker and then it opens a new string and the next ticker and then it opens and closes a new string immediately. Sometimes you can leave this completely and be done with it. um, but I like it for absolute clarity.

I like to have both in there, so now we have the URL that we're going to extract from and now we just have to say extract from that URL, so let's go to GNA. We're going to want to put it on sheet two, so I'm going to go ahead and tell it to go to sheets 2. Selecting and usually adding a select line in the VBA code is a little extra that's not needed. necessarily necessary, but in this case to extract that data with a lookup table, we are going to need to specifically say go to sheet two U because we want to import that data to sheet two and if we don't say go to sheet two and we try to import it directly without going there first, it's not going to work, so now I'm going to do it with and then to extract it from the URL, we're going to do it with the active sheet Act Now we have two sheets selected, so we have the active sheet. q r and t l lookup tables. add connection and then colon equals and then open quotes because we're going to do a URL and then a semicolon and close quotes and then we're going to do and and we have the URL that we want Q URL comma destination and now this is where we go. to place, so we need two equal points and then, although I already have leaves two selected, I'm going to be very specific here and do leaves two again. range A1 so that everything is placed in A1 and then here for the connection.

I opened another parenthesis, so I'm going to make an additional closing parenthesis so that there are two closing parentheses at the end and that's because there's one open here and then another one. up to here, so I'm going to go ahead and click outside of it and you can see this has turned blue and capitalized, so I know things are working fine whenever I have a WID, I need to have an ending. width and I see that it turns blue when you click outside of it um and now between those I just need to do some kind of cleanup so I'm going to make the background qu background and query equal to true and then I'll update the background qu query and then this one needs a colon equals false and then I'm going to get rid of these blank lines so it looks nice and clean, so now we have our for loop, we have our URL, we go to sheet two, we import this data, um , when we go to sheet two, let's go ahead and delete what's already there because if we're importing a bunch of stuff over and over again, I just want to make sure everything is clean, so I'm going to do that after sheets 2, select I I'm going to make cells.

CLE um and actually, just to be absolutely sure, let's go ahead and make sheets 2. cells. CLE and that's me, you really definitely don't need it if you're already recruiting and the immediate next line is sales. sure, but I'm trying to be as absolutely clear as possible here and then we have our width and that's going to bring up the data so I'm going to go ahead and put a stop line there and I'm just going to show you what it looks like so I'm going to run that and then we'll stop, so we'll stop right there and then on sheets two, so sheets one still have all of our tickers.

Sheet two has this is all the data. because in this case this is Google, because we did the first one and Sheet one is Google great, so we have all the data from Google right here and now we just have to say take all this data and put it in sheets one. Before I do that, I'm going to highlight all of this here and do c control c, so I'm going to copy everything. These are the headings for everything I'm going to come back to here. Let's put They put them right there and they come as a word wrap, so I'm going to go home while everything is highlighted.

I'm going to click Home and I'm going to unwrap and this is the word wrap which is the wrapped text. right there, uh, I'll make this big real quick so you can see it there, so wrap the text, unwrap it, and then just create column A. You know the right size, so now we have all the headers, so we didn't have what to go WR in the code to include those headers now we just need to copy and paste them every time we run the macro so I'm going to minimize that again and then we can go ahead and tell the code so I'm going to get rid of stop I'm going to delete that and I'm going to stop runningI'm going to hit the stop key up here to reset um and now we just need to fill in here and tell you at this point copy everything here, all of these headers on sheet one, go to sheet one under the corresponding symbol and So.

I'm going to go here, I'm going tosay we're looking from B3 to b69 and to do that, we're just going to say um, after our end, we're going to say sheets 2. range and then a range because we're doing cells, then we do like one in this case it would be like three commas two, but for this because we're making a range that spans multiple cells, so range and then we want B3 in quotes, colon, which means up to b69 and then close the quotes and close the parentheses, copy okay, so in this case we're going to copy all of these, so what we're saying is sheets 2, range B3 to 69. which is all that, copy it and then we'll go back to Sheets one, so we make sheets one. select and then we'll have to say it so we go back to Sheets One and then looking at Sheets One we'll need to be in whatever column we're in so we'll say uh cells and then we need to know which row we're always going to start in row two , so wherever it is depending on what ticker it is, but we're going to start at row two and put it there for the cells. two, two commas and then whatever column we are in is still I from our for loop, so we can do two comma I and that will put us in any column because I will be our column in this case, so cells 2 comma I and then close that um, select and then now we're going to say we already went through sheets two, we copied this, we came back here, we select the right header that we're on and then we select it and so we did it. cells 2 I select and then we're going to need to paste and so paste we do active sheet paste paste there we go um and then every time you make a copy it's going to do a little bit here I'll show you so if I highlight and then if you copy this area, you'll get the little dancing ants around it, so we want to turn that off, and to turn that off, normally you would just press Escape if you were on your keyboard, but in VBA. you do mode Cut Copy equals false and that says just turn off the dancing ants um and then that should be all we need so we check it out and paste it here um and then if we run everything it should work so I'm We'll run it if there's a problem, we'll fix it when we get to it so you can see it jumping through the data as it does.

And I have nine tickers to work with, actually eight. Here we go. I took them all out, uh, I'm just going to highlight them and resize them so now we can see. I'll also make it big, since we already have the code done, so now for all this data, it looks like they're all different. We know that not only was the same ticker run over and over again, so now we have a list of all this information for all these actions, apparently none of them have, oh Google, they don't have any leading or trailing information in the div, oh Google . it just doesn't have dividends, so it's pretty clear why that's the case, but now we have all this information here so we can use it to compare various tickers, so if you want to include some competitors, we have Target and Walmart. and we have Home Depot and Lowe, so those are some competitors, we also have Microsoft Apple and Google, those are somewhat competitors in terms of computer products and so on, so we can compare them with each other, we can see that we can see between Home Depot and The lows are market cap, so Home Depot is a bigger company than the lows and then if we scroll down and get a profit margin so we can see that we actually do this, let's highlight the first row, Let's go see and freeze the panels. and then just freeze the top row and now if we scroll down we can see that Home Depot and Lowe's are competitors so we'll go ahead and create a box border around them so we can see everything in between them, so now it's very clear.

Let's go ahead and see, we have the revenue. The income, so we have Home Depot, has a higher income than the minimum. Earnings per share also have even higher quarterly revenue growth, so Home Depot is growing more than Lowe's. They are all interesting things. to look at, Home Depot also has more cash, operating cash flow is even higher, we have their beta, they got a pretty similar beta 0.01 doesn't make much difference when you talk about beta, they probably move similar to the uh S&P um but now we can go look at the average volume so pretty much even around 400,000 more shares traded for lows than Home Depot uh that might have something to do with the price because the prices are different too um but we can go. go ahead and look at all this different, we can compare any of these U.

I'll probably do a video where we really look at some of these fundamentals and we really like to talk about how companies are run and how to compare them to each other, but this is just getting the information to You can do your own analysis on your own, but I like to look at the profit margin, that's probably a good place to start. The profit margin at Home Depot is almost double that of Lowe's, so I mean, I feel good. about Home Depot just from this, which is in no way scientific, so don't take this information and say "well I need to buy these", but at least you can look at it and say "okay", this is a good starting point and we You can do more extensive research from here, but it's a good starting point to get more detailed information about your background, you know the basics of all these companies, so anyway, here's how you can get that data. just change them as much as you want.

You'll know you get rid of the borders right there and so you can change these actions to whatever you want um and you can do it if you remove them like this um then just put new actions and run, run the macro if you want to do it, go to developer and insert a button , you can do it here and then connect it to that macro that we have and you can even call it, get data, there you go and then if you complete this, I'll do Apple and Microsoft real quick and then you run it, you click on that and then it pulls the data to so now we can compare them here and we have all the data we want. fill it out with whatever you need and then delete it when you're done and you're good to go so I hope it's helpful if you have any questions feel free to comment in the comments section below and I'll keep answering.

However, if you can, or if any of the awesome people who watch these videos can also help, it would be greatly appreciated, anyone who has any questions, so also, if you want to see a particular topic, please feel free to comment. below about everything you want me to cover as far as VBA or Excel or even just general finance topics so yeah thanks for watching if you like this video let me know in the comments and you'll probably like these too. My first thought was to do an if, then I could say if the month was that particular date, it was minus 12, it would be like the four wh's.