Friday, April 13, 2012

Using Yahoo Free EOD Data

Yahoo is an excellent source for free End of Day (EOD) data but it comes with some issues from time to time.

Since Yahoo is not providing a true data service, we can't expect them to have 100% up-time or to have 100% error free EOD data.   From time to time there are issues. 

I have created a number of watchlists in my Database which help me manage symbols.
Most of the watchlists above are self explanatory, I do want to point out the DB - Yahoo Issues watchlist though as this is the WL that I put all symbols that Yahoo seems to have an issue with.  Sometimes a symbol will stop updating, or have a bad data point (spike), or missing data, or it could be a new symbol and it doesn't have any historical data as of yet.

Yahoo has three types of data servers.
- Current holds the delayed intraday data
- Historical holds the official closing prices and is available sometimes not till 3 am the next day.
- Extended holds delayed (I think) premarket and after hours quotes.

When you bring up AmiQuote you get to select which server you want to obtain data from, the Yahoo Current (Server) or the Yahoo Historical (Server).

You can download at any time of the day during market hours - the data will be about 15 to 20 minutes delayed.

At approximately 30 minutes after the market closes (4:30 eastern) you can download once again (Yahoo Current) and those prices will be pretty close to if not the official closing prices.

At around 8 PM Eastern the Historical servers are being filled up with the day's official OHLCV data. 

What I do each day is perform a Current download around 4:30 PM or later so I can run explorations, etc.

Then the next morning I perform a Historical download for the previous day (1 day only).  This catches all of the official closing prices as well as prices for some Indexes that are not updated till wee morning hours.


Monday, March 12, 2012

Keeping the US Stocks Database Up to Date - Finding dead symbols


As I discussed in a previous post - the market is in a constant state of change.  Companies are being bought out, going private, or may even go bankrupt (stop trading).  Their symbols will either be removed from the exchanges (in the case of a buyout or merger) or may change as they get moved from one of the major exchanges down to the OTC BB or Pink Sheets.  Then there are companies that go public via the IPO process and therefore those symbols will have to be added to the database.

For now let's discuss how to spot those symbols which have stopped trading and how to investigate what actions need to be taken.

Take this code and save it with the name "Closing Prices"
// --------------------- Title Section ----------------------------
//  Title:           Closing Prices for Active Symbols  
//  by:              MarketMonk  
//  last rev date:   08-06-2011 

// --------------------- Filter Section --------------------------- 
Filter = 1

// --------------------- Display Results Section ------------------  
AddColumn(C, "Close");      // display Closing price  
AddTextColumn(FullName(), "   Name", 10.0, colorWhite,colorBlue, 320);

Once a week (or more) you can run the above code in an exploration against various Groups
- Common Stocks
- ETFs
- CEFs
- Indicies
 Typically the majority of changes will be coming from Stock symbols, the other groups don't change all that often.

