Backfill and Database Maintenance

An intraday database fills itself so quietly that most people never think about the mechanism until something is wrong. You add a symbol, you look at a chart, bars appear. It feels like the database has “the data” now.

What actually happened is narrower than that, and knowing exactly how narrow is the difference between a database you can trust and one that produces a backtest whose earliest trade is six weeks after the date you asked for.

✱AmiBroker Professional required

Backfill is on demand, per symbol, on first access

In a plug-in-fed database AmiBroker does not download anything until it needs it. Backfill is requested on first access to a symbol — the first time you display its chart, or the first time an Analysis run touches it — and it is requested for that symbol alone.

Three consequences follow, and all three are load-bearing.

A symbol you have never looked at has no history. It is in the symbol tree. It has a name. It has nothing else.

A first Analysis pass can silently analyse nothing. The request goes out, the run does not wait for the answer, and the formula executes against whatever short or empty array exists at that instant. Nothing errors. You get a result. Run it again five minutes later and you get a different one.

History depth is measured from the moment of first access. A symbol you added last week and one you added last year, on the same feed with the same settings, end up with different amounts of history — because each was backfilled from its own first-access date, backwards, as far as that vendor and your bar count allow.

What happens the first time a formula touches a symbol

1. 1AFL asks for the symbol’s arraysA chart repaint, or an Analysis run reaching that symbol
2. 2AmiBroker asks the plug-inGetQuotes for this symbol only
3. 3The plug-in requests backfill from the vendorBounded by the vendor’s depth and by Number of bars to load
4. 4The formula runs anyway, on whatever has arrivedUnless "Wait for backfill" is set, nothing blocks here
5. 5Bars arrive and are storedIf Local data storage is enabled
6. 6The next run sees a longer arrayWhich is why two identical runs disagree

The gap at step four is where most 'my scan found nothing' reports come from.

Making it happen in bulk

The documented way to backfill an entire database is to abuse a scan, and it works because of one Analysis setting.

1. In the Formula Editor write Buy = 1; and press Send to Analysis.
2. In the Analysis window set Apply to to All symbols.
3. Set Range to 1 recent bar.
4. Open the Settings split-button menu and tick Wait for backfill.
5. Press Scan.

Wait for backfill makes the run block on each symbol until the vendor’s backfill has actually arrived, which converts a scan into a mass-backfill tool. The range of one bar keeps it fast; you are not analysing anything, you are touching every symbol so that each one triggers its own request.

!Important

Wait for backfill does nothing in several situations, and knowing them saves you from concluding your database is broken: it is a no-op on local databases, on end-of-day plug-ins such as FastTrack, Quotes Plus and TC2000/TCNet, and with QuoteTracker, which manages its own backfills and exposes no control. Many third-party plug-ins simply do not implement the capability at all.

Interactive Brokers needs a different procedure entirely, because its backfill is one symbol at a time through Trader Workstation. The documented route is to add the symbols to the Real-Time Quote window, right-click the plug-in status area to choose a Backfill length, then choose Backfill All RT quote window symbols, which runs sequentially. The knowledge base groups sources into three classes on exactly this axis: unlimited backfill (eSignal, IQFeed), limited and one at a time (Interactive Brokers), and no backfill at all (DDE, where ASCII import is the only route to history).

Force backfill, and the two times you need it

Right-clicking the plug-in status area at the bottom right of the main window gives Reconnect, Shutdown (Disconnect) and Force backfill. The status light itself is worth learning: OK in green is healthy, WAIT in yellow is connecting or partially connected, ERR in red means a broken connection that the plug-in will keep retrying, and SHUT in purple means a serious failure after which the plug-in will not retry by itself.

Force backfill is normally unnecessary, because plug-ins backfill automatically when they see a gap. It is required in two documented cases:

• After enlarging Number of bars to load. Symbols already backfilled at the smaller count do not gain the extra bars on their own.
• To re-pull history the vendor may have repaired. Documented as working well with eSignal, which repairs bad ticks server-side.

What the vendors document, and how old the documents are

!Important

Everything in this table is what AmiBroker’s own pages state. The provider catalogue page carries the line “THIS PAGE IS CURRENT AS OF March 2, 2023”; the IQFeed setup page links a client version from a much earlier era; the mixed EOD/intraday note names plug-in versions “as of June 2008”. Two official pages even disagree with each other about IQFeed’s one-minute depth. Use this as a guide to the shape of each vendor’s offer, and confirm the current numbers with the vendor before you size a database around them.

