convert2annual
Description
Examples
Aggregate Timetable Data to Annual Periodicity
Applyseparate aggregation methods to related variables in a timetable
while maintaining consistency between aggregated results when converting to an annual periodicity. You can use convert2annual
to aggregate both intra-daily data and aggregated monthly data. These methods result in equivalent annual aggregates.
Load a timetable (DataTimeTable
) of simulated stock price data and corresponding logarithmic returns. The data stored in DataTimeTable
is recorded at various times throughout the day on New York Stock Exchange (NYSE) business days from January 1, 2018, to December 31, 2020. The timetable DataTimeTable
also includes NYSE business calendar awareness. If your timetable does not account for nonbusiness days (weekends, holidays, and market closures), add business calendar awareness by using addBusinessCalendar
first.
load("SimulatedStockSeries.mat","DataTimeTable"); 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
First, aggregate intra-daily prices and returns to a monthly periodicity. To maintain consistency between prices and returns, for any given month aggregate prices by reporting the last recorded price using "lastvalue"
and aggregate returns by summing all logarithmic returns using "sum"
.
DTTMonthly1 = convert2monthly(DataTimeTable,Aggregation=["lastvalue" "sum"]); head(DTTMonthly1)
Time Price Log_Return ___________ ______ __________ 31-Jan-2018 117.35 0.13462 28-Feb-2018 113.52 -0.033182 31-Mar-2018 110.74 -0.024794 30-Apr-2018 105.58 -0.047716 31-May-2018 97.88 -0.075727 30-Jun-2018 99.29 0.014303 31-Jul-2018 102.72 0.033962 31-Aug-2018 124.99 0.19623
Use convert2annual
to aggregate the data to an annual periodicity and compare the results of the two different aggregation approaches. The first approach computes annual results by aggregating the monthly aggregates and the second approach computes annual results by directly aggregating the original intra-daily data. Notice that by default, convert2annual
reports results on the last business day of December. To change the month that ends annual periods, use the EndOfYearMonth
optional name-value argument for convert2annual
.
DTTAnnual1 = convert2annual(DTTMonthly1,Aggregation=["lastvalue" "sum"]) % Monthly to annual
DTTAnnual1=3×2 timetable
Time Price Log_Return
___________ ______ __________
31-Dec-2018 84.26 -0.19664
31-Dec-2019 153.22 0.59797
31-Dec-2020 301.04 0.67537
DTTAnnual2 = convert2annual(DataTimeTable,Aggregation=["lastvalue" "sum"]) % Intra-daily to semiannual
DTTAnnual2=3×2 timetable
Time Price Log_Return
___________ ______ __________
31-Dec-2018 84.26 -0.19664
31-Dec-2019 153.22 0.59797
31-Dec-2020 301.04 0.67537
The results of the two approaches are the same because each annual period contains exactly 12 calendar months.
Input Arguments
TT1
— Data to aggregate to annual periodicity
timetable
Data to aggregate to an annual 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 = convert2annual(TT1,'Aggregation',["lastvalue"
"sum"])
Aggregation
— 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
Aggregation method for TT1
defining how to
aggregate data over business days in a year to an annual
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, convert2annual
applies the specified method to all time series in TT1
. If you specify a string vector or cell vector aggregation
, convert2annual
applies aggregation(
to j
)TT1(:,
; j
)convert2annual
applies each aggregation method one at a time (for more details, see retime
). For example, consider an input daily timetable 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 . . . . . . . . . . . . . . . 29-Dec-2018 249.16 498.32 747.48 996.64 30-Dec-2018 250.21 500.42 750.63 1000.84 31-Dec-2018 256.75 513.50 770.25 1027.00
convert2annual
applies the aggregation
method "lastvalue"
, which reports for each
variable the values of the last business day of each year. The
aggregated annual results are as
follows:TT2 = convert2annual(TT1)
TT2 = 1×3 timetable Time AAA BBB CCC ___________ ______ ______ ________________ 31-Dec-2018 256.75 513.50 770.25 1027.00
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
EndOfYearMonth
— Month that ends annual periods
"December"
(weeks end on
Friday) (default) | integer with value 1
to
12
| "January"
| "February"
| "March"
| "April"
| "May"
| "June"
| "July"
| "August"
| "September"
| "October"
| "November"
| "December"
| character vector
Month that ends annual periods, specified as a value in this table.
Value | Month Ending Each Year |
---|---|
"January" or
1 | January |
"February" or
2 | February |
"March" or
3 | March |
"April" or
4 | April |
"May" or
5 | May |
"June" or
6 | June |
"July" or
7 | July |
"August" or
8 | August |
"September" or
9 | September |
"October" or
10 | October |
"November" or
11 | November |
"December" or
12 | December |
Data Types: double
| char
| string
Output Arguments
TT2
— Annual data
timetable
Annual 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,
convert2annual
returns a NaN
for that variable and annual period in TT2
.
If the first annual period (year1
) of
TT1
contains at least one business day, the
first date in TT2
is the last business date of
year1
. Otherwise, the first date in
TT2
is the next end-of-year-period business
date of TT1
.
If the last annual period (yearT
) of
TT1
contains at least one business day, the
last date in TT2
is the last business date of
yearT
. Otherwise, the last date in
TT2
is the previous end-of-year-period
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)