Need help with deleting rows in my table

2 ビュー (過去 30 日間)
Simen Bydal
Simen Bydal 2019 年 3 月 22 日
コメント済み: Guillaume 2019 年 3 月 22 日
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),:)

採用された回答

Guillaume
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], :)
Note the the financial toolbox also has functions to tell you if a particular date is a holiday:
%isbusday requires financial toolbox
%for norwegian holidays you would have to first create a holiday file with createholidays
noholsreturns = returns(isbusday(returns.Time), :);
  4 件のコメント
Simen Bydal
Simen Bydal 2019 年 3 月 22 日
編集済み: Guillaume 2019 年 3 月 22 日
You are a brilliant man:) It works!
Just for my learning experience: the first one is saying that if both of the "condtions" (diff) are different from zero then we keep only those rows, right?
But can you explain the second one for me in detail? why do you need ~ before (diff(returns.Value)? When I am trying to explain the code to myself it looks as if either condition is equal to zero we keep the row, but I clearly misunderstand something because the two lines of code provides the same answer in matlab...An oral explanation of what each step in the code does would be helpful for my learning.
Hope you understand (I am a beginner just wanting to learn).
Guillaume
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 ExchangeData Type Conversion についてさらに検索

製品


リリース

R2017a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by