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]

1. the round up time is what i wanted, but it couldnot be used as the Vlookup function. i think i would go for a different approach, like maintaining the 8 digit time format as in column H, but make s it round up to nearest 5 min 8 digits, is this possible? then i could use the vlookup for it?

2. Column J is the bid price or stock price.
3. Only the time is sorted and the price are in random order. The time has to be sorted so i can extract the start price and the last price

 

[PaddyD]

Ian,

see if this works for you:

Problem Assumptions
Bid item names are in column B
Bid times are in column C
Corresponding Bid values are in column D
The items are not sorted in any particular order.
Problem
Given the above, find the start, end max & min prices for a particular bid item.

see the simulation below:

Book4
	B	C	D	E	F	G	H	I	J
1	BidItem	BidTime	BidPrice		Item	Start	Low	High	End
2	FIXT	08:50	21		FIXT	21	5	41	5
3	FIXT	08:55	10		FIXB	8	8	56	28
4	FIXT	09:00	41
5	FIXT	09:05	31
6	FIXT	09:10	26
7	FIXT	09:15	5
8	FIXB	09:20	8
9	FIXB	09:25	35
10	FIXB	09:30	18
11	FIXB	09:35	20
12	FIXB	09:40	56
13	FIXB	09:45	18
14	FIXB	09:50	38
15	FIXB	09:55	46
16	FIXB	10:00	31
17	FIXB	10:05	28
18
Sheet1

This message was edited by PaddyD on 2002-08-26 20:23

 

[PaddyD]

explanation:

1) Start times:

Need to use all three columns:

=INDEX($D$2:$D$17,MATCH(F2,IF($C$2:$C$17=MIN(IF($B$2:$B$17=F2,$C$2:$C$17)),$B$2:$B$17)),0)

First, use a min to get the min time for a particular bid for an item:
MIN(IF($B$2:$B$17=F2,$C$2:$C$17))
then plug the value into a standard index / match combination.

2) Min bid

only 2 columns here:

=MIN(IF($B$2:$B$17=F2,$D$2:$D$17))

again, a fairly standard array entered min to find the minumin value of one set of numbers that equal a certain condition.


3) max bid:

as above, but with a max:

=MAX(IF($B$2:$B$17=F2,$D$2:$D$17))

4) End price:

=INDEX($D$2:$D$17,MATCH(F2,IF($C$2:$C$17=MAX(IF($B$2:$B$17=F2,$C$2:$C$17)),$B$2:$B$17)),0)

again, just like the first formula but with min swapped for max to get the end time.

Post back if you need.

Paddy



EDIT:

Note:
1) All the formulas must be array entered.
2) There is no longer any need to sort the data.
This message was edited by PaddyD on 2002-08-26 20:28

 

[ianccy]

Sorry for not making it clear the first time, I want a worksheet to deal with one bid item separately, and each bid item has time seconds intervals from 08:45 to 13:45, and i want to rearrange it into 5 min intervals, and each 5 min intervals there is open, high, low, and close price.

 

[ianccy]

previously i thought with any format in the item column or time column, i can use the Vlookup function, but it's not possible for time format, i just thought that i could copy the formula yu provided for the item format to the time format, sorry !

 

[PaddyD]

It is a shame it took you four pages of posts to get round to asking your question. (Or that it took me 4 pages to work out what it was

Anyway, I see what you mean re the formatting:

Book4
	B	C	D	E	F	G	H	I	J
1	BidTextTime	BidTime	BidPrice		Time	Start	Low	High	End
2	08425000	08:45	18		08:45	18	18	38	38
3	08441000	08:45	38		08:50	#N/A	10	56	56
4	08451500	08:50	21		08:55	26	5	41	18
5	08451600	08:50	10		09:00	20	20	20	20
6	08452000	08:50	31
7	08453000	08:50	46
8	08481000	08:50	56
9	08513100	08:55	26
10	08520000	08:55	5
11	08531000	08:55	8
12	08531500	08:55	41
13	08542500	08:55	35
14	08550000	08:55	18
15	08560000	09:00	20
16
Sheet1

It can be tricky as G3 indicates. However, it should be fixable (there are no other errors, after all). If you can't fix it, you could consider changing the time values to text as a way round any precision issues...

Paddy

One suggest
This message was edited by PaddyD on 2002-08-26 21:13

 

[ianccy]

I learn a lot from you from this 4 pages

 

[ianccy]

How to convert the time format in to Text?

 

[ianccy]

Finally we can put an end to this, Thank you so much Paddy!