- 513K Members
- 2,307 Online
- 611K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: multiplication with increments of realtime clock

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-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

Labels:

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-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

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft