How to Compare and Analyze Stocks in Excel: The Definitive Guide

Stocks in Excel

Do you want to learn how to compare stocks in Excel? It can be a daunting task, but it can be a lot easier with the right guide. In this blog post, we will walk you through the process of comparing stocks in Excel. We will show you how to gather the data that you need and then use it to make informed decisions about your investments. So, if you're ready to learn more about stock analysis in Excel, keep reading!

What is Excel, and what are its uses for stock analysis?

Excel is a spreadsheet application that allows users to store, organize, and manipulate data. Excel can be used for a variety of purposes, but it is particularly well-suited for financial analysis. That's because Excel includes built-in functions that make it easy to calculate important financial metrics, such as return on investment (ROI) and price-to-earnings ratios (P/E ratios).

In addition, Excel's graphing capabilities make it easy to visualize data, which can be helpful when analyzing stocks. For example, you can use Excel to create a line chart that shows how a stock's price has fluctuated over time. This can give you a good sense of whether the stock is volatile or stable.

So, now that you know a little about Excel let's look at how to use it to compare stocks.

How to get stock data in Excel?

Before analyzing stocks in Excel, you need to get your hands on some data. The good news is that there are a number of ways to do this. One option is to copy-paste the data manually from a site like Yahoo Finance. But, of course, this option is time-consuming and ineffective. The other option is to use the Stocks function, which provides you with real-time and historical price data for stocks for free if you have an office 365 account. The other option is to use Wisesheets, which is an Excel stock add-in that provides you with financials, key metrics, growth metrics, real-time price data, dividend data, and more.

You can check out this article we wrote on how to use the last 2 methods to get all the stock data you need in your excel spreadsheet. In this article, we will focus less on the technical side of how to get data and more on how to analyze the stock data once it is on your spreadsheet.

How to compare stocks in Excel?

The best way to compare stocks in Excel is to pick a list of similar stocks based on their industry, market cap, sector, etc., and pick the most relevant key metrics to compare them to. Using Wisesheets, you can see this process is simple where using one formula, you can get all the data for all these semiconductor stocks.

Stock valuation Excel

Depending on the size of the stock list, there are two valuable ways to analyze the data.

Filter through stocks based on criteria

If your stock list is large enough, you can use Excel filters to find the stocks that have the best metrics according t your investing criteria. For example, the stocks that have the highest ROIC and lowest PE.

Wisesheets screener

This allows you to more quickly spot investment opportunities at scale.

Visualize the key metrics across companies

excel stock charts

If your stock list is manageable in size, you could take advantage of Excel graphs and create a visualization that allows you to quickly compare stocks across key metrics.

How to analyze stocks in Excel?

Now that you know how to get stock data in Excel and how to compare stocks, it's time to learn how to analyze them. There are a number of different ways to do this, but we'll focus on two of the most popular methods: fundamental analysis and technical analysis.

Fundamental analysis

Fundamental analysis is a method of stock analysis that focuses on a company's financial statements. This type of analysis can give you a good sense of a company's overall health and its potential for future growth.

Technical analysis

Technical analysis is a method of stock analysis that focuses on price movements. This type of analysis can help you identify trends and make predictions about where a stock is likely to go in the future.

Both fundamental analysis and technical analysis have their pros and cons, so it's important to use both methods when analyzing stocks. In our opinion, the best way to do this is to use Excel to create a financial model that includes both types of analysis. This will give you the most comprehensive picture of a stock's potential.

You can check out this article we wrote on how to create a financial model in Excel. In this article, we walk through the process step-by-step and provide a downloadable template that you can use for your own stocks.

Additional resources on Excel and stock analysis

If you're looking for more resources on Excel and stock analysis, we've got you covered. Here are some articles that we think you'll find helpful:

How to use the StockHistory function

Excel Stock Data – The Ultimate Guide [Live & Historical Stock Data]

The Best Stock Analysis Spreadsheets for Stock investing in 2022


We hope this article has been helpful and that you now have a better understanding of how to compare and analyze stocks in Excel. If you're looking for more resources on Excel and stock analysis, be sure to check out our other articles on the topic.

To your investing success!

Guillermo Valles
 | Website

Hello! I'm a finance enthusiast who fell in love with the world of finance at 15, devouring Warren Buffet's books and streaming Berkshire Hathaway meetings like a true fan.

I started my career in the industry at one of Canada's largest REITs, where I honed my skills analyzing and facilitating over a billion dollars in commercial real estate deals.

My passion led me to the stock market, but I quickly found myself spending more time gathering data than analyzing companies.

That's when my team and I created Wisesheets, a tool designed to automate the stock data gathering process, with the ultimate goal of helping anyone quickly find good investment opportunities.

Today, I juggle improving Wisesheets and tending to my stock portfolio, which I like to think of as a garden of assets and dividends. My journey from a finance-loving teenager to a tech entrepreneur has been a thrilling ride, full of surprises and lessons.

I'm excited for what's next and look forward to sharing my passion for finance and investing with others!

Related Posts