# A Beginner‘s Guide to Comparative Stock Analysis using Quandl Free Data

- Canonical: https://33rdsquare.com/comparative-stock-analysis/
- Published: 2024-09-03
- Author: Jordan Brown
- Categories: [Artificial Intelligence & Machine Learning & ChatGPT](https://33rdsquare.com/category/tech/ai/)

---

## Introduction

Comparative stock analysis is a fundamental technique used by investors and analysts to evaluate the relative performance and attractiveness of different stocks. By comparing key financial metrics and ratios across companies, you can gain insights into which stocks may be undervalued or have greater growth potential.

While professional investors often rely on expensive Bloomberg terminals and financial databases, it‘s now possible for anyone to access high-quality stock market data for free thanks to services like Quandl. In this tutorial, we‘ll walk through how to retrieve, analyze, and compare historical price data for several stocks using the Quandl API and the R programming language.

Whether you‘re an aspiring data scientist looking to learn more about financial analysis, or just a curious investor who wants to dig deeper into stock evaluation, this guide will provide you with a practical framework and the tools you need to get started. Let‘s dive in!

## What is Quandl?

Quandl is a leading provider of financial, economic, and alternative data for investment professionals. While they offer a range of premium datasets for institutional investors, they also have an excellent free tier that provides access to end-of-day stock prices, key financial indicators, and economic data.

Some of the key benefits of Quandl include:

- Consistent, well-formatted datasets that are easy to work with
- Extensive documentation and example code in multiple languages
- A generous free tier that‘s sufficient for most individual investors
- Affordable paid plans for those who need access to premium data or higher API limits

In this tutorial, we‘ll be focusing on Quandl‘s free end-of-day stock price data, which includes daily open, high, low, close prices and trading volume for most US stocks and ETFs. This data is sourced from the NYSE and NASDAQ, and goes back to 1980 for many symbols.

## Setting up the System

To get started, you‘ll need to sign up for a free Quandl account and obtain an API key. Here are the steps:

1. Go to [https://www.quandl.com/](https://www.quandl.com/) and click "Sign Up" in the top right corner
2. Enter your email address and a password, or sign up with Google/Facebook
3. Once logged in, click on your name in the top right corner, and then "Account Settings"
4. Your API key will be displayed at the top of the page. Keep this handy as we‘ll need it shortly!

Next, make sure you have R and RStudio installed on your computer. If you don‘t have them already, you can download them from the following links:

- R: [https://cran.r-project.org/](https://cran.r-project.org/)
- RStudio: [https://www.rstudio.com/products/rstudio/download/](https://www.rstudio.com/products/rstudio/download/)

Finally, install the following R packages which we‘ll use throughout this tutorial:

```
install.packages(c("Quandl", "tidyverse", "tidyquant", "ggplot2", "lubridate"))
```

## Retrieving Stock Data from Quandl

Now we‘re ready to start pulling some stock data from Quandl. We‘ll use the `Quandl()` function to retrieve end-of-day prices for a few well-known stocks: Apple (AAPL), Amazon (AMZN), Facebook (FB), and Google (GOOG).

First, make sure to load the required libraries and set your Quandl API key:

```
library(Quandl)
library(tidyverse)
library(lubridate)

Quandl.api_key("<YOUR_API_KEY>")
```

Next, let‘s define a vector of the stock symbols we want to analyze, and use `lapply()` to call the `Quandl()` function on each symbol. We‘ll specify a start date of January 1, 2020 and an end date of December 31, 2022 to focus on recent price history.

```
symbols <- c("AAPL", "AMZN", "FB", "GOOG")

stock_data <- lapply(symbols, function(x) {
  Quandl(paste0("WIKI/", x),
         start_date = "2020-01-01",
         end_date = "2022-12-31",
         collapse = "daily")
})

names(stock_data) <- symbols
```

We now have a named list called `stock_data` that contains a data frame of price history for each stock. Each data frame has columns for date, open, high, low, close, volume, ex-dividend, split ratio, and adjusted prices.

## Preparing the Data for Analysis

Before we start analyzing the data, we‘ll do some cleaning and prep work. First, we‘ll combine all the data frames into a single data frame and add a column to indicate which symbol each row belongs to.

```
stock_data_combined <- bind_rows(stock_data, .id = "symbol")
```

Next, we‘ll convert the `Date` column to a proper date type and create some additional date-related columns that will be useful for our analysis:

```
stock_data_combined <- stock_data_combined %>%
  mutate(date = ymd(Date),
         year = year(date),
         month = month(date),
         month_name = month.abb[month],
         week = week(date),
         weekday = weekdays(date))
```

## Analyzing Price Trends

Now that our data is cleaned up and prepped, let‘s start by looking at some high level price trends. We‘ll visualize the close price over the full 3 year period for all 4 stocks:

```
ggplot(stock_data_combined, aes(x = date, y = Close, color = symbol)) +
  geom_line(size = 1) +
  labs(title = "Closing Price Over Time",
       x = "", y = "Closing Price ($)") +
  scale_color_manual(values = c("AAPL" = "darkgrey",
                                "AMZN" = "blue",
                                "FB" = "red",
                                "GOOG" = "orange")) +
  theme_minimal()
```

This gives us a nice overview of how the stocks have performed relative to each other. We can see that Amazon and Google have had the highest absolute prices, but all 4 stocks saw significant price appreciation over this period.

Next, let‘s look at the year-over-year performance of each stock by calculating the annual returns:

```
stock_data_combined %>%
  group_by(symbol, year) %>%
  summarize(start_price = first(Close),
            end_price = last(Close),
            return = (end_price - start_price) / start_price) %>%
  ggplot(aes(x = year, y = return, fill = symbol)) +
  geom_col(position = "dodge") +
  labs(title = "Annual Stock Returns",
       x = "", y = "Annual Return (%)") +
  scale_fill_manual(values = c("AAPL" = "darkgrey",
                               "AMZN" = "blue",
                               "FB" = "red",
                               "GOOG" = "orange")) +
  theme_minimal()
```

This chart shows that while all 4 stocks delivered positive returns in both 2020 and 2021, Facebook lagged its peers significantly. We also see that 2022 was a more challenging year overall, with Amazon actually posting a negative annual return.

## Comparing Financial Ratios

While price trends are important, as fundamental investors we also care about valuation ratios and other financial metrics. One of the most widely used ratios is the price-to-earnings (P/E) ratio, which compares a company‘s stock price to its earnings per share. A higher P/E ratio indicates that investors are willing to pay more for each dollar of earnings, which could reflect higher growth expectations.

To calculate P/E ratios, we‘ll need to pull in earnings per share data from a different Quandl table. We can use the `QFS` table, which provides key financial statement metrics sourced from SEC filings.

Here‘s an example of how to pull annual EPS data for Apple and calculate the P/E ratio over time:

```
# Pull Apple EPS data from Quandl
aapl_eps <- Quandl("QFS/AAPL_EARNINGSPERSHAREDILUTED_ART",
                   start_date = "2020-01-01",
                   end_date = "2022-12-31")

aapl_pe <- stock_data$AAPL %>%
  inner_join(aapl_eps, by = c("Year" = "DATE")) %>%
  mutate(pe_ratio = Close / DILUTEDEPS) %>%
  select(Year, pe_ratio)

ggplot(aapl_pe, aes(x = Year, y = pe_ratio)) +
  geom_col(fill = "darkgrey") +
  labs(title = "Apple P/E Ratio",
       x = "", y = "P/E Ratio") +
  theme_minimal()
```

We could repeat this process for the other stocks to compare their P/E ratios over time.

Other valuation ratios like price-to-sales (P/S) and price-to-book (P/B) can be calculated in a similar way. We could also look at profitability ratios like gross margin and net income margin to assess the operational efficiency of each company.

The `QFS` tables in Quandl provide a range of financial statement metrics that can be used for this type of comparative analysis. Check the documentation for details on what‘s available.

## Identifying Top Performers

Finally, let‘s try to programmatically identify which stock had the best performance over this 3-year period. One simple way to rank the stocks would be to look at the total cumulative return.

```
stock_data_combined %>%
  group_by(symbol) %>%
  summarize(total_return = last(Close) / first(Close) - 1) %>%
  arrange(desc(total_return))
```

This tells us that Google had the highest total return over the period at 97%, followed by Apple at 90%, and Amazon at 49%. Facebook was the laggard at just 2% cumulative return.

Of course, a more robust analysis would look at risk-adjusted returns (e.g. Sharpe ratio) rather than just absolute returns. We could also incorporate other fundamental factors beyond just stock price performance.

## Conclusion

This tutorial provided a practical example of how to conduct comparative stock analysis using free data from Quandl. We covered the basics of retrieving, cleaning, and visualizing stock price data, as well as some fundamental ratio analysis using Quandl‘s `QFS` tables.

The framework and code samples provided here can be easily extended to cover more stocks, date ranges, and metrics. You could also integrate other free data sources like SEC filings to broaden the scope of your analysis.

However, it‘s important to note the limitations of this type of analysis. Historical price trends are not necessarily predictive of future returns, and focusing too narrowly on a small set of metrics can lead to overlooking other important factors.

Nonetheless, comparative stock analysis is a valuable tool to have in your investing toolkit. With some practice and by following a disciplined, data-driven approach, retail investors can gain meaningful insights to inform their investment decisions. As always, be sure to do your own research and consider your individual financial goals and risk tolerance before making any trades. Happy analyzing!

---

Source: [A Beginner‘s Guide to Comparative Stock Analysis using Quandl Free Data](https://33rdsquare.com/comparative-stock-analysis/)