Connect > Excel RTD

Excel RTD Server Reference

The Excel RTD Server is a Windows COM component, ProgID AmiRtd.Server, that Excel loads in-process and drives through its RTD function to display live Center fields. This page covers installation, the ami_rtd_config.json schema, authorizing the client on the Center, the RTD topic format, row identity, and the support boundary.

Overview

Excel calls the component for each =RTD(...) cell, passing a cluster name and a dotted topic. The component opens a Center subscription for the topic's table, caches the latest value per topic, and hands each new value back to Excel as it arrives. Subscriptions are reference-counted per table, so many cells on one table share a single Center subscription, and the number of connections the component opens depends on neither the number of cells nor the number of tables. The connection uses the Center subscription protocol over mutual TLS; the 3forge client libraries document that protocol and its type system in full.

System requirements

Component	Requirement
Operating system	Windows 10 or 11, 64-bit
Excel	Microsoft Excel for Windows, 64-bit desktop, with RTD support. Excel Online and Excel for Mac are not supported
Server	A reachable 3forge Enterprise Center listener over TCP or SSL
Runtime	Bundled with the installer (OpenSSL and the MSVC runtime)

Installing the RTD Server

Download the installer from the 3forge Client Portal under "Files." Contact support@3forge.com if it is not available to your account.

Run the per-user installer, AmiRtdServerSetup-<version>.exe, as a normal user.

Do not run the installer elevated

The installer registers the component per-user, under HKCU, so Excel can load it without admin rights, and it aborts if launched as an administrator. Running it elevated would register the component under the administrator account instead of the current one.

The installer:

• Installs to %LOCALAPPDATA%\Ami\RtdServer.
• Registers the component with regsvr32.
• Generates a mutual-TLS client keypair under the installation's keys/ directory and writes the public key, together with instructions for authorizing it, to keys/AUTHORIZE_THIS_CLIENT.txt.
• Records an entry under Add or Remove Programs.

The config file is installed without overwrite, so an edited ami_rtd_config.json survives an upgrade, and an existing keys/client.key.pem is never regenerated, so a reinstall keeps the identity the Center already authorizes.

To register a developer build by hand, run regsvr32 ami_rtd_server.dll from the directory holding the DLL, its runtime DLLs, and ami_rtd_config.json.

Registration falls back from machine to user scope

The component registers itself under HKLM if it can, and under HKCU otherwise. The installer produces a per-user registration because it runs unelevated, so the HKLM write fails and the fallback applies. A manual regsvr32 run from an elevated prompt would register the component machine-wide instead.

Authorizing the client on the Center

A secure Center enforces mutual TLS on every SSL listener and offers no switch to disable it, so a freshly installed client cannot connect until the Center is told to trust it.

Authorize the client in two steps:

1. Append the client's public key, the ssh-rsa line in keys/client.pub, to the file named by the Center's ami.center.ssl.authorized.clients.file property. Its shipped default is keys/3forge.auth. The generated keys/AUTHORIZE_THIS_CLIENT.txt carries the same key alongside its own instructions; where those name a different allow-list file, the property above is authoritative.
2. Restart the Center. It reads the allow-list once, when it builds its SSL context at startup, so a new key takes effect only after a restart.

Restart the Center itself, not just the Center component

Reloading the Center component picks up a new allow-list entry without a full restart, but it leaves the Center partly running: SQL reads and writes continue while the scheduler, the statistics timer, and the change publisher stop. No row update then reaches any subscriber, so cells stay at 0 and the cause is not obvious. A full restart restores normal operation. Do not use a component reload as a shortcut for the restart this step requires.

The generated client certificate is valid for 3650 days from installation. Regenerating the keypair produces a new identity, which invalidates the existing allow-list entry and returns the client to being rejected at the handshake until the new key is authorized.

Renewing the client certificate