Here are the steps you need to take to run the code above as an exploration:
First - Start up AmiBroker, then click on the Automatic Analysis icon as shown circled below
You should see the AA tab open up as shown below.
You will need to select the AFL code from above that was saved.  So use the open folder icon to point to it.  Then you will need to click on the filter icon that looks like a green funnel (circled) as shown below.
This will open the filter window and from which you will typically select the group called Stocks - Common.  You will then make sure the Range setting is set at 1 recent days (don't worry about the starting and ending date as those are ignored).   Click OK to accept these settings.

Once the Filter settings window closes you then click the Explore button to start your exploration.
Depending on your computer it should not take too long to get the results as shown below:
The results are sorted by symbol, we need to click on the Date/Time column heading to have them sorted by Date.   If all is okay you should not see any dates that are different.   But if you do have symbols that are no longer being updated in your database they will be listed at the top.
As you can see, in my database I have 2 symbols that are not being updated.  If I double click on BQI it will load up that symbol in my chart window, tabbing over to that window I see the following:
As you can see BQI has stopped trading for some unknown reason.  Now the task is to investigate why and then determine what actions to take.

The way I perform research is to look at two websites, Yahoo and Finviz, via the built in feature called Web Research.  I did a nice post about this here.  There are two ways to open up a web research window.
You can either click on the + (plus symbol) next to the Analysis tab or click on the "e" icon circled above.   Doing so will open a window bringing up internet explorer to the first web site in your listing of sites.   The first on in my list is Yahoo so the window below opens:
As you can see it stopped trading back on Nov 28th, 2011.  In reading the news headings it sounds like this company was halted from trading due to some litigation.  At this point you can either remove the symbol from your database - or place it in one of the watchlists I included in the US Stocks Database that will allow you to track this symbol.
You could add BQI to the DB - Halted or InActive symbols watchlist if you feel that the company is worth keeping in your database.   Otherwise just delete the symbol.

I will update this post with additional examples as they present themselves.

In the mean time, trade well.

Sunday, March 11, 2012

Installing the US Stocks Database

In this post I wanted to document the steps you need to take to get the database up and running.

I supply the following 4 zip files in an email to you once I receive payment.
The main file is the USDB which is the blank database consisting of symbols only (no price data).
Then there is a zip file called Root Folder which contains files that set up the database to have the predefined categories of Markets, Groups, Sectors, Industries, etc.
Then there is a file called Format Files which contains the necessary files which allow the downloading of price data and not the symbol description (as I want to maintain control over that aspect).
Then there is a file called Notes which contains custom notes that I have created for various symbols.

Save these files to your desktop or some place easy to get to.
Step 1 – Installing the files:
close down AmiBroker then

Unzip the contents of the Notes file into the Notes sub folder of AmiBroker
Unzip the contents of the Format file into the Formats sub folder of AmiBroker
Unzip the contents of the Root folder file into the root directory of AmiBroker
Unzip the USDB file into the root directory of AmiBroker
then start up AmiBroker
Click on File
Click on Open Database
point to USDB folder and click ok
You now have a blank database
Step 2 – Downloading quotes (price data)
You will need to run AmiQuote to download historical data.
I suggest starting up AmiQuote from the Windows Start Menu
not from within AmiBroker (by clicking on Tools – then Auto Update)

1st I recommend setting up AmiQuote
Click on Tools, then click on Settings
in the box on the left labeled Misc Settings
Number of symbols per request ---- change to 200
Number of simultaneous downloads --- change to 10
then click the Ok button in upper right corner
(these new settings will vastly improve download speed and shorten your waiting time)
now make sure the Automatic Import box is checked
Select Yahoo Historical in the drop down box
Choose a starting date (as far back as you want data)

Next we need to retrieve the symbols from the database.
Click on "Tools"
Then click on "Get tickers from AmiBroker"
you should see a long list of tickers (symbols) appear below

And finally we retrieve the quotation data
And then click the Green Run Icon.
AmiQuote will proceed to download historical price data and import it into your database.

Once the download is finished you will have a fully populated database of symbols with quotes.
In the next series of posts I will describe how to keep your database clean and up to date.
Trade well,

Introducing the US Stocks Database

Over the years of using AmiBroker I have learned how to use data from Yahoo. This has allowed me to customize AmiBroker in ways that no other software would.  Plus it saved me a lot of money since the EOD data from Yahoo is FREE.

Creating your own database is not difficult at all - it's just very time consuming.  Especially if you are new to AmiBroker.   So before I go into a series of posts that explain step by step how to create and maintain your own database, I wanted to offer my US Stocks Database to everyone.

This US Stocks Database consists of 6,700+ symbols.  All symbols are fully assigned to their respective categories.  As I explained in this Post, AmiBroker allows us to put symbols into various Categories.

Here is a screen capture for the Markets:
I do have 1 OTC-BB, 1 OTC-Pink, and 1 Mutual Fund symbol in the database just so they will show those Markets above.  Feel free to delete them or add more if desired.

Here is a screen capture for the Groups:
Here is a screen capture for the Sectors:
There are 9 main Sectors, I added N/A because Indices needed a place to be categorized at this level.  I also added an Undefined Sector because when you add symbols to your database they are automatically added to Sector 0 and Industry 0 (both of which are identified as Undefined).  I also used this part of AmiBroker to break out ETFs and CEFs.

Here is a screen shot for some of the Industries (which are assigned to a Sector):
The Basic Materials Sector consists of a number of Industries.   Then each of these Industries have symbols that are assigned to them.

The database has over 270 Indexes.  Of which I have them broken down into various groups.  One of which is the Industry Classification Benchmark (ICB) Indexes.
 As you can see I have modified the description of each symbol for these Indices (as well as other Indices and ETFs) to include some key information (IMHO) and a cross reference to a tradeable ETF.

With the database I include a Zip file with the notes for a good majority of these Indices with information like show above in the Notepad section.  (work in progress effort though).

I am charging $50 for this database.  Just shoot me an email if you are interested in obtaining the DB.

In the next post I discuss how get this US Stocks Database installed and up and running here.


Saturday, September 3, 2011

Symbols, Symbols, and more Symbols

Before we discuss the why and how to create your own database we need to have a discussion about Symbols.

There are well over a 1,00,000 symbols available to us to use in trading. That number will surely surprise a lot of folks. There are symbols for Stocks (Equities), ETFs, CEFs, MLPs, Mutual Funds, Forex, Options, Futures, and Indices.

Just in the category of Stocks we have (per as of 9/5/11):
5,928 Common Stocks (unfortunately also includes an est. 573 CEFs and the MLPs)
158 Preferred Stocks
43 Warrants
3,193 OTC BB Stocks
24,083 OTC Pink Sheet Stocks
So that's 33,405 tradable equity symbols.

The majority of the retail traders/investors are quite familiar with stock symbols. Here is an excellent write up at Wikipedia on the history of symbols used for equities.

There are over 1,200 Exchange Traded Funds (ETFs) and the number is growing as they offer significant advantages over Mutual Funds. Speaking of Mutual Funds there are 23,332 (per

Now if we look at Options on Equities and ETFs, I quite conservatively calculate 750,000+ symbols. There are over 3,000 equity symbols that have options trading on them per CBOE. For each underlying
- there are Calls and Puts.
- at least 25 strike prices.
- at least 5 expiration months or dates.
So here is my math - 3,000 x 2 x 25 x 5 = 750,000 options. There could easily be twice that # in reality. IMHO AmiBroker is not well suited for Options.

Forex is a global, worldwide decentralized financial market for trading currencies. There are over 900 Forex pairs (per AmiBroker is very well suited to trading Forex.

Per Wikipedia, a futures contract is a standardized contract between two parties to exchange a specified asset of standardized quantity and quality for a price agreed today (the futures price or the strike price) with delivery occurring at a specified future date, the delivery date. There are hundreds of futures related symbols and many times that of Options on Futures. AmiBroker is very well suited to trading Futures.

While not a tradable symbol, there are 1,000s of symbols of Indexes. One of the most famous index is that of the Dow Jones Industrials (^DJI at Yahoo). Indexes are created to track many aspects of the stock market. This is a major topic in and of itself that I will cover in a future post.

Successful investing/trading for the retail investor starts with a plan or methodology that identifies the markets (symbols) that are to be used.

I will discuss "why" to create your own database (versus paying for a service) in the next post.


Monday, August 29, 2011

AmiBroker and ICB

[Note: To go to the first post in this series about Industry Classification systems click here.]

In the previous post I introduced the Industry Classification System (ICB) and showed how Yahoo has the associated Indexes for each segment or classification. In this post I want to discuss how to assign the actual company symbols to their respective index.

Not too long ago AmiBroker added the capabilities to categorize symbols by the Global Industry Classification System (GICS). You can read more about GICS here.

The GICS categories and descriptions are provided by a simple "gics.txt" file located in the root directory of AmiBroker. Here is a partial listing:
10; Energy
1010; Energy
101010; Energy Equipment & Services
10101010; Oil & Gas Drilling; Drilling contractors or owners of drilling rigs that contract their services for drilling wells
10101020; Oil & Gas Equipment & Services; Manufacturers of equipment, including drilling rigs and equipment, and providers of supplies and services to companies involved in the drilling, evaluation and completion of oil and gas wells.
101020; Oil, Gas & Consumable Fuels
10102010; Integrated Oil & Gas; Integrated oil companies engaged in the exploration & production of oil and gas, as well as at least one other significant activity in either refining, marketing and transportation, or chemicals.
10102020; Oil & Gas Exploration & Production; Companies engaged in the exploration and production of oil and gas not classified elsewhere.
10102030; Oil & Gas Refining & Marketing; Companies engaged in the refining and marketing of oil, gas and/or refined products not classified in the Integrated Oil & Gas or Independent Power Producers & Energy Traders Sub-Industries.
10102040; Oil & Gas Storage & Transportation; Companies engaged in the storage and/or transportation of oil, gas and/or refined products. Includes diversified midstream natural gas companies facing competitive markets, oil and refined product pipelines, coal slurry pipelines and oil & gas shipping companies.
10102050; Coal & Consumable Fuels; Companies primarily involved in the production and mining of coal, related products and other consumable fuels related to the generation of energy. Excludes companies primarily producing gases classified in the Industrial Gases Sub-Industry.
15; Materials
1510; Materials
151010; Chemicals
[It looks better in text file with no line wrap].

To make AmiBroker ICB compliant we have to do a few things.
1) GICS uses 2 numbers per level (so by the 4th level it has 8 numbers - i.e. 10102050 Coal). ICB uses 1 digit per level. So we have to make up a number scheme to trick AmiBroker into accepting ICB as GICS.
2) Then we have to copy this scheme into the original GICS.TXT file.

Here is a partial listing of the ICB information mapped into a GICS format:
10; [1000] Basic Materials
1013; [1300] Chemicals
101350; [1350] Chemicals
10135030; [1353] Commodity Chemicals
10135070; [1357] Specialty Chemicals
1017; [1700] Basic Resources
101730; [1730] Forestry & Paper
10173030; [1733] Forestry
10173070; [1737] Paper
101750; [1750] Indus. Metals & Mining
10175030; [1753] Aluminum
10175050; [1755] Nonferrous Metals
10175070; [1757] Iron & Steel
101770; [1770] Mining
10177010; [1771] Coal
10177030; [1773] Diamonds & Gemstones
10177050; [1775] General Mining
10177070; [1777] Gold Mining
10177090; [1779] Platinum & Prec. Metals
As you can see, the first number is a GICS compatible number scheme. But the actual ICB index number is embedded into the description field and enclosed with the [ and ].

Below is a screenshot of the top level ICB categories as well as a chart of Agilent Technologies. Notice how just below the title it shows that A is assigned to the ICB Sub Sector 2737 Electronic Equipment.

To manually assign each symbol to their respective ICB category, you open up the Information window as shown below.

Then in the GICS field you click on it to bring up a drop down box that will now list all of the ICB categories (not GICS) and you scroll down to the one you want and click on it.

There is an automated way to do this as well.

Currently there is no paid data provider that I am aware of that offers a US Stocks Database with these symbols fully categorized according to ICB.

At least not yet ;-)

