Enter Timestamp for each time cell value changes

michaelyeehaw

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
VBA Code:
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
only updates once for one manual update change. hope this finally answers your question
 
Upvote 0
Ok, no problem
- update the thread when you can answer
thanks
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top