How to add column values of different tables based on dates column

Hello, I have a table of daily stock prices by date:

Date:            Price
01/01/2010       50.6
02/01/2010       51.2
...              ...

and another table with dividends by their payment date:

Date:            Dividend:
02/01/2010       2.5
02/05/2010       1.6
...              ...

I want to adjust the prices with the paid dividends by checking whether a dividend was paid for every Date in table1 and where the Date in table1 and table2 match, I want to overwrite the price in table1 to equals = Price.table1 + Dividen.table2 for that specific date.

So the end result should be:

Date:           Price:
01/01/2010      53.1
02/01/2010      51.2
...             ...

I know this can be done with a for and if loop, I just can't get my head around it.

Please advise

Answer 1

[Star Strider]

If you have R2016b or later, either the retime (link) or synchronize (link) function will probably work.

[Sara Dabour]

Hey, thanks for the suggestion, I saw that retime() has a 'sum' method, but when I try to apply the function I get this error:

>> k = retime(a,b,'sum')
Error using timetable/retime (line 121)
'sum' is not a valid specification of target time vector for synchronization.

Where a is one table and b is the other. They are of different lengths though, I don't know if that matters. I also had the table2timetable() conversion done before running the above function.

[Star Strider]

See if synchronize will do what you want. It also has 'sum' as an Aggregation Method.

[Sara Dabour]

synchronise() gives me the same error. I'm using the R2017b version, if that is useful. Regardless, I found out that retime(a,b,'union') brings me closer to what I want. It's not ideal but it results in a new timetable where it a and b are arranged by date as separate columns. Timetable p:

   Date       Dividend    Price
  '01/01/2010'  NaN  30.1136000000000
  '04/01/2010'  NaN  30.6121000000000
  '05/01/2010'  NaN  30.6236000000000
  '06/01/2010'  NaN  30.1336000000000

From here I can just use the sum() function to get the totals across rows. I've found that I have to convert p into a double for that to work. Now my problem is how to replace the summed values in a, according to the right date.

Almost there :D

[Star Strider]

I appreciate your update.

I can only direct you to what appear to me are appropriate ways to solve your problem, from your description of it and your description of your data.

Answer 2

[Peter Perkins]

I think 'sum', as in "the input to retime or synchronize", is a red herring here. That parameter sums across time. Assuming you've made two timetables, I think what you want is something like

ttDividends = retime(ttDividends,ttProces.Time,'FillWithConstant','Constant',0)
ttPrices.Price = tt.Price + ttDividends.Dividend

But it's probably useful to combine prices and dividends. Assuming the dividend dates are a subset of the price dates, this

ttCombined = synchronize(ttProcess,ttDividends,'first')

will NaN-fill the dates with no dividends. Then

hadDividend = ~isnan(ttCombined.Dividend);
ttCombined.NewVarName(i) = ttCombined.Price(i) + ttCombined.Dividend(i)