amibroker

There is a number of data-plugins, where AmiBroker reads quotes directly from an external database maintained by the data-vendor (such as TeleChart, FastTrack, PremiumData or other MetaStock-based sources). Depending on settings, AmiBroker may or may not keep a copy of such data in its own database. For in-depth explanation how it works see the following tutorial chapter:

http://www.amibroker.com/guide/h_workspace.html

Now, if we want to backup the data from an external data source, and use them offline, first we need to make sure that actual data are copied into AmiBroker’s local database.

To do so, first go to File->Database Settings set Local Data Storage to Enabled. This instructs AmiBroker to keep its own copy of retrieved data.

At this point the data are not copied yet. In order to bring them from external data source to AmiBroker local database, we need to make sure that AmiBroker accesses quotes of all symbols at least once. Only then external source will be queried for data and that data will be copied to a local database. The easiest way to do so is to run a Scan from Analysis window over all symbols, using any scanning formula, even as simple as:

Such single-line of code can be entered in the Formula Editor, then selecting Tools–>Send to Analysis menu would bring up the Analysis window. At this point just make sure that Apply To is set to All Symbols and press Scan.

After this the data is copied to local database. If you want to completely disconnect from external data source, you can do so by changing Data source to “(local database)” in File->Database Settings. This would allow you to use the data on a different computer. Now you can do File->Save Database As… to store the database into new location (or backup drive).

If you want to re-connect to external data source, just switch the Data source back to original setting (i.e. the data plugin you were using before).

Filed by Tomasz Janeczko at 7:41 am under Data
Comments Off on How to backup data from an external source

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:

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:
   

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

Filed by Tomasz Janeczko at 4:46 pm under AmiQuote
Comments Off on How to re-import the same data without downloading them again

AmiBroker database structure offers the following fields: Open, High, Low, Close, Volume, OpenInt, Aux1, Aux2. The last two fields, i.e. Aux1 and Aux2 are meant for storing any custom historical data-arrays we may need.

Pretty often, we already have quotations data present in the database and we just want to put some extra data into auxiliary fields. To combine existing data with imported data, we can need to use hybrid mode. In the Import Wizard just add this command in “Additional commands’ field.

# This example assumes that file has format: 
# Symbol, Year-Month-Day, auxiliary_data1, auxiliary_data2
$FORMAT Name, Date_YMD, Aux1, Aux2
$HYBRID 1
$ALLOWNEG 1

Hybrid mode works so that with each imported record it looks for matching record in the database and it combines existing data with data being imported. If OHLC prices are not provided in the imported file you need to specify $ALLOWNEG 1 option. Otherwise you would get error messages about missing close price.

Auxiliary data fields can then be read using simply Aux1 and Aux2 identifiers:

However – if two additional fields are not enough for our purposes, we can also import quotes into some synthetic tickers and have another set of OHLC, V, OI, Aux1 and Aux2 fields available for importing. Synthetic ticker in this context means just a custom symbol name that’s used just for storing such extra data. So – instead of importing additional arrays into IBM ticker or AAPL ticker, we could use for example IBM_extra and AAPL_extra symbols and their fields.

Using such common naming pattern (i.e. identical ‘_extra’ suffix with the original ticker name) will be useful, because later on to access data from the selected field we could use just the following AFL call:

and this line will read value from Close field of the respective ‘extra’ ticker as we select IBM or AAPL.

An alternative way to store and handle several custom arrays would be to use SQL database, then we could use ODBC plugin to read such data. The documentation of ODBC plugin is available at:

http://www.amibroker.com/odbc.html

Filed by Tomasz Janeczko at 6:45 pm under Data
Comments Off on Importing auxilliary data into AmiBroker database

Apart from regular Open, High, Low, Close, Volume, OpenInt fields – AmiBroker database allows to store custom data in auxiliary fields called Aux1 and Aux2. This allows to import our custom arrays and store in the database.

Since the data stored in those fields will vary, depending on the actual records imported in there – then in case of time-compressed intervals we may need to determine how exactly these values are compressed if we e.g. switch from Daily to Weekly interval. By default these values would get compressed the same way as Close field, i.e. Last value from given period would be used. We can however choose specific compression method if we need these fields to behave differently (for example like Volume, where weekly record represents a Sum of daily volumes).

The compression mode for Aux1 and Aux2 can be defined in File->Database Settings->Intraday Settings dialog Aux1,2 aggregation mode field:

Filed by Tomasz Janeczko at 2:25 pm under Data
Comments Off on Choosing compression method for Aux1 and Aux2 fields

In order to delete quotations from a local database manually, we can use Quotations Editor (Symbol–>Quote Editor), then mark the required range of quotes and press Delete button. To mark a range – it is enough to click on the first line of the range, then scroll to the other line, hold SHIFT and click on the end-line of the range. To multi-select individual lines, hold down CTRL key while clicking on the lines.

