Users' Knowledge Base

Jim, who is a long term AmiBroker user, regularly provides an updated template that automatically sets up a database of all US stocks available from Yahoo!Finance.

The stocks are initially categorized by Market and then as Stocks, Indexes or Funds at the Group level. They are also organized into Sectors, using the Hemscott classification system.

A link to the latest message from Jim is available at AmiBrokerYahooGroup message # 119057 – "AmiBroker/Yahoo Database File":  http://finance.groups.yahoo.com/group/amibroker/message/119057

Or link directly to the site to obtain the download:

http://www.icc-az.com

Go to Links >> AmiBroker at that site (instructions are available at the download page).

After the database is setup, use AmiQuote to download data from the Yahoo Historical server (allow approximately 1/2 – 1 hour download time, using a mid speed broadband connection, per year of history).

The length of back history available varies with each symbol (the author has downloaded up to 10 years history using this template).

Typically the ticker list, as setup, will contain a couple of hundred redundant symbols (symbols that return an error message when attempting to download historical data for them). Most of the redundant symbols are .OB (over the counter stock) and are not relevant to the majority of traders. Users can elect to delete them from the database or go to the Yahoo site, or other sites, to find the cause of the ‘download error’ e.g. the stock may have been de-listed or undergone a name change .

Instructions on how to find and delete the redundant (NoQuotes) symbols can be found in the UKB post >> Setup A Custom Database – Nasdaq

Information, or discussion, on the pros and cons of using Yahoo data is included in articles in the Yahoo sub-category at the Users’ Knowledge Base:

http://www.amibroker.org/userkb/?cat=99

Further information, about using the US-Stocks database, can be obtained by searching the AmiBrokerYahooGroup message board (use an Advanced Search and enter Subject contains "US-Stocks" OR "AmiBroker/Yahoo Database" as the search criteria .

_{Version 2 – Feb 02/2008 – by Brian_z – written using Windows Live Writer – updated links to current message and new download site}

Filed by brian_z at 10:03 pm under Yahoo
Comments Off on US-Stocks Database (v2)

An unsorted, unqualified list of FAQ’s relating to anything and everything to do with Yahoo! Finance data.

The list is dynamic and subject to change without notice.

Comments, where included, are the opinion of the individuals concerned and are not necessarily correct. In some cases they may be ‘out of date’ as circumstances change with time.

CAUTION – SOME OF THE LINKS CONTAIN COMMERCIAL REFERENCES OR LINK TO COMMERCIAL SITES.

NOTE: THE AUTHOR HAS NO COMMERCIAL AFFILIATIONS OF ANY KIND AND RECEIVES NO GRATUITIES, OR BENEFITS, FROM ANY COMMERCIAL ORGANIZATION OR INDIVIDUAL INVOLVED IN COMMERCIAL ACTIVITIES. NOR IS HE PERSONALLY ENGAGED IN ANY COMMERCIAL ACTIVITIES, RELATED TO TRADING, IN ANY WAY. THE LINKS ARE PROVIDED AS A RESOURCE AND FOR EDUCATIONAL PURPOSES. THEY ARE NOT A RECOMMENDATION, ON THE PART OF THE AUTHOR, NOR DO THEY CONSTITUTE INVESTMENT ADVICE AND SHOULD NOT BE CONSTRUED AS SUCH. 

Q7. I want to use Yahoo data for the ASX. Is it accurate?

A7. The use of Yahoo data for ASX stocks is not good. 

1.    No delisted history – any system testing you do based on Yahoo data is subject to population bias.  ie you can only test on the stocks that are currently listed.

2.    Significant inaccuracies for any stock exchange that trades in increments less than 1c.  This includes the Australian Stock Exchange (ASX), Singapore Exchange, some NASDAQ stocks and most OTCBB & Pink Sheet stocks.  This happens because Yahoo’s data is rounded to the nearest cent.  For example, there are 14000 listed instruments on the ASX.  Over 8000 of them are currently trading below 20c which means they move in either 0.5 or 0.1c increments.  Since Yahoo only prices in cents this introduce VERY SIGNIFICANT innacuracies.  – eg. a 2.6c shown as 3c represents a 15% error). 

