access data in table using for loop

Hi,

I have attached my data here(Data.mat).I have got a table having data for different companies(stock prices, returns dates and ticker of tha company).I want to extract stock prices and returns of each company from this table using the ticker of the company and then store that stock data in separate tables.one table for each comany.I managed to extract data based on company ticker using below code

idx={'OMFGA','MBNC'}; 
for i=1:numel(idx) 
B=strcmp(compTik,idx(i)); %trying to compare company tickers
OMFGA= data (B,:); % storing stock data related to OMFGA 
end

but this code just give me data of MBNC and not that of both MBNC and OMFGA.This is just an example code I have got alot of companies and I want my loop to pick company ticker automatically from a column vector and extract stock data for that company ad store it in a separate table.Then pick another company and do the same for that company too.

My second question is , I have got this column vector of company ticker in which the tickers are repeated (attached data as compTik.mat) for example

A
A
A
A
A
A
A
B
B
B
B
b
b
C
C
C
c
c

I want to remove the repetation from this column so that I can use this vector in my for loop.

[per isakson]

"remove the repetation" see help unique()

[Sana Ejaz]

Thanks alot.It worked and got rid of repeated tickers.

Answer 1

[tmarske]

You are overwriting the the data stored in OMGFA with each loop iteration.

On the first iteration you extract all rows where the ticker is 'OMFGA' and assign these to the variable OMFGA. On the second iteration you extract all rows where the ticker is 'MBNC' and assign these again to OMFGA, overwriting the data you assigned in the previous iteration.

[Sana Ejaz]

Hi,

Thanks for your comment.Yeah I know the reason why my loop is not working.I dont know the solution for this.I know my loop is overwritting but I dont know how to fix it.Thats what my question is.

[tmarske]

Bluntly, I think that what you are trying to do here is a bad idea and you need to stop and rethink.

The simplest way to create one table for each ticker would be to store each table in a cell array, as follows:

idx={'OMFGA','MBNC'};
%preallocate the cell array
tkrTables = cell(1, numel(idx))
for i=1:numel(idx) 
    B=strcmp(compTik,idx(i)); %trying to compare company tickers
    tkrTables{i} = data (B,:); % storing stock data related to OMFGA 
end

However this comes with problems - when you want to access these tables you'll need to remember that the 'OMFGA' table is stored in cell 1 and the 'MBNC' table is stored in cell 2. This will get a lot worse when dealing with more tickers - for example think on what will happen if you had 500 tickers total and need to find the table for 'MSFT'.

A better way imo would be to store the index for each ticker, then when you want to retrieve the data for that ticker simply retrieve it from your data table using that index. I'd suggest using a containers.Map() to store these indices (look up the help file on these if you're unfamiliar with maps)

idx={'OMFGA','MBNC'};
%setup the containers.Map()
tickerIdx = containers.Map();
for i=1:numel(idx) 
    B=strcmp(compTik,idx(i)); %Get the logical index for this ticker
    tickerIdx(idx{i}) = B; %Store the index in tickerIdx
end

then when you want to retrieve the data for 'OMFGA', you simply do

data(tickerIdx('OMFGA'), :) %retrieves data for OMFGA
data(tickerIdx('MNBC'), :) %retrieves data for MNBC

Lookups using a logical index in Matlab is extremely fast - there is no need to create a separate table for each ticker.

[Sana Ejaz]

Thanks alot.

That was really helpful.

Answer 2

[per isakson]

Try something like this

%%
idx = {'OMFGA','MBNC'};
OMFGA = cell( 1, numel(idx) );
for ii=1:numel(idx)
    B = strcmp(compTik,idx(ii)); %trying to compare company tickers
    OMFGA{ii} = data(B,:); % storing stock data related to OMFGA
end

[Sana Ejaz]

I did something like this

C = unique(compTik); %removing repeated tickers from compTik
%%
idx=(C)';
tables  = cell(numel(idx),1); % A cell array to store the tables
for i=1:numel(idx) % how to save different data? 
    B=strcmp(compTik,idx(i));%comparing tickers to obtain a logical array
    companydata = data(B,:);
    tables{i} = companydata;
end

I have stored the company data in array named 'tables.' Now I want to extract that data from tables and store it in separate variables.one variable for each company.like data of OMFGA should be stored in separate table named OMFGA and data of MBNC should be another separate table named MNBC.currently I have got 12 tables one table in each row of cell array named tables.I want to extract all these 12 tables and save them separately.