Использование DB RTD

Формулы получения данных

Формулы получения данных имеют следующий формат:

=RTD("db.rtd",,"<Строка подключения или имя подключения>","<Имя таблицы или представления>"
    [,"<Колонка для WHERE>","<Значение для WHERE>"[,"<Колонка для WHERE>","<Значение для WHERE>"[,...]]]
    ,"<Поле данных>")

Например:

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

=RTD("db.rtd",,"sqlexpress","dbo.historical_prices","symbol","AAPL","date",TODAY(),"close")

В русской версии Microsoft Excel вместо RTD используется формула ДРВ, и разделителем полей может быть точка с запятой.

Формулы DB RTD очень похожи на формулы GETPIVOTDATA Microsoft Excel.

В приведенном примере, sqlexpress это имя строки подключения. Это имя по умолчанию для локальной базы данных Microsoft SQL Server Express.

Вы можете создать строки подключения в визуальном режиме, используя мастер подключения.

Также, вы можете использовать строки подключения в формулах или ячейках. См. раздел Строки подключения ниже.

dbo.quotes и dbo.historical_prices - это имена таблиц или представлений. Имена могут включать имя базы данных, например test3.dbo.quotes.

symbol и date - это имена полей таблицы, которые используются в качестве фильтров WHERE.

close - это поле данных. DB RTD по умолчанию использует агрегатную функцию MAX. Вы можете указать агрегатную формулу напрямую, как например, COUNT(close) или MIN(close).

Для первой формулы примера, DB RTD генерирует и выполняет следующий SQL запрос:

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

Для второй формулы примера, DB RTD генерирует и выполняет следующий SQL запрос:

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

Интервалы обновления

Вы можете установить интервал обновления для таблицы, используя поле RTD_RefreshInterval и формулу типа:

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

Вы можете установить значение в секундах, как показано в примере, или же в формате времени, типа 0:15:00.

Для немедленного обновления всех данных можно вызвать следующую функцию:

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

Поля направления изменений

DB RTD включает специальные поля направления изменений данных, которые можно использовать в условном форматировании.

Для получения значения добавьте суффикс :tick к имени поля данных.

Например:

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

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

Примеры использования формул

Таблицы с автоинкрементными полями

Таблицы с автоинкрементными полями обычно содержат единственную колонку первичного ключа, типа id, как например:

Получение данных из таких таблиц требует использования формул двух типов:

1. Получение значений id.
2. Получение данных.

Для получения последнего значения id для тикера YHOO вышеприведенного примера можно использовать формулу:

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

При этом значения можно поместить в ячейки и использовать полностью регулярные формулы:

На следующем шаге для получения данных уже используются полученные значения id. Например:

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

Модель приведена ниже:

Таблицы с множественными полями ключа

Типичный случай таблицы с множественными полями ключа - таблица исторических цен, где ключами выступают тикер и дата:

Для получения значений конкретного тикера и даты можно использовать формулу:

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

Для получения значений в последней торговой дате, можно использовать два типа формул:

1. первой формулой получить последнюю дату
2. и потом использовать дату для получения данных.

Ниже приведен пример получения последней (максимальной) даты:

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

Модель приведена ниже:

Вторая формула выглядит следующим образом:

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

Модель приведена ниже:

Таблицы с единственным полем ключа

Это самый простой случай. Таблицы имеют единственное поле ключа, как symbol в таблице примера:

Для получения данных можно использовать формулу:

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

Модель приведена ниже:

Строки подключения

Как было сказано выше, в формулах RTD можно использовать строки подключения или имена подключений.

Использование имен более предпочтительно, т.к. вы можете настроить строки подключения в визуальном режиме и даже зашифровать их.

Тем не менее, вы можете использовать строки подключения непосредственно в формулах, в следующих форматах:

<.NET провайдер>;<строка подключения .NET провайдера>

System.Data.OleDb;<строка подключения OLEDB>

OLEDB;<строка подключения OLEDB>

<строка подключения OLEDB>

System.Data.Odbc;<строка подключения ODBC>

ODBC;<строка подключения ODBC>

<строка подключения ODBC>

OLEDB - это синоним System.Data.OleDb. ODBC - это синоним System.Data.Odbc.

Вы можете опустить определение типа OLEDB, т.к. строки подключения начинаются со строки "Provider=".

Также вы можете опустить определение типа ODBC, т.к. строки подключения начинаются со строки "Driver=".

DB RTD поставляется с провайдерами .NET для Microsoft SQL Server, Oracle Database, MySQL, PostgreSQL, NuoDB, SQL Server Compact и SQLite.

Для работы с IBM DB2 требуется загрузить и установить провайдер .NET, OLEDB или ODBC для DB2.

Ниже приведены примеры строк подключения.

Строки подключения для 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

Строки подключения для 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

Строки подключения для 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

Для работы требуется установить .NET провайдер или ODBC драйвер для DB2.

Используйте фразу "IBM Data Server Client Packages" для поиска провайдеров подключения к IBM DB2.

Строки подключения для 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

Строки подключения для PostgreSQL

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

Строки подключения для NuoDB

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

Строки подключения для Microsoft SQL Server Compact

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

Для работы требуется указать полный путь к файлу базы данных.

Можно использовать следующую формулу для создания строки к файлу в папке активной книги:

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

Строки подключения для SQLite

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

Для работы требуется указать полный путь к файлу базы данных.

Можно использовать следующую формулу для создания строки к файлу в папке активной книги:

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