Blog

Blog

Schematiq Release 4.0

November 20, 2017 | Darren Harris

What's new in Schematiq 4.0

Highlights

Version 4.0 brings a new, cleaner look and feel to the viewer, with Back / Forward browser-style navigation as well as the ability to click on parts of a cell formula to understand a calculation, and to follow links back to the previous cells in the calculation tree.

A comprehensive set of function wizards for tables now allow you to work with tables directly in the viewer and then paste the resulting formula back into the sheet when you're happy with the result.

Newer Excel functions such as MAXIFS, MINIFS are supported - if you don't have the latest version of Excel you can still use these functions in template and workbook functions. Workbook functions are faster than ever and support many more extreme and obscure cases of Excel usage. We also newly support over 100 of Excel's less-used functions, covering the dusty corners of statistics and financial mathematics.

The new table functions tbl.UpdateColumns() allows you to clean up dirty input data in bulk. tbl.FilterColumns() and tbl.SelectRows() allow you more power and flexibility to choose which rows and columns of an input data set to keep.

Full details of all the changes are below:

Improvements to workbook functions

  • SQ-3180 Prevent an error being logged when a name in a formula is not recognised
  • SQ-3315 Add support for workbooks and workbook functions in sq.Save() and sq.Load()
  • SQ-3356 Improvement to fn.Call, to allow input values to be passed as separate arguments
  • SQ-3394 Added support for internally non-standard workbook files
  • SQ-3405 Support for workbook functions with sheet names containing spaces
  • SQ-3407 Fix for rng.GetValues to ensure that array results are returned correctly
  • SQ-3408 Added support for current path within workbook functions
  • SQ-3410 Fix for workbook functions returning a single value when used with tbl.CalculateColumn
  • SQ-3419 Allow Schematiq array formula results to be returned into single cells
  • SQ-3448 Fix for template functions taking ranges as parameters
  • SQ-3461 Fix for nested usages of fn.Call()
  • SQ-3463 Added support for worksheet names that appear to be valid cell references
  • SQ-3488 Fixed a problem parsing sheet names containing single quotes
  • SQ-3497 Added support for workbook functions with multi-cell input ranges 

Improvements in the viewer                     

  • SQ-2924 Add Back / Forward buttons to the viewer
  • SQ-3258 Added username and colour-coded license status to the viewer home page
  • SQ-3414 Fixed an issue with dropdown lists in the viewer when running IE11
  • SQ-3421 Combine the cell label and formula on the same line in the viewer
  • SQ-3422 Remove the breadcrumb trail from the viewer and instead display only the current formula
  • SQ-3459 Added a wizard for the new function tbl.UpdateColumns()
  • SQ-3480 Added a wizard for the new function tbl.FilterColumns()
  • SQ-3481 Added a wizard for the new function tbl.SelectRows()
  • SQ-3581 Improvement to automatic formatting of dates in table columns
  • SQ-3642 Added more useful content to the Viewer home page
  

Improvements to table functions

  • SQ-800  Added support for character encoding auto-detection in tbl.ImportDelimited
  • SQ-3160 Add a new function tbl.UpdateColumns() to update multiple columns in a table.
  • SQ-3161 Add support for a filter expression in tbl.ColumnNames()
  • SQ-3442 Added a new option for tbl.Filter() to return all but the matched rows
  • SQ-3443 Added a new function tbl.SelectRows()
  • SQ-3457 Improvement to the Oracle virtual tables provider
 

