Finding the last day of the year

[bknight]

I have a AC97 DB with daily price information on the DJIA going back to 1928.
I'm looking for a criteria to only select the last day of each year. I
could look for the first day of the year also, whichever would be easier to
write.

 

[June7 via AccessMonster.com]

You want to know the day of week?
There is a Weekday function. It returns a numeric value for the day of week.
Weekday(#1/1/2009#, vbSunday) = 5 (for Thursday)
Weekday(#12/31/1928#, vbSunday) = 2 (for Monday)
http://msdn.microsoft.com/en-us/library/82yfs2zh(VS.80).aspx

 

[John W. Vinson]

I have a AC97 DB with daily price information on the DJIA going back to 1928.
I'm looking for a criteria to only select the last day of each year. I
could look for the first day of the year also, whichever would be easier to
write.

DateSerial(Year([datefield]) + 1, 1, 0)

is the 31st of December for the entered year;

DateSerial(Year([datefield]), 1, 1)

is January 1st for the entered year.

These will of course be the last or first calendar day of the year, not the
last or first *trading* day; January 1 is always a holiday, I believe.

 

[bknight]

That is correct about Jan. I need the last day of trading, it might be the
29th, 30th or 31st, conversly the first day of Jan would either be the 2nd,
3rd or 4th. I'm not interested in finding what day of the week it is, just
being able to select the last trading day (or first) and the data associated
with it for each year.

I have a AC97 DB with daily price information on the DJIA going back to 1928.
I'm looking for a criteria to only select the last day of each year. I
could look for the first day of the year also, whichever would be easier to
write.

DateSerial(Year([datefield]) + 1, 1, 0)

is the 31st of December for the entered year;

DateSerial(Year([datefield]), 1, 1)

is January 1st for the entered year.

These will of course be the last or first calendar day of the year, not the
last or first *trading* day; January 1 is always a holiday, I believe.

 

[bknight]

No, I need the data associated with the last trading day(or first). Nice
link about VB.

 

[kc-mass]

Here is some untested aircode:

Sub DoSomethingWithLastDay()
Dim lngYear As Long
Dim strDate As String
Dim dteCurrentLastDay As Date
lngYear = 1928
strDate = "12/31/" & CStr(lngYear)
dteCurrentLastDay = CDate(strDate)
Do While dteCurrentLastDay < Date
strDate = "12/31/" & CStr(lngYear)
dteCurrentLastDay = CDate(strDate)
Debug.Print dteCurrentLastDay

'Do something with the date

lngYear = lngYear + 1
Loop
End Sub


This will march from 1928 to present giving you the last day of the year.

Regards

Kevin

 

[bknight]

That will give me the 31st, but I don't know what the date of the last
trading day is. See my post to John Vinson.

Here is some untested aircode:

Sub DoSomethingWithLastDay()
Dim lngYear As Long
Dim strDate As String
Dim dteCurrentLastDay As Date
lngYear = 1928
strDate = "12/31/" & CStr(lngYear)
dteCurrentLastDay = CDate(strDate)
Do While dteCurrentLastDay < Date
strDate = "12/31/" & CStr(lngYear)
dteCurrentLastDay = CDate(strDate)
Debug.Print dteCurrentLastDay

'Do something with the date

lngYear = lngYear + 1
Loop
End Sub


This will march from 1928 to present giving you the last day of the year.

Regards

Kevin

 

[John W. Vinson]

That is correct about Jan. I need the last day of trading, it might be the
29th, 30th or 31st, conversly the first day of Jan would either be the 2nd,
3rd or 4th. I'm not interested in finding what day of the week it is, just
being able to select the last trading day (or first) and the data associated
with it for each year.

Access doesn't have a direct link to the New York Stock Exchange to ascertain
its rules about what days are trading days and what days aren't. I don't
happen to know either. What are the stock exchange rules on the subject? I
presume January 1 is a holiday, and that Saturdays and Sundays are not trading
days; but are there four-day weekends (say if the 1st is a Thursday is there a
one-day week the next day?

 

[bknight]

I'm not sure of all the trading days either, but I already have 20K+ days and
all I want to do is select only the last day (or first) of each year.
I have been able to:
1. Group by years---easy
2. Select Dec 29, 30 and 31 of all years--easy Month([Date])=12 and
((Day([Date]))=29 Or (Day([Date]))=30 Or (Day([Date]))=31))
What I have not been able to do is select the last of that group as it
varies depending on whether the 31st occurs on a weekend (in this occurrence
the 31st is NOT in the DB), then it would be the 30th unless that occurs on a
weekend then it would be the 29th.
For example the set of data has:
1928 31 Needed date 31
1929 30, 31 Needed date 31
1930 29, 30, 31 Needed date 31
1931 29, 30, 31 Needed date 31
1032 29, 30 Needed date 30
I need the last date available for each year in the DB. I hope this
clarifies what I am asking and needing

 

[David W. Fenton]

I need the last date available for each year in the DB.

SQL like this should do it:

SELECT Year(TradingDate) As TradingYear, Max(TradingDate)
FROM MyTable
GROUP BY Year(TradingDate)

This is very different from the question you asked -- all you're
wanting is to find the latest date in an existing data set, which
has nothing to do with what the last day of the year nor what the
last trading day was.

 

[bknight]

That is exactly what I was looking for, thanks.

SQL like this should do it:

SELECT Year(TradingDate) As TradingYear, Max(TradingDate)
FROM MyTable
GROUP BY Year(TradingDate)

This is very different from the question you asked -- all you're
wanting is to find the latest date in an existing data set, which
has nothing to do with what the last day of the year nor what the
last trading day was.