Final

R Markdown

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.

When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:

summary(cars)

##      speed           dist       
##  Min.   : 4.0   Min.   :  2.00  
##  1st Qu.:12.0   1st Qu.: 26.00  
##  Median :15.0   Median : 36.00  
##  Mean   :15.4   Mean   : 42.98  
##  3rd Qu.:19.0   3rd Qu.: 56.00  
##  Max.   :25.0   Max.   :120.00

if (!require("pacman")) install.packages("pacman")

## Loading required package: pacman

pacman::p_load(tidyverse, lubridate, tidyquant, readxl, writexl)

library(tidyverse)
library(lubridate)
library(purrr)

library(tidyquant)

library(readxl)
library(writexl)

#read the excel file
df <- read_excel("~/R/Constituent Companies of the FTSE Nareit All REITs Index_modified_2022.xlsx")

#1.1

#group and summarize the data by Property Sector
sectors <- df %>% group_by(`Property Sector`) %>% summarize()

#list all the Property Sector
sectors$`Property Sector`

##  [1] "Data Centers"    "Diversified"     "Health Care"     "Industrial"     
##  [5] "Infrastructure"  "Lodging/Resorts" "Mortgage"        "Office"         
##  [9] "Residential"     "Retail"          "Self Storage"    "Specialty"      
## [13] "Timber"

#1.2

distinct(df,`Property Subsector`)$`Property Subsector`

##  [1] "Office"               "Industrial"           "Shopping Centers"    
##  [4] "Regional Malls"       "Free Standing"        "Apartments"          
##  [7] "Manufactured Homes"   "Single Family Homes"  "Diversified"         
## [10] "Lodging/Resorts"      "Self Storage"         "Health Care"         
## [13] "Timber"               "Infrastructure"       "Data Centers"        
## [16] "Specialty"            "Home Financing"       "Commercial Financing"

#1.3

#group by property subsector and compute number of firms and average MKV
subsector_summary <- df %>% group_by(`Property Subsector`) %>% 
  summarize(n_firms = n(), avg_mkv = mean(`MKV(millions)`))
head(subsector_summary)

## # A tibble: 6 × 3
##   `Property Subsector` n_firms avg_mkv
##   <chr>                  <int>   <dbl>
## 1 Apartments                16   7301.
## 2 Commercial Financing      19   1245.
## 3 Data Centers               2  44723.
## 4 Diversified               15   1949.
## 5 Free Standing             12   6515.
## 6 Health Care               15   6474.

#2.1

df <- select(df, -No)

#rename columns
df <- rename(df, `Investment_Sector` = `Investment Sector`, `Property_Sector` = `Property Sector`, `Property_Subsector` = `Property Subsector`, `MKV_mio` = `MKV(millions)`)

#2.2

# convert columns to categorical data
df <- df %>% 
  mutate(`Investment_Sector` = as.factor(`Investment_Sector`),
         `Property_Sector` = as.factor(`Property_Sector`),
         `Property_Subsector` = as.factor(`Property_Subsector`))

# convert columns to categorical data
df <- df %>% 
  mutate_at(vars(`Investment_Sector`, `Property_Sector`, `Property_Subsector`),as.factor)
head(df)

## # A tibble: 6 × 6
##   Company                               Symbols Invest…¹ Prope…² Prope…³ MKV_mio
##   <chr>                                 <chr>   <fct>    <fct>   <fct>     <dbl>
## 1 Alexandria Real Estate Equities, Inc. ARE     Equity   Office  Office   23776 
## 2 Boston Properties, Inc.               BXP     Equity   Office  Office   10591.
## 3 Kilroy Realty Corporation             KRC     Equity   Office  Office    4513.
## 4 Vornado Realty Trust                  VNO     Equity   Office  Office    3990.
## 5 Cousins Properties Incorporated       CUZ     Equity   Office  Office    3830.
## 6 Highwoods Properties, Inc.            HIW     Equity   Office  Office    2942.
## # … with abbreviated variable names ¹Investment_Sector, ²Property_Sector,
## #   ³Property_Subsector

#3.1

# select firms with 'Investment_Sector' being equal to 'Equity'
equity_firms <- df %>% filter(`Investment_Sector` == "Equity")

#3.2

