convert2monthly
Aggregate timetable data to monthly periodicity
Description
Examples
Aggregate Timetable Data to Monthly Periodicity
Apply separate aggregation methods to related variables in a timetable
while maintaining consistency between aggregated results when converting to a monthly periodicity. You can use convert2monthly
to aggregate both intra-daily data and aggregated daily data. These methods result in equivalent monthly aggregates. Lastly, you can aggregate results on a specific day of each month (for example, the 15th), rather than the default end of the month.
Load a timetable (TT
) of simulated stock price data and corresponding logarithmic returns. The data stored in TT
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 TT
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('SimulatedStock.mat','TT'); head(TT)
Time Price Log_Return ____________________ ______ __________ 02-Jan-2018 11:52:11 100.71 0.0070749 02-Jan-2018 13:23:09 103.11 0.023551 02-Jan-2018 14:45:30 100.24 -0.028229 02-Jan-2018 15:30:48 101.37 0.01121 03-Jan-2018 10:02:21 101.81 0.0043311 03-Jan-2018 11:22:37 100.17 -0.01624 03-Jan-2018 14:45:20 99.66 -0.0051043 03-Jan-2018 14:55:39 100.12 0.0046051
First aggregate intra-daily prices and returns to daily periodicity. To maintain consistency between prices and returns, for any given trading day aggregate prices by reporting the last recorded price using "lastvalue"
and aggregate returns by summing all logarithmic returns using "sum"
.
TT1 = convert2daily(TT,'Aggregation',["lastvalue" "sum"]); head(TT1)
Time Price Log_Return ___________ ______ __________ 02-Jan-2018 101.37 0.013607 03-Jan-2018 100.12 -0.012408 04-Jan-2018 106.76 0.064214 05-Jan-2018 112.78 0.054856 08-Jan-2018 119.07 0.054273 09-Jan-2018 119.46 0.00327 10-Jan-2018 124.44 0.040842 11-Jan-2018 125.63 0.0095174
Use convert2monthly
to aggregate the data to a monthly periodicity and compare the results of two different approaches. The first approach computes monthly results by aggregating the daily aggregates and the second approach computes monthly results by directly aggregating the original intra-daily data. Note that although convert2monthly
reports results on the last business day of each month by default, you can report monthly results on the 15th of each month by using the optional name-value pair argument 'EndOfMonthDay'
.
tt1 = convert2monthly(TT1,'Aggregation',["lastvalue" "sum"],'EndOfMonthDay',15); % Daily to monthly tt2 = convert2monthly(TT ,'Aggregation',["lastvalue" "sum"],'EndOfMonthDay',15); % Intra-daily to monthly head(tt1)
Time Price Log_Return ___________ ______ __________ 12-Jan-2018 125.93 0.23056 15-Feb-2018 120.55 -0.043662 15-Mar-2018 113.49 -0.06035 13-Apr-2018 112.07 -0.012591 15-May-2018 110.47 -0.01438 15-Jun-2018 99.06 -0.10902 13-Jul-2018 95.74 -0.03409 15-Aug-2018 99.94 0.042934
head(tt2)
Time Price Log_Return ___________ ______ __________ 12-Jan-2018 125.93 0.23056 15-Feb-2018 120.55 -0.043662 15-Mar-2018 113.49 -0.06035 13-Apr-2018 112.07 -0.012591 15-May-2018 110.47 -0.01438 15-Jun-2018 99.06 -0.10902 13-Jul-2018 95.74 -0.03409 15-Aug-2018 99.94 0.042934
Notice that the results of the two approaches are the same. For months in which the 15th is not an NYSE trading day, the function reports results on the previous business day.
Use Custom Aggregation Method to Convert Timetable Daily Data to Monthly Periodicity
You can apply custom aggregation methods using function handles. Specify a function handle to aggregate related variables in a timetable
while maintaining consistency between aggregated results when converting from a daily to a monthly periodicity.
Load a timetable (TT
) of simulated stock price data and corresponding logarithmic returns. The data stored in TT
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 TT
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('SimulatedStock.mat','TT') head(TT)
Time Price Log_Return ____________________ ______ __________ 02-Jan-2018 11:52:11 100.71 0.0070749 02-Jan-2018 13:23:09 103.11 0.023551 02-Jan-2018 14:45:30 100.24 -0.028229 02-Jan-2018 15:30:48 101.37 0.01121 03-Jan-2018 10:02:21 101.81 0.0043311 03-Jan-2018 11:22:37 100.17 -0.01624 03-Jan-2018 14:45:20 99.66 -0.0051043 03-Jan-2018 14:55:39 100.12 0.0046051
First add another variable to TT
that contains the simple (proportional) returns associated with the prices in TT
and examine the first few rows.
TT.Simple_Return = exp(TT.Log_Return) - 1; % Log returns to simple returns
head(TT)
Time Price Log_Return Simple_Return ____________________ ______ __________ _____________ 02-Jan-2018 11:52:11 100.71 0.0070749 0.0071 02-Jan-2018 13:23:09 103.11 0.023551 0.023831 02-Jan-2018 14:45:30 100.24 -0.028229 -0.027834 02-Jan-2018 15:30:48 101.37 0.01121 0.011273 03-Jan-2018 10:02:21 101.81 0.0043311 0.0043405 03-Jan-2018 11:22:37 100.17 -0.01624 -0.016108 03-Jan-2018 14:45:20 99.66 -0.0051043 -0.0050913 03-Jan-2018 14:55:39 100.12 0.0046051 0.0046157
Create a function to aggregate simple returns and compute the monthly aggregates. To maintain consistency between prices and returns, for any given month, aggregate prices by reporting the last recorded price by using "lastvalue"
and report logarithmic returns by summing all intervening logarithmic returns by using "sum"
.
Notice that the aggregation function for simple returns operates along the first (row) dimension and omits missing data (NaN
s). For more information on custom aggregation functions, see timetable
and retime
. When aggregation methods are a mix of supported methods and user-supplied functions, the 'Aggregation'
name-value pair argument must be specified as a cell vector of methods enclosed in curly braces.
f = @(x)(prod(1 + x,1,'omitnan') - 1); % Aggregate simple returns tt = convert2monthly(TT,'Aggregation',{'lastvalue' 'sum' f}); head(tt)
Time Price Log_Return Simple_Return ___________ ______ __________ _____________ 31-Jan-2018 122.96 0.20669 0.2296 28-Feb-2018 121.92 -0.008494 -0.008458 29-Mar-2018 108.9 -0.11294 -0.10679 30-Apr-2018 110.38 0.013499 0.01359 31-May-2018 99.02 -0.10861 -0.10292 29-Jun-2018 96.24 -0.028477 -0.028075 31-Jul-2018 97.15 0.0094111 0.0094555 31-Aug-2018 101.51 0.043901 0.044879
Input Arguments
TT1
— Data to aggregate to monthly periodicity
timetable
Data to aggregate to a monthly 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 = convert2monthly(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 an intra-month 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, convert2monthly
applies the specified method to all time series in TT1
. If you specify a string vector or cell vector aggregation
, convert2monthly
applies aggregation(
to j
)TT1(:,
; j
)convert2monthly
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 . . . . . . . . . . . . . . . 31-Jan-2018 114.65 229.3 343.95 458.60 . . . . . . . . . . . . . . . 28-Feb-2018 129.19 258.38 387.57 516.76 . . . . . . . . . . . . . . . 31-Mar-2018 162.93 325.86 488.79 651.72 . . . . . . . . . . . . . . . 30-Apr-2018 171.72 343.44 515.16 686.88 . . . . . . . . . . . . . . . 31-May-2018 201.24 402.48 603.72 804.96 . . . . . . . . . . . . . . . 30-Jun-2018 223.22 446.44 669.66 892.88
TT2
(in which all days are business
days and the 'lastvalue'
is reported on the
last business day of each month) are as
follows.Time AAA BBB CCC ___________ ______ ______ ________________ 31-Jan-2018 114.65 229.30 343.95 458.60 28-Feb-2018 129.19 258.38 387.57 516.76 31-Mar-2018 162.93 325.86 488.79 651.72 30-Apr-2018 171.72 343.44 515.16 686.88 31-May-2018 201.24 402.48 603.72 804.96 30-Jun-2018 223.22 446.44 669.66 892.88
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
EndOfMonthDay
— Day of the month that ends months
last business day of month (default) | integer with value 1
to
31
Day of the month that ends months, specified as a scalar integer
with value 1
to 31
. For
months with fewer days than EndOfMonthDay
,
convert2monthly
reports aggregation results
on the last business day of the month.
Data Types: double
Output Arguments
TT2
— Monthly data
timetable
Monthly 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 a month within the sampling time span,
convert2monthly
returns a NaN
for that variable and month in TT2
.
If the first month (month1
) of
TT1
contains at least one business day, the
first date in TT2
is the last business date of
month1
. Otherwise, the first date in
TT2
is the next end-of-month business date of
TT1
.
If the last month (monthT
) of
TT1
contains at least one business day, the
last date in TT2
is the last business date of
monthT
. Otherwise, the last date in
TT2
is the previous end-of-month business date
of TT1
.
Version History
Introduced in R2021a
Abrir ejemplo
Tiene una versión modificada de este ejemplo. ¿Desea abrir este ejemplo con sus modificaciones?
Comando de MATLAB
Ha hecho clic en un enlace que corresponde a este comando de MATLAB:
Ejecute el comando introduciéndolo en la ventana de comandos de MATLAB. Los navegadores web no admiten comandos de MATLAB.
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)