Source	Documented intraday backfill depth	Notes
eSignal	10 days of tick, 60 days of minute bars; roughly 10 years end-of-day	Supports Wait for backfill; documented as repairing bad ticks server-side
DTN IQFeed	Catalogue page: 100+ days of tick, 10 years of 1-minute, 20+ years end-of-day. Setup page: 100,000 bars is “maximum history (8 months)”	The two official pages disagree. Supports Wait for backfill. Feed documented as unfiltered
Interactive Brokers	Up to 30 days of 1-minute (an experimental 180 days is mentioned); up to 2,000 bars at 1/5/15-second	Five days of 1-minute per request; one backfill at a time; throttled to 60 requests per five minutes; no backfill on demo accounts
QuoteTracker	Maximum five days, usually one	Wait for backfill has no effect
DDE link	None at all	ASCII import is the only route to history

Two further points that are easy to miss. Interactive Brokers’ finest backfill resolution is one-second bars even if your base interval is tick — so a tick database on IB has a grain it cannot backfill into. And AmiBroker’s own IB page recommends eSignal or IQFeed instead “for much faster backfills”, which is an unusually direct statement for a vendor-neutral document.

Verifying what you actually have

The three checks below are ordered by how quickly they run.

Look at the ends. Open a chart, press the End key to go to the newest bar, then scroll back. Note the date at which the chart stops. Do that for the symbol you added first and the symbol you added most recently. If they differ, the database is not uniform, which is normal and worth knowing.

Look at a session in the Quote Editor. The Quote Editor always shows every stored bar, unfiltered. That makes it the arbiter whenever a chart and your expectations disagree: bars in the editor but not on the chart mean filtering; bars in neither mean missing data.

Report every symbol at once. That is what the formula below does.

Goal

Produce one row per symbol answering: how many bars, over what dates, across how many sessions, at what average and extreme session lengths, and how old the newest bar is. Sorted so that the symbols with the shallowest history float to the top, because those are the ones that will silently shorten every study run across this database.

Complete formula

Complete runnable AFL

history-depth-report.afl

// ===========================================================================
//  History depth report
//  One row per symbol, answering the question a backfill never answers by
//  itself: how much history did each symbol actually end up with? Symbols in
//  the same database, added on different days, from the same feed, routinely
//  hold very different amounts of history - and nothing on a chart says so
//  until you scroll back far enough to fall off the end of the data.
//
//  HOW TO RUN
//    Analysis window: Apply to = All symbols (or a watch list),
//    Range = All quotations, then Explore.
//    Run it on an intraday database with the chart interval set to the base
//    interval, so that "Bars" means base-interval bars.
//
//  WHAT EACH COLUMN MEANS
//    Bars              bars this symbol supplied inside the range
//    First / Last      the ends of the loaded history
//    Cal.days          calendar days between the first and the last bar
//    Sessions          distinct calendar dates covered
//    Bars/session      Bars divided by Sessions
//    Fullest           bars in the busiest single session
//    Thinnest          bars in the emptiest single session
//    Last bar age h    hours between the newest bar and your computer clock
//
//  ASSUMPTIONS, STATED SO THEY CAN BE CHECKED
//    - A short history is not automatically a fault. A symbol listed last
//      month cannot have a year of bars, and a vendor's backfill depth is a
//      limit you were told about in advance. This report tells you what you
//      have; deciding whether that is wrong is your job.
//    - A session here is a distinct calendar date. For instruments that trade
//      overnight, one exchange session spans two dates and the session count
//      will be roughly double what a trader would say.
//    - "Last bar age" compares a bar timestamp against your local clock. If
//      the database time shift is set so that bar times are exchange times
//      rather than local times, the age is off by the shift. Read the shift
//      with Status("timeshift") before drawing conclusions from this column.
//    - Thinnest sessions are frequently real: half-day sessions before public
//      holidays are short by design, and an illiquid symbol simply prints no
//      bar in a minute in which nothing traded.
// ===========================================================================

Filter = Status( "lastbarinrange" );
SetOption( "NoDefaultColumns", True );

BarNumber = Cum( 1 );
BarsInRange = LastValue( BarNumber );

FirstBarDateTime = ValueWhen( BarNumber == 1, DateTime() );

// Cum(1) == 1 forces the first bar to open a session, because Ref() has no
// previous bar to compare against there.
NewSession   = BarNumber == 1 OR Day() != Ref( Day(), -1 );
SessionCount = Cum( NewSession );

