JediTrader
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.
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.