Data analysis question

[craig8125]

I have a spread sheet with shares data in, using a 30 min period. ie
Open, High, Low, Close for each 30 min period in a day. columns are laid out in like this

Date Time Open High Low Close

<table style="border-collapse: collapse;" width="469" border="0" cellpadding="0" cellspacing="0" height="160"><col style="width: 59pt;" width="79"> <col style="width: 48pt;" width="64" span="5"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt; width: 59pt;" width="79" align="right" height="20">13-Mar-07</td> <td class="xl65" style="width: 48pt;" width="64" align="right">22:30</td> <td style="width: 48pt;" width="64" align="right">139650</td> <td style="width: 48pt;" width="64" align="right">139875</td> <td style="width: 48pt;" width="64" align="right">139450</td> <td style="width: 48pt;" width="64" align="right">139500</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">13-Mar-07</td> <td class="xl65" align="right">23:00</td> <td align="right">139500</td> <td align="right">139875</td> <td align="right">139150</td> <td align="right">139625</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">13-Mar-07</td> <td class="xl65" align="right">23:30</td> <td align="right">139625</td> <td align="right">139650</td> <td align="right">139000</td> <td align="right">139050</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">14-Mar-07</td> <td class="xl65" align="right">0:00</td> <td align="right">139050</td> <td align="right">139225</td> <td align="right">138900</td> <td align="right">139200</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">14-Mar-07</td> <td class="xl65" align="right">0:30</td> <td align="right">139225</td> <td align="right">139225</td> <td align="right">139150</td> <td align="right">139200</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">14-Mar-07</td> <td class="xl65" align="right">1:00</td> <td align="right">139200</td> <td align="right">139275</td> <td align="right">139150</td> <td align="right">139175</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">14-Mar-07</td> <td class="xl65" align="right">2:00</td> <td align="right">139200</td> <td align="right">139300</td> <td align="right">139100</td> <td align="right">139175</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" align="right" height="20">14-Mar-07</td> <td class="xl65" align="right">2:30</td> <td align="right">139175</td> <td align="right">139225</td> <td align="right">139100</td> <td align="right">139150</td> </tr> </tbody></table>
There are in most (but not all, depending on the day) cases 24 entries each with the same date. I want to extract various statistics from this data.

ie. I want to count how many times the high of the day was made in which period, the same for the low etc.

can anyone assist please

 

[super]

try using countifs()

 

[craig8125]

Thanks Super

but not sure how I can use COUNTIFS in this instance in need to extract the high and low time of the each day. Maybe I will need to do this in VBA (GULP) but would rather do this by formula if posible

 

[super]

ok not sure exactly what you want but for example if you want how many times the stock reached the high on March 13 you could write

=COUNTIFS(A3:A10,DATE(2007,3,13),D3:D10,MAX(D3:D5))

 

[craig8125]

thanks Super

I see what you mean but I need to make a table which shows the number of times the stock made a high for the day during the period 08:00, 08:30, 09:00 etc etc

so basically I want to look through all the data 35000 entries, usually 47 per day. The table needs to show for example that in the period 08:00 the stock made a high 2500 times out of the 35000 time periods.

then I need to do this for all the periods listed because I am looking at statistical times of highs and lows.

ie.

Time High Low
08:00 2500 1450
08:30 1300 1790

etc etc.

Once I have set up the table and formula, I can then cut and past the data from any stock

I hope I explained this OK.

 

[super]

something similar to this perhaps

=COUNTIFS(D6:D13,SUMIFS(D6:D13,A6:A13,A6 )/47,B6:B13,"11:00:00 PM")

SUMIFS(D6:D13,A6:A13,A6 )/47

will calculate the high for the day

 

[craig8125]

Thanks Again Super,

but the problem is there are sometimes different length days in the data, like the with the e-mini S&P500 it starts trading on sunday afternoon and finishes on friday afternoon so the sunday session is only 32 periods.

so I really need a way to identify the beginning and end of each day and extracting the data from that and doing this for each day.

regards
Craig

 

[Andrew Fergus]

Hi

How about something like this:

   A          B      C      D      E      F      G    H    I     J     K     
 1 Date       Time   Open   High   Low    Close                              
 2 13/03/2007 22:30  139650 139875 139450 139500                             
 3 13/03/2007 23:00  139500 139875 139150 139625                             
 4 13/03/2007 23:30  139625 139650 139000 139050                             
 5 14/03/2007 0:00   139050 139225 138900 139225                             
 6 14/03/2007 0:30   139225 139225 139150 139200                             
 7 14/03/2007 1:00   139200 139275 139150 139175                             
 8 14/03/2007 1:30   139175 139300 139100 139175                             
 9 14/03/2007 2:00   139175 139225 139100 139150                             
10                                                                           
11                                                                           
12 Date       High   0:00   0:30   1:00   1:30   2:00 etc. 22:30 23:00 23:30 
13 12/03/2007 0      0      0      0      0      0    0    0     0     0     
14 13/03/2007 139875 0      0      0      0      0    0    1     1     0     
15 14/03/2007 139300 0      0      0      1      0    0    0     0     0     
16 15/03/2007 0      0      0      0      0      0    0    0     0     0     
17                                                                           
18                                                                           
19                                                                           
20 Date       Low    0:00   0:30   1:00   1:30   2:00 etc. 22:30 23:00 23:30 
21 12/03/2007 0      0      0      0      0      0    0    0     0     0     
22 13/03/2007 139000 0      0      0      0      0    0    0     0     1     
23 14/03/2007 138900 1      0      0      0      0    0    0     0     0     
24 15/03/2007 0      0      0      0      0      0    0    0     0     0     
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE   FORMULA (1st cell)
B13:B16 {=MAX(IF($A$2:$A$9=A13,$D$2:$D$9,0))}
B21:B24 {=IF(COUNTIF($A$2:$A$9,A21)>0,MIN(IF($A$2:$A$9=A21,$E$2:$E$9,9^9)),0)}
C13:K16 =SUMPRODUCT(--($A$2:$A$9=$A13),--($B$2:$B$9=C$12),--($D$2:$D$9=$B13))
C21:K24 =SUMPRODUCT(--($A$2:$A$9=$A21),--($B$2:$B$9=C$20),--($E$2:$E$9=$B21))
{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down
[Table-It] version 09 by Erik Van Geit

I haven't included the totals for each time period but I figure you can add the totals and get the percentage figures you want. What this provides is a grid of days and times and counts the number of times the high was reached for each day, grouped by time period.

Please note the array formulas - there may be an easier way of doing this in XL2007 without using an array formula.

I trust this helps.

Andrew

 

[craig8125]

Thanks just logged in again after a few days, I wil take a look and see if I can get it to work..

Thanks Again