First/Last Business Day of this/last quarter, month, and year

[12mcarchedi]

Hey guys,

I've been looking around for this for a while now, and I can't really seem to find exactly what I am looking for. I am trying to return stock prices from 4-weeks ago from the current date, the ending of last quarter, and the ending of last year. The problem is when any of those dates are a holiday or on a weekend, it doesn't pull the prices since stocks don't trade during these days. Is there a way to pull the last business day, so excluding holidays and weekends, for these different periods using normal excel functions? If not, is it possible to do it with VBA and if so could someone help me write that code?

Just for an example, if I am trying to find the price difference between the current price and the year ending price between 2011/2012, I wouldn't be able to pull the year end price since 12/31/2011 ends up on a Saturday. In this case, I would need to pull the price from 12/30/2011 instead, as it is the last price during that period.

Thanks for any help in advance, and I will gladly provide anymore clarification if needed.

Best,
MJC

 

[BiocideJ]

Yes, if you are using Excel 2007 or later than it is a default formula WORKDAY()
If you have a prior version you will need to enabled the Analysis TookPak add-in to have access to this function.

You can then take your current formulas and wrap them in this...
=WORKDAY({your current formula}+1,-1,Holidays!A:A)

Where Holidays is a sheet in your workbook with all of the valid holidays (New Years, Thanksgiving, etc) you would like to consider non-work days in column A
e.g. you can add days that stock market was closed such as for Sandy

 

[RobsterM]

Last business day of the month question

Hi guys, In cell A1 I need to get todays date i.e =today() or 5 Sept 2015 - that's what I can do.. In cell A2 I need to see the last working day of the previous month i.e. 31 Aug, in cell A3 I need the previous last working day i.e. 31 July. Ive used EOMONTH but I struggle with the cell A2 because it defaults to 30 Sept which hasn't happened yet! and the WORKDAY incorporation - any ideas?

Also, I would be really handy to get the public holidays in there, where if the last working day of the month was a public holiday the formula would revert to the day prior.

All of this will be going into a chart where I'll will be trying to use OFFSET to make the chart living - instead of asking about this i'll do my own trial and errors.

Any help much appreciated.

Cheers,
Rob

 

[BiocideJ]

Re: Last business day of the month question

Might be a bit late on the response for this, but the following formula should do what you are requesting.

In A2
=WORKDAY(A1-DAY(A1)+1,-1,HOLIDAYS!$A$1:$A$100)

Where HOLIDAYS is the name of a tab containing your list of holiday dates to exclude in range A1:A100