Excel help Required.

I want to compare some US stocks data with similar UK stocks data. I can get the EOD historical data in the form of csv then import it into Excel. Trouble is the raw data consists of different dates, due to various bank holidays etc on both sides of the pond. So my question is this:-

How do I "clean up" the data so that I only have data of the same dates, thus allowing a more accurate EOD comparison? I could do it manually, but I am sure one of you bright guys out there, will know the easy way to do it.


regards,

dbt

there is no simple excel way i can think of right now - guess my bright light must be out - but the easiest and best way is to write a visual basic for applications program VBA and tell it to do exactly what u want it to do - if u cant do that - find a friend who is a programmer - the programmer can then build u all sorts of filters etc - i can see the path you want to go down - but it is one badass route and far more complex that it seems at first due to currrency differentials, length of trading days, interest rates, sector rotation and a whole bunch of other stuff

and would the result be all that different anyway.

Xmas, Thanksgiving,Presidents Day, Easter,May day....uuuurrghh.

Bonsai - I've just noticed your locale - have you had a garage built yet or do you still park on the street?
Ron

Morning guys,

I know there are functions such as vlookup, match etc, but I just can't get them to work for this problem.

"Does it matter" Well if you are trying to compare EOD data over say the last 3 years. You are out of sync within the 1st month and by the end of the 3yr period you can be as much as 5 days out. Not a very accurate comparison.

Is there a way of importing the data into something like Approach and then filtering it that way?

I'm just not up to speed with these packages.

dbt

just thought I would try to save you a lot of work.

5 days in 3 years.
doesnt sound very much in statistical terms

if you eliminate the odd days, you may get some very interesting
spikes or gap ups on a comparative chart whic didnt actually happen ?

but if you must then convert the dates back to numerics
and write a macro that simply rolls down each table comparing dates and print what you need when you get a match ?

but which ones are you going to eliminate ?
Uk or US ?

One of them will have to be your master table.

Delboy,

Do you have to use Excel ? There are a number of other packages that would be much better for doing this comparison.


Paul

Paul,

Which packages would be able to do it?

dbt