3.    Normal dividends are adjusted by subtracting all historical data by the dividend amount.  Exchanges such as the ASX do not adjust price data for standard dividends.  By diluting it the way they do, any technical analysis or back-testing no longer shows a correct % return, nor does it show critical technical levels such as support and resistance.

4.    No sector information – critical if you want to perform sector-based analysis.

5.    Significant number of missing stock splits and other capital adjustments.

Re the rounding, if the data is adjusted (for splits/rights issues etc.) then you want plenty of decimal places so your testing shows the correct % gain.  In years past, the ASX had a different mechanism for determining the open and closing price of a stock, ending up with a weighted average of the bids/asks to come up with one open/close price (which would often be a fractional amount).  They changed this method a few years ago to more of a volume-matching method so that it’s a “normal” trading level.

Richard Dale.

Norgate Investor Services
– Premium quality Stock, Futures and Foreign Exchange Data for
  markets in Australia, Asia, Canada, Europe, UK & USA –
www.premiumdata.net

AmiBrokerYahooGroup message#68314 “Yahoo EOD data not always correct ?” http://finance.groups.yahoo.com/group/amibroker/message/68314

Q6. I am downloading EOD data from Yahoo to an ASX 200 group on a daily basis and I am also using another source. I have noticed discrepancies in quotes between these sources. It appears that Yahoo is incorrect. Sometimes it can be 5 cents or more difference.

Differences may be because one source is adjusting for dividends while the other is not. AmiQuote/Yahoo is adjusting, by default, for splits and
dividends but you can turn adjustment off by editing Formats\aqh.format file (using Notepad) and removing ADJCLOSE field.

Tomasz Janeczko – amibroker.com

 Q5. For two days in a row Yahoo data for US stocks retrieved with Amiquote has been very spotty – data comes through for some symbols but not
others?

A5. This is a Yahoo problem. For example if a stock is missing the LOW price of the day AmiQuote, by default, does not import data with missing LOW-HIGH prices.
You can however turn this off – however LOW price will be still missing – in fact it will use lowest of open/close as a low).

To accept it, despite missing LOW, you can go to aqd.format file (inside Formats subdirectory) and remove $STRICT 1 line.

Tomasz Janeczko – AmiBroker.com

AmiBrokerYahooGroup  message #116918     “Yahoo Data Very Spotty Last Two Days”  http://finance.groups.yahoo.com/group/amibroker/message/116918

Q4. AmiQuote does not automatically list tickers for the exchanges supported by Yahoo!Finance. Are there any other Yahoo down-loaders, compatible with AmiBroker, that do?

A4. Yes, there are at least two that the author is aware of. They are both commercially available.

a) Bodhi Freeway (formerly Bodhi Gold) is a down-loader, with Yahoo capability, that allow users to select the tickers they want to download. It is distributed by JustData and is free when bundled with their data subscription packages).

http://www.justdata.com.au/Products/BodhiFreeway/bg_yahoo.htm

They produce data files in the Metastock format that can be dynamically referenced in AmiBroker using the Metastock plugin.

http://www.justdata.com.au/Training/Tutorials/Bodhi3/amibroker.htm

Sector classification is not available with Yahoo international data.

At the time of writing, Bodhi Freeway, to purchase as a stand-alone product is approximately twice the price of AmiQuote.

b) MLDownloader

http://www.trading-tools.com/

Download End-of-Day stock quotes on 50+ exchanges worldwide. Forex, commodity futures, market indices, mutual funds, money market funds, ETF, corporate bonds, and metals are also supported.
Stores data in MetaStock or in the form of ASCII files.
Supports all common MetaStock formats (Version 6.52, 8.0, 9.0 and 10.0 (6000 securities per directory)).
Stock splits are performed automatically.
Notification when dividends were distributed.
Automatic update of indices and maintenance of your symbol lists.
Download attempts can easily be repeated – Detects symbol changes.
Downloading data can be scheduled.
Automatically connecting, downloading and disconnecting for analogue modem users.
MLDownloader supports multiple stock quotes servers like Yahoo, MSN.com or Quote.com
Easy way to create symbol list from existing MetaStock database and continue updating historical EOD data.
Auto-re-download of missing data.
Option of Daily, Weekly and Monthly or Intraday data.

