如何在R中动态获取DAX成分股2009-2019年每日收益？

Got it, let's tackle this problem step by step—you're right that handling dynamic index constituents is the tricky part here, not just pulling static data. Here's a more robust approach using R that addresses both the constituent changes and data consistency issues:

DAX adjusts its constituents quarterly (third Friday of March, June, September, December), so we need to map out these rebalance dates and fetch the correct stocks for each period. We'll use a combination of tidyquant for data pulling and a fallback static list for periods where historical constituent lookup might fail (since free APIs don't always have full historical index membership data).

library(tidyquant)
library(tidyverse)

# Define your target date range
start_date <- as.Date("2009-06-30")
end_date <- as.Date("2019-06-30")

# Generate DAX rebalance dates (quarterly third Fridays)
rebalance_dates <- seq.Date(start_date, end_date, by = "quarter") %>%
  map_dbl(~ {
    month_end <- ceiling_date(., unit = "month")
    # Find the third Friday of the month
    if (wday(month_end) >= 5) {
      month_end - days(wday(month_end) - 5) + days(14)
    } else {
      month_end - days(wday(month_end) + 2) + days(7)
    }
  }) %>%
  as.Date() %>%
  unique() %>%
  sort()

# Function to get DAX constituents for a given date (with fallback static lists)
get_dax_constituents <- function(date) {
  tryCatch({
    # Try pulling historical constituents via tidyquant
    tq_index("DAX", from = date, to = date) %>%
      pull(symbol) %>%
      paste0(".DE")
  }, error = function(e) {
    # Fallback: manually curated lists based on DAX historical changes (check Wikipedia for accuracy)
    if (date <= as.Date("2011-06-30")) {
      c("ADS.DE","ALV.DE","BAS.DE","BMW.DE","BAYN.DE","BEI.DE","DAI.DE","DBK.DE","DB1.DE","DPW.DE","DTE.DE","EOAN.DE","FME.DE","FRE.DE","HEN3.DE","LIN.DE","LHA.DE","MRK.DE","MUV2.DE","RWE.DE","SAP.DE","SIE.DE","TKA.DE","VOW3.DE")
    } else if (date <= as.Date("2015-06-30")) {
      # FRE.DE was removed in 2012; add CON.DE as replacement example
      c("ADS.DE","ALV.DE","BAS.DE","BMW.DE","BAYN.DE","BEI.DE","DAI.DE","DBK.DE","DB1.DE","DPW.DE","DTE.DE","EOAN.DE","FME.DE","HEN3.DE","LIN.DE","LHA.DE","MRK.DE","MUV2.DE","RWE.DE","SAP.DE","SIE.DE","TKA.DE","VOW3.DE","CON.DE")
    } else {
      # Add DHER.DE (added in 2015)
      c("ADS.DE","ALV.DE","BAS.DE","BMW.DE","BAYN.DE","BEI.DE","DAI.DE","DBK.DE","DB1.DE","DPW.DE","DTE.DE","EOAN.DE","FME.DE","HEN3.DE","LIN.DE","LHA.DE","MRK.DE","MUV2.DE","RWE.DE","SAP.DE","SIE.DE","TKA.DE","VOW3.DE","CON.DE","DHER.DE")
    }
  })
}

# Map constituents to each rebalance period
constituent_map <- map(rebalance_dates, ~ tibble(rebalance_date = ., symbol = get_dax_constituents(.))) %>%
  bind_rows()

Instead of using quantmod's environment approach (which leads to inconsistent lengths), we'll use tidyquant to pull adjusted prices, align all dates, and handle missing values gracefully.

# Split our date range into rebalance periods
periods <- tibble(
  period_start = c(start_date, rebalance_dates[-length(rebalance_dates)] + days(1)),
  period_end = rebalance_dates,
  symbols = map2(period_start, period_end, ~ {
    filter(constituent_map, rebalance_date <= .y & rebalance_date >= .x) %>%
      pull(symbol) %>%
      unique()
  })
)

# Function to fetch adjusted prices for a set of symbols in a period
fetch_period_prices <- function(symbols, start, end) {
  tq_get(symbols, get = "stock.prices", from = start, to = end) %>%
    select(symbol, date, adjusted) %>%
    group_by(symbol) %>%
    mutate(adjusted = na.locf(adjusted))  # Forward fill missing prices (adjust if this isn't right for your use case) %>%
    ungroup()
}

# Pull prices for all periods and combine
price_data <- pmap(periods, ~ fetch_period_prices(..3, ..1, ..2)) %>%
  bind_rows()

# Align all dates to ensure no gaps (critical for consistent return calculations)
full_dates <- tibble(date = seq.Date(start_date, end_date, by = "day"))
aligned_prices <- full_dates %>%
  left_join(price_data, by = "date") %>%
  arrange(symbol, date)

Now we can compute daily returns using adjusted prices, ensuring consistency across all stocks and dates.

daily_returns <- aligned_prices %>%
  group_by(symbol) %>%
  mutate(daily_return = (adjusted / lag(adjusted)) - 1) %>%
  ungroup() %>%
  select(symbol, date, daily_return) %>%
  drop_na(daily_return)  # Remove first row of each stock where return can't be calculated

Since FRE.DE has no data on Yahoo Finance, you have two options:

• Option 1: Remove it entirely

  daily_returns <- daily_returns %>% filter(symbol != "FRE.DE")
  

• Option 2: Replace with DAX index returns as a fallback

  # Pull DAX index returns
  dax_returns <- tq_get("^GDAXI", get = "stock.prices", from = start_date, to = end_date) %>%
    mutate(daily_return = (adjusted / lag(adjusted)) - 1) %>%
    select(date, daily_return) %>%
    mutate(symbol = "FRE.DE") %>%
    drop_na(daily_return)
  
  # Add to your returns data
  daily_returns <- daily_returns %>%
    bind_rows(dax_returns) %>%
    arrange(symbol, date)
  

Key Improvements Over Your Original Approach: #

• Dynamic constituent handling: Automatically updates stocks based on DAX's quarterly rebalance schedule
• Consistent date alignment: No more mismatched lengths between stocks
• Cleaner data workflow: Uses tidyverse principles to keep data structured and easy to debug
• Flexible missing value handling: Choose between forward-filling, removing, or replacing missing data

内容的提问来源于stack exchange，提问作者Juli