Databases and the Base Time Interval

This lesson has one thing in it that matters more than everything else in this part put together, so it goes first, before the walkthrough.

When you create an AmiBroker database you choose a base time interval, and that choice is effectively permanent. It sets the finest bar the database can ever contain. Everything coarser is manufactured on demand by compressing that grain. Nothing finer is ever available, not by changing a setting, not by buying an upgrade, not by importing better data afterwards. Choose end-of-day and you have an end-of-day database for life. The intraday menu entries will be greyed out permanently, and AmiBroker will not explain why.

The rest of the lesson earns that statement and shows you how to make the choice deliberately.

A database is a folder, not a file

Most Windows programs open a file. AmiBroker opens a directory, and in its menus and dialogs you are always picking or creating a folder rather than a document. The documentation calls it a “database directory”, or in older pages a “workspace directory”; both words still appear.

Inside that folder:

• 0-9, a-z and _ subdirectories. Each symbol’s quotes and its hand-drawn studies live in one binary file named after the ticker, in the subdirectory matching the ticker’s first character. IBM’s data is the file IBM inside the folder I.
• broker.master — the table of all symbols, used for fast loading. Deleting it is safe: AmiBroker rebuilds it from the individual symbol files.
• broker.workspace — the database’s settings, its category names, and global advance/decline data. Deleting this one loses your database settings and resets category names to defaults.
• Watchlists — a folder of plain-text .TLS files, one per watch list.
• Layouts — the local layouts for this database.

!Important

Backing up “the database” means copying the whole folder. There is no single file that is the database. And the guide is emphatic that in almost every circumstance you should not touch the files inside a database — the program manages them.

One exception is worth knowing because the error message is alarming and the damage is not: broker.newcharts maps chart panes to formula files. Deleting it produces “formula file empty or cannot be found” in your charts. Your formulas are not lost — only the pane-to-formula references.

A default installation creates a data folder inside the main AmiBroker directory, containing the sample Dow Jones Industrial Average database. That is the one you have been looking at. It is a demonstration, and this lesson is where you stop using it.

Creating a database

File -> New -> Database. Older tutorial pages in the guide still write this as File -> New database; it is the same command.

Creating a database, in the documented order

1. 1File -> New -> DatabaseThe Database Settings window opens with only its top half usable
2. 2Browse, or type a folder pathYou are naming a folder that does not exist yet
3. 3Press CreateNothing below becomes usable until you do
4. 4Choose Data source(local) for imported data, or a plug-in for an external feed
5. 5Choose Local data storage and Number of barsBoth are ignored when the data source is (local)
6. 6Choose the Base time intervalThe one that cannot usefully be changed afterwards
7. 7OKThe database exists and is open

The step people get stuck on is the third. The Database Settings window has two halves, and until you press Create the lower half is not available. It is not broken; it is waiting.

Choose the folder deliberately. Put it somewhere you back up, and somewhere outside C:\Program Files, which Windows protects. A folder next to your other market work is better than a folder inside the application directory — and, if you ever reinstall AmiBroker, considerably safer.

✦Tip

Name the folder after what is in it, including the interval: eod-us-equities, intraday-1min-futures. You will end up with more than one database, they all look identical from outside, and the base interval is not visible until you open one and look at Database Settings. A name that carries the answer saves you opening them.

The Database Settings window, field by field

File -> Database Settings reopens it at any time.

General half — only usable at creation time. The guide states this plainly: these controls are “enabled only at the database creation time… once the database is created, these controls become disabled”. This is where Browse... and Create live.

Data source half — usable later.

Field	What it does
Data source	(local) — AmiBroker maintains the data itself — or one external plug-in
Local data storage	Caches external data in AmiBroker’s own files. Required for most real-time sources. No effect at all when the data source is (local)
Number of bars to load	How many bars are pulled from an external source and kept. No effect when the data source is (local)
Base time interval	The smallest bar the database stores. See below
Flush cache	Forces a cache flush and a re-fetch from the plug-in
Configure	Opens the data source’s own configuration dialog
Intraday settings	Per-database session filtering and time-compression rules

✕Common mistake

Two of those fields do nothing on a local database, and this is responsible for a great deal of wasted time. If your data source is (local) — which it is for every database you fill by AmiQuote or ASCII import — then Local data storage and Number of bars to load are inert. People tune them, observe no change, and conclude the settings are broken. They are simply not applicable: with a local data source AmiBroker holds all the bars you gave it.

Local data versus plug-in data

A local database is one AmiBroker owns outright. You put data into it, by AmiQuote or by the ASCII importer, and AmiBroker stores it in its own files. This is the right choice for this course and for most end-of-day work.

A plug-in-fed database delegates the quotes to an external source. AmiBroker asks the plug-in for a symbol’s data instead of reading its own files.

Where quotes come from

request travels down, data comes back up

1. Charts, Analysis, AFLAsk for bars at some interval
2. Time compressionBuilds weekly, daily, hourly bars from the stored grain
3. AmiBroker databaseThe bars actually stored, at the base intervalalways present
4. Data plug-in (optional)Read-only access to an external source
5. External vendor database or feedNever modified by AmiBrokernot yours

