Emulating AmiQuote Refresh Database for Custom Datasource

[ka66]

Hi All,

My scenario:

1. I have a custom database of securities, queryable over a web-based API. I can dump data into files if need be, do transforms, etc.
2. Ideally, I wish to integrate into AmiBroker such that it periodically (not realtime, EOD right now) fetches the latest symbol and prices from the database -- ideally as well -- it does this incrementally since the last refresh point for a symbol.

What's the best approach to do this -- sorta like the way AmiQuote works with automatic refresh/import.

I'm happy to write code, use SDKs, but don't want to duplicate functionality that may already exist.

Additionally, I saw the generic ODBC driver approach.

But I'm not sure how it does incremental updates, e.g. refresh since last timestamp for a symbol. I had the crazy idea of adding a very basic ODBC wrapper to the custom datasource, just enough to support AmiBroker's queries. Probably overly complicated.

ASCII import is a simple mechanism, but I really am after some level of automation that's more 'tightly' integrated in.

Thanks very much for the product and this forum.

PS. I really have been searching for this prior to posting, apologies in advance if I could not structure my query well enough!

[fxshrat]

You may use Batch processor for periodic (EOD) import of data files.

As for file extensions and format file names read here

Using Specific Format with Batch "Data Import ASCII"

It does NOT matter what appears in the ASCII importer “Files of type” combo box. This is UI of ASCII importer not the batch processor. This combo box displays content of Formats\import.types file and is completely IRRELEVANT to batch. What matters for BATCH is the extension of imported data file and actual name of format definition file, so to import yourfile.CSV data file, batch would use Formats\CSV.format file and ONLY that file. It does not matter what appears in the ASCII importer file pic…

So in short format file name must match with extension of data files to be imported. And content of format file must match with data files format. As for creating format files read here.
Other documentation as regards to Ascii import
https://www.amibroker.com/guide/d_ascii.html
https://www.amibroker.com/guide/w_impwizard.html
http://www.amibroker.com/kb/2014/09/25/how-to-import-huge-ascii-files-quickly/

[ka66]

Thanks very much for the detailed reply and links @fxshrat, this seems perfect.

Some questions/assumptions on the data file's interaction with the batch process, please:

1. Since this is a fixed file selection, rather than a pattern, guessing this file is the one to be updated periodically by an external mechanism (e.g. my own scheduled/triggered code)?
2. How does AmiBroker deal with duplicate values, e.g. if I miss updating the file, and the batch process runs against previously imported data.

With (2), is it just indexed on symbol+time as a primary key with price as value to be inserted if non-existent or updated with new prices if it exists?

[fxshrat]

1. That batch action "Data import ASCII" is about import but not about creating data file. You said the data file(s) would be created by yourself. So the file to be imported has to exist.

2. If you re-import the same data file (not being updated) then nothing will change to data entries of AB database. You will have same data entries and same amount of entries there in DB.
   It will not be created duplicate EOD time stamp with duplicate or different prices.
   And if there is new (updated) price data for same existing EOD time stamp in your file then AB import will overwrite price data of exiting dates within DB (well, and if there is new EOD time stamp then new data entry will be created). So same here... AB will not create duplicate EOD timestamp (just having different price/vol data).
   It is different if you use $TICKMODE 1 (concerning intraday (subsecond) data but not EOD). Then if you have duplicate timestamp(s) (same date and time) in your file no matter if having different or same price data then duplicate stamps will be created by AB too.
   Tickmode is explained here https://www.amibroker.com/guide/d_ascii.html
   But you should not bother about TickMode here because you just work with EOD data. It is just side note.

[ka66]

Thanks @fxshrat, crystal clear on EOD/OHLC data.

You are correct that I intend to provide the data. However, I was also curious on how AmiQuote does a pull from an external datasource, and push/update to AmiBroker's database in one flow/process.

Nevertheless, your provided solution is appreciated and I'll look into implementing it this way, cool to learn of the batch feature as well, to reduce UI interactions for common, repetitive tasks.