There is also a way to delete quotations programmatically with use of OLE automation interface explained here:

http://www.amibroker.com/guide/objects.html

The following code presents how to do it using automation scripts (the code deletes all quotations of MSFT ticker):

// THIS IS NOT AFL
// This is Windows script to be run from the outside of AmiBroker
function RemoveAllQuotes( Name )
{
    AB = new ActiveXObject("Broker.Application");
    Stk = AB.Stocks( Name );
    Quotes = Stk.Quotations;
    iQty = Quotes.Count;
    for( i = iQty - 1; i >= 0; i-- )
    {
       Quotes.Remove( i );
    }
    AB.RefreshAll();
}
RemoveAllQuotes("MSFT");
WScript.Echo ( "Completed" );

The code above is intended to be used from the outside of AmiBroker.

To use above code follow these steps:

1. Open Notepad
2. Copy-paste above the code
3. Save the file with .JS extension (which means that system will treat this as JScript code)
4. Make sure that AmiBroker is running with desired chart as active one
5. Double click on .JS file to execute the JScript code

IMPORTANT: if you are running 64-bit Windows and have BOTH 32-bit and 64-bit versions of AmiBroker installed the OLE scripts by default would only talk to 64-bit instance. To use 32-bit version instead you would need to follow advice given in this article: “Running OLE automation scripts with 32- and 64-bit versions of AmiBroker”

Using the very same method you can delete quotes selectively, for example the script below deletes only quotes having zero volume:

// THIS IS NOT AFL
// This is Windows script to be run from the outside of AmiBroker
function RemoveQuotesWithZeroVolume( Name )
{
     AB = new ActiveXObject("Broker.Application");
     Stk = AB.Stocks( Name );
     Quotes = Stk.Quotations;
     iQty = Quotes.Count;
     cnt = 0;
     for( i = iQty - 1; i >= 0; i-- )
     {
        qt = Quotes.Item( i );
        if( qt.Volume == 0 ) 
        { 
           cnt++;
           Quotes.Remove( i );
        }
     }
    
     AB.RefreshAll();

     return cnt;
}
n = RemoveQuotesWithZeroVolume("MSFT");
WScript.Echo ( "Removed " + n + " quotes with zero volume" );

Filed by Tomasz Janeczko at 6:18 pm under Data
Comments Off on How to delete quotes without removing the symbol from a database

When we import data from ASCII (plain text) files, sometimes the data in the input files are arranged in columns separated by different characters. This article shows how to configure Import Wizard / ASCII Importer to import such data correctly.

Let us consider data file using the following format

Ticker,Date/Time,Open,High,Low,Close,Volume
EURUSD,2011-06-13 20:19:00,1.4421,1.4421,1.4419,1.4419,332
EURUSD,2011-06-13 20:20:00,1.4419,1.4419,1.4418,1.4418,298
EURUSD,2011-06-13 20:21:00,1.4418,1.4418,1.4416,1.4417,192


In this sample data file columns are separated with a comma, with one exception – date and time columns are combined together, with a space in between. ASCII importer requires us to specify Date and Time columns separately. Fortunately – there is a way to treat space and comma both as separators at the same time.

To configure Import Wizard, we need to specify columns to match the input data, treating Date and Time as two separate columns (Column 2 and Column 3 in this case), additionally we need to set Separator field as Comma or Space, so the importer would properly recognize space as a character delimiting the new column.

This way our data will be imported properly. There are also other multiple separator choices available in the Import Wizard, such as Tab or Space and Semicolon or Space that can be used if your data file uses tabs or semicolons as primary column separator.

If we build our import definition file manually for ASCII importer, we can also specify multiple separators, by enclosing the required characters in quotation marks in $SEPARATOR command. The equivalent format definition for the above input data would look like this:

$FORMAT Ticker, Date_YMD, Time, Open, High, Low, Close, Volume
$SKIPLINES 1
$SEPARATOR ", "
$CONT 1
$GROUP 255
$AUTOADD 1
$DEBUG 1


More information about ASCII importer and Import Wizard functionalities can be found in the manual:
http://www.amibroker.com/guide/w_impwizard.html
http://www.amibroker.com/guide/d_ascii.html

Filed by Tomasz Janeczko at 3:56 am under Data
Comments Off on Importing data using multiple column separators

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

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:

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)

Filed by Tomasz Janeczko at 6:33 am under AmiQuote
Comments Off on How to verify if EOD data is available for download at Google Finance

Sometimes Interactive Brokers symbology may be difficult to figure out. It is however very easy to find out proper symbol using Contract Description window in the TWS.

The general format for symbols that AmiBroker uses to access Interactive Brokers’ data is:

SYMBOL-EXCHANGE-TYPE-CURRENCY

