A Challenge

Ok, This is a tuffy.

I have a spreadsheet that has the hourly data for two stocks (Stock A minus Stock B). This is called a spread. Each line represents 1 hour of data. The columns look like this:

Date Open High Low Close
12/21/2005 -0.6 -0.38 -0.69 -0.3
12/21/2005 -0.3 -0.27 -0.45 -0.4
12/21/2005 -0.41 -0.41 -0.54 -0.55
12/21/2005 -0.54 -0.48 -0.33 -0.31
12/21/2005 -0.29 -0.35 -0.39 -0.45
12/21/2005 -0.44 -0.45 -0.35 -0.51
12/21/2005 -0.5 -0.53 -0.49 -0.59
12/22/2005 -0.73 -0.54 -0.58 -0.34
12/22/2005 -0.35 -0.35 -0.18 -0.17
12/22/2005 -0.17 -0.14 0.03 0.12
12/22/2005 0.1 0.03 0.13 0.14
12/22/2005 0.14 0.12 -0.08 -0.08
12/22/2005 -0.08 0.02 0.04 0.01
12/22/2005 0.02 -0.01 0.02 0.03
12/23/2005 0.36 0.02 -0.24 -0.18
12/23/2005 -0.17 0.03 -0.1 -0.06
12/23/2005 -0.07 -0.03 -0.06 -0.03
12/23/2005 -0.05 0.02 -0.02 0.01
12/23/2005 -0.01 0.02 0.01 0.02
12/23/2005 0.01 0.07 -0.01 0
12/23/2005 0 0.03 0 0.03

As you can see for each day there are 7 entries. I need to find the highest high and the lowest low for each day and have it export in order by data so that I get something that looks like this.............

Date Open High Low Close
12/8/2005 0.1 0.51 -0.04 0.26
12/9/2005 0.18 0.42 -0.04 0.28
12/12/2005 0.35 0.76 0.17 0.73
12/13/2005 0.85 0.85 -0.33 -0.18
12/14/2005 -0.13 -0.13 -0.75 -0.55
12/15/2005 -0.58 -0.42 -0.82 -0.44
12/16/2005 -0.56 -0.46 -0.91 -0.57

Thank you!!

It looks like a job for Data|pivottable. You can bring in the max or min of any
field.

Or you could use Data|subtotals.

Use Max as the function for both fields.
And use each change in the Date column.

You'll end up with formulas like:
=subtotal(4,c2:c9)

The 4 means max.

