How to get Yahoo Finance Data in Google Sheets!

in this video we are going to be going over how to pull yahoo finance data into google sheets and we can see here yahoo finance provides a lot of useful data that would be really nice to have into our spreadsheets so we're going to go over how to pull things like the price which we can see listed here the earnings date the x dividend date and if we scroll down even farther we are going to pull things in like you can see we have a full description of microsoft right here which we are going to pull into our spreadsheet and if we jump back over to our spreadsheet this is what our finished product is going to look like this is just an example of what you can do and you can see here i have apple stock ticker listed here but by the time we're finished you can put in whatever stock ticker you want and hit enter and you can see all this data is going to be pulled into our spreadsheet from yahoo finance so let's go ahead and jump into our tutorial okay so you can see i already have my spreadsheet formatted where it is ready to pull in yahoo finance data and we want to pull things from yahoo finance like the price of the stock the earnings date the x dividend date we want to pull its industry and we also want a complete description of the company so let's go ahead and jump over to yahoo finance and for the sake of this tutorial we are going to be looking at microsoft and if you've looked at yahoo finance before then you're familiar with the setup of this page we can see here they have the stock price listed they have their earnings date here they have their ex dividend date here and if we scroll down farther down this page we'll be able to see a complete description of microsoft let's just go ahead and jump back over to our spreadsheet and if you've watched my web scraping video on google sheets before then you know how to use the import html command that's a really useful video i suggest you watch it but for pulling data from yahoo finance we are actually going to be using a function called import xml you can see it listed right here and essentially what it does is it imports data from structured data online and so we're gonna go ahead and use this formula here and open up a parentheses and in order to use this formula the very first thing we're gonna need to do is we're going to jump back over to yahoo finance and what we're going to do is we're going to copy the url of this page into our spreadsheet and i know you guys can't see me doing it but i'm up here at the top i'm copying the url and i'm going back over to our spreadsheet now and what i'm going to do is i'm just going to add quotations and i'm going to paste the url so you can see it right here and i'm just going to close this off with quotations so that's part one of pulling this data into google sheets there's only one more part all we're going to do now is we're going to add a comma and add quotations again and now we need to actually pull the price into google sheets so what we're going to do is we're going to jump back over to yahoo finance and again here is the price that we want to pull and this is where things they don't necessarily get tricky but it is a little bit more advanced so what we're going to do is i'm going to double click or double tap here depending on if your mac and i'm going to come here and click on inspect and what's going to happen when you click on inspect you're going to have this show up on the right side of your screen and essentially it takes a second to load so don't fret if it takes just a moment but we're going to have all this code show up on our right and you can see as i hover my mouse over these different codes different things over here are being highlighted so if i scroll down a little bit you can see the day's gain and percent gain is being highlighted but if we scroll back up we can see if i have my mouse listed right here it is highlighting the price of microsoft and that is what we're going to need to pull into our spreadsheet so what we're going to do is i'm going to double click on this code right here if we come down here to copy you're going to see we're going to have a list of options i'm going to click on copy full xpath and once i've done that we're going to jump back over to our spreadsheet and we're going to paste this into our formula we'll close it off with quotations and close it off with a parenthesis so you can see what we did we used the import xml function we then added the url and then we pasted in the xpath like i just showed you and this is all we need in order to pull things into google sheets so when i hit enter if everything goes as planned you can see we now have microsoft stock price listed in google sheets so what we're going to do is i'm going to go ahead and go forward we're going to pull things like our earnings date and x dividend date into google sheets using the exact same method so let's go ahead and we'll copy the first part of this formula since that's going to stay exactly the same so i'm just going to copy this we'll put this here let's go ahead and fix that there we go okay so you can see all i did was copy the first part of this formula now we need a new xpath in order to pull in the earnings date so what we're going to do is we're going to jump back over to yahoo finance and if we scroll down let's see the earnings date is listed right here so what i'm going to do is i'm going to double click on this we'll click on inspect and you can see right here that april 29th is what i want to pull into my spreadsheet so i'm going to click double click this we'll come down here to copy we'll copy the full xpath let's jump back over to our spreadsheet and we'll paste this in we'll close off this quotations close it off with the parentheses and here's our formula for earnings date i'll push enter and you can see we now have our earnings date in google sheets go ahead and center this text as well and so now i want to pull my x dividend date into google sheets so again we're going to do this almost the exact same thing i'm going to go ahead and copy the first part of this formula since it's going to stay exactly the same and i'll go ahead and paste that here and so if we jump back over to yahoo finance we can see our ex dividend date is listed right let's go ahead and scroll back up it's listed right here so again i'm going to double click on this we're going to click on inspect and this will pull up it takes just a moment usually let's scroll back down so we can see what we're highlighting and right here this is the code that we need to copy the xpath for so i'll double click on this we'll come down here to copy we'll copy the full xpath jump back over to our spreadsheet and we will paste this into google sheets once again we'll close it off with a parentheses be sure to add quotations if you're having trouble make check and make sure that your quotations are there for the url and the xpath let's go ahead and hit enter and now we have our x dividend date in google sheets as well all we need left to do is import our industry and the detailed company description so let's go ahead and just like we have been doing we'll go ahead and copy the first part of this formula just for time's sake and jump back over to yahoo finance and now i need to find where the industry is listed here and pulling industry into google sheets is something that the google finance function doesn't have so this is actually a really useful method so let's go ahead and scroll down and see if we can find where this is listed and okay so here we go right here you can see the sector and the industry is listed you can pull on whichever one you want i'm going to go ahead and pull in the sector so i'm going to double click right here we'll click on inspect and let's see if it'll show us which one we need to pull up and right here is what we want to pull into google sheets so i'll double click we'll go to copy we'll copy the full xpath we'll we'll add quotations and paste this into our spreadsheet close it off and hit enter and now we have our industry into google sheets i'll go ahead and increase the font size just a tad and the last thing that i want to pull in is our detailed description of the company so again i'm going to copy the first part of this formula since it's going to stay exactly the same and i'll paste this in here and now let's go back over to yahoo finance and here's our detailed company description you can see it's pretty long so it's pretty cool to be able to pull this into our spreadsheet so we can see exactly what the business model is for each stock that we look at so let's go ahead and double click we'll click on inspect and if we highlight our cursor here we can see this entire thing is being highlighted so let's go ahead and double click we'll go to copy we'll copy the full xpath and we'll come back over to our spreadsheet we'll paste this in and hit enter and there we go you can now see we have the entire company description paste it into google sheets so let's go ahead and center this text i'll give it just a little color let's go ahead and fix the formatting for this and this as well and so we have just successfully web scraped all of microsoft's data into google sheets now you'll notice if i were to change the stock ticker here to apple nothing updated and that's because we hard coded all of this data what we can do is we can make a simple change in order to automate this entire web scraping process and so what we'll do is we'll come over here to the price and we're currently looking at the price for microsoft and what we can do is we can remove this part of the url right here remove microsoft as well and we'll come right here outside of the quotations and what we can do is we can actually add on to this url let me go ahead and fix that i'm going to remove this okay there we go so you're going to remove all the data up to where it lists microsoft stock ticker in the url and what we're going to do is we're going to add to this url and in order to do this all we're going to do is we're going to add an and sign let's see maybe it needs to be right here we're gonna add an and sign and we're gonna click right here and once i've done that i'm gonna hit enter and you can see we have microsoft stock price listed right here still but now if i were to change this to apple stock ticker we can see our price was automatically adjusted based off of what is listed right here and so this will work for pretty much any stock so let's say i want to look at verizon i'll hit enter and this will automatically update or if i want to look at bank of america and you can see just how effective this can be when looking at large amounts of data so let's go ahead and do this for all of our formulas all we need to do is remove this data here we'll add quotations we'll add an and sign and we want to add on whatever is listed here we'll do the same thing here as well we'll do the same thing for our industry and now we're going to do the same thing for our description we'll add our and sign we'll come up here click on our stock ticker and hit enter and so now we've done everything correctly this entire spreadsheet should be completely automated to pull data from yahoo finance depending on the stock ticker that we have listed right here so let's go ahead and test it out we'll list apple stock ticker and hit enter and you can see all this data was automatically pulled so there you have it guys that is how you pull yahoo finance data and put it into google sheets and i also showed you how to completely automate the process so if you enjoyed the video be sure to like and subscribe to the channel i appreciate you watching this video i'll see you next time thanks so much

Channel: Dividendology

Views: 79,997

Keywords: yahoo finance, google sheets, yahoo finance google sheets, how to get live data in google sheets, how to get stock data in google sheets, how to get dividend data in google sheets, how to get live stock data in google sheets, finance, google finance google sheets, refresh yahoo finance data in google sheets, google finance, pull yahoo finance data into google sheets, stocks in google sheets, yahoo finance api, yahoo finance and google sheets, yahoo finance api google sheets

Id: 8iQ-v7XduyQ

Length: 13min 1sec (781 seconds)

Published: Fri Apr 01 2022