Side note on TICKMODE is noted, thanks.

[beppe]

@ka66, in addition to the suggested easy to implement solution, if you do not mind a bit of extra coding, you could also use the OLE Automation to import data.

Some notes and examples using an AFL or via an external .JS/.VB script here:

How to manually import OHLC data via AFL script?

There are lots of misunderstandings / misconceptions here. First and foremost, you need to understand that AmiBroker is MULTITHREADED application. And that each AFL formula runs in DIFFERENT thread. The thread that is handling data is ALSO different than formula execution thread. Therefore any ‘import’ action triggered from AFL level would NOT be immediately reflected in current formula array but only possibly in NEXT execution. Having said that, import can easily be triggered from AFL, witho…

Cant import data from ASCII file by using JS - OLE

HI all , my code for importing ASCI data as below, but after I run the JS , it does nothing for me. my database hasn't updated. Why?? please help. Thanks AB= new ActiveXObject( "Broker.Application" ); AB.LoadDatabase("C:\Program Files\AmiBroker\REALTIME"); // Change Datbase AB.Import(0, "C:\Users\user\Desktop\OPEN POSITION\AUDJPY_D1.csv","REAL_TIME_DATA.Format" );

OLE import() with vbscript

Hi, I do not see any imported data after running the following vbscript. Where is the mistake in the code? ' WIN 7 HomePremium SP1, ABx64 AmiBroker 6.30.0 RC November 18, 2018 18:13 ' run: cscript /nologo 0_IMPORT_AQH2AB.vbs ' or c:\windows\syswow64\cscript.exe 0_import_aqa2ab_temp.vbs ' OLE import in vbs ' full installtion on AB 6.3 ' run ABReg64 ' Run AmiQuote Ticker=AA start_date=16.11.2018 - automatic import unchecked ' Database is given by AB ' I read ' AB Help / Refere…

[ka66]

Many thanks for the OLE links, @beppe, I code day to day, but not generally on Windows. Links are appreciated, will look through shortly. Apologies for the delayed response.

[ka66]

Looking at ASCII format defs, I don't suppose there's a way of defining a format for the following dataset (1-minute intervals OHLC data):

datetime_iso8601,symbol,open,high,low,close,volume
2019-02-13T00:00:00+00:00,AUDJPY,78.503,78.505,78.488,78.488,202.77
2019-02-13T00:01:00+00:00,AUDJPY,78.488,78.492,78.477,78.489,194.59
2019-02-13T00:02:00+00:00,AUDJPY,78.489,78.491,78.467,78.468,155.68
2019-02-13T00:03:00+00:00,AUDJPY,78.467,78.483,78.467,78.473,176.59

is there?

The date/time are combined into a single field representing in ISO date time with timezone data. I note that generally there's a separate field expected for date, time, and a timeshift -- for timezones.

[fxshrat]

The only formats supported are the ones being documented.

I note that generally there's a separate field expected for date, time, and a timeshift -- for timezones.

You can define separator(s) which my be multiple ones per line.
So you have separators comma, "T" and "+"

$SEPARATOR ,T+

As for timeshift there is separate command by name... well, $TimeShift.

So your format could be, for example

$FORMAT Date_YMD,Time,Skip,Ticker,Open,High,Low,Close,Volume
$SKIPLINES 1
$SEPARATOR ,T+
$CONT 1
$AUTOADD 1
$OVERWRITE 1
$DEBUG 1
$BREAKONERR 1
$TIMESHIFT 0

And as you can see below. I was able to import your sample data.

[ka66]

Ah thank you very much, @fxshrat -- did not realise you could have multiple separators. This works as expected.

(Re: timeshift, yes, was actually referring to that command itself, ).

Okay, thus far I've added my format entry type to <AB_INSTALL>/Formats/import.types and a new format definition file to the same directory. This gets picked up fine, I see my new filetype in the dialog box and I can filter out dataset files appropriately.

