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 may 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 may create named connection strings for your databases in a visual mode using Connection Manager.

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

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

Symbol and Date are table columns.

Close is a data field. RTD.DB uses the MAX formula by default. You may 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 may set the refresh interval for tables using formulas like this:

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

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

Tick Fields

Tick fields allow understanding value change directions and may 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:

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 may use the formula:

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

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

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:

Tables with Multiple Key Fields

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

To get values for the specific symbol and date, we may 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:

The second formula example is

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

This is a model:

Tables with Single Key Fields

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

You may use a formula like this:

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

This is a model:

Connection Strings

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

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

However, you may 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 may omit the OLEDB provider if the OLEDB connection string starts with 'Provider='.

Also, you may 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 may 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 may 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 may 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;"