How to Analyze Stocks in Spreadsheets

In a few steps, you can create a basic stock analyzer spreadsheet. Our post and 5-minute video tutorial will guide you to building a spreadsheet template that charts prices, calculates return and volatility metrics, and identifies lowest and highest daily returns! (Basic template also accessible below).

[youtube=http://youtu.be/hMawNIfdFRo]

As you create this stock analyzer framework, you’ll discover various spreadsheet functions, which include the array formula and GoogleFinance along with statistical functions, such as average, standard deviation, min and max. With these functions, we can automate our spreadsheet template!

Inputs

There are a few elements you need to begin analyzing a stock. First, select a stock you’d like to analyze! Don’t have one in mind? No problem. Pick any public company to start! What is your favorite store? Or favorite beverage? Or tech firm? As an example, let’s choose Google.

Once you’ve picked the company, find its stock symbol or ticker, which is the abbreviation used to identify publicly traded shares of a stock. Google Finance or Yahoo Finance are examples of two sources to find the ticker. In our example, the ticker of Google is GOOG.

Finally, an important element or input to define is the time period. Over what time period do you want to analyze the stock? Are you interested in a stock’s performance over the short-term (say, a day), or over the long-term (several months or years)? In our example, we’ll begin our analysis on July 1, 2012 and end it 1 year later, June 30, 2013.

Okay, now that we’ve got all the important inputs (company, ticker, and time period), let’s start our analysis!

Prices and Returns

Now that we’ve identified the inputs, we must retrieve the data that will form the basis of our analysis – daily stock prices. Using these prices, we can then calculate daily stock returns.

So, how do we access stock prices? There are a couple of ways, such as: 1) Google Finance or Yahoo Finance websites; 2) Bloomberg terminal (if you have access to one!); or 3) directly in your Google spreadsheet with the function Googlefinance. 

The daily return is calculated as (ending price today – ending price yesterday) / ending price yesterday. This is simply the percentage change of the stock price over a given day. Mathematically, this is also equal to ending price today / ending price yesterday – 1. Once we set up the formula in the column, we can just drag the formula down!

Analysis

Curious how the stock price has moved over a given time period? Let’s chart out the stock price. Using a line graph, we can see how the stock price has changed over the past 6 months and change (our defined time period). To create a line graph, you can watch our video here: line graph.

Below is the output of our analysis (scroll to the right). The top portion includes the stock price and the bottom incorporates various stats.

[googleapps domain=”docs” dir=”spreadsheet/pub” query=”key=0ArU-OSCYb_YpdGFPdlhBdUlzWXNONWVRVjFMN2FyYUE&output=html&widget=true” width=”640″ height=”300″ /]

In a nutshell, we can use daily stock returns to understand the rate of return over a given time period, to evaluate how volatile the stock is, to determine the return for a unit of risk, and to see the range of stock price movement – among many more.

Return, Volatility, Risk-Adjusted Returns

Let’s start with absolute return. Rather than simply add up the daily returns over the period, we want to compound them. Why? We want to take interest into account! Compounding returns is simply more accurate. To compound returns in a spreadsheet, we need to be sure to apply the “array” formula. Learn more about this concept here array.

Volatility is an important concept too. Knowing how much volatility you can stomach essentially refers to your risk tolerance. The greater the movement in a stock price, the higher the stock price volatility is. Using daily returns, we measure volatility using the STDEV function. If we are interested in annualized standard deviation, we would multiply this figure by the square root of the 250 trading days. Or, if you are using monthly data, you would multiply by the square root of 12.

Rather than look at return and volatility as standalone figures, why not understand the risk-adjusted return. By simply dividing the stock’s return over its standard deviation, we have a measure of the stock’s return for every unit of risk. In this case, the risk measure we use is standard deviation. There are other measures of risk, such as beta or drawdown, but let’s start with standard deviation for now.

Finally, understanding the range of stock price movement can be helpful in giving you a sense the stock’s high and low. If you are curious, you can also look at the date that corresponds with the high and the low and understand the main driver(s) of the outlier returns.

Access this Spreadsheet

You can find a copy of our spreadsheet in our Sample Spreadsheet section located here. Or, if you select Stock Analysis, you can access a read-only version. IMPORTANT: To edit the spreadsheet, go to ‘File’ and select, ‘Make a Copy’. This will automatically save a copy on your Google drive.  

Have questions? What are other stock analysis topics are you interested in? Let us know in the Comment/Question box below.