Time Series Data in bins (Sumproduct?)

[Guest]

I have the trade and quotes from the New York Stock Exchange and need the average price and average volume for every five minute interval of a trading day.

for example the SUM of volumes/number of trades for the time period 9:35:00 AM to 9:40:00 AM

Typical wksht looks lik

9:05:01 T 100 43.7

time symbol size price

 

[Frank Kabel]

Hi
I assume you also want this only for the specific trading symbol. If
yes try the following (though not fully tested):
Assumptions:
- columns A store your data
- column E will get the average price
- column F the average volume
- it will put the average values for the last 5 minutes in each row

Enter the following in E1
=SUMPRODUCT(($A$1:$A$1000>=TIME(HOUR(A1),MINUTE(A1)-5,SECOND(A1)))*($A$
1:$A$1000<=A1)*($B$1:$B$1000=B1),$C$1:$C$1000)/SUMPRODUCT(($A$1:$A$1000

=TIME(HOUR(A1),MINUTE(A1)-5,SECOND(A1)))*($A$1:$A$1000<=A1)*($B$1:$B$1 000=B1))

F1:
=SUMPRODUCT(($A$1:$A$1000>=TIME(HOUR(A1),MINUTE(A1)-5,SECOND(A1)))*($A$
1:$A$1000<=A1)*($B$1:$B$1000=B1),$D$1:$D$1000)/SUMPRODUCT(($A$1:$A$1000
=TIME(HOUR(A1),MINUTE(A1)-5,SECOND(A1)))*($A$1:$A$1000<=A1)*($B$1:$B$1

000=B1))

copy both formulas down. This way you would get a running average in
each row

 

[Frank Kabel]

Hi
you have lost me. What are 'bins'?
You may explain how you want your output created. How do you want to
show the average values?

 

[Guest]

Sorry, I am horrible at explaining things.There are 78 "5-minute" periods from 9:30:00 AM to 4:00:00 PM. I need the average for each one of the 78 bins of time. Alternately, the first observation is the average Volume from 9:30:00 AM to 9:34:59 PM. The next observation will be the average volume from 9:35:00 AM to 9:39:59 PM.

Maybe this helps . . .

The data:

A B C D
1 9:30:00 T 100 43.50
2 9:31:00 T 200 44.00
3 9:34:00 T 100 43.50
4 9:34:59 T 200 43.75

5 9:35:00 T 100 43.50
6 9:36:02 T 200 44.00
7 9:37:31 T 100 43.50
8 9:38:40 T 100 43.50
9 9:39:59 T 100 43.50

10 9:39:59 T 100 43.50

The result should be:
A:Bin B:Number of Trades C:Total Volume D:Average Volume E:Average Price
1 9:30:00 4 600 = 600/4 = 150 43.6875
2 9:35:00 5 600 = 600/5 = 120 43.60
. . .

The average (demeaned) of a discrete 5 minute time period

 

[Frank Kabel]

hi
now we are getting somethere
I assume that you have your data on a separate sheet called 'data'.
Enter the following formulas in your result sheet:
B1:
=SUMPRODUCT(--('data'!$A$1:$A$1000>=A1),--('data'!$A$1:$A$1000<A2))

C1:
=SUMPRODUCT(--('data'!$A$1:$A$1000>=A1),--('data'!$A$1:$A$1000<A2),'dat
a'!$C$1:$C$1000)

D1:
=C1/B1

E1:
=SUMPRODUCT(--('data'!$A$1:$A$1000>=A1),--('data'!$A$1:$A$1000<A2),'dat
a'!$D$1:$D$1000)/B1

 

[Guest]

My first born shall now carry the name Frank.

 

[Frank Kabel]

Hi
thanks for this feedback <vbg>.