0

I'm trying to calculate various beta for a CAPM model from past recorded monthly stock prices and market prices.

             Date  S_adjclose   M_adjclose  S_returns  M_returns
    1 2006-03-01   83.057541  1294.869995   0.031026   0.011035
    2 2006-04-01   88.786636  1310.609985   0.066703   0.012082
    3 2006-05-01   83.362877  1270.089966  -0.063033  -0.031405
    4 2006-06-01   81.792465  1270.199951  -0.019018   0.000087
    5 2006-07-01   86.534538  1276.660034   0.056358   0.005073
    ..         ...         ...          ...        ...        ...
   168 2020-02-01    8.720000  2954.219971  -0.048135  -0.087860
   169 2020-03-01    6.420000  2584.590088  -0.306201  -0.133668
   170 2020-04-01    7.410000  2912.429932   0.143412   0.119421
   171 2020-05-01    8.400000  3044.310059   0.125401   0.044287
   172 2020-06-01    9.460000  3053.239990   0.118841   0.002929

the Date column data type is already datetime64.

so far I managed to calculate the beta for the entire dataset using

#cov matrix
covmat = np.cov(data["S_returns"], data["M_returns"])

#get beta
beta = covmat[0, 1] / covmat[1, 1]

Now I want to create a new data frame with the beta recorded for every five years, in monthly intervals. For example from March 2006 till March 2011, than from April 2006 till April 2011, so on and so forth until June 2020.

Is there a way I can loop this somehow using the timestamps?

Thanks,

Patrick

Patrick Bouchard
  • 11
  • 2
  • You might want to check the DataFrame method `rolling`. You can find its documentation [here](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rolling.html?highlight=rolling#pandas.DataFrame.rolling) – nomansland008 Jul 02 '20 at 13:32
  • Try to avoid looping in `pandas` if you can, there most likely are other, very efficient options. Have a look at [resample](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.resample.html#pandas-dataframe-resample) - is that what you have in mind? Or do you want a moving average? – FObersteiner Jul 02 '20 at 18:49
  • Thanks for the hint on `rolling`, I will look into that. @MrFuppes, thank you for the tip! I am not trying shrink the data down, rather the aim is to create different "beta" values which is computed with the available data, with one month shifted forward for each "beta" , I guess its more similar to finding a moving average now come to think of it (: – Patrick Bouchard Jul 02 '20 at 20:54
  • https://stackoverflow.com/questions/52875986/rolling-covariance-on-df – Patrick Bouchard Jul 02 '20 at 21:37

0 Answers0