Business Days Between Two Dates?
in [General]
|
From: Tom van Stiphout on 15 May 2010 00:57 On Fri, 14 May 2010 19:14:23 -0400, "(PeteCresswell)" <x(a)y.Invalid> wrote: How slow? FMS Inc has a BusinessDayAdd and BusinessDayDiff loosely based on the same algorithm, and I have never had a problem. Probably never had to run it over several years. Do bonds really care about business days? That surprises me. Yes, you could pre-populate a table overnight every night. Or perhaps even populate a table with offsets on a more permanent basis. Go for it! Maybe you even want to contribute it back to the greater good. Just be careful about the oleaut32 bug reported here: http://support.microsoft.com/kb/200299 -Tom. Microsoft Access MVP >Business days between two dates has got to involve a tblHoliday. > >Right now I've got such a table - mostly populated with bank >holidays and I compute biz days between two dates by iterating >through the days one-by-one: if it's a Saturday or Sunday it's >not a biz day.... otherwise I do a table lookup - no hit, then >it's a biz day. > >But this is *really* slow for processing a record set of, say, >2000 records. > >I'm thinking a faster approach would involve re-creating a table >of business days from some begin date to some end date each time >tblHolidays is updated and then doing some SQL magic to bang a >recordset up against that table and somehow bulk-calculate, for >instance, the number of business days between a bond's maturity >date and today's date... or whether or not a bond matures within >60 business days. > >Has anybody come up with such an approach?
From: Bob Quintal on 15 May 2010 07:58 "(PeteCresswell)" <x(a)y.Invalid> wrote in news:7ulru5htgd47ait4t2ot7c86gblr0cprcu(a)4ax.com: > Business days between two dates has got to involve a tblHoliday. > > Right now I've got such a table - mostly populated with bank > holidays and I compute biz days between two dates by iterating > through the days one-by-one: if it's a Saturday or Sunday it's > not a biz day.... otherwise I do a table lookup - no hit, then > it's a biz day. > > But this is *really* slow for processing a record set of, say, > 2000 records. > > I'm thinking a faster approach would involve re-creating a table > of business days from some begin date to some end date each time > tblHolidays is updated and then doing some SQL magic to bang a > recordset up against that table and somehow bulk-calculate, for > instance, the number of business days between a bond's maturity > date and today's date... or whether or not a bond matures within > 60 business days. > > Has anybody come up with such an approach? I calculate the business interval as follows. weeks = datediff("ww",dtFrom,dtTo,vbmonday) then add the days in the zeroeth week, subtract the days after dtTo from the final week, and subtract the dCount() of the number of days in tblHolidays where holiday between dtFrom and dtTo this means one dCount per calculation, as opposed to, if I understand you, a dLookup for each day in the interval.
From: Arvin Meyer [MVP] on 15 May 2010 11:00 The case of bond maturity can run almosrt instantly since it doesn't need to run either of those functions or the one at: http://www.datastrat.com/Code/GetBusinessDay.txt for every row. For bond maturity in say 60 business days, use the above link to add 60 days to the current day, then store that date in a global variable. In a query, use a function to compare that variable to the Bond Maturity Date. Since it does need to run the function thousands of times, it's very fast that way. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message news:e1asu59leelv0mdfog6ee1bdddsi0at27b(a)4ax.com... > On Fri, 14 May 2010 19:14:23 -0400, "(PeteCresswell)" <x(a)y.Invalid> > wrote: > > How slow? FMS Inc has a BusinessDayAdd and BusinessDayDiff loosely > based on the same algorithm, and I have never had a problem. Probably > never had to run it over several years. > > Do bonds really care about business days? That surprises me. > > Yes, you could pre-populate a table overnight every night. Or perhaps > even populate a table with offsets on a more permanent basis. Go for > it! Maybe you even want to contribute it back to the greater good. > Just be careful about the oleaut32 bug reported here: > http://support.microsoft.com/kb/200299 > > -Tom. > Microsoft Access MVP > > >>Business days between two dates has got to involve a tblHoliday. >> >>Right now I've got such a table - mostly populated with bank >>holidays and I compute biz days between two dates by iterating >>through the days one-by-one: if it's a Saturday or Sunday it's >>not a biz day.... otherwise I do a table lookup - no hit, then >>it's a biz day. >> >>But this is *really* slow for processing a record set of, say, >>2000 records. >> >>I'm thinking a faster approach would involve re-creating a table >>of business days from some begin date to some end date each time >>tblHolidays is updated and then doing some SQL magic to bang a >>recordset up against that table and somehow bulk-calculate, for >>instance, the number of business days between a bond's maturity >>date and today's date... or whether or not a bond matures within >>60 business days. >> >>Has anybody come up with such an approach?
From: KenSheridan via AccessMonster.com on 15 May 2010 12:36 You can either use a suitable function, which should not be unduly slow, or by means of a JOIN. For the former I use the following function, which allows for different sets of holiday dates for different countries as in the UK each constituent country has differing public holidays, and the Republic of Ireland also differs. It also gives the option of including or excluding public holidays from the count: Public Function WorkDaysDiff(varLastDate As Variant, _ varFirstDate As Variant, _ strCountry As String, _ Optional blnExcludePubHols As Boolean = False) As Variant Const conSATURDAY As Integer = 6 Const conSUNDAY As Integer = 7 Dim lngDaysDiff As Long, lngWeekendDays As Long Dim intPubHols As Integer If IsNull(varLastDate) Or IsNull(varFirstDate) Then Exit Function End If ' if first date is Sat or Sun start on following Monday Select Case WeekDay(varFirstDate, vbMonday) Case conSATURDAY varFirstDate = varFirstDate + 2 Case conSUNDAY varFirstDate = varFirstDate + 1 End Select ' if last date is Sat or Sun finish on following Monday Select Case WeekDay(varLastDate, vbMonday) Case conSATURDAY varLastDate = varLastDate + 2 Case conSUNDAY varLastDate = varLastDate + 1 End Select ' get total date difference in days lngDaysDiff = DateDiff("d", varFirstDate, varLastDate) ' get date difference in weeks and multiply by 2 ' to get number of weekend days lngWeekendDays = DateDiff("ww", varFirstDate, varLastDate, vbMonday) * 2 ' subtract number of weekend days from total date difference ' to return number of working days WorkDaysDiff = lngDaysDiff - lngWeekendDays ' exclude public holidays if required If blnExcludePubHols Then intPubHols = DCount("*", "qryPubHols", "HolDate Between #" _ & Format(varFirstDate, "mm/dd/yy") & "# And #" & _ Format(varLastDate - 1, "mm/dd/yy") & "#" & _ " And Country = " & Chr(34) & strCountry & Chr(34)) WorkDaysDiff = WorkDaysDiff - intPubHols End If End Function To do it with a JOIN you don't need to recreate a table each time; simply create a calendar table with dates over a 10 or 20 year period say. You can either include all dates and exclude the weekends from the count by calling the Weekday function in a query, or you can just include Monday to Friday dates. You can then either add a Boolean IsHoliday column to the table, or you can join it to a Holidays table in a query to exclude the holidays from the count. The following function, of which DAO and ADO versions are included below, can be used to create a calendar table, and allows for selected days of the week only to be included by passing a list of days into the function as the final parameter array argument. With ADO: Public Function MakeCalendar(strTable As String, _ dtmStart As Date, _ dtmEnd As Date, _ ParamArray varDays() As Variant) ' Accepts: Name of calendar table to be created: String. ' Start date for calendar: DateTime. ' End date for calendar: DateTime. ' Days of week to be included in calendar ' as value list, e,g 2,3,4,5,6 for Mon-Fri ' (use 0 to include all days of week) Dim cmd As ADODB.Command Dim cat As New ADOX.Catalog Dim tbl As ADOX.Table Dim strSQL As String Dim dtmDate As Date Dim varDay As Variant Dim lngDayNum As Long Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText Set cat = New Catalog cat.ActiveConnection = CurrentProject.Connection ' does table exist? If so get user confirmation to delete it On Error Resume Next Set tbl = cat(strTable) If Err = 0 Then If MsgBox("Replace existing table: " & _ strTable & "?", vbYesNo + vbQuestion, _ "Delete Table?") = vbYes Then strSQL = "DROP TABLE " & strTable cmd.CommandText = strSQL cmd.Execute Else Exit Function End If End If On Error GoTo 0 ' create new table strSQL = "CREATE TABLE " & strTable & _ "(calDate DATETIME, " & _ "CONSTRAINT PrimaryKey PRIMARY KEY (calDate))" cmd.CommandText = strSQL cmd.Execute ' refresh database window Application.RefreshDatabaseWindow ' refresh catalog cat.Tables.Refresh If varDays(0) = 0 Then ' fill table with all dates For dtmDate = dtmStart To dtmEnd lngDayNum = lngDayNum + 1 strSQL = "INSERT INTO " & strTable & "(calDate) " & _ "VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)" cmd.CommandText = strSQL cmd.Execute Next dtmDate Else ' fill table with dates of selected days of week only For dtmDate = dtmStart To dtmEnd For Each varDay In varDays() If Weekday(dtmDate) = varDay Then lngDayNum = lngDayNum + 1 strSQL = "INSERT INTO " & strTable & "(calDate) " & _ "VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)" cmd.CommandText = strSQL cmd.Execute End If Next varDay Next dtmDate End If End Function Or with DAO: Public Function MakeCalendar_DAO(strTable As String, _ dtmStart As Date, _ dtmEnd As Date, _ ParamArray varDays() As Variant) ' Accepts: Name of calendar table to be created: String. ' Start date for calendar: DateTime. ' End date for calendar: DateTime. ' Days of week to be included in calendar ' as value list, e,g 2,3,4,5,6 for Mon-Fri ' (use 0 to include all days of week) Dim dbs As DAO.Database, tdf As DAO.TableDef Dim strSQL As String Dim dtmDate As Date Dim varDay As Variant Dim lngDayNum As Long Set dbs = CurrentDb ' does table exist? If so get user confirmation to delete it On Error Resume Next Set tdf = dbs.TableDefs(strTable) If Err = 0 Then If MsgBox("Replace existing table: " & _ strTable & "?", vbYesNo + vbQuestion, _ "Delete Table?") = vbYes Then strSQL = "DROP TABLE " & strTable dbs.Execute strSQL Else Exit Function End If End If On Error GoTo 0 ' create new table strSQL = "CREATE TABLE " & strTable & _ "(calDate DATETIME, " & _ "CONSTRAINT PrimaryKey PRIMARY KEY (calDate))" dbs.Execute strSQL ' refresh database window Application.RefreshDatabaseWindow If varDays(0) = 0 Then ' fill table with all dates For dtmDate = dtmStart To dtmEnd lngDayNum = lngDayNum + 1 strSQL = "INSERT INTO " & strTable & "(calDate) " & _ "VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)" dbs.Execute strSQL Next dtmDate Else ' fill table with dates of selected days of week only For dtmDate = dtmStart To dtmEnd For Each varDay In varDays() If Weekday(dtmDate) = varDay Then lngDayNum = lngDayNum + 1 strSQL = "INSERT INTO " & strTable & "(calDate) " & _ "VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)" dbs.Execute strSQL End If Next varDay Next dtmDate End If End Function To make a calendar over the next 10 years, of weekdays only for instance, you'd call the function like this: MakeCalendar "CalendarWeekDays", #2010-01-01#,#2020-12-31#,2,3,4,5,6 If you were to add an IsHoliday column to this you'd then simply count the rows between two dates were the IsHoliday column is FALSE, e.g. PARAMETERS [Enter start date:] DATETIME, [Enter end date:] DATETIME; SELECT COUNT(*) AS WorkDays FROM CalendarWeekDays WHERE calDate BETWEEN [Enter start date:] AND [Enter end date:] AND NOT IsHoliday; Ken Sheridan Stafford, England (PeteCresswell) wrote: >Business days between two dates has got to involve a tblHoliday. > >Right now I've got such a table - mostly populated with bank >holidays and I compute biz days between two dates by iterating >through the days one-by-one: if it's a Saturday or Sunday it's >not a biz day.... otherwise I do a table lookup - no hit, then >it's a biz day. > >But this is *really* slow for processing a record set of, say, >2000 records. > >I'm thinking a faster approach would involve re-creating a table >of business days from some begin date to some end date each time >tblHolidays is updated and then doing some SQL magic to bang a >recordset up against that table and somehow bulk-calculate, for >instance, the number of business days between a bond's maturity >date and today's date... or whether or not a bond matures within >60 business days. > >Has anybody come up with such an approach? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1
From: KenSheridan via AccessMonster.com on 16 May 2010 13:43 Purely for consistency with other code. I don't do it in this function but frequently, by starting the week on Monday, I can then test for a weekday being <6 or a weekend >5. The custom constants, with values 6 and 7 are then used for the weekend days as the built in ones are 7 and 1 for Saturday and Sunday of course. Ken Sheridan Stafford, England (PeteCresswell) wrote: >Per KenSheridan via AccessMonster.com: >> I use the following function > >That's exactly what I was looking for. > >With your permission, I will adapt it to my needs. > >One question: Why are you using vbMonday with the Weekday() calls >and then using conSATURDAY and conSUNDAY instead of dropping the >vbMonday and just using vbSaturday/vbSunday? > >Maybe I'll have it figured out by the time you read this... but >maybe not... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1
|
Pages: 1 Prev: hu Next: "Automatic Configuration of Access has failed" 2007 was working fi |