Create Your Own Stock Watchlist: Beginner Google Sheets Tutorial

[@MichaelJayValueInvesting]

This tutorial is targeted to beginners who do not have much experience with Excel or Google Sheets. As such it is relatively slow paced and relaxed, walking you through the process. Feel free to use the timestamps below to skip around. If you'd like more spreadsheet tutorials leave a like/comment to let me know! Timestamps for navigation 00:24 Walking through finished spreadsheet features 01:36 Getting started: spreadsheet setup 02:44 GOOGLEFINANCE() function 05:49 Adding market capitalization 07:10 Adding 52 week high/lows 08:28 Adding daily percent change 10:22 Adding % off 52 week high/low 11:28 Adding data validation column (drop down menu) 13:11 Adding buy/sell price targets 14:22 Adding a dynamic stock rating (buy/hold/sell) 17:43 How to add visualizations (charts/graphs) 21:15 Adding summary statistics (example) 22:26 Formatting tips and tricks 23:38 Adding conditional formatting 27:09 Reviewing the final product

[@leoinquisitive8096]

Many Thanks, Please continue more of such content, would like you to guide us towards more advance versions of portfolio management through sheets.

[@IsaacWendt]

Great Video, I'm not sure if I'm more impressed with the amount of Excel I just learnt or that fact that I have this sweet stock watchlist.

[@ikrashafiq7911]

Thank you very much!. yesterday, I make stock track sheet and today watch list. I wanted to built for a while now. Tomorrow, I will work on the complexed stock price track sheet. I really appreciate your effort to teach all of us for free.

[@jasong4879]

Wow! This is blowing my mind a little!! Thank you. This was very helpful and exactly what I've been wanting to do!

[@KostaJankovski]

Thank you Michael, we did not get good value with this tutorial, WE GOT A GREAT VALUE. Please continue delivering such great value.

[@kutubbiswas2401]

I don't find words to thank u. it was very helpful for me.

[@joylynsilveira]

wow! i've been searching for this for so long, so glad i came across this video. Thank you so much. pls do share more such spreadsheet ideas.

[@dmehta3476]

Thanks. Mike we need something to track independent portfolio record calculating TOtal return per stock as well as CAGR per stock and per portfolio.

[@grahamr939]

This is one of the best tutorials i have seen. I will be trying a similar spreadsheet soon. Keep up the great work.

[@MichaelJayValueInvesting]

Good luck! I find they are quite fun to make and customize :)

[@randimark7680]

Dang Bro this is Awesome. I'm creating my own watch lists , never used this and it's FREE. Such a great Tool.

[@sanjc8683]

I’ve seen a few videos on how to create a spreadsheet portfolio, this is by far the best. The step by step guide and explanations have made it simple and easy to follow and I was inspired to create my own portfolio spreadsheet, which I completed iin just 30mins. Thank you for your time and inspiration, look forward to seeing your other videos!

[@shopshop144]

I'm glad you showed the finished list, that way I knew not to go further; I want to list stocks by name, not ticker symbol. Maybe this process would include this as well, but one bit of info I want on my list is the date and price of a stock when I put it on the list.

[@meyaun11]

Thank you for sharing wonderful video, It will definitely help me to track stocks as a begginer

[@bourgeoise2]

Excellent video. Very easy for me to follow and create my watchlist. Thank you!

[@craigcothren]

A GREAT end product made the q0 hours it took me to learn the Sheet commands starting from no experience at all. I will not lie, I completed this course over a two day period. But now I have a very useful WATCH LIST......

[@Amdamguy]

Thank you for your tutorials.clear simple easy to follow! Great work

[@mi-hi7464]

Dear Michael, thank you very much: That kind of tutorial was what i was looking for. Great Video, great job, greets from Austria.

[@nicholaspescinski7818]

Thank you very much for hard work and professional presentation. My group and I will make sure we make good use of this spread sheet

[@williamd946]

You are the master. That program takes a college degree to figure out. I was with you, up until the last 8 minutes, when you added the final customizations. Guess I'll have to slow the playblack. Anyway, great tool. Thanks a lot. Update: I did it! :)

[@InspiringThings101]

