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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

michaelyeehaw

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Ok, no problem
- update the thread when you can answer
thanks
 

michaelyeehaw

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

Watch MrExcel Video

Forum statistics

Threads
1,123,257
Messages
5,600,560
Members
414,388
Latest member
Pkmep4

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
Top