Investment Data Engineering and Analytics Group Project

1 Executive Summary

This project developed an end-to-end investment analytics workflow that transformed data from multiple financial sources into meaningful business insights. Through automated data collection, cleaning, statistical modeling, and visualization, the project evaluated historical stock performance, portfolio risk, macroeconomic conditions, financial news, and investor preferences within a single reproducible framework.

The analyses demonstrated how combining market data with statistical techniques can improve the understanding of investment behavior. Regression modeling showed how overall market performance and changes in market volatility influenced individual stock returns, while risk metrics and portfolio analyses highlighted differences in investment performance across the selected securities. The conjoint survey further provided insight into the investment characteristics most valued by respondents, complementing the quantitative market analysis with investor preference data.

The resulting datasets, visualizations, and statistical outputs provide a foundation for data-driven investment evaluation and future financial analysis. Although the project relies on live financial data that change as markets update, the analytical workflow remains fully reproducible and can be rerun to generate current results. Overall, the project illustrates how modern data engineering and business analytics techniques can be integrated to support informed investment decision-making.

2 Project purpose

This project builds reproducible investment and class-survey datasets from several external sources. Massive supplies the main daily stock-price data, Alpha Vantage supplies company fundamentals, FRED supplies macroeconomic indicators, and rvest collects finance headlines. The report cleans and combines these sources, calculates investment measures, creates charts, and exports CSV files for the group.

3 Setup

required_packages <- c(
  "httr", "jsonlite", "dplyr", "tidyr", "purrr", "readr",
  "lubridate", "ggplot2", "quantmod", "rvest", "stringr",
  "tidytext", "scales", "knitr", "zoo", "readxl", "plotly"
)

missing_packages <- required_packages[
  !required_packages %in% rownames(installed.packages())
]

if (length(missing_packages) > 0) {
  install.packages(missing_packages, repos = "https://cloud.r-project.org")
}

invisible(lapply(required_packages, library, character.only = TRUE))

knitr::opts_chunk$set(
  echo = TRUE,
  message = FALSE,
  warning = FALSE,
  fig.width = 10,
  fig.height = 6
)

dir.create("output", showWarnings = FALSE)
dir.create("figures", showWarnings = FALSE)

massive_key <- Sys.getenv("MASSIVE_API_KEY")
alpha_key <- Sys.getenv("ALPHAVANTAGE_API_KEY")

if (!nzchar(massive_key)) {
  message("MASSIVE_API_KEY is missing. Yahoo Finance will be used as a fallback.")
}

if (!nzchar(alpha_key)) {
  message("ALPHAVANTAGE_API_KEY is missing. Fundamentals will be skipped.")
}

ticker_table <- tibble::tribble(
  ~ticker, ~company_name, ~asset_type, ~portfolio_weight,
  "AAPL", "Apple", "Stock", 0.20,
  "MSFT", "Microsoft", "Stock", 0.20,
  "NVDA", "NVIDIA", "Stock", 0.20,
  "AMZN", "Amazon", "Stock", 0.15,
  "JPM", "JPMorgan Chase", "Stock", 0.10,
  "SPY", "SPDR S&P 500 ETF", "ETF", 0.15
)

tickers <- ticker_table$ticker

end_date <- Sys.Date() - 1
start_date <- end_date - lubridate::years(2) + lubridate::days(7)
risk_free_rate <- 0.04
initial_investment <- 10000

ticker_table %>%
  knitr::kable(caption = "Selected investments and portfolio weights")

Selected investments and portfolio weights

ticker	company_name	asset_type	portfolio_weight
AAPL	Apple	Stock	0.20
MSFT	Microsoft	Stock	0.20
NVDA	NVIDIA	Stock	0.20
AMZN	Amazon	Stock	0.15
JPM	JPMorgan Chase	Stock	0.10
SPY	SPDR S&P 500 ETF	ETF	0.15

4 Massive API stock data

The function below sends an HTTP GET request to Massive’s daily aggregate endpoint. It checks the HTTP status, checks the API response, converts timestamps to dates, and returns a consistent table.

get_massive_stock <- function(ticker, from_date, to_date, api_key) {
  if (!nzchar(api_key)) {
    stop("MASSIVE_API_KEY is not available.")
  }

  endpoint <- paste0(
    "https://api.massive.com/v2/aggs/ticker/", ticker,
    "/range/1/day/", from_date, "/", to_date
  )

  response <- httr::GET(
    endpoint,
    query = list(
      adjusted = "true",
      sort = "asc",
      limit = 50000,
      apiKey = api_key
    ),
    httr::timeout(30)
  )

  httr::stop_for_status(response)
  parsed <- jsonlite::fromJSON(
    httr::content(response, "text", encoding = "UTF-8"),
    simplifyDataFrame = TRUE
  )

  if (!is.null(parsed$error)) stop(parsed$error)
  if (!is.null(parsed$message) && is.null(parsed$results)) stop(parsed$message)
  if (is.null(parsed$results) || nrow(parsed$results) == 0) {
    stop(paste("No Massive data returned for", ticker))
  }

  result <- as.data.frame(parsed$results)

  tibble::tibble(
    ticker = ticker,
    date = as.Date(lubridate::as_datetime(result$t / 1000, tz = "America/New_York")),
    open = as.numeric(result$o),
    high = as.numeric(result$h),
    low = as.numeric(result$l),
    close = as.numeric(result$c),
    volume = as.numeric(result$v),
    vwap = if ("vw" %in% names(result)) as.numeric(result$vw) else NA_real_,
    transactions = if ("n" %in% names(result)) as.numeric(result$n) else NA_real_,
    data_source = "Massive API"
  )
}

5 Yahoo Finance fallback

This fallback keeps the report usable when a key is absent or an individual Massive request fails. The data_source column clearly records which source supplied each ticker.

get_yahoo_stock <- function(ticker, from_date, to_date) {
  x <- quantmod::getSymbols(
    ticker,
    src = "yahoo",
    from = from_date,
    to = to_date + 1,
    auto.assign = FALSE,
    warnings = FALSE
  )

  colnames(x) <- c("open", "high", "low", "close", "volume", "adjusted")

  tibble::tibble(
    ticker = ticker,
    date = as.Date(zoo::index(x)),
    open = as.numeric(x[, "open"]),
    high = as.numeric(x[, "high"]),
    low = as.numeric(x[, "low"]),
    close = as.numeric(x[, "adjusted"]),
    volume = as.numeric(x[, "volume"]),
    vwap = NA_real_,
    transactions = NA_real_,
    data_source = "Yahoo Finance fallback"
  )
}

6 Download and combine stock prices

stock_results <- purrr::map(tickers, function(symbol) {
  tryCatch(
    {
      get_massive_stock(symbol, start_date, end_date, massive_key)
    },
    error = function(e) {
      message("Massive request failed for ", symbol, ": ", conditionMessage(e))
      message("Using Yahoo Finance fallback for ", symbol, ".")
      get_yahoo_stock(symbol, start_date, end_date)
    }
  )
})

stock_prices <- dplyr::bind_rows(stock_results) %>%
  dplyr::arrange(ticker, date) %>%
  dplyr::distinct(ticker, date, .keep_all = TRUE)

readr::write_csv(stock_prices, "output/stock_prices.csv")

stock_prices %>%
  dplyr::group_by(ticker, data_source) %>%
  dplyr::summarise(
    first_date = min(date),
    last_date = max(date),
    observations = dplyr::n(),
    .groups = "drop"
  ) %>%
  knitr::kable(caption = "Downloaded stock data")

Downloaded stock data

ticker	data_source	first_date	last_date	observations
AAPL	Yahoo Finance fallback	2024-08-12	2026-08-03	495
AMZN	Yahoo Finance fallback	2024-08-12	2026-08-03	495
JPM	Yahoo Finance fallback	2024-08-12	2026-08-03	495
MSFT	Yahoo Finance fallback	2024-08-12	2026-08-03	495
NVDA	Yahoo Finance fallback	2024-08-12	2026-08-03	495
SPY	Yahoo Finance fallback	2024-08-12	2026-08-03	495

7 Data-quality checks

data_quality <- stock_prices %>%
  dplyr::group_by(ticker) %>%
  dplyr::summarise(
    observations = dplyr::n(),
    duplicate_dates = sum(duplicated(date)),
    missing_open = sum(is.na(open)),
    missing_close = sum(is.na(close)),
    missing_volume = sum(is.na(volume)),
    nonpositive_close = sum(close <= 0, na.rm = TRUE),
    first_date = min(date),
    last_date = max(date),
    .groups = "drop"
  )

readr::write_csv(data_quality, "output/data_quality_summary.csv")
knitr::kable(data_quality, caption = "Stock-data quality summary")

Stock-data quality summary

