amibroker

HomeKnowledge Base

Wrong close price in Yahoo data (no more?)

July 22, 2017 UPDATE :
Yahoo today changed the order of columns (swapped “Close” with “Adjusted Close”).
To fix, you need to edit aqh.format file (in Formats subdirectory).

Old format line


$FORMAT Date_DMY,Open,High,Low,Skip,Close,Volume

should be changed to:


$FORMAT Date_DMY,Open,High,Low,Close,Skip,Volume

A ready to use aqh.format file can be downloaded from here (right click and choose “Save target as…”). Place it into “Formats” subdirectory.

You may have noticed over last few days that on some symbols (stocks and ETF that pay dividends) close price is below open, high, low prices.

This is new Yahoo error due to the fact that they added “dividend adjustments” incorrectly
http://forum.amibroker.com/t/amiquote-3-15-is-coming/171/18

In the period May 17 – June 10, Yahoo was NOT adjusting for dividends so OHLC prices were adjusted for splits only.

Charts looked correct (albeit without dividend adjustments).

Then people sent complaints about missing dividend adjustments and Yahoo started adjusting for Dividends, but… only Close price, leaving High, Low, Open adjusted for Splits only.

Here is example of EWJ data downloaded from Yahoo:

02-08-2010, 39.000000, 39.360001, 38.880001, 9.840000, 35.239758, 4488800

The columns are as follows:

Date, AdjOpen, AdjHigh, AdjLow, RawClose, AdjCloseForSplitsAndDividends, Volume

As you can see adjusted for splits&dividends close price (35.239758) is lower than adjusted low (38.880001)

So here is where the mess is coming from.

Yahoo is adjusting High, Low, Open fields for splits but at the same time they are adjusting Close field for splits and dividends.

That is why you are getting Close field below OHL fields and strange looking charts.

Another example – this time straight from Yahoo web site.

Link:
https://finance.yahoo.com/quote/IP/history?period1=804549600&period2=807055200&interval=1d&filter=history&frequency=1d

What you get:

Wrong data on Yahoo

Clearly visible Adjusted Close is lower than Adjusted Low, because they subtract dividends from Close price, but don’t do that for High, Low, Open prices.

This needs to be fixed by Yahoo on their site. You need to complain to Yahoo to fix their mess.

There is already a thread that describes this data error on Yahoo web site https://forums.yahoo.net/t5/Yahoo-Finance-help/Historical-Data-Split-and-Dividend-Adjustment-Errors-in-EEM/td-p/279800 but apparently Yahoo does not see / understand that yet.

The are two possible ways to fix that on Yahoo side:
1. Yahoo needs to adjust Open, High, Low fields the same way as they are adjusting Close.
..or..
2. Yahoo needs to send RawOpen, RawHigh, RawLow instead of adjusted ones. Then adjusted OHL can be calculated on the fly by deriving split ratio from AdjClose/Close

Method 2 was used in the past (prior to May 17, 2017 changes). It is best solution as it gives access to both adjusted and unadjusted OHLC easily. But any method would do as long as OHLC fields are adjusted the same way (i.e. for both splits AND dividends)

How to install AmiQuote 3.14 correctly

UPDATE: AmiQuote 3.21 public version is available now.

AmiQuote 3.21 has been announced here: http://www.amibroker.com/devlog/2017/07/11/amiquote-3-21-released/

AmiQuote 3.21 is not a standalone installer, therefore – it requires AmiBroker installed on your computer and the installation path should point to the folder where AmiBroker is installed.

To run the installer – use the link at:
http://www.amibroker.com/bin/aq3210.exe (119KB) – 32 bit version
http://www.amibroker.com/bin/aq3210x64.exe (143KB) – 64 bit version

Make sure to download correct version. If you have 32-bit AmiBroker, use 32-bit AmiQuote. If you have 64-bit AmiBroker, use 64-bit AmiQuote. If you have both, install both.

