Tricky formulas needed

[Guest]

Hello,
I have a tricky problem I would like to see if anyone is able to figure out.
I need to find a certain “condition†that occurs many times through 400 rows
of data. I want to place 2 formulas in cells C500 & D500 that will find and
calculate the below example:

A B What I need
1 100 98
2 95 97 ----- A2 dips below B2. I need this
condition identified first.
3 90 94 ----- A3 is less than A2. Column B is no
longer relevant.
4 85 91 ----- A4 is less than A3 Column B is no
longer relevant.
5 89 92 ----- A5 is larger than A4 Column B is no
longer relevant.
6 numbers in A & B after this do not matter after rows 2 thru 5
“condition†has been found.
7 continue down A & B to find the next “condition†as rows 2 thru 5.


Now in C500 instead of a result of “TRUEâ€, could C500 contain the total
number of times this “condition†occurred between rows 1 thru 400? (the
values in columns A & B constantly change).

Finally in cell D500, what formula can I place here to (per example above)
calculate the % decline in value of A4 from A2? With this knowledge, I need
all the % declines of all the “conditions†found to result into an AVERAGE %
drop showing up in cell D500.

Thank you very much to anyone who can help me!
George B.

 

[Guest]

Hi
not fully sure but try:
C500
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398),--(A4:A400>A3:A399))


D500
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398),--(A4:A400>A3:A399),(1-A3:A399/A1:A397))/SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398),--(A4:A400>A3:A399))

 

[Myrna Larson]

Is this to do with stock market prices? If so, you may find it more useful to
buy specialized software.

 

[Guest]

Hello Frank,
Cell C500 formula worked great! But, in cell D500 the result came up #DIV/0!

Any idea how to fix?

Appreciate your help Frank
George.

 

[Frank Kabel]

Hi
what result do you have in C500 and what are the exact formulas you
have used in both cells

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
not fully sure but try:
C500:
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400>A3:A399))


D500:

=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400>A3:A399),(1-A3:A399/A1:A397))/SUMPRODUCT(--(A1:A397<B1:B397

 

[Guest]

Hello Myrna,
Thank your for reading my post. This does have to do with stock prices. I
have checked with my existing stock data provider and external software for a
number of circumstances pertaining to stock data I wish to explore. In
researching this avenue, software out ther does get specific on some points
but mostly large general calculations. I am attempting to be very specific
with respect to certain conditions that appear in my way of trying to analize
stock data. I appreciate your suggestion though.

George.

 

[Myrna Larson]

That's what I thought. A "technician"/"chartist", right? My term for same is
"phrenologist" <g>.

As such, you might find it more useful to chart the prices and look (with your
eyes) for the changes you are interested in.

 

[Guest]

Hi, The result in C500 is 28. Result in D500 is #DIV/0!

The formula I used in C500 was exactly the formula you suggested.
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400>A3:A399))

The formula I used in D500 was exactly the other formula you suggested also.
=SUMPRODUCT(--(A1:A397<B1:B397),--(A2:A398<A1:A397),--(A3:A399<A2:A398)
,--(A4:A400>A3:A399),(1-A3:A399/A1:A397))/SUMPRODUCT(--A1:A397<B1:B397
),--(A2:A398<A1:A397),--(A3:A399<A2:A398),--(A4:A400>A3:A399))

Thanks,
George

 

[Myrna Larson]

Let me see if I can put into words what you are trying to accomplish.

I'm going to assume that column A is the day's open price, B is the day's
close price. B-A represents the intra-day gain or loss. A2-A1 represents the
inter-day gain or loss based on the open prices.

