Calculating sampling frequency of a 5 min time using the last tick interpolation

Hi,

I have a dataset like this, (1) is the serial, (2) year, (3) month, (4) days, (5) time in seconds, and (6) price. The upper and lower part as follows,

(1) (2) (3) (4) (5) (6)

1 2005 1 3 35 121.535000000000

1 2005 1 3 80 121.510000000000

1 2005 1 3 83 121.520000000000

1 2005 1 3 91 121.520000000000

1 2005 1 3 96 121.540000000000

1 2005 1 3 112 121.550000000000

1 2005 1 3 114 121.550000000000

1 2005 1 3 147 121.560000000000

1 2005 1 3 162 121.550000000000

1 2005 1 3 172 121.540000000000

1 2005 1 3 180 121.550000000000

1 2005 1 3 182 121.560000000000

1 2005 1 3 185 121.550000000000

1 2005 1 3 209 121.590000000000

1 2005 1 3 223 121.590000000000

1 2005 1 3 224 121.590000000000

1 2005 1 3 250 121.610000000000

1 2005 1 3 251 121.620000000000

1 2005 1 3 254 121.630000000000

1 2005 1 3 262 121.640000000000

1 2005 1 3 263 121.640000000000

1 2005 1 3 264 121.640000000000

1 2005 1 3 266 121.650000000000

1 2005 1 3 282 121.620000000000

1 2005 1 3 283 121.620000000000

1 2005 1 3 285 121.600000000000

1 2005 1 3 290 121.620000000000

1 2005 1 3 296 121.620000000000

1 2005 1 3 297 121.640000000000

1 2005 1 3 304 121.630000000000

1 2005 1 3 314 121.620000000000

1 2005 1 3 331 121.660000000000

1 2005 1 3 332 121.660000000000

1 2005 1 3 333 121.660000000000

1 2005 1 3 334 121.670000000000

1 2005 1 3 335 121.650000000000

1 2005 1 3 336 121.650000000000

1 2005 1 3 337 121.680000000000

1 2005 1 3 363 121.670000000000

1 2005 1 3 365 121.670200000000

1 2005 1 3 366 121.650000000000

1 2005 1 3 404 121.650000000000

1 2005 1 3 410 121.670000000000

1 2005 1 3 411 121.700000000000

1 2005 1 3 412 121.700000000000

1 2005 1 3 413 121.710000000000

1 2005 1 3 415 121.710000000000

1 2005 1 3 416 121.710000000000

1 2005 1 3 418 121.710000000000

1 2005 1 3 419 121.710000000000

1 2005 1 3 421 121.710000000000

1 2005 1 3 426 121.720000000000

1 2005 1 3 446 121.710000000000

1 2005 1 3 457 121.710000000000

1 2005 1 3 471 121.710000000000

1 2005 1 3 473 121.710000000000

1 2005 1 3 476 121.710000000000

1 2005 1 3 484 121.720000000000

1 2005 1 3 499 121.720000000000

1 2005 1 3 521 121.720000000000

1 2005 1 3 531 121.710000000000

1 2005 1 3 532 121.710000000000

1 2005 1 3 534 121.700000000000

1 2005 1 3 573 121.720000000000

1 2005 1 3 574 121.720000000000

1 2005 1 3 588 121.710000000000

1 2005 1 3 597 121.690000000000

1 2005 1 3 682 121.650000000000

1 2005 1 3 694 121.650000000000

1 2005 1 3 710 121.670000000000

1 2005 1 3 712 121.670000000000

1 2005 1 3 713 121.670000000000

1 2005 1 3 739 121.690000000000

1 2005 1 3 744 121.690000000000

1 2005 1 3 745 121.670000000000

1 2005 1 3 746 121.660000000000

1 2005 1 3 777 121.710000000000

1 2005 1 3 781 121.660000000000

1 2005 1 3 801 121.650000000000

1 2005 1 3 811 121.650000000000

