Database Providers

Database Providers

Basics

RealTimeToExcel allows getting and updating data from databases in Microsoft Excel using native RTD formulas.

Excel formula format:

=RTD("gartle.rtd",,"<Database provider>","<Database table name>",<First key column value>
    [,<Second key column value>[,...]],"<Data field>")

For example:

=RTD("gartle.rtd",,"rtd-sqlce","QuotesYahoo","AAPL","Last")
=RTD("gartle.rtd",,"rtd-mysql","fundamentals_yahoo","AAPL","OneYearTargetPrice")
=RTD("gartle.rtd",,"rtd-mysql","fundamentals_day_history_yahoo","AAPL","12/31/2014","OneYearTargetPrice")

In the example above, the first formula uses the rtd-sqlce database provider, and the second one uses the rtd-mysql database provider.

Database providers are the text files located in the DataProviders subdirectory and named as <database provider>.config.
So, you can create multiple database providers for various databases on various database platforms.

Here is an example of the rtd-sqlce database provider:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="RefreshInterval" value="00:00:05" />
    <add key="NextRequestDelay" value="0" />
    <add key="DataStartTime" value="09:20:00 -05:00" />
    <add key="DataEndTime" value="16:30:00 -05:00" />
    <add key="LoadEndTime" value="24:00:00 -05:00" />
    <add key="LoadOnSaturday" value="1" />
    <add key="LoadOnSunday" value="1" />
    <add key="DefaultSchema" value="" />
  </appSettings>
  <connectionStrings>
    <add name="sqlce-rtdxls" connectionString="Data Source=%LOCALAPPDATA%\Gartle\RealTimeToDB\rtd.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>
</configuration>

The appSettings section contains configuration parameters described below.

You can use any text file editor to edit the parameters. For example, notepad.exe.

The connectionStrings section contains the connection string to the target database.

RealTimeToExcel uses only the first connection string. So, the database provider describes only one database connection.

You can use gConnectionManager to edit connection strings in visual mode and to encrypt the strings.

gConnectionManager is a companion product integrated with RealTimeToExcel.

Table names, key columns, and data fields depend on a database. Read the database documentation to get the correct values.

Key Columns

Database tables can contain several keys with different columns.

RealTimeToExcel uses the first available primary key, unique key, or unique index with the same number of key columns as specified in the RTD formula.

First of all, only unique keys and indexes are used. So, each formula gets only the one value identified by the key and the data column. This works fast.

The second. The first key or index with the same number of columns is used as there is no way to detect the key using the simplest formula format.
Otherwise, the Excel formula should contain column names like "Symbol", "YHOO", "Date", "31/12/2014", etc. This approach is not implemented.

This rule requires eliminating the ambiguity of keys.

For example, a table should not have an ID auto-incremented field and a unique key for another column.
If the second column is unique in the table, remove the ID column.

Another example, two unique keys or indexes are in a table: (Symbol, Date) and (Date, Symbol).
To get one unique key per table, make another index non-unique.

Table, Views, and Stored Procedures

Only tables can be used in formulas.

The views have no metadata about keys and indexes. So there is no way to apply the table logic to views.

Stored procedures potentially can be used in formulas as the procedures have well-known parameters and developers can secure selecting single row only.
If you interested in this feature, please, contact us.

Database Provider Configuration Parameters

DefaultSchema
This string value defines the default schema of the databases tables.
This feature allows users to use only table names in Excel formulas.
The default schema is empty.
RefreshInterval
This time value defines the interval of row data refreshing.
The default value is 00:00:05, 5 seconds.
NextRequestDelay
This integer value defines the interval between requests to a data source server, in milliseconds.
The default value is 0, no delay.
DataStartTime
This time value defines the time when the data source server starts publishing data.
For example, this is 09:30:00 -05:00 for US exchanges.
The default value is 09:20:00 -05:00.
DataEndTime
This time value defines the time when the data source server ends publishing data.
For example, this is 16:00:00 -05:00 for US exchanges.
The default value is 16:30:00 -05:00.
LoadEndTime
This time value defines the latest time when today's data can be loaded.
For example, this is 24:00:00 -05:00 for US exchanges.
This is the default value.
LoadOnSaturday
This integer value defines the behavior of data loading on Saturday:
0 - Load data as for other working days.
1 - Load Friday's data once.
2 - Do not load the data.
The default value is 1.
LoadOnSunday
This integer value defines the behavior of data loading on Sunday and has the same values as LoadOnSaturday.