Searching for multiple conditions in a large array

if ( strcmp(f(j,1),'OPTSTK') && strcmp(C(j,2),stock) && ((datenum(C(j,3),'mm/dd/yyyy')-Earnings_Date)>5 && (datenum(C(j,3),'mm/dd/yyyy')-Earnings_Date)<25 && found==0 && B(j,8)>5) && (Earnings_Date-(datenum(Date_1,'dd-mmm-yyyy')+L)>5))

Hi folks, I am working with a large set of files, each that have several thousands rows each. THe number of columns are constant so that helps. I have a few conditions on some of the colums. Which when all satisfied yield a unique row. What's the most efficent way of finding this? I have been strcmp, but takes quite some time to pull the result. I am bit rusty as I haven't MATLAB in many years.

I am searching if Coulmn1==A && Column2==B && Column3==C.

Cheers!

[David Hill]

A better example of your data would help. Do you have a cell array containing your data or a table or structural array?

[Adam Danz]

There's nothing that jumps out to me as terribly inefficient.

You could covert all of the date values to datetime format which would eliminate the need to do so within the set of conditions. That would surely speed up the line you shared.

Also, unless case is necesary, I always recommend using strcmpi instead of strcmp but that won't make a difference in timing.

Lastly, you could run the profiler to look deeper into what's sucking up the time.

[vipul chaudhary]

Sure, I am using xlsread to load my data. Then I am searching for row that satisfies- Instrument==FUTIDX && SYMBOL== Z and EXPIRY_DT==28-May-20.

TIA!

INSTRUMENT	SYMBOL	EXPIRY_DT	STRIKE_PR	OPTION_TYP	OPEN	HIGH	LOW	CLOSE	SETTLE_PR	CONTRACTS	VAL(mn)	OPEN_INT	CHG_IN_OI	TIMESTAMP
FUTIDX	Z	28-May-20	0	XX	192.65	193.5	189.25	190.75	190.75	309645	1186374.84	1179620	-34180	14-May-20
FUTIDX	Z	25-Jun-20	0	XX	193	193.75	189.75	190.65	190.65	4921	18859.31	55880	6640	14-May-20

[Adam Danz]

Instead of converting the datetime values to datenum, convert your "Earnings_Date" etc values to datetime. Doing that prior to tesing the conditions would make the conditions like faster.

Also, are you sure the time is being consumed by the conditions line and not other parts of your code?

Since you're using r2018a you can't use the newer and improved readtable function. But you could convert your cell array to a table using cell2table but I'm not sure whether that will speed things up or not.

I'm still not convinced that the conditions line consumes lots of time.

[vipul chaudhary]

Thanks both.

This is a little embarassing, I seem to have run into a bit of hurdle in writing a find function :| Is this how you would write to find the Index that matches this? Dataset- cell array.

PS: I have defined the variable Instrument and Symbol

f is what I am reading my excel file into.

find(f(:,1)==Instrument && f(:,2)==Symbol)

[Adam Danz]

Since f is not scalar you'll need to use only 1 ampersand.

ind = find(f(:,1)==Instrument & f(:,2)==Symbol);

alternatively, you could just use logical indexing

idx = f(:,1)==Instrument & f(:,2)==Symbol;

[vipul chaudhary]

Thanks Adam! To answer your previous question. It perhaps may not be slowing, I am unsure. Thing is, I wrote this 5 years back and I am going through it line by line now, trying to be as efficient as possible. I am reading several files in a loop so that's what is taking the most time. Need to figure out a better way for that.

On the preceeding comment, does indexing not work on tables? I am getting a "Undefined operator '==' for input arguments of type 'table'." error.

Thanks again.

[Adam Danz]

Indexing is different for tables. Here's everything you need to know about accessing and indexing table data. The table at the top of the page summarizes everything.

https://www.mathworks.com/help/matlab/matlab_prog/access-data-in-a-table.html

For example, instead of

strcmp(f(j,1),'OPTSTK')

use

strcmp(f{j,1},'OPTSTK')  % where f is your table

or you can use the header values (aka 'variable names')

strcmp(f.Var1(j),'OPTSTK') % where f is your table and Var1 is the header for column 1

[vipul chaudhary]

Thanks Adam! You have been very helpful. Have a great day.

Regards,