// Bars elapsed since the session opened, counted on every bar.
SessionStartBar = ValueWhen( NewSession, BarNumber );
BarsInSession   = BarNumber - SessionStartBar + 1;

// The last bar of a session is the bar before the next session opens. On the
// final bar of the array there is no next bar, so the analysis-range flag is
// used to make sure the newest session is still measured.
EndOfSession = Nz( Ref( NewSession, 1 ) ) OR Status( "lastbarinrange" );
SessionTotal = IIf( EndOfSession, BarsInSession, Null );

// Highest() and Lowest() are running extremes over everything seen so far, so
// LastValue() of them is the extreme over the whole loaded history.
FullestSession  = LastValue( Highest( Nz( SessionTotal ) ) );
ThinnestSession = LastValue( Lowest( IIf( EndOfSession, BarsInSession, 999999 ) ) );

CalendarDays = DateTimeDiff( DateTime(), FirstBarDateTime ) / 86400;

// Now(5) returns the current date and time as a DateTime number.
LastBarAgeHours = DateTimeDiff( Now( 5 ), DateTime() ) / 3600;

AddTextColumn( Name(), "Symbol", 1.0, colorDefault, colorDefault, 80 );
AddColumn( BarsInRange,      "Bars",         1.0 );
AddColumn( FirstBarDateTime, "First",        formatDateTime );
AddColumn( DateTime(),       "Last",         formatDateTime );
AddColumn( CalendarDays,     "Cal.days",     1.1 );
AddColumn( SessionCount,     "Sessions",     1.0 );
AddColumn( IIf( SessionCount > 0, BarsInRange / SessionCount, Null ),
                             "Bars/session", 1.1 );
AddColumn( FullestSession,   "Fullest",      1.0 );
AddColumn( ThinnestSession,  "Thinnest",     1.0 );
AddColumn( LastBarAgeHours,  "Last bar age h", 1.1 );

// Newest first bar at the top: the symbols with the least history are the ones
// that will silently shorten every study you run across this database.
SetSortColumns( -3 );

Download history-depth-report.afl89 lines

How it works

Filter = Status("lastbarinrange") is the standard idiom for a one-row-per-symbol exploration: the row is emitted on the final bar of the range, by which point every running total has accumulated the whole history.

The bar accounting uses Cum(1) to number bars from one, so LastValue of it is the bar count and ValueWhen(BarNumber == 1, DateTime()) is the first timestamp. Sessions are detected where the day number changes, with the first bar forced to open a session because Ref() has no earlier bar there.

The session extremes need one more idea. BarsInSession counts bars since the session opened, so its value on the last bar of a session is that session’s length. EndOfSession finds those bars by looking one bar forward at NewSession — a forward reference that is entirely legitimate in a data audit and would be look-ahead bias in a trading rule. Highest() and Lowest() are running extremes over everything seen so far, so LastValue() of them gives the fullest and thinnest session across the whole loaded history.

Now(5) returns the current date and time as a DateTime number, which DateTimeDiff turns into the age of the newest bar in hours.

Key functions

• Status( "lastbarinrange" ) — true on the final bar of the analysis range.
• ValueWhen( condition, array ) — carries forward the value the array had the last time the condition was true.
• Highest( array ) / Lowest( array ) — running extremes since the first bar.
• Now( 5 ) — the current date and time as a DateTime number.
• SetSortColumns( -3 ) — sorts the result descending on the third column.

Expected result

A table with one row per symbol. On a healthy uniform database the First column is nearly identical across symbols and Bars/session clusters tightly. On a database that has grown over time — which is most of them — First varies by weeks and the newest additions sit at the top of the sort.

Test it

Add a symbol you have never displayed, then run the report. It should appear with zero or very few bars. Display its chart, wait for the backfill, run the report again: the row should change. That is the on-demand mechanism made visible in two runs.

Common errors

Running it with the chart on a compressed interval rather than the base interval gives bar counts divided by the compression factor — correct, but not what you meant. Running it while Bar Replay is paused shortens every history identically, which looks alarming and is not real. And a “Last bar age” that is out by a constant number of hours across every symbol is a time-shift setting, not a stale feed.

Extension

Add a column that divides the bar count by your Number of bars to load setting. Any symbol at 100 per cent of it is not short of history because of the vendor — it is short because your own cap is what stopped it.

Removing bad bars

AmiBroker offers three routes, in increasing order of bluntness.

