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.
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?
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.
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 ;)
***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!
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!!
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!
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
My didn't work and I needed to put table "10" instead of ""9" Maybe they updated a web site with added table on top. Thanks a LOT!!!!!!!!!!!!!!!!!!!!!!
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!
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!
Good work! You mentioned payday, whereas the Ex-dividend date is not actually the pay date, it is the date you have to be invested in the underlying, to get paid on the actual pay date. Just to clarify. Thanks for your work! I guess the tables have been changed again as the link no longer works. in native Windows, the options for selecting tables is available, but not in the Mac version 365, much to my dismay.
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?
As someone pointed out to get the dividend payout you now use table 10 instead of nine. Now the dividend date on yahoo has changed, has anyone figured this out yet? Thanks
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?
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
Another enhancement we can do in this, is to multiply the shares with dividend amount when it is declared and log the date and dividend amount in history tab for the shares I currently hold and add it to the dashboard as dividend received. This thing no one has done it yet, so I'll be working on it and let you know if got success. Btw thanks for this, now I have something which can be improved further.
Has anyone recently dont this? The issue i have with pully from finvis is that the yield pct is importing as text with both the yeld pct and payout dollar ammount. For instance VZ shows as 2.63 (6.29%) anyone know how to deal with that?
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!
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
Any update toyour revised function. Get the same error as below: Error Function INDEX parameter 2 value is 8. Valid values are between 0 and 1 inclusive.
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
Ex-dividend date formula =index(importhtml(Concatenate("YAHOO WEB ADDRESS",A2),"table",2),7,2) you'll need to replace YAHOO WEB ADDRESS with what's shown at 9:36
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
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.
finviz has changed the table number again - its now 10 instead of 9. I'm still trying to figure out where yahoofinance has hidden their table though 😕 hope this helps you some. -FC
@@nuthingoinon Got things to work with FINVIZ #10 but they don't have the XDATE in their sheet. Can't seem to find the key for the Yahoo Finance transfer for Xdate
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.
Hi., this is a great Material to world of investing.. can you please tell how to pull other than US Stocks for example Indian NSE stock market.. I tried for ACC.NS, but it turned out to be #N/A whereas it returns value for AAPL.. can you please enlighten?
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?
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!
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. ua-cam.com/video/MAi62N3V1H4/v-deo.html
@@randolfeo9277 just to clarify, I am using TGT at initial cell A10 hence the specific numbers of * 4.17 (2.52%) * ( there is no space between the * and 4 or ) and * )
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
Great video. showing me how to auto extract the ex-dividend date. Please show the code for extracting the dividend.and the dividend yield? For example Yahoo Finance shows : Forward Dividend & Yield 1.92 (15.74%) for stock OCR.
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?
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 ?
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.
thanks man, literally came back because of that
I am still getting error loading data...
It says “parameter 2 value is 8. Valid values are between 0 and 2.”
Table number is 9 now.
@@thecantstopwontstopentrepr4156 Legend!
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?
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."
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.
You're welcome!
As of April 21, 2023 it's Table 10.
Thank you
As of 6/20/2024, it’s still Table 10
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
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 ;)
Talking about the first Dividend Yield formula.
thanks man, got the same thing
Thank you,was getting the same problem
does it still run? can you copy the formula in here? for me it doesnt run with 9 and 8 and other numbers. TY
@@Razorhead12345 same
Did u find the solution?
***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!
Hi, how can we figure out the table number?
Yay having a hard time getting the correct table number
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!!
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!
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
Great tips man! Having it all in documentation is great to see and track those dividends!
you saved my life. I've been trying to figure this out all day and you helped me in 5 mins. You rock
My didn't work and I needed to put table "10" instead of ""9" Maybe they updated a web site with added table on top. Thanks a LOT!!!!!!!!!!!!!!!!!!!!!!
THIS! Thank you sooo much!
Yeah this fixed it for me, thanks!! Anyone know how to check the table location in the future in case it moves again?
Super useful. Much better than similar videos because you go through the formula so clearly. Subscribed!
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!
Thanks! And glad it works for you!
I keep getting an ERROR message :(( it says = "Function INDEX parameter 2 value is 7. Valid values are between 0 and 1 inclusive"
This was helpful, thank you. Can you advise how i determine what the table number is if i choose a different html website ?
unable to get data from yahoo finance, can you help me in this regard
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!
I cant thank you enough for making this video. I was trying to figure this out for many hours. 5 stars for your content.
Good work!
You mentioned payday, whereas the Ex-dividend date is not actually the pay date, it is the date you have to be invested in the underlying, to get paid on the actual pay date. Just to clarify.
Thanks for your work!
I guess the tables have been changed again as the link no longer works. in native Windows, the options for selecting tables is available, but not in the Mac version 365, much to my dismay.
Thank you. I tried multiple things, but your solution is simple and works perfectly
Super Cool! Just followed all your steps and got what i needed. More blessings to you.
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?
Thanks! And not yet but thats a good idea for a video!
As someone pointed out to get the dividend payout you now use table 10 instead of nine.
Now the dividend date on yahoo has changed, has anyone figured this out yet?
Thanks
How do you know that it's table 9 on finviz? What if they change their site up? How would we find that?
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?
Hello have you found out another website that can use this method?
Thanks a lot. I saw it on a blog, but with a vidéo, this is day better ! 👌🏻👍🏻
I have a problem with the exdate. Idk what I have done wrong but I am getting a "loading" error and no result....
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
Thanks for the very valuable information. Is there any way formula for getting Dividend Payout Date.
Thanks for the video. It took me some tries to make the code work, but I finally did it today.
Thats awesome, glad it works for you!
AYO NO CAP THIS WAS MAD HELPFUL, I ALWAYS WONDERED HOW PEOPLE GET THE DATE OMG TYSM TYSM TYSM TYS
Dude!!, this was epic and helpful beyond words. I'm working on a dividend stock screener/tracker and this is perfect.
You're so right. I just couldn't get the importxml function to work with Yahoo Finance data. A mega mega mega thanks.
Another enhancement we can do in this, is to multiply the shares with dividend amount when it is declared and log the date and dividend amount in history tab for the shares I currently hold and add it to the dashboard as dividend received.
This thing no one has done it yet, so I'll be working on it and let you know if got success.
Btw thanks for this, now I have something which can be improved further.
hey! Any progress on that? I was thinking the same.
Has anyone recently dont this? The issue i have with pully from finvis is that the yield pct is importing as text with both the yeld pct and payout dollar ammount. For instance VZ shows as 2.63 (6.29%) anyone know how to deal with that?
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!
Pretty useful! I was looking for this solutions for months...Thanks! Subscribed
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
THe Ex div date from Yahoo isn't working for me. Could you please place the formula in the comments?
Any update toyour revised function. Get the same error as below: Error
Function INDEX parameter 2 value is 8. Valid values are between 0 and 1 inclusive.
Man u r genius, I have been looking for this forever!
hey i kowla
iam late to the game you might have already figured it out but i used the importxml formu
You went hard on this video and thanks for the updated code it was exactly what I needed. Thank you sir
You are the man for my spreadsheet nightmare. Subscribed! Thanks and take care
Thanks for the sub!
Hi, Just watched your video. This is amazing, you used hidden techniques to get this data. Thank you for making this video.
My pleasure, glad you liked it!
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
have the same question
Aren´t Ex-Dividend Date and Pay date still two seperate things?
Yes ones when you have to own the stocks by and the other is when you get money for owning that stocks.
Ex-dividend date formula
=index(importhtml(Concatenate("YAHOO WEB ADDRESS",A2),"table",2),7,2)
you'll need to replace YAHOO WEB ADDRESS with what's shown at 9:36
it doesn't work
@@northinvestor well you'll just need to copy it from the video then
@@EpicTalesHub-d7z I've just spotted a typo in my original post, I had B2 where it should be A2 so formula was looking at wrong cell to get ticker
you're awesome, huge thanks for all your hard work in sharing your wisdom
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
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.
it doesn't work with me
I don't know why!
Change the 9 at the end to a 10
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.
I changed the table number back to 9. worked for me.
Followed instructions exactly but I receive the following errors - "#REF! #ERROR! #ERROR! #ERROR!" Looking for solutions please
finviz has changed the table number again - its now 10 instead of 9. I'm still trying to figure out where yahoofinance has hidden their table though 😕 hope this helps you some. -FC
@@nuthingoinon Got things to work with FINVIZ #10 but they don't have the XDATE in their sheet. Can't seem to find the key for the Yahoo Finance transfer for Xdate
You are a god send. I have been looking for this information! Thanks
In the formula you provide to Copy the sell is not the correct one "C6" intead off "A2"
Great job
Thanks for the video. Very helpful and easy to follow.
I need to import dividend yield data for Canadian securities also; any ideas?
Same here
Not working anymore
It's table 10 now (as of July 2023) 👍
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.
Thanks for this. I’m trying to pull divided payments date into Google sheets. Any idea what that formula would be? Thanks
thank you so much! this was so helpful, cant thank you enough!
How do you know the "table #" is?
Great explanation, thanks a lot. Can we do this with MSFT Excel?
Hi., this is a great Material to world of investing.. can you please tell how to pull other than US Stocks for example Indian NSE stock market.. I tried for ACC.NS, but it turned out to be #N/A whereas it returns value for AAPL.. can you please enlighten?
Very helpful video, "Subscribed". Hoping to learn a lot from this channel.
A ver si puedes actualizarlo a como es ahora, sería muy útil, a mi no me funciona, pero ví que tu video es muy bueno
Good stuff but Not working anymore...any updates?
EXCELLENT THANKS ,INFORMATIVE AND ACTULLY WORKS, REAL JOY!!!
You are a genius. I don't know how to thank you!
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?
Thank you so much for posting this. This is very helpful. :)
I tried the function you posted in your description but I keep getting the error index 2 parameter value is 8, valid values are 0 and 1
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.
Yahoo Finance function as shown in above video is Returning error for table, 2. Says, "Resource at url not found".
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!
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. ua-cam.com/video/MAi62N3V1H4/v-deo.html
Hey man, this was a great video. Can you also make a video on pay dates? Thanks so much man
exactly what i was looking for (ex dividend date)
this won't work anymore, since the table got updated in finviz. Now it displays both dividend and dividend yield in the same cell of the table.
You can use LEFT and RIGHT to trim the result.
Delete *1 at the end of the formula. If it's still causing an error, then trim whitespace (data, data cleanup, trim whitespace).
@@randolfeo9277 just to clarify, I am using TGT at initial cell A10 hence the specific numbers of * 4.17 (2.52%) * ( there is no space between the * and 4 or ) and * )
exactly what I needed. Thank u so much !
Thanks, but the dividend is the total annual dividend, not the exact dividend in the Ex-dividend date ? how it can be resolved?
Any suggestions on how you found the 'table number'? You said - this is table 9 but not sure how you figured that out.
Any update for what table is for the ex dividend date???
does the link still work?
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
Great video. showing me how to auto extract the ex-dividend date. Please show the code for extracting the dividend.and the dividend yield? For example Yahoo Finance shows : Forward Dividend & Yield 1.92 (15.74%) for stock OCR.
Seems not working for LSE. Strange enough it works for SHEL.L and VOD.L but not for the others . It get stuck in loading or #n/A
Any help?
Cheers
Just watched your video on the ex-dividend date. Can you do the same video for Canadian viewers. Thans man
Ex-dividend only appeared in the cell i put the formula in for that stock but not the others. How could i fix this?
Would like to pull dividend PAY date as well. Thanks.
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?
Liked the video, will check out the rest of the channel. Thank you for the educational video.
I want to sum my dividends from multiple stocks. Some stocks don't have dividends and it return #value. How can i replace the #value with a zero?
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 ?
Hi , thank you so much for this video much appreciated
Hmm... doesnt work :-/ I just copied yout code but nothing works. Is it changed by google last times?
do you have an update for the Ex-Dividend Date line getting NA
Any idea on a data source for ASX stocks? Thank you!
great vid. Yahoo doesn't post ex div for ETFS
The table index was 9, then 8 and now it's 7
This should be pinned