RTD Database

RTD Database

Overview

The RTD database is a ready-to-use solution for getting real-time data from preconfigured data providers using RealTimeToDB.

You may use the database as is, customize it, or use the source code to create required tables in your databases.

Preconfigured real-time data providers:

Supported database servers:

  • Microsoft SQL Server.
  • Microsoft SQL Server Compact.
  • SkySQL MariaDB.
  • Oracle MySQL.
  • Oracle Database.
  • IBM DB2.
  • NuoDB.

You may find the installation code and instructions in the RTD Database folder of the setup package.

The RTD database also contains preconfigured views for getting real-time data from database tables into Microsoft Excel using companion products RealTimeToExcel and the SaveToDB add-in for Microsoft Excel.

The SaveToDB add-in can also be used for editing configuration tables in Microsoft Excel. So you do not need any installed database client to manage and use the database.

Preinstalled Local Database

The local RTD database for Microsoft SQL Server Compact is installed by default into the %LOCALAPPDATA%\Gartle\RealTimeToDB folder.

You may start loading real-time data into your database and using the real-time data in Microsoft Excel in a couple of minutes.

The source copy of the rtd.sdf database is also located in Resources subdirectory. You may use this copy to restore the database to the initial state.

Installing and Uninstalling

The RTD Database folder of the setup package contains the subfolders with the source code and instructions for every supported database server.

Installation is an easy process: edit the connection properties and run the setup batch file.

The database must have two users:

  • A user to update database tables using RealTimeToDB. The default name is rtd.
  • A test user to get the data from the database into Microsoft Excel using RealTimeToExcel. The default name is rtdxls.

These names are used in preconfigured Excel workbooks and configuration files.

Configuration Tables

SQL Server and SQL Server CompactMySQL and MariaDBOracle Database, IBM DB2, and NuoDB
RealTimeTablesreal_time_tablesREAL_TIME_TABLES
RealTimeFormulasreal_time_formulasREAL_TIME_FORMULAS

The RealTimeTables table contains table configurations in line with RealTimeToDB requirements.

Change the IS_DISABLED field to manage updating data tables.

Change the LOAD_CODE field to manage tickers to update.

The RealTimeFormulas table configures Excel-like formulas used to get data from RTD and DDE servers.

See formulas in data table descriptions.

You may use the following Excel workbooks included in the setup package to manage tables using Microsoft Excel with the SaveToDB add-in installed:

  • rtd-mssql.xlsx
  • rtd-sqlce.xlsx
  • rtd-mysql.xlsx
  • rtd-ora.xlsx
  • rtd-db2.xlsx
  • rtd-nuodb.xlsx

Data Tables