However, the document on ASCII import also says:

This file, all other ".format" files and "import.types" file (described later) should be stored in \Formats subdirectory of AmiBroker's current working directory.

My AB_INSTALL above for example is: C:\Program Files\AmiBroker. Wondering if there's a way to store this in a separate folder, e.g. relative to where the datasets are or to where the database is.

For a manual ASCII import (point and click), I've tried it in both those locations, creating a Formats folder within them with the format definition file and import.types. Neither seems to get picked up, only the one in AB_INSTALL registers.

My concern is at AmiBroker upgrade time, will the files and folders in AB_INSTALL be overwritten, including custom formats.

[fxshrat]

Manual import via File - Import ASCII requires format file location in
<AB_INSTALL>\Formats\ folder.

[ka66]

No worries, thanks for confirming.

[ka66]

Hi @beppe,

I've started looking at the OLE import, basically going by this referenced link.

To confirm -- I need AmiBroker running for this to work? (Sorry, new to OLE semantics)

When I run the script without AmiBroker already running, it seems to start AB (splash screen), then ends. But on opening AmiBroker, I don't see any data.

Conversely, if I have AmiBroker opened, even on a different database, the script will switch the database and import data in, refreshing the view.

Additionally, what is the first type argument Application.Import(*type*...). In your example, you just set it to zero. Unless I've missed something, can't find further documentation on it.

Thanks again.

[codejunkie]

But on opening AmiBroker, I don't see any data.

Try adding

AB.SaveDatabase();

Conversely, if I have AmiBroker opened, even on a different database, the script will switch the database and import data in, refreshing the view.

Remove AB.LoadDatabase if you do not want it to switch database.

Additionally, what is the first type argument Application.Import(*type*...) . In your example, you just set it to zero. Unless I've missed something, can't find further documentation on it.

Type should be kept at zero

Import data through ole

Type should always be set to 0. And you need to import one file in single call to Import. Old newsletter article includes examples http://www.amibroker.com/newsletter/02-2001.html

[ka66]

Thanks @codejunkie, SaveDatabase() did the trick. Re: LoadDatabase(): I'm happy with it switching database for this purpose.

Thank you for the remaining links, too. Will attempt to search better next time.

[ka66]

Thanks for your help everyone, I have initial data imported, and have incremental pushes happening through OLE (more precisely a Python script that interacts with the API, then shells out to an OLE JS script passing on the dataset files just created).

I've pushed about 7 years worth of 1-minute interval OHLC data across 16 tickers: any suggestions, besides eyeballing the values (which I will be doing anyway at random points), on making sure I have loaded in everything correctly?

For example, via AFL I could potentially do a bar count and verify that with the no. of CSV records over the time period?

Wondering if people have approaches to doing sanity checks on imported data in AmiBroker.

I suppose an exhaustive check would be to export and compare to source datasets...

Thank you again.

[fxshrat]

For example, via AFL I could potentially do a bar count and verify that with the no. of CSV records over the time period?

Yes, you may run exploration if it makes you feel better.

SetBarsRequired(sbrAll);
AddSummaryRows(1, 1);
Filter = Status( "lastbarinrange" );
AddTextColumn(Interval(2), "Interval", 1);
AddColumn(Cum(1), "#Bars", 1);

---

But if import.log file of AmiBroker main directory does not have any errors lines then you can be pretty much sure that everything got imported.

[ka66]

Thanks @fxshrat --- the exploration code is very handy indeed.

I also took your suggestion and wrote some basic sanity checks to verify <AB_INSTALL>/import.log contents on each run of my update.

In case it helps others, I've collected code snippets in a github repo, including my rudimentary import.log checker (importlog.py).

Thank you again everyone, appreciate the help very much. With data sorted, I can actually go back to more fun bits of trading -- hopefully finding a profitable system, fingers crossed!

[kvlmanohar]

I didnt find batch tab to import ASCII files periodically, can anyone guide me pl