Help needed for Time Stamp

New Member
Dear Mr Chandoo and all gentlemen who are selflessly assisting others both in their learning and solving problems,

I have a stock trading work sheet, and there are two things I am trying to accomplish and stuggling a lot :
(1) In my trading sheet, I have Buy/Sell signal appearing in a certain column which is based on certain conditions (strategy). So these Buys and Sells appear whenever the conditions are met, in Real Time (Trading sheet takes data from the internet and continues to update every 1 minute, so there are many Buys and Sells spawning from time to time). What I am looking for is the exact time stamp (date not so required as I know the sheet is being updated today). Strangely upon scouring the net, most of the solutions available are about the same formulae viz. IF(A2"",IF(B2"",B2,NOW()),"") and this does'nt serve my purpose. If this is all confusing, let me give a clear example here :
Columns A-E has data like High, Low, Close, Open, LTP (Stock market) .
Column F has a signal : Buy or Sell (which is generated automatically based on a strategy and changes based on any change in the price)
So what I am looking for is to get trigger time (in any Column after Colulmn F which updates the time as and when a signal (Buy or Sell) appears in the Column F. The reason is I want to see which are the new signals that appeared as I am handling 200 rows of scrips.
OR ALTERNATELY,
(2) Can Column F which contains the signal, Buy or Sell be colored in such a manner it accomplishes the following :
(a) New signal BUY as and when it appears should carry a different color, let us say DARK GREEN
(b) Likewise New Signal SELL as and when it appears should carry a different color, let us say DARK RED
(c) All signals after a time decay, say 1 minute (which if configurable would be awesome), should change color. So in the case of Buy (Dark Green), color can change to Light Green and in case of Sell (Dark Red), color can change to say Orange after a certain time interval (period).

Just to add to the above, the formulae IF(A2"",IF(B2"",B2,NOW()),"") seem to accomplish, but my excel sheet refreshes every 1 minute, and the moment it refreshes, the time stamp changes to the local computer time (which is current and not the time when the signal came actually).
I even tried my own like =IF(F2="Buy",IF(F2="Sell",NOW(),"")), getting the time stamp, again when the excel refreshes, the time stamp changes to the current time. For e.g. if Buy or Sell came at say 10:15 hrs, after refreshing (let us say 2 minute refresh interval), the time reflected is 10:17 hrs, while the actual signal came at 10:15 hrs.

Thanks for reading and any assistance will be greatly appreciated.

jayalaxmi

Active Member
Dear Mr Chandoo and all gentlemen who are selflessly assisting others both in their learning and solving problems,

I have a stock trading work sheet, and there are two things I am trying to accomplish and stuggling a lot :
(1) In my trading sheet, I have Buy/Sell signal appearing in a certain column which is based on certain conditions (strategy). So these Buys and Sells appear whenever the conditions are met, in Real Time (Trading sheet takes data from the internet and continues to update every 1 minute, so there are many Buys and Sells spawning from time to time). What I am looking for is the exact time stamp (date not so required as I know the sheet is being updated today). Strangely upon scouring the net, most of the solutions available are about the same formulae viz. IF(A2"",IF(B2"",B2,NOW()),"") and this does'nt serve my purpose. If this is all confusing, let me give a clear example here :
Columns A-E has data like High, Low, Close, Open, LTP (Stock market) .
Column F has a signal : Buy or Sell (which is generated automatically based on a strategy and changes based on any change in the price)
So what I am looking for is to get trigger time (in any Column after Colulmn F which updates the time as and when a signal (Buy or Sell) appears in the Column F. The reason is I want to see which are the new signals that appeared as I am handling 200 rows of scrips.
OR ALTERNATELY,
(2) Can Column F which contains the signal, Buy or Sell be colored in such a manner it accomplishes the following :
(a) New signal BUY as and when it appears should carry a different color, let us say DARK GREEN
(b) Likewise New Signal SELL as and when it appears should carry a different color, let us say DARK RED
(c) All signals after a time decay, say 1 minute (which if configurable would be awesome), should change color. So in the case of Buy (Dark Green), color can change to Light Green and in case of Sell (Dark Red), color can change to say Orange after a certain time interval (period).

Just to add to the above, the formulae IF(A2"",IF(B2"",B2,NOW()),"") seem to accomplish, but my excel sheet refreshes every 1 minute, and the moment it refreshes, the time stamp changes to the local computer time (which is current and not the time when the signal came actually).
I even tried my own like =IF(F2="Buy",IF(F2="Sell",NOW(),"")), getting the time stamp, again when the excel refreshes, the time stamp changes to the current time. For e.g. if Buy or Sell came at say 10:15 hrs, after refreshing (let us say 2 minute refresh interval), the time reflected is 10:17 hrs, while the actual signal came at 10:15 hrs.

Thanks for reading and any assistance will be greatly appreciated.
Hi Jedi,

First of all, Welcome to Chandoo's Forum.

I would suggest you not to write such big messages rather be more precise with what you need. For eg, sharing a sample workbook with desired output will be more helpful for the readers to answer your query at the forum.

Thank you
Jaya S

Peter Bartholomew

Well-Known Member
There is a way to prevent an existing timestamp from updating by introducing a circular reference that will iterate once only
http://www.howtoexcelatexcel.com/excel-tips-tricks/create-a-timestamp-in-excel-with-formulas/

I would prefer a VBA solution that responds to the update as an event and converts the result of NOW() into a string (or simply writes the time itself).

The get the time from NOW(), just extract the fractional part
= MOD( NOW(), 1 )

I have just tested the first formula approach (it is not something I have ever tried before) and it appears to work quite well.
= IF( input<>"", IF( time="", MOD( NOW(), 1 ), time ), "" )

Last edited:

New Member
Hi Jedi,

First of all, Welcome to Chandoo's Forum.

I would suggest you not to write such big messages rather be more precise with what you need. For eg, sharing a sample workbook with desired output will be more helpful for the readers to answer your query at the forum.

Thank you
Jaya S

Yes Jaya, will remember that in future, I just wrote elaborately so that every one is clear with what I am looking for.

Cheers
JT

New Member
There is a way to prevent an existing timestamp from updating by introducing a circular reference that will iterate once only
http://www.howtoexcelatexcel.com/excel-tips-tricks/create-a-timestamp-in-excel-with-formulas/

I would prefer a VBA solution that responds to the update as an event and converts the result of NOW() into a string (or simply writes the time itself).

The get the time from NOW(), just extract the fractional part
= MOD( NOW(), 1 )

I have just tested the first formula approach (it is not something I have ever tried before) and it appears to work quite well.
= IF( input<>"", IF( time="", MOD( NOW(), 1 ), time ), "" )
Thanks for your kind response Peter.

As stated before, the command (last line) does produce the required result, but upon refresh the time stamp changes to the current time. For e.g. if the signal time was 9:15 hrs in the morning and if the current time is 14:30 hours, it displays 14:30 hrs and not 9:15 hrs when actually the signal came. I am not too familiar with VBA, which I regret, but will checking with a few who knows VBA on the same lines that you mentioned.

Thanks,
JT

Peter Bartholomew

Well-Known Member
I now realise that the original formula you posted was the same as the one I suggested but the inequality signs seem to have gone missing (possibly treated as a null tag). Without a sample workbook, I am neither sure about the layout nor the update mechanism. However the attached workbook may provide a start.

Attachments

• 11 KB Views: 8

GraH - Guido

Well-Known Member
Since you say any column after F, should contain the timestamp, I believe this might work (without testing, since no sample data uploaded).
[G2]= IF(AND(A2="",B2<>""),if(G2<>"",G2,NOW()),B2)

As referenced by Peter, iterative calculation should be on...

New Member
I now realise that the original formula you posted was the same as the one I suggested but the inequality signs seem to have gone missing (possibly treated as a null tag). Without a sample workbook, I am neither sure about the layout nor the update mechanism. However the attached workbook may provide a start.
Thank you Peter. I checked the Sample sheet provided, but the time keeps changing every minute. Attaching a few images which I hope will help you understand what I mean.

Attachments

• 5 KB Views: 4
• 4.8 KB Views: 1
• 4.7 KB Views: 2
• 13.8 KB Views: 1

New Member
Since you say any column after F, should contain the timestamp, I believe this might work (without testing, since no sample data uploaded).
[G2]= IF(AND(A2="",B2<>""),if(G2<>"",G2,NOW()),B2)

As referenced by Peter, iterative calculation should be on...

Thank you Grah - Guido for assisting me on this.
Can you clarify me which are the columns A2, B2 and G2 references. The reason I ask you this is, because, assuming that my signal Column where I get Buy/Sell is Column F, and needing the Time Stamp in Column G, there are only two columns that needs to be referenced, but in your formula above I see 3 columns being referenced and I am a bit confused (may be ignorant too ).

I dont mind attaching the sample sheet, but the sample sheet takes data from Sharekhan and you may not be able to fetch this data (live/RT) as these relates to Indian stock market. I am attaching a snapshot of the excel sheet.

Attachments

• 88.8 KB Views: 3

New Member
Thank you Peter. I checked the Sample sheet provided, but the time keeps changing every minute. Attaching a few images which I hope will help you understand what I mean.
Hi Peter, Attaching the snapshot of the sheet.

As you may see, I have the signals appearing in Column F (Buy or Sell), and the whole sheet refreshes every one minute interval. I need the trigger time in Column T, whenever there is a Buy or Sell, OR a Buy changing to Sell OR Sell changing to Buy.
The Buy or Sell in Column G is dynamic and will continue to change based on the price conditions (refreshed data).

Regards
JT

Attachments

• 88.8 KB Views: 7

GraH - Guido

Well-Known Member
there are only two columns that needs to be referenced, but in your formula above I see 3 columns being referenced and I am a bit confused (may be ignorant too ).
Because for the timestamp to work, if the hour is filled in (reference to G-column), you should not update it else depending on the data in he other columns you need to fill it or leave it empty. Yours does keep on updating, right?
With iterative calculations on, you can use a circular reference where the formula refers to the cell it actually sits in. Meaning once it is filled with a value, it will not update regardless of what happens in the other cells.

Peter Bartholomew

Well-Known Member
Attaching a few images …
What the images show is that the cell E6 containing the time formula and conditional formatting has been overwritten. Only cell C6 should be changed (using the validation dropdown).

Cell G6 is meant to update on every calculation cycle. It is used to determine the time elapsed since the signal in order to control the conditional formatting.
The time threshold used for the formatting change is in cell C16.

I was thinking that you hadn't set the maximum iterations to 1 but I see that is not the case.

If you still wish to evaluate the sample spreadsheet I posted, you will need to download afresh.

Peter Bartholomew

Well-Known Member
This post is for my curiosity. How does the 'Streaming Stock Watch' sheet work? What causes it to refresh?

I note that this discussion has moved to VBA Macros so at least one of the moderators believes the best solution to your problem lies in code development and not spreadsheet formulas.

New Member
Thank you Gentlemen.

Peter : Streaming Stock Watch is a live streaming from Sharekhan (Broker) using the Excel RTD feature. This Streaming sheet is on Sheet 1, while we are working on Sheet 2 which is referenced (Data) from Sheet 1 by Vlookup. I downloaded the excel sheet, chose Buy and I see the time change and then the time that has elapsed. So waited for a few minutes, and then chose Sell, but the Column D and Second line under Column G continued to reflect the same time when I chose Buy and didnt change when I chose Sell. Snapshot attached.

As you mentioned, may be this does require a VB code, as there was another sheet on the internet who probably had a different strategy and had successfully implemented it using VB Code. I did check with him, to share the code, but sadly no response. I am also attaching his snapshot (I dont have his sheet which is a strategy based and people generally dont share just like that ). Here you can see he has the trigger time for Signal, Target and also Stop Loss, as and as you may see the time doesnt change.

Now that you mentioned about a VB code, would you or Grah can take this up or should I reach out to someone else in this forum.

Appreciate both of your time sincerely.

Warm Regards
JT

Attachments

• 10.9 KB Views: 8
• 10.9 KB Views: 5
• 219.3 KB Views: 7

New Member
Thank you Gentlemen.

Peter : Streaming Stock Watch is a live streaming from Sharekhan (Broker) using the Excel RTD feature. This Streaming sheet is on Sheet 1, while we are working on Sheet 2 which is referenced (Data) from Sheet 1 by Vlookup. I downloaded the excel sheet, chose Buy and I see the time change and then the time that has elapsed. So waited for a few minutes, and then chose Sell, but the Column D and Second line under Column G continued to reflect the same time when I chose Buy and didnt change when I chose Sell. Snapshot attached.

As you mentioned, may be this does require a VB code, as there was another sheet on the internet who probably had a different strategy and had successfully implemented it using VB Code. I did check with him, to share the code, but sadly no response. I am also attaching his snapshot (I dont have his sheet which is a strategy based and people generally dont share just like that ). Here you can see he has the trigger time for Signal, Target and also Stop Loss, as and as you may see the time doesnt change.

And this is precisely I am looking for.

Now that you mentioned about a VB code, would you or Grah can take this up or should I reach out to someone else in this forum.

Appreciate both of your time sincerely.

Warm Regards
JT

Peter Bartholomew

Well-Known Member
I have just looked at the RTD help page.
"The RTD COM automation add-in must be created and registered on a local computer"
If you are operating at this level then perhaps we should settle for you advising me and not the other way round !

OK. I think I now get it. It is Sharekhan (Broker) that has developed the automation add-in?

I still see no reason why the approach that you found on the internet and both Guido and I have been trying to help with shouldn't work.

Equally it should be straightforward to implement a VBA event handler to pick up change events created by the RDT function (provided they exist) and update the time if it is blank or clear it if Buy/Sell has disappeared.

Last edited:

Peter Bartholomew

Well-Known Member
This is the same demo but relies upon a VBA macro to break the circular reference by testing the time (named ref) and only updates it if it is empty.

Attachments

• 20.7 KB Views: 5