---
title: "8 Building Winning Portfolios"
format:
  html:
    self-contained: TRUE
---

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

```{r, eval = F}
install.packages("DBI")
install.packages("RPostgres")
```

```{r, eval = F}
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:

```{r, eval = F}
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.

```{r}
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.

```{r}
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. How much would that 
# investment be worth now (at the end of our data)?


```
