Deriving close-high-low-open

**db8r** · 08-08-2009, 11:33 PM

Hi,

I'm struggling with the following problem and would really appreciate some help.

I have one sheet with two columns:

date and time
price

I want to find relevant values (open, high, low and close) for every day and place them in another sheet that has five columns:

date
open
high
low
close

Open is the first price of a particular day (as found on sheet one)
High is the highest price of a particular day (as found on sheet one)
Low is the lowest price of a particular day (as found on sheet one)
Close is the last price of a particular day (as found on sheet one)

The finished result could look something like this:

SHEET ONE (datetime and price)

2008-01-01 00:25 200
2008-01-01 13:25 250
2008-01-01 19:40 170
2008-01-01 22:40 150
2008-01-02 01:05 205
2008-01-02 12:15 125
2008-01-02 14:35 250
2008-01-02 23:20 170
etc.

SHEET TWO (date, open, high, low, close)

2008-01-01 200 250 150 150
2008-01-02 205 250 125 170

I've attached the excel files here too. Thanks!

**DonkeyOte** · 08-09-2009, 03:12 AM

key questions (given they affect viable approaches)

Q: will both files be open together simultaneously at all times ?

Q: if the answer to the above is no, can I ask whether you would be willing to ensure the data from the 2nd file is present in the first ...

Q: if the answer to the above is no, can you add a column to the 2nd file ?
eg C2: =INT(A2) - applied to all rows ?

there are issues using certain formulae where the source data resides in closed file(s) necessitating less efficient formulae be used...

**db8r** · 08-10-2009, 12:36 AM

Thanks for your quick reply!

No, there is no need to have two sheets really. I just didn't know it made things more complicated. I've put sheet one and two together and attached it here.