• Edit → Delete quotation removes the currently selected quote. Select it by clicking the chart first; a vertical line marks the selected date and quote.
• Edit → Delete session removes the quotations of all symbols for a given day. It is the right tool for an exchange day your feed recorded wrongly across the board, and the wrong tool for anything narrower.
• The Quote Editor lets you edit or delete individual quotes directly.

⚠Warning

Back up the database folder before any bulk deletion. The User’s Guide states explicitly that removing a symbol and performing a split cannot be undone, and there is no general undo for data operations. A database is a folder; copying it takes seconds and has saved more evenings than any other habit in this part.

In a plug-in-fed database the Quote Editor is restricted in ways that catch people out: it works only at 1-minute or higher intervals, only on symbols that are fully backfilled with no backfill in progress, and the last three bars cannot be edited because they are held in the plug-in’s cache.

⌀Data quality warning

Do not import ASCII data into a plug-in-fed real-time database. The plug-in will eventually overwrite the imports, and importing end-of-day data over an intraday database is documented as corrupting it. There are exactly two safe cases: importing intraday data at exactly the same bar interval, or importing symbols that are not in the database at all — in which case the importer marks them “use only local database for this symbol”, visible in the Information window, and the feed leaves them alone. That second case is the supported way to bring in an auxiliary series for Foreign().

Compacting and repairing: what actually exists

This is the section where a course usually describes a “Compact database” command. AmiBroker does not document one, and this course is not going to invent it. What the documentation does give you is a small, specific set of repair operations, and it is worth knowing them precisely because the error messages they fix read far more alarming than the fixes are.

• broker.master is the symbol table, kept for fast loading. Deleting it is safe: AmiBroker rebuilds it from the individual symbol files. This is the documented repair for a corrupted or inconsistent symbol list.
• broker.workspace holds the database settings, category names and global advance/decline data. Deleting it loses your database settings and resets category names to defaults. It is a last resort, and it is also the guide’s stated trick for forcing an existing database to re-read the default category name templates.
• broker.newcharts maps chart panes to formula files. Deleting it produces “formula file empty or cannot be found” in every chart — and your formulas are not lost. Only the pane-to-formula reference is, and you re-insert them.
• The troubleshooting chapter’s startup-crash procedure is a sequence of renames: rename the default database directory, then all DEFAULT.AWL layout files, then broker.charts and broker.bcharts, then default.layout, testing after each. It is a bisection, not a repair tool.
• Re-running the installer over an existing installation is documented as the fix for missing program components — and you should not uninstall first if you want your settings preserved.
• For plug-in databases, the closest thing to a repair is File → Database Settings → Flush cache, which forces fresh retrieval from the plug-in, followed by Force backfill.

!Important

“Back up the database” means copying the whole database folder, not one file. A database is a directory of binary files organised into 0-9, a-z and _ subdirectories, plus broker.master and broker.workspace at the root. Copying one file backs up nothing useful.

One performance note that belongs in maintenance rather than tuning: the guide recommends turning off antivirus live scanning for the data files inside the AmiBroker folder, on the grounds that a database directory is many thousands of small non-executable files and intercepting every access to them is expensive. Read the recommendation in the performance chapter and make your own decision; this course is not going to tell you to disable security software.

A maintenance routine

After every session, if you run intraday

• Check the plug-in status light is green before you rely on anything.
• Run the completeness check from the next page over the current day.
• Note any symbol whose newest bar age is much larger than the others’.

Weekly

• Run the history depth report over the whole database. Compare against last week’s: the first-bar dates should be stable or older, never newer.
• Force backfill any symbol whose history looks short, and see whether it changes.
• Copy the database folder to a backup location.

Whenever you change a setting

• After raising Number of bars to load: force backfill on already-backfilled symbols, then re-run the depth report to confirm it worked.
• After changing session times or time shift: re-run the completeness check. Session boundaries move, and start and end times move with them.
• After adding symbols to an external source: File → Database Settings → Configure → RETRIEVE.

Monthly

• Re-read your vendor’s current documentation for backfill depth. It changes, and AmiBroker’s copy of it is older than you think.
• Check whether your bar count is still the right size for the history you actually use.

If you have no live feed

Backfill in the plug-in sense does not apply to a local database, and the whole of the first half of this lesson describes machinery you do not have. What replaces it is an import routine, and the same discipline transfers directly.

