//导入所需的包
import xxx(所用数据库)
import pandas as pd
import matplotlib.pylab as plt
import statsmodels.api as sm
//创建dictionary，包含目标股票，大盘，开始截止日期
parm = {'firm': 'TSLA', 'index': 'SPY', 'startdate': '2012-04-01', 'enddate': '2022-03-31'}
//连接wrds数据库
db = xxx.Connection(xxx_username = yourname)
//自建函数，获得目标股票历年日价
def get_daily_stock_price_us(p):
df = db.raw_sql("""SELECT a.date, a.prc
FROM crsp.dsf a
LEFT JOIN crsp.dsfhdr b
ON a.permno = b.permno
WHERE b.htsymbol = %(firm)s and a.date >= %(startdate)s and a.date <= %(enddate)s""",
params = p, date_cols = ['date'])
return df
daily_price_firm = get_daily_stock_price_us(parm)
daily_price_firm = daily_price_firm.rename(columns = {"prc": parm['firm']})
//描述性统计
daily_price_firm.describe()
//自建函数，获取大盘每日点数
def get_daily_index_price(p):
df = db.raw_sql("""SELECT a.date, a.prc
FROM crsp.dsf a left join crsp.dsfhdr b
ON a.permno = b.permno
WHERE b.htsymbol = %(index)s and a.date >= %(startdate)s and a.date <= %(enddate)s""",
params = p, date_cols = ['date'])
return df
daily_index_price = get_daily_index_price(parm)
daily_index_price = daily_index_price.rename(columns = {"prc": parm['index']})
//将目标日价与大盘每日点数两张表合并成一张
df1 = daily_price_firm.merge(daily_index_price, left_on = 'date', right_on = 'date')
//日期从date型转成datetime型
daily_price_firm['date'] = pd.to_datetime(df1['date']).dt.date
daily_index_price['date'] = pd.to_datetime(df1['date']).dt.date
df1['date'] = pd.to_datetime(df1['date']).dt.date
//计算目标股票日价与大盘点数变动趋势之间的相关系数
df1.corr()
//绘制大盘点数历年变动趋势
daily_price_firm.set_index('date').plot(kind = 'line',
figsize = (12, 4),
legend = False,
title = f"{parm['firm']}'s Price History")
plt.show()
//计算以数据所包含期间内第一天目标股票日价为基础，大盘历年来每日对应股价
df1['SP500_rebased'] = df1['TSLA'][0] / df1['SPY'][0] * df1['SPY']
del df1['SPY']
//绘制目标股票日价与大盘每日对应股价趋势的组合图
df1.set_index('date').plot(kind = 'line',
figsize = (12, 6),
legend = True,
title = 'Price History')
plt.show()
//自建函数，获取目标股票历年月回报率
def get_stock_return_monthly(p):
df = db.raw_sql("""SELECT a.date, a.ret
FROM crsp.msf a
LEFT JOIN crsp.dsfhdr b
ON a.permno = b.permno
WHERE b.htsymbol = %(firm)s and a.date >= %(startdate)s and a.date <= %(enddate)s""",
params = p, date_cols = ['date'])
return df
Tesla = get_stock_return_monthly(parm)
//自建函数，获取大盘历年来月回报率
def get_index_return_monthly(p):
df = db.raw_sql("""SELECT date, sprtrn
FROM crsp.msi
WHERE date >= %(startdate)s and date <= %(enddate)s""",
params = p, date_cols = ['date'])
return df
SP500 = get_index_return_monthly(parm)
//线性回归，计算目标股票的CAPM模型，并绘散点+线性拟合图
X = SP500['sprtrn']
Y = Tesla['ret']
X = sm.add_constant(X)
model = sm.OLS(Y, X).fit()
print(model.summary())
predictions = model.predict(X)
model.params
print(f"The beta of {parm['firm']} is {model.params[1]}")
df2 = pd.concat([X, Y], axis = 1)
ax = df2.plot(x = 'sprtrn', y = 'ret', kind = 'scatter')
plt.scatter(X['sprtrn'], Y)
plt.xlabel("S&P 500 Return")
plt.ylabel("Tesla Return")
plt.plot(X['sprtrn'], predictions, "r")
plt.show()
//绘图分析发行发行目标股票公司的资产结构
def plot_assets(p):
stk_assets = db.raw_sql("""SELECT a.fyear, a.che, a.rect, a.invt, a.ppegt, a.intan
FROM comp.funda a
LEFT JOIN comp.names b
ON a.gvkey = b.gvkey
WHERE b.tic = %(firm)s and a.datafmt = 'STD' and a.consol = 'C' and a.indfmt = 'INDL' and a.fyear >= 2012 and a.fyear <= 2021""",
params = p)
stk_assets['fyear'] = stk_assets['fyear'].astype(int)
stk_assets = stk_assets.set_index('fyear')
stk_assets['intan'] = stk_assets['intan'].fillna(0).astype(int)
stk_assets[['che', 'rect', 'invt', 'ppegt', 'intan']].plot.area().set_title('Stucture of Assets')
plot_assets(parm)
//最后断开与数据库的连接
db.close()
