A Challenge

[jimbob]

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!!

 

[Dave Peterson]

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)

 

[Don Guillett]

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]

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

 

[jimbob]

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

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

So what did you try?

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.

 

[Don Guillett]

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 .

 

[jimbob]

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
posted, twice

 

[gjcase]

Sorry for butting in late. Are you looking for the maximum sprea
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)?

---GJ

 

[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....

 

[jimbob]

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.

 

[gjcase]

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

 

[Don Guillett]

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 { }.

 

[jimbob]

I am looking for the absolute value. I follow about 20 spreads. Th
charting program I use does not have any way to construct a histori
chart of the spread on it's own....I have to import the data. Th
reason I am using hourly data and not just end of day data is becaus
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
stocks do not trace exactly, in other words stock A may have hit it
high for the day at 10:00AM while stock B did that at 2:00PM. Thank
for ebveryones help and I'm sorry if I wasn't clear in my posts.
will try the array formulas that Don posted. One question: Do I cop
that array formula in the first blank Col to the right?

 

[Don Guillett]

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?

 

[jimbob]

Don,

Thanks for your help and concern