32-bit version of AmiBroker

The correct folder is automatically detected if AmiBroker is installed so you don’t need to change it. The default installation path is the following:

Image1

64-bit version of AmiBroker

The correct folder is automatically detected if AmiBroker is installed so you don’t need to change it. The default installation path is the following:

Image1

TROUBLESHOOTING:

  1. If you are getting “Failed to update registry, use REGEDIT” message, then you need to run AmiQuote just once with administrator rights – to do so click on AmiQuote icon with right mouse button and select “Run As Administrator”. Do this just once. Don’t run as admin all the time because automatic import won’t work.
  2. If you are not able to download more than 2 years worth of data it means you entered too early “From” date. For example if you enter 1900 as “from” date you won’t get more than recent 2 years. But if you enter more resonable starting date such as year 2000, then you will be able to download 17 years worth of data
  3. AmiQuote 3.20 uses new method that should be independent from IE version installed, however if the download hangs on Windows XP – new Yahoo Finance pages are incompatible with Windows XP, and specifically with Internet Explorer 9 or lower that Windows XP shipped with. Windows XP is obsolete and not supported anymore by Yahoo. You need at least Internet Explorer 10 to be installed (Windows 7)
  4. If it still does not work – you did something wrong. The program works fine, as long above steps are PRECISELY done. Rinse and repeat until it clicks.

AmiQuote Yahoo Historical stopped working

In May 2017 Yahoo Finance started making changes to their web services. During this time certain services may be interrupted or broken.

Three AmiQuote functionalities are affected by recent Yahoo changes

  1. Yahoo Historical download – the old CSV download API is broken at Yahoo Finance. Fix is available in v3.14 and higher
  2. Yahoo Fundamental Extra – the API has been changed. Fix is available in v3.14 and higher
  3. Yahoo Intraday download – API does not respond. No fix available at the moment

You need to update to the most recent AmiQuote version announced here http://www.amibroker.com/devlog/ and available in the download section http://www.amibroker.com/download.html to be able to use Yahoo data again.

Where does AmiQuote save downloaded data?

AmiQuote is a companion program shipped with AmiBroker, which allows data from free resources, such as Yahoo Finance, Google Finance and others. Since it is a separate application, then it can work independently from AmiBroker and it saves data in text files stored in Destination Folder defined in Tools->Settings window:

aq download folder

AmiQuote can also communicate with AmiBroker using OLE automation and automatically import downloaded data into AmiBroker if Automatic Import option is selected:

aq download folder

AmiQuote will import data to the database, which is opened in AmiBroker at the time of import.

Additionally, if more than one instance of AmiBroker is opened at the same time with different databases loaded, then AQ will communicate with the instance that was launched first and will import data into the database opened in this instance of AmiBroker.

Do not exceed real-time symbol limit

When we subscribe to a real-time datasource, such as eSignal or IQFeed – our subscription package determines how many symbols we can access in realtime at the same time. The plugin configuration in File->Database Settings->Configure should match the subscription limit.

IQFeed:

rt config

eSignal:

rt config

As it is explained in the users guide here: http://www.amibroker.com/guide/h_rtsource.html – although AmiBroker is able to handle more symbols in the database than the streaming limit, we should not really exceed the RT subscription limits in continuous screening during session hours.

This is because if we do otherwise and try to access more symbols than our subscription covers, then it would requires lengthy process that includes:

  1. removing the oldest symbol from the streaming list
  2. adding the new one
  3. triggering backfill for the newly added stock to fill the historical data from last valid update that we already have
  4. streaming and displaying RT data.

Then such process will be repeated for each new symbol that is included in screening. As a result, that might cause various problems with the data source not able to handle that many backfill requests in a short time, additionally data-vendors may be pro-actively protecting their servers from abusing the streaming limits this way.

Therefore – it is highly recommended to stay within the subscription limits for real-time operation and scanning to avoid problems.

How to count symbols in given category