Three consequences of that picture are worth stating, because they surprise people.

Plug-ins are read-only. AmiBroker never modifies the external source. Anything you create — hand-drawn studies, category assignments, watch lists — is written to AmiBroker’s own database. So AmiBroker needs a database folder of its own even when every quote comes from somewhere else.

Preferences are defaults, not settings. Tools -> Preferences, Data tab holds the same-looking fields, but they are only the defaults applied when a new database is created. File -> Database Settings always takes precedence — except when the Data source combo is left on Default or the Local data storage radio is on Default, in which case the Preferences value is used after all.

External-source databases need retrieving when symbols change. If the vendor adds or removes symbols, File -> Database Settings -> Configure -> RETRIEVE refreshes the symbol list. A local database has no equivalent because you control its symbol list yourself.

The base time interval

Here is what the setting actually does.

The base time interval is the grain of the database: the finest bar it stores. AmiBroker builds every coarser interval on the fly by time-compressing that grain. A 5-minute database can show you 5-minute, 15-minute, hourly, daily, weekly, monthly, quarterly and yearly charts, plus any custom interval at or above five minutes. It can never show you a 1-minute chart, because the information required to draw one was never stored.

The guide states the rule from the chart side too: “intraday intervals are disabled if your database is in end-of-day mode… If you, for example, set Base time interval to 5-minute, all chart periodicities from 5-minute up will be enabled.”

A 5-minute base interval, and what it makes available

compressed upwards on demand

1. Weekly, monthly, quarterly, yearlyAvailable
2. Daily, hourly, 15-minuteAvailable
3. 5-minute — the base intervalStored on disk. This is the grain
4. 1-minuteNever available in this database
5. TickNever available in this database

The base interval is a floor. Everything at or above it is yours; nothing below it ever will be.

Why “effectively permanent” and not simply “permanent”

This deserves precision, because the internet says both and only one is accurate.

The base-interval control sits in the Data source half of the Database Settings window, which the guide describes as remaining usable after the database is created. So the documentation does not say the combo is greyed out forever, and this course will not claim it is. What the guide does say, for real-time sources, is that the base interval “should be set once at the database creation time”, because real-time sources collect ticks and pack them into interval bars, and this setting defines the minimum grain.

The real reason is physical rather than about the user interface. Bars that have already been time-compressed and written at the old grain cannot be un-compressed. If a database stored daily bars, changing the setting to 5-minute does not conjure five-minute history out of the daily bars — the intraday detail was never captured. What you get is a database whose setting claims one thing and whose stored history is another.

The practical rule, therefore:

⚠Warning

Changing the base time interval later does not retroactively give you finer data. If you need a finer grain, you create a new database with that base interval and re-collect or re-import the history into it. Treat the choice as permanent when you make it, because in every way that affects your work, it is.

What the choice costs, in both directions

Choosing coarser than you need means intraday work is unavailable, and the only route back is a new database plus a fresh import.

Choosing finer than you need is not free either. A 1-minute database over the same calendar period holds hundreds of times more bars than a daily one, which costs disk, RAM and time on every scan you ever run. And finer intraday history is generally harder and more expensive to obtain than daily history: many free sources offer decades of daily data and nothing intraday at all.

The guide’s own recommended way out of the dilemma is not a compromise interval. It is two databases — one end-of-day with long history, one intraday with short history — optionally running in two AmiBroker instances. Some plug-ins additionally support an Allow mixed EOD/Intraday data option in Intraday Settings, which lets one database hold both kinds of bar, showing EOD bars in daily mode and hiding them in intraday modes.

!Important

What to choose for this course. Create an end-of-day database now and use it for Parts 1–16 and 27–36. It is all those parts need, free data exists in abundance for it, and it stays small and fast. When you reach Part 19 you will create a second, intraday database — deliberately, as a separate thing, with its own base interval chosen then. Do not try to make one database serve both purposes.

One more place the base interval shows up, which you will meet later: the Bar Replay tool’s recommended Step interval is the database’s base interval, and the AFL debugger’s default bar interval is likewise “use base time interval”. A setting you made once at creation quietly becomes the default in three other places.

Number of bars, and sizing a database

For an external data source, Number of bars to load caps how many bars per symbol AmiBroker pulls and holds. The documented reference points: about 2,600 bars is ten years of end-of-day data; about 30,000 is sixty days of one-minute intraday.

The number that makes this concrete is the memory cost. One data bar is 40 bytes, and AmiBroker actually allocates the memory. So:

Bars per symbol	Memory per symbol	500 symbols cached
2,600	~104 KB	~52 MB
30,000	~1.2 MB	~600 MB
100,000	~4 MB	~2 GB

The guide’s performance advice is to keep the working set inside the CPU’s on-chip cache, and with a 4 MB CPU cache it says it “is strongly advised not to use more than 100,000 bars”. Separately, the default maximum you may type into that field was raised to 1,000,000 in version 6.18. Those two numbers are different things: one is the ceiling on what the dialog accepts, the other is what you should actually type.

✕Common mistake