• On-demand versus bulk becomes: import the file for one symbol to check the format definition, then import the whole directory. The ASCII importer accepts multiple files selected with Ctrl or Shift.
• Vendor backfill limits become the date range of the files you can obtain. The limit is just as real and rather more visible.
• Force backfill becomes re-importing. Imports of intraday data at the same bar interval are additive, so re-importing an overlapping file repairs a gap without disturbing the rest.
• Verifying completeness is identical. Both formulas in this part run unchanged on a local database, and the completeness check on the next page was written to be portable for exactly this reason.
• Repair is identical too, because broker.master, broker.workspace and the folder-copy backup are database facts, not feed facts.

The lab at the end of this part walks the import route step by step.

Recap

Backfill is on demand, per symbol, triggered by first access, and it does not block your formula unless you tell it to — which is why two identical Analysis runs can disagree. The bulk route is a one-bar scan over all symbols with Wait for backfill ticked, except on Interactive Brokers, on local databases, on end-of-day plug-ins and on QuoteTracker, where it does nothing. Vendor depths vary by an order of magnitude and AmiBroker’s own record of them is dated and in one case self-contradictory.

Verification is not optional, because none of the failure modes announce themselves. AmiBroker has no compact-or-repair command; it has a small set of specific, documented recoveries, most of which are less destructive than their error messages suggest, plus one — deleting broker.workspace — that costs you your settings.

The next page is a diagnostic exercise built from all of this, and it leaves you with the completeness check.

Check your understanding

Question 1. You run a scan over 200 symbols in a plug-in-fed intraday database for the first time. It completes in four seconds and finds nothing. What is the most likely explanation?

• The formula has a logic error
• Backfill was requested for each symbol but the run did not wait, so most arrays were empty or very short
• The plug-in is disconnected
• The base interval is wrong for the formula

Show the answer and why

Answer: Backfill was requested for each symbol but the run did not wait, so most arrays were empty or very short

Backfill is requested on first access and does not block the run. The fix is Settings → Wait for backfill, which is also how a one-bar scan over all symbols becomes a mass-backfill tool. A logic error would not usually produce a suspiciously fast run.

Question 2. You raise Number of bars to load from 30,000 to 100,000 on an IQFeed database. Which symbols gain the extra history?

• All of them, at the next chart refresh
• Only symbols not yet backfilled; already-backfilled symbols need a Force backfill
• None until AmiBroker is restarted
• Only symbols in the Real-Time Quote window

Show the answer and why

Answer: Only symbols not yet backfilled; already-backfilled symbols need a Force backfill

The guide states this explicitly: after enlarging the bar count you must force a backfill for symbols already backfilled with the smaller count. Skipping this step is one of the standard ways to end up with a database that is uneven for no visible reason.

Question 3. Which of these are documented as safe operations on a database folder? Select all that apply.

• Deleting broker.master, which AmiBroker rebuilds from the symbol files
• Deleting broker.workspace, which loses the database settings and category names
• Deleting broker.newcharts, which loses chart-to-formula references but not the formulas
• Deleting the whole 0-9 and a-z subdirectory tree

Show the answer and why

Answer: Deleting broker.master, which AmiBroker rebuilds from the symbol files, Deleting broker.newcharts, which loses chart-to-formula references but not the formulas

broker.master is a cache of information held redundantly in the symbol files. broker.newcharts loses only references; the error message is frightening and the formulas are intact. broker.workspace costs you settings, and the symbol subdirectories are the data itself.

Question 4. A colleague suggests importing your end-of-day CSV history into your IQFeed-fed intraday database so everything is in one place. What should you say?

• Good idea, provided the symbols already exist
• It is documented as corrupting an intraday database; use a separate end-of-day database
• It works if you enable mixed EOD/Intraday mode first
• It works if you disable local data storage

Show the answer and why

Answer: It is documented as corrupting an intraday database; use a separate end-of-day database

The guide is direct: do not import ASCII into a plug-in-fed real-time database, and importing end-of-day data over an intraday database corrupts it. Two supported exceptions exist — intraday data at exactly the same interval, and symbols absent from the database, which the importer then marks as local-only.

Question 5. What is the most reliable way to tell whether bars are missing or merely hidden?

• Compare two charts at different intervals
• Open the Quote Editor, which always shows every stored bar regardless of filtering
• Force a backfill and see whether anything changes
• Check the plug-in status light

Show the answer and why

Answer: Open the Quote Editor, which always shows every stored bar regardless of filtering

Session filtering hides bars without deleting them, and the Quote Editor is documented as the window that always shows everything. Forcing a backfill would eventually answer the question too, but it costs vendor requests and time to answer something one window settles immediately.

Check my answersClear