Stock Analysis Tool Using Google Spreadsheets [Free Download] (2024)

By David Carlson / Last updated: February 11, 2016 / Download, Investing

We may receive compensation from companies mentioned within this post via affiliate links. Read our full advertiser disclosure. Opinions, reviews, analyses & recommendations are the author’s alone, and have not been reviewed, endorsed or approved by any of these entities.

324 Shares

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.

The data being pulled in includes:

Stock

Current Price

Daily Pct Change

Daily Price Change

52 Week Low

52 Week High

Market Cap (Billions)

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 our FREE Automated Budget Spreadsheet and our Student Loan Spreadsheet

Get the First Chapter Free!

Join our online community and get the first chapter of the book Student Loan Solution absolutely FREE!

324 Shares

David Carlson

David Carlson is the founder of Young Adult Money. He is a nationally recognized speaker and the author of Student Loan Solution (2019) and Hustle Away Debt (2016). His opinions have been featured on such media outlets as The New York Times, The Washington Post, Cheddar, NBC's KARE11, and more.

Connect:

Comments Policy

Comments, responses, and other user-generated content is not provided or commissioned by this site or our advertisers. Responses have not been reviewed, approved, or otherwise endorsed by this website or its advertisers. It is not the responsibility of advertisers or this website to ensure that all comments and/or questions are answered. With all that being said, please comment away!

Reader Interactions

Comments

Charles@gettingarichlife says
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.
- DC @ Young Adult Money says
  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.
Laurie TheFrugalFarmer says
Very interesting, DC!! This is something we’ll definitely use once we start investing. Thanks for sharing.:-)
- DC @ Young Adult Money says
  Laurie TheFrugalFarmer No problem, hope you find it useful!
Holly at ClubThrifty says
Nice! This would be great for someone who has a hands-on approach to investing!
See Also
Liquidity Report Template
- DC @ Young Adult Money says
  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.
FrugalRules says
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.
- DC @ Young Adult Money says
  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.
brokeandbeau says
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.
- DC @ Young Adult Money says
  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.
BudgetforMore says
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
- DC @ Young Adult Money says
  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.
Brian @ Luke1428 says
That’s great stuff DC! I usually track all my investments in Quicken but this looks like it has some valuable features.
- DC @ Young Adult Money says
  Brian @ Luke1428 I like the flexibility that spreadsheets offer. Now if only it was easier to import stock data into Excel…
Eyesonthedollar says
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…..
- DC @ Young Adult Money says
  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.
Raquel@Practical Cents says
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.
- DC @ Young Adult Money says
  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.
Matt @ Mom and Dad Money says
I use that Google Finance formula as well for my own tracking purposes. It’s amazing how easy they make some of this stuff!
- DC @ Young Adult Money says
  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.
DonebyForty says
That’s great work there, David. Does it work with mutual funds as well?
- DC @ Young Adult Money says
  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 ;)
ShannonRyan says
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.
- DC @ Young Adult Money says
  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.
Ugifter says
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.
- DC @ Young Adult Money says
  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.
DebtChronicles says
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!
- DC @ Young Adult Money says
  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!
BudgetBlonde says
Whoa your spreadsheets just got extra fancy. Love it.
- DC @ Young Adult Money says
  BudgetBlonde I do what I can : ) Thanks!
ImpersonalFinance says
Seriously awesome DC. Thanks for sharing, and if you’re inclined to, keep ’em coming! Are you tracking the health care sector as well?
- DC @ Young Adult Money says
  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.
pdbartsch says
DC @ Young Adult MoneyCharles@gettingarichlife I found that you have the 52 week low and 52 week high mixed up. Other than that, great stuff! Thank you.
DC @ Young Adult Money says
pdbartschDC @ Young Adult Money Thanks for pointing that out!