MATLAB Answers

Performing operations on non-uniform data

1 view (last 30 days)
Harsh Rob
Harsh Rob on 18 Jan 2020
Edited: dpb on 19 Jan 2020
I want to perform subtraction operation on the second column of the below datapoints-
I want this operation to be performed on every 20 minutes. Basically, I am trying to find out log returns at every 20 minutes. However, since my data is not uniform, i am not able to write a general code to do the same. For example, i start my calculations at 00:00:00 and then go to 00:20:00 and my code works. However, when it goes to 01:00:00, it does not find any data. Also it does not find any data at 01:20:00 or 01:40:00.
%Original dataset
1512604800 '07-Dec-2017 00:00:00' 14090
1512604801 '07-Dec-2017 00:00:01' 14090
1512604803 '07-Dec-2017 00:00:03' 14090
1512604804 '07-Dec-2017 00:00:04' 14090
1512604805 '07-Dec-2017 00:00:05' 14090
1512604807 '07-Dec-2017 00:00:07' 14090
1512604808 '07-Dec-2017 00:00:08' 14090
1512604809 '07-Dec-2017 00:00:09' 14090
1512604810 '07-Dec-2017 00:00:10' 14090
1512604812 '07-Dec-2017 00:00:12' 14090
1512604813 '07-Dec-2017 00:00:13' 14090
1512604814 '07-Dec-2017 00:00:14' 14090
1512604815 '07-Dec-2017 00:00:15' 14090.5
1512604816 '07-Dec-2017 00:00:16' 14098
1512604817 '07-Dec-2017 00:00:17' 14098.98
1512604818 '07-Dec-2017 00:00:18' 14099
1512604820 '07-Dec-2017 00:00:20' 14098.99
1512604821 '07-Dec-2017 00:00:21' 14099
1512604822 '07-Dec-2017 00:00:22' 14098.99
1512604823 '07-Dec-2017 00:00:23' 14099
1512604825 '07-Dec-2017 00:00:25' 14099
1512604826 '07-Dec-2017 00:00:26' 14099
%Reduced dataset (Just for understanding the 20 min datapoints)
1512604800 '07-Dec-2017 00:00:00' 14090
1512606000 '07-Dec-2017 00:20:00' 14369.99
1512607206 '07-Dec-2017 00:40:06' 14389.99
1512608402 '07-Dec-2017 01:00:02' 14370.03
1512609601 '07-Dec-2017 01:20:01' 14388
1512610801 '07-Dec-2017 01:40:01' 14399.48
1512612000 '07-Dec-2017 02:00:00' 14361.01
1512613200 '07-Dec-2017 02:20:00' 13900
1512614400 '07-Dec-2017 02:40:00' 14079
1512615600 '07-Dec-2017 03:00:00' 13974.03
1512616804 '07-Dec-2017 03:20:04' 14082.94
1512618001 '07-Dec-2017 03:40:01' 13976
1512619201 '07-Dec-2017 04:00:01' 13911.8
1512620400 '07-Dec-2017 04:20:00' 14099.985
%I need these returns (have calcualted on excel sheet)
1512604800 '07-Dec-2017 00:00:00' 14090 Desired_returns
1512606000 '07-Dec-2017 00:20:00' 14369.99 279.99
1512607206 '07-Dec-2017 00:40:06' 14389.99 20
1512608402 '07-Dec-2017 01:00:02' 14370.03 -19.96
1512609601 '07-Dec-2017 01:20:01' 14388 17.97
1512610801 '07-Dec-2017 01:40:01' 14399.48 11.48
1512612000 '07-Dec-2017 02:00:00' 14361.01 -38.47
1512613200 '07-Dec-2017 02:20:00' 13900 -461.01
1512614400 '07-Dec-2017 02:40:00' 14079 179
1512615600 '07-Dec-2017 03:00:00' 13974.03 -104.97
1512616804 '07-Dec-2017 03:20:04' 14082.94 108.91
1512618001 '07-Dec-2017 03:40:01' 13976 -106.94
1512619201 '07-Dec-2017 04:00:01' 13911.8 -64.2
1512620400 '07-Dec-2017 04:20:00' 14099.985 188.185
%Code
startDate = final_data(1,1);
interval = 20;
returnsForDay = [];
previousValue = 14389.99;
j=1;
tempDateArray = [];
flag = 0;
while j<=(length(final_data))
startdatetemp = final_data(j,1);
CurrentDate = datestr( unixtime_to_datenum( startdatetemp ) );
CurrentDateAfterInc = datestr( unixtime_to_datenum( startDate ) );
if(startDate == startdatetemp)
price_var = final_data(j,2);
fprintf('Previous value when datesat are equal %d',previousValue);
if(j==1)
previousValue = price_var;
end
newcol = length(returnsForDay);
j
returnsForDay(newcol + 1) = price_var - previousValue;
returnsForDay
previousValue = price_var;
flag = 1;
j=j+1;
elseif(startDate > startdatetemp)
if(flag == 0)
previousValue = final_data(j,2);
end
continue
elseif(startDate < startdatetemp)
newcol = length(returnsForDay);
fprintf('Previous value when datesat are not equal %d',previousValue);
if(flag ==1)
fprintf('am here');
returnsForDay(newcol + 1) = final_data(j,2) - previousValue;
else
if(j == 2)
returnsForDay(newcol + 1) = previousValue - final_data(j-1,2);
returnsForDay
previousValue = final_data(j-1,2);
else
returnsForDay(newcol + 1) = previousValue - final_data(j-2,2);
returnsForDay
previousValue = final_data(j-2,2);
end %enf of small if statement
end
end %end of bigger if statement
Converted_dates = datestr( unixtime_to_datenum( startDate ) );
startdateTEMP = posixtime(datetime(Converted_dates) + minutes(00:interval:interval));
startDate = startdateTEMP(1,2);
end

  2 Comments

