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.

How to re-import the same data without downloading them again

If we have already downloaded several years of historical data using AmiQuote and for any reason we need to re-import this data once again (e.g. into a new database) – there is a way to avoid re-downloading the whole history.

AmiQuote stores all downloaded data files inside of the dedicated destination folder. The exact location can be checked from AmiQuote: Tools->Settings menu:

AmiQuote settings

All recently downloaded files are stored in this folder and we can use ASCII importer in AmiBroker to pick these files manually for import. To do that please follow the steps below:

  1. Select File->Import ASCII
  2. In the open file dialog navigate to Download folder
  3. From Files of type field pick correct import definition to match the source used in AmiQuote:
    Import ASCII

  4. mark the .AQH files we want to import (to select all, it is enough to click on any of the files and hit CTRL+A, multiple file selection is also possible by clicking with CTRL or SHIFT keys pressed)
  5. press Open button to start import procedure

It is important to remember that by default each new download via AmiQuote overwrites the earlier files, so the AQH files will contain quotes from the very last download for given symbol.

More information about using ASCII importer can be found here:
http://www.amibroker.com/guide/h_amiquote.html
http://www.amibroker.com/guide/d_ascii.html

How to verify if EOD data is available for download at Google Finance

AmiQuote downloader allows to get free quotes from a number of sources included Google Finance. AmiQuote works like a specialized web-browser, so the quotations can be downloaded if they are accessible at the website of that particular data-vendor. In case of Google Finance downloads, not every symbol that is present on their site is available for historical download.

Google Finance page does NOT allow downloading historical data for non-US (international) symbols and some indices.

If you have trouble downloading particular symbol (you are getting errors in AmiQuote), chances are that Google Finance does not allow downloading data for this symbol. To verify that we need to look for Download to spreadsheet link on Google Finance page as instructed below:

  1. Visit http://finance.google.com/
  2. Use Search field to find the symbol we need, e.g. MSFT
  3. Go to Historical Prices section and look for Export: Download to spreadsheet

Historial Prices

If Export: Download to spreadsheet is present, it means that historical data are available for download. If the link is NOT present – it means no data for download. As you can see in the picture below, the link is present for US stocks like MSFT:

Historial Prices

But Download to spreadsheet link is missing for SP500 and PLC (Traded on London Exchange) – as you can see using these two links:
Historical Prices page for SP500 (no download link)
Historical Prices page for TESCO, PLC (no download link)

AmiQuote and free data from Yahoo

There are a couple of things you need to know about Yahoo Finance pages that AmiQuote uses to download “historical” and “current” quotes.

Current quotes are quotes for current day (or previous day if there is no trading session today). For example MSFT current quote page is here:
http://finance.yahoo.com/q?s=MSFT
AmiQuote uses rather “download data” link which is: http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=sl1d1t1c1ohgv&e=.csv
But it is not relevant because both show same current quote.

Now there is a second source. Historical quotes are downloaded from Historical Prices page. For example MSFT historical page is here: https://finance.yahoo.com/quote/MSFT/history?p=MSFT (again AmiQuote uses rather plain text link “Download data” (URL is dynamic, so you must click on “Download data” link on Yahoo page)

Why using two sources? That’s simple: current mode gives data during trading session, while historical is only updated many hours after markets close so both compliment each other. Current mode is also much faster as it downloads as many as 200 symbols at once, while historical must download one by one. So recommended usage is to use Yahoo Current mode everyday, and Yahoo Historical once a week.
Of course you may use historical everyday as well if you have time and fast internet connection.

It is important to understand that AmiQuote is just the downloader (like Internet Explorer) and it does nothing except downloading the data, so if you belive that there is a bad quote – it is not AmiQuote, but rather Yahoo problem. To verify always go to relevant page (see links above) and check the quote on Yahoo Finance site directly.

It may happen that quotes on Yahoo Current page and Yahoo Historical pages differ. It is so because Yahoo gets them from different data vendors. If this happens the only solution is to report data error to Yahoo.

There are however 2 things you need to know about importing of data:

  1. AmiBroker by default imports split adjusted data (“Open, High, Low, Close” on Yahoo Historical page). For more information on how data are adjusted see Yahoo Help page at: http://help.yahoo.com/l/us/yahoo/finance/quotes/quote-12.html
  2. You can change it to import split-and-dividend adjusted data (“Adj. Close” on Yahoo Historical Page) as described below

These things are adjustable, so if you don’t like them, you can change them.
The import process of historical quotes is controlled using aqh.format file that you will find inside “Formats” subfolder. By default it looks as follows (you can open it with Windows Notepad).

# AmiQuote historical quotes download format (.AQH extension)
$FORMAT Date_DMY,Open,High,Low,Close,Skip,Volume
$SKIPLINES 0
$BREAKONERR 0
$SEPARATOR ,
$DEBUG 1
$AUTOADD 1
$CONT 1
$GROUP 254

Lines marked with bold mark important areas.

$FORMAT line controls the import format.
Yahoo currently delivers only ADJUSTED data. But they are adjusted in two ways. First way is just adjusting for splits. And these data are downloaded by default.
Additionally Yahoo has “Adj. Close” column that provides close price adjusted for splits AND dividends.
You can download data adjusted for splits AND dividends by changing aqh.format file.
AdjClose field says that AmiBroker should use adjusted price. If you want prices adjusted for both splits and dividends simply replace $FORMAT line with:

$FORMAT Date_DMY,Open,High,Low,Close,AdjClose,Volume
(note AdjClose in place of Skip field)

$VOLFACTOR line controls the volume multiplier. If you want volume to be expressed in single shares instead of hundreds of shares replace $VOLFACTOR line with:

$VOLFACTOR 1

The same $VOLFACTOR change should be applied to aqd.format file that is responsible for importing data in Yahoo CURRENT mode (if you are using it).

How to use your LiveCharts account with AmiQuote ?

UPDATE April 2, 2008: We received the following information from eSignal:
” It wasn’t our intention to have this product be made compatible with 3rd party software[…] The new version of LiveCharts uses a different method of exporting data and it will no longer work with AmiQuote starting the week of April 14th, 2008.”

This effectivelly means that the instructions below will become obsolete April 14th, 2008.

You can use your Livecharts ($9.95/month) subscription to feed AmiQuote with real time data from Lycos. However, there is one extra step involved in order to make it work.
(more…)