You can load a copy of a web page, or selected parts of a page, into Excel using the Data/Import External Data/New Web Query command. It opens up a web browser and allows you to travel to the page you want and select tables from the web page. They download nicely into the rows and columns of a spreadsheet. I download my Yahoo portfolio to get prices and my Quicken portfolio to get my actual portfolio holdings from Quicken. You can then use the values from the web pages to calculate whatever you want.
You can load in the Yahoo historical prices and look up the price for a specific date with a formula in Excel. I used to do that to plot a stock's price on an Excel chart.
Probably not the easiest way to load historical prices for a bunch of stocks, but it is great for monitoring your portfolio. You can even tell Excel to update the data every few minutes. I used that with real-time Yahoo prices back when I was trading stocks more often.
This is a normal Excel function, but it may not be installed with the default Excel installation. I do have the AnalysisPak loaded, I think. Also, it is a pain in the butt if Yahoo changes their web page format! I guess that will be happening again soon.
Dan