dpb
dpb on 18 Jan 2020
  1. Attach a sample data file so folks have something can use to work with
  2. Use timetable() and datetime not ancient datenum
  3. retime() to minute keeping nearest value
Alternatively to 3. round (truncate, actually) to nearest minute first.
Harsh Rob
Harsh Rob on 18 Jan 2020
I have attached the dataset for one day for your reference.
Could you illustrate on the use of these suggestions?

Sign in to comment.

Answers (1)

dpb
dpb on 18 Jan 2020
Edited: dpb on 19 Jan 2020
t=timetable(datetime(final_data(:,1),'ConvertFrom','posixtime'),final_data(:,2)); % build timetable
t20=retime(t,'regular','next','TimeStep',minutes(20)); % 20-minute w/ first after
t20(isnan(t20.Var1),:)=[]; % rid of 08Dec 00:00:00 extrap
results in
>> [t20(1:10,:); t20(end-10:end,:)]
ans =
21×1 timetable
Time Var1
____________________ _____
07-Dec-2017 00:00:00 14090
07-Dec-2017 00:20:00 14370
07-Dec-2017 00:40:00 14390
07-Dec-2017 01:00:00 14370
07-Dec-2017 01:20:00 14388
07-Dec-2017 01:40:00 14399
07-Dec-2017 02:00:00 14361
07-Dec-2017 02:20:00 13900
07-Dec-2017 02:40:00 14079
07-Dec-2017 03:00:00 13974
....
07-Dec-2017 20:20:00 15927
07-Dec-2017 20:40:00 16130
07-Dec-2017 21:00:00 16466
07-Dec-2017 21:20:00 16778
07-Dec-2017 21:40:00 16755
07-Dec-2017 22:00:00 16750
07-Dec-2017 22:20:00 16996
07-Dec-2017 22:40:00 17213
07-Dec-2017 23:00:00 17193
07-Dec-2017 23:20:00 17098
07-Dec-2017 23:40:00 17200
>>
The difference is then simply
>> t20.Returns=[nan;diff(t20.Var1)]
t20 =
72×2 timetable
Time Var1 Returns
____________________ _____ _______
07-Dec-2017 00:00:00 14090 NaN
07-Dec-2017 00:20:00 14370 279.99
07-Dec-2017 00:40:00 14390 20
07-Dec-2017 01:00:00 14370 -19.96
07-Dec-2017 01:20:00 14388 17.97
07-Dec-2017 01:40:00 14399 11.48
07-Dec-2017 02:00:00 14361 -38.47
07-Dec-2017 02:20:00 13900 -461.01
07-Dec-2017 02:40:00 14079 179
07-Dec-2017 03:00:00 13974 -104.97
...
07-Dec-2017 20:00:00 15865 104.92
07-Dec-2017 20:20:00 15927 61.65
07-Dec-2017 20:40:00 16130 203.4
07-Dec-2017 21:00:00 16466 336.35
07-Dec-2017 21:20:00 16778 311.78
07-Dec-2017 21:40:00 16755 -22.865
07-Dec-2017 22:00:00 16750 -5.245
07-Dec-2017 22:20:00 16996 246
07-Dec-2017 22:40:00 17213 217.46
07-Dec-2017 23:00:00 17193 -20.55
07-Dec-2017 23:20:00 17098 -94.91
07-Dec-2017 23:40:00 17200 101.98
>>

  0 Comments

Sign in to comment.

Sign in to answer this question.

Products


Release

R2019a