Whether you’re training for a marathon or going on an adventure trip, being ready can make a world of difference.
The same is true for the stock market. It’s important to be prepared with a watchlist of fundamentally sound stocks ready to go at right prices.
Whether the market is in rally mode or in a correction, being prepared with a watchlist is key.
Here is a video I’ve prepared to help you learn a simple yet powerful tool of creating your own stock watchlist, which is dynamic and tells you in a snap the status of stocks you are watching.
If you can’t see the video above, click here to see.
Let me know your thoughts on the video, and also share any other way(s) you maintain your own stock watchlist.
Resources:
1. Sample Stock Watchlist
2. Google Spreadsheets Help Center
P.S. This post was originally published in Dec. 2013, and has been republished following a lot of reader questions on this topic.
Superb, simply awesome.
You have saved so many man hours of work with these simple applications. Thanks a tonne.
Happy Investing 🙂
Thanks Sanjeev! Glad you found this valuable.
One query:
Is their any way to directly export the list of stocks from google screener to spreadsheet?
Happy Investing
Sanjeev, you can export the Google spreadsheet this way – Select File > Download as and select a file type (like excel or pdf). Then select Open or Save.
Actually I was asking about screener results being exported, not the spreadsheet 🙁
You can export the results into a Spreadsheet. What I do is copy the relevant data and paste in the Excel sheet I maintain.
Good Stuff. I knew one could create something like this, but never tried. You made it sound so easy. Thanks again!
Thanks Nitin!
Thank you, Vishal! Although I maintain a screener, there are 2 things I learnt from this. One is to be very strict about what gets into the screener (only those within one’s circle of competency, or chosen with some rational method). And second is I have added the 52wk low/high to the screener to get a feel for where the stock stands relative to its recent prices. It would be even better if there were a 3 year low/high (for cigar butt stocks), but not sure where to find it.
Regards,
Rajaram
Indeed Rajaram…knowing the boundaries of your circle and then staying within it is very important while creating your watchlist.
As for your point on 3-year lows and higs, you can open the Google’s 3/5 year price chart for the stock and drag the mouse to the visibly highest and lowest levels to find the numbers and then enter them in the spreadsheet manually.
By the way, in the Sample Watchlist I have shared at the end of the post, I have added a column showing the 1-year charts of all companies in the watchlist. You can edit the header to see the 2-year charts. Regards.
very useful and pretty easy to create , thanks for sharing
Thanks for your feedback, Shyam!
Unlike other portfolio trackers offered by financial websites, blogs, this way of creating a stock watchlist seems different in style, which can be more customized. Excellent..!!
Indeed Upendra. Thanks for your comment!
Vishal,
Superb work.
But one question. Y not use the watchlist provided by screener.in or moneycontrol. In moneycontrol u can also enter ur price to buy and find out whether it is trading higher than ur price or not ?
– Deepak
Thanks Deepak! Yeah, Screener is also an option. I showcased Google as I liked the simplicity of the tool. Regards.
I use Stocks in my iPhone. I’ve a mental watchlist as well.
I feel that this Google thing is a lot of hassle. Why not use Screener.in? It has a watchlist, does all the calculation itself, plus has other nice tools. My math disability was never an issue to do calculate key ratios to aid in decision.
I find Google a handy tool. Screener is also a nice option though.
You have indicated 30% mos but multiplying the iv by 0.6. Pls change it to 0.7. Thanks!
Yeah, it was a mistake that I have already corrected in the sheet. Thanks!
I had prepared this kind of auto updating spreadsheet screener, but using more complex excel macro stuff. And also that was difficult to make changes and maintain. So i had abandoned that. This one is pretty easy. Thanks Vishal for sharing this.
Thanks for your feedback, Prasanna!
Awesome! WIll definitely be using this! 🙂 Thanks a lot
Thanks Bharat! All the best!
Dear Vishal,
Thank you very much for your post , this was very helpful.
I spend lot of time to figure out the way to find historical PEs but was not able to, is there any way to do this with Google Spreadsheet. This is the information which might be very useful , could do similar analysis that Professor Bakshi did in his October Fest 13 where he discusses the importance of buying quality.
Also is there any other way to get this information on historical PE ratios for last 10 years for free.I am stugling to get this kind of information from last 3 weeks but could not.
Great work Vishal. Just wanted to know about how did you find Google finance tags to be used. The strings like pe etc. Can you tell me how to get that info ?
I got my answer…thanks..It is there in one of the link.
Thanks for this useful info
Hi. Sometimes the stock price falls because of bonus issues. How can we account for that in the chart itself? (visually)
One workaround I can think of is to make chart of daily market cap instead of daily stock price. Is this right way?
Thank you for sharing this great piece of advice. This will be of tremendous help. Greetings from Switzerland!
I have a watchlist set in HDFC Securities portal and based on 52 weeks low/high I will get a email alert. Creating watch list with Google Sheet is pretty good option as its simple and free.
One more improvement to this sheet would make this a great tool, getting email alerts on for 52 week low/high so that we can act quickly. Is there any option to set the email alerts ?
Hi Vishal,
Thanks for this very useful information. I just have one small question. Are these Data points from Google finance based on “consolidated financials” or “standalone financials” specially the P/E ratios etc. I find calculated data points based on “consolidated financials” as more accurate and logical as data points based on “standalone financials” can be very misleading in many cases. Also are these ratios like P/E etc. from Google Finance based on last full financial year’s performance OR the last latest 12 months performance.
For example P/E ratios on Moneycontrol are calculated using last twelve months standalone financial in many cases which is very frustrating and misleading as I have to again recalculate everything myself.
Thanks and Regards,
Amit Goyal.
This is Brilliant and extremely useful. Thanks a million!
Thanks Praveen!
Hi Vishal,
I use Google finance website to create a watchlist-it also get refreshed automatically when i open it-(downside is it may not have some of custom columns which you have created-or i am not sure if it may allow to create some columns on the website itself)
additionally if any of the stock which i have bought it let me enter the buy amount etc so that it can track my profits or losses as well.
so if i am most of the things on google finance website – i may not need excel(also- i can completely understand love for excel).
one thing i am still to figure out- i want to capture screenshots of prices at some particular dates- so which means if i am watching a stock from an year and i reviewed it around 4 times- it should be captured in the list automatically so that i can learn from my mistakes- this stock was at this price in feb and this was the pe ratio- why the hell i did not buy it? what was i thinking ?-so i go to comments of that quarter and see it)
because this price is going to be dynamic so the price will get auto-refreshed and will show me the current price not a price of a particular day which i wanted to capture just for my analysis.
Thank you so much for putting the efforts in creating and sharing this video.
Vishal San, What are the GoogleFinance attributes for finding Debt, Debt-to-Equity ration, various Cashflow financials etc.?
thnx Vishal Sir for sharing this. I use screener.in which many of you here might already know. When you login into the site you get all the recent announcements on your watchlist on the homescreen. You also get email alerts regarding stocks in your watchlist.
Dear Sir,
Good work. My query is on Stop/Lock profit tools. On purchase, people for some days keep track and once it enters into profit they do not track them with a hope to maximise further profits. And they repent later of not booking gains. In this case, Stop profit tool is of use to lock into the profit. Say, if one wants lock profits from a 10% fall from the recent high (not 52 week high), the tool shall alert the investor to lock into the profit. If this kind of tool is available, it will help the investors to take a call to lock into profits. This will help in sailing into profits and helps into lock the gains. I request you to consider this stop profit tool also to help on sell side which investors are lacking and very much required. Many thanks.
Hi Vishal,
This is simplicity at its best! You don’t know how many hours you have saved by just sharing this super tool.
Can we also do the same for Mutual Funds as well? Tried doing it but it did not work, not sure why though.
Hi Sir,
This is really useful. How does one carry out analysis of the stock he wants to – other than the ones mentioned in the sheet)? So if I want to analyze Sun Pharma, how should I do that using the Google Sheet? Also how does one identify his circle of competence ? Also what is the formula applied to calculate Intrinsic Value ?
Regards,
Kedar Lele
Very useful spreadsheet. Gr8 work and sharing with others!!
Fantastic !! loved the idea very simple yet effective.
Sir, this is a very useful tools for freshers such as myself. Just one question, does the google stock watchlist tool work for NSE as well? I tried using the NSE scrip codes. the prices were getting updated, but the pe and the 52 week high number is different from what is mentioned in NSE. Thanks.
Thanks a lot. This is quite helpful.