Enter Timestamp for each time cell value changes

[michaelyeehaw]

I have dynamic low of day (LOD) stock prices in column A and last stock prices (LSP) in column B. The data is pulled from an external web service and never hard coded. I need to create 11 additional columns of data based on these two values:

Column C would display the times the LSP fell below LOD plus .01
Column D would display the times the LSP fell below LOD plus .02
Column E would display the times the LSP fell below LOD plus .03
Column F would display the times the LSP fell below LOD plus .04
and so on

For example: The LOD in A5 is 10.5.

C5 would display the timestamps for when the LSP fell below 10.51
D5 would display the timestamps for when the LSP fell below 10.52
E5 would display the timestamps for when the LSP fell below 10.53
F5 would display the timestamps for when the LSP fell below 10.54
...
M5 would display the timestamps for when the price fell below 10.61

If the LOD fell to 10.49, all the data would shift right one column, with C5 the only cell to display the new timestamp as the LSP fell below 10.50 and M5 now displays the timestamps for when the LSP fell below 10.60 as the data had been shifted right one column (previous data in M5 is now gone).

If the LOD suddenly changes from 10.50 to something like 10.45 at 10:30:29 AM, the data shifts right 5 rows such that rows I to M disappear to make way for the five fresh new sets of data in rows C to G, each containing the one timestamp 10:30:29 AM.

Upward movements of the LSP wont necessitate a timestamp. Only negative movements of the LSP below LOD plus .11 will necessitate a new timestamp, each separated by a semicolon (, in the rows represented by the prices the LSP decreased below.

Note: if the LOD is 10.50, a sudden move in the LSP from 10.55 to 10.53 would only one time stamp for Columns G and F as the LSP decreased below LOD plus .05 and LOD plus .04.

 

[Yongle]

Welcome to MrExcel

I can help you get started
Event macro Worksheet_Calculate can be used to do what you require

First a little test
Place code below in the sheet's code window - do not place it anywhere else as it will not work
Right click on sheet tab\ View Code \ paste code into that window

Private Sub Worksheet_Calculate()
    Debug.Print Now
End Sub

Explanation
The code simply writes something like this to the immediate window
25/07/2020 08:15:51

The immediate window is displayed in VBA editor with {CTRL} g or via the menu option View \ImmediateWindow
Select

Results
Have a look in the immediate window AFTER the values have been updated ONCE
Is the code triggered when the values in the sheet are updated ?
- if not, place this volatile formula in any unused cell in the worksheet
=NOW()
The code will be triggered when you enter the formula
I also expect it to be triggered when the values in the sheet are autoupdated

Q How many times is the time stamp written to the immediate window each time your values are auto-updated ?

 

[michaelyeehaw]

This worked well. i ran the code, made some changes, and then the changes were reflected in the window. Eager to know the next steps

 

[Yongle]

I ask questions because I do not see what is going on in your computer
So it is important that you answer every question that is asked

What is the answer to the question in the last line of post#2 ?
thanks

 

[michaelyeehaw]

the answer is the window updated each time i changed the sheet. i delete the window contents and tried several times with various changes to verify this was the case

 

[Yongle]

Multiple values are updated concurrently when the sheet is auto-updated.
When that happens is the code triggered ONCE or more than ONCE?

If you cannot answer the question ...
- delete all values in the sheet
- delete whatever is in the immediate window
- and have the sheet fully auto-updated
- look in immediate window to see how many times the date stamp appears

I need confirmation that the code is only triggered once
thanks

 

[michaelyeehaw]

only updates once for one manual update change. hope this finally answers your question

 

[michaelyeehaw]

only updates once for one manual update change. hope this finally answers your question

cannot test with dynamic data at the moment

 

[Yongle]

Ok, no problem
- update the thread when you can answer
thanks

 

[michaelyeehaw]

ok i tried it out with dynamic data and it only updates with one timstamp when 1 change made and 3 when i made 3 changes