Excel help Required.

[delboy trotter]

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

 

[stevet]

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

 

[bonsai]

and would the result be all that different anyway.

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

 

[Rognvald]

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

 

[delboy trotter]

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

 

[bonsai]

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.

 

[Trader333]

Delboy,

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


Paul

 

[delboy trotter]

Paul,

Which packages would be able to do it?

dbt

 

[ColinRiche]

This all depends on how much data you want to compare and how many stocks

If its just a few stocks as an exercise and you have to use excel and you cant write a macro or use VBA then you may as well do it manually

Choose which will be the master table
Compare the dates by using a function so that its easy to see which enteries are out of line
Then delete the extra data with a shift up of rows

But it all depends what it is you are trying to do

 

[delboy trotter]

I knew this would happen. What seems to be an easy task is turning into a nightmare. Lets try again.

I have 2 worksheets with raw data for different stocks. I want to filter the data so that I only have the days when both stocks traded. So how do I eliminate the days which do not match this criteria. I am not worried about spikes or anything like that since I am not trying to create charts. I know how to write macros, but do not know how to write a macro to do this task.

dbt

 

[ColinRiche]

send us the worksheets and i'll have a look

 

[Trader333]

Delboy,

How far back does the data go ?


Paul

 

[stevet]

there are not that many days which do not sync

so, copy the data so columns of each are next to each other in the same sheet and with the first date of each matching up on the the same row

then do a formula such as - if (x = y,"yes", "no") in a cell on the same row as your matched dates, and referencing those same matched dates as "x" and "y"

now just copy that formula right down to the bottom of all your rows

spot where the first "no" appears and then visually just use delete to get your data into line again

you will then need to recopy the formula from a higher row to the bottom again as the cell references will have moved when you did the delete

and there you go - messy etc, but unless u want to learn programming etc - thats what u need to do and it'll be done in a flash - you wont find anything of any use though!- but thats what learning is all about - cul de sacs till you focus on the simple - just "follow the trend" in trading and u cant go wrong

 

[delboy trotter]

Thanks stevet and the rest of you guys,

Ended up doing it with the if (x = y,"yes", "no") that stevet suggested. worked fine, but I would hate to have to do this every time I wanted to compare historical data. If anyone has a better solution then please let me know.

dbt

 

[ColinRiche]

Its still a manual way but here are the dates to delete

 

[stevet]

delboy trotter

easy way is to employ a programmer on 50 - 100K a year! although i guess i can see the downside to that one!

 

[TBS]

Why not just set it up as a vlookup table, then you will only report the data for the dates that you want?

 

[delboy trotter]

"Why not just set it up as a vlookup table, then you will only report the data for the dates that you want?"

Yes TBS that's what I mentioned a the start of the thread, but I could not get my head round how to do that. How do you do it?

regards,

dbt

 

[mutantcar]

DelBoy,

If you haven't solved it yet, have you tried 'Data-PivotTable' with 'Multiple Consolidation ranges'.

If you're not sure, send me some sample data & I'll give it a go.

Cheers,

Mute.