Need help with deleting rows in my table
2 ビュー (過去 30 日間)
古いコメントを表示
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 件のコメント
採用された回答
Guillaume
2019 年 3 月 22 日
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 件のコメント
Guillaume
2019 年 3 月 22 日
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:
その他の回答 (0 件)
参考
カテゴリ
Help Center および File Exchange で Data Type Conversion についてさらに検索
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!