MATLAB: Splitting up data vector into matrix with weekly data separated by columns

datadatesmatrixvectorweekly

Greetings,
I have a Dataset containing 1-minute Stock Returns for a Period of 6 months. I have a Table containing the Date in the first column, and then the closing Prices in the next column. In total that equals roughly 187000 closing prices. The 6-month Period starts at 2008.09.01 00:00:00, and ends 2009.03.01.00:00:00.
Since i want to do weekly regressions, i now have to convert the vector of the closing Prices into a Matrix, where the first column contains all the closing prices for the first Week, the second column contains all the closing prices of the second week and so on.
However, weekends are excluded in the dataset, and not every week has the same amount of closing prices, since at some point time shift from Summer to Winter time has to be considered, which is why i cannot simply divide the data into equal parts. The last closing price of the week is always 20:59:00 (Summer Time) or 21:59:00 (Winter Time) on each Friday. Quotes then continue in the next week on Sunday 21:00:00 (Summer Time) or 22:00:00 (Winter Time).
Therefore I either need a loop, which writes me a new matrix from the vector until the condition: write data in first column until the last closing price of the week has arrived, then stop, proceed to next column and then continue writing the next datapoint in the next column until the last closing price of week 2 has arrived and so on,
Or maybe if there is some way to do this without a loop just using the find function to show me where exactly in the dataset every new week starts, however, i haven't been able to do that.
Thanks in Advance!

Best Answer

I think the key challenge is to find the index (row number in your table) where the end of each week occurs. Here is one way you could do this. Let us say your table is called T, and you already have two columns, one for time, which for brevity I will say has a column name of t, and the other for the stock price. I will also assume that T.t is already a MATLAB datetime array, if not you can convert it to one. See for example https://www.mathworks.com/help/matlab/matlab_prog/convert-between-datetime-arrays-numbers-and-strings.html#bth57t1-1 
% add column with day numbers 1-7 1 -represents Sunday
T.day = daynumber(T.t);
% get logical index which is true at last time on each Friday
% note that diff(T.day) is one element shorter than T.day, so I assume that last time is not the end of a
% end of the day on a Friday and pad with a false to get both vectors equal length
idl = [diff(T.day)==1;false]&T.day==6; % look for jump to next day numbe on Fridays
% if you actually wanted the row numbers for the last time on each Friday you could do a find instead, but
% usually the logical indices can be used directly and that is more efficient, but you could use
idx = find([diff(T.day)==1;false]&T.day==6;)
I think once you know where the jumps occur you can do what ever is needed to manipulate the data into the arrays you want for regression or whatever.
If you don't have any data for Saturday and Sunday in your table you will have to modify the jump logic a little to catch any change in day, maybe better to use
idl = [diff(T.day)~=0;false]&T.day==6;
Related Question