The failure the documentation calls out by name is typing 50,000 into Number of bars for an end-of-day database. That is 192 years of daily data. It is pure waste — memory allocated for bars that do not exist and never will. Read the equivalent number of days that the dialog displays next to the field before accepting any number.

Two related settings live in Tools -> Preferences, Data tab: In-memory cache (max. symbols) and In-memory cache (max. megabytes), which work together. The documented minimum for the symbol count is 11, and the cache has held up to 100,000 symbols since version 5.70. Resist the urge to shrink the cache: the guide is explicit that a larger cache is faster as long as you do not run out of memory, and shrinking it is only correct for very large databases.

A final reality check from the multi-threading chapter: even on 64-bit Windows, an application struggles beyond roughly 5 GB of data, simply because reading it from disk at a few hundred megabytes per second takes longer than you will tolerate. The first rule for speed is to keep the data small enough to sit in RAM.

∴Research note

There is no published maximum number of symbols in an AmiBroker database. The marketing page claims no limit and mentions handling the entire US stock universe; the only concrete figure anywhere nearby is the 100,000-symbol in-memory cache ceiling, which is a cache size and not a database size. When someone quotes you a symbol limit, ask where it is documented.

Intraday Settings, briefly

File -> Database Settings -> Intraday settings is per-database and controls which intraday data is displayed and how intraday bars compress into daily bars. Its filtering options are Show 24 hours trading (no filtering), Show day session only, Show night session only and Show day and night session only, plus Filter weekends, trading-hours start and end times for the day and night sessions, a choice of whether daily compression uses exchange time, local time or the defined session times, and a time shift for the difference between local and exchange time zones.

You do not need it yet — an end-of-day database ignores all of it — but one property is worth planting now.

✕Common mistake

Filtering hides data, it does not delete it. If bars appear to be missing from a chart but are present when you look at the same symbol in the Quote Editor, session filtering is the cause. The Quote Editor is the documented exception that always shows every bar, which makes it the right tool for answering “is the data actually there?”.

There is also a per-group override — a group can be marked as using its own intraday settings, with its own trading hours. If exactly one instrument behaves oddly around session boundaries, that override is worth checking before anything else.

What changed in your understanding

You now know that an AmiBroker database is a folder with a documented structure, that the data source decides whether AmiBroker owns the quotes or borrows them, and that two of the most prominent fields in Database Settings do nothing at all on the kind of database you are about to build.

Above all, you know what the base time interval is: a floor, chosen once, that determines the finest bar the database can ever produce. You know why it is effectively permanent — not because a control is disabled, but because compressed bars cannot be un-compressed — and you know the documented way of living with that, which is to run more than one database rather than to compromise on one.

The next lesson puts actual prices into the database you are about to create.

Check your understanding

Question 1. You created a database with a base time interval of 15-minute. Which chart intervals can that database produce?

• Only 15-minute
• 15-minute and everything coarser: hourly, daily, weekly and up
• Everything, because AmiBroker interpolates finer bars
• 15-minute and 5-minute, but not 1-minute

Show the answer and why

Answer: 15-minute and everything coarser: hourly, daily, weekly and up

The base interval is a floor. AmiBroker compresses the stored grain upward on demand, so every periodicity at or above 15 minutes is available, and nothing below it ever is. There is no interpolation downwards — the finer detail was never recorded.

Question 2. Your database has data source (local) and you are updating it with AmiQuote. You raise "Number of bars to load" from 2,600 to 20,000 and see no change. Why?

• The setting needs a restart
• The setting has no effect when the data source is (local)
• AmiQuote overrides it
• You must also raise the in-memory cache

Show the answer and why

Answer: The setting has no effect when the data source is (local)

Number of bars to load and Local data storage both apply only to external data sources. On a local database AmiBroker holds whatever bars you imported, so the field is inert. Discovering this by experiment is a rite of passage; discovering it from the documentation is faster.

Question 3. Which statements about changing the base time interval after creation are accurate? Select all that apply.

• The control is permanently greyed out once the database exists
• Changing it does not create finer history that was never stored
• The documented route to a finer grain is a new database plus a fresh import
• For real-time sources the guide says it should be set once at creation time

Show the answer and why

Answer: Changing it does not create finer history that was never stored, The documented route to a finer grain is a new database plus a fresh import, For real-time sources the guide says it should be set once at creation time

The first is a widely repeated claim that the documentation does not make — the control sits in the half of the dialog that stays usable. The reason the choice is permanent in practice is physical rather than about the interface: bars already compressed at a coarser grain cannot be un-compressed, so the setting can be changed while the data cannot.

Question 4. A database holds 800 symbols. What memory does a Number of bars setting of 100,000 imply per symbol, given AmiBroker allocates 40 bytes per bar?

• About 400 KB
• About 4 MB
• About 40 MB
• It depends on the base interval

Show the answer and why

Answer: About 4 MB

100,000 bars at 40 bytes each is about 4 MB per symbol, and AmiBroker allocates it for real rather than reserving it lazily. Across a large cached symbol set that reaches gigabytes, which is why the guide advises against exceeding 100,000 bars on a machine with a 4 MB CPU cache.

Check my answersClear