This year I’m trying to add more investing-related content as well as more spreadsheet downloads and tools. A few weeks ago I shared a dividend analysis tool. Today I have another investing-related tool that you can download and use.
Today’s tool is a stock analysis tool in Google Spreadsheets. It’s fairly easy to track and analyze stocks using Google Spreadsheets. Google spreadsheets pulls in stock data every twenty minutes if you use their Google Finance formulas.
You don’t have to mess around with setting up a spreadsheet yourself, though, because I already did the heavy lifting and set up a spreadsheet that will allow you to track whatever stock or group of stocks you want.
Components of the Stock Analysis Tool
As you can see below, there is a variety of data being pulled into the spreadsheet for each stock.
- Stock
- Current Price
- Daily Pct Change
- Daily Price Change
- 52 Week Low
- 52 Week High
- P/E
- Market Cap (Billions)
- EPS
- 1 Year Change
- YTD Change
To analyze a stock all you have to do is enter the stock ticker in column B in the highlighted cells. You can enter as many as 20. In the download I plugged in some health-insurance stocks as an example.
A couple other features of the tool are:
Stock Price Chart – A simple chart of price of the various stocks from the first tab:
Stock P/E Chart – Even more useful than the stock price is comparing the price/earnings, or P/E ratio. There is another tab in the file with a chart comparing the P/E of the stocks from the first tab:
Some additional notes about the stock analysis tool:
- Track up to 20 Stocks
- Stock Data refreshes every 20 minutes
- While Microsoft Excel is usually preferable to Google Spreadsheets – especially for data analysis – there is no easy way to pull in stock data to Excel. Google Spreadsheets has Finance Formulas that can be used to easily pull in stock data.
- Useful for tracking a group of related stocks
- Useful for a quick high-level view of a group of stocks you are interested in
This is the first stock analysis tool I’ve created so if you have any suggestions for additional features please let me know in the comments.
Download a copy of the Stock Analysis Tool
Click to download the Stock Analysis Tool. Once the file loads, go to File->Make a Copy to save your own copy.
Head on over and Download the free 2014 editorial calendar now.
What are your thoughts on the stock analysis tool? Do you have any ideas for additional features? Do you track any specific industries?
Check out some of our favorite personal finance resources:
Are you getting the best credit card rewards? Check out the top cash back credit card offers (updated daily)
Make extra money: 15 ways to make money from your computer
Get the First Chapter Free!
Join our online community and get the first chapter of the book Student Loan Solution absolutely FREE!
Nice spreadsheet, can you add dividend yield and a weighted average column. If someone chooses a large cap vs. a mid one how much of that will be weighted in your 20 stocks. It will give an investor a true allocation by company size what each company would make up of the portfolio.
Charles@gettingarichlife Thanks for the suggestions, Charles. I for sure want to add dividend yield, but unfortunately Google didn’t include an easy formula for it. It requires a table lookup of a URL, which Google limits for each spreadsheet (you can see what I’m talking about if you check out the dividend analysis spreadsheet -> https://www.youngadultmoney.com/2014/01/27/how-much-money-would-you-need-to-live-off-dividends-free-download/). I will do some testing with it, though, and add it in ‘version 2.0’ if it doesn’t slow down the file too much. Weighted average is definitely something I will look into as well.
Very interesting, DC!! This is something we’ll definitely use once we start investing. Thanks for sharing.:-)
Laurie TheFrugalFarmer No problem, hope you find it useful!
Nice! This would be great for someone who has a hands-on approach to investing!
Holly at ClubThrifty Thanks Holly, I think it can be a great way to track how your stocks are doing. Plus once you have data in a spreadsheet there’s pretty much an unlimited number of ways to analyze it.
Very cool DC! The only thing I’d change is adding more dividend features, but you already tackled that with your previous spreadsheet. :) I don’t know that I track any specific industries, but I use screeners all the time when doing analysis.
FrugalRules Yeah the problem with dividend yield is that Google doesn’t have an easy =GoogleFinance() formula for it so unfortunately you have to use a lookup on a table housed in a URL which Google limits per spreadsheet. I also might have saved that for version 2.0 ;) I try to keep tabs on the health insurance industry because it’s the industry I’m in and there really aren’t that many players in it anymore, so it’s pretty easy to keep tabs on. I try to keep my financial analysis skills sharp through this because it’s definitely true that if you don’t use a skill you lose it.
Definitely a useful tool for the active investor. I try not to check out my stocks too often so that I don’t react to temporary market trends.
brokeandbeau That’s fair and I know many investors don’t want to check day-by-day but I do like the potential for tracking your investments in a spreadsheet that you have control over versus having to rely on widgets and tools that are online. Then again, I also just really like messing around with spreadsheets and seeing what is possible with them.
Another great tool DC! I would really like to get more into investing but right now we are super focused finishing funding our emergency fund (3 more months!) . Once that’s done, we’ll probably put a little bit towards investing each month
BudgetforMore I also have been focusing on my emergency fund and once that’s as high as I would like it to be I will start looking into investing outside of my 401k. I have a few stocks in mind, but for the most part I just enjoy analyzing businesses and trying to predict which are priced lower/higher than they should be.
That’s great stuff DC! I usually track all my investments in Quicken but this looks like it has some valuable features.
Brian @ Luke1428 I like the flexibility that spreadsheets offer. Now if only it was easier to import stock data into Excel…
I don’t actively track industries because it seems like a lot of work. I was just waiting for someone to come up with a handy spreadsheet…..
Eyesonthedollar I only really pay attention to health insurance because there are only a few players in the game now and it’s the industry I’m in. I am interested in tech and retail, but there’s so many stocks that I haven’t found the time or will to do it. Though, I have been tracking Target pretty closely because they are based here and I’m interested in where they will go the next 5 years.
Wow, it’s great what you can do with google spreadsheets. I don’t track any specific industries anymore. At one point I had about 10 individual stocks but I moved away from individual stock investments in favor of just a few index funds.
Raquel@Practical Cents Not a bad idea! There are definitely pros and cons to tracking and investing in individual stocks versus index funds. For most people index funds will do the trick. I personally want to continue to practice stock and business analysis and tracking stocks and even a whole industry (like health insurance) keeps me in the habit.
I use that Google Finance formula as well for my own tracking purposes. It’s amazing how easy they make some of this stuff!
Matt @ Mom and Dad Money I really like the Google Finance formulas and wish there was something comparable in Excel. What I don’t like is that you have to do a work-around for dividend yield and the limitations if you try to do anything more advanced with Google Spreadsheets. With that being said, for the purpose of tracking stocks, industries, etc. there isn’t anything simpler out there.
That’s great work there, David. Does it work with mutual funds as well?
DonebyForty That’s a great question. I actually put this post together a month ago so haven’t looked at the formulas in a while, but I *think* there are different formulas that need to be used for mutual funds. If so, that’s my next project ;)
Great job, DC! This is a handy spreadsheet and you definitely have a lot of talent working with them. :) My broker dealer provides tools for me to track my clients’ investments but this would be great for a personal investor to use.
ShannonRyan I’d be interested in seeing the tools your broker dealer has. There seems to be quite a few tools out there but whenever possible I like to pull data into spreadsheets because they are flexible and you can do so much with them.
I didn’t know that they had linking capability! That’s awesome! My spouse wrote a macro several years back to do it, but it never quite did what we wanted it to.
Ugifter Unfortunately Microsoft Excel has no perfect way of pulling in stock data. I know that there are some solutions (such as the macro your spouse wrote) but it’s hard to beat Google Finance because they can easily sync their tools with their finance website data.
With our debt management plan done, investing is something I want to get more into…..and by the sounds of it I’ve got quite a learning curve ahead of me. thanks for the pointer, DC!
DebtChronicles I also have started to look into investing more and it’s been fun to combine my interest in spreadsheets and data with investing as a whole. Looking forward to reading some posts about what you learn and what you decide to invest in!
Whoa your spreadsheets just got extra fancy. Love it.
BudgetBlonde I do what I can : ) Thanks!
Seriously awesome DC. Thanks for sharing, and if you’re inclined to, keep ’em coming! Are you tracking the health care sector as well?
ImpersonalFinance Thanks, glad you like it! I try to keep tabs on the health insurance industry since that’s where I work and it’s a really easy industry to keep track of stock-wise because there aren’t that many companies still in that industry.
DC @ Young Adult Money Charles@gettingarichlife I found that you have the 52 week low and 52 week high mixed up. Other than that, great stuff! Thank you.
pdbartsch DC @ Young Adult Money Thanks for pointing that out!