multiplication with increments of realtime clock

Copper Contributor

Hello Team , 

Need your help on the below requirement , i am planning and looking for trading data collection below requirement is one among them 

 

COLUMN A : had the volume traded of a stock in real time , meaning can be 10 @ 9.01 am , 564@9.05, its basically increment with relevance with time. (getting this data for market data readers)

 

Data i have is average volume per minute ,example : average value per minute is 50 .

I need for 9.01 am it should multiple 50*1 for 9.02 it should 50*2 so on , i have to do this till 3.30 PM 

or it can add based on minute increment.        (multiple or do addition with increments if time) 

 

COLUMN B : i need for formula for above

 

 

4 Replies

Hi,

 

Could you please provide small sample with source data and desired result? Part of questions:

What is exact format for the text in column A ? It's like "volume @ time" or "volume@time", 9.05 means 9Hrs 5min or 9 and 5/100 Hrs, etc

- where the magic 50 is kept

- where is the time with which sync values in column B

- what exactly you'd like to calculate in B (multiplying average on number of minutes fives cumulative sum; which increments to take)

Firstly thanks for the response 

 

Trading time : 09:00 to 15:30

 

COLUMN A : this is the data i get from the Data brokers which will give me cumulative data and will be changing based on interval for example , this value is to compare with value from Column B.

Column B is what i need your help 

example if i see data at 09:01 as 100 and see 09:02 as 200 which means by 9:02 which is 2 minutes from trading the volume is 200, so if i see at 10: 00 as 2000 which means after 1 hour of trading the volume is 1000 quantities.

 

i don't get minute by minute data and hence i am trying to that in excel which is column B and compare with Volume A ( meaning i don't get what happened between 9:06 and 9:07 when i see the data at 09:10 )

 

Column B : this magic 50 is number i derived for different sheet , this is basically  volume average per minute , take any number of your choice. 

 

lets say for example

 

at 09:01 AM  it should multiple 50 by one time because 1 minute(after trading started)  for example which is 50*1 = 50 , this value i will compare with Volume A at 09:01 am and create an alert if it is 10 times more , meaning alert me when Column A (volume traded getting from data broker) is greater than 500 (10 times)

 

at 09:02 AM  it should multiple 50 by two time because 2 minute(after trading started)  for example which is 50*2 = 100 , this value i will compare with Volume A at 09:02 am and create an alert if it is 10 times more , meaning alert me when Column A (volume traded getting from data broker) is greater than 1000(10 times)

 

Note 10:00 AM should be 60 minute , similarly 11:00 120 minute and so on till 15:30 which is 390 Minute from trading started which is 09:00 AM 

 

at 15:30 AM  it should multiple 50 by 390 time because 390 minute(after trading started)  for example which is 50*390 = 19500 , this value i will compare with Volume A at 15:30 PM and create an alert if it is 10 times more , meaning alert me when Column A (volume traded getting from data broker) is greater than 195000(10 times)

 

I have attached screenshot which should explain most of the things

 

 

If I understood that correctly

image.png

you may use 

=IF(ISBLANK(A2),"",(A2-$F$2)*24*60*$F$1)

where you have time from left most column.

Is the Time getting incremented every minute ? 

in real time you only have only ROW , where Data column be changing every minute and so do A2 

 

our B2 must be multiplying in increments of 50 for every minute change 

 

minute_increment.png

 

I really appreciate your efforts on this , but i dont see the time incrementing real time