Summarizing Data Over Arbitrary Time Frames

I have an Access database which contains the daily opening, closing, high, 
and low prices for a selection of stocks. The main table in this database 
is structured as follows:

Table: Price
=======================================
Field1: PriceID autonumber primary key
Field2: PriceDate date
Field3: Symbol text(3)
Field4: Open currency
Field5: Close currency
Field6: High currency
Field7: Low currency

I often want to know what the highest and lowest prices were for one of  these stocks for each month, over the course of the past 6 months. The  output I want looks like:

Symbol	Month	Low	High
ANZ	September	19.71	25.42
ANZ	October	20.72	25.59
ANZ	November	20.98	25.99
ANZ	December	21.22	26.03
ANZ	January	21.45	26.11
ANZ	February	21.14	24.12

(I've made up the above figures, so don't take any of the sample data  presented here as serious investment advice).

In Access, I can group data by month using the Month() function:

SELECT Symbol, Month(PriceDate) AS Month, MIN(Low), MAX(High)
FROM Price
WHERE Symbol='ANZ' AND PriceDate>=#01/Sep/2000#
GROUP BY Symbol, Month(PriceDate)

This query will output the Month as a number between 1 and 12. I can  output the name of the month instead, by using another Access-specific  function:

SELECT Symbol, Format$(Month(PriceDate),"mmmm") AS Month, MIN(Low), 
MAX(High)
FROM Price
WHERE Symbol='ANZ' AND PriceDate>=#01/Sep/2000#
GROUP BY Symbol, Format$(Month(PriceDate),"mmmm")

Note that when grouping on a calculated field, the whole calculation must  be repeated in the GROUP BY clause.

The same query could be rewritten for SQL server using the DATEPART and  CONVERT functions.

"Isn't this tip supposed to be about arbitrary time frames?"

Yeah, I'm getting to that. Access has a Year() and a Month() function, but  it doesn't have a Week() function. The following will return the Monday  that falls in the same week as PriceDate:

CDate(((PriceDate-2)\7)*7+2)

I've cheated a little here, because Access performs an automatic type  conversion from date to number.

Access uses a date system which starts at midnight, on the morning of  Saturday 30 December, 1899. That is, 1 is a Sunday, 2 is a Monday, 3 is a  Tuesday, etc.

Note that the back-slash is the integer division operator in Access. The  simpler code fragment:

(PriceDate) \ 7

will return the number of whole weeks that have passed since 30 December,  1899, and

CDate(((PriceDate)\7)*7)

will return the Saturday on or before PriceDate. I'm looking at Monday,  because it is usually the first trading day of the week. The following  query will return the weekly High and Low prices for ANZ since September:

SELECT Symbol, Format$(CDate(((PriceDate-2)\7)*7+2),"dd/mm/yyyy") AS Month, 
MIN(Low), MAX(High)
FROM Price
WHERE Symbol='ANZ' AND PriceDate>=#01/Sep/2000#
GROUP BY Symbol, Format$(CDate(((PriceDate-2)\7)*7+2),"dd/mm/yyyy")

I hope you can see where I'm headed with this. All the 7's in the above  query could be replaced with any number you could name. If I want to  extract the price ranges over 3 day or 12 day periods, I could do that  really easily. Change the 2's in the above query, and I can change the  date that my 3 day or 12 day periods start on too.

I've kept today's tip simple, by not mentioning how to get the opening and closing stock prices for the week. That's a simpler task in Access than in SQL server, but I'll save that for a future tip.