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]

there is still problem, i am trying to find out

 

[ianccy]

This message was edited by ianccy on 2002-08-27 03:12

 

[ianccy]

what is wrong, what does #N/A mean?

 

[ianccy]

and some data is 0, what is wrong?

 

[PaddyD]

</table>

 

[PaddyD]

Ian,

FYI for future reference -

1) the html maker uses a lot of code dealing with spreadsheet formatting.
2) There is a limit to the amount of text / code you can have in a single post.
3) The effect of having too much formatting can be that not all of the html code stays in the post.
4) If which case, you end up missing off some of the end of te html, including the tags that end the tables etc.
5) So, best to post spreadsheets with no / little formatting!



As far as NA is concerned - it means that the lookup could not find the lookup value in the range specified. Depending on what you've got, this may or may not be OK.


Also, as I said in an earlier reply, the methods I used (vlookup & array entered max) ignored your date / time info ('cos your set up allowed me to). You are now using the time values - e.g. as lookup values in formulas like:

=VLOOKUP(M7,$I$2:$J$8000,2,FALSE)

This is not what was originally intended & so the suggested approach may not be appropriate.

How about indicating what your 'live' data is like...(without the formatting!)

Paddy
This message was edited by PaddyD on 2002-08-25 22:13

 

[ianccy]

As you can see from the worksheet, i paste the data fro column A to F, the relevant data to add up the 5 min data is the time, is it possible to convert the time to other format and then vlookup it?

 

[PaddyD]

Could you answer some questions:

1) Are you happy with the rounded time values you are getting in col I
2) Are the values in col J your bid items? If not, where are they?
3) Will your data always be sorted in the following fashion:

first by bid item (col J), then by descending time (col I).

(i.e. so that all the entries for a particular bid item are next to each other in the data with the earliest first & the latest last). If not, what is the order - random? Sorted by bid but not time?

Paddy

 

[ianccy]

The bid price is not sorted but in random order, only the time is sorted in ascending order.

And the time column may have different bid price for a same time.

Thank you for your efford
This message was edited by ianccy on 2002-08-26 18:42

 

[PaddyD]

Ian, those were answers to some questions, but not the ones I asked!!

Paddy