How To Pull Dividend Data Into Google Sheets

Подписаться 35 тыс.

Просмотров 103 тыс.

50% 1

How To Pull Dividend Data Into Google Sheets
📈 Link to download all my Portfolio Trackers: / thinkstocks
🌎 Follow me on Twitter: / keithfrislid
🎥 Here is the camera I use for my videos: amzn.to/2PB865Q
🔶Get 50% off Seeking Alpha Premium with my Promo Link!!!🔶
www.sahg6dtr.com/28KL61/R74QP/
In this video, I will show you how to pull live dividend data into your google sheets portfolio tracker. If you have messed around with google sheets you may already know about the google finance function. And if you know about that you also know you cannot pull dividend data from it which is the dumbest thing on Google's part. Like, come on Google. You are a multi-billion dollar company and you cant let us pull dividend data??
Anyway, there's a nice workaround to that by pulling from Finviz which I will show you how to in the first half of the video. But that will only get you so far. One thing Finviz does not have is the Ex-Dividend Date, which a lot of people want to know when that date it, so you know when they need to invest to get the next dividend payment. After a lot of research, I was able to find a way to pull the ex-dividend date so from a place outside of google finance and Finviz. Make sure to watch this video to find out!
**Finviz code update** In the video my I'm pulling from table 9, but now it is from Table 7.
- The codes below have been updated so you shouldn't have a problem
- for company name you need to pull from Google Finance now.
- The code for the company name is also updated below!
Pulling from Finviz:
=(substitute(index(IMPORTHTML("finviz.com/quote.ashx?t="&C6,"table", 7),8,2),"*",""))*1
Pulling the Ex-Dividend Date:
Watch the video to find out the secrete code ;)
Disclaimer: This is not financial advice. I am not a financial advisor. This video is meant for education and entertainment. Do not use this video as investing advice and consult with a professional advisor before buying or selling securities.
AFFILIATE DISCLOSURE: Some of the links on this channel and in video descriptions are affiliate links. At no additional cost to you, we receive a commission if a purchase is made after clicking the link.
#dividenddata #googlesheets #howtogooglesheets

Опубликовано:

24 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 348

@stevenfair3992 3 года назад

This is amazing. I’ve been trying to do this for months and was completely stuck on it. I can’t wait to incorporate this into my spreadsheet. Thank you.

@ThinkStocks 3 года назад

You're welcome!

@KaiRush 3 года назад

No...You rock!!! This was so easy. I read over 20 blogs and the steps were so out there. You made it so easy! Thank you and I hit "Subscribe."

@maggiekreuzkamp6193 3 года назад

you saved my life. I've been trying to figure this out all day and you helped me in 5 mins. You rock

@pressrepeat2000 3 года назад

Super useful. Much better than similar videos because you go through the formula so clearly. Subscribed!

@junabad3412 3 года назад

Super Cool! Just followed all your steps and got what i needed. More blessings to you.

@TradingWithCJ 3 года назад

Great tips man! Having it all in documentation is great to see and track those dividends!

@brucecampbell208 2 года назад

I cant thank you enough for making this video. I was trying to figure this out for many hours. 5 stars for your content.

@ionelmitel9835 Год назад

Thank you. I tried multiple things, but your solution is simple and works perfectly

@dentontay 3 года назад

Thanks, this was great! I saw your last video about pulling the dividend amount, and I couldn't get the formula to work. I used this one, adjusting row, column, and my personal spreadsheet cell, and it works great. Awesome!

@ThinkStocks 3 года назад

Thanks! And glad it works for you!

@sarubinca 8 месяцев назад

I copied and pasted your revised function. I get an error: "Function ERROR parameter 2 value is 8. Valid values are between 0 and 1 inclusive." Any correction to this?

@swedesam 2 года назад

Dude!!, this was epic and helpful beyond words. I'm working on a dividend stock screener/tracker and this is perfect.

@nikhiluthale6472 2 года назад

