Generating stock price change (daily and weekly)

[jaadu]

Hi - I am stuck in a little problem with excel and am hoping someone can help guide me in the right direction to generate the correct stock price change (daily and weekly).

In the attached spreadsheet, I am experiencing problems in generating the correct stock price change (daily and weekly) by changing the date in cell A1 in the "AAA overview" worksheet.

Listed below are some things to help you understand the attached spreadsheet.

1. There are only 2 worksheets in the excel file:

a. AAA overview -- this is where all the calculations occur

b. AAA historical data -- this is where I am storing all the historical stock information

2. If I change the date in cell A1 in the "AAA overview" spreadsheet, the daily and weekly information should generate accordingly, cell C9 and cell C10 respectively.

3. Formula in C9 (indicates daily stock price change) works when the date in cell A1 is between Tues - Fri, and fails between Sat - Mon. This formula has an insane IF statement, and is needed, as I have to meet 2 criteria before calculating the correct daily price change (Ex - Monday the stock market is open, but Friday and Thursday it was closed due to holiday. So the daily change should be from Wednesday to Monday)

4. Formula in C10 (indicates weekly stock price change) works when the date in cell A1 is between Mon - Fri, and fails between Sat-Sun. Also, I do not have an IF statement in cell C10, but now that I am thinking about it, I guess it should, considering if the markets were closed the prior weeks Thursday and Friday, then the current weeks change should be based from the prior weeks Wednesday to this weeks Friday.

I believe I said that correctly.

Well, if someone could shed some light, I would appreciate it.

Thanks and if you have any questions, or think I should use another excel function/formula, please feel free to mention it. Also, if you can make the change in my spreadsheet and repost, I will see what you are referring to.

[Ken Puls]

I'd do it like the attached, with some less complex formulas.

For reference, in order to use the VLOOKUP formulas, I had to sort your data into ascending order of dates.

I also added another sheet that has a calculation of the holidays and the previous work days.

[JoePublic]

Cross post here, BTW.

[jpr73]

Hi,
Cross post here

[jaadu]

Ken Puls - I greatly appreciate you taking the time to help me.

I reviewed your spreadsheet and it has given me some new ideas and will help me generate the results I am looking for.

Thank you once again.

[jaadu]

I did not know this post was going to be a cross post with other sites. I tried to remove my post from the other two sites, but was unable to do so from both sites.

Can you tell me how I can remove/delete/edit my post from ozgrid and mrexcel?

Thanks and sorry for the dupe posts.

[Ken Puls]

I did not know this post was going to be a cross post with other sites.

Uhh... you posted them, right? How could you not know? The action of cross posting is not about the resolution, it's about posting the original questions to multiple sites. Here, have a read of this article. It explains the issues fairly thoroughly.

I tried to remove my post from the other two sites, but was unable to do so from both sites. Can you tell me how I can remove/delete/edit my post from ozgrid and mrexcel?

Nope, I can't tell you... I'm not actually sure how. I would actually be surprised if they let you.

My suggestion would be to post a link in both forums back to this thread, letting them know that you got an answer here. It might not be a bad idea to also apologize for the cross posting too, as one of them does have a slightly harsher view on cross posting than I do.

Thanks and sorry for the dupe posts.

No harm done here as long as you get to it before anyone else answers. Just please do read the article.

[JoePublic]

It might not be a bad idea to also apologize for the cross posting too, as one of them does have a slightly harsher view on cross posting than I do.

Yeah, those MrExcel guys are real bears!