SQL Server and SQL Server CompactMySQL and MariaDBOracle Database, IBM DB2, and NuoDB
FundamentalsYahoofundamentals_day_history_yahooFUNDAMENTALS_DAY_HISTORY_YAHOO
FundamentalsDayHistoryYahoofundamentals_yahooFUNDAMENTALS_YAHOO
StocksYahoostocks_yahooSTOCKS_YAHOO
QuotesYahooquotes_yahooQUOTES_YAHOO
QuoteDayHistoryYahooquote_day_history_yahooQUOTE_DAY_HISTORY_YAHOO
QuoteTimeHistoryYahooquote_time_history_yahooQUOTE_TIME_HISTORY_YAHOO
QuoteTickHistoryYahooquote_tick_history_yahooQUOTE_TICK_HISTORY_YAHOO
QuotesMsnMoneyquotes_msnmoneyQUOTES_MSNMONEY
QuoteDayHistoryMsnMoneyquote_day_history_msnmoneyQUOTE_DAY_HISTORY_MSNMONEY
QuoteTimeHistoryMsnMoneyquote_time_history_msnmoneyQUOTE_TIME_HISTORY_MSNMONEY
QuoteTickHistoryMsnMoneyquote_tick_history_msnmoneyQUOTE_TICK_HISTORY_MSNMONEY
OptionsYahoooptions_yahooOPTIONS_YAHOO
OptionDayHistoryYahoooption_day_history_yahooOPTION_DAY_HISTORY_YAHOO
OptionTickHistoryYahoooption_tick_history_yahooOPTION_TICK_HISTORY_YAHOO
OptionsGoogleoptions_googleOPTIONS_GOOGLE
OptionDayHistoryGoogleoption_day_history_googleOPTION_DAY_HISTORY_GOOGLE
OptionTickHistoryGoogleoption_tick_history_googleOPTION_TICK_HISTORY_GOOGLE
OptionsMsnMoneyoptions_msnmoneyOPTIONS_MSNMONEY
OptionDayHistoryMsnMoneyoption_day_history_msnmoneyOPTION_DAY_HISTORY_MSNMONEY
OptionTickHistoryMsnMoneyoption_tick_history_msnmoneyOPTION_TICK_HISTORY_MSNMONEY
QuotesESquotes_esQUOTES_ES
QuoteDayHistoryESquote_day_history_esQUOTE_DAY_HISTORY_ES
QuoteTimeHistoryESquote_time_history_esQUOTE_TIME_HISTORY_ES
QuoteTickHistoryESquote_tick_history_esQUOTE_TICK_HISTORY_ES
QuotesTOSquotes_tosQUOTES_TOS
QuoteDayHistoryTOSquote_day_history_tosQUOTE_DAY_HISTORY_TOS
QuoteTickHistoryTOSquote_tick_history_tosQUOTE_TICK_HISTORY_TOS
OptionsTOSoptions_tosOPTIONS_TOS
OptionDayHistoryTOSoption_day_history_tosOPTION_DAY_HISTORY_TOS
OptionTickHistoryTOSoption_tick_history_tosOPTION_TICK_HISTORY_TOS
QuotesTWSquotes_twsQUOTES_TWS
QuoteDayHistoryTWSquote_day_history_twsQUOTE_DAY_HISTORY_TWS
QuoteTickHistoryTWSquote_tick_history_twsQUOTE_TICK_HISTORY_TWS
OptionsTWSoptions_twsOPTIONS_TWS
OptionDayHistoryTWSoption_day_history_twsOPTION_DAY_HISTORY_TWS
OptionTickHistoryTWSoption_tick_history_twsOPTION_TICK_HISTORY_TWS
QuotesVFXquotes_vfxQUOTES_VFX
QuoteDayHistoryVFXquote_day_history_vfxQUOTE_DAY_HISTORY_VFX
QuoteTimeHistoryVFXquote_time_history_vfxQUOTE_TIME_HISTORY_VFX
QuoteTickHistoryVFXquote_tick_history_vfxQUOTE_TICK_HISTORY_VFX
CurrenciesYahoocurrencies_yahooCURRENCIES_YAHOO
CurrenciesDayHistoryYahoocurrencies_day_history_yahooCURRENCIES_DAY_HISTORY_YAHOO
CurrenciesTimeHistoryYahoocurrencies_time_history_yahooCURRENCIES_TIME_HISTORY_YAHOO
CurrenciesTickHistoryYahoocurrencies_tick_history_yahooCURRENCIES_TICK_HISTORY_YAHOO
CurrenciesMsnMoneycurrencies_msnmoneyCURRENCIES_MSNMONEY
CurrenciesDayHistoryMsnMoneycurrencies_day_history_msnmoneyCURRENCIES_DAY_HISTORY_MSNMONEY
CurrenciesTimeHistoryMsnMoneycurrencies_time_history_msnmoneyCURRENCIES_TIME_HISTORY_MSNMONEY
CurrenciesTickHistoryMsnMoneycurrencies_tick_history_msnmoneyCURRENCIES_TICK_HISTORY_MSNMONEY

Data tables can be viewed from several perspectives.

Tables by data providers:

Tables by stored data:

  • Fundamentals (fundamental data from Yahoo! Finance).
  • Stocks (static data from Yahoo! Finance).
  • Quotes (stocks, futures, and currency pairs).
  • Options.

Tables by time frame:

  • Last values.
  • Day history.
  • Time history.
  • Tick history.

Last value tables have one column primary keys, Symbol or Code.

Day history tables have two column primary keys, Symbol or Code and Date.

Time history tables have two column primary keys, Symbol or Code and DateTime.

You may change the time frame for such tables: 1 second (default), 1 minute, 5, 10, 15, 20, 30, and 60 minutes.
To change the time frame, modify used functions in the column formulas.

The time interval depends on a real-time data provider also.

Tick history tables contain all records from the real-time data provider. RealTimeToDB uses only INSERT statements for such tables.

The tick history tables must have an auto-increment primary key column.

You may click on any table above to go to the table description.

Task Tables

SQL Server and SQL Server CompactMySQL and MariaDBOracle Database, IBM DB2, and NuoDB
QuoteListStocksquote_list_stocksQUOTE_LIST_STOCKS
QuoteListYahoooption_list_yahooOPTION_LIST_YAHOO
QuoteListMsnMoneyoption_list_msnmoneyOPTION_LIST_MSNMONEY
QuoteListESquote_list_esQUOTE_LIST_ES
QuoteListTOSquote_list_tosQUOTE_LIST_TOS
QuoteListTWSquote_list_twsQUOTE_LIST_TWS
QuoteListVFXquote_list_vfxQUOTE_LIST_VFX
OptionListTOSoption_list_tosOPTION_LIST_TOS
OptionListYahoooption_list_yahooOPTION_LIST_YAHOO
OptionListGoogleoption_list_googleOPTION_LIST_GOOGLE
OptionListMsnMoneyoption_list_msnmoneyOPTION_LIST_MSNMONEY
CurrenciesListYahoocurrencies_list_yahooCURRENCIES_LIST_YAHOO
CurrenciesListMsnMoneycurrencies_list_msnmoneyCURRENCIES_LIST_MSNMONEY