Thanks for the code. If anyone is getting a REF error, please update table number to 7 and the issue will be resolved. The table number is updated since this video was uploaded.

@JohnnyWalkerYT 2 года назад

thanks man, literally came back because of that

@levanberdzenishvili79 2 года назад

I am still getting error loading data...

@That1Fam 2 года назад

It says “parameter 2 value is 8. Valid values are between 0 and 2.”

@thecantstopwontstopentrepr4156 2 года назад

Table number is 9 now.

@petera4640 2 года назад

@@thecantstopwontstopentrepr4156 Legend!

@IbadassI 2 года назад

I wish I could like this 100 times! You've earned a subscription from me. I can put this into a dividend calendar and it will tell me where to concentrate my money every month so I maximise the dividend payout instead of spreading it everywhere at the same time. This will boost my dividends snowball! Thank you so so much!

@denisjose5993 Год назад

Thanks brother. You have made my life much much much easier now!!. Super love your video here. Thank you so much for the info and looking forward to seeing more of your videos here! Cheers

@joelpolese Год назад

Pretty useful! I was looking for this solutions for months...Thanks! Subscribed

@grezm0nkey 2 года назад

made personal sheet in 2015 and for the life of me couldnt accomplish or find solution till now, many many thx, hats off to you.....cheers

@hectormunoz4812 2 года назад

You went hard on this video and thanks for the updated code it was exactly what I needed. Thank you sir

@darkpaladin4917 2 года назад

you're awesome, huge thanks for all your hard work in sharing your wisdom

@gastonibanez7349 3 года назад

Thanks for the video. It took me some tries to make the code work, but I finally did it today.

@ThinkStocks 3 года назад

Thats awesome, glad it works for you!

@Thomas-il6el 3 года назад

Thanks a lot. I saw it on a blog, but with a vidéo, this is day better ! 👌🏻👍🏻

@craigschleppi2573 3 года назад

You are a god send. I have been looking for this information! Thanks

@broadwaygypsy3304 3 года назад

EXCELLENT THANKS ,INFORMATIVE AND ACTULLY WORKS, REAL JOY!!!

@akshatrastogi9063 2 года назад

Man u r genius, I have been looking for this forever!

@DH-pz7bc 2 года назад

Thanks for the video. Very helpful and easy to follow.

@ProdLeo 3 года назад

thank you so much! this was so helpful, cant thank you enough!

@rp19loring 3 года назад

This is very helpful, thank you!

@Zues64 3 года назад

This was helpful, thank you. Can you advise how i determine what the table number is if i choose a different html website ?

@aarthiarun9440 2 года назад

exactly what I needed. Thank u so much !

@itworkss Год назад

This is awesome man, thank you so much!

@kpwong1439 3 года назад

Amazing work! Thank you so much!

@ThinkStocks 3 года назад

You're welcome!

@vipinarora9 3 года назад

Hi, Just watched your video. This is amazing, you used hidden techniques to get this data. Thank you for making this video.

@ThinkStocks 3 года назад

My pleasure, glad you liked it!

@adamstruss7434 3 года назад

How do you know what number to plug in for the table that will be pulled from finviz? Not the rows and columns but the actual table. I'm also having an issue where it is telling me I can only use numbers 0 or 1 for rows and columns. Thanks so much!

@omarfuentes2856 Год назад

You are the man for my spreadsheet nightmare. Subscribed! Thanks and take care

@ThinkStocks Год назад

Thanks for the sub!

@angelogramatica9967 2 года назад

Wow! Awesome dude. Thanks a lot. Learned a lot again. Works not for all stocks but still a big help. Doesn't work for a stock like UBS f.e.

@debbieshapiro9779 9 месяцев назад

This is fantastic! Thank you! 🙂

@philiptran617 2 месяца назад

Thank you so much for posting this. This is very helpful. :)

@mariex800 3 года назад

Wow - that is so cool. Thank you.

@AndreaVaturi 3 года назад

Great job man 👌🏻👌🏻👌🏻

