Database Management FAQ’s

An unsorted, unqualified list of FAQ’s relating to anything and everything to do with database management.

The list is dynamic and subject to change without notice.

 

Q6 (a). What happens when my original (MS) data source adds, or deletes, a stock code or merges two codes together?
Do I have to take care of this myself?

A6.  Use the METASTOCK PLUGIN  http://www.amibroker.com/guide/h_extsources.html  to read the MS database directly (so you don’t need to re-import all the time).

Re: understanding categories.

Categories are NOT folders (like in Windows Explorer). The difference is fundamental. In the Explorer a file appears (usually) only once in the given
tree leaf. In the Workspace symbol tree a given symbol appears multiple times because it appears in every category leaf that it belongs to, even if this is the same symbol and it exists only as a single entity. One symbol belongs to MANY categories at the same time. For example AAPL (Apple Inc.) will belong to"Stocks" group category, "Nasdaq" market category, "Information" sector category, "Comp-Computer Mfg" industry category, and may also belong to several watch lists and favorites category ALL at the same time.That’s why ONE symbol will appear in many leaves of the symbol tree. Now if you delete the SYMBOL it will of course disappear from ALL categories because you have deleted the SYMBOL.

There are two types of categories:

1. with mutually exclusive membership: groups,markets, sectors/industries – it means that symbol MUST belong to ONE group, ONE market, and ONEsector/industry at a time – you can move the symbol from one group/market/sector/industry to another but you can not remove this assignment – create "Unassigned" group/market/sector/industry instead.
2. with free membership: watch lists/favorites/indexes – it means that a symbol may belong to ANY number (including zero) of watchlists (and to favorite/indexcategory too)In this case you can remove this assignment by Watch List -> RemoveWatch lists are covered in detail in the User’s Guide:
http://www.amibroker.com/guide/h_watchlist.html     (the same is available when you press F1 from AmiBroker)

Another thing: if you use "DELETE" button in Symbol->Categoriesit displays the warning:"This will delete selected symbols FROM THE DATABASE".Again: it will delete FROM THE DATABASE, not from the category.

Tomasz Janeczko – amibroker.com

Authors comments: As a rule of thumb, where data is accessed directly from an external source, WITHOUT a local (AmiBroker) database enabled, changes made to the database by the provider will be reflected at the AB user interface.

Where data is accessed from an external source, WITH a local (AmiBroker) database enabled, database changes will be at the discretion of the user. Depending on the access method, new symbols will be automatically added to the database e.g. when using the MS plug-in, and redundant symbols, or symbols that change, will remain in the database (with their data). The user has the option to merge changed symbols or delete old symbols (the symbol lists are stored in the local AmiBroker database, independently of the plug-in).

Where a local AmiBroker database is used as a standalone (data is updated via AmiQuote) the user has complete control over which symbols to add and delete etc (once again redundant symbols, and their data, will remain in the database unless manually deleted).

Q6 (b). How can I merge two symbols together after a name change?

A6 (b). Pick the symbol, that you want to retain in the database (master symbol), from the All symbols list and select Symbols >> Merge from the AB file menu. Click on the symbol, that you want to assimilate into the master, from the drop-down list in the Merge quotes window that opens, select from the options available (overwrite duplicate quotes, delete the slave symbol after assimilation or assign an alias name) and then click on OK.

 

Q5. Does anybody know how to check the number of stock symbols in a database?

A5. Hover the mouse pointer over the Data information section of the AmiBroker Status Bar (in the bottom right of the AB screen). A tool-tip will report on the database file name and path, the number of symbols in the database and the number of quotes in the current symbol.

 

Q4. Currently, my EOD data is supplied in Metastock format from an external supplier (JustData)…how can I setup to use it in AmiBroker?

It is stored in the following folders/directories on my pc:
C: – MyDatabases – Metastock –
ASX – 0
ASX – 1
ASX – A
ASX – B
ASX – C, etc, etc
LSE – 0
LSE – 1
LSE – A
LSE – B, etc, etc
World Indices

At the moment I trade multiple exchanges (ASX, LSE) as shown above.
To use this data within Amibroker, I am aware that I can use the Metastock Plug-in to recreate my databases in Amibroker, as per this link http://www.amibroker.com/guide/h_extsources.html