Trade well,

Sunday, August 28, 2011

Industry Classification Benchmark (ICB)

[Note: To go to the first post in this series about Industry Classification systems click here.]

The Industry Classification Benchmark (ICB) is an industry classification system developed by FTSE. It is used to segregate markets into sectors within the macroeconomy. The ICB uses a system of 10 industries, partitioned into 19 supersectors, which are further divided into 41 sectors, which then contain 114 subsectors.

You can download a well written PDF that details the structure here.

What I like about this system is that Yahoo has the associated Index symbols for each segment of the ICB and the historical data which allows us to plot it.

Symbols begin with ^DJUS and then have two additional letters to identify each of the indexes.

Above is a chart of the Basic Materials Index (ICB-1000 Industry Level).

This system not only provide much better granularity but also the historical price data which allows us to chart it and perform various relative strength analysis routines on it.

And it has been licensed to various ETF providers so we can invest in the various levels.

All of these ICB Index symbols have been loaded into a number of watchlists in my MM US Stocks Database.

Which allows us quick access to the indices by Industry.

[note: I have created custom Full Names for each of the indexes, and have turned off the retrieval of the Full Name (Company/ETF/Index description) that Yahoo provides because it's typically inadequate and Yahoo truncates it down to 16 characters]

The next post will be about setting up AmiBroker for ICB and getting symbols assigned to their respective categories, please click here.

To go to the first post in this series about Industry Classification systems click here.

To learn how about the various ways of categorizing symbols within AmiBroker click here.

Trade well,