At the time of writing, MLD, is approximately twice the price of AmiQuote.

Q3. I am having problems with the US-Stocks database. There are 8630 symbols in the database. Yahoo has no historical data for over 300 of them. Amiquote puts ‘junk’ (html 404 error) in the data file for missing historical data.

A3. Authors comments – Not all download errors are caused by a lack of historical data. Sometimes this occurs because there have been symbol (name) changes or companies have restructured in some way (mergers, de-listings) and this is not reflected in the Yahoo database. Typically the majority of them are .OB (Bulletin Board) stock. This is not an error caused by AmiQuote, AmiBroker or the US _Stocks database.

Within AmiBroker they can be reported as NoQuotes and sent to a Watch list by using the Database Purify Tool (they have been sorted into a NoQuotes watch list by the developer of the US_Stocks template). They can be deleted from the database by deleting them from the Watch list, or alternatively users can check the Yahoo site to find the reason for the ‘failure to download’ and make repairs to the database where necessary. 

Examples of how to use the Database Purification Tool, and other a tips and tricks to help with quality control of databases, have been documented in this section of the Users’ Knowledge Base.

Q2. I recently purchased AmiBroker & downloaded historical quotes from Yahoo. The volume display will not display values greater than 2,147,483,648.00 for indexes. All the recent volume values stop at this level. Is there a way to increase the display limit for volume?

A2. 2,147,483,648 is the upper limit for integers in AmiBroker. The volume, as it is imported from Yahoo, can be adjusted down using decimal factors.  Refer to the AmiBroker Knowledge Base article http://www.amibroker.com/kb/index.php?s=Yahoo by Tomasz Janeczko.  Also search the Yahoo message board archives, or the Help manual, for $volfactor to find additional information.

AmiBrokerYahooGroup  message #116336     “Volume Display Stops at 2,147,483,648.00 on Indexes”    http://finance.groups.yahoo.com/group/amibroker/message/116336

Q1. Is there a template for downloading Yahoo data, plus sectors, for the international  markets like Germany or India?

A1. Authors comments – sorry but it appears not (unless someone comes forward and tells us otherwise). Generally the products and services available for the American market are superior to the other Yahoo supported markets e.g. for the American market a database is maintained, including sector classification, for approximately 8000 stocks (the sector  information can be found on the Yahoo!Finance Profile page for each stock).

By comparison, the Company information, including the Profile, is not available for the leading German or Indian stocks, let alone a range representative of those market as a whole.

Germany – the top 30 company Adidas has no active links in the Company section.

India – the top 100 company Bank of India has no active links in the Company section.

Further to that, many of the Y!Finance international sites do not provide constituent lists for ‘local’ indices.

The only way around this appears to be:

• – for people to dig in and find some local information to use,
• – use the global indices constituents for international markets,
• – ‘practice’ on the American markets then ‘purchase’ local data, for comparison and trading, later,
• – or, pay for the information.

Unfortunately the trend is towards ‘pay for play’ e.g. index lists for German stocks are downloadable from the Xetra site http://deutsche-boerse.com/dbag/dispatch/en/kir/gdb_navigation/technology/20_Applications/10_Trading/10_Xetra_Trading_Platform but only on a subscription basis.

Standard and Poors maintain global indices that cover 70-80% of the equity market for the major international regions. Constituent lists, including GIC’s classifications, are downloadable from their site but only approximately 30-40 German companies are deemed liquid enough to make that list.

http://www2.standardandpoors.com/portal/site/sp/en/us/page.home/home/1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0.html

Alternatively, “If you can’t beat them, join them”.  Consider trading the American market and enjoy the benefits of participation in a liquid market (commissions might be cheaper than trading in your own country).

