How to create extra data based on a string cell value?

hi,

How do I extract the data from a cell based on a string value? I'm trying to create a new array with all the data for each unique 'symbol' sorted by TIME -oldest to newest.

thanks Allan

The new arrays would be:

new_VWAPdata.symbol (for ACB)

Symbol Most_Recent_Trade_Time VWAP

ACB 14:22:11.863324 10.2616

ACB 14:21:11.999557 10.2616

etc

new_VWAPdata.symbol (for ACB)

Symbol Most_Recent_Trade_Time VWAP

AAPL 14:24 123.8655

AAPL 14:25 123.88

etc.

Answer 1

[Harry Laing]

I'm going to assume that the actual date (day) of the time column doesn't matter, or happens to be todays date. Because MATLAB's datetime function/format can be used here, but you need to specify a day too.

I'd write a mini-function to convert the time value you have to a datetime variable with todays date and that time, then you can write a for loop to filter through your 'Symbol' column and place the data in an associated array, then sort by time afterwards.

% Create Function that takes your recent trade time value and convert it to date+time format
function most_recent_trade_time_DATETIME = Time_to_Datetime(most_recent_trade_time)

        % your time will now be in the form: 07-Dec-2020 14:22:11.863324
        datetime_string = [date,' ',most_recent_trade_time]; % 'date' gets todays date
      
        % datetime_string is still a string, must convert to 'datetime' format
        most_recent_trade_time_DATETIME = datetime(datetime_string);
end

Now that you have a function to convert from a string to a datetime format, you can write a loop. There's many ways to do this, and what I suggest below probably isn't the most efficient, but as an example:

% Initialise new 'struct' of data, each having its own table within the structure for each symbol
SortedData.ACP = table();
SortedData.AAPL = table();

for i = 1:length(new_VWAPdata.Symbol) % loop for number of rows in new_VWAPdata
    
    % convert time value to datetime using our function above
    most_recent_trade_time_DATETIME = Time_to_Datetime(new_VWAPdata.Most_Recent_Trade_Time(i)) 
    
    % Start an if statement based on Symbol
     if new_VWAPdata.Symbol(i) == 'ACP'
         SortedData.ACP = [SortedData.ACP; % Update the table for ACP by adding the time and VWAP data
               {most_recent_trade_time_DATETIME, new_VWAPdata.VWAP(i)}];
             
     elseif new_VWAPdata.Symbol(i) == 'AAPL'
         SortedData.AAPL = [SortedData.AAPL; % Update the table for AAPL by adding the time and VWAP data
               {most_recent_trade_time_DATETIME, new_VWAPdata.VWAP(i)}];         
         
     else
         error('Unknown Symbol')
     end

end

SortedData.ACP  = sortrows(SortedData.ACP, 1) % sort the rows based on the first column, in ascending order
SortedData.AAPL = sortrows(SortedData.AAPL,1) % sort the rows based on the first column, in ascending order

The resulting structure should have two tables, that look something like the below image. Obviously you can rename the columns in the tables.

NOTE: The function should be at the bottom/end of your script, or in it's own file in your current MATLAB directory.

Answer 2

[dpb]

Read the data into a table instead is probably quite a lot easier.

Then use grouping variables to operate over data by whatever you choose.