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
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
6. Created a FOR loop, looping through each month
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
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.