@Paddlehappy Год назад

Hi , thank you so much for this video much appreciated

2 года назад

thanks you, very useful material !!

@rhlogic 2 года назад

Wow! This is crazy brilliant!

@darkho0 2 года назад

Liked the video, will check out the rest of the channel. Thank you for the educational video.

@buffsurfer 2 года назад

You are a genius. I don't know how to thank you!

@kevinspaeth5895 2 года назад

Great video! Do you think you could reverse engineer this to pull the stocks that have an ex-dividend date within a certain date range?

@Pharaoo2012 2 года назад

Great explanation, thanks a lot. Can we do this with MSFT Excel?

@EricaDyson Год назад

You're so right. I just couldn't get the importxml function to work with Yahoo Finance data. A mega mega mega thanks.

@Mostafizur_rahman188 7 месяцев назад

Very helpful video, "Subscribed". Hoping to learn a lot from this channel.

@tamarcutler910 3 года назад

Great video. Thanx for the guidance. Gonna subscribe to the channel

@georgebruno9846 3 года назад

Thanks for the very valuable information. Is there any way formula for getting Dividend Payout Date.

@strowie1697 2 года назад

AYO NO CAP THIS WAS MAD HELPFUL, I ALWAYS WONDERED HOW PEOPLE GET THE DATE OMG TYSM TYSM TYSM TYS

@svbhave Год назад

As of April 21, 2023 it's Table 10.

@Mostafizur_rahman188 7 месяцев назад

Thank you

@erickkillian5590 Месяц назад

As of 6/20/2024, it’s still Table 10

@monroe1262 3 года назад

This is awesome!

@ThinkStocks 3 года назад

***FINVIZ UPDATE*** Hey everyone! Finviz likes to change around its backend code and the table we are pulling from will change every now and then. When I made this video the table we were pulling from was table 9. Now it changed to table 7! If your code doesn't work its because they changed around the tables, but it is usually a very easy fix!

@supersaiefull 3 года назад

Hi, how can we figure out the table number?

@cfii2fly 3 года назад

Yay having a hard time getting the correct table number

@thekid2003 3 года назад

I don't see how you can tell the table number for the chart? How do you know it is say table 7 vs table 8 or 1 for that matter, Thanks. Love the instruction!!

@ThinkStocks 3 года назад

Great question and unfortunately I don't have an answer to it. Not sure if anyone does yet! It's all trial and error! I've had several of my coder friends try to figure it out too and nothing. But its been table 8 for a while now so hopefully it stays that way!

@vortac2k9 3 года назад

Ok... yes, EX-Dividend date is the hardest one, and it became even harder for me...wonder if you have some code or formula to fix this? Here it is: Seems like the YAHOO Finance tables don’t follow a pattern in terms of the information available.... meaning that the same cell either has different data if dividend information is not available, so they fill it with other stats. As a result when I export to my sheet, some numbers are correct (ex. 44721), some are from other data (ex. 0.00634) and some give you and N/A error message because the cell has no data... The challenge is: how to filter ONLY the data that has numbers like 44127 (which will yield the correct date when converted) and just display a “-“ character for either an error message or any number not resembling 44286 for example I hope I didn’t confuse you too much, but your help would be much appreciated

@michaelcloresandersm.d.7050 2 года назад

Thanks Nikhil, that worked!

@yurathejeweler 2 года назад

Thank you! Thank you! Thank you!

@ironman9895 Год назад

Thank you so much for this

@SurferBum1002 2 года назад

Amazing. Thanks!

@jengs12 3 года назад

Excellent!

@hanshausen2676 3 года назад

This was very helpful, thanks. Just one question, How as the finviz table identified as 9? I don't see a numbering system. Your help is appreciated.

@ThinkStocks 3 года назад

Glad you liked it and currently finviz is pulling from table 8. They had a major update to their page a few months back but luckily it has been at table 8 for a while now. And honestly it was trial and error figuring out the table!

@arikissel9512 3 года назад

