There are a lot of requests on how to get stock quotations into an Excel file. My own advice is to purchase a financial package, but there are some rather interesting things you can do with Excel accessing online quotes.
I see a lot of postings in Excel Newsgroups that relate to keeping track of quotations, purchases of stocks and the like.
While Excel is a very good tool, I do not think that it lends itself well to replacing Quicken (Intuit) or Microsoft Money. Whatever you do in purchasing stocks or mutual funds, it becomes apparent when you go to sell them that you need complete accurate records of each transaction, purchase, reinvestment of dividends, capital gains, stock splits, etc. It is a lot easier to maintain accurate records with a financial package from your first purchase to your last transaction.
I don't use all the features available. To use some features may cost more money depending on your financial institution. In fact I enter all transactions myself, and only use online capability to update stock quotes.
Both Quicken and Money have hidden expiration dates in their software to force you to purchase a new version so you can continue to use online features such as getting quotes, getting bank and other balances, and paying bills, which is unfortunate since the enhancements that most people will actually use are generally minor. PCWorld.com - Intuit Cripples Older Quicken Versions in both online access and use of QIF formats, in addition to extorting fees from banks. Advertisements were added to paid for software and an almost total disregard for the best interests of customers continues. (here are some definitions: advertisement, extortion, fraud, profiteering, racketeering, ransom, ). You should be able to pick up Quicken 2005 for $30 after $20 rebate from warehouse discount store (one discount per membership #).
- Keep track of all stock purchases, sales, dividends, capital gains, reinvestments, splits.
- Obtain quotes from online source.
- Keeps a database and can supply your ROI (Return on Investment) between any two days of your choice (past or present).
- Obtain your financial transactions from your financial institution.
- Pay your bills.
- Compatibility with tax packages such as Turbo Tax (Intuit) and TaxCut.
- Conversion from another financial program.
- Customized reports, graphs, and charts, with the ability to generate a .csv file for use in a spreadsheet. MS Money has better features for getting information into Excel. Quicken has the bigger customer base.
- Bill payment and reminders.
- Quicken, backup the entire folder that contains qdata.QDF -- You should keep your data separate from the software, regardless of what software you are using. See additional topics concerning Quicken on this page.
- If might be a good idea to keep track of initial purchases and major sales of stocks so that you can compare it with your Financial software as a check for completeness, which you may have to resolve with your brokerage statements.
- You can create nice summaries for your accountant to help you make sure you have included everything. This helps your broker verify his figures that he inputs from your tax related brokerage forms.
- Portfolio Slicer - Free Excel workbook to track investments (free for personal use), Vidas Matelis (Microsoft SQL MVP)
Primary online activity is being able to update your portfolio in your financial package.
You can probably buy, trade stocks with your brokerage firm online, saving a lot in commissions, and probably find a lot more information there. If you can't trade online, I would change firms.
Also you might familiarize yourself with the following sites, each of which allows you to create a portfolio at their site, but more important allows you to see what is happening in the news about your stocks. (ordered by my preference)
The portfolio on a site will help you keep up with what is happening, but your package will have what you really need for your taxes.
- Investing: Stock market updates and investment advice - MSN Money http://money.msn.com/investing/
- http://www.quicken.com, Search Quicken support KB: http://www.intuit.com/support/quicken/
Also the packages (software) can be updated online besides the quotes.
Use of Keyword Shortcuts in Firefox to get a one or a few quotes — as opposed to using a stock profile
- Quote: IBM MSFT DELL PG &mdash obtain quotes at finance.yahoo.com, the %s will be substituted by your list, you can change the duration to days(1d) , months(3m), years(5y)
msnews.microsoft.com carry microsoft.public newsgroups and are accessible by anyone. netnews.msn.com would be available to those on MSN, others would probably have to access the servers on their own ISP.
- Excel Newsgroups, searching newsgroups, and the Microsoft Knowledge Base (MS KB).
Yes you still have to keep them, file them by brokerage firm then date (or account then date). You will need them.
Keep all of your end of year statements from your broker, some brokerage firms have good statements, some don't, either way you should be using a package. Transfer your holdings out of any firm that does not supply decent statements with correct information (current holdings, name and value of each stock, symbol, date of each transaction).
Why not just purchase Quicken or MS Money. Either of these packages will provide for purchase, reinvestment of dividends, stock splits and all those nasty things that you will have to have accounted for when you sell (and you have to figure taxes). Over a number of years this can get quite messy trying to catch up. About this time of year (Dec) local computer stores sell these packages as loss leaders to bring in customers.
Either package also provides for updating with latest stock prices through the internet and you have the total market value at your finger tips in a couple of minutes without keying in the latest quotes.
If you keep a stock invested you do not need to pay a lot of the taxes until you sell it. You have paid taxes on distributions all along and the reinvestment is done with money you have already paid tax on. Keep accurate records as the money reinvest is new money and you do not want to pay capital gains on it when you take it out again later -- don't pay tax on your money twice, that is why you need to use a financial package. Remember no taxes until sold, which may be after you have a job and will be in a lower bracket.
A day trader is into a stock and usually completely out of a stock in the same day, with the objective of gaining 1/8 point on 10,000 shares with much of it purchased on margin (money on loan). This means that they have more frequent commissions, have to pay short term gains instead of long term and have to pay tax on gains in current year. If they lose their money, and most of them will lose everything, they owe more than they have and have nothing to deduct their losses from after having paid taxes in previous years on their accumulation.
There are lots of stock quote sites, will list sites below that are not Yahoo and are not listed as International below.
Microsoft quote as seen on sites: Quotes, International (#international)DownLoad.com, International Quotes, download aftermarket values for all stocks from different exchanges.Download details: Excel 2003/2002 Add-in: MSN Money Stock Quotes,
FreeEnd of Day Quotes FreeDownload for more than 30 stockmarkets. Free sign-up. Define your own downloads. Many file formats supported : Metastock, Walmaster, Quicken, Excel, ASCII,... Manage several lists at one time. You may need to rerun your Outlook Express 5 install as you need to be able to run Java Applets, so you must include the "Microsoft Virtual Machine" when you install OE6. To verify that you can run Java Applets at DownLoad.com goto Lists then click on the hyperlink within the wording. Click here to see a demonstration of the applet. THIS SITE IS NO LONGER FREE. Not only is it no longer free but you probably have to "join" to find that out. I was told it was not longer free and this is what I found hidden in About Us, FAQ: 2001-05-11 We now accept payments in US dollars they don't even tell you what the charges would be.
A site providing Australian and US quotes is Hooyaya Stock Data Center daily stock market for Australia (posted after midnight Sydney time) and US. US daily text files after 8:30 PM ET (500K), monthly zip files (425K). ticker, date (yyyymmdd), open, high, low, close, volumeExcel 2003/2002 Add-in: MSN Money Stock Quotes Brief Description This add-in for Microsoft Office Excel 2003 and Microsoft Excel 2002 allows you to get dynamic stock quotes from the MSN Money Web site. The add-in allows you to easily gather and study the stocks of interest to you.
As stated elsewhere, I personally would use software such as Quicken to keep track of stocks, but a lot of people ask for how to keep track of stocks in Excel -- which is just barely scratching the surface as Quicken can tell you what your portfolio was worth for each day that you've used it and in far less space with more reports.
Historical Quotes (#historical)Members of AOL have been able to get historical quotes in a variety and options as downloadable files. Recently many other sources now offer free historical quotes.
Anyone can use finance.yahoo.com and by looking at a chart for a stock can maximize the chart and see when splits took place, and can download a .csv file (for a single stock). You can directly use this link using a stock of your choice. i.e. http://chart.yahoo.com/t? or http://chart.yahoo.com/d?s=IBM, if you wanted to code the date range on the URL that also can be done.
There is no choice of format but it is as follows:Date,Open,High,Low,Close,Volume 23-Dec-99,117.25,119.25,116.75,117.4375,15514200 22-Dec-99,116.2969,118,115.125,117.5625,19282600 21-Dec-99,112.375,116.625,110.625,115.875,28723000 20-Dec-99,114.8125,115,111.1875,112.75,19716900 17-Dec-99,116.625,117.125,113.625,115.25,52949400Derived from a posting 1999/11/08 by Fred H Smith, AN=546196255 -- Re: closing price history for multiple stocks
You can input the .CSV file into your choice of a Financial package, though you may have to change some formatting, which if you can't handle programmatically you could use Excel though this might be tedious. Financial packages probably now are able to download historical quotes from a website, you would have to check this yourself.
Watch Lists (#watchlists)A package would allow you view the current value for your portfolio or for any day since you started keeping accurate records in the package.
It will also allow you to start tracking stocks that you are interested in by placing them in a watch list. I prefer to enter them under a separate account as owning .01 or .001 shares so I can see percentages and values change for a given evaluation period.
Maintaining a Portfolio on a Website (#webbased)While I recommend using a Financial package to keep track of your stocks. Maintaining some semblance of a portfolio on a web site allows you to check news for your stocks.
Sites where you can build a portfolio and download the portfolio as CSV or other means for use in a spreadsheet program.
When signing up for such free services, create a different userid and password than you use for anything else. privacy issue
- http://www.moneyworld.co.uk or specifically stocks
Privacy and Terms of Service on Sites you Visit (#privacy)Many of the web pages that you visit lie in their TOS statements. Others say that they don't collect statistics on you but don't tell you that they use Double-Click to collect the statistics for them. I believe IBM is taking a lead in providing links only to companies that observe their own TOS statements.
If you are a Firefox user, you would be well advised to install the Adblock extension.
To really be safe from doubleclick add the buggers to your hosts files.
DoubleClick provides an opt-out option so they don't collect personal information on you. Look in the Privacy statement and look for the link opt-out, it may take you several minutes to go through this nonsense but when you are finished they will not be tracking you -- your only protection that they keep their word this time is that they are being watched by a lot of privacy issue groups.
See Busines Week article (Feb 14, 2000, page 38) "A Privacy Backlash on the Net", a table of terms on Privacy Statements and another related article "Privacy War of Richard Smith" at
Found Junkbusters Corp, as a result of another article.
For those with MSN as their ISP as of 2001-05-10 special instructions for OE6 in identifying SPAM through MSN filtering. As of 2001-06-25 have seen no reduction in spam nor anything caught in such filters. As of 2001-11-25 it is filtering about 30% of spam coming through and is not filtering on at all on obvious viruses.
GazNET Anti-Spam Protection File With this filter, you'll be: protected against 5872 known spammers (zip file of). Appears to be useable in Outlook only, not Outlook Express. [LG 2001-02-17]
FAQ related to Spamming attacks attempting to discredit Linux.org, provides a pretty decent summary of what to look for.
Virus protection information can be found on my Outlook Express page.
Opt-Out and Murk laws: Ed Foster's Gripelog || The Ghost of Murk: States Enact Pro-Spam Laws
Disposable email accounts: You can use hotmail for subscriptions, and even safer use Sneakemail to generate disposable email addresses for very limited time use (i.e. 20 messages).
The UCE (Spam) War and Its Impact on E-Mail, Greg R. Notess (Reference Librarian Montana State University)
Protecting yourself against Identity Theft
The Seattle Times: Consumer Guide
UCE legal aspects, tips
Notes on Privacy, the Patriot Act, and Identity Theft also see
Privacy Off the WebHere's an excerpt from a Privacy statement required by law to be mailed to customers. It really shows how meaningless these statements are.We do not disclose any nonpublic personal information about our customers or former customers to anyone, except as permitted by law. (Credit Suisse, as received Jun 25, 2001)Translation we will do as we damn well please unless specifically prohibited by law. Or more to the point do you think anyone would state that they don't comply with the law. I'm sure they do everything in their power within some group to insure that there are no laws, or that laws are ambiguous, conflict with other laws and are generally unenforceable.
Pasting fractional stock prices show up as dates in Excel (#Fractional)Stock prices less than 1 when pasted into Excel show up as dates instead of fractions:
i.e. 1/2, 63/64, 3/8
See my response 24Jan2000 that contains a macro that attempts to address this problem, also see other responses in the thread for a better understanding:
Quicken Interchange File used by Quicken and MS Money (#qif)
- There probably aren't many free utilities to convert a QIF file to CSV format because there really is no point to doing so as both Quicken and Money can export CSV files directly.
- XL2QIF conversion of an Excel speadsheet to QIF file (Excel to Quicken). Converts bank statement or credit card statement information. « Site has links for related utilities such as one to convert QIF dates mm/dd/yyyy to dd/mm/yyyy.
When the addin is active there is a new menu "Money" added with submenus XL2QIF to create a QIF file (Save to QIF) and an item to import a QIF file (Load from QIF).
- QuickAccess (archive link of orginal broken link) by McGyver (Habeeb J. Dihu), QuickAccess is essentially an add-in for Excel that provides several functions that can pull data in from Quicken. There is a drawback the addin requires Quicken to be up when Excel is up. so you probably want to turn on the addin only when you really need it. (archived)
- Money FAQ, MS KB links, import/export, investments, pathces, updates, downloads
Facilities in Excel (#excel)See HELP --> ans wiz. --> get external data
and see "Retrieve data from a Web page" and following the link 'About retrieving data from a Web page' there is a lot more to read about in "About retrieving data from a Web page" and help to explain an what an .iqy Web query file actually is. (based on reply by Bob O'Donnell in worksheet.functions 2000-02-01).
Excel 2002 and Stock Quotes (Tips), in Excel 2002, you can easily insert an automatically updating stock price quote for a specific company into your spreadsheet
Query (#query)Evidently one of the inquiries embedded in an example has to be be changed to
In a saved query named "Microsoft Investor Stock Quotes.iqy", -- [ref] (ref link broken, but as already noted...) appears related to HOW TO: Specify Dynamic Web Query Parameters in Excel 2000
Specific Information relating to Yahoo Quotes (#yahoo)Several topics above have already mentioned Yahoo, also see...
- Yahoo! Finance - Business Finance, Stock Market, Quotes, News
- Obtaining Yahoo Quotes into Excel, limited to 200 symbols, posting by Don Guillett, misc, 2002-05-16.
A mish mash of Related and unrelated postings (#mishmash)Have concentrated on using Google Usenet Advanced Search
UsenetAdvance Search [http://groups.google.com/advanced_search]
Old Deja Search Argument looked something
like this: (~s stock* | ~s quote) &! inventory (~s stock* | ~s quote) &! inventory
Some excerpts from the above (#excerpts)
- AN=556736718 1999-12-05 Re: Stock quotes, T.N.Spearman; macro to obtain stock information. Msgbox requests stock to be interrogated.
- AN=556795573 1999/12/04 Re: Stock quotes, tim williams. Spreadsheet has list of symbols in a column to be interrogated.
- AN=527913994 1999/09/21 Re: Can I link to stock quotes on the web?, Chip Pearson. Suggests downloading the StockWeb workbook which requires Excel 97 or Excel 2000. Shows how to download the DJIA, for example, it shows how to do this. Getting this info is 1) possible, 2) perfectly legal, 3) totally free.
I tried Chip's download, the format of the HTML report has changed, common problem when attempting to create a report based on another report. You can get the same information using a financial package without the automatic update. The cells you want are now located on different rows than the symbols.
- AN=555285199 1999/12/01 Re: VBA Code to Access Stock Quotes, Tom Ogilvy refers to MS KB article: http://support.microsoft.com/support/excel/content/excel97/xlweb.asp -- Microsoft Excel 97 on the Web -- Get the information you need to take advantage of the Microsoft Excel 97 Web integration features and functionality.
- AN=551338306 1999/11/21 Re: Linking to stocks and bonds database, Tom Ogilvy refers to MS KB article: http://support.microsoft.com/support/excel/content/excel97/xlweb.asp -- Microsoft Excel 97 on the Web -- Get the information you need to take advantage of the Microsoft Excel 97 Web integration features and functionality. and to
http://support.microsoft.com/support/excel/content/excel97/xlweb.asp Microsoft Excel 97 on the Web
- Q157482 -- XL97: How to Create Web Query (.iqy) Files for use with Microsoft Excel 97.
- Q213730 -- XL2000: How to Programmatically Perform a Web Query, * go for the Table example.
- Stephen Bullen's Excel Page, has several downloads pertaining to continuously updated 'live' DDE Stock Price Feeds.
- Template Categories - Office.com
- Word Templates - Office.com
- Excel Templates - Office.com
unfortunately Microsoft has lousy search facilities and documentation of their search so you will pick up Word templates as well
- Record of stock purchase and sales. txt (archived), download (archived) which was presented by Sheldon Koepf in the Investment Sig (archived) of the ACGNJ Amateur Computing Group of New Jersey. Keep track of purchases and sales. To keep track of details and current value use software like Quicken or Money. Note archived copies copies from 2005-2006 are available from the "Wayback Machine" (archive.org).
- Different Ways of Using Web Queries in Microsoft Office Excel 2003 (broken MSDN link), MSDN
watch out for old links within query:
New: http://money.cnn.com/quote/quote.html?shownav=true&symb=MSFT%20IBM (not sure)
- Quicken scorecard
- A sample XL2QIF macro to make a QIF file from an Excel file specifially formatted in the desired order needed for the Quicken Interchange File (QIF).
- XL2QIF conversion of an Excel speadsheet to QIF file. Converts bank statement or credit card statement information. Site has links for related utilities. «
- Dave Steppan's Excel a la Carte (replaced broken geocities link), download a zip file to analyze your mutual fund or stock portfolio, obtains current stock value from http://www.pcquote.com for a stock. haven't tried it but it will use web query and update any stocks you add to your list.
- Willow Solutions Tips for Excel and Word, downloadable files demonstrating live stock information, Tip 3 Displaying color coded up/down arrows in Excel with Reuters data.
- Financial Calculators written in Java (or maybe not, broken link, rest of Tushar's pages are intact) by Tushar Mehta, some Excel and unrelated VBA examples.
- Martindale's Calculators On-Line Center, look for Stocks
Currency Conversions (#currency)
This page was introduced on December 23, 1999.
[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved