Need help with deleting rows in my table
4 views (last 30 days)
Show older comments
Hi,
We are working on a project where we have importated dailyreturn (column A) and dailyreturnindex (column B) as well as dailyreturntime2 (this is a date on the format 732316 found in column C). But the data includes non-trading days such as Norwegian holidays and we want to remove these. We therefore want to make a code with a rule such that if both dailyreturn(t) = dailyreturn(t-1) and dailyreturnindex(t) = dailyreturnindex(t-1) holds at the same time we want to delete that particular row.
Could someone please help me? I have attatched the code I have tried to write, but the deleting process is not working as I hoped.
Thanks in advance.
%% Read excel
dailyreturn = xlsread('storfil.xlsx','KOG','D4:D3692');
dailyreturnindex= xlsread('DailyOSEBXOSEAX.xlsx','OSEAX Index','D8:D3695');
dailyreturntime = xlsread('DailyOSEBXOSEAX.xlsx','OSEAX Index','A8:A3695');
dailyreturntime2 = dailyreturntime + 693960;
%%Convert to table
table_dailyreturn =array2table(dailyreturn);
table_dailyreturnindex =array2table(dailyreturnindex);
table_dailyreturntime = array2table(dailyreturntime);
table_dailyreturntime2 = array2table(dailyreturntime2);
merged_table=[table_dailyreturn table_dailyreturnindex table_dailyreturntime2];
array=table2array(merged_table);
%%delete non-trading days (see rule in description)
array = array(any(diff(array,1),2),:)
0 Comments
Accepted Answer
Guillaume
on 22 Mar 2019
Why are you reading the data as arrays, converting to table, then converting back to array. Choose one type and stick to it rather than wasting time on unnecessary conversions.
Personally, I'd use tables, so:
dailyreturn = readtable('storfil.xlsx', 'Sheet', 'KOG', 'Range', 'D4:D3692', 'ReadVariableNames', false);
dailyreturnindex= readtable('DailyOSEBXOSEAX.xlsx', 'Sheet', 'OSEAX Index', 'Range', 'D8:D3695', 'ReadVariableNames', false);
dailyreturntime = readtable('DailyOSEBXOSEAX.xlsx', Sheet', 'OSEAX Index','Range', 'A8:A3695', 'ReadVariableNames', false);
I'm assuming your columns don't have header above (hence the 'ReadVariableNames', false).
I'm not sure why you're adding 693960 to the time. If you're using readtable matlab will automatically convert excel times into datetime.
You can then merge the 3 columns and manipulate the table directly.
dailyreturn.Properties.VariableNames = {'Value'}; %make sure names of columns are not the same in each table
dailyreturnindex.Properties.VariableNames = {'Index'};
dailyreturntime.Properties.VariableNames = {'Time'};
returns = [dailureturntime, dailyreturn, dailyreturnindex];
You could even convert the table to a timetable if you need to resample according to time. In any case, there's certainly no need to convert to an array.
If I understood correctly what you want:
noholsreturns = returns([true; diff(returns.Value) ~= 0 & diff(returns.Index) ~= 0], :)
%isbusday requires financial toolbox
%for norwegian holidays you would have to first create a holiday file with createholidays
noholsreturns = returns(isbusday(returns.Time), :);
4 Comments
Guillaume
on 22 Mar 2019
1st one: keep the values if the difference in the first column is not 0 or the difference in the 2nd column is not 0
2nd one: discard values if the difference in the first column is 0 and the difference in the 2nd column is 0. invert that to keep values.
In boolean language:
More Answers (0)
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!