Stock data arrange in 5 min interval.........

[ianccy]

I have a workbook that has stock data gathered from some site its in seconds interval, i want it to be arranged in 5 min intervals, the time format from the data is in hmmss00 as from the file downloaded. From the file,the data is arranged as Date, Name of the stock, ending date(not needed), Time, Price, Volume. how to arrange it so that my data starts from 08:45 ( data gather from 8:45 to 8:49), 08:50, 08:55,.....etc, and the data should be created as in Date(mm/dd), Time(hh:mm), Volume, Open price, High price, low price, close price. (High price of 08:45 is the highest price from 08:45:00 to 08:49:99), etc. if you can, please email me the excel file
This message was edited by ianccy on 2002-08-27 03:09

 

[ianccy]

the time fromat is not in time, when i use your formula, eg, 08451000 still 08451000,

I think it's not in time format

 

[ianccy]

the cell format is not in Time, so i get 08451000, how to convert it to time format?

 

[PaddyD]

Looks like you've got text values that just look like times.

Best optin is to try & fix this as the data is coming in...

If not, you can try & coerce the text entries into times. A standard way of doing this is to use the text() funciton. Say I have 08451000 entered as text in A5

=TEXT(A5,"HH:MM:SS.00")

would be usually be the way to go, or

=TEXT(A5,"HH:MM:SS.00")+0

to get it as a proper time value. But, I haven't been able to get this approach to work (not enough coffee no doubt).

So here's a slightly longer way instead that used the time() function. Again, with 08451000 in A5 you can use:

=TIME(LEFT(A5,2),MID(A5,3,2),MID(A5,5,2))

to get a proper time value. You can also embed it in a ceiling() function to do the rounding up:

=CEILING(TIME(LEFT(A5,2),MID(A5,3,2),MID(A5,5,2)),1/(24*12))

Hope that helps.

Paddy

 

[ianccy]

there is still some problem, soory for the mistake, the data should be 8451000 not 08451000, if i were to change your formula to =CEILING(TIME(LEFT(A5,1),MID(A5,2,2),MID(A5,4,2)),1/(24*12)), it's not right for time 12101000, hoew to add a 0 infront of 8451000

 

[PaddyD]

one method:

1) create another column
2) enter the formula & copy down.

=IF(LEN(A5)<8,"0"&A5,A5)

...assuming you haven't got the same problem with times of the form 08081000 turning up as 881000

Paddy

 

[ianccy]

there is still a little problem with the {=MAX(IF($I$2:$I$8000=M2,$J$2:$J$8000))}, i assumed the data i pasted(from column A to F) would not exceed 8000rows, the MAX formula show #value!, how too fix this?

FITX-TW_min.xls
	A	B	C	D	E	F	G	H	I	J
6648	20020823	FITX	200209	13395800	4935	8		13395800	13:40	4935
6649	20020823	FITX	200209	13400000	4935	4		13400000	13:40	4935
6650	20020823	FITX	200209	13450800	4935	272		13450800	13:50	4935
6651	20020823	FITX	200209	13450900	4935	445		13450900	13:50	4935
6652	20020823	FITX	200209	13451000	4935	1		13451000	13:50	4935
6653								0	#VALUE!	0
6654								0	#VALUE!	0
6655								0	#VALUE!	0
6656								0	#VALUE!	0
6657								0	#VALUE!	0
6658								0	#VALUE!	0
6659								0	#VALUE!	0
6660								0	#VALUE!	0
0850

 

[PaddyD]

one way - stick it in an if that does the check for a value in the appropriate cell:

=if(len(H6648),CEILING(TIME(LEFT(H6648,2),MID(H6648,3,2),MID(H6648,5,2)),1/(24*12)),"")


replace the "" with sometihng else if needed.

Paddy

 

[ianccy]

where to paste this formula?

 

[ianccy]

i got it, Thanks Paddy, yu are a genius

 

[PaddyD]

glad to help...