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

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!

[dpb]

Be far easier to have at least a portion of the actual file...are the weekend dates included in the file or only trading times for one question.

Answer 1

[Jon]

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;

[Hamid Butt]

Thanks a bunch!

I also found another way which works. I don't know if it is effective and 100% accurate but it works so far.

AskTime = datetime(Ask.Time_UTC_, 'InputFormat', 'yyyy.MM.dd HH:mm:SS');
% Convert Ask.Time_UTC_ from 'cell' to 'datetime' format
AskTimedifference = [0;diff(datenum(AskTime))]; % Create a Vector which 
% measures the difference in time between individual quotes 
NewWeek = [1;find(AskTimedifference>1)]; % Output is a vector with the starting
% points of the individual weeks, by starting at week 1 and then finding values 
% where the difference in time between quotes exceeds 1 day

In case you have time, would you mind telling me if this code for writing those Weekly Close prices in a Matrix is efficient by any means?

%% Weekly Ask Close Data

% Creation of a Matrix which divides the total Ask Closing quotes into
% weekly quotes in order to estimate the weekly volatility later


% Syntax: 
%   - [WeeklyAskClose]=WeeklyAskClose(nPaths, NewWeek, AskClose)
% Inputs: 
%   - nPaths: Total amount of Weeks, with Weeks Starting on Sundays
%   - NewWeek: The Index of the Ask Close quote where the respective new
%   weeks start
%   - AskClose: Vector of all the Ask Close quote over the entire 6 month
%   sample period
%  
% Notes:
%   - 'AskWeeklyClose' columns each represent a different week, starting with
%   the first week in September of the respective year
%   - 'AskWeeklyClose' rows show the Ask closing prices for the week. The
%   Quote count is not always the same and might differ in:
%       -> week 1 and the last week (might not be full weeks from Sunday-Monday)
%       -> weeks where there is a shift from Summer to Winter Time
      
function [AskWeeklyClose,Rest]=WeeklyAskClose(nPaths, NewWeek, AskClose)
nPaths = length(NewWeek)-1; % Since 1 is added for Loop purposes in 'NewWeek'
% Variable, length(NewWeek) has to be subtracted by 1
for column = 1:nPaths % 26 full weeks of quote data, Rest values from week 27 added later
    AskWeekly(1:(NewWeek(column+1)-NewWeek(column)),column) = AskClose((NewWeek(column):(NewWeek(column+1)-1)),1);
    % 'AskWeekly' is a Matrix which will store all the close quote values for
    % each week in a separate column, always starting at row 1. Close
    % Quotes are taken from the 'AskClose' Vector containing all of the total
    % quotes for the 6 months. 'NewWeeks' contains the quote values which
    % singal the start of a new week and allow for extraction of the right
    % quotes for the specific Weeks from 'AskClose'.
    Rest = AskClose(NewWeek(column+1):length(AskClose)); % Values of the
    % remaining quotes of the last week
    
end
Rest(end+1:length(AskWeekly))=0; % Filling the Value Vector of the remaining
% quotes of the last week with zeros, so to match the correct size to add
% to the final Matrix with all the weeks and respective quotes
% 'AskWeeklyClose'
AskWeeklyClose = [AskWeekly,Rest];
end