How to Merge 500 tables with occsaional different dates in their date column

Dear Expert Users, I'd like to merge my 500 tables, each table is a stock's daily ~6 to 12-yr history. Each table has 7-columns with the first column as yyyyMMdd, 4 columns in $ for hi/low/close/open prices, 1 column for amt of volume traded on that day, and last column is just one I added that is the "ticker" stringvar for that stock (duplicated on every line for eveery day of historical date).

I looked at using merge but each table is a different length. I tried to use join but some dates are missing. I tried to use Financial Tool, however, I have a single file with 500-tables so it is not reading varibles in app.

Do I have any options?

Thank you. --AR

Answer 1

[Peter Perkins]

Is outerjoin what you are looking for?

>> t1 = table(datetime(2016,1,[1;2;3;5]),[1;2;3;4],[5;6;7;8]);
>> t2 = table(datetime(2016,1,[2;4;5;6]),[9;10;11;12],[13;14;15;16]);
>> outerjoin(t1,t2,'Keys','Var1','MergeKeys',true)
ans = 
       Var1        Var2_t1    Var3_t1    Var2_t2    Var3_t2
    ___________    _______    _______    _______    _______
    01-Jan-2016      1          5        NaN        NaN    
    02-Jan-2016      2          6          9         13    
    03-Jan-2016      3          7        NaN        NaN    
    04-Jan-2016    NaN        NaN         10         14    
    05-Jan-2016      4          8         11         15    
    06-Jan-2016    NaN        NaN         12         16

If you have access to R2016b, you should look at using timetables.

[AR]

Pete, Thanks. This worked w/adding 'Type' 'left' to outerjoin and looping across all 500 tables. Within loop I horzcat my table-X with my table-X Variable of interest (e.g. Opening Prices) to create one large matrix with Opening Price as columns and every day as row to create a 6429 (# of total days) x 500 (# of stocks) matrix. Once matrix created some stocks had NaN for that day. My code.

X=allDates; % before loop create vector of all date ranges across all 500 tables

i=1:length(files)

XTable=horzcat(XTabletimes(:,1), XTableVar(:,2)); % column 1 in table is datenum value and column 2 is Opening Price  

[A,~,~] = outerjoin(X,XTable,'key','Date','Type','left','MergeKeys',true); %first joins XTable to allDates then subsequently joins new XTable (XTable2) to (allDates + XTable1) then continues through all 500 tables (e.g. files)

    X=A; % creates new X (allDates + XTable1) for next iteration to join

end