Converting Formulas

Converting Formulas

Developers can return Microsoft Excel formulas from views, stored procedures, and SQL queries.

For example:

    , name
    , qty
    , price
    , '=@qty*@price' AS amount

SaveToDB products convert such formulas to platform-specific calculated formulas (Excel, DataTable, or JavaScript).

The SaveToDB add-in supports either all Excel formulas. DBEdit, DBGate, and ODataDB support a subset of formulas.

SaveToDB products detect such formulas by the first-row cell, analyze the column formulas, and apply the formula to the entire column or every cell.

ConverFormulas and DoNotConverFormulas

Developers can turn on or off converting formulas using the ConvertFormulas and DoNotConverFormulas handlers in the xls.handlers table.

For example:


Use ConvertFormulas to force converting formulas even if the first-row cell does not contain a formula like an initial balance in the example.

Use DoNotConvertFormulas to prevent converting formulas if the first-row cell can contain a value like a formula, for example, like in the HANDLER_CODE field.

Formula and FormulaValue

Developers can set formulas to calculate column values using the Formula event type and specifying formulas in the HANDLER_CODE field.

For example:


This case is similar to the one shown in the introduction.

However, the query returns actual data but not the formulas while the formulas are added after loading. So, you can use this method for tables also.

Also, products save all rows when new calculated values are not equal to the loaded values.

This powerful feature allows recalculating column values.

The sample configuration also contains the DoNotChange handler. Use it to disable manual cell changes.

The second option is FormulaValue.

For example:


Contrary to the Formula type, such formulas are calcucated when a user changes any row cell.

You can use Excel formulas or special built-in formulas: =DomainUserName() and =UserName().

Using DDE Formulas

You can use the SaveToDB add-in to create DDE dashboards easily.

For example, here is a sample SQLite configuration for a Thinkorswim watch list using an SQL query in the xls.objects table:

 DDE WatchListCODE<SQL code>WatchListWatchListWatchList

Where the TABLE_CODE field contains the following SQL code:

    , '=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

Note that use can save data to SQLite databases using the free SaveToDB edition.