Skip to content
Advertisement

Python pandas calculate rolling stock beta using rolling apply to groupby object in vectorized fashion

I have a large data frame, df, containing 4 columns:

JavaScript

etc.

I am attempting to calculate a common financial measure, known as beta, using a function, that takes two of the columns, ret_1m, the monthly stock_return, and ret_1m_mkt, the market 1 month return for the same period (period_id). I want to apply a function (calc_beta) to calculate the 12-month result of this function on a 12 month rolling basis.

To do this, I am creating a groupby object:

JavaScript

What I would like to do is use something like:

JavaScript

Now, here is the first problem. According to the documentation, pd.rolling_apply arg can be either a series or a data frame. However, it appears that the data frame I supply is converted into a numpy array that can only contain one column of data, rather than the two I have tried to supply. So my code below for calc_beta will not work, because I need to pass both the stock and market returns:

JavaScript

So my questions are as follows, I think it makes sense to list them in this way:

JavaScript

I gather that rolling_apply previously was unable to handle data frames, but the documentations suggests that it is now able to do so. My pandas.version is 0.16.1.

Thanks for any help! I have lost 1.5 days trying to figure this out and am totally stumped.

Ultimately, what I want is something like this:

JavaScript

etc.

Advertisement

Answer

JavaScript

I fix a forward looking bias for Happy001’s code. It’s a finance problem, so it should be cautious.

I find that vlmercado‘s answer is so wrong. If you simply use pd.rolling_cov and pd.rolling_var you are making mistakes in finance. Firstly, it’s obvious that the second stock CAN00WH0 do not have any NaN beta, since it use the return of CAN00WG0, which is wrong at all. Secondly, consider such a situation: a stock suspended for ten years, and you can also get that sample into your beta calculating.

I find that pandas.rolling also works for Timestamp, you can see how in my answer above if interested. I change the code of Happy001’s code . It’s not the fastest way, but is at least 20x faster than the origin code.

JavaScript
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement