Using panel data, how can I create a new table with weekly price data based on a table with daily price data, for every stock?

My current table (dimension 743644 x 3) consists of Column 1 'Ticker' (278 different stock tickers), Column 2 'Date' (daily format), and Column 3 'Price' (daily format). (excerpt hereafter)

The goal is to create a new table that displays the weekly ('SimpAvg') price together with the weekly date format (usually Friday, but 1 day prior if it is bank holiday, 2 days prior if THU and FRI are holidays, etc.) for EVERY ticker. Thanks for your help.

[John]

I generated the following code for 1 stock: Date vector (208x1) and Price vector (208x1)

% Convert time from Excel format to Matlab format

x = x2mdate(Date, 0);
t = datestr(x, 'dd-MM-YY');

% Create financial time series using daily prices
x0 = fints(x, PX_LAST, {'Price'}, 'd', 'Index');

% Conversion into weekly price series
newFIN = toweekly(x0, 'CalcMethod', 'SimpAvg', 'Altholidays', '');

How can I apply this rationale for my example (see above) by looping through the 278 stocks in the table?

Answer 1

[Peter Perkins]

John, I'll assume you have the Financial Toolbox. I think what you're looking to do is to calculate mean price, by ticker and week, with the exemplar date for each week being the last business day. There are several ways you could do that. Since you already have a table, varfun is one easy way.

First cook up some fake data.

>> Date = datetime(2016,3,[22;23;24;28;29;21;22;23;24;28;29],'Format','eee dd-MMM-yyyy');
>> Ticker = categorical({'A'; 'A'; 'A'; 'A'; 'A'; 'B'; 'B'; 'B'; 'B'; 'B'; 'B'});
>> Price = rand(size(Ticker));
>> TS = table(Date,Ticker,Price)
TS =
         Date          Ticker     Price  
    _______________    ______    ________
    Tue 22-Mar-2016    A          0.13197
    Wed 23-Mar-2016    A          0.94205
    Thu 24-Mar-2016    A          0.95613
    Mon 28-Mar-2016    A          0.57521
    Tue 29-Mar-2016    A          0.05978
    Mon 21-Mar-2016    B          0.23478
    Tue 22-Mar-2016    B          0.35316
    Wed 23-Mar-2016    B          0.82119
    Thu 24-Mar-2016    B         0.015403
    Mon 28-Mar-2016    B         0.043024
    Tue 29-Mar-2016    B          0.16899

Now find the last business day in each week, by first finding the end of the week and then stepping back.

>> hol = holidays(datetime(2016,1,1),datetime(2016,12,31))
>> EOWDate = dateshift(TS.Date,'end','week');
>> TS.BusDate = busdate(EOWDate,'previous',hol);
>> TS.BusDate.Format = ['eee ' TS.BusDate.Format]
TS =
         Date          Ticker     Price           BusDate      
    _______________    ______    _______    ___________________
    Tue 22-Mar-2016    A         0.64912    Thu Thu 24-Mar-2016
    Wed 23-Mar-2016    A         0.73172    Thu Thu 24-Mar-2016
    Thu 24-Mar-2016    A         0.64775    Thu Thu 24-Mar-2016
    Mon 28-Mar-2016    A         0.45092    Fri Fri 01-Apr-2016
    Tue 29-Mar-2016    A         0.54701    Fri Fri 01-Apr-2016
    Mon 21-Mar-2016    B         0.29632    Thu Thu 24-Mar-2016
    Tue 22-Mar-2016    B         0.74469    Thu Thu 24-Mar-2016
    Wed 23-Mar-2016    B         0.18896    Thu Thu 24-Mar-2016
    Thu 24-Mar-2016    B         0.68678    Thu Thu 24-Mar-2016
    Mon 28-Mar-2016    B         0.18351    Fri Fri 01-Apr-2016
    Tue 29-Mar-2016    B         0.36848    Fri Fri 01-Apr-2016

Finally, apply mean to the prices, grouping by ticker and week.

>> varfun(@mean,TS,'GroupingVariables',{'Ticker' 'BusDate'},'InputVariables','Price')
ans =
    Ticker      BusDate      GroupCount    mean_Price
    ______    ___________    __________    __________
    A         24-Mar-2016    3             0.67672   
    A         01-Apr-2016    2             0.31749   
    B         24-Mar-2016    4             0.35613   
    B         01-Apr-2016    2             0.10601

[Peter Perkins]

I should also have said that if you have access to R2016b, you could create TS as a timetable. You can use the retime function on a timetable to compute a weekly mean price, but retime only supports aggregating by time, and you want to aggregate by time and ticker. So a timetable might be more helpful to you for other reasons, but you can use varfun on either a table or a timetable.

[John]

Thanks a lot, Peter! Very much appreciated.