Then select the column that should have the minimums.
Edit|replace
what: =subtotal(4,
with: =subtotal(5,
replace all

(5 means min.)

Then you can use the outlining symbols on the left to hide/show the details.

If you need to save just the subtotal rows:
hide the details
select the range
edit|goto|special|visible cells only
edit|copy
edit|paste (at a new location)


jimbob wrote:
>
> Ok, This is a tuffy.
>
> I have a spreadsheet that has the hourly data for two stocks (Stock A
> minus Stock B). This is called a spread. Each line represents 1 hour of
> data. The columns look like this:
>
> Date Open High Low Close
> 12/21/2005 -0.6 -0.38 -0.69 -0.3
> 12/21/2005 -0.3 -0.27 -0.45 -0.4
> 12/21/2005 -0.41 -0.41 -0.54 -0.55
> 12/21/2005 -0.54 -0.48 -0.33 -0.31
> 12/21/2005 -0.29 -0.35 -0.39 -0.45
> 12/21/2005 -0.44 -0.45 -0.35 -0.51
> 12/21/2005 -0.5 -0.53 -0.49 -0.59
> 12/22/2005 -0.73 -0.54 -0.58 -0.34
> 12/22/2005 -0.35 -0.35 -0.18 -0.17
> 12/22/2005 -0.17 -0.14 0.03 0.12
> 12/22/2005 0.1 0.03 0.13 0.14
> 12/22/2005 0.14 0.12 -0.08 -0.08
> 12/22/2005 -0.08 0.02 0.04 0.01
> 12/22/2005 0.02 -0.01 0.02 0.03
> 12/23/2005 0.36 0.02 -0.24 -0.18
> 12/23/2005 -0.17 0.03 -0.1 -0.06
> 12/23/2005 -0.07 -0.03 -0.06 -0.03
> 12/23/2005 -0.05 0.02 -0.02 0.01
> 12/23/2005 -0.01 0.02 0.01 0.02
> 12/23/2005 0.01 0.07 -0.01 0
> 12/23/2005 0 0.03 0 0.03
>
> As you can see for each day there are 7 entries. I need to find the
> highest high and the lowest low for each day and have it export in
> order by data so that I get something that looks like
> this.............
>
> Date Open High Low Close
> 12/8/2005 0.1 0.51 -0.04 0.26
> 12/9/2005 0.18 0.42 -0.04 0.28
> 12/12/2005 0.35 0.76 0.17 0.73
> 12/13/2005 0.85 0.85 -0.33 -0.18
> 12/14/2005 -0.13 -0.13 -0.75 -0.55
> 12/15/2005 -0.58 -0.42 -0.82 -0.44
> 12/16/2005 -0.56 -0.46 -0.91 -0.57
>
> Thank you!!
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714

--

Dave Peterson

This is an array formula which must be entered using ctrl+shift+enter CSE.
F2 contains 1st date


=max(IF($A$2:$A$22=F2,$C$2:$C$22))
--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> Ok, This is a tuffy.
>
> I have a spreadsheet that has the hourly data for two stocks (Stock A
> minus Stock B). This is called a spread. Each line represents 1 hour of
> data. The columns look like this:
>
> Date Open High Low Close
> 12/21/2005 -0.6 -0.38 -0.69 -0.3
> 12/21/2005 -0.3 -0.27 -0.45 -0.4
> 12/21/2005 -0.41 -0.41 -0.54 -0.55
> 12/21/2005 -0.54 -0.48 -0.33 -0.31
> 12/21/2005 -0.29 -0.35 -0.39 -0.45
> 12/21/2005 -0.44 -0.45 -0.35 -0.51
> 12/21/2005 -0.5 -0.53 -0.49 -0.59
> 12/22/2005 -0.73 -0.54 -0.58 -0.34
> 12/22/2005 -0.35 -0.35 -0.18 -0.17
> 12/22/2005 -0.17 -0.14 0.03 0.12
> 12/22/2005 0.1 0.03 0.13 0.14
> 12/22/2005 0.14 0.12 -0.08 -0.08
> 12/22/2005 -0.08 0.02 0.04 0.01
> 12/22/2005 0.02 -0.01 0.02 0.03
> 12/23/2005 0.36 0.02 -0.24 -0.18
> 12/23/2005 -0.17 0.03 -0.1 -0.06
> 12/23/2005 -0.07 -0.03 -0.06 -0.03
> 12/23/2005 -0.05 0.02 -0.02 0.01
> 12/23/2005 -0.01 0.02 0.01 0.02
> 12/23/2005 0.01 0.07 -0.01 0
> 12/23/2005 0 0.03 0 0.03
>
> As you can see for each day there are 7 entries. I need to find the
> highest high and the lowest low for each day and have it export in
> order by data so that I get something that looks like
> this.............
>
> Date Open High Low Close
> 12/8/2005 0.1 0.51 -0.04 0.26
> 12/9/2005 0.18 0.42 -0.04 0.28
> 12/12/2005 0.35 0.76 0.17 0.73
> 12/13/2005 0.85 0.85 -0.33 -0.18
> 12/14/2005 -0.13 -0.13 -0.75 -0.55
> 12/15/2005 -0.58 -0.42 -0.82 -0.44
> 12/16/2005 -0.56 -0.46 -0.91 -0.57
>
> Thank you!!
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

BTW. Please try to use meaningful subject lines in your subject

--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> Ok, This is a tuffy.
>
> I have a spreadsheet that has the hourly data for two stocks (Stock A
> minus Stock B). This is called a spread. Each line represents 1 hour of
> data. The columns look like this:
>
> Date Open High Low Close
> 12/21/2005 -0.6 -0.38 -0.69 -0.3
> 12/21/2005 -0.3 -0.27 -0.45 -0.4
> 12/21/2005 -0.41 -0.41 -0.54 -0.55
> 12/21/2005 -0.54 -0.48 -0.33 -0.31
> 12/21/2005 -0.29 -0.35 -0.39 -0.45
> 12/21/2005 -0.44 -0.45 -0.35 -0.51
> 12/21/2005 -0.5 -0.53 -0.49 -0.59
> 12/22/2005 -0.73 -0.54 -0.58 -0.34
> 12/22/2005 -0.35 -0.35 -0.18 -0.17
> 12/22/2005 -0.17 -0.14 0.03 0.12
> 12/22/2005 0.1 0.03 0.13 0.14
> 12/22/2005 0.14 0.12 -0.08 -0.08
> 12/22/2005 -0.08 0.02 0.04 0.01
> 12/22/2005 0.02 -0.01 0.02 0.03
> 12/23/2005 0.36 0.02 -0.24 -0.18
> 12/23/2005 -0.17 0.03 -0.1 -0.06
> 12/23/2005 -0.07 -0.03 -0.06 -0.03
> 12/23/2005 -0.05 0.02 -0.02 0.01
> 12/23/2005 -0.01 0.02 0.01 0.02
> 12/23/2005 0.01 0.07 -0.01 0
> 12/23/2005 0 0.03 0 0.03
>
> As you can see for each day there are 7 entries. I need to find the
> highest high and the lowest low for each day and have it export in
> order by data so that I get something that looks like
> this.............
>
> Date Open High Low Close
> 12/8/2005 0.1 0.51 -0.04 0.26
> 12/9/2005 0.18 0.42 -0.04 0.28
> 12/12/2005 0.35 0.76 0.17 0.73
> 12/13/2005 0.85 0.85 -0.33 -0.18
> 12/14/2005 -0.13 -0.13 -0.75 -0.55
> 12/15/2005 -0.58 -0.42 -0.82 -0.44
> 12/16/2005 -0.56 -0.46 -0.91 -0.57
>
> Thank you!!
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

Neither of those solutions works. The max and min could be in any of the 4 columns......that's the problem. Any other solutions?

I think if you had stated that portion in the original message, you wouldn't
have gotten those replies.

I'd use helper columns and put

=min(b2:e2)
and
=max(b2:e2)

and drag down. Then use data|subtotals for just those two columns.

But you could use Don's formula, too:

=max(IF($A$2:$A$22=F2,$b$2:$e$22))

Still an array formula.

jimbob wrote:
>
> Neither of those solutions works. The max and min could be in any of
> the 4 columns......that's the problem. Any other solutions?
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714

--

Dave Peterson

So what did you try?



jimbob wrote:
>
> I can't get it to work. Maybe I wasn't clear, so here goes......
>
> Col A has the Date, Col B has the Open for that hour, Col C has the
> High (ONLY for that hour!), Col D has the Low (ONLY for that hour) and
> Col E has the Close for that hour. I want to find the Low for the DAY
> and the High for the DAY. To do this, all 7 lines for each day must be
> compared to find the lowest low and highest high.
>
> I am not that well versed in Excel, so let me try to say it the way I
> would describe it to someone....
>
> "For all the entries that have the same date, find the lowest low and
> highest high in cells B2 thru E8, then export those values into another
> col or worksheet with the appropriate date". *Note that the next date
> starts on row 9, then the third date starts on row 16 and so on. There
> are 7 rows of data for each DAY.
>
> Thanks again for all your help, I really appreciate it.
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile: http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714

--

Dave Peterson

I agree with Dave but I still think you want the highest HIGH and the lowest
LOW. Probably the same either way, but??? Give us an example of the CORRECT
answer(s) for you .

--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> I can't get it to work. Maybe I wasn't clear, so here goes......
>
> Col A has the Date, Col B has the Open for that hour, Col C has the
> High (ONLY for that hour!), Col D has the Low (ONLY for that hour) and
> Col E has the Close for that hour. I want to find the Low for the DAY
> and the High for the DAY. To do this, all 7 lines for each day must be
> compared to find the lowest low and highest high.
>
> I am not that well versed in Excel, so let me try to say it the way I
> would describe it to someone....
>
> "For all the entries that have the same date, find the lowest low and
> highest high in cells B2 thru E8, then export those values into another
> col or worksheet with the appropriate date". *Note that the next date
> starts on row 9, then the third date starts on row 16 and so on. There
> are 7 rows of data for each DAY.
>
> Thanks again for all your help, I really appreciate it.
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

I tried copying the array formula into a col and got #VALUE. And yes, I'm looking for the highest HIGH and lowest LOW, which is what I posted, twice.

Sorry for butting in late. Are you looking for the maximum spread regardless of whether A is higher than B or not (i.e., absolute value), or are you looking for the max & min value of the spread (A-B=5 is high, A-B=-5 is low)?

---GJC

Did you modify the formula to meet your criteria? Did you enter using
ctrl+shift+enter as instructed? Give us an example of the "correct" answer
you are looking for. OR....

--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> I tried copying the array formula into a col and got #VALUE. And yes,
> I'm looking for the highest HIGH and lowest LOW, which is what I
> posted, twice.
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

Lets try another tact. Please refer to my opening post. There are 7 lines of data for 12/21/2005. I want to find and export the HIGHEST value and LOWEST value for the 4 columns (B, C, D, E) of cells to the right of all dates that match 12/21/2005. So in this case, -.27 is my highest value and -.55 is my lowest value. I then want to do the same thing for 12/22/2005 and 12/23/2005 and so on.

Regarding the array formula, I do not know how to modify it since I'm unfamiliar with that function. I am very new to Excel, so don't assume I know anything.

Originally Posted by Don Guillett

Did you modify the formula to meet your criteria? Did you enter using
ctrl+shift+enter as instructed? Give us an example of the "correct" answer
you are looking for. OR....

--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> I tried copying the array formula into a col and got #VALUE. And yes,
> I'm looking for the highest HIGH and lowest LOW, which is what I
> posted, twice.
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

I was able to sort of do what you're after using helper columns & a pivot table. I added 2 columns to calculate the max of each row (MAX) & the min of each row (MIN) respectively, and one col to pick up the last close, DayClose (if the date in each row <> equal to the date in the next, then enter the date). I then did a pivot table with dates in the LH col, and in the data field I put Max of Max col and the Min of Min col, and the sum of DayClose col.

Data came out as follows:

Date Data Total
12/21/05 Max of Max -0.27
Min of Min -0.69
Sum of DayClose -0.59
12/22/05 Max of Max 0.14
Min of Min -0.73
Sum of DayClose 0.03
12/23/05 Max of Max 0.36
Min of Min -0.24
Sum of DayClose 0.03

Not exactly what you want, but close. You could reformat manually if you needed this in rows only. I don't know whether there is a way to get a Pivot table to place the field data in rows vs columns.

HTH

---GJC

I still don't agree (you should want the highest high and the lowest low)
but, I think, this does what you want. We are matching the date with the
first where f2 contains the date desired. $A$2:$A$22=F2. Then we take the
maximum of b:e for that date which is -.27
=MAX(IF($A$2:$A$22=F2,$B$2:$E$22))=-.27
=MAX(IF($A$2:$A$22=F2,$B$2:$E$22))-.69 Not -.55 as you said
again, these are array formulae so they must be entered with
Ctrl+shift+enter instead of just enter. When properly done the formula is
wrapped in { }.


--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> Lets try another tact. Please refer to my opening post. There are 7
> lines of data for 12/21/2005. I want to find and export the HIGHEST
> value and LOWEST value for the 4 columns (B, C, D, E) of cells to the
> right of all dates that match 12/21/2005. So in this case, -.27 is my
> highest value and -.55 is my lowest value. I then want to do the same
> thing for 12/22/2005 and 12/23/2005 and so on.
>
> Regarding the array formula, I do not know how to modify it since I'm
> unfamiliar with that function. I am very new to Excel, so don't assume
> I know anything.
>
>
> Don Guillett Wrote:
>> Did you modify the formula to meet your criteria? Did you enter using
>> ctrl+shift+enter as instructed? Give us an example of the "correct"
>> answer
>> you are looking for. OR....
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "jimbob" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > I tried copying the array formula into a col and got #VALUE. And
>> yes,
>> > I'm looking for the highest HIGH and lowest LOW, which is what I
>> > posted, twice.
>> >
>> >
>> > --
>> > jimbob
>> >
>> ------------------------------------------------------------------------
>> > jimbob's Profile:
>> > http://www.excelforum.com/member.php...o&userid=29107
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=527714
>> >
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

I am looking for the absolute value. I follow about 20 spreads. The charting program I use does not have any way to construct a historic chart of the spread on it's own....I have to import the data. The reason I am using hourly data and not just end of day data is because eod data does give an accurate relfection of the range (high to low) that the spread had during the day. The reason for this is that the 2 stocks do not trace exactly, in other words stock A may have hit its high for the day at 10:00AM while stock B did that at 2:00PM. Thanks for ebveryones help and I'm sorry if I wasn't clear in my posts. I will try the array formulas that Don posted. One question: Do I copy that array formula in the first blank Col to the right?

Originally Posted by gjcase

Sorry for butting in late. Are you looking for the maximum spread regardless of whether A is higher than B or not (i.e., absolute value), or are you looking for the max & min value of the spread (A-B=5 is high, A-B=-5 is low)?

---GJC

Isn't that obvious from the example? It really makes no difference where you
put it if you have your reference correct. Of course you would put it in g2
if you had a list of dates in col F and just wanted to copy the formula down
after you are satisified it works. Are you sure that this trading thing is
for you?

>>Do I copy that array formula in the first blank Col to the right?


--
Don Guillett
SalesAid Software
[email protected]
"jimbob" <[email protected]> wrote in
message news:[email protected]...
>
> I am looking for the absolute value. I follow about 20 spreads. The
> charting program I use does not have any way to construct a historic
> chart of the spread on it's own....I have to import the data. The
> reason I am using hourly data and not just end of day data is because
> eod data does give an accurate relfection of the range (high to low)
> that the spread had during the day. The reason for this is that the 2
> stocks do not trace exactly, in other words stock A may have hit its
> high for the day at 10:00AM while stock B did that at 2:00PM. Thanks
> for ebveryones help and I'm sorry if I wasn't clear in my posts. I
> will try the array formulas that Don posted. One question: Do I copy
> that array formula in the first blank Col to the right?
>
>
>
> gjcase Wrote:
>> Sorry for butting in late. Are you looking for the maximum spread
>> regardless of whether A is higher than B or not (i.e., absolute value),
>> or are you looking for the max & min value of the spread (A-B=5 is high,
>> A-B=-5 is low)?
>>
>> ---GJC
>
>
> --
> jimbob
> ------------------------------------------------------------------------
> jimbob's Profile:
> http://www.excelforum.com/member.php...o&userid=29107
> View this thread: http://www.excelforum.com/showthread...hreadid=527714
>

Don,

Thanks for your help and concern.