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?

1 次查看（过去 30 天）
John
John 2016-11-5
评论： John 2016-11-12
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.
  1 个评论
John
John 2016-11-9
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?

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Peter Perkins
Peter Perkins 2016-11-10
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
  2 个评论
Peter Perkins
Peter Perkins 2016-11-10
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.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Financial Toolbox 的更多信息

标签

产品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by