Main Content

convert2daily

Aggregate timetable data to daily periodicity

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

Apply separate aggregation methods to related variables in a timetable while maintaining consistency between aggregated results for a daily 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)
ans=8×2 timetable
            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

Aggregate prices and logarithmic returns to a daily periodicity. To maintain consistency between prices and returns, for any given trading day, aggregate the prices by reporting the last recorded price by using "lastvalue" and aggregate the returns by summing all logarithmic returns by using "sum".

tt = convert2daily(TT,'Aggregation',["lastvalue" "sum"]);
head(tt)
ans=8×2 timetable
       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 

To verify consistency, examine the input and output timetables for January 2 and 3, 2018.

TT(1:8,:)  % Input data for 02-Jan-2018 and 03-Jan-2018
ans=8×2 timetable
            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

tt(1:2,:)  % Return aggregated results
ans=2×2 timetable
       Time        Price     Log_Return
    ___________    ______    __________

    02-Jan-2018    101.37     0.013607 
    03-Jan-2018    100.12    -0.012408 

For each business day in TT, notice that the output aggregated price is the last price of the day and that the aggregated return is the sum of all logarithmic returns. Also, the aggregated returns are consistent with aggregated prices.

For example, the aggregated return for January 3, 2018, is -0.012408, which is the logarithmic return associated with the last prices recorded on January 2 and 3, 2018 (that is, -0.012408 = log(100.12) - log(101.37)).

The dates of the aggregated results are whole dates that indicate the dates for which aggregated results are reported.

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.

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 = 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.

Introduced in R2021a