How to match intraday data on excel?

[stow254]

Anybody know how to match intraday data based on date?. I need to arrange the data so that the prices of different stocks are alligned row by row according to time (trading date and minute). At the moment, the open and close prices for the stocks aren't aligned as some of them are missing minutes.

 

[Rhody Trader]

Your best option is probably going to be to write a macro to handle the process.

 

[kipptrader]

Anybody know how to match intraday data based on date?. I need to arrange the data so that the prices of different stocks are alligned row by row according to time (trading date and minute). At the moment, the open and close prices for the stocks aren't aligned as some of them are missing minutes.

Your first problem is you are assuming that every stock has a duplicate date/time stamp for alignment. This is NOT TRUE.

Intraday data will only have a date/time stamp is a transaction occurs during the time frame of the data. If you have minute data, stock ABC might have trades at:

05/07/2010,1200,...
05/07/2010,1201,...
05/07/2010,1202,...
05/07/2010,1203,...

but a a stock with thin volume might only have trades at:

05/07/2010,1200,...
05/07/2010,1202,...
05/07/2010,1205,...

So the first thing is you will need to add blank date/time stamps where the lack of trades occur.

It gets a bit more complex with tick data, but you didn't say what kind of intraday data you are working with.

 

[stow254]

Thanks for the responses.

Your first problem is you are assuming that every stock has a duplicate date/time stamp for alignment. This is NOT TRUE.

Intraday data will only have a date/time stamp is a transaction occurs during the time frame of the data. If you have minute data, stock ABC might have trades at:

05/07/2010,1200,...
05/07/2010,1201,...
05/07/2010,1202,...
05/07/2010,1203,...

but a a stock with thin volume might only have trades at:

05/07/2010,1200,...
05/07/2010,1202,...
05/07/2010,1205,...

So the first thing is you will need to add blank date/time stamps where the lack of trades occur.

It gets a bit more complex with tick data, but you didn't say what kind of intraday data you are working with.

Yes, that's exatly right! I need to somehow add blank time stamps. Any idea on how to do this?

The data im using is OHLC with minute intervals. Here's where i got it from: http://www.trade2win.com/boards/day...tf-us-markets-historical-data-download-2.html

 

[kipptrader]

Thanks for the responses.
Yes, that's exatly right! I need to somehow add blank time stamps. Any idea on how to do this?

The data im using is OHLC with minute intervals. Here's where i got it from: http://www.trade2win.com/boards/day...tf-us-markets-historical-data-download-2.html

I would use a script to parse the text file and insert blank lines. Simple enough task, but what fields will you use for the blank lines?

If you have:

05/07/2010,1200,Open,High,Low,Close,Volume
05/07/2010,1202,Open,High,Low,Close,Volume

will you insert blank strings ("") like below?

05/07/2010,1201,,,,,