However, when it comes to creating the equivalent databases in Amibroker, how would others recommend I create them?

For example, would I need a different Amibroker Database for:
1. each current folder (as listed above),
2. 1 for all the ASX data, 1 for all the LSE data, etc
3. 1 database which captures ALL my data (LSE, ASX & Indices)?

If option 3 is suitable, then how would I distinguish in Amibroker which exchange a security belongs to (LSE or ASX)?

A4. Authors comments – AmiBroker doesn’t provide for easy ways to reference symbols that are in one database from within another, so with that restriction in mind it depends on how you want to use the data. If you are going to cross reference World indices to local or international markets, then you would be better off merging them into one database. This takes some work, so if you are not going to do inter-market analysis it is a matter of personal choice whether the benefits are worth the effort.

From an organizational point of view, international exchanges can be ‘managed’ at the Market, Group or Watch list level (refer to Users’ Knowledge Base >> Setup A Custom Database for assistance with organizing Categories within AmiBroker).

Adapting some of the basic procedures outlined in that tutorial you can either:

1) Create a new local AmiBroker database and use the Metastock Wizard to import the symbols into Watch lists, folder by folder, and then assign them to Categories from there (with the help of categoryAddSymbol).

2) Create a new database and access the symbols using the Metastock plug-in (this requires nominating the folders that will be referenced by selecting them in the File >> Database settings >> Configure window). From there you will need to import Watch lists, that correspond to the Metastock folders, and then assign them to Categories (using categoryAddSymbol once again).

Method 1 will create a static database, for historical back-testing only, and method 2 will create a database that dynamically updates to the Metastock database.

Both database types rely on organizing the symbols into Categories, after the initial setup, if you want to replicate Metastock folders.

 

Q3. I have a big database with intraday 1-sec quotes. How can I change the quotes interval in the database to shrink the database size ?

A3. You can use Exploration to generate any desired interval (set Periodicity in AA) and export to ASCII then import back to a new database.
http://www.amibroker.com/kb/2006/03/04/how-to-export-quotations-from-amibroker-to-csv-file/

Tomasz Janeczko – amibroker.com

AmiBrokerYahooGroup message #116071 "How can I convert AmiBroker tick database to AmiBroker 1-minute dat" http://finance.groups.yahoo.com/group/amibroker/message/116071

 

Q2. Is it possible to enter negative values via the Quote Editor?

A2. There is special flag $ALLOWNEG 1 that you can use to allow negative data during ASCII import.

Tomasz Janeczko – amibroker.com

AmiBrokerYahooGroup  #116322 "How to enter negative values in quote editor?"  http://finance.groups.yahoo.com/group/amibroker/message/116322

 

Q1. Is it possible to import tick data with the Wizard in AB?

A1. Tick mode _is_ supported by the wizard.
You just need to add $TICKMODE 1 in the "Additional Commands" field in the wizard and use "Close" for Last tick price and "Volume" for last tick size.

Tomasz Janeczko – amibroker.com

AmiBrokerYahooGroup message #116178  "Import TickData"  http://finance.groups.yahoo.com/group/amibroker/message/116178

Statistics Resources For Traders

An unqualified, unordered, list of statistical resources that have relevance to trading.

The list is dynamic and subject to change without notice.

NOTE – SOME OF THE LINKS CONTAIN COMMERCIAL REFERENCES, OR LINK TO COMMERCIAL SITES.

THE AUTHOR HAS NO COMMERCIAL AFFILIATIONS OF ANY KIND AND RECEIVES NO GRATUITIES, OR BENEFITS, FROM ANY COMMERCIAL ORGANIZATION OR INDIVIDUAL INVOLVED IN COMMERCIAL ACTIVITIES, NOR IS HE PERSONALLY ENGAGED IN ANY COMMERCIAL UNDERTAKINGS, RELATED TO TRADING, IN ANY WAY.

THE LINKS ARE PROVIDED AS A RESOURCE AND FOR EDUCATIONAL PURPOSES ONLY. THEY ARE NOT A RECOMMENDATION, ON THE PART OF THE AUTHOR, NOR DO THEY CONSTITUTE INVESTMENT ADVICE AND SHOULD NOT BE CONSTRUED AS SUCH.

