Different Dates

[ttony123]

Hi everyone,

I need a helping hand with cleaning my time-series data as I have to collect a couple of variables on a daily basis between 2005-2010. However, some of the variables don't have matching dates, e.g the London Stock Exchange doesn't record the closing value of the FTSE100 index on weekends/no-trading days whereas pollution stations around the city measure levels of pollution every day. Is there any way I can use the dates of the FTSE100 as the only date axis/column and delete the dates from column A that don't match with the ftse100 dates? I could go manually but then I need to do this for another 8 years of daily observations and will be too complicated/time consuming to do it manually and I need this data clean for my final year econ dissertation. Thanks in advance.

[dosydos]

Create a new column A.
In A2 paste this formula and then drag down


=IF(ISNA(VLOOKUP(B2,$G:$G,1,0)),VLOOKUP(B2,$G:$G,1,1),VLOOKUP(B2,$G:$G,1,0))


Is this what you are looking for?

[ChemistB]

So you only want to use the dates in Column F? or only dates that are both in A and F (some dates in F are not in A).?

If the first option, it might be easiest, if in Col G, G2 copied down and right
=IFERROR(ROUND(VLOOKUP($F2,$A$1:$D$1790, COLUMNS($H$1:I$1),FALSE),1),"")
Then you can place them all into a single chart, each column being a different series and using a secondary vertical axis. See attached.
Is that what you are looking for?

[ttony123]

Hi,

I copied the formula and I have been given some random numbers which don't look right. Might be me doing it wrong. In case you didn't quite understand what I am after: In column A I have date in the form off dd/mm/yy and for each day I have a value assigned for all the variables such as windspeed, pm10 etc. This basically summarizes all the values recorded for the variables on a specific date on this row. However, if you look into the FTSE100 dates, they differ because the stock exchange doesn't trade on certain dates like Christmas so there will be missing dates in column E when you compare it to column A. I need to keep the values recorded for each of the variables in accordance to the dates from the FTSE100 as I will be regressing the FTSE100 index against the variables so some of the dates in column A are useless and I don't need them. E.g I don't need to know what the windspeed was on 01/01/2006 because the FTSE100 has no recorded closing value for this date. I am trying to sort my variables (closing value of the FTSE100 index, PM10,humidity, windspeed) on a daily basis. Ideally, my 1st date in cell A2 will be 04/01/2005 so I can go and delete the values recorded for windspeed,humidity and PM10 for the 1st,2nd and 3rd of January, but unfortunately the data set is quite big so manually deleting individual dates from column A + the recorded values for the independent variables(humidity,windspeed and PM10) will take a lot of time. Hopefully that helps.

[dosydos]

Im guessing it is showing numbers like 38356 38400 ...etc. This is the actual number value Excel gives a date. Just change the cell formatting of column A to date.
My formula was looking up the date u have for wind speed etc in your FTSE100 date. If it found the date then it returned itself. If it did not find the date the it does another Vlookup which will return an approximate match.
So for example take christmas of 2005. The last day FTSE100 has for chrismas is 12/23/2005. The next date FTSE100 has is 12/28/2005. So for dates in Column B 12/23/2005 - 12/27/2005 it will return the date 12/23/2005.

[ttony123]

Okay, so far so good. I changed it to date and I can see the repeating dates, which in the case mean that FTSE100 has no recordings for those. Does that mean I can just delete the repeating dates and then copy and paste the values for each of the variables? Thanks a lot man btw.

[dosydos]

Wait, did you just want to delete all rows of your date where there was not a corresponding FTSE1000 date??

[ttony123]

Yes, pretty much. The observations of the variables on dates that don't correspond to the FTSE100 dates are not needed at all. Useless statistics.

[dosydos]

then just use this formula instead.

=VLOOKUP(B2,$G:$G,1,0)

then coopy paste special values.
Then Delete all Rows that are #N/A

You could even throw a filter on and search for #N/A and then delete to make it even quicker.

[ttony123]

I have the dates now but no observations.