The symbols must be entered in UPPER case. CURRENCY may be skipped if it is USD. TYPE can be skipped if it is STK (stock). EXCHANGE can be skipped if it is SMART.

To find correct symbol using TWS follow these steps:

1. Select desired symbol in TWS
2. Click on the line with right mouse button and select Contract Info->Description menu

   

3. Now read the values from the Description window and use them to build proper symbol for IB plugin

   

As per rules mentioned above, if CURRENCY is USD it may be skipped so we can use either ESZ4-GLOBEX-FUT-USD or ESZ4-GLOBEX-FUT.

For non-US symbols the procedure is the same, so TESCO PLC would be TSCO-SMART-STK-GBP. In this case we can not skip SMART and STK because we need to specify currency, and it is 4th part of the symbol. If we skipped SMART and STK, IB plugin would think that GBP is second part of dash delimited string and interpret it as exchange, and this is NOT what we want.

Filed by Tomasz Janeczko at 4:29 am under Data
Comments Off on How to find correct symbol for Interactive Brokers data

Some platforms, like Tradestation, use “future looking” timestamps in their data so for example 5 minute bar covering market activity from 9:30:00 till 9:34:59 is stamped with 9:35:00 (future time – see Tradestation manual explaining their timestamps) so their data have a kind of future offset.

AmiBroker, on the other hand, uses and expects natural timestamping, in which data from 9:30 are marked with 9:30 timestamp, so data from 9:30:00 until 9.34:59 belong to 9:30 5-minute bar.

If you want to use future-timestamped data in AmiBroker you need to remove the offset.

To do so, you need to shift the timestamps back by the amount equal to the base data interval during import process. In case of 5-minute bars, you need to shift data by -5 minutes.

To shift the data during import you can use $TIMESHIFT command of ASCII importer, see http://www.amibroker.com/guide/d_ascii.html

$TIMESHIFT offset_in_hours

the offset_in_hours parameter defines the time in hours that should be added to imported timestamps. You can use negative and fractional values too. Each minute is 1/60 of hour so shifting back by 1-minute would be -0.01666667.

If you are using ASCII Importer Wizard, and want to import 5-minute data with future looking timestamps, you can simply type the following in the “Additional commands” box of ASCII Importer Wizard.

$TIMESHIFT -0.08333333333333

-0.08333333 is a result of dividing 5 by 60 (number of minutes in an hour)

Please find out what time-stamping method is used by your data provider because it is important to know that to get your higher-interval charts right.

Filed by Tomasz Janeczko at 3:36 am under Data
Comments Off on How to correct forward looking timestamps

Sometimes it is useful to update existing database with quotes from other source and sometimes we just want to update one data field, leaving other unaffected. For example we may want to import data into Aux1 and Aux2 fields leaving regular OHLC prices untouched.

This post will show how to use $HYBRID mode of ASCII importer to combine data from multiple input files.

When we use Import Wizard / Import ASCII features to import quotations, by default the importer expects Close prices to be present in the imported data. There are some situations however when it would be convenient to import data just to one field (e.g. Aux1) if we have some additional values that we want to store in the database.

Of course, one way would be to combine all these quotes in a single file before the import and process in one run – but ASCII importer allows also to use special $HYBRID mode to import such partial data.

As an example, let us consider a situation where we already have adjusted OHLC quotes imported into the database and we have a file containing unadjusted quotes in the following format:

Ticker,Date,Aux1
AAPL,13-10-2014,99.81
AAPL,10-10-2014,100.73
AAPL,09-10-2014,101.02
AAPL,08-10-2014,100.80
AAPL,07-10-2014,98.75
AAPL,06-10-2014,99.62
AAPL,03-10-2014,99.62
AAPL,02-10-2014,99.90
AAPL,01-10-2014,99.18
AAPL,30-09-2014,100.75
AAPL,29-09-2014,100.11

In order to import those quotes into Aux1 field and combine with existing data, go to File->Import Wizard menu, pick the file and define format the following way:

First we need to define columns so they match the format of input data (in this case set first three columns to Ticker, DMY, Aux1 and the rest to Skip).

To activate special hybrid mode we need to type:

$HYBRID 1

into Additional Commands box and since we are not importing regular prices that include Close array, we also need to mark Allow negative prices.

If instead of using the wizard we create import definition file manually, then it should contain:

$HYBRID 1
$ALLOWNEG 1

Now we can verify the results in Symbol->Quote Editor. The OHLC fields still contain adjusted values, while additional data has been properly stored in Aux1 field without affecting the other fields.

For more information about Import Wizard and ASCII importer please check the following parts of the User’s Guide:
http://www.amibroker.com/guide/w_impwizard.html
http://www.amibroker.com/guide/d_ascii.html

Filed by Tomasz Janeczko at 7:16 pm under Data
Comments Off on How to combine data from multiple input files