MISCELLANEOUS ARTICLES

1. Link to a short introductory article on the pitfalls of Neural Networking "A Little Knowledge Can Be A Dangerous Thing" by Dr. Halbert White: http://www.secondmoment.org/articles/ann.php

2. Link to a short introductory article on over-fitting data "Reality Check for Data Mining" by Dr. Halbert White: http://www.secondmoment.org/articles/datamining.php

3. Link to a short introductory article on managing missing data "The Curse of the Missing Data" by Yong Kim: http://www.secondmoment.org/articles/missingdata.php

4. Link to a dictionary level definition of the statistical P-value: http://en.wikipedia.org/wiki/P-value

5. Link to the Home Page of the The American Finance Association (AFA): the premier academic organization devoted to the study and promotion of knowledge about financial economics:  http://www.afajof.org/default.asp

Publishers of the Journal of Finance:

  •           search or browse back issues,
  •           free to students,
  •           reasonably priced for others,
  •           registration by international visitors accepted.

6. Link to an overview of Technical Analysis: http://en.wikipedia.org/wiki/Technical_analysis

7. Link to a short, mathematically orientated, article on trend analysis (includes links to regression and least squares methodology):  http://en.wikipedia.org/wiki/Trend_estimation

 

 BOOKS

DRAFT – SUBJECT TO CHANGE – IT IS POSSIBLE THAT REVIEWS WILL BE ADDED AT SOME TIME IN THE FUTURE.

 

1) Aronson – Professor David R, "Evidence-Based Technical Analysis", John Wiley & Sons Inc, 2007.

http://www.amazon.com/Evidence-Based-Technical-Analysis-Scientific-Statistical/dp/0470008741/ref=pd_sim_b_img_4

Link to Professor Aronson’s Home Page: http://www.evidencebasedta.com/   

Professor Aronson has a long term interest in applying "data mining and non-linear predictive modelling to the development of systematic trading methods".

His web-site includes links to two downloadable PDF’s:

  •           Monte Carlo Permutation Evaluation of Trading Systems
  •           Reader Questions & Answers

(Thanks to Whitney Broach who first introduced the author to Aronson’s work via the AmiBrokerYahooGroup).

 

2) Bandy – Doctor Howard B,"Quantitative Trading Systems", Blue Owl Press, 2007.

Link to Doctor Bandy’s home page: http://www.quantitativetradingsystems.com/index.html

 

3) Bernstein – Peter L, "Against The Gods", John Wiley & Sons Inc, 1998.

http://www.amazon.com/Against-Gods-Remarkable-Story-Risk/dp/0471295639/ref=pd_sim_b_img_1

 

4) Katz – Jeffy Owen and McCormack – Donna L, " The Encyclopedia of Trading Strategies", McGraw-Hill, 2000.

http://www.amazon.com/Encyclopedia-Trading-Strategies-Jeffrey-Owen/dp/0070580995/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1202289418&sr=1-1

 

5) Kaufman – Perry J, "New Trading Systems and Methods", John Wiley & Sons, Inc, 2005.

An intermediate level reference book that is up to the mark with the latest trading techniques. It isn’t a book specifically on ‘statistics for traders’ but it does  include analysis, underpinned by sound logic and quantitative methodologies, on a wide range of trading related subjects.

http://www.amazon.com/New-Trading-Systems-Methods-Wiley/dp/047126847X/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1202293686&sr=1-1

 

6) Kennedy – Peter, "A Guide To Econometrics",  T. J. Press Ltd, 1992.

http://www.amazon.com/Guide-Econometrics-5th-Peter-Kennedy/dp/026261183X/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1202292822&sr=1-1

 

7) Kestner – Lars N, "Quantitative Trading Strategies", McGraw-Hill, 2003.

http://www.amazon.com/Quantitative-Trading-Strategies-Irwin-Traders/dp/0071412395/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1202292735&sr=1-1

 

8a) Pardo – Robert, "Design, Testing, and Optimization of Trading Systems", John Wiley & Sons, Inc, 1992.

