Function/name mismatch – you use ['Close'] but the function is called get_adj_close_data.
If you truly want adjusted prices (usually better for returns): change to ['Adj Close'] (or rename the function).
Month-end pick – your groupby(...).tail(1) works; resample('M').last() is a common alternative. Either is fine.
Benchmark total return – you already noted this: ^GSPC is price index (no dividends). Beta/vol are fine, but long-run return comps will be lower than total return.
Excel save – you create the workbook but don’t save it. Add something like wb.save('beta_report.xlsx') (and optionally wb.close()).
Column formatting – your ranges match the output layout:
B (beta), C (rho), F (vol ratio) → 0.0000
D (sigma_s), E (sigma_m) → 0.0000% (those are decimals; percent format is appropriate)
Type hints – list[str] needs Python 3.9+. For broader compatibility: from typing import List then tickers: List[str].
Data gaps/NaNs – pandas’ corr skips NaNs pairwise, so you’re fine. Just be aware early months can be sparse for some tickers.
yf.download end date – yfinance usually treats end as inclusive for daily data, but behavior can vary; your 5y+15d window gives enough buffer either way.
Function/name mismatch – you use ['Close'] but the function is called get_adj_close_data.
If you truly want adjusted prices (usually better for returns): change to ['Adj Close'] (or rename the function). [yf.download default prices to adjusted prices]
Month-end pick – your groupby(...).tail(1) works; resample('M').last() is a common alternative. Either is fine. [resample('M').last() would show month-end date instead of original date]
Benchmark total return – you already noted this: ^GSPC is price index (no dividends). Beta/vol are fine, but long-run return comps will be lower than total return. [Unfortunately ^SP500TR does not work here, don't know why]
Excel save – you create the workbook but don’t save it. Add something like wb.save('beta_report.xlsx') (and optionally wb.close()).
Column formatting – your ranges match the output layout:
B (beta), C (rho), F (vol ratio) → 0.0000
D (sigma_s), E (sigma_m) → 0.0000% (those are decimals; percent format is appropriate)
Type hints – list[str] needs Python 3.9+. For broader compatibility: from typing import List then tickers: List[str].
Data gaps/NaNs – pandas’ corr skips NaNs pairwise, so you’re fine. Just be aware early months can be sparse for some tickers.
yf.download end date – yfinance usually treats end as inclusive for daily data, but behavior can vary; your 5y+15d window gives enough buffer either way.
Anaconda installation Jupyter notebook
# ********************* import yfinance as yf # pip install yfinance import pandas as pd import datetime import dateutil import xlwings as xw # ********************** def get_adj_close_data( tickers: list[str], start_date: datetime.date, end_date: datetime.date, interval: str='1d' ) -> pd.DataFrame: return yf.download(tickers=tickers, start=start_date, end=end_date, interval=interval)['Close'] def get_monthly_return_data( tickers: list[str], start_date: datetime.date, end_date: datetime.date ) -> pd.DataFrame: p = get_adj_close_data(tickers=tickers, start_date=start_date, end_date=end_date) # pick the last day's price of each month, not necessarily month end p = p.groupby(p.index.to_period('M')).tail(1) return (p/p.shift(1) - 1)[tickers] def calc_beta_etc(monthly_return_data: pd.DataFrame, benchmark: str) -> pd.DataFrame: """ Input: monthly_return_data: DataFrame with timestamp index and stock/index ticker columns including the benchmark benchmark: str of benchmark ticker Output: DataFrame with """ r_m = monthly_return_data[benchmark] def calc_one_beta_etc(r_s, r_m): """ r_s: monthly stock returns r_m: monthly benchmark returns """ rho = r_s.corr(r_m) # correlation sigma_s = r_s.std() * (12**0.5) # annualized volatility sigma_m = r_m.std() * (12**0.5) # annualized volatility beta = rho * sigma_s / sigma_m return pd.Series( data = {'beta': beta, 'rho': rho, 'sigma_s': sigma_s, 'sigma_m': sigma_m, 'vol ratio': sigma_s/sigma_m, } ) return monthly_return_data.apply(lambda c: calc_one_beta_etc(r_s=c, r_m=r_m), axis=0).T # ********************************* end_date = datetime.date(2025, 7, 31) start_date = end_date - dateutil.relativedelta.relativedelta(years=5, days=15) tickers = [ 'AAPL', 'AMZN', 'GOOG', 'GOOGL', 'META', 'MSFT', 'NVDA', 'TSLA', 'T', '^GSPC' # S&P500 Index, unfortunately the total return index isn't available from yfinance ] monthly_return_data = get_monthly_return_data(tickers=tickers, start_date=start_date, end_date=end_date) beta_etc = calc_beta_etc(monthly_return_data=monthly_return_data, benchmark='^GSPC') # ********************************* wb = xw.Book() ws1 = wb.sheets.add('monthly returns') ws1.range('A1').options(index=True, header=True).value = monthly_return_data ws2 = wb.sheets.add('beta etc') ws2.range('A1').options(index=True, header=True).value = beta_etc ws2.range('B:C,F:F').number_format = '0.0000' ws2.range('D:E').number_format = '0.0000%'Key nits (easy fixes/notes):
Function/name mismatch – you use ['Close'] but the function is called get_adj_close_data.
If you truly want adjusted prices (usually better for returns): change to ['Adj Close'] (or rename the function).
Month-end pick – your groupby(...).tail(1) works; resample('M').last() is a common alternative. Either is fine.
Benchmark total return – you already noted this: ^GSPC is price index (no dividends). Beta/vol are fine, but long-run return comps will be lower than total return.
Excel save – you create the workbook but don’t save it. Add something like wb.save('beta_report.xlsx') (and optionally wb.close()).
Column formatting – your ranges match the output layout:
B (beta), C (rho), F (vol ratio) → 0.0000
D (sigma_s), E (sigma_m) → 0.0000% (those are decimals; percent format is appropriate)
Type hints – list[str] needs Python 3.9+. For broader compatibility: from typing import List then tickers: List[str].
Data gaps/NaNs – pandas’ corr skips NaNs pairwise, so you’re fine. Just be aware early months can be sparse for some tickers.
yf.download end date – yfinance usually treats end as inclusive for daily data, but behavior can vary; your 5y+15d window gives enough buffer either way.
adjust for leverage? How this beta compare with industry average?
过去我有什么想法要实现都要等比较会编程的组员，现在直接交给AI生成一个再修补
Key nits (easy fixes/notes):
Function/name mismatch – you use ['Close'] but the function is called get_adj_close_data.
If you truly want adjusted prices (usually better for returns): change to ['Adj Close'] (or rename the function). [yf.download default prices to adjusted prices]
Month-end pick – your groupby(...).tail(1) works; resample('M').last() is a common alternative. Either is fine. [resample('M').last() would show month-end date instead of original date]
Benchmark total return – you already noted this: ^GSPC is price index (no dividends). Beta/vol are fine, but long-run return comps will be lower than total return. [Unfortunately ^SP500TR does not work here, don't know why]
Excel save – you create the workbook but don’t save it. Add something like wb.save('beta_report.xlsx') (and optionally wb.close()).
Column formatting – your ranges match the output layout:
B (beta), C (rho), F (vol ratio) → 0.0000
D (sigma_s), E (sigma_m) → 0.0000% (those are decimals; percent format is appropriate)
Type hints – list[str] needs Python 3.9+. For broader compatibility: from typing import List then tickers: List[str].
Data gaps/NaNs – pandas’ corr skips NaNs pairwise, so you’re fine. Just be aware early months can be sparse for some tickers.
yf.download end date – yfinance usually treats end as inclusive for daily data, but behavior can vary; your 5y+15d window gives enough buffer either way.
https://octave.org/
the more I know, the deeper I am trapped
the market is irrational