# Compute the sum of 'MKV_mio' by 'Property_Sector' category
mkv_summary <- equity_firms %>%
  group_by(`Property_Sector`) %>%
  summarize(`MKV` = sum(`MKV_mio`))
mkv_summary <- mkv_summary %>%
  mutate(`MKV_text` = paste0("$", format(round(`MKV`, 2), big.mark = ",")))
head(mkv_summary)

## # A tibble: 6 × 3
##   Property_Sector     MKV MKV_text  
##   <fct>             <dbl> <chr>     
## 1 Data Centers     89445. $ 89,445.1
## 2 Diversified      29235. $ 29,234.8
## 3 Health Care      97112. $ 97,111.7
## 4 Industrial      151856. $151,855.6
## 5 Infrastructure  188749. $188,748.8
## 6 Lodging/Resorts  33215. $ 33,214.9

#3.3

mkv_summary <- mkv_summary %>%
  arrange(desc(`MKV`))

ggplot(mkv_summary, aes(x = `Property_Sector`, y = `MKV`, fill = `Property_Sector`)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  theme_tq() +
  ggtitle("Market Value by Equity Property Sector ($M)") +
  xlab("Property Sector") +
  ylab("Market Value")

#4

symbols <- c("HPP", "PGRE", "DEA", "PDM", "ESRT", "BDN", "OPI", "ONL", "CIO", "FSP", "CMCT", "NYC", "PLD", "REXR", "COLD", "EGP", "FR", "STAG", "TRNO", "IIPR", "LXP", "PLYM", "INDT", "ILPT", "KIM", "REG", "FRT", "BRX", "KRG", "PECO", "SITC", "SKT", "ROIC", "UE", "IVT", "AKR", "ALEX", "BFS", "RPT", "UBA", "WSR", "CTO", "UBP", "WHLR", "SPG", "MAC", "CBL", "O", "STOR", "NNN", "ADC", "SRC", "EPRT", "FCPT", "GTY", "NTST", "RTL", "PSTL", "PINE", "AVB", "EQR", "MAA", "ESS", "UDR", "CPT", "AIRC", "IRT", "ELME", "VRE", "NXRT", "AIV", "CSR", "BRT", "CLPR", "BHM", "SUI", "ELS", "UMH", "INVH", "AMH", "WPC", "BNL", "JBGS", "AAT", "GNL", "SVC", "ALX", "AHH", "GOOD", "STAR", "OLP", "MDV", "MDRR", "SQFT", "GIPR", "HST", "RHP", "APLE", "PK", "SHO", "PEB", "DRH", "RLJ", "XHR", "INN", "CLDT", "HT", "BHR", "AHT", "SOHO", "IHT", "PSA", "EXR", "CUBE", "LSI", "NSA", "SELF", "WELL", "VTR", "PEAK", "HR", "MPW", "OHI", "DOC", "SBRA", "NHI", "CTRE", "LTC", "CHCT", "UHT", "GMRE", "DHC", "WY", "RYN", "PCH", "AMT", "CCI", "SBAC", "UNIT", "CORR", "PW", "EQIX", "DLR", "VICI", "IRM", "GLPI", "LAMR", "EPR", "OUT", "SAFE", "FPI", "LAND")
start_date <- as.Date("2010-01-01")
end_date <- as.Date("2022-12-31")
stock_data <- tq_get(symbols, get = "stock.prices", from = start_date, to = end_date)
adj_stock_data <- select(stock_data, symbol, date, adjusted)
colnames(adj_stock_data)[3] <- " "
print(adj_stock_data)

## # A tibble: 399,627 × 3
##    symbol date         ` `
##    <chr>  <date>     <dbl>
##  1 HPP    2010-06-24  11.6
##  2 HPP    2010-06-25  11.8
##  3 HPP    2010-06-28  11.6
##  4 HPP    2010-06-29  11.6
##  5 HPP    2010-06-30  11.5
##  6 HPP    2010-07-01  11.4
##  7 HPP    2010-07-02  11.4
##  8 HPP    2010-07-06  11.2
##  9 HPP    2010-07-07  11.0
## 10 HPP    2010-07-08  11.0
## # … with 399,617 more rows

Final

Zolboo Baatarkhuu 111035091

2023-01-13

R Markdown

Including Plots