Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Checking Past Stock Prices
Old 08-24-2007, 01:38 PM   #1
Thinks s/he gets paid by the post
 
Join Date: Jul 2003
Location: Pasadena CA
Posts: 2,695
Checking Past Stock Prices

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?
__________________

__________________
T.S. Eliot:
Old men ought to be explorers
yakers is online now   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 08-24-2007, 01:42 PM   #2
Thinks s/he gets paid by the post
twaddle's Avatar
 
Join Date: Jun 2006
Posts: 1,378
Yahoo. Click on historical prices.
__________________

__________________
twaddle is offline   Reply With Quote
Old 08-24-2007, 06:36 PM   #3
Full time employment: Posting here.
 
Join Date: Sep 2006
Posts: 608
Quote:
Originally Posted by yakers View Post
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.
__________________
JohnEyles is offline   Reply With Quote
Old 08-24-2007, 07:03 PM   #4
Thinks s/he gets paid by the post
twaddle's Avatar
 
Join Date: Jun 2006
Posts: 1,378
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
__________________
twaddle is offline   Reply With Quote
Old 08-24-2007, 07:37 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jun 2005
Posts: 8,615
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.
__________________
LOL! is offline   Reply With Quote
Old 08-24-2007, 09:49 PM   #6
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Sep 2005
Location: Northern IL
Posts: 18,255
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
__________________
ERD50 is offline   Reply With Quote
Old 08-25-2007, 02:48 PM   #7
Full time employment: Posting here.
 
Join Date: Sep 2006
Posts: 608
Awesome, thanks for the answers - on automatically (or semi-auto)
updating of share prices into my own spreadsheet.
__________________
JohnEyles is offline   Reply With Quote
Old 08-25-2007, 11:15 PM   #8
Confused about dryer sheets
 
Join Date: Aug 2007
Posts: 1
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.
__________________
bighill is offline   Reply With Quote
Old 08-26-2007, 02:02 AM   #9
Thinks s/he gets paid by the post
twaddle's Avatar
 
Join Date: Jun 2006
Posts: 1,378
Quote:
Originally Posted by bighill View Post
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).
__________________
twaddle is offline   Reply With Quote
Old 08-28-2007, 09:44 PM   #10
Full time employment: Posting here.
 
Join Date: Sep 2006
Posts: 608
Quote:
Originally Posted by twaddle View Post
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.
__________________
JohnEyles is offline   Reply With Quote
Old 08-28-2007, 11:02 PM   #11
Full time employment: Posting here.
Darryl's Avatar
 
Join Date: Mar 2007
Posts: 577
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.
__________________
I highjacked a rainbow and crashed into a pot of gold - Bon Jovi
Darryl is offline   Reply With Quote
Old 08-28-2007, 11:09 PM   #12
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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
__________________
Animorph is offline   Reply With Quote
Old 08-29-2007, 10:40 AM   #13
Recycles dryer sheets
 
Join Date: May 2006
Posts: 230
Quote:
Originally Posted by Animorph View Post
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!
__________________

__________________
terminator is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Question on selling stock Tadpole FIRE and Money 12 05-31-2007 09:38 AM
What Real Estate Bubble? Art FIRE and Money 76 11-09-2006 03:33 PM
ISO Stock Options and AMT triplane FIRE and Money 2 01-12-2006 01:40 AM
Vanguard Pacific Stock Index Fund Craig FIRE and Money 7 04-05-2005 09:17 PM
Unitized Stock Fund RatherBeFishn Other topics 1 03-19-2005 09:07 AM

 

 
All times are GMT -6. The time now is 11:55 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.