I am using the MSN MONEY Add-in with Excel 2007. I would like to keep & track historical data and am attempting to do the following:
1) I want the dynamic quote to be pasted on a cell on another sheet as a static value. I think this is =sheetname!cell#
2) I want that same static cell value to highlight purple if it 5% higher or 5% lower than the previous static cell.
3) I want that same static cell value to become red text IF it is less than the original static cell value (the value in the first column)
4) I want that same static cell to become underlined IF it is less than the previous displayed static values. I assume this would be tricky because the number of previous cells could differ. If there are only 4 cells with static values before the cell in question, then it would underline based on those 4 cells. If there are 100 cells with static values before the cell in question, then I would want it to underline based on only the visible cells. When I say visible cells, I am freezing the panes so that I can continue to add data and assume that the maximum number of visible cells would be 10 to 15.
5) Not only would I then want to copy these formulas/formatting to the other stocks, but to make it even more complex, I would like for the above conditions to move one spot to the left after the close of each business day (4:20pm).
Is this possible?
The point of this is to see static historical quotes and be able to see at a glance what stocks are at recent lows, lows since I have been tracking them, and big jumps up or down.
Thanks in advance for any help you can provide.