How To Pull Dividend Data Into Google Sheets

what is going on guys i finally figured it out i don't know if any of you have been having this problem but i feel like one of the most searched things on youtube when it comes to google sheets and the stock market is how to import your dividend data you have a stock you want to know your dividend yield dividend payout dividend ratio and most importantly when payday is and so many people have no clue how to get your ex dividend date this stuff also isn't the easiest but you could pretty much figure it out but here we go watch this let's there's apple right there let's type in target as you see everything changes look at this everything's loading especially the ex dividend date this is one of the hardest ones it took me a wait a while to figure this out but i finally figured it out i'm going to break it all down how i made this so you could implement it into your portfolio trackers but before that make sure to smash that like button subscribe to this channel i work hard to make awesome things like this for you guys all i ask for in return is a like and a subscribe let's dive right in so again this could be for anything if you have some kind of portfolio tracker and you just want to know how to pull this information well it is very simple we're going to make this right below right here so we got our stock ticker and then i'm just going to copy and paste these so i'm going to hit copy i'm going to do control shift v there we go so the first thing is dividend yield how do we get dividend yield well i am pulling it from finviz stock screener well i guess finvez not the stock screener just finviz in general so we have target open let's go up here to tgt for target we're pulling it from this chart right here if you have watched my videos before you already know how to pull from this chart but let's just go over it so what we're going to be doing is we're going to hit equals and we're going to hit index okay so that's what we're going to be using for start then i'm going to hit open parentheses import html there's yes import html there's xtml html html is pulling from a chart and that's what we want then what we want to do is take the actual web browser so we're going to open quotations we're going to go here i'm going to copy finvis.com all of this all the way before the target from the h to the equals hit control c then plug that into here control v and at that point what you want to do is close that quotation now the next thing we need to do is hit the ampersand so control seven and what we want is we want right here a2 so you can click on a2 it plugs it in make sure that text is orange hit that comma and we are pulling like i said from a table so we want to do in quotations table so it's green hit that comma as well and now what we want is our dividend yield so where are we pulling that from well we're actually pulling that from table 9 on the website that's that big table on the bottom if we go back here where is our dividend yield well it's right over here dividend yield one point six seven percent so that's what we're going to want so what is that if we we could actually count it is one well one two three four five six seven eight it's eight down and then it's two over so we want eight and two so we go back here redo eight comma two and then we close our quotations i mean our parentheses hit enter we get an error let's see what i messed up on so we just look at this really quick i forgot a comma right here hit enter that should work it's loading there we go 1.67 now you might be thinking what's up with the asterisks why do i have that that could also be annoying if you want to try to add that so say you had the 1.67 and you had whatever five five shares you you can't multiply that you can't take this you can't hit multiply and that you're gonna get a value error because this isn't a number so how do we fix that it's actually really simple and again if you've watched my videos you already know this i put in just about everything what we want is substitute so in front of this we're going to type in substitute we then want to cl open the uh quotation again and then all the way at the end what we want to do is comma open quotations star close quotations comma quotation quotation so what the saying substitute star for nothing close and then we want to close that parenthesis again because we have our parentheses at the very beginning right and what we want to do now is multiply it by one just so we are sure it turns it into an actual value and there we go with this we now can hit the um parentheses and we're good that is the first step that's complete you can copy this formula it's in the description um because now for the next two we just get to do some copy and paste so it's going to go along a lot quicker but the x dividend date we're going to have to pull it from a different thing we're going to have to learn a new code so make sure to watch it to the very end we're going to paste that in there i'm going to paste this in there because we already have both annual payout so annual payouts easy because it's right above the dividend payout the dividend yield so our annual payout is 2.72 so where are we getting that from well instead of eight it's gonna be seven hit enter there we go you wanna make that a dollar sign go for it let's center that and now our payout ratio where's that it is right over here payout 37.7 and that's going to be 11 down and 8 over so now we know we got to plug in 11 and 8. okay so now we have all that we'll make that a percentage we'll center it and now the last thing is our ex dividend date this is the hardest one or at least it was the hardest one for me and if you look around the internet no one knows how to do it on youtube um i figured it out maybe you do know how to do it maybe you've seen someone do it before but i sure as hell had to dig around so we're gonna be pulling from yahoo finance and now you may have seen plenty of videos if we zoom out here to 100 you may have seen plenty of videos where they're doing the whole equals import html import html open uh parentheses they're going to yahoo and what they are doing is here let's just type in say apple they're copying all of this putting that in so you're only pulling apple and you're getting like the table view and it can't be dynamic because it's not like there's a cell here so it's not actually hooked up to any cell i'm not going to get into all of it but like it just it doesn't work it doesn't make it into a nice portfolio tracker where everything can be dynamic and connected to each other well here is the actual answer what we're going to be doing is i'm going to hit equals the first step is co uh index and then what we want to do is open our quotations we want to do import html so import html open again now here's the secret it's called concatenate so that is c o n a t e n a t e open parentheses again this is where we're gonna put in our yahoo finance our https yahoo finance all the way up to quote all right and make sure to get the uh forward slash as well so we're going to put that into our code and then we want to close our quotation hit comma pick the cell we want which is a2 then we want to close our parentheses hit comma again we're pulling from a table so we want to do table close our quotation make sure table is green and on yahoo finance we want to pull right here here's the x dividend date so there's actually two tables here so we want to pull from the second table and this is going to be two over so one two and then seven down so what we want is like i said table two close our quotations i mean our parentheses we want to make sure we have the comma do 7 comma 2 close our parentheses and if we hit enter we get a number so you know it works but clearly this is not a dividend date this is not an actual date so all you have to do you didn't screw up you just need to go here and you need to go down to date change that cell to a date and there you go let's center that and just to make sure everything works just to test everything if we're changing this all of these are connected up here i forgot to put it in down here so we're just going to delete that uh this is just to check everything target let's type in apple see if everything changes everything's loading there you go look at that everything changes we get our ex dividend dates what's another uh sock how about microsoft that is a dividend paying stock there it is everything is in line with what we want it to be one more there's verizon loading and there we go everything is in line that is your x dividend date all these codes are down below you got payout annual payout and your dividend yield are all from finvez this one's from yahoo finance if you like this video guys please smash that like button subscribe if you haven't already check out all the other awesome videos on my channel you guys rock and as always i will see you guys in the next one

Channel: Think Stocks

Views: 33,578

Keywords: think stocks, keith frislid, how to pull dividend data into google sheets, dividend data in google sheets, how to get dividend data in google sheets, google sheets dividend tracker, google finance google sheets dividend, stock dividends in google sheets, get stock dividends in google sheets, ex-dividend date in google sheets, pull ex-dividend date into google sheets, dividend yield in google sheets, google finance functions in google sheets, dividend data from finviz, yahoo finan

Id: uZY-AeZex2g

Length: 12min 13sec (733 seconds)

Published: Fri Nov 20 2020