Hi Mike,
What about holidays?
What follows is an old response to "add business days":
you'll have to create a table where you
enter holidays that won't count, for example,
table "Holidays" like:
ID HolDate HolName
1 7/4/2005 July 4th
Public Function fAddBusinessDay(pStart As Date, pAdd As Integer)
'Adds the proper Business day skipping holidays and weekends
'adapted from code by Arvin Meyer 05/26/98
On Error GoTo Err_fAddBusinessDay
Do While pAdd > 0
pStart = pStart + 1
If Weekday(pStart) <> 1 And Weekday(pStart) <> 7 Then
'not Sunday nor Saturday
If DCount("*", "Holidays", "[HolDate]=#" & pStart & "#") = 0 Then
'not holiday
pAdd = pAdd - 1
End If
End If
Loop
fAddBusinessDay = pStart
Exit_fAddBusinessDay:
Exit Function
Err_fAddBusinessDay:
MsgBox Err.Description
Resume Exit_fAddBusinessDay
End Function
////////////////////
to subtract business days, I think you just need to
change a few things:
Public Function fSubtractBusinessDay(pStart As Date, pSubtract As Integer)
'Subtracts the proper Business day skipping holidays and weekends
'adapted from code by Arvin Meyer 05/26/98
On Error GoTo Err_fSubtractBusinessDay
Do While pSubtract > 0
pStart = pStart - 1
If Weekday(pStart) <> 1 And Weekday(pStart) <> 7 Then
'not Sunday nor Saturday
If DCount("*", "Holidays", "[HolDate]=#" & pStart & "#") = 0 Then
'not holiday
pSubtract = pSubtract - 1
End If
End If
Loop
fSubtractBusinessDay = pStart
Exit_fSubtractBusinessDay:
Exit Function
Err_fSubtractBusinessDay:
MsgBox Err.Description
Resume Exit_fSubtractBusinessDay
End Function
tests in Immediate window:
?fsubtractbusinessday( #7/6/05#,5)
6/28/2005
?fsubtractbusinessday( #7/5/05#,1)
7/1/2005
?fsubtractbusinessday( #7/11/05#,1)
7/8/2005
save above function in a module (say "modUtilities")
Then...I might create a query that includes all
relevant fields from your table plus computes
1DayBefore and 5DaysBefore dates
(say "Q"):
SELECT
StockID,
StockDate,
fsubtractbusinessday([StockDate],1) As 1DayBefore,
fsubtractbusinessday([StockDate],5) As 5DaysBefore,
ClosingPrice
FROM yourtable;
then join this stored query ("Q") to 2 instances
of your table in another query
(alias one instance say "1Day"
and the other say "5Day").
Besides other pertinent fields that will
need to be joined,
Left join Q.1DayBefore to 1Day.StockDate
and
Left join Q.5Day.5DaysBefore to 5Day.StockDate
then you will end up with
Q.ClosingPrice,
Nz(1Day.ClosingPrice,0),
Nz(5DayClosingPrice,0)
for each record (if table is as simple as I assumed)
so you can compute your rate of return.
good luck,
gary
