Build a Stock Watchlist in Google Sheets! (Tutorial)

Get 59% off of Seeking Alpha Premium!

Get up to 17 Free stocks with moomoo | Open a new account and deposit:

In this video, I show you how powerful of a tool google sheets can be. I take you step by step on how to build a stock watchlist in google sheets. Let me know if you have any questions!

Formulas:

Here is the updated formula to pull dividends and industry:

Dividends:

Industry:

=if(I2="","",Left(Q2, Search(" |",Q2)))

*Be sure to adjust I2 to where your ticker is located

12 Month Return: =(GOOGLEFINANCE(C5,"price") / index(GOOGLEFINANCE(C5,"price", datevalue(today()-365) ) , 2,2) - 1 )

1 Yr Chart: =(SPARKLINE(index(GOOGLEFINANCE(C5,"price",today()-365,today()),,2),{"Charttype","column";"color","purple"}))

2 Yr Chart: =(SPARKLINE(index(GOOGLEFINANCE(C5,"price",today()-730,today()),,2),{"Charttype","column";"color","purple"}))

I am not a Financial advisor or licensed professional. Nothing I say or produce on YouTube, or anywhere else, should be considered as advice. All content is for educational purposes only. I am not responsible for any financial losses or gains. Invest and trade at your own risk. Some of the links in the description may be affiliate links.

[Dividendology]

Formulas:



Industry step 2: =if(B16="", "", Left(N16, Search(" |", N16)))

12 Month Return: =(GOOGLEFINANCE(C5, "price") / index(GOOGLEFINANCE(C5, "price", datevalue(today()-365) ), 2, 2) - 1 )

1 Yr Chart: =(SPARKLINE(index(GOOGLEFINANCE(C5, "price", today()-365, today()), , 2), {"Charttype", "column";"color", "purple"}))

2 Yr Chart: =(SPARKLINE(index(GOOGLEFINANCE(C5, "price", today()-730, today()), , 2), {"Charttype", "column";"color", "purple"}))

[Nightbreed]

Industry formula does not work currently. For me, it just prints "-", which is default for Error.

[daviddijkman]

Wow this is an amazingly clear video, even for an excel noob like me you make it look easy! Thanks a lot :)

[ilop]

Thank you my friend. One of the BEST tutorial I have found.
Direct and simple. Memories from Spain

[jeanphi]

Thank you very much fro this very informative video. One question why you cannot add $ symbol for the dividend column

[callumhewson]

Got Error Message (same as last time on your older video and you told me it was fixed on more recent) = Message = function INDEX parameter 2 value is 7 Valis values are between 0 and 1 inclusive

[fizr]

wat about a column for exdiv date? formula?

[joaobarbosa]

incredible work, but i have a doubt when i pull the dividend data and try to do the calculations for the dividend yield google sheets say: "Paramete 1 expects number values but is a text and cannot be coerced to a number." Any idea on how to solve this?

keep the good work

[memphisz]

Excellent video! Thank you for sharing.

[patrickbradsher]

Trying to code Industry into A3, but it is not working. I can pull it up using Stock Ticker in code but not automate from cell. Any ideas to why it's not working?

[Martin_Kitahara]

Hi, in my German sheet your formula for dividends doesnt work. I figured out that we replace ", " with ";" in our google formulas commands (example: =GOOGLEFINANCE(B15;"PE") but I still didnt get it to work for the dividend. Anyone have some advice?

[FeedYourView]

Hi and thanks for all your video and informations. It's help me so very much.
But in your formula for Industry, in the second step it's B15 of all, because if is B16 that is for the next stock.
If i'm wrong i'm sorry.
Thanks once again

[andrecorreia]

Fantastic video. Thank you!. Could be interesting if you add a column for your intrinsic value calculation, margin of safety and acceptable price to buy :D

[kingc]

Love this, Question: when i put in the formula for dividend I get this #REF! error, any idea what that could be? I'm following all your steps one by one

[nationalnotes]

I joined your Patreon on the 12 of Dec. for 1 month. Tried unsuccessfully to get access today. Can you help. Thx

[barendippel]

Great Vid, but I do have some company's from Europe wich are not listed at finviz :(

[DisruptiveWealthCreation]

Great walthrough on the tutorial LIKE 143#

[Billyonaire__]

Man seems to work for other people, idk what I'm doing wrong but the dividend formula gives me #REF! and industry formula just gives me "-". Dang

[glendavis]

I believe this is Google spreadsheet not Exce!