Использование 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, как например:
Получение данных из таких таблиц требует использования формул двух типов:
- Получение значений id.
- Получение данных.
Для получения последнего значения 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")
Для получения значений в последней торговой дате, можно использовать два типа формул:
- первой формулой получить последнюю дату
- и потом использовать дату для получения данных.
Ниже приведен пример получения последней (максимальной) даты:
=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;"