UPDATE -- Check out my recent release of my new 2.0 spreadsheet portfolio tracker product! ua-cam.com/video/qAV8D5r1I_M/v-deo.html Very very cool! Don't forget to SUBSCRIBE and LIKE this video as a simple way to thank me. It really helps the channel ! Thanks, I appreciate it! Support me & get Patreon perks ➜ www.patreon.com/genexdividendinvestor Please join my free investing Dividend Discord chat server with over 2500 dividend investors on it: discord.gg/kkSr5FY Also, I forgot to show how you can add logo's in this video - the best way to add a logo into a spreadsheet cell is go into the cell, and insert image then image in cell, google image search, and search for company logo. Click the one you want and then click insert. Google has some timeout bugs where it can cause issues sometimes if you are scraping too many websites or if they have timeout issues which can lead to display issues. Hopefully Google will resolve this! Also, Gurufocus changed their site so you can't scrape CAGR from it, but I list the new formula in the FAQ document on my Discord (as its the easiest centralized way I can update the info).
@@jerijenee You could use =index(importhtml("finviz.com/quote.ashx?t=jnj", "table", 11),11,8) Though, the more you scrape HTML, the more issues Google unfortunately has. I bet they will resolve this issue someday, but its annoying.
@@GenExDividendInvestor Thanks but new problem, I had to change it to look up Dividend 3.80 on the table which worked but it puts (*)3.80(*) in the cell and unable to change it to $ amount and throws off the rest of the data cells. Still playing with it
@@osros Hmm I'm not sure I see why it would do that. If you want to share your spreadsheet with me with edit rights then I can take a look.. or explain it more... #Partner22
@GenExDividendInvestor, great content here. I am not having success pulling the 3 yr CAGR from gurufocus. My my command is =abs(index(IMPORTHTML(CONCATENATE("www:gurufocus.com/stock/", C3, "/summary"),"table", 7),4,2))/100 and is returning #NA ( with the hovering message reading, " could not fetch www:gurufocus.com/stock/", C3, "/summary". Any thoughts? Any other sites you use to pull a 5 or 10 year div CAGR? #partner18
@@GenExDividendInvestor , great content here. I am not having success pulling the 3 yr CAGR from gurufocus. My my command is =abs(index(IMPORTHTML(CONCATENATE("www:gurufocus.com/stock/", C3, "/summary"),"table", 7),4,2))/100 and is returning #NA ( with the hovering message reading, " could not fetch www:gurufocus.com/stock/", C3, "/summary". Any thoughts? Any other sites you use to pull a 5 or 10 year div CAGR? #partner18
I'm subbing, sharing and liking because you were unselfish and shared this with us, not trying to sell it like everyone else. You will blow this channel up fo sho in due time little brother!!!
Yours is the best spreadsheet tutorial. Clear, accurate, etc..others don't specify how they create the formulas, or the formulas are two long without explanation. You are talented, almost an intellectual artist for the dividend enthusiast.
Thanks! On my Discord I created a FAQ and at the bottom of it I have a link to a google doc that has all the formulas folks can cut and paste if they want.
@@GenExDividendInvestor Yes, I signed up for that and have to do that...my sheet has gone to hell with companies dropping out the tracking ability. Yours is the only cogent and clear tutorial....I hate to lose my sheet
@@edwardmcdonald8194 Yeah that's part of why I ended up making a spreadsheet as a tool I sell as a service is because there were some limitations with google...
It's probably sad how blown away and in awe I was of this. I'm so thankful for people out there who are smarter or more talented than me at these things. Thank you thank you thank you!
I was looking through your old videos and saw this one. Very informative and easy to use guide. I started in July with your referral link on M1. Added about a thousand dollars to it and kind of played with the account till around September. Then decided to get more serious about investing into it. I've been adding 900 to 1000 a month since. I'm up to 7000, nothing extravagantly huge, but I've set up a plan. In two years I'm going to try to add 2000 a month. Somewhere in 2026 I figure to have 100k, and 2036 looking to be 500k. Those may be lofty goals, but I see that dividend snowball rolling hard. lol Thanks for all your videos. Dwayne
Ahh cool and thanks! The hardest part is starting and the next hardest part will be not quitting during times of fear. Keep investing in good assets and over the long run I think you will be blown away by where you end up.
This was a great step by step.. My only issue was manipulating the CAGR field. I figured that the chart number he was using to pull data from was 7 and mine was 8. worked perfectly!
Thanks, Andres, very nice of you to say that :) You should jump on my Discord -- we have lots of investors from all around the world on it :) discord.gg/kkSr5FY
I'm subbing, liking etc. for all of the info that you gave away here. Thank you so much. I'm new to this and quality info like this is hard to come by. Thank you.
Hello ...Just wanted to say thanks for all your time and hard work on the video that broke down the dividend tracking spreadsheet. Awesome job could not have done it without your help just became a fan I subscribed to your channel and will be catching all your videos thanks again I'll be watching.
Thanks, glad you liked it. I'm going to be releasing a significant update which goes way beyond just a portfolio tracker that I'm beta'ing with my Patreon Aristocrats and Kings right now :) It will be for those who remain higher tier Patreons, but I think folks are gonna be blown away ;)
Thanks, I'm glad you enjoyed it :) I wasn't even planning to make this vid and then a sub asked when I first started my channel so I made it, and boom it became my most popular video ever, lol
It didn't work for me. Can you type it copy/paste this one ? =SPARKLINE(GOOGLEFINANCE($A2, "PRICE", TODAY()-365,TODAY(),"weekly"),{"charttype","line";"linewidth",3;"color", if(googlefinance(A2,"price")
If you want to share your spreadsheet with me and add in comments into it where you want me to check, I can try to figure it out... Remove any info you don't want me to see first: gen.x.dividend.investor@gmail.com (or you can jump in my Discord and we can chat). #Partner49
Is there any way to draw from the canadian stock exchange for tickers? Also is there a way to get a live currency exchange calculation in a cell (like a stock price updating) drawing off of google data?
Thank you so much for this video. I just started investing in January of this year and found your channel two weeks ago. Thank you for breaking down all 25 companies. Also, for helping me learn how to create a spreadsheet to help track my investments.🙏
You're welcome :) Google has some issues where its api croaks sometimes (like if you open too many calls from a single spreadsheet), so at some point I'll do an update showing how you can use another service to get data... #Partner33
For Disney and Altria under the current PE column it is coming up as n/a and says error. Google finance parameter 2 value is invalid for the symbol specified. All the other positions work. Any thoughts on this. Also did you change your dividend yield to percentage or leave it with the decimal and several numbers after. Thanks again
Unfortunately all data sources sometimes come up bad.. So, probably its a Google issue... I usually tweak the number of decimals based on what I prefer...
Heyas. I put the formula (it is long and complex and it won't copy here in the comments due to google's html constraints) in a google doc for people to copy and paste -- its on my free Discord Dividend server in a FAQ channel in a FAQ document (near the bottom).
@@GenExDividendInvestor Thanks, I managed to do that one, but my recent challenge is to display the chart for a single historical day (start at opening and finish at closure). Any tip? Cheers !
i am trying to figure out how to dymacally import divident exdates and Pay dates, and dividend amounts. Also, I want to create a dividend payment transaction list (including date and ticker it applied to) and make a monthly subtotal.. or be able to dynamically make subtotals of what ever range of dates i want..
Ya things get more and more complicated. I have ex dates and a lot more in my 2.0 version that is a monthly service I give to anyone who remains a Patreon Aristocrat or King.
For the sparkline must i just copy that above text in fx block? Cuz when i did copy it, it told me ERROR formule parse error. And it has a red line under the words line line width and color , it says there can only be two arguments but this one has three
Are you in the USA? I found that outside the USA the sparkline doesn't work the same. If you are in the USA, then jump onto my Discord (link in description).. its free and I have a FAQ document where I list the sparkline so you can copy and paste it.
Love the video! Do you happen to know if the Ticker works for Canadian Companies? I tried adding AC.TO (Air Canada) but instead gives me AC (Associated Capital Group Inc). Thanks!!!
I finally got that long sparkline thing to work so, if your ticker is also A2, see if copying this will help. I literally just followed everything he said, awesome vid. =SPARKLINE(Googlefinance($A2,"price", TODAY()-365,today(),"weekly"),{"charttype","line";"linewidth",3;"color",IF(googlefinance(A2,"price")
@@GenExDividendInvestor hey, could you help me with the same sparkline but for 7 days instead of a year? I tried changing the code but i couldn't do it right. Thanks for the guide by the way, very helpfull.
Hi awesome video! Is there a way to automate google sheets to actually grab the annual dividend rate as well or do we have to always manually enter it?
Yeah.. you can do =index(googlefinance("jnj", "price", "12/1/2014", "12/2/2014", "DAILY"),2,2) to return back the price of jnj on 12/1/2014... (i.e. pick the start date and 1 day later).. you can also return ranges and then not use the index command #Partner46
Jump onto my Discrd. I created a FAQ channel, and in the channel is a FAQ document. In that document is a link to a Spreadsheet formulas document where you can cut and paste it to make it work. If you still can't get it to work you can PM me on there and then share your spreadsheet (after you removed any sensitive info) and I will login to your googlesheet and fix it for you.
Try to prefix your ticker with LON for GoogleFinance.. Or tell me a ticker that isn't working for, and I can try to find a solution. Also, jump in the Discord.. I don't always get notifications of new comments here :) #Partner49
Dennis Yap I’m not sure I’d need to see it.. I’d just check online the current price to deduce what currency you are probably getting.. then convert it if needed :) #Partner49
Super helpful thanks! Eventually I want to set things up so that I can pull my portfolio data into a spreadsheet like this so I don't have to manually update the # of shares column. Then the only thing I'd need to update is the dividend yield as it increases or if it gets cut 🙄
Cool.. Google has some bugs which can cause their spreadsheet to time out if you make too many API connections, so I'll probably make a follow up video on another api that can be used... #Partner34
Hi, awesome video, is there a way to put the actual payout (ie, the quarterly or monthly payout of dividend), and can you track if you reinvested the dividend?
Oops, I thought I answered this.. You can, you just need to create a new tab and track whatever info you want.. That's why I tried to teach people about the spreadsheet, so that then you can customize it.. Or jump on my Discord and I can help you out if you get stuck :) #Partner48
GenExDividendInvestor I meant isn’t there a function in Googlefinance that does it automatically? I found this see if it works for you: =concatenate("old.nasdaq.com/symbol/", $A4, "/dividend-history")
Just switched to thinkorswim. They dont show the percentage portfolio allocations. I am trying to see if i can export the statement to excel to show that.
@@GenExDividendInvestor yea I could export and used that for my october update but the manual work is unnecessary. I want to focus on research instead.
@@GenExDividendInvestor their platform is geared more towards encouragin tradin rather than long term investing. Mayb with zero commissions now that will change
Investing Education ahh, does your current portfolio allocation influence your investing decisions at all? (Or do you feel you have a good enough sense that you don’t care about specifics)
Fantastic video, you deserve all the Likes and Subscribes and you didn't even ask for them in this video. Quick question, I'd like to add a row to show the average price of the shares bought from XYZ company for instance. it helps me compare it to the current market price. I'm not sure if anyone else asked this question, but I keep adding stocks from a specific company when the price is right depending on how much money I have at that moment. and every-time the price is different from the one's previously bought. Hence this row would be extremely helpful.
Thanks.. I actually show how to calculate average price per share (as part of a cost basis calculation, even though its not cost basis, per se) in the part 2 video.
Great Vid! i was wondering how we could do a dollar cost averaging. what if you buy more of the stock at a different price. how would you input that? thanks in advance!
@@OscarCastro-nw7qo Once you understand the basics of how spreadsheets work, you can then customize things. This isn't tax advice, of course. But, you can enter in a new row of information in your spreadsheet to document when you bought or sold shares, how many shares, and the share price. Then you end up with a bunch of rows that are buys and sells that you can add up and average (or weighted average) to use however you want. Try it out. Make a spreadsheet and put a few buys and sells in there to see how the numbers look. #Partner43
I want to do that Google finance trick to add the share prices, but I don't know how you get that Google option to pop up in the cell. What key or command do you have to hit to bring up that Google option in the cell?
@@GenExDividendInvestor I decided to do a quick Google search to find the answer. I just couldn't see what you typed before Google. I did find the answer though. Thank you for getting back to me and for the great spreadsheet video.
Hey GenEx. For share price, should I be entering the amount I paid on average for the shares or what the current market share price is? Tragically, this is actually the first time I've ever made a spreadsheet lol
@@lukemiller1385 It depends. Start with something simple, use it for a while, and then determine if you need more. Some folks like tracking their cost basis, their average share price, etc. Others don't.
@@GenExDividendInvestor Well wouldn't I need to put in the info of how much I paid vs the market to know how much gains or losses I've had? What would be the point of just entering the current share price if it doesn't show what my actual costs are? Sorry for all the questions. I'm new to this part lol Do I just put in the average cost I paid into the Share Price column or do I need to make another column for average cost price? Because I have no idea what that would do to the current spreadsheet. Basically I'm curious how to know how much my actual gains are for what i've put in.
Hi, that video helps a lot ! Thank you. I do have a question on how to set up something with the stock purchases..... so say you bought JNJ on the 1st of Jan 2019 say 10 shares bought for 150 another 10 shares 01 apr 2019 for 140 then maybe an additional 10 shares on 01feb 2020 for say 130.......... is there way to add (like my brokerage has got a Plus sign) to drop down and track all this info? Thank you for your time and I am enjoying the videos !
Looks like you already answered this on someone else's question! New tab for each stock into the master sheet. Might be a great video !!!!!! Thank you so much!!!!
Thanks, Anthony :) If I wanted to track each buy and sell for each stock, then I'd create a new tab/sheet for each stock. Then I'd capture the information I need (date of transaction, how many shares I bought or sold at what price, and a running total for number of shares I ended up after the transaction). Then you could use that info to track your ongoing cost basis changing or whatever else you wanted. #Partner47
Thanks, Roger :) I think it will be like dividend investing.. It starts off slow, but then if you keep it at and focus on quality, it will grow -- until one day it surprises you :) #Partner48
Great video, so helpful for those just getting started. I use a google sheets add-on called Sheets Market Data that is really great as well, you should check it out, it gives dividend data for individual stocks as well.
Hi, thank you so much for this video, it really helped alot! May i ask for the portfolio allocation pie chart, is there a way to combine the same industries together under the same slice of the pie chart? For example in your pie chart, there two health care "industries" and i think it would be clearer if we could combine it together into one.
I load up a blank spreadsheet and go to any website I wanna try to scrape then I just try the first 10 numbers/tables to see if I get anything :). Since I made this video gurufocus has changed and isn’t as scrapable.
Try this URL: www.streetinsider.com/dividend_history.php?q=jnj (also, on my free Discord I have a FAQ where I list good websites as well as list all the formulas so you can copy and paste them as you like). The link to it is in the description.
I create them.. but you can also scour websites that list div cagr in table format, and pull them that way. Gurufocus used to, as did Reuters -- but every so often they change something which can break the formula. Easy to calculate -- like if you want the 5 year div cagr, you can go to somewhere like www.streetinsider.com/dividend_history.php?q=jnj ... Then pick a 5 year timeframe, like q1 of 2014 to q1 or 2019 and then the 5 year div cagr is =((J22/I22)^(1/5))-1 (where i is where you stored q1 2014's dividend, and j is where you stored q1 or 2019's dividend). #Partner26
Gah! I cannot get the =sparkline to work. Examples don't show up automatically. I screenshot your input from my phone and tried following it as I entered it into Sheets on my laptop. Any chance you could post it here? Thank you very much for sharing this. Just came across it this morning. Will be watching second video next.
Heyas Wendy.. I tried but there are formatting issues. However, on my Discord ( discord.gg/kkSr5FY ) there is a FAQ channel, and in there is a FAQ document, and at the bottom of that is a link to a Spreadsheet formulas document where you can cut and paste that formula plus a bunch of others.
@@GenExDividendInvestor I'm struggling to get the sparkline to work. I have copied your formula word for word. Spelt colour both the American way and British way but to no prevail. Any chance you can throw some suggestions?
@@AADadrah Share a sanitized version of your spreadsheet with me and give me write permissions and I'll show you gen.x.dividend.investor@gmail.com #Partner44
@@GenExDividendInvestor Thanks for this, I've emailed it over to you. Sorry for putting you out of your way. Your communication with your audience is great! I haven't seen anyone on this platform like it. Thanks again.
Quick question, for the name somwtimes its pulling it from different stock markets for example the ticker symbol Telus on the TSX is T but when i imput T as a symbol is pulls out AT&T ive been trying to fix this issue for a few days but can figure it out. Any help would be appreciated!:) thank you for the great video
Thank you so much for the tuturial, but for whatever reason everything gives me error, Example: =GOOGLEFINANCE(A2 , "name") gives me error, I am desesperate and I do not know what to do to track my portfolio. Your tutorial is great really, do you know how could get it sorted out? Thank you so much
Does =googlefinance("jnj", "price") work for you? Does =googlefinance("jnj", "name") work for you? Feel free to share a sanitized version of your spreadsheet with me @ gen.x.dividend.investor@gmail.com and I'll login and see what you are doing wrong. #Partner29
@@GenExDividendInvestor Yeah I am living in the UK. Thank you so much for such a quick response Since you uploaded the video, have you found any add on that helps to get the dividends numbers automatically? P.S. you got a new subscriber, you got very good channel, completely underrated, you deserve a lot more subscribers
@@coolbudu Thx! You can pull from finviz.. =regexextract(index(ImportHtml("finviz.com/quote.ashx?t="&A2, "table", 11),7,2),"/*[0-9.]+/*") where column a is where you store tickers.. so a2 might be jnj... #Partner29
A great way to pull in all the latest prices in (almost) real-time. And the ease to make sparklines is quite impressive. What sucks a bit is that you still have to go elsewhere to get the dividends. This would be really awesome if that process was automated as well.
good video but i tried to do it myself but the uk tickers i use when i want google finance to bring up the names is only bringing up US company names and not the uk names. I thought it would recognise the ticker no matter what country it is? Any ideas how i can resolve this?
Thanks.. Can you tell me which ticker you are trying? I just tried =GOOGLEFINANCE("lloy", "price") and it worked. So did =GOOGLEFINANCE("lloy", "name") #Partner26
@@jetlife1112 Try prefixing ithe ticker with LON: if its on the london stock exchange.. so if the stock is ABC then do =googlefinance("lon:abc", "price") #Partner26
One thing I'm a little confused about is how you just pull the latest number for the Annual dividend and multiply it be its frequency. Is this just supposed to be a rough estimate? What about in cases such as AstraZeneca which pays two drastically different amounts per year? Would you average it? ty
Concatenate just puts 2 strings together.. so if you have a ticker in a cell, and the url has the ticker in it, then you would concatenate the string together. #Partner38
Hi, my goal with this was to teach people to fish, so that they can go through the steps and learn and create it themselves. And then I'm here to help answer questions if someone gets stuck. Unfortunately, there isn't a great function that does it, so you can screenscape html or you can call api's (but those are behind walls normally, like iex)... #Partner27
This is amazing. Thank you so much! Some of my current PEs show #N/A and says "error, function googlefinance parameter 2 value is invalid for the symbol specified" Do you know how can I fix this?
MrBotia Do you want to give me edit permissions so I can see what you are talking about? Maybe make a copy, delete anything you don’t want me to see, then share it with gen.x.dividend.investor@gmail.com #Partner35
@@kasonblair263 Yeah, that is one of the annoying things with Google. Sometimes it seems to do that if you call too many api's and it takes too long -- then it displays that. I'm sure they will fix it once of these days, and as 5G rolls out then display can happen faster -- but its annoying when it happens now.. #Partner47
Thanks.. I think its helpful to learn how to do it, so you can customize and get exactly what you want.. But I can understand why others don't ;) #Partner27
How do you publish this sheet on for instance on blogspot.com after you are done with it? I mean so that it is automatically "called" each time a specific page on the blog is opened
Well, if you can embed your spreadsheet, it should automatically update every 15 mins or so (or however long google does it).. See if you can embed it .. #Partner42
Morning GenEx sorry one more question if you don't mind :), There is no column for a total cost of my shares, (only a live update) i thought it would be cool to see my Gain/Loss in percentage.
Heyas -- yeah you would need to add that... It is something I've never cared for, so I didn't track.. but I would just create a new tab per ticker, and then keep a running total of how many total shares I've bought, the average price, and each buy/sell on a new row.. Maybe I'll do a quick video showing how to do that.. #Partner48
this is awesome! thank you so so much!!! do you know how you can change the result google gives you to euros? cause in my depot i just see the euro-amounts i paid for each stock so i d have to manually calculate for each stock how much that is in dollars... do you know of anything that could fix that?
Thx.. Yeah, you can use their currency function on googlefinance and convert stuff from one currency to another... Check out my Discord if you have any specific questions. We have a channel just for people who are building their own stock spreadsheets: discord.gg/kkSr5FY
Hmm, im having an issue with the tickers that trade in the Toronto Stock Exchange and for some reason it does not correspond to the right ticker such as Toronto Dominion Bank, as it is both traded in NYSE and TSX. So when I put TD.TO, it does not register the price that corresponds to the price in the TSX market.
UPDATE -- Check out my recent release of my new 2.0 spreadsheet portfolio tracker product! ua-cam.com/video/qAV8D5r1I_M/v-deo.html Very very cool!
Don't forget to SUBSCRIBE and LIKE this video as a simple way to thank me. It really helps the channel ! Thanks, I appreciate it!
Support me & get Patreon perks ➜ www.patreon.com/genexdividendinvestor
Please join my free investing Dividend Discord chat server with over 2500 dividend investors on it: discord.gg/kkSr5FY
Also, I forgot to show how you can add logo's in this video - the best way to add a logo into a spreadsheet cell is go into the cell, and insert image then image in cell, google image search, and search for company logo. Click the one you want and then click insert.
Google has some timeout bugs where it can cause issues sometimes if you are scraping too many websites or if they have timeout issues which can lead to display issues. Hopefully Google will resolve this!
Also, Gurufocus changed their site so you can't scrape CAGR from it, but I list the new formula in the FAQ document on my Discord (as its the easiest centralized way I can update the info).
@@jerijenee You could use =index(importhtml("finviz.com/quote.ashx?t=jnj", "table", 11),11,8) Though, the more you scrape HTML, the more issues Google unfortunately has. I bet they will resolve this issue someday, but its annoying.
@@GenExDividendInvestor Thanks but new problem, I had to change it to look up Dividend 3.80 on the table which worked but it puts (*)3.80(*) in the cell and unable to change it to $ amount and throws off the rest of the data cells. Still playing with it
@@osros Hmm I'm not sure I see why it would do that. If you want to share your spreadsheet with me with edit rights then I can take a look.. or explain it more... #Partner22
@GenExDividendInvestor, great content here. I am not having success pulling the 3 yr CAGR from gurufocus. My my command is =abs(index(IMPORTHTML(CONCATENATE("www:gurufocus.com/stock/", C3, "/summary"),"table", 7),4,2))/100 and is returning #NA ( with the hovering message reading, " could not fetch www:gurufocus.com/stock/", C3, "/summary". Any thoughts? Any other sites you use to pull a 5 or 10 year div CAGR? #partner18
@@GenExDividendInvestor , great content here. I am not having success pulling the 3 yr CAGR from gurufocus. My my command is =abs(index(IMPORTHTML(CONCATENATE("www:gurufocus.com/stock/", C3, "/summary"),"table", 7),4,2))/100 and is returning #NA ( with the hovering message reading, " could not fetch www:gurufocus.com/stock/", C3, "/summary". Any thoughts? Any other sites you use to pull a 5 or 10 year div CAGR? #partner18
I'm subbing, sharing and liking because you were unselfish and shared this with us, not trying to sell it like everyone else. You will blow this channel up fo sho in due time little brother!!!
Thanks! I like to teach people to fish so hopefully my vids will help folks out :) #Partner47
Facts
@Expanse Half thought much?
Yours is the best spreadsheet tutorial. Clear, accurate, etc..others don't specify how they create the formulas, or the formulas are two long without explanation. You are talented, almost an intellectual artist for the dividend enthusiast.
Thanks! On my Discord I created a FAQ and at the bottom of it I have a link to a google doc that has all the formulas folks can cut and paste if they want.
@@GenExDividendInvestor Yes, I signed up for that and have to do that...my sheet has gone to hell with companies dropping out the tracking ability. Yours is the only cogent and clear tutorial....I hate to lose my sheet
@@edwardmcdonald8194 Yeah that's part of why I ended up making a spreadsheet as a tool I sell as a service is because there were some limitations with google...
It's probably sad how blown away and in awe I was of this. I'm so thankful for people out there who are smarter or more talented than me at these things. Thank you thank you thank you!
Thanks and you're welcome :) But I'm not smarter or more talented than you -- I've probably just spent more time learning how googlesheets work.
I was looking through your old videos and saw this one. Very informative and easy to use guide. I started in July with your referral link on M1. Added about a thousand dollars to it and kind of played with the account till around September. Then decided to get more serious about investing into it. I've been adding 900 to 1000 a month since. I'm up to 7000, nothing extravagantly huge, but I've set up a plan. In two years I'm going to try to add 2000 a month. Somewhere in 2026 I figure to have 100k, and 2036 looking to be 500k. Those may be lofty goals, but I see that dividend snowball rolling hard. lol
Thanks for all your videos.
Dwayne
Ahh cool and thanks! The hardest part is starting and the next hardest part will be not quitting during times of fear. Keep investing in good assets and over the long run I think you will be blown away by where you end up.
@@GenExDividendInvestor So far my account is doing great. Especially compared to the S&P.
My life may be boring but the google finance function is the single coolest thing I've seen
I know, right? It’s not perfect but it’s pretty cool :)
I can't thank you enough for this tutorial, very appreciated.
No prob, if you have any questions, drop me a note here :) #Partner28
Best UA-cam videos ever. Very informative! God bless you and your channel ! Thank you for all of your vids
Thanks, Angel :)
This was a great step by step.. My only issue was manipulating the CAGR field. I figured that the chart number he was using to pull data from was 7 and mine was 8. worked perfectly!
Cool, I did an entire video on CAGR if you want to check it out :) #Partner27
This will be my first time creating a spreedsheet. This was super helpful. Thanks.
Glad it was helpful! I have a Discord with a channel just for people who are making their own spreadsheet :)
Excellent explanation.....I love Google Spreadsheets to track investments....great job!
Thanks, I wasn't even planning on making this video until a subscriber suggested it, and then it turns out being my most popular video, lol.
Speechless tbh, I never though to stumble upon such a great person. I have like and I will be a loyal subscriber !
Thanks, Andres, very nice of you to say that :) You should jump on my Discord -- we have lots of investors from all around the world on it :) discord.gg/kkSr5FY
I'm subbing, liking etc. for all of the info that you gave away here. Thank you so much. I'm new to this and quality info like this is hard to come by. Thank you.
Hey thanks, I appreciate it :)
Hello ...Just wanted to say thanks for all your time and hard work on the video that broke down the dividend tracking spreadsheet. Awesome job could not have done it without your help just became a fan I subscribed to your channel and will be catching all your videos thanks again I'll be watching.
Rudy Black Awesome, thanks :). You should check out my free discord... we have over 2500 dividend investors on it :)
Your video is wonderful. I have to refer back to it when I buy a new stock.
Thanks, glad you enjoy it :)
Thanks for the video and sharing. is there a reason i have stock on TSX the price isnt correct?
Only reason I can think of is a google finance data issue.
The spreadsheet video is blowing up! All the best.
Haha ya, and it was a sub who asked me to make it, lol. #Partner25
Subscribed because of this video alone. Thank you so much and great work
Thanks, glad you liked it. I'm going to be releasing a significant update which goes way beyond just a portfolio tracker that I'm beta'ing with my Patreon Aristocrats and Kings right now :) It will be for those who remain higher tier Patreons, but I think folks are gonna be blown away ;)
An instant like and sub. Thank you so much for showing how to do this.
Thanks, I'm glad you enjoyed it :) I wasn't even planning to make this vid and then a sub asked when I first started my channel so I made it, and boom it became my most popular video ever, lol
Dude I did not know that google sheets had the ability to load stock data!!!! SUBSCRIBED!!
Pretty cool, huh? :)
It didn't work for me. Can you type it copy/paste this one ?
=SPARKLINE(GOOGLEFINANCE($A2, "PRICE", TODAY()-365,TODAY(),"weekly"),{"charttype","line";"linewidth",3;"color", if(googlefinance(A2,"price")
If you want to share your spreadsheet with me and add in comments into it where you want me to check, I can try to figure it out... Remove any info you don't want me to see first: gen.x.dividend.investor@gmail.com (or you can jump in my Discord and we can chat). #Partner49
Is there any way to draw from the canadian stock exchange for tickers?
Also is there a way to get a live currency exchange calculation in a cell (like a stock price updating) drawing off of google data?
Yes. I’ve posted a spreadsheet document in the faq channel and document on my Discord. Check that out for answers.
from Brazil and i got to say great job!!! be sure to make more content like that
Thanks, Walter. I love hearing from people around the world :) #Partner28
THANK YOU for the introduction. I just started investing and this video help a lot.
Welcome :)
This tutorial is UA-cam gold. Very informative and easy to follow. Should consider doing additional tutorials in the future. #Partner4
Thanks If you have any suggestions what would be useful, toss em my way... #Partner29
Thank you so much for this video. I just started investing in January of this year and found your channel two weeks ago. Thank you for breaking down all 25 companies. Also, for helping me learn how to create a spreadsheet to help track my investments.🙏
You're welcome :) Google has some issues where its api croaks sometimes (like if you open too many calls from a single spreadsheet), so at some point I'll do an update showing how you can use another service to get data... #Partner33
GenExDividendInvestor I’m experiencing that now. Thank you for the update and kind reply.
Wow. I had no idea google sheets could even do some of these functions.😳
You do fun things in Scotland, I do fun things in spreadsheets! They both are excit... no.. never mind, Scotland is better!
Thanks for making a video regarding this topic. Dropped a like 👍
Thanks!
@@GenExDividendInvestor I just built it now. Just a question I need to add in Canadian amounts for stocks prices but google finance does in USD.
Got it =googlefinance(“tse:symbol”,”price”).
Yours is the clearest..".keep it simple stupid" is the motto applied to me..
Glad you like it :)
For Disney and Altria under the current PE column it is coming up as n/a and says error. Google finance parameter 2 value is invalid for the symbol specified. All the other positions work. Any thoughts on this. Also did you change your dividend yield to percentage or leave it with the decimal and several numbers after. Thanks again
Unfortunately all data sources sometimes come up bad.. So, probably its a Google issue... I usually tweak the number of decimals based on what I prefer...
Thank you very much ! Do you know how put the share price for a canadian stock ? For TD Bank, I put the ticker TD.TO but it didn't work.
=googlefinance("tse:td", "price") #Partner45
@@GenExDividendInvestor Thank you for replying so fast! Loved your video!
@@suzytruong4219 Thanks Suzy, I appreciate it :) #Partner45
Great video! how do you go about pulling the information from google in £ rather than $ or is there a simple conversion I can do?
You can use the google functions to auto look up current currency rates and do the calc :)
*GOOGLEFINANCE("currency:USDEUR")
@@renks05 I tried that, but for some reason couldn’t get it to work, I found a way to work around the issue already anyway! Thanks
Hi there, what is the trick to get a sparkline chart that represent the share movement of a specific day?
Heyas. I put the formula (it is long and complex and it won't copy here in the comments due to google's html constraints) in a google doc for people to copy and paste -- its on my free Discord Dividend server in a FAQ channel in a FAQ document (near the bottom).
@@GenExDividendInvestor Thanks, I managed to do that one, but my recent challenge is to display the chart for a single historical day (start at opening and finish at closure). Any tip? Cheers !
i am trying to figure out how to dymacally import divident exdates and Pay dates, and dividend amounts. Also, I want to create a dividend payment transaction list (including date and ticker it applied to) and make a monthly subtotal.. or be able to dynamically make subtotals of what ever range of dates i want..
Ya things get more and more complicated. I have ex dates and a lot more in my 2.0 version that is a monthly service I give to anyone who remains a Patreon Aristocrat or King.
Would this work with fractional shares?
Yep #Partner48
For the sparkline must i just copy that above text in fx block? Cuz when i did copy it, it told me ERROR formule parse error. And it has a red line under the words line line width and color , it says there can only be two arguments but this one has three
Are you in the USA? I found that outside the USA the sparkline doesn't work the same. If you are in the USA, then jump onto my Discord (link in description).. its free and I have a FAQ document where I list the sparkline so you can copy and paste it.
GenExDividendInvestor oh thats why , im from South Africa 🇿🇦
This is the greatest video on the interwebs!
lol.. I don't know why I crackup when I hear "interweb"... #Partner28
@@GenExDividendInvestor Yeah me too lol
Wow this was very detailed! Good info, mine is just the price I bought it at and the dividend it pays but similar concept.
Thanks! As long as you are tracking metrics than are relevant to you, it's all good!
Love the video! Do you happen to know if the Ticker works for Canadian Companies? I tried adding AC.TO (Air Canada) but instead gives me AC (Associated Capital Group Inc). Thanks!!!
Thanks! Yes, try TSE:
I finally got that long sparkline thing to work so, if your ticker is also A2, see if copying this will help. I literally just followed everything he said, awesome vid. =SPARKLINE(Googlefinance($A2,"price", TODAY()-365,today(),"weekly"),{"charttype","line";"linewidth",3;"color",IF(googlefinance(A2,"price")
Cool and thanks :) #Partner34
@@GenExDividendInvestor hey, could you help me with the same sparkline but for 7 days instead of a year? I tried changing the code but i couldn't do it right. Thanks for the guide by the way, very helpfull.
@@spectrius112 Thanks.. try this =SPARKLINE(GOOGLEFINANCE($A2,"price",TODAY()-7,TODAY(),"daily"),{"charttype","line";"linewidth",3;"color", if(googlefinance(A2,"price")
@@GenExDividendInvestor it worked, thank you!
Hi awesome video! Is there a way to automate google sheets to actually grab the annual dividend rate as well or do we have to always manually enter it?
Thanks. You can pull the dividend from finviz using inporthtml or you can pull from the old nasdaq site with importxml. #Partner37
Do you know if it's possible to get a quote from a stock at a specific date in google finance?
Yeah.. you can do =index(googlefinance("jnj", "price", "12/1/2014", "12/2/2014", "DAILY"),2,2) to return back the price of jnj on 12/1/2014... (i.e. pick the start date and 1 day later).. you can also return ranges and then not use the index command #Partner46
@@GenExDividendInvestor fantastic! it works like a charm!
What a beautiful job of explaining this stuff. You have a great way of presenting this information.
Thanks, Joseph :)
Great way to track your progress. Dividend investing is a long but rewarding journey.
Definitely... An eternity, for future generations as well :) Thanks!
Hi thank you so much for the information anyways do you know any way to get the annual dividend automatically?
You could try =regexextract(index(ImportHtml("finviz.com/quote.ashx?t="&A2, "table", 11),7,2),"/*[0-9.]+/*") where a2 is where the ticker is....
What is the ticker symbol to put on this spreadsheet for the original S&P500???
I like .INX
@@GenExDividendInvestor THANK YOU
Great video! Very helpful
Thanks, I'm glad you liked it :)
hey I been having trouble with the spark lines I been trying too type in letter by letter and its not seeming too working any tips?plz thank you
Jump onto my Discrd. I created a FAQ channel, and in the channel is a FAQ document. In that document is a link to a Spreadsheet formulas document where you can cut and paste it to make it work. If you still can't get it to work you can PM me on there and then share your spreadsheet (after you removed any sensitive info) and I will login to your googlesheet and fix it for you.
Hi, I wonder how would I need to amend if I have shares beside NYSE, I do invest in LSE too. What do u think the spreadsheet should be?
Try to prefix your ticker with LON for GoogleFinance.. Or tell me a ticker that isn't working for, and I can try to find a solution. Also, jump in the Discord.. I don't always get notifications of new comments here :) #Partner49
Yes, I got it. But do I need to convert the GBP to USD. Or is already converted when I pick up from Google finance?
Dennis Yap I’m not sure I’d need to see it.. I’d just check online the current price to deduce what currency you are probably getting.. then convert it if needed :) #Partner49
Super helpful thanks! Eventually I want to set things up so that I can pull my portfolio data into a spreadsheet like this so I don't have to manually update the # of shares column. Then the only thing I'd need to update is the dividend yield as it increases or if it gets cut 🙄
Cool.. Google has some bugs which can cause their spreadsheet to time out if you make too many API connections, so I'll probably make a follow up video on another api that can be used... #Partner34
Hi, awesome video, is there a way to put the actual payout (ie, the quarterly or monthly payout of dividend), and can you track if you reinvested the dividend?
Oops, I thought I answered this.. You can, you just need to create a new tab and track whatever info you want.. That's why I tried to teach people about the spreadsheet, so that then you can customize it.. Or jump on my Discord and I can help you out if you get stuck :) #Partner48
GenExDividendInvestor I meant isn’t there a function in Googlefinance that does it automatically? I found this see if it works for you: =concatenate("old.nasdaq.com/symbol/", $A4, "/dividend-history")
Hey great video, how would I go about changing the currency to GBP from USD cant figure it out without messing with the formula.
=A1*GOOGLEFINANCE("CURRENCY:GBPUSD")
Gee, thanks for the info. I finally had some time to complete this.
Awesome... if you have any questions feel free to drop by my discord (link in description) #Partner48
Thank you! Subscriber now for sure!
Thanks, David, I appreciate it :)
How do I get data for local exchanges? For instance, Manulife price is displayed in USD, while I have bought it in CAD from the Toronto Stock Exchange
Try to concatenate "TSE:" before the ticker.. like =googlefinance("tse:mfc", "price")
@@GenExDividendInvestor Thanks, Done!
Just switched to thinkorswim. They dont show the percentage portfolio allocations. I am trying to see if i can export the statement to excel to show that.
Seems so surprising.. I'm sure you can export.. These firms need better UX people.. Or maybe they just aren't prioritizing it.
@@GenExDividendInvestor yea I could export and used that for my october update but the manual work is unnecessary. I want to focus on research instead.
@@GenExDividendInvestor their platform is geared more towards encouragin tradin rather than long term investing. Mayb with zero commissions now that will change
Investing Education ahh, does your current portfolio allocation influence your investing decisions at all? (Or do you feel you have a good enough sense that you don’t care about specifics)
Investing Education Ah gotcha.
Thanks man really helpful video..
You got yourself a new subscriber keep up the good work
Thanks, Hassan... I appreciate your support and kind words :)
Fantastic video, you deserve all the Likes and Subscribes and you didn't even ask for them in this video. Quick question, I'd like to add a row to show the average price of the shares bought from XYZ company for instance. it helps me compare it to the current market price. I'm not sure if anyone else asked this question, but I keep adding stocks from a specific company when the price is right depending on how much money I have at that moment. and every-time the price is different from the one's previously bought. Hence this row would be extremely helpful.
Thanks.. I actually show how to calculate average price per share (as part of a cost basis calculation, even though its not cost basis, per se) in the part 2 video.
Clean nice and organized
Thanks, I appreciate it :)
Are there similar call functions outside of google?
You can call APIs using Json...
Great Vid! i was wondering how we could do a dollar cost averaging. what if you buy more of the stock at a different price. how would you input that? thanks in advance!
Thanks.. you could just add a new tab in where you track each new buy and then average that info in to a row where you are aggregating it. #Partner41
Sorry , im a little confused on how to cost average.
@@OscarCastro-nw7qo Once you understand the basics of how spreadsheets work, you can then customize things. This isn't tax advice, of course. But, you can enter in a new row of information in your spreadsheet to document when you bought or sold shares, how many shares, and the share price. Then you end up with a bunch of rows that are buys and sells that you can add up and average (or weighted average) to use however you want. Try it out. Make a spreadsheet and put a few buys and sells in there to see how the numbers look. #Partner43
I want to do that Google finance trick to add the share prices, but I don't know how you get that Google option to pop up in the cell. What key or command do you have to hit to bring up that Google option in the cell?
You just type =googlefinance("jnj") (if you want jnj)(
@@GenExDividendInvestor I decided to do a quick Google search to find the answer. I just couldn't see what you typed before Google. I did find the answer though. Thank you for getting back to me and for the great spreadsheet video.
Hey GenEx. For share price, should I be entering the amount I paid on average for the shares or what the current market share price is? Tragically, this is actually the first time I've ever made a spreadsheet lol
@@lukemiller1385 It depends. Start with something simple, use it for a while, and then determine if you need more. Some folks like tracking their cost basis, their average share price, etc. Others don't.
@@GenExDividendInvestor Well wouldn't I need to put in the info of how much I paid vs the market to know how much gains or losses I've had? What would be the point of just entering the current share price if it doesn't show what my actual costs are? Sorry for all the questions. I'm new to this part lol Do I just put in the average cost I paid into the Share Price column or do I need to make another column for average cost price? Because I have no idea what that would do to the current spreadsheet. Basically I'm curious how to know how much my actual gains are for what i've put in.
Do you have to update the annual dividend every year when the company raises or lowers their dividend?
Not if you pull it dynamically from a site like finviz :)
Very well explained. Thanks for sharing.
Thanks, Bob. I appreciate it :)
Absolutely impressed..
Glad you enjoyed it :)
New to tour Chanel just want to say thanks for this info and looking forward to your videos. 👍👍
Cool, thanks for joining up and watching :) #Partner37
Hi, that video helps a lot ! Thank you. I do have a question on how to set up something with the stock purchases..... so say you bought JNJ on the 1st of Jan 2019 say 10 shares bought for 150 another 10 shares 01 apr 2019 for 140 then maybe an additional 10 shares on 01feb 2020 for say 130.......... is there way to add (like my brokerage has got a Plus sign) to drop down and track all this info? Thank you for your time and I am enjoying the videos !
Looks like you already answered this on someone else's question! New tab for each stock into the master sheet. Might be a great video !!!!!! Thank you so much!!!!
Thanks, Anthony :) If I wanted to track each buy and sell for each stock, then I'd create a new tab/sheet for each stock. Then I'd capture the information I need (date of transaction, how many shares I bought or sold at what price, and a running total for number of shares I ended up after the transaction). Then you could use that info to track your ongoing cost basis changing or whatever else you wanted. #Partner47
Thank you for making this, very informative! Liked & Subbed!
Thanks & you’re welcome!
You might have a small channel, but i like the content alot! I hope you can make a decent income off this channel.
Thanks, Roger :) I think it will be like dividend investing.. It starts off slow, but then if you keep it at and focus on quality, it will grow -- until one day it surprises you :) #Partner48
Great video, so helpful for those just getting started. I use a google sheets add-on called Sheets Market Data that is really great as well, you should check it out, it gives dividend data for individual stocks as well.
Cool, thanks for the tip!
Hi, thank you so much for this video, it really helped alot! May i ask for the portfolio allocation pie chart, is there a way to combine the same industries together under the same slice of the pie chart? For example in your pie chart, there two health care "industries" and i think it would be clearer if we could combine it together into one.
Select "aggregate" #Partner49
@@GenExDividendInvestor It worked! Thank you
How do you combine the industries with same name in Portfolio allocation? You have 2 healthcare in the chart that are separate.
I think I know what you mean.. When you create the graph, check the "aggregate" box. #Partner28
That worked, Thank You!!
how do you know table 7 from the gurufocus website?
I load up a blank spreadsheet and go to any website I wanna try to scrape then I just try the first 10 numbers/tables to see if I get anything :). Since I made this video gurufocus has changed and isn’t as scrapable.
Thank you 🙏 this has helped me sooo much to help keep track of my investments
Great, I'm glad it helped :)
Thankyou Very Much for this! Much LOVE!
You are welcome, glad you found it useful :)
It seems street insider needs to subscribe in order to use. any alternate website to use?
Try this URL: www.streetinsider.com/dividend_history.php?q=jnj (also, on my free Discord I have a FAQ where I list good websites as well as list all the formulas so you can copy and paste them as you like). The link to it is in the description.
@@GenExDividendInvestor Thank you :)
How would you pull the 5 and 10 year cagr and so on?
I create them.. but you can also scour websites that list div cagr in table format, and pull them that way. Gurufocus used to, as did Reuters -- but every so often they change something which can break the formula. Easy to calculate -- like if you want the 5 year div cagr, you can go to somewhere like www.streetinsider.com/dividend_history.php?q=jnj ... Then pick a 5 year timeframe, like q1 of 2014 to q1 or 2019 and then the 5 year div cagr is =((J22/I22)^(1/5))-1 (where i is where you stored q1 2014's dividend, and j is where you stored q1 or 2019's dividend). #Partner26
@@GenExDividendInvestor much appreciated, i think i understand
great video one of the best i seen
Thank you, that means the world to me :)
Gah! I cannot get the =sparkline to work. Examples don't show up automatically. I screenshot your input from my phone and tried following it as I entered it into Sheets on my laptop. Any chance you could post it here? Thank you very much for sharing this. Just came across it this morning. Will be watching second video next.
Heyas Wendy.. I tried but there are formatting issues. However, on my Discord ( discord.gg/kkSr5FY ) there is a FAQ channel, and in there is a FAQ document, and at the bottom of that is a link to a Spreadsheet formulas document where you can cut and paste that formula plus a bunch of others.
@@GenExDividendInvestor Thanks!
Thanks for your hard work teaching us this.
You are welcome, my friend :) #Partner44
@@GenExDividendInvestor I'm struggling to get the sparkline to work. I have copied your formula word for word. Spelt colour both the American way and British way but to no prevail. Any chance you can throw some suggestions?
@@AADadrah Share a sanitized version of your spreadsheet with me and give me write permissions and I'll show you gen.x.dividend.investor@gmail.com #Partner44
@@GenExDividendInvestor Thanks for this, I've emailed it over to you. Sorry for putting you out of your way. Your communication with your audience is great! I haven't seen anyone on this platform like it. Thanks again.
@@AADadrah Done and no worries. Jump on my discord if you want to chat with me and over a thousand other investors :) #Partner44
Quick question, for the name somwtimes its pulling it from different stock markets for example the ticker symbol Telus on the TSX is T but when i imput T as a symbol is pulls out AT&T ive been trying to fix this issue for a few days but can figure it out. Any help would be appreciated!:) thank you for the great video
Try =googlefinance("tse:t", "price") #Partner49
Thank you so much! Ive spent hours trying to do something that simple lol i really do appreciate it keep up the good work!:)
Thank you so much for the tuturial, but for whatever reason everything gives me error, Example: =GOOGLEFINANCE(A2 , "name") gives me error, I am desesperate and I do not know what to do to track my portfolio. Your tutorial is great really, do you know how could get it sorted out? Thank you so much
Does =googlefinance("jnj", "price") work for you?
Does =googlefinance("jnj", "name") work for you?
Feel free to share a sanitized version of your spreadsheet with me @ gen.x.dividend.investor@gmail.com and I'll login and see what you are doing wrong. #Partner29
Alright I think I found the problem, instead of using this simbol comma "," I have to use semicolon";"
@@coolbudu Ahh, are you outside the US? I believe outside requires ;, which isn't listed in google documentation... #Partner29
@@GenExDividendInvestor Yeah I am living in the UK. Thank you so much for such a quick response
Since you uploaded the video, have you found any add on that helps to get the dividends numbers automatically?
P.S. you got a new subscriber, you got very good channel, completely underrated, you deserve a lot more subscribers
@@coolbudu Thx! You can pull from finviz.. =regexextract(index(ImportHtml("finviz.com/quote.ashx?t="&A2, "table", 11),7,2),"/*[0-9.]+/*") where column a is where you store tickers.. so a2 might be jnj... #Partner29
A great way to pull in all the latest prices in (almost) real-time. And the ease to make sparklines is quite impressive. What sucks a bit is that you still have to go elsewhere to get the dividends. This would be really awesome if that process was automated as well.
Hopefully this gives you the tools you need to evolve it to your liking :)
good video but i tried to do it myself but the uk tickers i use when i want google finance to bring up the names is only bringing up US company names and not the uk names. I thought it would recognise the ticker no matter what country it is? Any ideas how i can resolve this?
Thanks.. Can you tell me which ticker you are trying? I just tried =GOOGLEFINANCE("lloy", "price") and it worked. So did =GOOGLEFINANCE("lloy", "name") #Partner26
Or, =googlefinance("lon:adm", "price") i.e. prefix your stock with "lon:" #Partner26
GenExDividendInvestor I had tried TW, NG which are Uk stocks TW- Taylor wimpy and NG- national grid
GenExDividendInvestor so do I need to just put
=googlefinance(A2, “Lon”)
Cause that’s not working
@@jetlife1112 Try prefixing ithe ticker with LON: if its on the london stock exchange.. so if the stock is ABC then do =googlefinance("lon:abc", "price") #Partner26
I have tried to put the tickers for Centrica PC (CNA) and Centamin PLC (CEY) but seems to be giving me incorrect companies giving me false prices
=googlefinance("OTCMKTS:CELTF") see if that works..
Is there a way to change the Google finance search to other stock exchanges?
Yes, you prefix the ticker with the exchange and a colon.. Like "tse:enb" instead of "enb".
One thing I'm a little confused about is how you just pull the latest number for the Annual dividend and multiply it be its frequency. Is this just supposed to be a rough estimate? What about in cases such as AstraZeneca which pays two drastically different amounts per year? Would you average it? ty
It depends on what you want to see and the data you can find :). I ended up with 2 numbers.. 1 I automate and one I can overide the automatic one.
For the 3 Yr Div CAGR formula, what is the difference when you type "importhtml" with CONCATENATE and without CONCATENATE?
Concatenate just puts 2 strings together.. so if you have a ticker in a cell, and the url has the ticker in it, then you would concatenate the string together. #Partner38
GenExDividendInvestor you are genius!
@@KkianN Haha I don't know about that, but hopefully it was valuable to you :) #Partner39
GenExDividendInvestor definitely! Thanks for the idea and all info you have shared
can you share the google sheet you created in this video? How can the dividend rates for stocks be tracked? is there a function for this? thanks!
Hi, my goal with this was to teach people to fish, so that they can go through the steps and learn and create it themselves. And then I'm here to help answer questions if someone gets stuck. Unfortunately, there isn't a great function that does it, so you can screenscape html or you can call api's (but those are behind walls normally, like iex)... #Partner27
Great video! Thanks for doing this.
Thanks, I appreciate it :) #Partner35
This is amazing. Thank you so much!
Some of my current PEs show #N/A and says "error, function googlefinance parameter 2 value is invalid for the symbol specified"
Do you know how can I fix this?
Sure... Show me what you typed in that is giving you an error... #Partner35
@@GenExDividendInvestor =GOOGLEFINANCE(A2, "pe") That is what I typed, but out of my 6 stock, two of those show N/A.
MrBotia Do you want to give me edit permissions so I can see what you are talking about? Maybe make a copy, delete anything you don’t want me to see, then share it with gen.x.dividend.investor@gmail.com #Partner35
@@GenExDividendInvestor I too ran into this exact issue with the PE.
@@kasonblair263 Yeah, that is one of the annoying things with Google. Sometimes it seems to do that if you call too many api's and it takes too long -- then it displays that. I'm sure they will fix it once of these days, and as 5G rolls out then display can happen faster -- but its annoying when it happens now.. #Partner47
Really helpful video man! All investors should make their own spreadsheet imo.
Thanks.. I think its helpful to learn how to do it, so you can customize and get exactly what you want.. But I can understand why others don't ;) #Partner27
How do you publish this sheet on for instance on blogspot.com after you are done with it? I mean so that it is automatically "called" each time a specific page on the blog is opened
Well, if you can embed your spreadsheet, it should automatically update every 15 mins or so (or however long google does it).. See if you can embed it .. #Partner42
Do I change to pounds?
I'm adding that capability in my 2.0 spreadsheet.
You can also use the CURRENCY command... like GOOGLEFINANCE("CURRENCY:USDGBP")
GenExDividendInvestor thank you so much. I was trying to change it manually and that definitely wasn’t working 😂
Morning GenEx sorry one more question if you don't mind :), There is no column for a total cost of my shares, (only a live update) i thought it would be cool to see my Gain/Loss in percentage.
Heyas -- yeah you would need to add that... It is something I've never cared for, so I didn't track.. but I would just create a new tab per ticker, and then keep a running total of how many total shares I've bought, the average price, and each buy/sell on a new row.. Maybe I'll do a quick video showing how to do that.. #Partner48
Really helpful. Thank you.
Thanks! You’re welcome, friend :)
I subbed and gave you the thumbs up, simply because of this
Thanks Keith :) #Partner48
Is there a way to have the annual dividend auto-populate without having to look up values manually, as they change often?
Cities try Finviz
Specifically `=regexextract(index(ImportHtml("finviz.com/quote.ashx?t="&A2, "table", 11),7,2),"/*[0-9.]+/*")`
I put my formulas in a doc people can copy and paste on my Discord server. Sometimes formulas don't work in comments...
@@GenExDividendInvestor Discord joined, thanks very much sir.
this is awesome! thank you so so much!!! do you know how you can change the result google gives you to euros? cause in my depot i just see the euro-amounts i paid for each stock so i d have to manually calculate for each stock how much that is in dollars... do you know of anything that could fix that?
Thx.. Yeah, you can use their currency function on googlefinance and convert stuff from one currency to another... Check out my Discord if you have any specific questions. We have a channel just for people who are building their own stock spreadsheets: discord.gg/kkSr5FY
@@GenExDividendInvestor thank you so so much! :)
Hmm, im having an issue with the tickers that trade in the Toronto Stock Exchange and for some reason it does not correspond to the right ticker such as Toronto Dominion Bank, as it is both traded in NYSE and TSX. So when I put TD.TO, it does not register the price that corresponds to the price in the TSX market.
If you want to log into my discord I can log into your spreadsheet and help you and talk voice.