Checking Past Stock Prices

[yakers]

Every month I update my spread sheet to reflect the value of my ‘portfolio’ on the last day of the month. I will be away from internet access at the end of the month and would like to know the value of each stock/fund. Is there a site that lists the closing price for stocks and mutual funds on a previous date?

 

[twaddle]

Yahoo. Click on historical prices.

 

[JohnEyles]

Every month I update my spread sheet to reflect the value of my ‘portfolio’ on the last day of the month.

I don't suppose there's a way to do this automtically ?
By 'automatically', I mean not having to look up each
ticker symbol and then enter it into my spreadsheet by hand.
Yes, I know finance.yahoo.com allows me to set up a
portfolio (as do many other sites) by I want my
portfolio in MY spreadsheet so I can manipulate it
as I see fit.

 

[twaddle]

There's no built-in way to update stock prices in Excel (at least not in the version I'm running), but there are VBA macros to do it for you. I haven't really played with them (yet), but check these sites:

Financial Alchemist: Using Excel to Import Financial Data from the Web

Index of /Excel

 

[LOL!]

the tools at moneycentral.msn.com let you get historical prices as well. I usually create a chart around the date needed and read the price off the chart.

 

[ERD50]

If you set up a list of stock/fund symbols in Yahoo, there is a 'download to spreadsheet' button. It will dl a simple spreadsheet of symbols and prices. You can copy/past this into your spreadsheet, or get a bit fancier with some 'VLOOKUP' formulas.

Either way, the data is in your spreadsheet, to manipulate as you see fit.

-ERD50

 

[JohnEyles]

Awesome, thanks for the answers - on automatically (or semi-auto)
updating of share prices into my own spreadsheet.

 

[bighill]

Excel version 2003 has an function on the Add-ins menu called MSNStockQuote.Functions. Once you install it, you can enter a formula on your spreadsheet that will get the most recent prices for stocks or mutual funds. It adds a button to the Excel toolbar that you click on to update the prices. I've got it updating 16 prices in my spreadsheet - takes about 15 seconds.

 

[twaddle]

Excel version 2003 has an function on the Add-ins menu called MSNStockQuote.Functions. Once you install it, you can enter a formula on your spreadsheet that will get the most recent prices for stocks or mutual funds. It adds a button to the Excel toolbar that you click on to update the prices. I've got it updating 16 prices in my spreadsheet - takes about 15 seconds.

I don't think that's built-in to excel. I believe you first need to download the add-in package from microsoft.

Anyway, I finally played with the smf add-in available here:

smf_addin : EXCEL Stock Market Functions Add-in

Seems pretty cool. Download the package, add it in via Tools:Add-In, and you have access to about 10,000 new internet-based financial functions.

To get a stock price, you just do something like this in a cell:

=RCHGetElementNumber("IBM",25)

Where "IBM" is the ticker, and 25 is "get last price from MSN" -- one of 10,000 new functions (documented in a spreadsheet that is included as part of the download).

 

[JohnEyles]

There's no built-in way to update stock prices in Excel (at least not in the version I'm running), but there are VBA macros to do it for you ... check these sites:

Financial Alchemist: Using Excel to Import Financial Data from the Web

This points you to a yahoo-group called smf_addin and they
seem to have a cool Excel add-in to do stuff like this ... BUT ...

I got wierd error messages from Excel and the guy said he really
doesn't know if it works on a Mac. Oh well.

 

[Darryl]

I set up a morningstar portfolio and it emails me everyday with prices and move for the entire portfolio by stock. All I would have to do when returning to a computer is click on the date I wanted.

 

[Animorph]

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

 

[terminator]

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.

That is a great tip. I used to use the "Download Spreadsheet" from Yahoo so I could copy and paste price information into an Excel spreadsheet, but I could never figure out how to get the other data (that I have in my custom display format) into the spreadsheet. Thank you!