The installer leaves openssl.exe and openssl_req.cnf in place so the keypair can be regenerated later. Renewing produces a new identity, so it is the two steps below followed by authorizing the new key exactly as for a new client.

Run both from the installation directory.

1. Read the current certificate's subject, so the new one can keep it:

   .\openssl.exe x509 -in keys\client.crt.pem -noout -subject -dates
   

2. Generate a new keypair and certificate, replacing the old pair, and derive the ssh-rsa line the Center's allow-list needs:

   .\openssl.exe req -x509 -config openssl_req.cnf -newkey rsa:2048 -nodes -sha256 -days 3650 -keyout keys\client.key.pem -out keys\client.crt.pem -subj "/CN=<the CN from step 1>"
   
   & "$env:WINDIR\System32\OpenSSH\ssh-keygen.exe" -y -f keys\client.key.pem | Out-File -Encoding ascii keys\client.pub
   

Then append the new keys/client.pub line to the Center's allow-list and restart the Center, as in Authorizing the client on the Center. Until that is done the renewed client is rejected at the handshake, because its old entry no longer matches.

Write client.pub with Out-File -Encoding ascii, not >

PowerShell's > writes UTF-16 with a byte-order mark, and the Center's allow-list parser rejects it. The file must be plain ASCII. A client.pub written with > looks correct in an editor and fails at the handshake with no indication of why.

The client config cannot satisfy mutual TLS on its own

Setting client_cert_file and client_key_file presents the client's certificate, but the connection succeeds only once that certificate's public key is in the Center's allow-list. Until then the Center rejects the connection at the TLS handshake, and the rejection surfaces only in ami_rtd.log or a direct probe, not as an error in Excel. See Troubleshooting.

To authenticate the Center in turn, obtain the Center's own ssh-rsa public key and name it in trusted_server_pub_file (see the config schema). This is optional and pins the server; omitting it still connects.

Configuring ami_rtd_config.json

The component reads ami_rtd_config.json from the same directory as the DLL when Excel first starts it. Edit it to name the Center servers to connect to. Restart Excel after editing, because the config is read when the server starts.

{
  "clusters": [
    {
      "name": "N1",
      "host": "your-center-host",
      "center_shards": [
        {
          "port": "3270",
          "use_ssl": true,
          "client_cert_file": "keys/client.crt.pem",
          "client_key_file": "keys/client.key.pem",
          "trusted_server_pub_file": "keys/3forge.pub"
        }
      ]
    }
  ]
}

Field	Required	Default	Meaning
clusters[].name	Yes		Cluster name, used as the first RTD argument (e.g: N1)
clusters[].host	Yes		Center hostname or IP
clusters[].username	No		Login user, only for a query gateway
clusters[].password	No		That user's password
clusters[].keys	No		Row-identity columns for a table with no server-supplied object id (see Identifying rows)
center_shards[].port	Yes		Center port
center_shards[].use_ssl	No	true	true for SSL/TLS
center_shards[].client_cert_file	With SSL		Path to the client PEM certificate
center_shards[].client_key_file	With SSL		Path to the client PEM private key
center_shards[].trusted_server_pub_file	No		Path to the Center's ssh-rsa public key, to pin the server
center_shards[].client_key_password	No		Passphrase for an encrypted client key
center_shards[].failover	No		Backup endpoints, each {host, port}

The shipped config points at example hosts and must be edited before use. Relative paths in these fields resolve against the directory holding ami_rtd_config.json, not against Excel's working directory; absolute paths are used as given.

To subscribe across several Centers, add more entries to center_shards; each may carry its own failover list.

Connecting through a query gateway

A cluster may point at a query gateway, a Center-protocol endpoint on a separate port (default 3406), instead of a Center. A gateway with an authenticator configured refuses any subscriber that has not logged in, and the mutual-TLS certificate does not satisfy that login. Set username and password on the cluster for a gateway:

