Using DB.RTD

Using DB.RTD

Data Formulas

DB.RTD has the following formula format:

=RTD("db.rtd",,"<Connection string or connection name>","<Database table or view name>"
    [,"<Where column>","<Where value>"[,"<Where column>","<Where value>"[,...]]]
    ,"<Data field>")

For example:

=RTD("db.rtd",,"sqlexpress","dbo.quotes","Symbol","AAPL","Close")

=RTD("db.rtd",,"sqlexpress","dbo.historical_prices","Symbol","AAPL","Date",TODAY(),"Close")

As you can see, DB.RTD formulas are similar to the GETPIVOTDATA Excel formula.

In this example, sqlexpress is a name of the connection string. It is a default name for a local Microsoft SQL Express database.

You can create named connection strings for your databases in a visual mode using Connection Manager.

Also, you can use connection strings directly. See Connection Strings below.

dbo.quotes and dbo.historical_prices are names of tables or views. You can specify names including a database like test3.dbo.quotes.

Symbol and Date are table columns.

Close is a data field. DB.RTD uses the MAX formula by default. You can specify the aggregate formulas like COUNT(Close) or MIN(Close).

For the first formula, DB.RTD generates and executes the following SQL query:

SELECT MAX([last]) AS [last] FROM [dbo].[quotes] WHERE [symbol] IN ('AAPL') GROUP BY [symbol]

For the second formula, DB.RTD generates and executes the following SQL query:

SELECT MAX([last]) AS [last] FROM [dbo].[historical_prices]
         WHERE [symbol] IN ('AAPL') AND [date] IN (<TODAY() value>) GROUP BY [symbol], [date]

Refresh Intervals

You can set the refresh interval for tables using formulas like this:

=RTD("db.rtd",,"sqlexpress","dbo.quotes","RTD_RefreshInterval",15*60)

You can set values in seconds like shown above or as a time value like 0:15:00.

You can call the following function to start refreshing data immediately:

=RTD("db.rtd",,"rtd_refresh_now")

Tick Fields

Tick fields allow understanding value change directions and can be used with conditional formatting.

Add the :tick suffix to the underlying data field name. For example:

=RTD("db.rtd",,"sqlexpress","dbo.quotes","symbol","AAPL","last")

=RTD("db.rtd",,"sqlexpress","dbo.quotes","symbol","AAPL","last:tick")

Formula Examples

Tables with Identity Fields

Tables with identity fields have a single column like id with auto-incremented value like this:

Table of stock tick prices

Getting values for such tables requires two types of formulas:

  1. Getting id values.
  2. Getting data values.

To get the last id value for the YHOO symbol, we can use the formula:

=RTD("db.rtd",,"sqlexpress","test3.dbo.tick_history","symbol","YHOO","id")

We can use cell values in regular Excel formulas like this:

=Id formula example for stock tick prices

In the next steps, we use the id value to get other data. For example:

=RTD("db.rtd",,"sqlexpress","test3.dbo.tick_history","id",4,"date")

This is a model:

Date formula example for stock tick prices

Tables with Multiple Key Fields

The typical scenario is getting historical prices from tables like this:

Table of historical stock prices

To get values for the specific symbol and date, we can use a formula:

=RTD("db.rtd",,"sqlexpress","test3.dbo.historical_prices","symbol","YHOO","date",TODAY(),"open")

To get values for the last trade data, we have to use two types of formulas:

  1. the first to get the last date,
  2. and the second to get data.

For example:

=RTD("db.rtd",,"sqlexpress","test3.dbo.historical_prices","symbol","YHOO","date")

This is a model:

Date formula example for historical stock prices

The second formula example is

=RTD("db.rtd",,"sqlexpress","test3.dbo.historical_prices","symbol","YHOO","date","4/13/2017","open")

This is a model:

Open formula example for historical stock prices

Tables with Single Key Fields

This is the simplest case. The tables have a single primary column like this:

Table of stock quotes

You can use a formula like this:

=RTD("db.rtd",,"sqlexpress","test3.dbo.quotes","symbol","YHOO","open")

This is a model:

Date formula example for stock quotes

Connection Strings

As mentioned above, you can use connection strings or connection names in the RTD formulas.

Using the connection names is much better as you can create connections in a visual mode and encrypt the connection strings.

However, you can use the connection strings directly in the following forms:

<.NET provider>;<.NET connection string>

System.Data.OleDb;<OLEDB connection string>

OLEDB;<OLEDB connection string>

<OLEDB connection string>

System.Data.Odbc;<ODBC connection string>

ODBC;<ODBC connection string>

<ODBC connection string>

OLEDB is as a synonym of System.Data.OleDb and ODBC is as a synonym of System.Data.Odbc.