You want to identify the beginning and end of a trend, defined as follows: it
begins on the first day on which there is a positive intra-day change (A1<B1).
The last day is the day before the first inter-day gain (if A2>A1, it's A1).

If that's correct, the following formulas will work to give you the result in
C500:

You need a helper column, C. The value there is either 1 or 0, with a 1
meaning a trend has started and you are searching for the end of it; 0 meaning
one trend has ended and you are looking for the start of the next one.

In C1, put the formula =IF(A1<B1,1,0)

In C2 =IF(C1=1,IF(A2>A1,0,1),IF(A2<B2,1,0))

Copy this formula down through row 400.

Then you can use this formula in C500.

=SUMPRODUCT(--(C1:C399=1),--(C2:C400=0))

For D500, it's a different story. You want to calculate the percent change for
each trend, then average these percentages for all trends that have occurred
over the 400 days.

To do that, you have to find the 1st cell in a string of 1's, and the last
cell in that string of 1's, get the corresponding values from column A and
subtract them; then continue that over the entire 400 cell range, and average
the list of changes. I'm not sure you can do that readily with formulas,
though it's possible with a VBA macro.

I think this one will do. It returns an array consisting of the number of
trends completed (i.e. number of times the formula in column C would change
from 1 to 0), and the average price change (I calculated it as P2/P1 - 1, so a
loss is negative, a gain is positive).

With this function, you don't need the helper column, but you may want to set
it up anyway to check the macro results. Note that, if you do that and the
last value in column C is a 1, that trend won't be counted as it hasn't
completed.

To use the function, select 2 adjacent cells in the same row, say C500 and
D500, type the formula =TRENDDATA(A1:B400), and press CTRL+SHIFT+ENTER. If you
just press ENTER, you'll only see the count, not the average.

When you make a million dollars with this, you can send me a 25% cut. If you
lose your shirt, you never heard of me.

I'm not holding my breath <g>. I invested the time to write the macro because
the logic interested me, not because I thought the idea would work.


Option Explicit

Function TrendData(PriceData As Range) As Variant
Dim Count As Long
Dim Changes() As Double
Dim i As Long
Dim InTrend As Boolean
Dim P0 As Double
Dim Prices As Variant

If PriceData.Rows.Count < 3 Or PriceData.Columns.Count <> 2 Then
TrendData = CVErr(xlErrRef)
Exit Function
End If

Prices = PriceData.Value
ReDim Changes(1 To UBound(Prices)) 'bigger than needed

InTrend = (Prices(1, 2) > Prices(1, 1)) 'intra-day gain
If InTrend Then P0 = Prices(1, 1)

For i = 2 To UBound(Prices, 1)
If InTrend Then
If Prices(i, 1) > Prices(i - 1, 1) Then
'look for between-day gain = end of trend
Count = Count + 1
Changes(Count) = Prices(i - 1, 1) / P0 - 1
InTrend = False
End If
Else
If Prices(i, 2) > Prices(i, 1) Then
'look for within-day gain = start of trend
InTrend = True
P0 = Prices(i, 1)
End If
End If
Next i

ReDim Preserve Changes(1 To Count)
TrendData = Array(Count, Application.Average(Changes))

End Function

 

[Myrna Larson]

Please tell me what the VBA macro gives with those data.

 

[Myrna Larson]

I'm still not sure I understand the problem definition.

In looking at the first formula below, it looks for this pattern like this:
A1<B1, A2<A1, A3<A2, and A4>A3. In other words, a "trend" must last EXACTLY 3
days (A1:A3 in this case). Was that your intention? I assumed the trend could
be of any length. If I was wrong, then the macro won't do.

 

[Guest]

Hello Myrna,
I appreciate your advice. Just to clarify the stock data I am using, "A"
column contains the "low" of day, and the "B" column is a "moving average"
value. I don't care about intraday open, high or close values.

My intention was to identify this "trend" lasting EXACTLY 3 days in this
case. My goal in creating this post was to have a formula in 1 cell at
bottom showing me how many times this "specific trend" occured. Then another
formula in a 2nd cell at bottom telling me what the average % of decline from
the first "low of day" to the 3rd "low of day". (average of all trend
declines together).

As for making a million bucks in the stock market with this, I'm not sure
how any one approach could be THE formula for success. The challenge of
trying to make sense of the market is the driving factor me as much as trying
to make money from it.

I appreciate your help very much.
George.

 

[Myrna Larson]

I misread your question. I couldn't imagine that a trend of a particular
length would be significiant, but then I'm not a technician.

If you can't get the proposed worksheet formula for D500 to work, it would be
possible to modify the VBA code to specify both the data range and the length
of the trend.

That might be easier than reworking those formulas if you decide you now want
to look for 2-day or 4-day trends <g>.

 

[Myrna Larson]

I've modified the code as I mentioned in a previous reply. If you want to see
it, let me know. It will most likely be lower to calculate than a formula, but
if you decide you want to see data for periods of different lengths, it will
be a lot easier to generate the formula <g>.

 

[Guest]

Hi Frank, Your formulas did work after all. I was able to determine my
data had a couple spots in it the confused the formula to work right. I
repaired my data and your formulas work great. You are a genius when it
comes to this software.
I really appreciate your assistance!
George.