You are great man! I hit the like button and subscribed to your channel. Thanks a bunch!

[@brooksbertelsen9122]

I do more mutual funds do you have a video on both stock and funds same spreadsheet?

[@perfectbrowsstudio]

This was so helpful! I really appreciate how straight-forward and easy-to-follow your tutorial was. I can't wait to use this spreadsheet to help me monitor my own portfolio :)

[@VEucaliptoUS]

if=(c4

[@beach_lion]

Awesome, I was wondering how you make your sheets w/ Google's API!

[@MichaelJayValueInvesting]

Google Sheets has a lot of flexibility for customization, part of the reason I enjoy making/using them so much :)

[@trevor6549]

thank you for this great video. I followed exactly your instructions and made my stock portfolio

[@amithachandima3318]

Superb thanks. Do this kind of more vedios.

[@DavidElstob73]

Thanks for the tutorial - it has been really helpful.

[@DividendGrowthInvesting]

Great tutorial! If you don't track your success, you will never know in which direction your investments are going.

[@MichaelJayValueInvesting]

True! There is a saying that what gets measured gets managed. Managing your finances and investments are one area where spreadsheets really excel.

[@ryanking6915]

Great video. How do you add a dividend slot?

[@lakegirlroxy9852]

Amazing video thank you! .

[@ryoneal26]

Thank you for this video! You are an excellent trainer!

[@carlosnavarrete6616]

Is there a way to add the forward PE ?

[@stevensteven1624]

This is pretty close to what I need in a Stock Watch app, big problem is the COVID related crash makes anything52 difficult at best to use, or at least in my investments it does. But overall really great video & Google Sheet !

[@fightforfreedom21]

Hi, is there a basic spreadsheet you shared with the community outside this course?

[@Amdamguy]

Great videos thank you so much

[@briansnead4787]

Please tell me you have one that can calculate outcomes of exercising vs. selling/buying options contracts....

[@travelindiaindia9217]

Hey Jay...Greetings from India hope you doing good? This is mind blowing idea man a quick question in stead of using google can we use our stock exchange site? Thanks & Regards

[@hongwahliu5056]

Excellent tutorial! I already have a lot of experience with spreadsheets, but I didn't know about the Google Finance function. This makes it so much easier for me to keep track of our family finances. Thank you very much for teaching, your video is so much better than the one I saw before! :-D

[@kailashpatil8807]

Hey , can you help in identifying stocks like fast , slow , stalwarts, cylicals and turn around by peter lynch ideas

[@mcrayfourd]

can you track options this way too?

[@CLOUDY_TALK]

Can we get Excel in which date wise data in updated in next coloum automatically

[@tomigurl415]

Very educational video ! Learned more than just investments. 😁

[@dron121]

Can you show how to create a transaction google sheet(buy/sell daily) to apply on the portfolio?

[@nikoleta1509]

Amazing video. Did not know the potential of google spreadsheet. Thank you

[@skymaster9665]

very helpful, I have a question. Can i get real time sparklines for daily movement of stocks?

[@mariomujica3339]

GRETA JOB THANK YOU

[@seancullen99]

Awesome content, thanks so much. One question that I can't seem to solve myself - what's the formula for the percent weekly change?

[@ostapsulyk]

is there a way to track dividend per share or dividend yield, because googlefinance gives me an error when i try to use provided attributes

[@mark-3466]

Fantastic, the only thing I notice you did not mention, is if you want London stocks you have to put LON: next to the ticker, or else it messes every thing up, (LON:CCL) for carnival .otherwise I really enjoyed your video and learnt loads., Thank you.

[@rancholaflorida1168]

hi. it would be nice if you do spreadsheet of option trading

[@dmehta3476]

You are awesome: thank you so much Michael; please expand on this to include ANNUAL RETURN ; CAGR and TOTAL RETURN as well as Comparison to S&P 500 tracking

[@jeanettemcknight1092]

Great instructional Video. I just recently found this video and did it step by step. The issue I am having is that the market cap shows error for some stock that I am watching. How to find the problem? I checked to make sure the ticker symbol is correct.

[@craigschleppi2573]

Would you be able to tell me how I can get the annual dividend yield for a stock?

