loop based on a condition in another column

I'm working with a 694672x4 table containing the PermNo., date, CUSIP and stock price of a multitude of shares. The data is sorted such that the full time series of a stock is listed below the full time series of the following stock and so forth. (Pls see photo below). I am trying to calculate the daily returns of each stock individually, however I struggle with the way the data is structured. As you can see, column 3 contains the CUSIP which is an individual identifier to every stock in the sample. I am now trying to tell MatLab to calculate the daily returns (using (diff(log(X))))for all rows with the same identifier and start from scratch once the identifier changes. Unfortunately, I cannot provide any initial code as I don't know where to start...

Thank you in advance!

[Walter Roberson]

https://www.mathworks.com/help/matlab/ref/splitapply.html

[Simon Jurgies]

Thank you for your response Walter. Unfortunately, I still cannot make it work..

I created a 506x4 table named 'test' with only two stocks underneath each other for the year 2020 and used the below inputs.

func = (diff(log(test.PRC)))
splitapply(func,test,test.CUSIP)

However the output I recieve is the following error code: 'Error using splitapply (line 55), First input must be a function handle.'

Could you help me out once again.

Thank you!

[Jan]

func = @(x) diff(log(x))

[Walter Roberson]

Probably you will need

func = @(x) {diff(log(x))}

That is because you will be processing vectors, and diff() of a vector is a vector (one shorter), but splitapply() requires that you return a scalar.

Answer 1

[Seth Furman]

To add to Walter's answer, you might also consider groupsummary.

https://www.mathworks.com/help/matlab/ref/double.groupsummary.html

>> rng default
>> t = table([1;1;1;2;2;2;2],rand(7,1),'VariableNames',{'Group','Data'})

t =

  7×2 table

    Group     Data  
    _____    _______

      1      0.81472
      1      0.90579
      1      0.12699
      2      0.91338
      2      0.63236
      2      0.09754
      2       0.2785

>> groupsummary(t,'Group',@(x){diff(log(x))})

ans =

  2×3 table

    Group    GroupCount     fun1_Data  
    _____    __________    ____________

      1          3         {2×1 double}
      2          4         {3×1 double}