|
Checking Past Stock Prices
08-24-2007, 12:38 PM
|
#1
|
Thinks s/he gets paid by the post
Join Date: Jul 2003
Location: Pasadena CA
Posts: 3,346
|
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
|
|
|
|
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!
|
08-24-2007, 12:42 PM
|
#2
|
Thinks s/he gets paid by the post
Join Date: Jun 2006
Posts: 1,703
|
Yahoo. Click on historical prices.
__________________
Emancipated from wage-slavery since 2002
|
|
|
08-24-2007, 05:36 PM
|
#3
|
Full time employment: Posting here.
Join Date: Sep 2006
Posts: 608
|
Quote:
Originally Posted by yakers
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.
|
|
|
08-24-2007, 06:03 PM
|
#4
|
Thinks s/he gets paid by the post
Join Date: Jun 2006
Posts: 1,703
|
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
__________________
Emancipated from wage-slavery since 2002
|
|
|
08-24-2007, 06:37 PM
|
#5
|
Give me a museum and I'll fill it. (Picasso) Give me a forum ...
Join Date: Jun 2005
Posts: 10,252
|
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.
|
|
|
08-24-2007, 08: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: 26,895
|
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
|
|
|
08-25-2007, 01: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.
|
|
|
08-25-2007, 10: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.
|
|
|
08-26-2007, 01:02 AM
|
#9
|
Thinks s/he gets paid by the post
Join Date: Jun 2006
Posts: 1,703
|
Quote:
Originally Posted by 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.
|
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).
__________________
Emancipated from wage-slavery since 2002
|
|
|
08-28-2007, 08:44 PM
|
#10
|
Full time employment: Posting here.
Join Date: Sep 2006
Posts: 608
|
Quote:
Originally Posted by twaddle
|
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.
|
|
|
08-28-2007, 10:02 PM
|
#11
|
Full time employment: Posting here.
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
|
|
|
08-28-2007, 10:09 PM
|
#12
|
Thinks s/he gets paid by the post
Join Date: Jul 2005
Posts: 4,366
|
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
|
|
|
08-29-2007, 09:40 AM
|
#13
|
Recycles dryer sheets
Join Date: May 2006
Posts: 230
|
Quote:
Originally Posted by 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.
|
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!
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
» Recent Threads
|
|
|
|
|
|
|
|
|
|
|
|
|
» Quick Links
|
|
|