Support for many of Excel's less commonly used functions:

                    

  • SQ-2426 Add support for Excel's COVAR, COVARIANCE.P and COVARIANCE.S functions
  • SQ-3295 Add support for Excel's ACOT, ACOTH, COT, COTH, CSC, CSCH, SEC and SECH functions
  • SQ-3365 Add support for Excel's BITAND, BITLSHIFT, BITOR, BITRSHIFT and BITXOR functions
  • SQ-3366 Add support for Excel's ERF, ERF.PRECISE, ERFC, ERFC.PRECISE, GAMMA, GAMMALN, GAMMALN and GAMMALN.PRECISE functions
  • SQ-3367 Added support for Excel's DATEDIF, DAYS, ISOWEEKNUM, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY.INTL and YEARFRAC functions
  • SQ-3368 Add support for Excel's CRITBINOM, GAUSS, NORM.INV, NORM.S.INV, NORMINV, NORMSINV, PHI, POISSON, POISSON.DIST, WEIBULL and WEIBULL.DIST functions
  • SQ-3369 Added support for Excel's VAR, VAR.P, VAR.S, VARA, VARP and VARPA functions
  • SQ-3370 Added support for Excel's CHISQ.TEST, CHISQ, F.TEST, FTEST, T.TEST, TTEST, Z.TEST and ZTEST functions
  • SQ-3372 Added support for Excel's ENCODEURL function
  • SQ-3373 Added support for Excel's PERCENTRANK, PERCENTRANK.EXC, PERCENTRANK.INC, RANK, RANK.AVG, RANK.EQ functions
  • SQ-3379 Added support for Excel's SHEET, SHEETS, TYPE, ISEVEN, ISFORMULA, ISLOGICAL and ISODD functions
  • SQ-3380 Added support for Excel's BASE, CEILING.MATH, COMBINA, DECIMAL, FACTDOUBLE, FLOOR.MATH, PERMUTATIONA and QUOTIENT functions
  • SQ-3381 Added support for Excel's COUPDAYBS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD and COUPDAYS functions
  • SQ-3382 Added support for Excel's EFFECT, FVSCHEDULE, MIRR, PRICE, PRICEDISC, PRICEMAT, RECEIVED, FV, PDURATION, PV, CUMIPMT, CUMPRINC, PPMT, ACCRINT, ACCRINTM, XIRR and XNPV functions
  • SQ-3383 Added support for Excel's AMORDEGRC, AMORLINC, DDB, DB, DISC, DURATION, INTRATE, IPMT, ISPMT, MDURATION, NOMINAL, NPER, RATE, RRI, SYD and VDB functions
  • SQ-3384 Added support for Excel's DOLLARDE and DOLLARFR functions
  • SQ-3385 Added support for Excel's YIELD, YIELDDISC, YIELDMAT, TBILLEQ, TBILLPRICE, TBILLYIELD, ODDFPRICE, ODDFYIELD, ODDLPRICE and ODDLYIELD functions
  • SQ-3386 Added support for Excel's AREAS, FORMULATEXT, CHOOSE, HYPERLINK, COLUMNS, ROWS and TRANSPOSE functions
  • SQ-3388 Added support for Excel's MAXIFS and MINIFS functions

Improvements to existing Excel function support

  • SQ-1982 Fix a bug where MATCH can return the wrong error type
  • SQ-2764 Fix a bug in the DAY function prior to March 1900
  • SQ-3338 Add support for errors as criteria in **IF() and **IFS() functions
  • SQ-3406 Added support for row / column absolute in Excel's ADDRESS function

Performance optimisation

      

  • SQ-2572 Improve performance of error viewer with large input ranges
  • SQ-3392 Simplified handling of vector and range function arguments
  • SQ-3396 Added support for setting ranges with values of different shapes
  • SQ-3474 Performance improvements in workbook function evaluation
  • SQ-3553 Improvement to speed of load and first calc of very large sheets containing data links
  • SQ-3554 Fixed a performance issue where a cell was being re-evaluated earlier than necessary
  • SQ-3607 Improvements to xl.Load()
    

Bug fixes

              

  • SQ-2659 Fix a bug where the viewer occasionally appears in the wrong Excel window
  • SQ-3038 Fix a bug when a link within a table is clicked within a wizard
  • SQ-3083 Fix a bug where an error is reported as being caused by itself
  • SQ-3147 Fix a bug where applying a hint could fail when the target sheet doesn't have focus
  • SQ-3159 Fix for issue whereby a user's licence could be erroneously disabled or corrupted
  • SQ-3179 Fix a bug where a formula containing an unrecognised name is rendered as #NAME?
  • SQ-3361 Fix a bug in package deployment for Asset Manager
  • SQ-3399 Fixed a bug in cross-process serialisation of nested ranges
  • SQ-3403 Fixed an issue with the packaging of the x64 XLL
  • SQ-3415 Fixed a bug in the naming of data-linked JSON objects
  • SQ-3418 Fix the tutorial sheet links
  • SQ-3425 Fixed a bug when clicking through from a database view to its tables
  • SQ-3428 Fixed a bug where drilling into a formula could result in an invalid data link
  • SQ-3429 Fixed a bug where Excel's undo history could be lost as a result of recalculation
  • SQ-3430 Fix for bug where a nested error would display a generic message instead of the root cause
  • SQ-3434 Fixed a bug in the viewer, where error input ranges were not being displayed correctly
  • SQ-3436 Fixed a bug where the viewer selected cell could change unexpectedly
  • SQ-3441 Fixed a bug in the viewer where an icon was still visible while pasting a formula
  • SQ-3451 Fixed a bug when selecting the last possible column in a worksheet
  • SQ-3453 Fixed a bug where the tbl.SelectColumns() wizard is used with very long column names
  • SQ-3464 Added additional error logging to event handlers in the XLL
  • SQ-3467 Fixed a bug when using template and workbook functions in tbl.Filter()
  • SQ-3468 Fixed a bug where only part of an array formula is used in a workbook function
  • SQ-3469 Fixed a bug in the viewer when the selected cell changes rapidly
  • SQ-3570 Fix a bug in the click-through behaviour of a data source in the viewer
  • SQ-3577 Fixed a bug in Paste Formula, when more than one workbook is open
  • SQ-3595 Fix for bug in the Viewer's handling of HTTPS requests