comparing contracts in the current minute with the previous

rpwagner · Mar 1st, 2002, 06:01 AM

I trade stocks for a living. I am trying to build a formula to do the following: Assume for example, that during the previous minute 12:47:00 to 12:47:59, there were a total 200 stocks sold (actually contracts). I want to be able to tell at the end of the first 15 seconds in the current time period (12:48:00 to 12:48:15), how many were sold and compare that with the previous minute (12:47:00 to 12:47:59 (total was 200). if for example, there were 200 sold the previouse minute , and within the first 15 seconds of the current minute, there were 800 sold, that tells me very quickly something big is happening (volume increasing dramitacally as 800 in the current 15 seconds is 4 times more than the entire previous minute!) and I can react quicker. I then would like to compare the first 30 seconds (12:48:00 to 12:48:30) to the previous minute etc, until i have compared the 4 quarters of the current minute with the previous minutes. I want an alarm to be triggured (in this example 4 times more in the first 15 seconds than last. I already have the data coming in real time into an existing spread sheet and need the formulase, vba etc in other cells to act on the time and nr of contracts already in the worksheet. Any help would greatly be appreciated.

Don C · Mar 1st, 2002, 06:53 AM

Would conditional formatting work for you?

Don't know how you have your spreadsheet set up, but if the previous minute counts are in a specific cell and the current counts are cumulating in a specific cell, you could have the current minute cell (or another cell for that matter) turn to red if the current cell is larger than the previous minute cell. The formula for the condition could be a simple larger than (B>A) or a multiple ((B>(4*A)).

rpwagner · Mar 3rd, 2002, 05:26 AM

yes, that would help and thanks. However, I need to know by 15 second intervals. Ideally if I knew that this 15 second interval was larger in number than the previous corresponding 15 second interval it would tip me off that something is happening very quickly...but I will definetly put in your suggestion

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 1st, 2002, 06:01 AM	#1
rpwagner New Member Join Date: Feb 2002 Location: Robert P. Wagner Posts: 22	I trade stocks for a living. I am trying to build a formula to do the following: Assume for example, that during the previous minute 12:47:00 to 12:47:59, there were a total 200 stocks sold (actually contracts). I want to be able to tell at the end of the first 15 seconds in the current time period (12:48:00 to 12:48:15), how many were sold and compare that with the previous minute (12:47:00 to 12:47:59 (total was 200). if for example, there were 200 sold the previouse minute , and within the first 15 seconds of the current minute, there were 800 sold, that tells me very quickly something big is happening (volume increasing dramitacally as 800 in the current 15 seconds is 4 times more than the entire previous minute!) and I can react quicker. I then would like to compare the first 30 seconds (12:48:00 to 12:48:30) to the previous minute etc, until i have compared the 4 quarters of the current minute with the previous minutes. I want an alarm to be triggured (in this example 4 times more in the first 15 seconds than last. I already have the data coming in real time into an existing spread sheet and need the formulase, vba etc in other cells to act on the time and nr of contracts already in the worksheet. Any help would greatly be appreciated.

Mar 1st, 2002, 06:53 AM	#2
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	Would conditional formatting work for you? Don't know how you have your spreadsheet set up, but if the previous minute counts are in a specific cell and the current counts are cumulating in a specific cell, you could have the current minute cell (or another cell for that matter) turn to red if the current cell is larger than the previous minute cell. The formula for the condition could be a simple larger than (B>A) or a multiple ((B>(4*A)).

Mar 3rd, 2002, 05:26 AM	#3
rpwagner New Member Join Date: Feb 2002 Location: Robert P. Wagner Posts: 22	yes, that would help and thanks. However, I need to know by 15 second intervals. Ideally if I knew that this 15 second interval was larger in number than the previous corresponding 15 second interval it would tip me off that something is happening very quickly...but I will definetly put in your suggestion