How to Program Excel to Create a Warning Signal?

[amirstal]

I have a rather complicated question (at least I think it is...)

I have a cell that shows a number. That number is not constant, but
changing every couple of seconds. Let's say it shows the profit and
loss (p/l) level in USD derived from an open position in some a stock.

Once p/l reaches $25,000 and/or goes above, I want excel to give me a
warning (a pop-up window plus an audio one if possible) when the p/l
level reaches 67% of the maximum level it had been.

So, for example, if the p/l has surpassed $25,000 and reached $100,000
and than dropped to $50,000 but never returned to $100,000, excel will
give me the warning when it reaches $67,000 (67% of $100,000).

In the case $25,000 was the highest level and the p/l dropped
thereafter, excel will keep the "67%" rule and warn me at $16,750 (67%
of 25,000).

Can this be done at all?

Thanks,

Amir

 

[merjet]

Probably yes. Suppose cell A2 has the stock's purchase price, cell B2
the highest price since purchase, cell C2 the current price, and cell
D2 the gain/loss since purchase. I assume C2 changes automatically
somehow, since you say it changes every couple of seconds, and D2
recalculates when C2 changes. Then you could use something like the
following in the code module for that worksheet.

Private Sub Worksheet_Calculate()
If Range("D2") >= 25000 Then _
MsgBox "$25,000 gain", vbExclamation
If Range("C2") <= 0.6667 * Application.Max(Range("A2"), Range("B2"))
Then _
MsgBox "1/3rd loss", vbExclamation
End Sub


Hth,
Merjet

 

[amirstal]

Probably yes. Suppose cell A2 has the stock's purchase price, cell B2
the highest price since purchase, cell C2 the current price, and cell
D2 the gain/loss since purchase. I assume C2 changes automatically
somehow, since you say it changes every couple of seconds, and D2
recalculates when C2 changes. Then you could use something like the
following in the code module for that worksheet.

Private Sub Worksheet_Calculate()
If Range("D2") >= 25000 Then _
MsgBox "$25,000 gain", vbExclamation
If Range("C2") <= 0.6667 * Application.Max(Range("A2"), Range("B2"))
Then _
MsgBox "1/3rd loss", vbExclamation
End Sub

Hth,
Merjet

Thanks for your reply.
In fact, the price of the stock is irrelevant. What only matters is
the profit and loss (p/l) level, which changing constantly.
Can the code above be used when there is only one cell involved?

Thanks,

Amir

 

[merjet]

What only matters is the profit and loss (p/l) level,

which changing constantly. Can the code above be
used when there is only one cell involved?

Maybe 'yes' for the $25000 gain. You haven't said
how the p/l cell changes. 'No' for the 1/3rd loss,
since more info than simply the p/l is needed.

Merjet

 

[amirstal]

Maybe 'yes' for the $25000 gain. You haven't said
how the p/l cell changes. 'No' for the 1/3rd loss,
since more info than simply the p/l is needed.

Merjet

The P/L (which can also be any price of a stock for example) is
derived from an application that is connected directly to Excel (from
Reuters database). Maybe if we think of it as a price of a stock that
is changing throughout the day (and has no code or formula), it will
be easier. So, I am looking for excel to capture the highest price of
the day and save it on a different cell. If the stock started the day
at 50, went to 60, dropped to 40 and went back to 59, excel will show
60 has the highest price until the price exceeds it.

Amir

 

[merjet]

Again, use the Worksheet_Calculate() procedure.

Merjet