Historical prices and holiday table

[addingle]

In Excel 2003, would like to know how to handle holidays when looking for historical securities prices. I have all historical prices for the security, use web queries to get adjusted close from previous day, calculate YTD return, and then need to calculate 3, 5, 10 year returns from yesterday's date. Use Edate to determine number of months back. Use vlookup for closing price, but get error when the date is either a weekend or a market holiday. I think I need a holiday table, but need some help on that.

Thanks in advance.....

[alansidman]

I googled US Stock Market Holidays and found many hits. This is just one example. I think that depending on far back you go, you may have to do a couple of searches and then compile a comprehensive table from that.

http://www.money-zine.com/Investing/...rket-Holidays/

I then think that you will have to run a if statement that does a vlookup for the date in question and if it is in the table, you will know that it is not a proper date to test for.

Alan

[tlafferty]

Here's a link to the holidays for the NYSE:
http://corporate.nyx.com/holidays-and-hours/nyse
Trap the error by using the by using an IF and the WEEKDAY function. Here's a shell function since I'm not how your VLOOKUP is structured:

Please Login or Register  to view this content.

Here's how I see this working:
Place the table of holidays in an unused area of your workbook. The left hand column will be the date of the observed holiday. The right hand column will be the date the market was last open or will next open - not sure what you need there. The above formula will check the current VLOOKUP you're using to see if the returned date is in the holiday table. If it is, it will look in the second column for the date it should return, if not, it will check to see if your lookup date is on a weekend. If it's a Saturday, it will add 2, if Sunday, it will add 1. You'll need to change the reference to YOURLOOKUP to the actual formula you're using.