http://www.amazon.com/Design-Testing-Optimization-Trading-Systems/dp/0471554464/ref=pd_bxgy_b_text_b

 

8b) Pardo – Robert, "The Evaluation and Optimization of Trading Strategies", John Wiley & Sons, Inc, 2008?

http://www.amazon.com/Evaluation-Optimization-Trading-Strategies-Wiley/dp/0470128011/ref=pd_sim_b_img_16

Link to Bob Pardo’s Home Page: http://www.pardocapital.com/

 

9) Stridsman – Thomas, "Trading Systems That Work", McGraw-Hill, 2001.

http://www.amazon.com/Trading-Systems-That-Work-Evaluating/dp/007135980X/ref=pd_sim_b_img_1

 

10) Taleb – Nassim Nicholas, "Fooled By Randomness", Random House, 2005.

 http://www.amazon.com/Fooled-Randomness-Hidden-Chance-Markets/dp/0812975219/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1202292553&sr=1-1

 

 

11b)  Vince – Ralph, "The Mathematics Of Money Management: Risk Analysis Techniques For Traders", John Wiley & Sons, Inc, 1992.

 

http://www.amazon.com/Mathematics-Money-Management-Analysis-Techniques/dp/0471547387/ref=sr_1_5?ie=UTF8&s=books&qid=1207524559&sr=1-5

Setup A Custom Database (v3)

Backfill Exploration for Real-Time data

Backfilling data can be done in many ways, and different methods may be needed for different data providers. The method shown below was developed for eSignal data. If you use another data provider, you may need to modify the code and the procedure.

When backfilling data, you should confirm that data are backfilled properly and have some indication regarding the presence of data holes. As pointed out in Data Holes in Real-Time Trading, to be able to detect holes, you need a perfect data array against which to compare the data. Since AmiBroker doesn’t have such a data array, the method presented here uses the QQQQ as a reference ticker. Turn ON and set Backtester Settings -> General -> Pad and align all data to reference symbol -> QQQQ. To have all tickers backfilled, you should also turn ON Wait for Backfill(RT only). To speed up real-time backfills you may want to display only a simple price chart, instead of complex code. Refreshing complex Indicators or Systems will slow down backfill. The following Exploration will Backfill all tickers in your Watchlist and report on it’s success:

15 Day Performers Trading System

AFL Testing Databases

Small databases, with a limited number of symbols and limited data, can be useful for solving complex problems or designing systems within AmiBroker. A small amount of data can be easier to use, for debugging code or proving strategies, than larger amounts. For AmiBroker users, Yahoo data is readily available for this purpose.

In this tutorial new users are shown a simple method to create small quality controlled databases that are suitable for testing basic AFL functions and procedures.

FORMULAS FEATURED: BarCount, BarIndex();

INCLUDING: creating, copying, naming, deleting and backing up databases; managing databases from AmiBrokers? File commands or Windows Explorer; database naming conventions, creating small databases using AmiQuote; manipulating data with Quote Editor; understanding errors reported by the Database Purify Tool, plotting AFL functions as a training aid and more …………….

Attached Files:

  • Click on the link to browse document files on line, or, Save As to download an editable version, complete with a document map (the document map will only be available to users who own a copy of Word).
  • To download .afl files to the desktop right click on the link  and Save Target As ‘filename’ at Program Files/AmiBroker/Formulas/Custom to access them as formulas in the AmiBroker Charts list.
  • Keeping Your Chart Right-Justified

    In real-time trading it is vital to keep your chart right justified, i.e., ensuring the right-most price bar is visible at all times. Failing to do so and looking at the wrong bar when trading real-money can invoke a panic response by the trader that results in erroneously closing a position or worse, placing a wrong order. This can easily happen if you are running several computers and are not always monitoring the chart being traded.

    The following code shows you how you can right-justify your chart at system startup or from the Param window. A visible alert that changes the chart back ground color when the last bar is not visible is included.

    The Alarm uses DateTime() to automatically correct for blank bars at the right edge of your chart. The code is kept explicit and you may be able to optimize it for speed.

    <p>

    Edited by Al Venosa.

    « Previous Page