如何识别每周实际市场收盘日并提取对应ETF收盘价数据

Great job getting a working solution up and running—let's refine this to be cleaner, more efficient, and easier to maintain, especially since you'll be scaling to 60+ ETFs and 40 weeks of data soon. Your core goal (grabbing the last trading day's close per week) can be achieved far more directly with pandas built-in tools, which will also avoid edge cases your current method might hit (like if your date range doesn't start on a Monday).

Optimized Solution Breakdown #

We'll focus on two key improvements:

1. Faster data fetching: Batch-download ETF data instead of looping through tickers (cuts down on API calls and overhead).
2. Simpler weekly close filtering: Use pandas grouping/resampling to directly grab the last trading day's close per week—no need for manual day-difference calculations.

Here's the streamlined code:

import yfinance as yf
import pandas as pd
from datetime import date, timedelta

# Configure parameters
end_date = date.today()
start_date = end_date - timedelta(days=50)
tickers = ['VUG', 'VV', 'MGC', 'MGK', 'VOO', 'VXF', 'VBK', 'VB']

# 1. Batch-download ONLY the Close column (saves memory/bandwidth)
raw_data = yf.download(
    tickers,
    start=start_date,
    end=end_date,
    progress=False  # Hides download progress, cleaner for Power BI
)['Close']

# 2. Convert wide-format data to long-format (easier to work with in Power BI)
long_data = raw_data.stack().reset_index()
long_data.columns = ['Date', 'Ticker', 'Close']

# 3. Grab the last trading day's close per week, per ETF
# 'W-FRI' sets the week to end on Friday, but .last() will automatically pick the prior trading day if Friday is a holiday
weekly_closes = long_data.groupby(
    [pd.Grouper(key='Date', freq='W-FRI'), 'Ticker']
).last().reset_index()

# Optional: If you want to explicitly use the actual last day of the week (regardless of day), use freq='W' instead
# weekly_closes = long_data.groupby([pd.Grouper(key='Date', freq='W'), 'Ticker']).last().reset_index()

Why This Works Better #

• Batch downloading: yfinance handles multiple tickers in one request, which is way faster than looping through each ETF individually—critical when you scale to 60+ assets.
• Targeted data fetch: We only download the Close column upfront, so we don't waste time/space on unused columns like Volume or High.
• Robust weekly filtering: Using pd.Grouper with freq='W-FRI' tells pandas to group dates into weeks ending on Friday. The .last() method grabs the final row in each group, which automatically adapts to holidays (e.g., if Friday is closed, it picks Thursday's data). No manual NaN handling or day-difference math required!
• Power BI-friendly: The long-format structure (Date, Ticker, Close) plays nicely with Power BI's data modeling tools.

Quick Notes on Your Original Code #

Just to call out small fixes for context:

• You had duplicate filtering logic (data = data[data['Days'] != -1] followed by data = data[data['Days'].ne(-1)])—only one of these is needed.
• Your day-difference method relies on the date range starting on Monday, which could break if your start date falls on a different day (the optimized method avoids this entirely).

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