Forum Discussion

Madhukumar's avatar
Madhukumar
Copper Contributor
Dec 31, 2018

multiplication with increments of realtime clock

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

 

 

excel

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 31, 2018

    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)

    • Madhukumar's avatar
      Madhukumar
      Copper Contributor
      Dec 31, 2018

      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

       

       

Resources