{
  "name": "TGW",
  "host": "your-gateway-host",
  "username": "demo",
  "password": "demo123",
  "center_shards": [
    { "port": "3406", "use_ssl": true,
      "client_cert_file": "keys/client.crt.pem",
      "client_key_file": "keys/client.key.pem" }
  ]
}

Credentials are for a gateway only, and their absence is silent

A plain Center is sent no login and needs no username or password. Setting them against a plain Center makes the component send a login request that a Center has no route for: the Center returns an exception, the subscription is destroyed, and ami_rtd.log records only Server returned an exception (Type::Throwable); see AMI server log for details, which points at a log an Excel user cannot reach. A gateway given no credentials, or the wrong ones, accepts the connection and the subscription and then sends no data, so every cell stays 0 exactly as if the table were empty.

ami_rtd.log prints a logged in line on the no-credential path even though no login is sent, so that line confirms neither a login nor a successful authentication.

Identifying rows with keys

The middle component of a topic, the AAPL in Trades.AAPL.price, names which row the cell follows. The client resolves that identity in one of two ways:

• From an object id the server supplies out-of-band with the row. This is not available for every table, and a table's index alone does not guarantee it.
• From one or more data columns named in a keys entry, which the client then matches against the topic's middle component. Name the column or columns that identify a row per table:

"keys": [
  { "table": "crypto", "key": ["symbol"] }
]

• key accepts a bare string for the single-column case ("key": "symbol").
• Several columns form a composite key, joined with | in the order listed: "key": ["exchange","symbol"] is addressed as crypto.NYSE|BTC.price. A key value may contain neither a dot nor a |, because the topic string is dot-delimited and a composite key is |-delimited.

A | inside a key value silently merges two rows

Rows whose key values join to the same string are indistinguishable to the client. An exchange of NY|SE with a symbol of ZFAAPL, and an exchange of NY with a symbol of SE|ZFAAPL, both resolve to the identity NY|SE|ZFAAPL. Both rows are identified under that one string, nothing is logged, and a cell watching it shows whichever of the two updated most recently, so the cell alternates between two different instruments. Choose key columns whose values cannot contain |.

Add a keys entry when no value appears

If the connection and subscription succeed and the table is publishing, but no value ever arrives and every cell on the table stays at 0, the client cannot match rows to the topic. Add a keys entry naming the row's identifying column and reconnect. Once identity resolves, ami_rtd.log prints a row identified line for each row and values begin to arrive. Add a keys entry whenever values do not surface, rather than assuming the table's index supplies identity on its own.

The RTD formula and topic format

Enter an RTD formula in a cell:

=RTD("AmiRtd.Server", , "<cluster>", "<table>.<object_id>.<field>")

=RTD("AmiRtd.Server",,"N1","Trades.AAPL.price")

• AmiRtd.Server is the fixed ProgID. It is not case-sensitive, and Excel normalizes it when it saves the formula, so a stored formula may read amirtd.server.
• The second argument, the server, is empty for a local component.
• <cluster> is a cluster name from the config.
• The topic <table>.<object_id>.<field> identifies the table, the row's object id, and the field to show. Only the first two dots are separators, so a field name may itself contain dots.

A topic with fewer than two dots makes no subscription, but nothing reports the failure: the component returns success to Excel and the cell shows 0 permanently.

A cell shows 0 until a value for its topic arrives, then shows the live value and refreshes as the Center pushes updates. Only the first topic on a table receives the table's current values when the subscription opens; every other cell on that table shows 0 until the field it watches next changes on the Center.

The Center sends only the columns that changed, and emits nothing at all when a write leaves every column unchanged. A field rewritten with the value it already holds therefore never populates a cell, however often it is written, and a cell is updated only when its own field changes rather than whenever its row does.

A cell reading 0 may not hold a zero value

0 is what an RTD cell holds before its first update, and it is an ordinary number rather than an Excel error, so it flows into any formula that reads it without raising anything. A field that has not changed since the subscription opened, a malformed topic, and a topic naming a table that does not exist all present identically. Confirm a 0 against the Center before treating it as data. See Troubleshooting.

