Hello,
I am trying to calculate stock market returns over different periods. I have downloaded the daily index values from yahoo finance for the S&P 500 and the Russell 2000 index. I would like to start with the 1 year, 3 year and 6 year rolling periods. This is difficult because the data excludes the weekends and holidays which is difficult to calculate 365 days. When I enter a starting period the ending date some times falls on a weekend or holiday. I have used the vlookup function to randomly pick starting dates and find its corresponding index value, but the rolling period often ends on a weekend or holiday where there is no corresponding date or value. I would like to create a lookup that allows the ending date to move out to the next trading day in the future and find the corresponding index value.
This is the formula I have been using =WORKDAY(EDATE(C9,12)-1,0,'S&P Daily '!J2:J7288)
12 is the 12 month rolling period from a specific date. I get a "#N/A" when the end date lands on a weekend, holiday or the data is missing.
Any suggestion would be very helpful.
Thanks
I am trying to calculate stock market returns over different periods. I have downloaded the daily index values from yahoo finance for the S&P 500 and the Russell 2000 index. I would like to start with the 1 year, 3 year and 6 year rolling periods. This is difficult because the data excludes the weekends and holidays which is difficult to calculate 365 days. When I enter a starting period the ending date some times falls on a weekend or holiday. I have used the vlookup function to randomly pick starting dates and find its corresponding index value, but the rolling period often ends on a weekend or holiday where there is no corresponding date or value. I would like to create a lookup that allows the ending date to move out to the next trading day in the future and find the corresponding index value.
This is the formula I have been using =WORKDAY(EDATE(C9,12)-1,0,'S&P Daily '!J2:J7288)
12 is the 12 month rolling period from a specific date. I get a "#N/A" when the end date lands on a weekend, holiday or the data is missing.
Any suggestion would be very helpful.
Thanks