Database queries can return different formulas for different rows that can be used to configure DDE and RTD (Real-Time Data) formulas.
Customizing DDE formulas using SQL is much easier than customizing using macros.
You may use Microsoft SQL Server Compact or SQLite database located in the workbook directory.
Below is a step-by-step guide.
For example, a workbook has a table with the Symbol and Comment fields. We have to add DDE formulas for the Thinkorswim trading platform (TOS).
Simple steps to create an application:
- Create a database in the workbook directory and create the WatchList table using the Publish Wizard.
- Install SaveToDB Framework into the database using the Application Installer.
- Generate a configuration workbook using the Application Workbooks wizard.
- Add the source WatchList table and a new SQL code (WatchList TOS) with DDE formulas into the QueryList configuration table (see below). Save the table.
- Connect to the WatchList TOS query on a new sheet using the Connection Wizard.
As a result, we have an Excel table with configured DDE formulas.
You may add, delete, and update data (Symbol and Comment). Then click the Save button.
When the add-in saves the table, it refreshes the data and updates formulas for new symbols.
Below is an example of the QueryList table configuration::
, '=TOS|LAST!' + Symbol AS Last
, '=TOS|NET_CHANGE!' + Symbol As NetChange
, '=TOS|PERCENT_CHANGE!' + Symbol As Change
, '=TOS|HIGH!' + Symbol As High
, '=TOS|LOW!' + Symbol As Low
, '=SUBSTITUTE(TOS|VOLUME!' + Symbol+'," ","")+0' As Volume
The table contains the SQL code in the TABLE_SCHEMA column and the CODE type in the TABLE_TYPE field.
The INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields contain the WatchList table as a target table to save changes.
Possible further steps to modify the application:
- You may change WatchList columns using the Publish Wizard. Just republish the table.
- You may change existing queries in the QueryList table in the configuration workbook. Then reload data and configuration in the working workbook.
- You may add new queries to the QueryList table in the configuration workbook and open them in the working workbook using the Connection Wizard or the query list.