Dec 31 2018 01:36 AM
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
Dec 31 2018 04:30 AM
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)
Dec 31 2018 05:05 AM
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
Dec 31 2018 05:31 AM
If I understood that correctly
you may use
=IF(ISBLANK(A2),"",(A2-$F$2)*24*60*$F$1)
where you have time from left most column.
Dec 31 2018 06:17 AM
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
I really appreciate your efforts on this , but i dont see the time incrementing real time