Separating Data into columns based on certain text variable in that data

I have a list of daily stock returns for each stock on the S&P 500. The returns are formatted so that each stock is grouped together like this:

1/1/2013 AAPL .005%

1/2/2013 AAPL -.1%

....

1/1/2013 GOOG .5%

1/2/2013 GOOG .25%

How would I sort this so that the each stock can be a column name and have its returns below it?

Thanks!

[Bob Thompson]

Is the string setup such that there is a space between the letters and the trailing percentage? If so, you could split the string at the two spaces using one of a couple of different commands (I can't think of what exactly they are off the top of my head, something like strsplit() and regexp()), and then use sorting or indexing based on the middle string which has your specific letter code.

Answer 1

[dpb]

>> data=readtable('jack.dat','format','%{MM/dd/yyyy}D %s %f%%')
data = 
     Var1        Var2     Var3 
  __________    ______    _____
  01/01/2013    'AAPL'    0.005
  01/02/2013    'AAPL'     -0.1
  01/01/2013    'GOOG'      0.5
  01/02/2013    'GOOG'     0.25
>> [u,~,ic]=unique(data.Var2);
>> stks=table(data.Var1(ic==1));   % get the dates first; must all be same
>> for i=1:length(u)               % get each individual stock
     stks(:,i+1)=table(data.Var3(ic==i));end
>> stks.Properties.VariableNames=[{'Date'}, u.']  % add useful names
stks = 
     Date       AAPL     GOOG
  __________    _____    ____
  01/01/2013    0.005     0.5
  01/02/2013     -0.1    0.25
>> clear data                      % done with it...

Above does assume that every stock has the identical timestamp; any missing will cause failure.

[Jack Bratton]

What if the timestamps were not the same for all of them? There are some days of missing data.

[dpb]

You'd have to preprocess(*) to augment the missing locations to use a table; they are by definition, rectangular. Alternatively, something of the same sort could be done with structure using name fields each with its own array of dates/times.

It'd depend on just what you're going to do with the data as to which might work better altho I'd expect the first would likely be more convenient once built.

(**)ADDENDUM

That would end up as a two-step process; first separate by Ticker as shown but retrieve the Date field as well as close; then merge the data based on matching dates; a timetable might help; if you have Financial Toolbox (I don't) I think there are some specialized tools there that might be helpful as well. retime certainly is one possibility that does have the facility; what you have to do is ensure you keep the data with the proper timestamp where the missing values are...

[Ulrik Græsvik]

Could you elaborate more on the preprocessing? I'm struggeling with the same issue with the same three columns (date, ticker and return).

All companies/tickers will have different timestamps as we are looking into IPOs and therefore they will start at different times.

[dpb]

Specific code would depend greatly on how you have the data available to start from and whether you know the overall first/last dates a priori or have to determine that from a collection of files as well.

Probably the simplest would be to first create a datetime array of the overall length from first to last and use it to build the date column in the output table. Then, when you read each dataset, convert its date field to datetime and use logical indexing to insert into the proper locations in the table with a missing value indicator elsewhere.

If you would attach a representative input file, I'd at least look at it and see if gives me any further hints -- I'm really busy right now so won't promise I can actually write code at least until weekend...