Center types are not preserved in a cell

A cell receives the value and no indication of the column's Center type. A timestamp column arrives as epoch milliseconds under Excel's General format, 1790073701499 rather than a date, and is indistinguishable from an ordinary number. To show it as a date, convert it in the sheet and apply a date format, for example =A1/86400000+25569. The delivered value is UTC and carries no timezone; the Center's own configured timezone does not apply to it.

How it works

1. Excel calls the component for each cell, passing the cluster and topic.
2. The component ensures a Center subscription to the topic's table is active, reusing an existing one where a cell already watches that table.
3. As the Center pushes row updates, the component records the latest value per topic and signals Excel that new data is available.
4. Excel reads the updated values back.

Many cells watching the same table.object_id.field share one cached value, and many cells on the same table share one Center subscription.

Excel's own Application.RTD.ThrottleInterval governs how often cells refresh, and it defaults to 2000 ms; the component has no refresh setting of its own. A value that changes more than once inside that window is conflated: the cell receives the most recent value and the intermediate ones are discarded, so a cell is a current-value view rather than a tick stream. Setting Application.RTD.ThrottleInterval = 0 in the VBA "Immediate Window" refreshes on every update.

Logging and diagnostics

The component writes an append-only log, ami_rtd.log, in the same directory as the DLL. It records the connection, login, subscription, and error lifecycle, and it is the first place to look when a cell does not update.

A dropped connection leaves cells showing their last value

If the Center becomes unreachable, every cell keeps the last value it received. Nothing in the sheet changes: no error, no blank cell, and no staleness indicator, so a frozen sheet is indistinguishable from a live one. The only signal is a disconnected line in ami_rtd.log. The component reconnects on its own once the Center is reachable again, and cells resume as data arrives, but each cell holds its stale value throughout the outage and until its own field next changes.

A console driver, ami_rtd_test_driver.exe, exercises the component without Excel and is the quickest way to confirm a topic delivers data. It takes the cluster and topic as arguments:

ami_rtd_test_driver.exe N1 Trades.AAPL.price

It prints an update notification and the field values as they arrive.

A driver or COM success line does not mean the connection succeeded

The driver reports its startup and COM steps as [OK], and Excel loads the component, before any network connection is confirmed. These indicate the component started, not that it connected or authenticated. Confirm the connection in ami_rtd.log, not from an [OK] line or a loaded cell.

What the log does not report

ami_rtd.log records what the component attempted, not what the Center made of it. It reports a subscription as successful whether or not the table exists, prints a logged in line on the no-credential path where no login was sent at all, and reports an unreachable host for a client the Center rejected at the handshake. Confirm the table name, the row identity, and the client's authorization against the Center itself rather than inferring them from the log.

To see the subscriptions a sheet has actually opened, run ami_center.showSubscriptions() from the f1 administration console. The component appears under the username ami_client_cpp, with one row per subscribed table and a single SubscriberID shared across them, so a sheet of five cells across three tables shows three rows rather than five. FocusedRows gives the number of identified rows in each table. Where the config names localhost, RemoteIP is an IPv6 loopback address rather than 127.0.0.1.

This is the only server-side view of subscriptions

There is no SQL equivalent, and neither SHOW CONNECTIONS nor the __CONNECTION table reports them.

What this does not support

• No CA trust chain: the Center pins the client by public key from a self-signed certificate, with no certificate authority, and server authentication is optional key pinning through trusted_server_pub_file.
• No write-back, SQL, or publishing: a cell subscribes and displays but cannot write to the Center, so use the 3forge client libraries for query, insert, and publish.
• No Excel Online or Excel for Mac: only 64-bit Windows desktop Excel is supported; see System requirements.
• No built-in way to browse a table's rows or columns: the component ships no schema browser, so a topic must be built from a known table, object id, and field.