How to get Yahoo Finance Data into Google Sheets in 2024

so this is a fault video to one I did about how to get Yahoo finance data in Google Sheets uh I made this video about five years ago and looking at a lot of the comments it's saying that it's not working anymore or that they're getting na and resource unfound uh if I go to the template so again always templates are in the description they're free you don't have to email me you just click on the link uh if I go to it you can see yeah definitely getting Na's resource not found uh I'm using the import XML function to do this we were looking at the key statistics page in Yahoo uh if I go to that key statistics page you could see it's definitely changed uh so here's how it looked 5 years ago you can see everything's pretty much there there's no locks behind anything uh and now you can see that there's locks behind the page uh key information is Now hidden uh they give you more but you have to pay for it uh and I know that this page is being generated by JavaScript and with these Google functions they've been kind of nerfed in a way um so they can't really get anything from uh JavaScript pages so I tried to find other videos more recent videos uh that try to handle this issue this one is only a year year ago uh pretty much the same exact title that I had how to get Yahoo finance data but it uses the same exact method I used five years ago import XML uh using the same exact thing um and you could see that it doesn't work anymore either and we know it doesn't work because we did it 5 years ago and it doesn't work here I did try other methods but strictly staying within Google Sheets using Google Sheets functions and not importing other like tools or add-ons or adding any other programming language just to not complicate things or open you up to risk and it just doesn't work so you're you're not able to get this stuff that's in the key statistic page without paying for it for from Yahoo or signing up I don't I don't know what the locks are I did try to look at other sheets to see or tabs to see if there's anything on those tabs that might not be generated with JavaScript and uh that isn't already available in using the Yahoo or Google Finance function so I started with the summary page and uh just to check some of the summary information that they had anything of Interest here and yeah they have um the dividend I know I use the dividend a lot and we used to get it from the NASDAQ until they went to JavaScript as well so I tried pulling it from there um and the method I used assuming this was a table there's a lot of import functions I know this one doesn't work this one doesn't work so I tried to import HTML and yeah it seemed to work so that's what this is here I'm using the import HTML function generating the URL dynamically so I can check it and pretty much following the same structure as before importing the headers and the row and then importing a line item for that particular one without the headers so there's two functions working here one function to pull the header and the row data and then another function just to pull the row um so you don't have to repeat the header so that way you can have multiple stocks uh and you know if you want to compare multiples together so import HTML it uses a URL so in this case getting the one from Yahoo finance and then the query here is you only have two options either a list or table uh I assumed it was a table and then you have an index index is pretty much if there's 10 tables or 10 lists on a web page um you put in the number you want one two to whatever so in this case we'll just show what one gets you so if I go back here You' see that this is previous close previous close so it's pretty much just this half of the table and then there's another table here so if I click that's what number two is that's the one I wanted um to be honest though I even though I thought this was a table if I go to inspect and if I try to find the table tag uh I don't I don't get what I was expecting I thought this would be wrap up in a table but it's not um so honestly I don't know how this import HTML function is working to recognize that it's a table or how it's rendering the sheet but it seems to work so I'm going to use it and share it with you guys so if I click table two you could see that you know you're getting this other half here the market cap and the part with the dividend since I'm interested in the dividend and they get it in the format of of a row you could use the transpose just like before and then that gives you a nice row format this function here uh to get it without the header um that's where the index function comes into play so let me just show you what the index function does it pretty much takes a reference and then you can specify whether you want rows or columns here you want all the rows but you just want the second half which is the column with the data so you just put number two that'll be the column with the data so yeah you can see that works out and then you can do the same thing so if I put number one as the column you can see I'm getting the headers so that's what in index does so combining those two import uh HTML to get the data index to get the values and then transpose now you have just a line item itself so that's what this function does here and that's pretty much it so just a couple caveats um this is created dynamically based off of this because remember that Yahoo and Google had have different tickers sometimes the ticker in Google doesn't work in Yahoo um or isn't in the HTML or in the URL for Yahoo so just be mindful of that uh and the reason I did it here is so they can check to see if the the one that works in Google is the same exact one that works in Yahoo if it doesn't then it will just give you like a blank page saying that I can't find it you can see here for the dividend that it's a mix of two pieces of information so you can use the Rex function here to extract out the value U so then you can perform calculations on it so what this does is it looks at the cell and then has this Rex pattern so this says look at all the digits look at all the non-digits and then look at the digits again so that gets the three period in the five in the 0 five and then then it calculate then it turns in the string so Rex Returns the string and then it turns it into a value so you can use it um as a number this is the Google Finance function so it gives you the current price whatever the current price is for the ticker and then this just calculates the current yield so it takes the forward um dividend and then divides it by the uh current price so you get the yield so you can see it's a different number here because this isn't automatically updated but this is updated I think every 20 minutes or so uh like it's saying here it's a delay of 20 minutes so if you wanted to add other stocks so for example Microsoft I think it's I think that's right um You just copy this line these two lines and that'll give you everything for Microsoft and then you could just copy this stuff if you wanted to I have this as optional you don't need to use it if you want if you don't find it useful uh and then you can see the yield here now if you wanted other pieces of information say say you wanted the bid and ask since that isn't in the um Google Finance function either uh you could do the same thing um so for example you could take uh let's see import HTML you can do the same thing look at the URL get the table table and get the first one right that gives you everything and if you just wanted the bid and ask um you could do use the index function again so you can use the index so let's first say this is one two 3 this is the third row uh and this is the fourth row so you'd use the index Row three and you'd want column two for the value itself and you could just label it bid and then you could say ask and do the same thing um I just you can copy it if you wanted to and that'll be row four now you have to p and ask without getting the header and just drag it down and then you have everything so you could do it that way too if you just want like specific pieces of information and not having to uh you know pull everything say if you you could do that with a dividend you could do with a four dividend uh if you didn't want to get all this information this is the solution I have it's not the best but I think it could be useful if you're like into a dividends or if you want to track um bid and asks volume and uh the earnings or not the earnings the oneyear estimate uh if it doesn't have a dividend like Google for example uh it would just give you na and I have an error function here well one you don't have to copy it down or two um the error function will just give zeros if there's any error so these will return error that's the option I have uh hopefully it helps it's again it's not as comprehensive as it was before but I think it's something and yeah so thanks guys for watching and I really appreciate uh all the likes in the comments it it really helps

Channel: danesh j

Views: 2,911

Keywords: yahoo finance, googlesheets, dividend investing, importhtml, importxml, web scrapping googlesheets, stock portfolio, google, google finance trading, yahoo finance api, portfolio tracker, google sheets, yahoo finance in google sheets, excel stocks, robinhood, reddit, how to get yahoo finance data, retire early, coastfire, dividend tracking

Id: -hexF6hSBRo

Length: 10min 26sec (626 seconds)

Published: Wed Feb 28 2024