When we want to find out how many symbols belong to given category (such as watchlist) then for manual inspection, it is enough to hover the mouse cursor over the particular category name in the Symbols window and the information will be shown in a tooltip:

Category symbol count

If we want to check such information using AFL code, we could read the list of symbols returned with CategoryGetSymbols and by counting commas (which separate symbol names) find out the number of tickers.

A reusable function is presented below:

function CategoryCountSymbolscategorynumber )
{
   
count StrCount( list = CategoryGetSymbolscategorynumber ), ",");
   return 
IIf( list == ""0count );
}    

Title "Symbols in watchlist 0: " CategoryCountSymbolscategoryWatchlist)

How to create custom import definition for ASCII importer

When we use File->Import ASCII to import data, we can choose import file format using one pre-defined import format definitions. As it is explained in the manual (http://www.amibroker.com/guide/d_ascii.html) it is also possible to create our custom import definitions to match data we are trying to import. This article explains all the required steps.

The easiest method to create import definition is to use File->Import Wizard. In the first page, select at least one file in the format we want to import and on the second page configure columns:

Import Wizard page 2

This all easy when we are importing quotation data, but when we are importing non-quotation data such as category assignments, we can not select appropriate columns using Import Wizard. In such case we need to type-in appropriate $FORMAT command in the “Additional commands” field of Import Wizard.

For example if we have file with categories like this:

"DDD","3D Systems Corporation","Technology","Computer Software: Prepackaged Software",1
"MMM","3M Company","Health Care","Medical/Dental Instruments",1
"WBAI","500.com Limited","Consumer Services","Services-Misc. Amusement & Recreation",1
"WUBA","58.com Inc.","Technology","Computer Software: Programming, Data Processing",1
"AHC","A.H. Belo Corporation","Consumer Services","Newspapers/Magazines",1
"ATEN","A10 Networks, Inc.","Technology","Computer Communications Equipment",1
"AIR","AAR Corp.","Capital Goods","Aerospace",1
"AAN","Aaron's,  Inc.","Technology","Diversified Commercial Services",1

We need to add the following commands in the “Additional commands” field of Import Wizard

$FORMAT Ticker,FullName,SectorName,IndustryName,Group
$OVERWRITE 1
$CLEANSECTORS 1
$SORTSECTORS 1

First line tells AmiBroker the column meaning, second line tells it to overwrite existing data. Last two lines tell AmiBroker to wipe existing category structure and sort imported sectors alphabetically. Be sure NOT to specify $CLEANSECTORS command when you do NOT want to wipe existing category structure.

We also need to mark “No quotation data” box in the second page of Import wizard to tell AmiBroker that the file that we are importing does not contain quotes and it should switch off all price checking.

Import Wizard page 2

Finally, in the last step of the wizard save the format definition:

Import Wizard page 3

Once we do this, next time we use File->Import ASCII a new selection My own format will appear in the Files of type combo box in the file selector dialog.

Import ASCII

It is worth noting that import definitions are plain text files that are stored in “Formats” subfolder of AmiBroker directory, and the list of available import definitions that appears in “Files of type” combo box, is also a plain text file called “import.types” that is located in the same subfolder. So, advanced users may also modify those files directly using plain text editor such as Notepad. It is all explained in great detail in the manual http://www.amibroker.com/guide/d_ascii.html

How to combine multiple databases into one

In order to combine data stored in two separate databases within a single database we may consider one of the following options:

ASCII IMPORT/EXPORT METHOD

First of the possible ways is to export data from one database into CSV files using the procedure presented here:

http://www.amibroker.com/kb/2006/03/04/how-to-export-quotations-from-amibroker-to-csv-file/

Once we have our quotations exported into text files, we can load the other database and use built in ASCII importer to import data. The detailed procedure is outlined in the following Knowledge Base Article:

http://www.amibroker.com/kb/2014/12/29/importing-data-using-multiple-column-separators/

FILE COPY METHOD

Another way of combining the databases is to copy the individual symbols files. Each database stores individual data files within 0-9,a-z,”_” subfolders and it is possible to copy the individual data files between databases. When copying, we need to maintain the same folder pattern and copy symbols from “a” subfolder of one database into “a” subfolder of the other database (the same for other folders), so each of the symbols would land in its respective folder.

After we copy the data files, we also need to delete broker.master file from the target database folder. This is because this file stores pre-generated symbol list used for fast loading. When we delete the file, it will be reconstructed based on information from individual data files.

More information about the database structure can be found in the manual:
http://www.amibroker.com/guide/h_workspace.html

How to categorize symbols coming from Metastock databases

Many data vendors that deliver data in MetaStock database format offer quotes in separate MS databases organized in several folders, separate for different markets or industries.

AmiBroker does not have any symbol limits, there is no need to maintain separate databases in AB and all the quotes can be placed in a single database. The configuration process of Metastock database as external data source is described here: http://www.amibroker.com/guide/h_extsources.html

AmiBroker supports internally many ways to categorise symbols into groups, markets, sectors, industries, etc. To learn more about categories available in AmiBroker please check this: http://www.amibroker.com/guide/h_categories.html

Now we may want to bring MS folder structure into AmiBroker’s category system. Sometimes data vendor would prepare appropriate automation scripts to do that work for us, but when they are not available, we can arrange the categories ourselves in the initial setup process.

To do so, we could pick the folders one-by-one, then reassign the symbols to desired categories. The process is the following:

First we configure the database:

  • Select File->New->Database menu
  • Enter the folder name and press Create
  • Choose MetaStock plugin as the datasource
  • Press Configure

MS plugin config

Now we pick single MetaStock folder (AmiBroker allows to import them all at once, however we want to avoid mixing the symbols from various folders) and press Retrieve button.

MS plugin config

After pressing Retrieve we can close the database configuration dialog (OK, then OK again), go to Symbol->Organize Assignments and reassign the newly imported symbols from Undefined market or Industry into our desired location.

MS plugin config

Now we can return to File->Database Settings->Configure, retrieve another folder and repeat the assignment for that folder and so on and so on.

It is important to mention that this is just one-time procedure. After it is done, AmiBroker will automatically read all updates directly from MetaStock files.

There are also data-vendors offering data in MS format (such as PremiumData for example) that deliver ready-to-use configuration scripts – in such case it would allow to avoid such manual setup procedure and synchronize all category assignments automatically.

“Invalid symbol” message for stocks traded on multiple exchanges (IB)

Some of the symbols available from Interactive Brokers data-feed may be traded on multiple exchanges. In such case, when we enter the symbol to the database as:

INTC
GLD
CSCO

then we may see “Invalid symbol” message displayed by the Interactive Brokers plugin, as symbol SMART-STK-USD default format is not enough to uniquely identify the symbol. Therefore we would need to specify the primary exchange for the given symbol.

The primary exchange can be specified in the symbol using -!EXCHANGE suffix, so we can write the symbol like this

INTC-!NASDAQ

and it will instruct Interactive Brokers plugin to send NASDAQ as Primary Exchange for INTC to TWS.

We can however maintain similar ticker naming for all symbols in our database and use short names for all US symbols. To make it possible there is special dictionary (or translation table) for IB symbols that both plugin and IB automated trading interface use. In AmiBroker main folder there is a IBDictionary.txt file, which we can edit with any plain text editor (like Notepad) and provide symbol translations. It describes how symbols are translated before plugin communicates with TWS API.

The format of this file is CSV (comma separated values):

InputSymbol,OutputSymbol, comment (optional)

For example:

INTC,INTC-!NASDAQ, This is because INTC is now traded on multiple exchanges, so we need to set primary exchange
CSCO,CSCO-!NASDAQ, The same as above 
MSFT,MSFT-!NASDAQ
GLD,GLD-!ARCA
Next Page »