[@pamhill8710]

Excellent, many thanks.

[@jnadler6586]

Amazing! Thank you Michael

[@investing2089]

Great spreadsheet Michael. Very useful. 🔥🔥

[@enlaotrapuntadelmund]

Great video, thanks. For sharing mate

[@nileshsarangdhar609]

Yes great work I'm certainly going to try making one. Will you please also make one video using Excel and micros

[@AlejandroLopez-zo7ki]

Thank you very much Michael! This is great!!

[@kalpeshmodi6209]

Hello Mike, How can I add ticker symbol from TSX? Regards,

[@richardm5589]

This was awesome! Thanks Michael.

[@jorgemarien5140]

Great tutorial!!! Very useful and and straight to the point. I was able to get everything to work, but the gradient (Percent Off BUY). I'm not sure I understand why the Mid Value is "Percent 50". Keep up your great informative videos. You are amazingly concise as well as entertaining. Best always.

[@jorgemarien5140]

OK, So it all lohanksoks great. The gradient fixed itself after a day. One thing to note is that when you SORT A>>Z or Z>>A only the cells that are directly linked to the ticker will sort. Everything else will remain in place (# of shares, sector, investment type...). If anyone knows a fix to this then great. Otherwise one will have to add tickers by inserting a row and then copying the formula(s) down every time. Not too complicated, but sorting could mess you up if you are not paying attention. I'm a dividend investor and the Google function "yieldpct" does not seen to work. Wondering how to fix that. Thanks again Michael. You Rock.

[@tomblack6972]

Michael, thanks for walking us through this. Had to laugh though, there is actually quite a lot of info thrown at us who are not familiar with Google Sheets. Thank God for the replay button!

[@MichaelJayValueInvesting]

Haha I hope it was manageable! Hard to cover everything without making the video too long :)

[@4suc6]

Is there any way to fetch historical pe?

[@edwardzachary1426]

Can you have live updates for precious metals spot price and us treasury yeild graphs? Thanks for the video.

[@fitnessvideos101]

Thanks bro 👍... helping me a lot

[@hman2912]

Awesome tutorial. Thank you.

[@MaartenFelix]

Great vid! For some reason color scale is not working in my sheet

[@jordanhicken1999]

This is amazing

[@Kig_Ama]

does this spreadsheet have a reminder or do u have to look for yourself whether stock price hit target price or not?

[@rhyswells8725]

Great Video

[@yapi1994]

Hello Michael, can you make a video about Qudian (QD)?

[@stanleyngumo1]

Hey awesome tutorial! I am based in Kenya, I am trying to set it up but I am only interested in stock listed in Nairobi stock exchange, how the google function does not seem to work for these stock. is the google finance limted only to US stock?

[@yashmodha282]

I think You have to add The Full index Just like this "NASDAQ:AAPL"

[@MrYas18]

Does this work with UK stocks or just US stocks

[@TomasSinkv]

Thank you for showing how to do this! Also, I want to ask, if the function GOOGLEFINANCE works with non-American stocks too?

[@VipulVaibhaw]

Yes, I Tried with NSE(Indian) stocks

[@TomasSinkv]

@@VipulVaibhaw Thanks!

[@kallewarn]

@@VipulVaibhaw Didnt work for me trying to enter tickers from swedish companies. OMX30 listed ones, I might add.

[@VipulVaibhaw]

@@kallewarn Yes. It doesn't work for swedish companies :(

[@yashmodha282]

@@kallewarn I think You have to add The Full index Just like this "NASDAQ:AAPL"

[@stillcrazyhaha]

Thanks Michael, that was great!

[@thedon281]

This was my first spreadsheet that i ever made. without this video i would be nowhere. very informative. however there is a ticker that isn't on google finance, so i tried to use yahoo finance and of course i get an error. is there a workaround or way to use it? thank you :)

[@portal9405]

Well done.

[@marlonreyes3433]

WHAT code i need to use for philippine stock market?

[@redking1831]

I m addicted to the spread sheets

[@shajiauh]

THANK YOU !

[@davidjackson7675]

How can I pull down the data for he indexes? I.e. DJIA or NASDAQ?

[@huanwengan180]

