# convert2daily

Aggregate timetable data to daily periodicity

Since R2021a

## Description

example

TT2 = convert2daily(TT1) aggregates data (for example, high-frequency and intra-day data) to a daily periodicity.

example

TT2 = convert2daily(TT1,Name,Value) uses additional options specified by one or more name-value arguments.

## Examples

collapse all

Load the simulated stock price data and corresponding logarithmic returns in SimulatedStockSeries.mat.

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.

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

Aggregate the daily price series to a daily series by reporting the final price of each day.

DailyPrice = convert2daily(DataTimeTable(:,"Price"));
tail(DailyPrice)
Time        Price
___________    ______

24-Dec-2020    286.35
25-Dec-2020    286.26
26-Dec-2020    285.68
27-Dec-2020    285.61
28-Dec-2020    294.36
29-Dec-2020    300.44
30-Dec-2020    303.84
31-Dec-2020    301.04

DailyPrice is a timetable containing the final prices for each reported day in DataTimeTable.

This example shows how to specify the appropriate aggregation method for the units of a variable.

Load the simulated stock price data and corresponding logarithmic returns in SimulatedStockSeries.mat.

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.

Aggregate the data so that the result has a daily periodicity. For each series, specify the aggregation method that is appropriate for the unit.

DailyTT = convert2daily(DataTimeTable,Aggregation=["lastvalue" "sum"])
DailyTT=1096×2 timetable
Time        Price     Log_Return
___________    ______    __________

01-Jan-2018    100.15     -0.023876
02-Jan-2018     99.72    -0.0043028
03-Jan-2018    105.57      0.057008
04-Jan-2018    109.01      0.032065
05-Jan-2018    110.69      0.015294
06-Jan-2018    110.48     -0.001899
07-Jan-2018    113.83      0.029872
08-Jan-2018    116.41      0.022412
09-Jan-2018    118.54      0.018132
10-Jan-2018    120.46      0.016067
11-Jan-2018    120.87     0.0033978
12-Jan-2018    119.91    -0.0079741
13-Jan-2018    117.38     -0.021325
14-Jan-2018    116.04     -0.011482
15-Jan-2018    114.72     -0.011441
16-Jan-2018    115.28     0.0048696
⋮

DailyTT1 is a timetable containing the daily final prices and log returns.

Verify the results for January 1, 2018, through January 3, 2018.

jan42018 = datetime(2018,01,04);
DataTimeTable(DataTimeTable.Time < jan42018,:)
ans=9×2 timetable
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
03-Jan-2018 14:45:20    105.57     -0.013922

DailyTT(DailyTT.Time < jan42018,:)
ans=3×2 timetable
Time        Price     Log_Return
___________    ______    __________

01-Jan-2018    100.15     -0.023876
02-Jan-2018     99.72    -0.0043028
03-Jan-2018    105.57      0.057008

By visual comparison, the daily final results match. Each computed daily log return is the sum of the log returns recorded during the corresponding day in the raw data. Cross-check the log returns of January 2 and 3 by computing the difference between the log final prices for each day.

verify = diff(log(DailyTT.Price));
verify(1:2)
ans = 2×1

-0.0043
0.0570

## Input Arguments

collapse all

Data to aggregate to a daily periodicity, specified as a timetable.

Each variable can be a numeric vector (univariate series) or numeric matrix (multivariate series).

Note

• NaNs indicate missing values.

• Timestamps must be in ascending or descending order.

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 = convert2daily(TT1,'Aggregation',["lastvalue" "sum"])

Intra-day aggregation method for TT1 defining how data is aggregated over business days, 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 timetable 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, convert2daily applies the specified method to all time series in TT1. If you specify a string vector or cell vector aggregation, convert2daily applies aggregation(j) to TT1(:,j); convert2daily 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 09:45:47    100.00    200.00    300.00    400.00
01-Jan-2018 12:48:09    100.03    200.06    300.09    400.12
02-Jan-2018 10:27:32    100.07    200.14    300.21    400.28
02-Jan-2018 12:46:09    100.08    200.16    300.24    400.32
02-Jan-2018 14:14:13    100.25    200.50    300.75    401.00
02-Jan-2018 15:52:31    100.19    200.38    300.57    400.76
03-Jan-2018 09:47:11    100.54    201.08    301.62    402.16
03-Jan-2018 11:24:23    100.59    201.18    301.77    402.36
03-Jan-2018 14:41:17    101.40    202.80    304.20    405.60
03-Jan-2018 16:00:00    101.94    203.88    305.82    407.76
04-Jan-2018 09:55:51    102.53    205.06    307.59    410.12
04-Jan-2018 10:07:12    103.35    206.70    310.05    413.40
04-Jan-2018 14:26:23    103.40    206.80    310.20    413.60
05-Jan-2018 13:13:12    103.91    207.82    311.73    415.64
05-Jan-2018 14:57:53    103.89    207.78    311.67    415.56
The corresponding default daily results representing TT2 (where the 'lastvalue' is reported for each day) are as follows.
Time         AAA       BBB            CCC
___________    ______    ______    ________________
01-Jan-2018    100.03    200.06    300.09    400.12
02-Jan-2018    100.19    200.38    300.57    400.76
03-Jan-2018    101.94    203.88    305.82    407.76
04-Jan-2018    103.40    206.80    310.20    413.60
05-Jan-2018    103.89    207.78    311.67    415.56

All methods omit missing data (NaNs) 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

## Output Arguments

collapse all

Daily data, returned as a timetable. The time arrangement of TT1 and TT2 are the same.

If a variable of TT1 has no records for a business day within the sampling time span, convert2daily returns a NaN for that variable and business day in TT2.

The first date in TT2 is the first business date on or after the first date in TT1. The last date in TT2 is the last business date on or before the last date in TT1.

## Version History

Introduced in R2021a