- Attach a sample data file so folks have something can use to work with
- Use timetable() and datetime not ancient datenum
- retime() to minute keeping nearest value
Info
This question is closed. Reopen it to edit or answer.
Performing operations on non-uniform data
2 views (last 30 days)
Show older comments
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
Answers (1)
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
This question is closed.
See Also
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!