Hi, do you have a solution to not show any number (or just 0) when a stock (for example paypal) doesnt pay any dividends? It just shows the #value error and I cant seem to figure out how to fix it

@carlosfernandoam 3 года назад

Got a #REF error when trying it first, but I corrected it by typing 8 instead of 9 (the one that goes after "table", ). In case someone else had the same problem ;)

@carlosfernandoam 3 года назад

Talking about the first Dividend Yield formula.

@JohnnyWalkerYT 3 года назад

thanks man, got the same thing

@cheetah-virusxi7560 3 года назад

Thank you,was getting the same problem

@Razorhead12345 2 года назад

does it still run? can you copy the formula in here? for me it doesnt run with 9 and 8 and other numbers. TY

@justin3814 2 года назад

@@Razorhead12345 same Did u find the solution?

@KaiRush 3 года назад

I am teacher and I am telling you...you can teach! Keep making videos! Do you have any videos on how to do covered calls, if you already own 100 shares of a stock?

@ThinkStocks 3 года назад

Thanks! And not yet but thats a good idea for a video!

@ruddvann 3 года назад

Hi I cannot get the asterixis out of the way even with your modified code. Is there a fix to this. Thanks!

@1stFoxmovie 3 года назад

I have a problem with the exdate. Idk what I have done wrong but I am getting a "loading" error and no result....

@ruddvann 3 года назад

Also is there a way to do this for mutual funds for trackings sake

@danielporter4493 3 года назад

Hey bro, awesome videos. So my spreadsheet worked beautiful up until the other day, now it won't pull any data from Yahoo, displays N/A with an error that says " could not fetch url". All of my dividend paying stocks now have that error from Yahoo, so any other cells that reference those for calculations also don't work. Any ideas? Thank you

@theintelligentinvestor300 3 года назад

Hello my friend could you please explain me the first number that you have wrote after "table" ?? how do you know that this is the number to choose ? Thanks you so much

@jorgp.1966 3 года назад

have the same question

@jameswelker226 3 года назад

Thank you for your video, i have been using your videos to take my spreadsheet up a notch. I had a problem with the ex-div formula not working for my ETF's. Any ideas? Thanks.

@ThinkStocks 3 года назад

Glad you like them! And ETFs are just one of those tricky ones. Still trying to figure out the best formula for them

@jimshanks3541 Год назад

@@ThinkStocks Anything new on getting rid of 12/30/1899?

@alphabeta01 2 года назад

Fantastic video. I tried and all these work great. Only issue is that it takes a long time to load the data esp for symbols like AAPL. JNJ, GSK and ARKK. Thanks.

@Steve_Gouws 2 года назад

Thanks for this. I’m trying to pull divided payments date into Google sheets. Any idea what that formula would be? Thanks

@giandigiandomenico9179 3 года назад

Good, but Finviz doesn't have a complete listing of dividend paying stocks on the TSX. Could you suggest another screener that pull dividend data from the TSX please?

@Fadliadiputra 2 года назад

Hello have you found out another website that can use this method?

@PacPacBumGaming 2 года назад

Great video! But I encounter a problem when trying to fetch data for a quote like RNO(dot)PA for example(on yahoo). It seems to be the dot before PA that cripples the Google Sheets. If you find any way around it, please do let me know! Thanks again for the video!

@BetasCoin Год назад

Instead of pulling data using htmlimport, then you can pull all data on Yahoo Finance using the fact that it is build on Javascript. I just made a video showcasing my version of this if you need. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MAi62N3V1H4.html

@verywai 2 года назад

Thank you, nice

@JasonAndrewReid 3 года назад

Thanks 🙏🏻

@ThinkStocks 3 года назад

You're welcome!

@lmao9222 3 года назад

Ex-dividend only appeared in the cell i put the formula in for that stock but not the others. How could i fix this?

@investinggrind 8 месяцев назад

Hey man, this was a great video. Can you also make a video on pay dates? Thanks so much man

@lance4u19 2 года назад

