convert2weekly
Description
Examples
Aggregate Timetable Data to Weekly Periodicity
Load the simulated stock price data and corresponding logarithmic returns in SimulatedStockSeries.mat
.
load SimulatedStockSeries
The timetable DataTimeTable
contains measurements recorded at various, irregular times during trading hours (09:30 to 16:00) of the New York Stock Exchange (NYSE) from January 1, 2018, through December 31, 2020.
For example, display the first few observations.
head(DataTimeTable)
Time Price Log_Return ____________________ ______ __________ 01-Jan-2018 11:52:48 100 -0.025375 01-Jan-2018 13:23:13 101.14 0.011336 01-Jan-2018 14:45:09 101.5 0.0035531 01-Jan-2018 15:30:30 100.15 -0.01339 02-Jan-2018 10:43:37 99.72 -0.0043028 03-Jan-2018 10:02:21 100.11 0.0039033 03-Jan-2018 11:22:37 103.96 0.037737 03-Jan-2018 13:42:27 107.05 0.02929
DataTimeTable
does not include business calendar awareness. If you want to account for nonbusiness days (weekends, holidays, and market closures) and you have a Financial Toolbox™ license, add business calendar awareness by using the addBusinessCalendar
function.
Aggregate the price series to a weekly series by reporting the final price in each week.
WeeklyPrice = convert2weekly(DataTimeTable(:,"Price"));
WeeklyPrice
is a timetable containing the final prices for each reported week in DataTimeTable
.
Specify Aggregation Method for Each Variable
This example shows how to specify the appropriate aggregation method for the units of a variable. It also shows how to use convert2weekly
to aggregate both intra-day data and aggregated daily data, which result in equivalent weekly aggregates.
Load the simulated stock price data and corresponding logarithmic returns in SimulatedStockSeries.mat
.
load SimulatedStockSeries
The price series Price
contains absolute measurements, whereas the log returns series Log_Return
is the rate of change of the price series among successive observations. Because the series have different units, you must specify the appropriate method when you aggregate the series. Specifically, if you report the final price for a given periodicity, you must report the sum of the log returns within each period.
To understand how to maintain consistency among aggregation methods, use two approaches to aggregate DataTimeTable
so that the result has a weekly periodicity.
Pass
DataTimeTable
directly toconvert2weekly
.Aggregate
DataTimeTable
so that the result has a daily periodicity by usingconvert2daily
, then pass the result toconvert2weekly
.
In both cases, specify reporting the last price and the sum of the log returns for each period.
Directly aggregate the data so that the result has a weekly periodicity. For each series, specify the aggregation method that is appropriate for the unit.
aggmethods = ["lastvalue" "sum"]; WeeklyTT1 = convert2weekly(DataTimeTable,Aggregation=aggmethods)
WeeklyTT1=157×2 timetable
Time Price Log_Return
___________ ______ ___________
05-Jan-2018 110.69 0.076188
12-Jan-2018 119.91 0.080008
19-Jan-2018 116.6 -0.027992
26-Jan-2018 118.51 0.016248
02-Feb-2018 120.03 0.012744
09-Feb-2018 117.07 -0.02497
16-Feb-2018 117.06 -8.5423e-05
23-Feb-2018 116.72 -0.0029087
02-Mar-2018 109.98 -0.059479
09-Mar-2018 110.27 0.0026334
16-Mar-2018 107.35 -0.026837
23-Mar-2018 112.78 0.049344
30-Mar-2018 110.27 -0.022507
06-Apr-2018 105.27 -0.046403
13-Apr-2018 106.01 0.007005
20-Apr-2018 107.93 0.017949
⋮
WeeklyTT1
is a timetable containing the weekly data. Price
is a series of the final stock prices for each week, and Log_Return
is the sum of the log returns for each week.
Aggregate the data in two steps: aggregate the data so that the result has a daily periodicity, then aggregate the daily data to weekly data. For each series, specify the aggregation method that is appropriate for the unit.
DailyTT = convert2daily(DataTimeTable,Aggregation=aggmethods); tail(DailyTT)
Time Price Log_Return ___________ ______ ___________ 24-Dec-2020 286.35 -0.0067521 25-Dec-2020 286.26 -0.00031435 26-Dec-2020 285.68 -0.0020282 27-Dec-2020 285.61 -0.00024506 28-Dec-2020 294.36 0.030176 29-Dec-2020 300.44 0.020445 30-Dec-2020 303.84 0.011253 31-Dec-2020 301.04 -0.0092581
WeeklyTT2 = convert2weekly(DailyTT,Aggregation=aggmethods)
WeeklyTT2=157×2 timetable
Time Price Log_Return
___________ ______ ___________
05-Jan-2018 110.69 0.076188
12-Jan-2018 119.91 0.080008
19-Jan-2018 116.6 -0.027992
26-Jan-2018 118.51 0.016248
02-Feb-2018 120.03 0.012744
09-Feb-2018 117.07 -0.02497
16-Feb-2018 117.06 -8.5423e-05
23-Feb-2018 116.72 -0.0029087
02-Mar-2018 109.98 -0.059479
09-Mar-2018 110.27 0.0026334
16-Mar-2018 107.35 -0.026837
23-Mar-2018 112.78 0.049344
30-Mar-2018 110.27 -0.022507
06-Apr-2018 105.27 -0.046403
13-Apr-2018 106.01 0.007005
20-Apr-2018 107.93 0.017949
⋮
DailyTT
is a timetable with daily periodicity. Price
is a series of the final stock prices for each day, and Log_Return
is the sum of the log returns for each day.
WeeklyTT1
and WeeklyTT2
are equal.
convert2weekly
reports results on Fridays by default. For weeks during which Friday is not a trading day in the NYSE, the function reports results on the previous business day. You can use the name-value argument EndOfWeekDay
to specify a different day of the week that ends business weeks.
Input Arguments
TT1
— Data to aggregate to weekly periodicity
timetable
Data to aggregate to a weekly periodicity, specified as a timetable.
Each variable can be a numeric vector (univariate series) or numeric matrix (multivariate series).
Note
NaN
s indicate missing values.Timestamps must be in ascending or descending order.
By default, all days are business days. If your timetable does not account for nonbusiness
days (weekends, holidays, and market closures), add business calendar awareness by using
addBusinessCalendar
first. For example, the following command adds business calendar logic to include only NYSE
business
days.
TT = addBusinessCalendar(TT);
Data Types: timetable
Name-Value Arguments
Specify optional pairs of arguments as
Name1=Value1,...,NameN=ValueN
, where Name
is
the argument name and Value
is the corresponding value.
Name-value arguments must appear after other arguments, but the order of the
pairs does not matter.
Example: TT2 = convert2weekly(TT1,'Aggregation',["lastvalue"
"sum"])
Aggregation
— Aggregation method for TT1
data for intra-week or inter-day aggregation
"lastvalue"
(default) | "sum"
| "prod"
| "mean"
| "min"
| "max"
| "firstvalue"
| character vector | function handle | string vector | cell vector of character vectors or function handles
Aggregation method for TT1
defining how
data is aggregated over business days in an intra-week or
inter-day periodicity, specified as one of the following methods,
a string vector of methods, or a length
numVariables
cell vector of methods,
where numVariables
is the number of variables
in TT1
.
"sum"
— Sum the values in each year or day."mean"
— Calculate the mean of the values in each year or day."prod"
— Calculate the product of the values in each year or day."min"
— Calculate the minimum of the values in each year or day."max"
— Calculate the maximum of the values in each year or day."firstvalue"
— Use the first value in each year or day."lastvalue"
— Use the last value in each year or day.@customfcn
— A custom aggregation method that accepts a table variable and returns a numeric scalar (for univariate series) or row vector (for multivariate series). The function must accept empty inputs[]
.
If you specify a single method, convert2weekly
applies the specified method to all time series in TT1
. If you specify a string vector or cell vector aggregation
, convert2weekly
applies aggregation(
to j
)TT1(:,
; j
)convert2weekly
applies each aggregation method one at a time (for more details, see retime
). For example, consider a daily timetable
representing TT1
with three
variables.
Time AAA BBB CCC ___________ ______ ______ ________________ 01-Jan-2018 100.00 200.00 300.00 400.00 02-Jan-2018 100.03 200.06 300.09 400.12 03-Jan-2018 100.07 200.14 300.21 400.28 04-Jan-2018 100.08 200.16 300.24 400.32 05-Jan-2018 100.25 200.50 300.75 401.00 06-Jan-2018 100.19 200.38 300.57 400.76 07-Jan-2018 100.54 201.08 301.62 402.16 08-Jan-2018 100.59 201.18 301.77 402.36 09-Jan-2018 101.40 202.80 304.20 405.60 10-Jan-2018 101.94 203.88 305.82 407.76 11-Jan-2018 102.53 205.06 307.59 410.12 12-Jan-2018 103.35 206.70 310.05 413.40 13-Jan-2018 103.40 206.80 310.20 413.60 14-Jan-2018 103.91 207.82 311.73 415.64 15-Jan-2018 103.89 207.78 311.67 415.56 16-Jan-2018 104.44 208.88 313.32 417.76 17-Jan-2018 104.44 208.88 313.32 417.76 18-Jan-2018 104.04 208.08 312.12 416.16 19-Jan-2018 104.94 209.88 314.82 419.76
The corresponding default weekly results representing
TT2
(in which all days are business
days and the 'lastvalue'
is reported on
Fridays) are as
follows.
Time AAA BBB CCC ___________ ______ ______ ________________ 05-Jan-2018 100.25 200.50 300.75 401.00 12-Jan-2018 103.35 206.70 310.05 413.40 19-Jan-2018 104.94 209.88 314.82 419.76
The default 'lastvalue'
returns the latest
observed value in a given week for all variables in
TT1
.
All methods omit missing data (NaN
s) in direct aggregation calculations on each variable. However, for situations in which missing values appear in the first row of TT1
, missing values can also appear in the aggregated results TT2
. To address missing data, write and specify a custom aggregation method (function handle) that supports missing data.
Data Types: char
| string
| cell
| function_handle
Daily
— Intra-day aggregation method for TT1
"lastvalue"
(default) | "sum"
| "prod"
| "mean"
| "min"
| "max"
| "firstvalue"
| character vector | function handle | string vector | cell vector of character vectors or function handles
Intra-day aggregation method for TT1
, specified as an aggregation method, a
string vector of methods, or a length numVariables
cell vector of
methods. For more details on supported methods and behaviors, see the
'Aggregation'
name-value argument.
Data Types: char
| string
| cell
| function_handle
EndOfWeekDay
— Day of week that ends business weeks
"Friday"
(weeks end on
Friday) (default) | scalar integer with value 1
through
7
| "Sunday"
| "Monday"
| "Tuesday"
| "Wednesday"
| "Thursday"
| "Friday"
| "Saturday"
| character vector
Day of the week that ends business weeks, specified as a value in the table.
Value | Day Ending Each Week |
---|---|
"Sunday" or
1 | Sunday |
"Monday" or
2 | Monday |
"Tuesday" or
3 | Tuesday |
"Wednesday" or
4 | Wednesday |
"Thursday" or
5 | Thursday |
"Friday" or
6 | Friday |
"Saturday" or
7 | Saturday |
If the specified end-of-week day in a given week is not a business day, the preceding business day ends that week.
Data Types: double
| char
| string
Output Arguments
TT2
— Weekly data
timetable
Weekly data, returned as a timetable. The time arrangement of TT1
and TT2
are the same.
If a variable of TT1
has no business-day records
during an annual period within the sampling time span,
convert2weekly
returns a NaN
for that variable and annual period in TT2
.
If the first week (week1
) of
TT1
contains at least one business day, the
first date in TT2
is the last business date of
week1
. Otherwise, the first date in
TT2
is the next end-of-week business date of
TT1
.
If the last week (weekT
) of
TT1
contains at least one business day, the
last date in TT2
is the last business date of
weekT
. Otherwise, the last date in
TT2
is the previous end-of-week business date
of TT1
.
Version History
Introduced in R2021a
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)