install.packages("DBI")
install.packages("RPostgres")8 Building Winning Portfolios
In this assignment, you will learn how to query WRDS inside RStudio to download stock return data and evaluate investment strategies.
Background
The S&P 500 is a stock market index made up of about 500 large publicly traded U.S. companies. It is often used as a benchmark for “the stock market” because it represents a large share of the total value of U.S. equities.
A portfolio is a collection of assets. Investors often want to know:
- Should I just buy the market?
- Can I do better by picking a group of stocks?
- Which types of firms tend to perform better over long periods?
In this assignment, you will compare a passive market investment to a portfolio you design yourself.
Part 1: Query WRDS from RStudio
library(tidyverse)
library(DBI)
library(RPostgres)
con <- dbConnect(
RPostgres::Postgres(),
host = "wrds-pgdata.wharton.upenn.edu",
port = 9737,
dbname = "wrds",
sslmode = "require",
user = "____", # enter your username here
password = "____" # enter your password here
# WRDS may need 2-factor authentication via your phone
)Now that we’ve connected to WRDS, we’re going to pull monthly returns from the S&P500 index and also firm-level returns:
sp500 <- dbGetQuery(con, "
SELECT date, vwretd AS ret
FROM crsp.msi
WHERE date >= '2000-01-01'
ORDER BY date
")
# Write the S&P500 csv file to your computer.
sp500 %>% write_csv("sp500_index.csv")
stocks <- dbGetQuery(con, "
SELECT
a.permno,
a.date,
a.ret,
b.ticker,
b.comnam
FROM crsp.msf AS a
LEFT JOIN crsp.msenames AS b
ON a.permno = b.permno
AND a.date BETWEEN b.namedt AND b.nameendt
WHERE a.date >= '2000-01-01'
ORDER BY a.permno, a.date
")
# Write the firm-level csv file to your computer.
stocks %>% write_csv("stocks.csv")
# Close the WRDS connection:
DBI::dbDisconnect(con)Part 2: Inspect the S&P500 data
Answer each of these questions using a query. Recall that sp500 is an index that represents investing a little bit in 500 large, publicly traded companies in the US.
library(tidyverse)
sp500 <- read_csv("sp500_index.csv")
# 1) What is the date range for stock returns?
# 2) Plot the S&P500 returns over time.
# 3) Compounding returns: Suppose an investment has a return of
# 10% each month for a year, and suppose you invest $1 in January.
# In February, your investment would be worth (1) * (1.10) = $1.10.
# In March, you earn 10% on your new amount:
# (1) * (1.10) * (1.10) = $1.21.
# In April: (1) * (1.10)^3 = $1.331.
# Etc. until the following January: (1) * (1.10)^12 = $3.14.
# The formula to find how much your investment is worth is:
# initial_investment * (1 + r)^t
# And if your returns are different each month:
# initial_investment * (1 + r1) * (1 + r2) * ... * (1 + rt)
#
# If you invested $1000 in the S&P500 in January 2000, how much
# would that investment be worth now (at the end of our data)?
sp500 %>%
mutate(cumulative_return = cumprod(1 + ret) * ___) %>%
___
# 4) Plot the value of your $1000 investment over time. When
# was it the highest (when should you have taken your money out)?Part 3: Inspect the stocks data
Now we’ll turn to firm-level data instead of the market-wide index. Note that permno is a permanent number for the stock. Company name (comnam) and ticker can change, but permno will not.
stocks <- read_csv("stocks.csv")
# 1) What am I doing in the query below, and why?
# Then: choose 3 stocks in the resulting list that you want to invest in.
# Note their permnos.
stocks %>%
drop_na(ret) %>%
count(permno) %>%
filter(n == 300) %>%
select(-n) %>%
left_join(stocks) %>%
filter(date == ymd("2000-01-31")) %>%
view()
# Company 1: ____ permno: ____
# Company 2: ____ permno: ____
# Company 3: ____ permno: ____
# 2) Suppose you invest your $1000 in January, 2000 in all three
# of those companies you chose equally (filter by permno).
# How much would that investment be worth now (at the end of our data)?Download this assignment
Here’s a link to download this assignment. Turn in your finished work to Canvas (one copy per group).