Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Program Excel to Create a Warning Signal?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Program Excel to Create a Warning Signal?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Program Excel to Create a Warning Signal?
On Mar 31, 12:29 am, "merjet" wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Program Excel to Create a Warning Signal?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Program Excel to Create a Warning Signal?
On Apr 2, 3:50 pm, "merjet" wrote:
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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Program Excel to Create a Warning Signal?
Again, use the Worksheet_Calculate() procedure.
Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
warning signal | Excel Programming | |||
Can I set up a signal in Excel to let me know when my due date is. | Excel Worksheet Functions | |||
How to turn off warning when hyperlinking to other program? | Excel Discussion (Misc queries) | |||
Email from Excel; MS warning "program is trying to send mail on your behalf" | Excel Programming | |||
Why does my excel program create a .xls:1 extension? | Excel Discussion (Misc queries) |