ticker	observations	duplicate_dates	missing_open	missing_close	missing_volume	nonpositive_close	first_date	last_date
AAPL	495	0	0	0	0	0	2024-08-12	2026-08-03
AMZN	495	0	0	0	0	0	2024-08-12	2026-08-03
JPM	495	0	0	0	0	0	2024-08-12	2026-08-03
MSFT	495	0	0	0	0	0	2024-08-12	2026-08-03
NVDA	495	0	0	0	0	0	2024-08-12	2026-08-03
SPY	495	0	0	0	0	0	2024-08-12	2026-08-03

8 Returns, moving averages, volume trends, and volatility

technical_indicators <- stock_prices %>%
  dplyr::group_by(ticker) %>%
  dplyr::arrange(date, .by_group = TRUE) %>%
  dplyr::mutate(
    daily_return = close / dplyr::lag(close) - 1,
    sma_20 = zoo::rollmean(close, 20, fill = NA, align = "right"),
    sma_50 = zoo::rollmean(close, 50, fill = NA, align = "right"),
    average_volume_20 = zoo::rollmean(volume, 20, fill = NA, align = "right"),
    rolling_volatility_20 = zoo::rollapply(
      daily_return,
      width = 20,
      FUN = function(x) stats::sd(x, na.rm = TRUE) * sqrt(252),
      fill = NA,
      align = "right"
    )
  ) %>%
  dplyr::ungroup()

daily_returns <- technical_indicators %>%
  dplyr::select(ticker, date, daily_return) %>%
  dplyr::filter(!is.na(daily_return))

readr::write_csv(daily_returns, "output/daily_returns.csv")
readr::write_csv(technical_indicators, "output/technical_indicators.csv")

9 Investment risk metrics

