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.

What a Financial program such as Quicken or Money will provide

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.

What you can do in Excel

Some Things you can do 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.

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

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

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

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.

Quotes, 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 OE5.  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

Historical Quotes

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

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

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

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. 

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 OE5 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]

Virus protection information can be found on my Outlook Express page.

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

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

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

Specific Information relating to Yahoo Quotes

A mish mash of Related and unrelated postings

Have concentrated on using Google Usenet Advanced Search
Google Usenet Advance Search [http://groups.google.com/advanced_group_search]
Old Deja Search Argument looked something
like this: (~s stock* | ~s quote) &! inventory (~s stock* | ~s quote) &! inventory

Some excerpts from the above

Calculators

Currency Conversions

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 mailto:DMcRitchie@msn.com.