You can omit the OLEDB provider if the OLEDB connection string starts with 'Provider='.

Also, you can omit the ODBC provider if the ODBC connection string starts with 'Driver='.

DB.RTD includes .NET providers for Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, MySQL, PostgreSQL, NuoDB, and SQLite.

You have to download and install .NET, OLEDB or ODBC providers to connect to IBM DB2.

Below are examples of the connection strings.

Connection Strings for Microsoft SQL Server

System.Data.SqlClient;Data Source=.\SQLEXPRESS;Initial Catalog=test;Integrated Security=SSPI

System.Data.SqlClient;Data Source=.\SQLEXPRESS;Initial Catalog=test;Password=Pa$$w0rd!;User ID=sa

System.Data.OleDb;Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=test;Integrated Security=SSPI

System.Data.OleDb;Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=test;Password=Pa$$w0rd!;User ID=sa

System.Data.Odbc;Driver=SQL Server;Server=.\SQLEXPRESS;Database=test;Trusted_Connection=Yes

System.Data.Odbc;Driver=SQL Server;Server=.\SQLEXPRESS;Database=test;Pwd=Pa$$w0rd!;UID=sa

Driver=ODBC Driver 13 for SQL Server;Server=.\SQLEXPRESS;Database=test;Trusted_Connection=Yes

Driver=ODBC Driver 13 for SQL Server;Server=.\SQLEXPRESS;Database=test;Pwd=Pa$$w0rd!;UID=sa

You can download and install Microsoft ODBC Driver 13.1 for SQL Server to use the latest features of Microsoft SQL Server 2016 and Microsoft Azure SQL Database.

However, try the built-in .NET provider first.

Connection Strings for Microsoft SQL Server Compact

System.Data.SqlServerCe.4.0;Data Source=d:\data\test.sdf;Password=

Note that you have to specify the absolute path to the database file.

You can use the following formula to get the path to the file in the active workbook folder:

="System.Data.SqlServerCe.4.0;Data Source="&LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)&"test.sdf;Password="

Connection Strings for Oracle Database

Oracle.DataAccess.Client;Data Source=localhost/test;Password=Pa$$w0rd!;User ID=system;

System.Data.OleDb;Provider=OraOLEDB.Oracle;Password=Pa$$w0rd!;User ID=system;Data Source=localhost/test;PLSQLRSet=True

Provider=OraOLEDB.Oracle;Password=Pa$$w0rd!;User ID=system;Data Source=localhost/test;PLSQLRSet=True

Connection Strings for IBM DB2

IBM.Data.DB2.10.5.0;Server=localhost;Database=test;UserID=db2admin;Password=Pa$$w0rd!

Driver=IBM DB2 ODBC DRIVER;Hostname=localhost;Port=50000;Protocol=TCPIP;Database=test;Pwd=Pa$$w0rd!;UID=db2admin;LONGDATACOMPAT=1

You have to install IBM DB2 .NET provider or ODBC driver to your computer.

Use "IBM Data Server Client Packages" phrase to find the required IBM DB2 connectivity components.

Connection Strings for MySQL

MySql.Data.MySqlClient;Server=localhost;Password=Pa$$w0rd!;User ID=root;Database=test

Driver=MySQL ODBC 5.3 ANSI Driver;Server=localhost;Database=mysql;Pwd=Pa$$w0rd!;UID=root;OPTION=67108864

Driver=MySQL ODBC 5.3 Unicode Driver;Server=localhost;Database=mysql;Pwd=Pa$$w0rd!;UID=root;OPTION=67108864

Connection Strings for PostgreSQL

Npgsql;Server=localhost;Database=test;User Id=postgres;Password=Pa$$word!

Connection Strings for NuoDB

NuoDb.Data.Client;Server=localhost;User=DBA;Password=goalie;Database=test

Connection Strings for SQLite

System.Data.SQLite;Data Source=d:\data\test.db;Version=3;

Note that you have to specify the absolute path to the database file.

You can use the following formula to get the path to the file in the active workbook folder:

="System.Data.SQLite;Data Source="&LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)&"test.db;Version=3;"

Troubleshooting

The formula returns #N/A
Use the rtd_LastMessage field to check errors like this:
=RTD("db.rtd",,"sqlexpress","dbo.quotes","Symbol","AAPL","rtd_LastMessage")
The data are not refreshed
You can check the refresh interval using the formula like this:
=RTD("db.rtd",,"sqlexpress","dbo.quotes","rtd_RefreshInterval")
and set the interval in seconds like
=RTD("db.rtd",,"sqlexpress","dbo.quotes","rtd_RefreshInterval",15*60)