Beta計算 Python notebook 程序來了，老虎幫忙看看

這是對我 股票Beta的理解誤區 的補充

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%'