Alert when price changes by certain percentage

[yord]

Hello everyone,

I am having a little difficulty in creating some code which will alert me when the prices of a list of stocks move by a certain percentage (I have a live price feed in excel):
The code needs to do the following:
1) Bring up a message box which gives the name of the stock and the time and date the event occurred.
2) When the message box appears, there should be an audible alert until the user clicks ok on the message box.
3) The name of the stock and time and date needs to be recorded on a separate worksheet when the event occurs.

It should only alert me once the price has moved by the certain percentage and not every time the price moves when it is above the percentage.

If we use column A as the name of the stock, column B for the current price and column C for the reference price.

Any help with the code will be very much appreciated

[Mallycat]

So when you say you are having difficulty, have you started? If so, post your sample sheet with what you have got.

[yord]

Hi Mallycat,

Here is what I have so far. It gives an audible alert and message box when the price moves over a certain percentage. However it alerts me everytime the price changes when it is over the percentage. I only want the audible alert and message box when the price has crossed over the percentage.

I'm not sure how to code it for a list of stocks and I'm not sure how to copy the message box into a new worksheet.

Many thanks

[Mallycat]

I am not sure if you wrote this yourself or if you got it elsewhere.

This code is incorrect.

Please Login or Register  to view this content.

The whole concept of a change event is that the 'target' is passed to the event. eg, if you wrote this code

Please Login or Register  to view this content.

you would be able to find out which cell has changed. So in your case, you are trying to execute your code only when one of your percentages crosses the threshold. To do this, you will need to have a before and after cell so that you can trap the change. So maybe in column K you can paste the % from the previous update, then in column L you could put a formula that traps if the percentage change is above your threshold AND if the previous percentage change is below your threshold. Make this column evaluate to TRUE or FALSE. Then you don't really need a bell at all, you can just look at the True items and see the ones you need to look at. But assuming you had such a column L

Then something like this

Please Login or Register  to view this content.

[yord]

Hi Mallycat.

Thanks for the direction, got a lot further with this.

Much appreciated.

[yord]

Hi Mallycat,

I'm having issues with the message box. I have trapped the change and in column" "L" I have an IF function which gives TRUE if the change is above my threshold AND previous change is below my threshold or FALSE otherwise.

I am using the code you provided to bring up the message box but get Run Time Error '91': Object Variable or With block variable not set. Am I missing something?

Thanks

[Mallycat]

Could be, but please post the sample again with your latest code

[jworkman7]

Yord,

I'm interested in the live Excel feed you have created, that is something I have looked into but not taken the time to make. I would be interested in obtaining a copy of it if you are so kind as to provide it. Thanks