risk_metrics <- technical_indicators %>%
  dplyr::filter(!is.na(daily_return)) %>%
  dplyr::group_by(ticker) %>%
  dplyr::summarise(
    start_date = min(date),
    end_date = max(date),
    trading_days = dplyr::n(),
    total_return = prod(1 + daily_return, na.rm = TRUE) - 1,
    annualized_return = prod(1 + daily_return, na.rm = TRUE)^(252 / dplyr::n()) - 1,
    annualized_volatility = stats::sd(daily_return, na.rm = TRUE) * sqrt(252),
    sharpe_ratio = (annualized_return - risk_free_rate) / annualized_volatility,
    max_drawdown = min(
      cumprod(1 + daily_return) / cummax(cumprod(1 + daily_return)) - 1,
      na.rm = TRUE
    ),
    average_daily_volume = mean(volume, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  dplyr::arrange(dplyr::desc(sharpe_ratio))

readr::write_csv(risk_metrics, "output/risk_metrics.csv")

risk_metrics %>%
  dplyr::mutate(
    dplyr::across(
      c(total_return, annualized_return, annualized_volatility, max_drawdown),
      scales::percent,
      accuracy = 0.1
    ),
    sharpe_ratio = round(sharpe_ratio, 2),
    average_daily_volume = scales::comma(average_daily_volume)
  ) %>%
  knitr::kable(caption = "Return and risk metrics")

Return and risk metrics

ticker	start_date	end_date	trading_days	total_return	annualized_return	annualized_volatility	sharpe_ratio	max_drawdown	average_daily_volume
JPM	2024-08-13	2026-08-03	494	78.2%	34.3%	25.2%	1.20	-24.4%	9,397,287
SPY	2024-08-13	2026-08-03	494	45.4%	21.1%	16.8%	1.02	-18.8%	66,316,273
AMZN	2024-08-13	2026-08-03	494	70.3%	31.2%	34.3%	0.79	-30.9%	44,701,721
NVDA	2024-08-13	2026-08-03	494	89.9%	38.7%	45.5%	0.76	-36.9%	211,159,111
AAPL	2024-08-13	2026-08-03	494	40.5%	19.0%	28.9%	0.52	-33.4%	51,909,754
MSFT	2024-08-13	2026-08-03	494	21.8%	10.6%	28.8%	0.23	-34.5%	26,401,851

10 Portfolio returns

The portfolio uses the weights defined in the project-settings section of this R Markdown file. Returns are calculated only for dates shared by all investments.

returns_wide <- daily_returns %>%
  tidyr::pivot_wider(names_from = ticker, values_from = daily_return) %>%
  tidyr::drop_na(dplyr::all_of(tickers)) %>%
  dplyr::arrange(date)

weights <- ticker_table$portfolio_weight
names(weights) <- ticker_table$ticker
weights <- weights[tickers]
weights <- weights / sum(weights)

portfolio_performance <- returns_wide %>%
  dplyr::mutate(
    portfolio_return = as.numeric(as.matrix(dplyr::select(., dplyr::all_of(tickers))) %*% weights),
    portfolio_value = initial_investment * cumprod(1 + portfolio_return),
    cumulative_return = portfolio_value / initial_investment - 1,
    drawdown = portfolio_value / cummax(portfolio_value) - 1
  ) %>%
  dplyr::select(date, portfolio_return, portfolio_value, cumulative_return, drawdown)

readr::write_csv(portfolio_performance, "output/portfolio_performance.csv")

portfolio_summary <- tibble::tibble(
  metric = c(
    "Beginning value", "Ending value", "Total return",
    "Annualized return", "Annualized volatility", "Sharpe ratio", "Maximum drawdown"
  ),
  value = c(
    initial_investment,
    dplyr::last(portfolio_performance$portfolio_value),
    dplyr::last(portfolio_performance$cumulative_return),
    prod(1 + portfolio_performance$portfolio_return)^(252 / nrow(portfolio_performance)) - 1,
    stats::sd(portfolio_performance$portfolio_return) * sqrt(252),
    (prod(1 + portfolio_performance$portfolio_return)^(252 / nrow(portfolio_performance)) - 1 - risk_free_rate) /
      (stats::sd(portfolio_performance$portfolio_return) * sqrt(252)),
    min(portfolio_performance$drawdown)
  )
)

knitr::kable(portfolio_summary, digits = 3, caption = "Equal-weight portfolio summary")

Equal-weight portfolio summary

metric	value
Beginning value	10000.000
Ending value	16144.586
Total return	0.614
Annualized return	0.277
Annualized volatility	0.229
Sharpe ratio	1.036
Maximum drawdown	-0.238

11 FRED macroeconomic data

fred_symbols <- c("FEDFUNDS", "CPIAUCSL", "UNRATE", "VIXCLS", "UMCSENT")

fred_list <- purrr::map(fred_symbols, function(symbol) {
  x <- quantmod::getSymbols(
    symbol,
    src = "FRED",
    from = start_date,
    to = end_date,
    auto.assign = FALSE
  )
  tibble::tibble(
    date = as.Date(zoo::index(x)),
    value = as.numeric(x),
    indicator = symbol
  )
})

fred_economic_data <- dplyr::bind_rows(fred_list) %>%
  tidyr::pivot_wider(names_from = indicator, values_from = value) %>%
  dplyr::arrange(date)

readr::write_csv(fred_economic_data, "output/fred_economic_data.csv")

fred_economic_data <- fred_economic_data %>%
  dplyr::filter(
    date >= max(date, na.rm = TRUE) - 183
  ) %>%
  dplyr::slice_tail(n = 12) %>%
  knitr::kable(
    caption = "Recent FRED observations"
  )

12 Alpha Vantage company fundamentals

This section uses the OVERVIEW endpoint. It pauses between requests to reduce the chance of exceeding a free-plan rate limit. When the key is absent, an empty CSV is created so the report still knits.

get_alpha_overview <- function(ticker, api_key) {
  if (!nzchar(api_key)) stop("ALPHAVANTAGE_API_KEY is not available.")

  response <- httr::GET(
    "https://www.alphavantage.co/query",
    query = list(`function` = "OVERVIEW", symbol = ticker, apikey = api_key),
    httr::timeout(30)
  )
  httr::stop_for_status(response)

  parsed <- jsonlite::fromJSON(
    httr::content(response, "text", encoding = "UTF-8"),
    simplifyVector = TRUE
  )

  if (length(parsed) == 0 || !is.null(parsed$Information) || !is.null(parsed$Note)) {
    stop(paste("No Alpha Vantage overview returned for", ticker))
  }

  value_or_na <- function(name) {
    if (!is.null(parsed[[name]]) && parsed[[name]] != "None") parsed[[name]] else NA_character_
  }

  tibble::tibble(
    ticker = ticker,
    company_name = value_or_na("Name"),
    description = value_or_na("Description"),
    exchange = value_or_na("Exchange"),
    currency = value_or_na("Currency"),
    country = value_or_na("Country"),
    sector = value_or_na("Sector"),
    industry = value_or_na("Industry"),
    market_capitalization = suppressWarnings(as.numeric(value_or_na("MarketCapitalization"))),
    pe_ratio = suppressWarnings(as.numeric(value_or_na("PERatio"))),
    peg_ratio = suppressWarnings(as.numeric(value_or_na("PEGRatio"))),
    book_value = suppressWarnings(as.numeric(value_or_na("BookValue"))),
    dividend_per_share = suppressWarnings(as.numeric(value_or_na("DividendPerShare"))),
    dividend_yield = suppressWarnings(as.numeric(value_or_na("DividendYield"))),
    eps = suppressWarnings(as.numeric(value_or_na("EPS"))),
    profit_margin = suppressWarnings(as.numeric(value_or_na("ProfitMargin"))),
    beta = suppressWarnings(as.numeric(value_or_na("Beta"))),
    fifty_two_week_high = suppressWarnings(as.numeric(value_or_na("52WeekHigh"))),
    fifty_two_week_low = suppressWarnings(as.numeric(value_or_na("52WeekLow"))),
    analyst_target_price = suppressWarnings(as.numeric(value_or_na("AnalystTargetPrice")))
  )
}

if (nzchar(alpha_key)) {
  fundamental_tickers <- ticker_table %>%
    dplyr::filter(asset_type == "Stock") %>%
    dplyr::pull(ticker)

  fundamental_results <- vector("list", length(fundamental_tickers))

  for (i in seq_along(fundamental_tickers)) {
    symbol <- fundamental_tickers[i]
    fundamental_results[[i]] <- tryCatch(
      get_alpha_overview(symbol, alpha_key),
      error = function(e) {
        message("Alpha Vantage request failed for ", symbol, ": ", conditionMessage(e))
        tibble::tibble(ticker = symbol)
      }
    )
    if (i < length(fundamental_tickers)) Sys.sleep(12)
  }

  company_fundamentals <- dplyr::bind_rows(fundamental_results)
} else {
  company_fundamentals <- tibble::tibble(
    ticker = character(), company_name = character(), sector = character(),
    industry = character(), market_capitalization = double(), pe_ratio = double(),
    dividend_yield = double(), beta = double()
  )
}

readr::write_csv(company_fundamentals, "output/company_fundamentals.csv")

if (nrow(company_fundamentals) > 0) {
  company_fundamentals %>%
    dplyr::select(dplyr::any_of(c(
      "ticker", "company_name", "sector", "industry",
      "market_capitalization", "pe_ratio", "dividend_yield", "beta"
    ))) %>%
    knitr::kable(caption = "Alpha Vantage company fundamentals")
} else {
  cat("Alpha Vantage fundamentals were skipped because no API key was available.")
}

## Alpha Vantage fundamentals were skipped because no API key was available.

13 Finance-headline web scraping

The scraper tries multiple finance pages and several common headline selectors. This is true HTML scraping with rvest. Because websites change, the code fails gracefully and records the source that worked.

absolute_url <- function(link, base_url) {
  ifelse(
    stringr::str_detect(link, "^https?://"),
    link,
    xml2::url_absolute(link, base_url)
  )
}

scrape_headlines <- function(page_url, source_name) {
  page <- rvest::read_html(
    httr::GET(
      page_url,
      httr::user_agent("Mozilla/5.0 investment analytics class project"),
      httr::timeout(30)
    )
  )

  selectors <- c(
    "h3 a", "h2 a", "a[data-testid*='headline']",
    "a[href*='/news/']", "a[href*='/markets/']"
  )

  nodes <- purrr::map(selectors, ~ rvest::html_elements(page, .x)) %>%
    purrr::list_flatten()

  if (length(nodes) == 0) stop("No headline elements found.")

  tibble::tibble(
    headline = rvest::html_text2(nodes),
    url = rvest::html_attr(nodes, "href")
  ) %>%
    dplyr::mutate(
      headline = stringr::str_squish(headline),
      url = absolute_url(url, page_url),
      source = source_name,
      scraped_at = Sys.time()
    ) %>%
    dplyr::filter(
      !is.na(url),
      stringr::str_length(headline) >= 25,
      stringr::str_detect(url, "^https?://")
    ) %>%
    dplyr::distinct(headline, .keep_all = TRUE) %>%
    dplyr::slice_head(n = 40)
}

headline_sources <- tibble::tribble(
  ~page_url, ~source_name,
  "https://finance.yahoo.com/topic/stock-market-news/", "Yahoo Finance",
  "https://www.cnbc.com/markets/", "CNBC Markets",
  "https://www.marketwatch.com/markets", "MarketWatch"
)

headline_attempts <- purrr::pmap(
  headline_sources,
  function(page_url, source_name) {
    tryCatch(
      scrape_headlines(page_url, source_name),
      error = function(e) {
        message("Headline scraping failed for ", source_name, ": ", conditionMessage(e))
        NULL
      }
    )
  }
)

finance_headlines <- dplyr::bind_rows(headline_attempts)

if (
  nrow(finance_headlines) == 0 ||
  !"headline" %in% names(finance_headlines)
) {
  finance_headlines <- tibble::tibble(
    headline = character(),
    url = character(),
    source = character(),
    scraped_at = as.POSIXct(character())
  )
}

finance_headlines <- finance_headlines %>%
  dplyr::distinct(headline, .keep_all = TRUE)

if (nrow(finance_headlines) == 0) {
  finance_headlines <- tibble::tibble(
    headline = paste(
      "No headlines were returned because all websites",
      "blocked or changed their HTML structure."
    ),
    url = NA_character_,
    source = "No successful source",
    scraped_at = Sys.time()
  )
}

readr::write_csv(finance_headlines, "output/finance_headlines.csv")

finance_headlines %>%
  dplyr::select(source, headline, url) %>%
  dplyr::slice_head(n = 20) %>%
  knitr::kable(caption = "Scraped finance headlines")

Scraped finance headlines

source	headline	url
No successful source	No headlines were returned because all websites blocked or changed their HTML structure.	NA

14 Headline word analysis

headline_word_counts <- finance_headlines %>%
  dplyr::filter(source != "No successful source") %>%
  tidytext::unnest_tokens(word, headline) %>%
  dplyr::anti_join(tidytext::stop_words, by = "word") %>%
  dplyr::filter(stringr::str_detect(word, "[a-z]")) %>%
  dplyr::count(word, sort = TRUE)

readr::write_csv(headline_word_counts, "output/headline_word_counts.csv")

if (nrow(headline_word_counts) > 0) {
  headline_word_counts %>%
    dplyr::slice_head(n = 15) %>%
    knitr::kable(caption = "Most common meaningful headline words")
}

15 Investment conjoint survey

The group survey is a conjoint-style investment preference study. Each of the 20 respondents evaluates five hypothetical investment profiles and ranks them from 1 (most preferred) to 5 (least preferred). The profiles vary across five attributes that connect directly to the external market datasets used in this project:

• Investment type: Individual Stock or ETF
• Expected annual return: 8% or 15%
• Risk level: Moderate or High
• Primary information source: Company Financials or Market News & Economic Indicators
• Dividend: Pays Dividend or No Dividend

Before knitting, upload the completed workbook to the Posit Cloud project folder and name it exactly:

Investment_Conjoint_Survey Group1.xlsx

Enter completed rankings in the Investment Survey sheet. The code below reads that sheet directly, so respondents do not need to copy their answers into another worksheet.

15.1 Import and clean survey responses

survey_file <- "Investment_Conjoint_Survey Group1.xlsx"
survey_sheet <- "Investment Survey"

clean_variable_names <- function(x) {
  x %>%
    stringr::str_trim() %>%
    stringr::str_to_lower() %>%
    stringr::str_replace_all("[^a-z0-9]+", "_") %>%
    stringr::str_replace_all("^_+|_+$", "") %>%
    make.unique(sep = "_")
}

if (file.exists(survey_file)) {
  survey_raw <- readxl::read_excel(
    path = survey_file,
    sheet = survey_sheet,
    skip = 4,
    na = c("", "NA", "N/A")
  )

  names(survey_raw) <- clean_variable_names(names(survey_raw))

  survey_clean <- survey_raw %>%
    dplyr::transmute(
      respondent_id = suppressWarnings(as.integer(respondent_id)),
      profile = stringr::str_squish(as.character(profile)),
      investment_type = stringr::str_squish(as.character(investment_type)),
      expected_return = stringr::str_squish(as.character(expected_annual_return)),
      risk_level = stringr::str_squish(as.character(risk_level)),
      information_source = stringr::str_squish(as.character(primary_information_source)),
      dividend = stringr::str_squish(as.character(dividend)),
      rank = suppressWarnings(as.integer(rank))
    ) %>%
    dplyr::filter(!is.na(respondent_id), !is.na(profile)) %>%
    dplyr::distinct(respondent_id, profile, .keep_all = TRUE) %>%
    dplyr::mutate(
      expected_return_numeric = readr::parse_number(expected_return) / 100,
      preference_score = dplyr::if_else(rank %in% 1:5, 6 - rank, NA_integer_),
      completed = rank %in% 1:5
    ) %>%
    dplyr::arrange(respondent_id, profile)

  respondent_quality <- survey_clean %>%
    dplyr::group_by(respondent_id) %>%
    dplyr::summarise(
      profiles_assigned = dplyr::n(),
      profiles_ranked = sum(completed),
      unique_ranks_used = dplyr::n_distinct(rank[completed]),
      missing_ranks = sum(!completed),
      valid_ranking = profiles_assigned == 5 & profiles_ranked == 5 &
        unique_ranks_used == 5 & setequal(rank[completed], 1:5),
      .groups = "drop"
    )

  survey_quality_summary <- tibble::tibble(
    measure = c(
      "Profiles in survey design",
      "Expected respondents",
      "Respondents represented",
      "Completed ranking rows",
      "Respondents with five valid unique ranks",
      "Respondents requiring correction",
      "Missing or invalid rank entries"
    ),
    value = c(
      nrow(survey_clean),
      25,
      dplyr::n_distinct(survey_clean$respondent_id),
      sum(survey_clean$completed),
      sum(respondent_quality$valid_ranking),
      sum(!respondent_quality$valid_ranking),
      sum(!survey_clean$completed)
    )
  )

  survey_variable_documentation <- tibble::tribble(
    ~variable, ~data_type, ~nonmissing_values, ~missing_values, ~unique_values, ~example_value, ~description,
    "respondent_id", "integer", sum(!is.na(survey_clean$respondent_id)), sum(is.na(survey_clean$respondent_id)), dplyr::n_distinct(survey_clean$respondent_id, na.rm = TRUE), "1", "Anonymous respondent identifier from 1 through 25",
    "profile", "character", sum(!is.na(survey_clean$profile)), sum(is.na(survey_clean$profile)), dplyr::n_distinct(survey_clean$profile, na.rm = TRUE), "A", "Profile label shown to the respondent",
    "investment_type", "character", sum(!is.na(survey_clean$investment_type)), sum(is.na(survey_clean$investment_type)), dplyr::n_distinct(survey_clean$investment_type, na.rm = TRUE), "ETF", "Investment vehicle presented in the profile",
    "expected_return", "character", sum(!is.na(survey_clean$expected_return)), sum(is.na(survey_clean$expected_return)), dplyr::n_distinct(survey_clean$expected_return, na.rm = TRUE), "8%", "Expected annual return displayed in the profile",
    "risk_level", "character", sum(!is.na(survey_clean$risk_level)), sum(is.na(survey_clean$risk_level)), dplyr::n_distinct(survey_clean$risk_level, na.rm = TRUE), "Moderate", "Investment risk level displayed in the profile",
    "information_source", "character", sum(!is.na(survey_clean$information_source)), sum(is.na(survey_clean$information_source)), dplyr::n_distinct(survey_clean$information_source, na.rm = TRUE), "Company Financials", "Primary information source used to evaluate the investment",
    "dividend", "character", sum(!is.na(survey_clean$dividend)), sum(is.na(survey_clean$dividend)), dplyr::n_distinct(survey_clean$dividend, na.rm = TRUE), "Pays Dividend", "Whether the investment pays a dividend",
    "rank", "integer", sum(!is.na(survey_clean$rank)), sum(is.na(survey_clean$rank)), dplyr::n_distinct(survey_clean$rank, na.rm = TRUE), "1", "Preference rank where 1 is most preferred and 5 is least preferred",
    "preference_score", "integer", sum(!is.na(survey_clean$preference_score)), sum(is.na(survey_clean$preference_score)), dplyr::n_distinct(survey_clean$preference_score, na.rm = TRUE), "5", "Reverse-coded preference score where larger values indicate stronger preference"
  )

  readr::write_csv(survey_clean, "output/investment_conjoint_responses_clean.csv")
  readr::write_csv(respondent_quality, "output/investment_conjoint_response_quality.csv")
  readr::write_csv(survey_quality_summary, "output/investment_conjoint_quality_summary.csv")
  readr::write_csv(survey_variable_documentation, "output/investment_conjoint_variable_documentation.csv")

  knitr::kable(survey_quality_summary, caption = "Investment survey completion and quality summary")
} else {
  survey_raw <- tibble::tibble()
  survey_clean <- tibble::tibble()
  respondent_quality <- tibble::tibble()
  survey_quality_summary <- tibble::tibble(
    measure = "Survey workbook status",
    value = paste(survey_file, "was not found")
  )
  survey_variable_documentation <- tibble::tibble(
    variable = character(), data_type = character(), nonmissing_values = integer(),
    missing_values = integer(), unique_values = integer(), example_value = character(),
    description = character()
  )
  cat("**Survey workbook not found:** Upload `Investment_Conjoint_Survey Group1.xlsx` to the Posit Cloud project folder and knit again. The investment-data sections will still run.")
}

## **Survey workbook not found:** Upload `Investment_Conjoint_Survey Group1.xlsx` to the Posit Cloud project folder and knit again. The investment-data sections will still run.

15.2 Survey response preview

if (nrow(survey_clean) > 0) {
  survey_clean %>%
    dplyr::slice_head(n = 15) %>%
    knitr::kable(caption = "Preview of cleaned investment conjoint responses")
}

15.3 Respondent ranking validation

Every respondent should assign each rank from 1 through 5 exactly once. This table identifies incomplete responses or duplicate ranks before the data are used in the conjoint model.

if (nrow(respondent_quality) > 0) {
  respondent_quality %>%
    dplyr::mutate(valid_ranking = ifelse(valid_ranking, "Valid", "Review")) %>%
    knitr::kable(caption = "Ranking validation by respondent")
}

15.4 Average preference by attribute level

Lower average rank means stronger preference. The table also reports the reverse-coded preference score, where a larger value means the profile was preferred more strongly.

if (nrow(survey_clean) > 0) {
  valid_survey <- survey_clean %>%
    dplyr::filter(completed) %>%
    dplyr::semi_join(
      respondent_quality %>% dplyr::filter(valid_ranking),
      by = "respondent_id"
    )
} else {
  valid_survey <- tibble::tibble()
}

if (nrow(valid_survey) > 0) {
  conjoint_level_summary <- dplyr::bind_rows(
    valid_survey %>% dplyr::group_by(level = investment_type) %>% dplyr::summarise(attribute = "Investment Type", responses = dplyr::n(), average_rank = mean(rank), average_preference_score = mean(preference_score), .groups = "drop"),
    valid_survey %>% dplyr::group_by(level = expected_return) %>% dplyr::summarise(attribute = "Expected Annual Return", responses = dplyr::n(), average_rank = mean(rank), average_preference_score = mean(preference_score), .groups = "drop"),
    valid_survey %>% dplyr::group_by(level = risk_level) %>% dplyr::summarise(attribute = "Risk Level", responses = dplyr::n(), average_rank = mean(rank), average_preference_score = mean(preference_score), .groups = "drop"),
    valid_survey %>% dplyr::group_by(level = information_source) %>% dplyr::summarise(attribute = "Information Source", responses = dplyr::n(), average_rank = mean(rank), average_preference_score = mean(preference_score), .groups = "drop"),
    valid_survey %>% dplyr::group_by(level = dividend) %>% dplyr::summarise(attribute = "Dividend", responses = dplyr::n(), average_rank = mean(rank), average_preference_score = mean(preference_score), .groups = "drop")
  ) %>%
    dplyr::arrange(attribute, dplyr::desc(average_preference_score))

  readr::write_csv(conjoint_level_summary, "output/investment_conjoint_level_summary.csv")

  conjoint_level_summary %>%
    dplyr::mutate(
      average_rank = round(average_rank, 2),
      average_preference_score = round(average_preference_score, 2)
    ) %>%
    knitr::kable(caption = "Average investment preference by attribute level")
}

if (exists("conjoint_level_summary") && nrow(conjoint_level_summary) > 0) {
  ggplot2::ggplot(
    conjoint_level_summary,
    ggplot2::aes(x = reorder(level, average_preference_score), y = average_preference_score)
  ) +
    ggplot2::geom_col() +
    ggplot2::coord_flip() +
    ggplot2::facet_wrap(~ attribute, scales = "free_y", ncol = 2) +
    ggplot2::labs(
      title = "Average Preference Score by Investment Attribute Level",
      subtitle = "Higher scores indicate stronger preference",
      x = NULL,
      y = "Average preference score"
    ) +
    ggplot2::theme_minimal()
}

15.5 Conjoint regression model

The regression uses the reverse-coded preference score as the outcome. Respondent fixed effects control for differences in how individual students use the ranking scale. Positive coefficients indicate that an attribute level increases predicted preference relative to its reference level.

if (nrow(valid_survey) >= 20 && dplyr::n_distinct(valid_survey$respondent_id) >= 4) {
  conjoint_model <- stats::lm(
    preference_score ~ factor(respondent_id) + investment_type + expected_return +
      risk_level + information_source + dividend,
    data = valid_survey
  )

  coefficient_table <- as.data.frame(summary(conjoint_model)$coefficients) %>%
    tibble::rownames_to_column("term") %>%
    dplyr::rename(
      estimate = Estimate,
      standard_error = `Std. Error`,
      t_value = `t value`,
      p_value = `Pr(>|t|)`
    ) %>%
    dplyr::filter(
      !stringr::str_detect(term, "^factor\\(respondent_id\\)"),
      term != "(Intercept)"
    ) %>%
    dplyr::mutate(
      attribute = dplyr::case_when(
        stringr::str_starts(term, "investment_type") ~ "Investment Type",
        stringr::str_starts(term, "expected_return") ~ "Expected Annual Return",
        stringr::str_starts(term, "risk_level") ~ "Risk Level",
        stringr::str_starts(term, "information_source") ~ "Information Source",
        stringr::str_starts(term, "dividend") ~ "Dividend",
        TRUE ~ "Other"
      ),
      level = term %>%
        stringr::str_remove("^investment_type") %>%
        stringr::str_remove("^expected_return") %>%
        stringr::str_remove("^risk_level") %>%
        stringr::str_remove("^information_source") %>%
        stringr::str_remove("^dividend")
    )

  readr::write_csv(coefficient_table, "output/investment_conjoint_coefficients.csv")

  coefficient_table %>%
    dplyr::mutate(
      estimate = round(estimate, 3),
      standard_error = round(standard_error, 3),
      p_value = round(p_value, 4)
    ) %>%
    dplyr::select(attribute, level, estimate, standard_error, p_value) %>%
    knitr::kable(caption = "Estimated conjoint preference effects")
} else {
  conjoint_model <- NULL
  coefficient_table <- tibble::tibble()
  cat("A conjoint model will be estimated after at least four respondents provide complete, valid rankings.")
}

## A conjoint model will be estimated after at least four respondents provide complete, valid rankings.

if (nrow(coefficient_table) > 0) {
  ggplot2::ggplot(
    coefficient_table,
    ggplot2::aes(x = reorder(paste(attribute, level, sep = ": "), estimate), y = estimate)
  ) +
    ggplot2::geom_col() +
    ggplot2::geom_hline(yintercept = 0, linetype = "dashed") +
    ggplot2::coord_flip() +
    ggplot2::labs(
      title = "Estimated Effects on Investment Preference",
      subtitle = "Positive values increase predicted preference relative to the reference level",
      x = NULL,
      y = "Estimated preference effect"
    ) +
    ggplot2::theme_minimal()
}

15.6 Predicted profile preferences

This section scores every unique investment profile in the survey design using the fitted conjoint model. The highest predicted preference identifies the combination of features that best matches the class sample.

if (!is.null(conjoint_model)) {
  profile_design <- survey_clean %>%
    dplyr::distinct(
      investment_type,
      expected_return,
      risk_level,
      information_source,
      dividend
    )

  reference_respondent <- valid_survey$respondent_id[1]

  prediction_data <- profile_design %>%
    dplyr::mutate(
      respondent_id = reference_respondent
    )

  predicted_values <- stats::predict(
    conjoint_model,
    newdata = prediction_data
  )

  profile_predictions <- prediction_data %>%
    dplyr::mutate(
      predicted_preference = as.numeric(predicted_values)
    ) %>%
    dplyr::arrange(dplyr::desc(predicted_preference)) %>%
    dplyr::mutate(
      predicted_rank = dplyr::row_number()
    ) %>%
    dplyr::select(
      predicted_rank,
      investment_type,
      expected_return,
      risk_level,
      information_source,
      dividend,
      predicted_preference
    )

  readr::write_csv(
    profile_predictions,
    "output/investment_conjoint_profile_predictions.csv"
  )

  profile_predictions %>%
    dplyr::slice_head(n = 10) %>%
    dplyr::mutate(
      predicted_preference = round(predicted_preference, 3)
    ) %>%
    knitr::kable(
      caption = "Top predicted investment profiles"
    )
}

15.7 Survey interpretation

if (exists("conjoint_level_summary") && nrow(conjoint_level_summary) > 0) {
  preferred_levels <- conjoint_level_summary %>%
    dplyr::group_by(attribute) %>%
    dplyr::slice_max(average_preference_score, n = 1, with_ties = FALSE) %>%
    dplyr::ungroup()

  cat("Among the completed and valid class responses, the most preferred level within each attribute was: ")
  cat(paste0(preferred_levels$attribute, " = ", preferred_levels$level, collapse = "; "))
  cat(". These findings describe the participating class sample and should not be generalized to all investors.")
} else {
  cat("Survey interpretations will appear after completed rankings are entered in the workbook.")
}

Survey interpretations will appear after completed rankings are entered in the workbook.

16 Documentation of variables

The table below documents the principal variables created for the stock, portfolio, economic, fundamental, headline, and survey datasets. This file is exported for the group as output/data_dictionary.csv.

core_data_dictionary <- tibble::tribble(
  ~dataset, ~variable, ~data_type, ~description,
  "stock_prices", "ticker", "character", "Trading symbol for the stock or ETF",
  "stock_prices", "date", "Date", "Trading date",
  "stock_prices", "open", "numeric", "Opening market price",
  "stock_prices", "high", "numeric", "Highest market price during the day",
  "stock_prices", "low", "numeric", "Lowest market price during the day",
  "stock_prices", "close", "numeric", "Adjusted closing price used in the analysis",
  "stock_prices", "volume", "numeric", "Number of shares traded",
  "stock_prices", "vwap", "numeric", "Volume-weighted average price when supplied by Massive",
  "stock_prices", "transactions", "numeric", "Reported transaction count when supplied by Massive",
  "stock_prices", "data_source", "character", "Source used for the ticker: Massive or Yahoo fallback",
  "technical_indicators", "daily_return", "numeric", "One-day percentage price return in decimal form",
  "technical_indicators", "sma_20", "numeric", "Twenty-trading-day simple moving average",
  "technical_indicators", "sma_50", "numeric", "Fifty-trading-day simple moving average",
  "technical_indicators", "average_volume_20", "numeric", "Twenty-day average trading volume",
  "technical_indicators", "rolling_volatility_20", "numeric", "Annualized volatility calculated over twenty trading days",
  "risk_metrics", "total_return", "numeric", "Cumulative return over the analysis period",
  "risk_metrics", "annualized_return", "numeric", "Return converted to an annual rate",
  "risk_metrics", "annualized_volatility", "numeric", "Annualized standard deviation of daily returns",
  "risk_metrics", "sharpe_ratio", "numeric", "Annualized excess return divided by annualized volatility",
  "risk_metrics", "max_drawdown", "numeric", "Largest percentage decline from a prior peak",
  "portfolio_performance", "portfolio_return", "numeric", "Weighted daily portfolio return",
  "portfolio_performance", "portfolio_value", "numeric", "Value of the hypothetical investment",
  "portfolio_performance", "cumulative_return", "numeric", "Portfolio return since the beginning of the period",
  "portfolio_performance", "drawdown", "numeric", "Portfolio decline from its prior peak",
  "fred_economic_data", "FEDFUNDS", "numeric", "Effective federal funds rate",
  "fred_economic_data", "CPIAUCSL", "numeric", "Consumer Price Index for All Urban Consumers",
  "fred_economic_data", "UNRATE", "numeric", "United States unemployment rate",
  "fred_economic_data", "VIXCLS", "numeric", "CBOE volatility index closing value",
  "fred_economic_data", "UMCSENT", "numeric", "University of Michigan consumer sentiment index",
  "company_fundamentals", "market_capitalization", "numeric", "Company market capitalization from Alpha Vantage",
  "company_fundamentals", "pe_ratio", "numeric", "Price-to-earnings ratio",
  "company_fundamentals", "dividend_yield", "numeric", "Dividend yield in decimal form",
  "company_fundamentals", "beta", "numeric", "Estimated market beta",
  "finance_headlines", "headline", "character", "Finance headline collected with rvest",
  "finance_headlines", "url", "character", "Web address associated with the headline",
  "finance_headlines", "source", "character", "Website from which the headline was collected",
  "finance_headlines", "scraped_at", "POSIXct", "Date and time the headline was collected"
)

survey_dictionary_for_export <- survey_variable_documentation %>%
  dplyr::transmute(
    dataset = "investment_conjoint_responses_clean",
    variable,
    data_type,
    description
  )

data_dictionary <- dplyr::bind_rows(
  core_data_dictionary,
  survey_dictionary_for_export
)

readr::write_csv(data_dictionary, "output/data_dictionary.csv")

knitr::kable(
  data_dictionary,
  caption = "Documentation of variables",
  row.names = FALSE
)

Documentation of variables

dataset	variable	data_type	description
stock_prices	ticker	character	Trading symbol for the stock or ETF
stock_prices	date	Date	Trading date
stock_prices	open	numeric	Opening market price
stock_prices	high	numeric	Highest market price during the day
stock_prices	low	numeric	Lowest market price during the day
stock_prices	close	numeric	Adjusted closing price used in the analysis
stock_prices	volume	numeric	Number of shares traded
stock_prices	vwap	numeric	Volume-weighted average price when supplied by Massive
stock_prices	transactions	numeric	Reported transaction count when supplied by Massive
stock_prices	data_source	character	Source used for the ticker: Massive or Yahoo fallback
technical_indicators	daily_return	numeric	One-day percentage price return in decimal form
technical_indicators	sma_20	numeric	Twenty-trading-day simple moving average
technical_indicators	sma_50	numeric	Fifty-trading-day simple moving average
technical_indicators	average_volume_20	numeric	Twenty-day average trading volume
technical_indicators	rolling_volatility_20	numeric	Annualized volatility calculated over twenty trading days
risk_metrics	total_return	numeric	Cumulative return over the analysis period
risk_metrics	annualized_return	numeric	Return converted to an annual rate
risk_metrics	annualized_volatility	numeric	Annualized standard deviation of daily returns
risk_metrics	sharpe_ratio	numeric	Annualized excess return divided by annualized volatility
risk_metrics	max_drawdown	numeric	Largest percentage decline from a prior peak
portfolio_performance	portfolio_return	numeric	Weighted daily portfolio return
portfolio_performance	portfolio_value	numeric	Value of the hypothetical investment
portfolio_performance	cumulative_return	numeric	Portfolio return since the beginning of the period
portfolio_performance	drawdown	numeric	Portfolio decline from its prior peak
fred_economic_data	FEDFUNDS	numeric	Effective federal funds rate
fred_economic_data	CPIAUCSL	numeric	Consumer Price Index for All Urban Consumers
fred_economic_data	UNRATE	numeric	United States unemployment rate
fred_economic_data	VIXCLS	numeric	CBOE volatility index closing value
fred_economic_data	UMCSENT	numeric	University of Michigan consumer sentiment index
company_fundamentals	market_capitalization	numeric	Company market capitalization from Alpha Vantage
company_fundamentals	pe_ratio	numeric	Price-to-earnings ratio
company_fundamentals	dividend_yield	numeric	Dividend yield in decimal form
company_fundamentals	beta	numeric	Estimated market beta
finance_headlines	headline	character	Finance headline collected with rvest
finance_headlines	url	character	Web address associated with the headline
finance_headlines	source	character	Website from which the headline was collected
finance_headlines	scraped_at	POSIXct	Date and time the headline was collected

17 Statistical Analysis

17.1 Analysis Overview

This analysis examines how overall market performance and market volatility influence the daily returns of the individual stocks included in the project portfolio. Separate multiple regression models were estimated using SPY returns and changes in the VIX to evaluate the relationship between broader market conditions and individual stock performance.

The analysis uses the cleaned daily_returns, risk_metrics, and fred_economic_data objects generated earlier in this report. Therefore, the datasets do not need to be imported again.

17.2 Prepare Regression Data

SPY represents overall market performance, while the VIX represents market volatility. Daily changes in the VIX were calculated before the datasets were combined by date.

# Import the CSV files created earlier in the group report
analysis_daily_returns <- readr::read_csv(
  "output/daily_returns.csv",
  show_col_types = FALSE
)

analysis_fred_data <- readr::read_csv(
  "output/fred_economic_data.csv",
  show_col_types = FALSE
)

analysis_risk_metrics <- readr::read_csv(
  "output/risk_metrics.csv",
  show_col_types = FALSE
)

# Convert date columns
analysis_daily_returns <- analysis_daily_returns %>%
  dplyr::mutate(date = as.Date(date))

analysis_fred_data <- analysis_fred_data %>%
  dplyr::mutate(date = as.Date(date))

# Create SPY market-return data
spy_returns <- analysis_daily_returns %>%
  dplyr::filter(ticker == "SPY") %>%
  dplyr::select(
    date,
    spy_return = daily_return
  )

# Calculate daily changes in the VIX
vix_data <- analysis_fred_data %>%
  dplyr::arrange(date) %>%
  dplyr::transmute(
    date,
    vix_level = VIXCLS,
    vix_change = VIXCLS - dplyr::lag(VIXCLS)
  ) %>%
  dplyr::filter(!is.na(vix_change))

# Combine individual stock returns with SPY and VIX data
regression_data <- analysis_daily_returns %>%
  dplyr::filter(ticker != "SPY") %>%
  dplyr::left_join(
    spy_returns,
    by = "date"
  ) %>%
  dplyr::left_join(
    vix_data,
    by = "date"
  ) %>%
  tidyr::drop_na(
    daily_return,
    spy_return,
    vix_change
  )

# Display the number of observations for each stock
regression_data %>%
  dplyr::count(ticker, name = "observations") %>%
  knitr::kable(
    col.names = c("Ticker", "Observations"),
    caption = "Regression Observations by Stock"
  )

Regression Observations by Stock

Ticker	Observations
AAPL	481
AMZN	481
JPM	481
MSFT	481
NVDA	481

17.3 Descriptive Statistics

Descriptive statistics summarize the average return, volatility, and range of daily returns for each stock included in the regression analysis.

descriptive_statistics <- regression_data %>%
  dplyr::group_by(ticker) %>%
  dplyr::summarise(
    observations = dplyr::n(),
    average_daily_return = mean(daily_return, na.rm = TRUE),
    standard_deviation = stats::sd(daily_return, na.rm = TRUE),
    minimum_return = min(daily_return, na.rm = TRUE),
    maximum_return = max(daily_return, na.rm = TRUE),
    .groups = "drop"
  )

descriptive_statistics %>%
  dplyr::mutate(
    dplyr::across(
      c(
        average_daily_return,
        standard_deviation,
        minimum_return,
        maximum_return
      ),
      ~ scales::percent(.x, accuracy = 0.01)
    )
  ) %>%
  knitr::kable(
    col.names = c(
      "Ticker",
      "Observations",
      "Average Daily Return",
      "Standard Deviation",
      "Minimum Return",
      "Maximum Return"
    ),
    caption = "Descriptive Statistics for Daily Stock Returns"
  )

Descriptive Statistics for Daily Stock Returns

Ticker	Observations	Average Daily Return	Standard Deviation	Minimum Return	Maximum Return
AAPL	481	0.09%	1.82%	-9.25%	15.33%
AMZN	481	0.14%	2.17%	-8.98%	15.32%
JPM	481	0.14%	1.60%	-7.48%	11.54%
MSFT	481	0.07%	1.83%	-9.99%	15.51%
NVDA	481	0.19%	2.85%	-16.97%	18.72%

17.4 Multiple Regression Analysis

For each stock, daily return was modeled as a function of the SPY market return and the daily change in the VIX:

\[ StockReturn = \beta_0 + \beta_1(SPYReturn) + \beta_2(VIXChange) + \epsilon \]

The SPY coefficient measures sensitivity to general market movements. The VIX coefficient measures how returns tend to change when market volatility increases or decreases.

stock_models <- regression_data %>%
  dplyr::group_by(ticker) %>%
  tidyr::nest() %>%
  dplyr::mutate(
    model = purrr::map(
      data,
      ~ stats::lm(
        daily_return ~ spy_return + vix_change,
        data = .x
      )
    )
  )

coefficient_results <- stock_models %>%
  dplyr::mutate(
    coefficients = purrr::map(
      model,
      ~ broom::tidy(.x, conf.int = TRUE)
    )
  ) %>%
  dplyr::select(ticker, coefficients) %>%
  tidyr::unnest(coefficients)

coefficient_table <- coefficient_results %>%
  dplyr::filter(term != "(Intercept)") %>%
  dplyr::mutate(
    predictor = dplyr::recode(
      term,
      spy_return = "SPY Market Return",
      vix_change = "Change in VIX"
    ),
    significance = dplyr::if_else(
      p.value < 0.05,
      "Statistically significant",
      "Not statistically significant"
    ),
    estimate = round(estimate, 4),
    std.error = round(std.error, 4),
    p.value = round(p.value, 4)
  ) %>%
  dplyr::select(
    ticker,
    predictor,
    estimate,
    std.error,
    p.value,
    significance
  )

knitr::kable(
  coefficient_table,
  col.names = c(
    "Ticker",
    "Predictor",
    "Coefficient",
    "Standard Error",
    "p-value",
    "Result"
  ),
  caption = "Regression Coefficient Results"
)

Regression Coefficient Results

Ticker	Predictor	Coefficient	Standard Error	p-value	Result
AAPL	SPY Market Return	0.8023	0.1264	0.0000	Statistically significant
AAPL	Change in VIX	-0.0016	0.0007	0.0135	Statistically significant
AMZN	SPY Market Return	1.5606	0.1435	0.0000	Statistically significant
AMZN	Change in VIX	0.0011	0.0007	0.1433	Not statistically significant
JPM	SPY Market Return	0.7604	0.1126	0.0000	Statistically significant
JPM	Change in VIX	-0.0010	0.0006	0.0788	Not statistically significant
MSFT	SPY Market Return	1.0569	0.1378	0.0000	Statistically significant
MSFT	Change in VIX	0.0007	0.0007	0.3076	Not statistically significant
NVDA	SPY Market Return	2.2250	0.1798	0.0000	Statistically significant
NVDA	Change in VIX	0.0018	0.0009	0.0487	Statistically significant

17.5 Model Performance

R-squared measures the proportion of variation in each stock’s daily returns explained by SPY returns and changes in the VIX. A higher R-squared indicates that the two market variables explain more of the stock’s daily movement.

model_performance <- stock_models %>%
  dplyr::mutate(
    performance = purrr::map(
      model,
      broom::glance
    )
  ) %>%
  dplyr::select(ticker, performance) %>%
  tidyr::unnest(performance)

model_performance_table <- model_performance %>%
  dplyr::transmute(
    ticker,
    r_squared = round(r.squared, 3),
    adjusted_r_squared = round(adj.r.squared, 3),
    model_p_value = round(p.value, 4),
    observations = nobs
  )

knitr::kable(
  model_performance_table,
  col.names = c(
    "Ticker",
    "R-Squared",
    "Adjusted R-Squared",
    "Model p-value",
    "Observations"
  ),
  caption = "Regression Model Performance"
)

Regression Model Performance

Ticker	R-Squared	Adjusted R-Squared	Model p-value	Observations
AAPL	0.402	0.399	0	481
AMZN	0.456	0.454	0	481
JPM	0.387	0.384	0	481
MSFT	0.295	0.292	0	481
NVDA	0.509	0.507	0	481

17.6 Statistical Visualizations

17.6.1 Daily Stock Returns

ggplot2::ggplot(
  regression_data,
  ggplot2::aes(
    x = date,
    y = daily_return
  )
) +
  ggplot2::geom_line(
    linewidth = 0.35,
    alpha = 0.75
  ) +
  ggplot2::facet_wrap(
    ~ticker,
    scales = "free_y",
    ncol = 2
  ) +
  ggplot2::scale_y_continuous(
    labels = scales::label_percent(accuracy = 0.1)
  ) +
  ggplot2::labs(
    title = "Daily Stock Returns Over Time",
    x = NULL,
    y = "Daily Return",
    caption = "Source: Market data collected by the project team"
  ) +
  ggplot2::theme_minimal()

17.6.2 Market Sensitivity

beta_results <- coefficient_results %>%
  dplyr::filter(term == "spy_return") %>%
  dplyr::mutate(
    ticker = reorder(ticker, estimate)
  )

ggplot2::ggplot(
  beta_results,
  ggplot2::aes(
    x = estimate,
    y = ticker
  )
) +
  ggplot2::geom_vline(
    xintercept = 1,
    linetype = "dashed"
  ) +
  ggplot2::geom_errorbar(
    ggplot2::aes(
      xmin = conf.low,
      xmax = conf.high
    ),
    width = 0.2
  ) +
  ggplot2::geom_point(size = 3) +
  ggplot2::labs(
    title = "Stock Sensitivity to Overall Market Returns",
    subtitle = "The dashed line represents a market coefficient of 1",
    x = "SPY Return Coefficient",
    y = NULL,
    caption = "Error bars represent 95% confidence intervals"
  ) +
  ggplot2::theme_minimal()

17.6.3 Relationship With Market Volatility

vix_results <- coefficient_results %>%
  dplyr::filter(term == "vix_change") %>%
  dplyr::mutate(
    ticker = reorder(ticker, estimate)
  )

ggplot2::ggplot(
  vix_results,
  ggplot2::aes(
    x = estimate,
    y = ticker
  )
) +
  ggplot2::geom_vline(
    xintercept = 0,
    linetype = "dashed"
  ) +
  ggplot2::geom_errorbar(
    ggplot2::aes(
      xmin = conf.low,
      xmax = conf.high
    ),
    width = 0.2
  ) +
  ggplot2::geom_point(size = 3) +
  ggplot2::labs(
    title = "Effect of VIX Changes on Stock Returns",
    subtitle = "Negative coefficients suggest lower returns when volatility rises",
    x = "VIX Change Coefficient",
    y = NULL,
    caption = "Error bars represent 95% confidence intervals"
  ) +
  ggplot2::theme_minimal()

17.6.4 Annualized Risk and Return

ggplot2::ggplot(
  analysis_risk_metrics,
  ggplot2::aes(
    x = annualized_volatility,
    y = annualized_return,
    label = ticker
  )
) +
  ggplot2::geom_point(size = 4) +
  ggplot2::geom_text(
    nudge_y = 0.015,
    check_overlap = TRUE
  ) +
  ggplot2::scale_x_continuous(
    labels = scales::label_percent(accuracy = 1)
  ) +
  ggplot2::scale_y_continuous(
    labels = scales::label_percent(accuracy = 1)
  ) +
  ggplot2::labs(
    title = "Annualized Risk and Return",
    x = "Annualized Volatility",
    y = "Annualized Return"
  ) +
  ggplot2::theme_minimal()

17.7 Key Statistical Findings

highest_r_squared <- model_performance %>%
  dplyr::slice_max(
    r.squared,
    n = 1,
    with_ties = FALSE
  )

highest_beta <- beta_results %>%
  dplyr::slice_max(
    estimate,
    n = 1,
    with_ties = FALSE
  )

lowest_beta <- beta_results %>%
  dplyr::slice_min(
    estimate,
    n = 1,
    with_ties = FALSE
  )

cat(
  paste0(
    "The regression model explained the greatest proportion of daily return ",
    "variation for **", highest_r_squared$ticker, "**, with an R-squared of **",
    scales::percent(highest_r_squared$r.squared, accuracy = 0.1),
    "**. **", highest_beta$ticker, "** had the highest estimated SPY ",
    "coefficient of **", round(highest_beta$estimate, 2),
    "** and was the most sensitive to overall market movements. **",
    lowest_beta$ticker, "** had the lowest estimated SPY coefficient of **",
    round(lowest_beta$estimate, 2),
    "** and was the least sensitive to general market movements."
  )
)

The regression model explained the greatest proportion of daily return variation for AAPL, with an R-squared of 40.2%. AAPL had the highest estimated SPY coefficient of 0.8 and was the most sensitive to overall market movements. AAPL had the lowest estimated SPY coefficient of 0.8 and was the least sensitive to general market movements. The regression model explained the greatest proportion of daily return variation for AMZN, with an R-squared of 45.6%. AMZN had the highest estimated SPY coefficient of 1.56 and was the most sensitive to overall market movements. AMZN had the lowest estimated SPY coefficient of 1.56 and was the least sensitive to general market movements. The regression model explained the greatest proportion of daily return variation for JPM, with an R-squared of 38.7%. JPM had the highest estimated SPY coefficient of 0.76 and was the most sensitive to overall market movements. JPM had the lowest estimated SPY coefficient of 0.76 and was the least sensitive to general market movements. The regression model explained the greatest proportion of daily return variation for MSFT, with an R-squared of 29.5%. MSFT had the highest estimated SPY coefficient of 1.06 and was the most sensitive to overall market movements. MSFT had the lowest estimated SPY coefficient of 1.06 and was the least sensitive to general market movements. The regression model explained the greatest proportion of daily return variation for NVDA, with an R-squared of 50.9%. NVDA had the highest estimated SPY coefficient of 2.22 and was the most sensitive to overall market movements. NVDA had the lowest estimated SPY coefficient of 2.22 and was the least sensitive to general market movements.

Overall, SPY returns were an important predictor of individual stock performance. Changes in the VIX provided additional information about how each security responded to changes in market uncertainty. Together, these results demonstrate that market direction and volatility both contribute to short-term stock movements.

17.8 Statistical Analysis Limitations

This analysis relies on financial data collected through external APIs and web scraping. Because the datasets are refreshed whenever the project is executed, the exported CSV files update to reflect the latest available market information. The statistical analysis presented in this section was originally completed using data collected approximately one week before the final report. As a result, returns, regression coefficients, risk metrics, and other results may differ when the report is rerun. These differences reflect changing market conditions rather than errors in the analytical methodology.

The regression models also include only SPY returns and changes in the VIX. Company-specific news, earnings announcements, interest rates, and other economic conditions may influence stock returns but are not directly represented in these models.

18 Visualizations

18.1 Closing prices and moving averages

chart_ticker <- "AAPL"

price_chart <- technical_indicators %>%
  dplyr::filter(ticker == chart_ticker) %>%
  ggplot2::ggplot(ggplot2::aes(x = date)) +
  ggplot2::geom_line(ggplot2::aes(y = close), linewidth = 0.6) +
  ggplot2::geom_line(ggplot2::aes(y = sma_20), linewidth = 0.7, linetype = "dashed") +
  ggplot2::geom_line(ggplot2::aes(y = sma_50), linewidth = 0.7, linetype = "dotted") +
  ggplot2::labs(
    title = paste(chart_ticker, "closing price with moving averages"),
    subtitle = "Solid: close | Dashed: 20-day SMA | Dotted: 50-day SMA",
    x = NULL,
    y = "Price"
  ) +
  ggplot2::theme_minimal()

plotly::ggplotly(price_chart)

ggplot2::ggsave("figures/price_moving_averages.png", price_chart, width = 10, height = 6)

18.2 Volume trend

volume_chart <- technical_indicators %>%
  dplyr::filter(ticker == chart_ticker) %>%
  ggplot2::ggplot(ggplot2::aes(x = date)) +
  ggplot2::geom_col(ggplot2::aes(y = volume), width = 1) +
  ggplot2::geom_line(ggplot2::aes(y = average_volume_20), linewidth = 0.7) +
  ggplot2::scale_y_continuous(labels = scales::label_number(scale_cut = scales::cut_short_scale())) +
  ggplot2::labs(
    title = paste(chart_ticker, "daily volume and 20-day average"),
    x = NULL,
    y = "Volume"
  ) +
  ggplot2::theme_minimal()

plotly::ggplotly(volume_chart)

ggplot2::ggsave("figures/volume_trend.png", volume_chart, width = 10, height = 6)

18.3 Risk versus return

risk_return_chart <- risk_metrics %>%
  ggplot2::ggplot(
    ggplot2::aes(
      x = annualized_volatility,
      y = annualized_return,
      label = ticker,
      size = average_daily_volume
    )
  ) +
  ggplot2::geom_point(alpha = 0.7) +
  ggplot2::geom_text(nudge_y = 0.015, show.legend = FALSE) +
  ggplot2::scale_x_continuous(labels = scales::label_percent()) +
  ggplot2::scale_y_continuous(labels = scales::label_percent()) +
  ggplot2::scale_size_continuous(labels = scales::label_number(scale_cut = scales::cut_short_scale())) +
  ggplot2::labs(
    title = "Annualized risk versus return",
    x = "Annualized volatility",
    y = "Annualized return",
    size = "Average volume"
  ) +
  ggplot2::theme_minimal()

plotly::ggplotly(risk_return_chart)

ggplot2::ggsave("figures/risk_return.png", risk_return_chart, width = 10, height = 6)

18.4 Portfolio growth

portfolio_chart <- portfolio_performance %>%
  ggplot2::ggplot(ggplot2::aes(x = date, y = portfolio_value)) +
  ggplot2::geom_line(linewidth = 0.8) +
  ggplot2::scale_y_continuous(labels = scales::label_dollar()) +
  ggplot2::labs(
    title = "Growth of the hypothetical portfolio",
    x = NULL,
    y = "Portfolio value"
  ) +
  ggplot2::theme_minimal()

plotly::ggplotly(portfolio_chart)

ggplot2::ggsave("figures/portfolio_growth.png", portfolio_chart, width = 10, height = 6)

18.5 Rolling volatility

volatility_chart <- technical_indicators %>%
  dplyr::filter(!is.na(rolling_volatility_20)) %>%
  ggplot2::ggplot(ggplot2::aes(x = date, y = rolling_volatility_20, group = ticker)) +
  ggplot2::geom_line(alpha = 0.8) +
  ggplot2::facet_wrap(~ticker, scales = "free_y") +
  ggplot2::scale_y_continuous(labels = scales::label_percent()) +
  ggplot2::labs(
    title = "Twenty-day rolling annualized volatility",
    x = NULL,
    y = "Volatility"
  ) +
  ggplot2::theme_minimal()

plotly::ggplotly(volatility_chart)

ggplot2::ggsave("figures/rolling_volatility.png", volatility_chart, width = 11, height = 8)

19 Automatically generated findings

best_return <- risk_metrics %>% dplyr::slice_max(annualized_return, n = 1)
best_sharpe <- risk_metrics %>% dplyr::slice_max(sharpe_ratio, n = 1)
highest_risk <- risk_metrics %>% dplyr::slice_max(annualized_volatility, n = 1)
worst_drawdown <- risk_metrics %>% dplyr::slice_min(max_drawdown, n = 1)

cat(
  paste0(
    "The investment with the highest annualized return was **", best_return$ticker,
    "** at approximately **", scales::percent(best_return$annualized_return, accuracy = 0.1),
    "**. The strongest risk-adjusted result based on the Sharpe ratio was **",
    best_sharpe$ticker, "**. **", highest_risk$ticker,
    "** had the highest annualized volatility, while **", worst_drawdown$ticker,
    "** experienced the largest maximum drawdown. The hypothetical portfolio ended at **",
    scales::dollar(dplyr::last(portfolio_performance$portfolio_value)),
    "**, compared with a starting value of **", scales::dollar(initial_investment),
    "**. These results describe historical performance and should not be treated as investment advice."
  )
)

The investment with the highest annualized return was NVDA at approximately 38.7%. The strongest risk-adjusted result based on the Sharpe ratio was JPM. NVDA had the highest annualized volatility, while NVDA experienced the largest maximum drawdown. The hypothetical portfolio ended at $16,144.59, compared with a starting value of $10,000. These results describe historical performance and should not be treated as investment advice.

20 Deliverables created

output_files <- list.files("output", pattern = "\\.csv$", full.names = TRUE)

tibble::tibble(
  file = basename(output_files),
  size_kb = round(file.info(output_files)$size / 1024, 1)
) %>%
  knitr::kable(caption = "CSV files created for the group")

CSV files created for the group

file	size_kb
company_fundamentals.csv	0.1
daily_returns.csv	107.3
data_dictionary.csv	2.8
data_quality_summary.csv	0.4
finance_headlines.csv	0.2
fred_economic_data.csv	15.0
headline_word_counts.csv	0.0
portfolio_performance.csv	43.2
risk_metrics.csv	1.0
stock_prices.csv	360.0
technical_indicators.csv	603.0

21 Limitations & Future Enhancements

21.1 Limitations

• Historical analysis may not predict future market behavior The analysis evaluates historical stock returns, volatility, and portfolio performance. While these metrics provide insight into past behavior, market conditions change over time due to economic events, company performance, investor sentiment, and unexpected market shocks.

• Portfolio includes a limited number of securities Results may be influenced by the performance characteristics of large-cap technology companies.

• Regression models capture only selected market factors The model is only looking at market returns and volatility, however, stock performance is also influenced by interest rates, earnings announcements, industry trends, inflation, etc.

• External APIs and web sources may introduce data availability issues The model in vulnerable to API limitations, rate limits, website structure changes, missing data, all which will impact results.

• Survey results represent a limited sample Preferences may reflect student demographics rather than actual investors.

21.2 Future Enhancements

• Expand asset coverage and diversification analysis Increase number of securities analyzed including:

  • Additional industries
  • Small-cap companies
  • International investments
  • Bonds or fixed-income assets
  • Alternative investments

• Add predictive machine learning models The current project focuses primarily on descriptive and explanatory analytics. Adding predictive modeling moves the project from understanding historical patterns toward predicting potential future scenarios.

• Build an interactive investment dashboard The project generates analytical outputs but could become more impactful through a user-facing dashboard.

• Integrate financial sentiment analysis The project collects financial headlines and performs headline word analysis. A stronger version could add:

  • Sentiment scoring
  • Positive/negative classification
  • Company-level sentiment trends
  • Relationship between sentiment and stock returns

• Increase and diversify investor survey participation Provides stronger insights into investor decision-making.

22 Conclusion

This report demonstrates the full data-collection and preprocessing role in one reproducible workflow, including investment conjoint-survey cleaning and analysis, REST API requests with httr, JSON processing with jsonlite, market and economic retrieval with quantmod, HTML scraping with rvest, data cleaning with dplyr, and export of reusable CSV datasets. The outputs can be shared with group members for visualization, modeling, forecasting, or presentation work.