Aligning Time Series data and calculating CAPM beta.
5 visualizzazioni (ultimi 30 giorni)
Mostra commenti meno recenti
I have time series data(daily) for 10 years for 500 firms. I would like to calculate monthly beta. I have the data on an excel sheet with column company code name, date, closing price. for the 100 firms. There are lots of gaps in the data and data is in 1 single excel sheet.
This is wat I did;
1. I pivoted the data. (date as column headers and company codes as rows). (Not sure if pivoting was absolutely required)
I downloaded a Pivot function from File exchange.
2. Created a FOR loop, looping through each company
for i=2:500
3. Used datevec to get the Y and M.
[Y, M] = datevec(datesCol);
4. Pulled out the closing price for the company (one column in the pivot)
coPriceCol = (pvt_dta(i,2:end))';
5. Created a FOR loop, looping through each year
for yr=2001:2010
6. Created a FOR loop, looping through each month
for mo=1:12
7. Pull out data for the particular year and month
stk_data_monthly = coPriceCol(M==mo & Y==yr);
mkt_dta_monthly = mktPriceCol(M==mo & Y==yr);
8. Check if stock data and market data for the month is completely empty
if(isnan(stk_data_monthly))
elseif(isnan(mkt_dta_monthly))
9. Now before I work with this I need only data for which dates are aligned between the stock data and market data. I knew time series could help, but I am a newbie, had no clue, so decided to work it out with what I already know. This is what I did,
nor = ~(isnan(stk_data_monthly) | isnan(mkt_dta_monthly));
stk_data_monthly = stk_data_monthly(nor);
mkt_dta_monthly = mkt_dta_monthly(nor);
10. I then calculated returns as follows,
stk_returns = (stk_data_monthly(2:end,:) - stk_data_monthly(1:end-1,:)) ./ stk_data_monthly(1:end-1,:) ;
mkt_returns = (mkt_dta_monthly(2:end,:) - mkt_dta_monthly(1:end-1,:)) ./ mkt_dta_monthly(1:end-1,:) ;
11. Then regressed the data,
mkt_returns = [ones(size(mkt_returns,1), 1) mkt_returns];
regRes = (inv(mkt_returns'*mkt_returns)) * (mkt_returns'*stk_returns);
Now, I am sure there is a better way to go about this. While I don't have a major problem with the time it takes to run, (except reading the excel file), I would like to know how can this be done differently and of course better and more efficiently.
- how to eliminate the FOR loops and
- if there is a smarter way to pull out data pertaining to a particular month and year and
- if the aligning of dates can be done some way other than using the OR+NOT operation.
P.S: I am at the beginner's level. So this code might sound atrocious.
0 Commenti
Risposte (1)
Laura Proctor
il 26 Dic 2012
You're doing great for a beginner. You're using logical indexing in your code which is awesome.
I'm assuming that you are getting expected results with the code you have so far?
You don't need to pivot the data, but it might make it easier to work with. Did you use the import tool to import the data or did you do it programmatically? Could you show the code for importing the data?
You can probably remove the outer FOR loop that goes through each company completely and so for step 4:
coPrice = (pvt_data(2:500,2:end))';
I think that you might be able to do something that removes the for loops for the year and month using accumarray, but I'll need to think about that one for a bit.
I'm not sure what you are checking for step #8. If you want to check to see if there are all NaNs in the column of data, you can use all:
if all(isnan(coPrice))
But, I'm not sure that you even need to have this IF/ELSEIF statement in your code... I don't see how you are using it in the code that you have shown.
You may wish to use another variable name other than nor in step #9 since nor is a MATLAB function name.
stk_returns = diff(stk_data_monthly) ./ stk_data_monthly(1:end-1,:) ;
regRes = (mkt_returns'*mkt_returns)\(mkt_returns'*stk_returns);
In fact, you may find that it isn't necessary to precondition both sides by mkt_returns'.
regRes = mkt_returns\stk_returns;
Vedere anche
Categorie
Scopri di più su Calendar in Help Center e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!