Dbank

Time Series Data Management
For Windows Desktops and Servers.

Using Microsoft Excel

Automating Excel Spreadsheets

Reading Dbank

Updating Dbank

Univariate Statistical Functions

Statistical Array-Formulae

 

 

 

 

Updating Dbank Time-Series

The following table lists all the Dbank add-in functions that can be used to create and update a time-series directly from Excel. The main functions for writing time-series are tswSave() and tswSaveRange(). Both allows users to write Excel ranges containing data and time-series attributes directly to a Dbank database. The remaining functions allow users to modify the attributes of a time-series, including, for example, its title, long-name, data source, and conversion method.

 

Dbank XLA Function

Action

tswConvertName ( SeriesName, ConvertName )

Changes series’ conversion method, e.g. "Average".

tswCopy ( SeriesName, Destination, [OverWriteIfNecessary=True])

Copies time-series.

tswCost ( SeriesName, Cost )

Changes series' update cost (for update over the internet using remote access).

tswDataSource ( SeriesName, DataSource )

Changes series’ data source.

tswDecimals ( SeriesName, Decimals )

Changes series’ decimal count.

tswDelete ( SeriesName )

Deletes the series.

tswFiscalLead (SeriesName)

Changes the time-series' fiscal lead value.  Useful for creating automatic fiscal offsets.

tswGroupLabel  ( GroupName, Label )

Changes label of a group.

tswGroupMemo (GroupName, Memo )

Changes memo of a group.

tswInterpolationMethod (SeriesName, InterpolationMethod)

Changes a series' interpolation method.

tswInternetAddress ( SeriesName, Address )

Changes address of ASCII file to update series.

tswCreateLink  ( SeriesName,  AliasName )

Creates an alias variable or link-variable for the named series.  An alias variable is simply a pointer to an existing time-series or another link variable.  Time-series may be referenced directly using their link names (of which there can be many).

tswLongName ( SeriesName, LongName )

Changes series’ long-name.

tswMakeString ( SeriesName, MakeString, [RefreshOnRead=True], [IgnoreMissingValues=False], [IgnoreMissingSeries=False] )

Changes series' make string field.  The optional "RefreshOnRead" parameter controls whether Dbank refreshes the equation prior to returning the result. "IgnoreMissingValues" controls whether Series Make ignores missing values when it calculates the make expression.  

tswMemo ( SeriesName, Memo )

Changes series' memo field.

tswObsCount ( SeriesName, NewCount )

Changes the number of observations in a series.

tswRename ( SeriesName, NewName )

Renames a series.

tswSignificantDigits ( SeriesName )

Changes series’ significant digits setting.

tswStartDate ( SeriesName, StartDate)

Changes series start date.

tswTitle ( SeriesName, Title )

Changes series’ title.

tswUnits ( SeriesName, Units )

Changes series’ units of measurement.

tswSave ( SeriesName, Dates as Excel.Range, DataValues as Excel.Range, Frequency, [Title][ConversionMethod], [MakeString], [MergeIntoExistingSeries as Boolean=False],[InterpolationMethod], [FiscalLead=0])

Converts an Excel range of values  (and associated dates) to time-series format and then saves it in a Dbank database (which can be either an Access MDB file or a SQL database).  This function is designed to save one series at a time. It can also "merge" data into an existing Dbank series; in this mode, tswSave() updates a series without (as far as possible) destroying any existing data.

tswUpdate(Range, [DefaultDataBank][DefaultGroup], [ConversionMethod], [InterpolationMethod])

Parses an Excel range of numbers (and associated dates) into time-series format and then saves them to an existing time series within a Dbank database (if possible).   The first column of the Excel range should be a sequence of Dbank dates, declared with "Date" or "Date/Refresh" in the (1,1) cell position. The first row, from columns 2 to N, should contain the name of the time-series in Dbank format, i.e, [group]series_name.  The series will be called "c1...cN", where N indicated the column number, if Dbank does not detect any series names in the first row.  Non-numeric cell entries, including empty cells, are interpreted as missing values. The default databank and group can be controlled by specifying a fully-qualified series name in the first cell of each column in the active range.  It "pays" to provide this function with dates, as it uses them to infer the frequency of the series.

Example Spreadsheet (Dbank required)

Note that this function behaves exactly like tswSaveRange when the target time-series does not exist.

tswSaveRange ( Range, [DefaultDataBank][DefaultGroup], [ConversionMethod], [InterpolationMethod], [UpdateExistingSeriesIfPossible=False])

Parses an Excel range of numbers (and associated dates) into time-series format and then saves them to a Dbank database. The first column of the Excel range should be a sequence of Dbank dates, declared with "Date" or "Date/Refresh" in the (1,1) cell position. The first row, from columns 2 to N, should contain the name of the time-series in Dbank format, i.e, [group]series_name.  The series will be called "c1...cN", where N indicated the column number, if Dbank does not detect any series names in the first row.  Non-numeric cell entries, including empty cells, are interpreted as missing values. The default databank and group can be controlled by specifying a fully-qualified series name in the first cell of each column in the active range.  It "pays" to provide this function with dates, as it uses them to infer the frequency of the series.

Example Spreadsheet (Dbank required)

Note that, by default, this function will delete all the contents of an existing time-series in a database (including its footnotes).  If you wish to update an existing time-series, set the UpdateExistingSeriesIfPossible flag to "TRUE" before invoking the function.  Doing so will preserve the attributes of an existing series provided the updating series has the same frequency.

| Copyright © 1994-2011 | TSDM Pte Ltd | Time Series Data Management for Windows | Contact TSDM |