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 file has more than 1 stock name , just choose any one, eg, FITX, Thanks for the help!

 

[PaddyD]

...if you can, please email me the excel file...

so what are you after here? someone to build you the excel file? Or sort out the files on the dowmnload site you listed - they're not small files & could take someone an age to pull down if they've got a slow connection...

I assume you've got at least one of the files yourself. You must, therefore, have a specific question - how about asking it!!!

As a guess at what you want, see the following, which will round up time values to the nearset 5 minutes:

Book3
	A	B	C	D
1	Source	RoundUptoFiveMinutes
2	26/08/200209:48	26/08/200209:50
3	26/08/200209:50	26/08/200209:55
4	26/08/200209:51	26/08/200209:55
5	26/08/200209:53	26/08/200209:55
6	26/08/200209:54	26/08/200209:55
7	26/08/200209:56	26/08/200210:00
8	26/08/200209:57	26/08/200210:00
9
10
Sheet1

Does this help?

Paddy

 

[ianccy]

Book3.xls
	A	B	C	D	E	F	G	H	I
2		BidItem	price		Item	Startbidprice	Highestbid	Lowestbid	LastBidPrice
3	2002/8/1	A	20		A	20	79	16	79
4	2002/8/2	A	53		B
5	2002/8/3	A	40		C
6	2002/8/4	A	16		D
7	2002/8/5	A	79
8	2002/8/6	B	20
9	2002/8/7	B	46
10	2002/8/8	B	53
11	2002/8/9	B	71
12	2002/8/10	C	43
13	2002/8/11	C	62
14	2002/8/12	D	10
15	2002/8/13	D	16
16	2002/8/14	D	22
17	2002/8/15	D	23
18	2002/8/16	D	29
19	2002/8/17	D	48
20	2002/8/18	D	11
21	2002/8/19	D	30
Sheet1

 

[ianccy]

How is the formula to get data as in blue?

 

[PaddyD]

see the attached for one method.

Note - this method ignores the date column - as the dates are all in order, and the bids for specific items are contiguous, this regularity can be exploited without explicitly referencing the dates themselves (for example, the vlookup can be used to retunr the first match, which is the same as the ealiest bid given how your data is set up).

There are numerous other ways of doing this - dfunctions, index / match combinations etc. Post back if you need more.

Paddy

Book3
	A	B	C	D	E	F	G	H	I
1		BidItem	Price		Item	Start	High	Low	Last
2	1/08/2002	A	20		A	20	79	16	79
3	2/08/2002	A	53		B	20	71	20	71
4	3/08/2002	A	40		C	43	62	43	62
5	4/08/2002	A	16		D	10	48	10	30
6	5/08/2002	A	79
7	6/08/2002	B	20
8	7/08/2002	B	46
9	8/08/2002	B	53
10	9/08/2002	B	71
11	10/08/2002	C	43
12	11/08/2002	C	62
13	12/08/2002	D	10
14	13/08/2002	D	16
15	14/08/2002	D	22
16	15/08/2002	D	23
17	16/08/2002	D	29
18	17/08/2002	D	48
19	18/08/2002	D	11
20	19/08/2002	D	30
21
22
Sheet1

edit: the min & max bits are array formulas - enter them with control + shift + enter, not just enter - excel will add the curly brackets if it's done OK
This message was edited by PaddyD on 2002-08-25 18:12

 

[ianccy]

Thanks, and what is the formula yu gave in the previous reply, the formula? i can't copy the formula from it

 

[PaddyD]

something seems to have gone wrong there. The formulas used the ceiling function to round a time up to the next nearest 5 minutes:

=CEILING(A1,1/(24*12))

where the 1/(24*12) bit is to determin the decimal fraction for 5 minute intervals - 24 hours in the day, 12 5 miutes in an hour.

Paddy

 

[ianccy]

the time format in the file is in hhmmss00, how to convert it to hh:mm:ss?

 

[PaddyD]

By changing the format??

If you've got fractions of a second, this shouldn't effect the ceiling calculations or their equivalents...what are you trying to do?

Paddy