Task tables contain tickers to update.

It is the simplest solution. You may delete all tickers and insert required ones. You may use Microsoft Excel workbooks like rtd-sqlce.xlsx or SQL statements to update task tables.

Another solution to specify tickers to update is to change the LOAD_CODE in the RealTimeTables table.

You may use SELECT UNION to combine tickers from several tables that require real-time data.

Don't forget to reload the configuration in RealTimeToDB after changes.

Views for Microsoft Excel for Refreshing Data in Real-Time

You may load database data into Microsoft Excel using native Excel features.

You may configure data auto-refresh to reload all table data.

However, you have a better way. You may use RealTimeToExcel, a companion product, to refresh the data using the Excel RTD functions with the lowest overhead.

The views shown below contain ready Excel formulas to get data from database tables in real-time.

SQL Server and SQL Server CompactMySQL and MariaDBOracle Database, IBM DB2, and NuoDB
FundamentalsDayHistoryYahoofundamentals_day_history_yahooFUNDAMENTALS_DAY_HISTORY_YAHOO
FundamentalsYahoofundamentals_yahooFUNDAMENTALS_YAHOO
OptionDayHistoryTOSoption_day_history_tosOPTION_DAY_HISTORY_TOS
OptionDayHistoryTWSoption_day_history_twsOPTION_DAY_HISTORY_TWS
OptionDayHistoryYahoooption_day_history_yahooOPTION_DAY_HISTORY_YAHOO
OptionDayHistoryGoogleoption_day_history_googleOPTION_DAY_HISTORY_GOOGLE
OptionDayHistoryMsnMoneyoption_day_history_msnmoneyOPTION_DAY_HISTORY_MSNMONEY
OptionsTOSoptions_tosOPTIONS_TOS
OptionsTWSoptions_twsOPTIONS_TWS
OptionsYahoooptions_yahooOPTIONS_YAHOO
OptionsGoogleoptions_googleOPTIONS_GOOGLE
OptionsMsnMoneyoptions_msnmoneyOPTIONS_MSNMONEY
QuoteDayHistoryESquote_day_history_esQUOTE_DAY_HISTORY_ES
QuoteDayHistoryTOSquote_day_history_tosQUOTE_DAY_HISTORY_TOS
QuoteDayHistoryTWSquote_day_history_twsQUOTE_DAY_HISTORY_TWS
QuoteDayHistoryVFXquote_day_history_vfxQUOTE_DAY_HISTORY_VFX
QuoteDayHistoryYahooquote_day_history_yahooQUOTE_DAY_HISTORY_YAHOO
QuoteDayHistoryMsnMoneyquote_day_history_msnmoneyQUOTE_DAY_HISTORY_MSNMONEY
QuotesESquotes_esQUOTES_ES
QuotesTOSquotes_tosQUOTES_TOS
QuotesTWSquotes_twsQUOTES_TWS
QuotesVFXquotes_vfxQUOTES_VFX
QuotesYahooquotes_yahooQUOTES_YAHOO
QuotesMsnMoneyquotes_msnmoneyQUOTES_MSNMONEY
StocksYahoostocks_yahooSTOCKS_YAHOO
CurrenciesDayHistoryYahoocurrencies_day_history_yahooQUOTE_DAY_HISTORY_YAHOO
CurrenciesDayHistoryMsnMoneycurrencies_day_history_msnmoneyQUOTE_DAY_HISTORY_MSNMONEY
CurrenciesYahoocurrencies_yahooQUOTES_YAHOO
CurrenciesMsnMoneycurrencies_msnmoneyQUOTES_MSNMONEY

Also, the SaveToDB add-in converts such text database formulas into real Excel ones. So, you easily connect to any table and get the real-time data immediately.

You may use preconfigured Excel workbooks from the setup package to get the data from views:

  • rtdxls-mssql.xlsx
  • rtdxls-sqlce.xlsx
  • rtdxls-mysql.xlsx
  • rtdxls-ora.xlsx
  • rtdxls-db2.xlsx
  • rtdxls-nuodb.xlsx

See Real-Time Views for Microsoft Excel for details.