Hi mate, finviz doesn't show Canadian stocks, do you have any suggestions for inputting dividends data for Canadian stocks? Much appreciate!

@BetasCoin Год назад

Instead of pulling data htmlimport, then you can pull all data on Yahoo Finance using the fact that it is build on Javascript. I just made a video showcasing my version of this if you need. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-MAi62N3V1H4.html

@tmmedia1000 2 года назад

How do you know its table 9? I'm trying to apply this to another site with a table, but have to find out which table I'm looking at, thanks.

@SuperKevinFS 3 года назад

excellent content

@ThinkStocks 3 года назад

Thanks!

@Babymem15 Год назад

How do you know that it's table 9 on finviz? What if they change their site up? How would we find that?

@cbg1992 2 года назад

thanks so much

@AutomationMaestro 3 года назад

THe Ex div date from Yahoo isn't working for me. Could you please place the formula in the comments?

@charenk 3 года назад

when I tried to create dividend yield, I am getting "#REF!" error with text "Function INDEX parameter 3 value is 2. Valid values are between 0 and 1 inclusive." Am I missing something here

@israelschurek9046 3 года назад

Thank you. Great! Any idea how to calculate how many days left to Ex-dividend day? I tried to substract from the result I get "now()", but I get an error, indicating that what i get from Yahoo is a text.

@ThinkStocks 3 года назад

Hmm thats a good one, ill have to look into that and see if I can come up with anything!

@nwgguy1070 2 года назад

Easiest way - use another cell, with the formula =E4-today() ~~ (E4 is the cell shown above, with the Ex-Div date from the tutorial).

@KeithBandell Год назад

I have been trying to figure out how to auto-populate dividend info into google sheets for a while. I tried the formula you listed above, but was not successful. Do you know if this formula is still current in January, 2023, or has something changed again like the table number?

@krystalyan4264 Год назад

Any idea on a data source for ASX stocks? Thank you!

@ryanparr516 2 года назад

can you pull free cash flow from yahoo? or is this only able to be pulled from the first page? Thanks for your help

@fitbh703 2 года назад

exactly what i was looking for (ex dividend date)

@NaveedAhmad-zg3rd 3 года назад

Hi Mate, I am having this error message each time I load the code. I have checked everything but nothing. Can you please help? Error Function MULTIPLY parameter 1 expects number values. But '-' is a text and cannot be coerced to a number

@callumhewson8514 7 месяцев назад

I keep getting an ERROR message :(( it says = "Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive"

@mrironleg 3 года назад

I need to import dividend yield data for Canadian securities also; any ideas?

@eliashanna2436 3 года назад

Same here

@gurnovs 3 года назад

Hi Guys, I have issues pulling stock info from Yahoo for alternative stock exchange, London or others. I'm trying to pull data for TCS.IL, TCSA.L or even AAPL.BA and formula returns #N/A. Any thoughts?

@jg5479 Год назад

Why do I keep getting n/a or resource at url not found when trying to change the dynamic cell from where I input to ticker after changing the url for the cell where I input the ticker?

@jorgebaia865 3 года назад

In the formula you provide to Copy the sell is not the correct one "C6" intead off "A2" Great job

@nodems613 Год назад

do you have an update for the Ex-Dividend Date line getting NA

@nedh.8792 Год назад

Great content. Subscribed! FYI I'm getting the following error message when trying to import this data into Google sheets - "Function INDEX parameter 2 value is 8. Valid values are between 0 and 3 inclusive." Any suggestions on how to fix this? Thanks.

@bigc716 Год назад

I changed the table number back to 9. worked for me.

@lihk9412 2 года назад

Thanks, but the dividend is the total annual dividend, not the exact dividend in the Ex-dividend date ? how it can be resolved?

@swarleycx1406 3 года назад

This just does not seem to work for me, I always get an error, even if I copy the code from your description and change the C6 to my own one. Or if I delete the &C6 and just put the ticket directly into the parentheses. Always an error. Any suggestions ?