I have two time series data with dates. For an academic purpose (beta calculation) I need same matching dates for both time series data sets in the same row side by side. Please note that the dates are recorded as dd/mm/yyyy.
I have copied a portion of two data series in the following. As you can see, I have 1st, 2nd, 5th, 6th of January data for ABBANK but I don't have data for those dates under the Index data, so I need to have these ones removed from ABBANK Data Series. Then again, I have 7th January data for Index data series but data for that date is missing in ABBANK data, so I need that one removed from Index data series.
Index Data Series ABBANK Data Series
Ticker Date Close Ticker Date Close
00DSEGEN 03/01/2007 1583.09 ABBANK 01/01/2007 90.07
00DSEGEN 04/01/2007 1589.41 ABBANK 02/01/2007 88.3
00DSEGEN 07/01/2007 1574.97 ABBANK 03/01/2007 87.75
00DSEGEN 08/01/2007 1579.21 ABBANK 04/01/2007 87.15
00DSEGEN 09/01/2007 1588.08 ABBANK 05/01/2007 89.55
00DSEGEN 10/01/2007 1587.61 ABBANK 06/01/2007 89.9
00DSEGEN 11/01/2007 1581.99 ABBANK 08/01/2007 89.03
00DSEGEN 14/01/2007 1656.93 ABBANK 09/01/2007 91.97
As such, following is the desired modification in both data sets.
Index Data Series ABBANK Data Series
Ticker Date Close Ticker Date Close
00DSEGEN 03/01/2007 1583.09 ABBANK 03/01/2007 87.75
00DSEGEN 04/01/2007 1589.41 ABBANK 04/01/2007 87.15
00DSEGEN 08/01/2007 1579.21 ABBANK 08/01/2007 89.03
00DSEGEN 09/01/2007 1588.08 ABBANK 09/01/2007 91.97
00DSEGEN 10/01/2007 1587.61 ABBANK 10/01/2007 87.93
00DSEGEN 11/01/2007 1581.99 ABBANK 11/01/2007 91.1
00DSEGEN 14/01/2007 1656.93 ABBANK 14/01/2007 89.6
Can anyone please show me how to do it automatically using excel?
Thanks a lot in advance.
Shahzada Imran
P.S.: I searched for a similar problem with solution in this forum but could not find one. Please refer to such posts if exist.
Bookmarks