NYSE Trading Days

[coliervile]

Is there away that a spread sheet can be made to figure out what days of the year and for the month are trading days? I would also want the trading days to be numbered 1, 2, 3, 4 ...even though the days of the week maybe Thursday, Friday, Tuesday, Wednesday??? There maybe a worksheet alread made to do this but I haven't been able to find one. Thanks for you help.

Charlie

 

[coliervile]

To explain a little further...the stock market is only open Monday thuough Friday except for about 9 federal holidays that fall mostly on Mondays (except for Thanksgiving and Chrismas). I want the worksheet to look something like this: starting in column A row 4 the dates the stock market is open, column B row 4 the day of the week that's in column A row 4, the trading day of the month that coincides with column A row 4. Example;

Column A Column B Column C (all are the same row (4))
Date: Day of week TD of week
April 1, 2010 Thursday 1
April 2, 2010 Friday 2
April 5, 2010 Monday 3
April 6, 2010 Tuesday 4

Charlie

 

[WaterGypsy]

Hi Charlie

You can start by using the function WORKDAY to decide what days are working days .. eg: F5 =WORKDAY($B$4,E5,"HOLIDAY_RANGE") where B4 contains Jan 1st, and e5 is 1.. then e6 is 2 etc and "HOLIDAY_RANGE" is a named range containing the exchange holidays... that will result in a list of dates that the exchange is open.

Then you should be able to derive the other information you need from that result.

kevin

 

[barry houdini]

As WaterGypsy says, you can use WORKDAY to get the dates, put the first date in A4 and then use this formula in A5 copied down

=WORKDAY(A4,1,Z$2:Z$10)

where Z2:Z10 has a list of holiday dates

In B4 use this formula copied down

=TEXT(A4,"dddd")

and in C4

=NETWORKDAYS(A4-DAY(A4)+1,A4,Z$2:Z$10)

 

[coliervile]

Thanks WaterGypsy and barry houdini I will give it a go and see what I come up with.

Charlie

 

[coliervile]

Barry I did as you suggested and got the following results;
A4 is January 1, 2010
A5 is =WORKDAY(A4,1,Z$2:Z$10) result was #NAME?
B4 is =TEXT(A4,"dddd") result Friday
C4 is =NETWORKDAYS(A4-DAY(A4)+1,A4,Z$2:Z$10) result #NAME?
Z2:Z10 is January 1, 2010
January 18, 2010
January 18, 2010
April 2, 2010
May 31, 2010
July 5, 2010
September 6, 2010
November 25, 2010
December 25, 2010

Charlie

 

[schielrn]

You will need to make sure you have the analysis toolpak installed. it is already on your computer, you just need to go to Tools-->Add-Ins.

In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.

If you see a message that tells you the Analysis Toolpak is not currently installed on your computer, click Yes to install it.

Hope that helps.

 

[coliervile]

Thank you schielrn that worked great!

Charlie