Generating stock price change (daily and weekly)

[jaadu1]

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).

Unfortunately, I can not attach the spreadsheet where I have this information, but I'm willing to e-mail it to you, for you to see what I am referring to.

Essentially, I am experiencing problems in generating the correct stock price change (daily and weekly), by changing the date in cell A1.

Things to know:
1. I have 2 spreadsheets in my excel workbook:

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

Here is my formula for the daily stock price change and is located in cell C9 of the "AAA overview" spreadsheet:

=IF(AND((MATCH($A$1,'AAA historical data'!$A:$A,0)),(MATCH($A$1-1,'AAA historical data'!$A:$A,0))),(INDEX('AAA historical data'!$F:$F,MATCH($A$1,'AAA historical data'!$A:$A,0))-INDEX('AAA historical data'!$F:$F,MATCH(($A$1-1),'AAA historical data'!$A:$A,0)))/INDEX('AAA historical data'!$F:$F,MATCH(($A$1-1),'AAA historical data'!$A:$A,0)),IF(AND((MATCH($A$1,'AAA historical data'!$A:$A,0)),(MATCH($A$1-2,'AAA historical data'!$A:$A,0))),(INDEX('AAA historical data'!$F:$F,MATCH($A$1,'AAA historical data'!$A:$A,0))-INDEX('AAA historical data'!$F:$F,MATCH(($A$1-2),'AAA historical data'!$A:$A,0)))/INDEX('AAA historical data'!$F:$F,MATCH(($A$1-2),'AAA historical data'!$A:$A,0)),IF(AND((MATCH($A$1,'AAA historical data'!$A:$A,0)),(MATCH($A$1-3,'AAA historical data'!$A:$A,0))),(INDEX('AAA historical data'!$F:$F,MATCH($A$1,'AAA historical data'!$A:$A,0))-INDEX('AAA historical data'!$F:$F,MATCH(($A$1-3),'AAA historical data'!$A:$A,0)))/INDEX('AAA historical data'!$F:$F,MATCH(($A$1-3),'AAA historical data'!$A:$A,0)),555555)))

Formula in C9 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)

Here is my formula for the weekly stock price change and is located in cell C10 of the "AAA overview" spreadsheet:

=(INDEX('AAA historical data'!$F:$F,MATCH($A$1,'AAA historical data'!$A:$A,0))-INDEX('AAA historical data'!$F:$F,MATCH(($A$1-7),'AAA historical data'!$A:$A,0)))/INDEX('AAA historical data'!$F:$F,MATCH(($A$1-7),'AAA historical data'!$A:$A,0))

Formula in C10 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.

And if you would like to see my spreadsheet, I can e-mail that to you as well.