Jun 12 2021 03:47 AM
I'm doing an event study on stock returns. I have created a dataset with stock returns, but because they trade on different exchanges the dates don't always match. Is it possible to align the dates that do match together with the data in the column on the left of the dates? So that each row shows the same date (with corresponding stock price) for each company?
Jun 12 2021 04:33 AM
I do not have add-in to play with actual data, thus on such model
DD and Value are arrays returned by formula. Result on the right could be generated by
=LET(d, D6:D22, stockdates, E6#, stockvalues, F6#,
k, SEQUENCE(ROWS(d)),
values, XLOOKUP(INDEX(d,k), INDEX(stockdates, k), stockvalues,,-1),
IF({1,0}, d, values))
Instead of ranges for stockdates and stockvalues you may use your formula. And, in general, there is no need to output dates for each stockvalue.
Jun 12 2021 04:51 AM
If you don't have Microsoft 365:
It is currently impossible to insert rows since each column contains a single array formula. Therefore, the following macro replaces the formulas with their values, then inserts row.
Sub MatchDates()
Dim r As Long
Dim m As Long
Dim c As Long
Dim n As Long
Application.ScreenUpdating = False
m = Cells(Rows.Count, 1).End(xlUp).Row
n = Cells(1, Columns.Count).End(xlToLeft).Column
With Cells(1, 1).Resize(m, n)
.Value = .Value
End With
For c = 2 To n - 1 Step 2
For r = 2 To m
If Cells(r, c).Value > Cells(r, 1).Value Then
Cells(r, c).Resize(1, 2).Insert Shift:=xlShiftDown
End If
Next r
Next c
Application.ScreenUpdating = True
End Sub