AmiBrokerYahooGroup message #116257  “Flags to get industry data from yahoo”  http://finance.groups.yahoo.com/group/amibroker/message/116257

Filed by brian_z at 6:47 am under Yahoo
Comments Off on Yahoo Data FAQ’s

Yahoo sources data from a number of providers who are acknowledged at the bottom of the Y!Finance page: http://finance.yahoo.com/ 

The partner page link in that section opens a list of delayed intra-day data suppliers: http://finance.yahoo.com/exchanges

As well as that, other providers are acknowledged in other places e.g. the S&P500 index is provided by Comstock and industry information, “on 7500 publicly traded U.S. companies”, is from Hemscott Americas etc.

The obvious point is that there are, at the least, two historical quotes databases, a fundamental database and several delayed intra-day data streams used, in different ways and in different places, at the site and there is no relationship between them in anyway. Some pages are a compendium, sourced from different providers, and it is difficult to be certain, in some cases, what is coming from where.

The main points to be aware of:

• 1) The intra-day quotes, for listings on the major US exchanges, come directly from the exchanges.
• 2) Intra-day quotes, for non-U.S. listings, are supplied by third party providers e.g. Comstock and Telekurs.
• 3) Fundamental data, for American exchange listings, is supplied by Capital IQ, a division of Standard and Poor’s.

Note: The Capital IQ  database is the logical choice as the underlying database for two stock screeners, although this is not confirmed at the site:

Java screener – http://screener.finance.yahoo.com/newscreener.html  

HTML screener –  http://screener.finance.yahoo.com/stocks.html 

At the time of writing the HTML database contains 8467 listings, but sadly only allows users to view screened lists 20 at a time, while the newer Java screener, which has superior exporting abilities, only references NYSE, Nasdaq and Amex stock i.e. 4370 listings.

Bulletin Board (.OB) listings are included in the HTML screener database but Pink Sheets symbols (.PK) are not (to obtain all listings for the databases run a filter using price >= 0 as the criteria).

• 4) Historical data, for the U.S. markets, is supplied by Commodity Service Inc (CSI) and is updated after hours (in a trial conducted by the author the update was logged at 8 PM EST (USA) i.e. four hours after the close).
• 5) Historical and delayed intra-day data for the non-U.S. markets is supplied by Hemscott Americas (historical data is updated after hours).

Note: International listings require a suffix to identify the exchange.

For a list of suffixes for each exchange refer to: http://finance.yahoo.com/exchanges

• 6) The stock listings in the CSI (historical) database are not the same as for the Capital IQ (fundamental) database, or any other database for that matter. 
• 7) Only one of the ‘databases’ has a downloadable index, albeit a limited download, while access to other resources at the site is on a symbol by symbol basis.

There is, however, the ‘mother of all indexes’ at: http://biz.yahoo.com/i/ (it seems likely that this index is provided by CSI, based on it’s size and content e.g. it is mainly comprised of US and Canadian listings and the number of constituents also approximates the CSI universe.

The patchwork nature of the site sometimes produces surprising outcomes. 

One example:

Japanese financial services company Acom Co Ltd is an ADR traded on the OTC market. It is not a constituent of the HTML screened list, and it does not have a ‘Yahoo’ chart. A search for the company by name, using Symbol Lookup, sometimes returns a result ……. 

……….and at other times it doesn’t (pity about that).

 In spite of all that it does have quotation and historical data at the Yahoo site.

Note:  To go to Yahoo!Finance site and enter ACMUY.PK in the Enter Symbol(s) input box and click on GET QUOTES at any Y!Finance page.

Clicking on the Historical Prices link, in the Quotes section of the left hand side bar, will open approximately two years of data for this symbol. (Yes – Amiquote will download the data).

This is a result that is typical for OTC ADR’s.

All in all a mixed bag from Yahoo!Finance.

• _{Version 1 – Sept21/2007}
• _{Version 2 – Sept23/2007 – Corrected minor errors and added links.}

_{Written using Windows XPHome and Internet Explorer v7.}

Filed by brian_z at 12:22 pm under Yahoo
Comments Off on Yahoo’s Data Providers (v2)