how do you find company tickers?

[@strangergranger10]

Is there a code for what type of dividend a company is paying???

[@pardeepatlas527]

Thanks

[@neilmartin99]

I hope you don't mind but I showed my brother my spreadsheet and he asked me how I learned to create it. I told him I figured it out. He thinks I'm a genius now. : ) Seriously, great video. Do you have a way to insert ex-div dates? If not, any suggestions would be appreciated.

[@StormedTrooper1]

really confused, google finance says the price per stock (one i purhcased) in is 305... but it's 3.50.... But now my spreadsheet is incorrect showing that im gaining over a grand somehow when it should be a little over a 100. I've followed this to a T, this doesn't seem to work for all companies.

[@leihoa]

I need some guidance how to calculate intrinsic value of a stock. I try to read all I can and I am circling around like a mad fish around the hook. I know I the books like Intelligent Investor etc, but haven;t started it yet. Guy, please recommend me good way to get into all important things like reading cashflow, reading balance sheet, what is important, how to calculate cash flow discounted properly etc.

[@MichaelJayValueInvesting]

I have done a video on valuing stocks that might interest you here: kzbin.info/www/bejne/pprTeZZ_eMd0oZo But I will likely cover intrinsic value again in future videos. Hopefully that video helps with the general mindset, thought process, and techniques.

[@leihoa]

@@MichaelJayValueInvesting hi Michael, yes, saw it and really thanks, I liked it a lot! But I am also aiming to crack it down step by step (not the model, general approach etc), plus as much knowledge as possible. I would love some videos from you focusing on each step, how to estimae future ESP, how to estimate potential growth, in variosu scenarious like ultra hossa or 60 % market crash etc. I know it may be obvious for some but I am still learning, thank you for any tips on this.

[@leihoa]

@@franz5255 hehe, I am little bit too small for professor Damodaran. I kinda understand some basic, cashflow to equity, cashflow to busines etc, but when he steps in onto risk models, I am dead. I am not economist or statistician, it is probably far ahead of me, if at all :/

[@neilmartin99]

How do you track additional purchases to stocks you already own? date of purchase. How many shares Cost per share total cost.

[@IsaacWendt]

Is there anyway to get the price when you first add the stock to your watch list?

[@MichaelJayValueInvesting]

If you prefill the price column with a formula before you enter the ticker then yes. It may need to be something like =IFERROR(GOOGLEFINANCE(cell reference),"") which will produce a blank cell if you haven't added the ticker yet.

[@05oscar05]

Hey can you make one for forex how we can use this and forex. Thanks & pls

[@ajaypatole4u]

Hey, Michael please send me this excel sheet immediately. I LOVED it. Will it run on my Indian Stock List.

[@MichaelJayValueInvesting]

Take the time to follow the tutorial and build it yourself :) That is where most of the fun is!

[@ajaypatole4u]

@@MichaelJayValueInvesting yes Sir surely I"ll try it...👍

[@russelindia]

Can someone tell me, when I add Bank of Nova Scotia ( BNS ) shares , I get the price from US , that is US dollars. I want the TSE last price , how can I resolve this issue ?

[@LearnWithMike]

Thanks very useful :)

[@MichaelJayValueInvesting]

No problem, Mike!

[@huanwengan180]

this doesnt appear to work for foreign stocks?

[@georgekyriakoulis]

BEST!!!!!!

[@giritharann1992]

Hii am from India i tried its working but the numbers are not correct here we have two exchanges BSE,NSE i dont know which price is showing ?? @Michael Jay

[@andrewdennis6209]

Awesome tutorial but how would you bring in cryptocurrency prices?

[@kallewarn]

Sadly this doesnt seem to work for foreign stocks. I live in Sweden, and when I try to enter the command =GOOGLEFINANCE(B4) it either shows up as N/A or gives me a wrong number, probably the number of another company. Good video though.

[@teodoro5987]

Tjena! Hade samma problem. Tror det är någon ny uppdatering kanske? Fixade det genom att skriva " =GOOGLEFINANCE(B4;"marketcap")/1000 " för exempelvis market cap. Hoppas det hjälper!

[@superiorsmallcaps6225]

Nice