Stocks, Quotes and keeping track of

Location:   http://www.mvps.org/dmcritchie/excel/stocks.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

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.

[Software], [Newsgroups], [Paper], [Quotes], [Historical], [Webbased],
[Privacy], [Fractional], [QIF], [Mishmash], [Excerpts], [Calculators], [Currency],

What a Financial program such as Quicken or Money will provide (#software)

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 #).

What you can do in Excel (#Excel)

Some Things you can do online (#online)

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.

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

Newsgroups (#newsgroups)

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.

Paper Records (#paper)

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).

Typical posting of mine, keeping Records (#records)

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.

Difference between investing and day-traders (#traders)

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.

Stock Quote, sites (#quotes)

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.  Free End of Day Quotes Free Download 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, volume

Download details: Excel 2003/2002 Add-in: MSN Money Stock Quotes,
Excel 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,52949400
Derived 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://my.yahoo.com
http://www.quicken.com
http://moneycentral.msn.com/investor/home.asp
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).

UCE
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 Web

Here'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: 
    http://groups.google.com/groups?oi=djq&ic=1&selm=an_576979014

Quicken Interchange File used by Quicken and MS Money (#qif)

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
  http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL=MSFT,IBM
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...

A mish mash of Related and unrelated postings (#mishmash)

Have concentrated on using Google Usenet Advanced Search
Google Usenet Advance 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)

Calculators   (#calculators)

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