1 2005 1 3 812 121.655000000000

1 2005 1 3 815 121.640000000000

1 2005 1 3 816 121.680000000000

1 2005 1 3 825 121.650000000000

1 2005 1 3 842 121.620000000000

1 2005 1 3 844 121.620000000000

1 2005 1 3 849 121.640000000000

1 2005 1 3 851 121.660000000000

1 2005 1 3 857 121.660000000000

1 2005 1 3 858 121.670000000000

1 2005 1 3 864 121.660000000000

1 2005 1 3 868 121.640000000000

1 2005 1 3 896 121.650000000000

1 2005 1 3 923 121.660000000000

1 2005 1 3 961 121.660000000000

1 2005 1 3 964 121.660000000000

1 2005 1 3 994 121.660000000000

1 2005 1 3 995 121.680000000000

1 2005 1 3 1027 121.700000000000

1 2005 1 3 1028 121.700000000000

1 2005 1 3 1029 121.700000000000

1 2005 1 3 1030 121.690000000000

1 2005 1 3 1035 121.690000000000

1 2005 1 3 1041 121.700000000000

1 2005 1 3 1053 121.689900000000

1 2005 1 3 1065 121.690000000000

1 2005 1 3 1074 121.680000000000

.

.

.

.

.

.

.

.

1 2012 1 23 23394 131.610000000000

1 2012 1 23 23395 131.610000000000

1 2012 1 23 23396 131.610000000000

1 2012 1 23 23397 131.610000000000

1 2012 1 23 23398 131.600000000000

1 2012 1 23 23399 131.600000000000

1 2012 1 23 23400 131.600000000000

and I want to calculate the the price at a 5 minute frequency, using last tick interpolation and construct 5 minutes log-returns. May you guide me to get the code.

Answer 1

[Altaïr]

Hey @Emad,

To calculate the 5-minute log return, the data can be resampled at 5-minute intervals and then the log and diff commands can be used to compute the log return. Here's an example snippet illustrating this process:

% Sample data
data = [
1	2005	1	3	0	121.535000000000
1	2005	1	3	209	121.590000000000
1	2005	1	3	363	121.670000000000
1	2005	1	3	499	121.720000000000
1	2005	1	3	682	121.650000000000
1	2005	1	3	801	121.650000000000
1	2005	1	3	961	121.660000000000
];
% Convert to table
dataTable = array2table(data, 'VariableNames', {'Serial', 'Year', 'Month', 'Day', 'TimeInSeconds', 'Price'});
% Convert to datetime
dataTable.DateTime = datetime(dataTable.Year, dataTable.Month, dataTable.Day) + seconds(dataTable.TimeInSeconds);
% Sort by DateTime
dataTable = sortrows(dataTable, 'DateTime');
% Convert to timetable
dataTimetable = table2timetable(dataTable(:, {'DateTime', 'Price'}));
% Resample at 5-minute intervals using last tick interpolation
resampledTimetable = retime(dataTimetable, 'regular', 'previous', 'TimeStep', minutes(5));
% Calculate log-returns
resampledTimetable.LogReturns = [NaN; diff(log(resampledTimetable.Price))];
% Display the first few rows of the result
disp(resampledTimetable);

          DateTime          Price     LogReturns 
    ____________________    ______    ___________

    03-Jan-2005 00:00:00    121.53            NaN
    03-Jan-2005 00:05:00    121.59     0.00045244
    03-Jan-2005 00:10:00    121.72      0.0010686
    03-Jan-2005 00:15:00    121.65    -0.00057526
    03-Jan-2005 00:20:00    121.66       8.22e-05

In this snippet, the dataset is first converted to a timetable. The retime function is then used to resample the timetable at 5-minute intervals, using the last observed value for interpolation. The log returns are subsequently calculated. For more detailed information on the retime function, please refer to the documentation page:

https://www.mathworks.com/help/releases/R2023a/matlab/ref/timetable.retime.html