How to Import Dividend Data into Google Sheets! (Get Real Time Dividend Tracking!)

hey what is going on you guys in today's video we are going to be going over how to import dividend data into google sheets the google finance function provides a lot of tools to pull useful information on stocks but it unfortunately does not have the ability to pull dividends which is what we will be going over in today's video so that being said let's dive into the video okay so we can see here i have a set of companies and here i have the amount paid by at t in dividends and you can see the code that i used right here and this is a completely interactive formula so when i drag this down we can see google sheets will automatically pull the amount paid in dividends by each of these companies so let's go over exactly how to make this happen so i'm going to jump over to my walkthrough tab here and you can see we now have a list of dividend paying companies but we do not have the amount paid in dividends by these companies so here's what we're going to do we are actually going to have to web scrape this data from a website called finviz.com so i'm going to jump over to that website now and let's say that we want to look up the amount paid in dividends by at t we'll click at t right here [Music] and if we scroll down we can see here we have a table with a lot of financial data on a t but right here we can see the amount paid in dividends by a t and this is the data that we need to try to pull into google sheets and automate it so now let's go back to google sheets and talk about how to automatically pull this data in and we want to pull at t's dividend payout right here so what we'll do is we want to web scrape it from finviz.com and in order to do that we're going to use something called the import html command and you can see it listed right here this is the command we're going to use and the first thing we need to do in order to use this command is we will jump back over to finviz.com and we want to copy this url and paste it into our formula here so i'm going to add quotation marks and then paste this formula here and close it off with quotation marks as well and then i'll add a comma and the next thing we need to do is we need to indicate to google sheets if this is a table or a list and in this scenario we're trying to pull data from a table so i'll add quotation marks again and type out table and close it off with quotation marks as well and now in order to finish this we need one more thing so i'll add a comma and we need to indicate what table this is to google sheets it's typically going to be anywhere between 1 to 12 and the best way to try it is just trial and error and i've gone ahead and tried it and found that this table is table number eight so i will type out an 8 right here close off the parentheses and hit enter and when i hit enter you'll see that that entire table that we just looked at on finvis.com just got imported into google sheets which is a great start but we just want to pull the dividend data that's listed right here so we need to take this formula another step further so now we need to indicate to google sheets that we only want to pull the amount paid in dividends so what we're going to do is we're going to come back up to this cell right here where we want where we want to have the dividend listed and before import html what we are going to do is we are going to type out index and this is going to give us the option to automatically have the dividend alone pooled and so now that we have index listed here what we need to do is we need to jump back over to finviz.com and to tell it what that we just want to pull the dividend we need to figure out how far down the dividend is and how many rows over it is so what we'll do is we'll count it out so if we go down first one two three four five six seven so the dividend payout is seven down and it's one two over so let's jump back over to our formula and all we're going to do right here is we are going to put a comma 7 comma 2 and close off this parenthesis and hit enter and we can see here now we have the amount paid by a t and t in dividends but we can see here it's also pulling these two little asterisks right here and we want to only pull the amount paid in dividends so let's add to our formula so where it doesn't pull these asterisks so what we're going to do is we're going to jump up here before index and we are going to type out substitute and do an open parenthesis and this is going to allow us to remove those dots so we're going to come over here to the end now and i'm going to add a comma and i'm going to indicate it to it what i want it to remove so i want it to remove the asterisk sign and we're going to put that in quotations and then add a comma and then i need to indicate what i want to remove it with and i just want it to show up blank so i'm just going to leave it blank and add two quotations and we will close off this parentheses and hit enter and you can see now we have the amount paid in dividends without the asterisks included so now our final step is to automate the dividend web scraping process and you'll see if i were to drag this formula down right now it's still only showing the amount paid in dividends by at t what we want to happen is we want to show the amount paid in dividends to the corresponding company here on the left so i'm going to delete this so in order to do this we need to go back to our formula and we are going to actually reformat this url and you can see here right here there's a t listed in the url and this is referring to the stock ticker for a t which is the company we're looking at right now what we're going to do is we're going to delete this and close it off with quotations right here and we're going to add to this url and in order to do that we are going to use an and sign and then we want this url to look at whatever stock we have listed right here so we're gonna click on a tnt and you can see cell a3 that's the cell att is listed in so when i hit enter on this you can see it's still showing up at t's dividend but now when i drag down this formula we can now see the amount paid in dividends by each of these companies this is a completely interactive formula and it's the easiest and fastest way to pull the amount paid in dividends in google sheets so there you have it that's how you pull the amount paid in dividends in google sheets this is the same method that i have used to create some of my dividend portfolio trackers like the ones you see here so with that being said thank you guys so much for watching the video and please don't forget to like and subscribe to the channel

Channel: Dividendology

Views: 30,065

Keywords: dividend investing, google sheets, google sheets dividend tracker, dividend stocks, how to track my dividends, how to get dividend data in google sheets, how to make a dividend income tracker in google sheets, dividend tracker, google finance google sheets dividend, how to make a portfolio tracker in google sheets, dividend data in google sheets, track your dividend income in google sheets, how to get real time dividends in google sheets, how to get dividends in google sheets

Id: E34LtqFxiJM

Length: 6min 42sec (402 seconds)

Published: Fri Oct 01 2021