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





Reading Dbank Time-Series

Example Spreadsheet (Dbank required)

The following table documents the "time-series" function calls that can be made after you activate Dbank’s add-in (a one-time requirement). These functions typically retrieve a single attribute of the time-series (e.g., time-series data point, title, conversion method) to a worksheet cell:

Dbank's Time-Series Retrieval Functions

Dbank XLA Function


tsConvertName ( SeriesName )

Series’ conversion method, e.g. "Average", "First", "Last!"

tsCost ( SeriesName )

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

tsCreated ( SeriesName )

Series’ creation date and time.

tsDate ( SeriesName, Offset )

Returns a date, reflecting the actual date associated with a particular observation.

tsDataBank ( SeriesName )

Series’ fully-qualified data bank name.

tsDataSource ( SeriesName )

Series’ data source.

tsDecimals ( SeriesName )

Series’ decimal count (for display purposes).

tsFiscalLead (SeriesName)

Series fiscal lead (for managing fiscal dates).  See also tsFiscalVal()

tsFiscalVal (SeriesName, CalendarDate)

Returns the value of a time-series at a particular date. In this case, the time-series dates are adjusted by the time-series' "fiscal lead" setting before returning the time-series value. 

tsFootNote ( SeriesName, DateString, FootNoteName ) 

Footnote entry for the series at the indicated date.  You must specify the name of the footnote to retrieve.

tsFootNoteCount ( SeriesName, DateString ) 

The number of footnotes available for the series at the indicated date.

tsFootNoteName ( SeriesName, DateString, N )

Name of Nth  footnote  for the series at the indicated date.

tsFrequency ( SeriesName )

Series’ frequency, e.g., "Annual"

tsGet ( "SeriesName1, tf , cm {| SeriesName2, tf, cm | ... |SeriesNameN, tf, cm"} )

Retrieves multiple series in a single call using Excel's "array formulae" protocol. Note that the raw series stored in the Dbank database need not be of the same frequency.  You can control the frequency of the series using the function's "tf" and "cm" arguments.  The "tf" argument represents the target frequency, and "cm" is the conversion method to use when converting to the target frequency (this will be applied to all the series in the argument list).  The time-series' native conversion method will be used if "cm" is not specified. You need only specify a single series name to use tsGet(), e.g.,

tsGet ($A$1 & "a,first!")

will convert the time-series named in cell $A$1 to annual frequency, using "First!" (ignore missing) as the conversion method. While tsGet() can be used to retrieve a single time-series at a given frequency, it is most useful for aligning series that have different start dates and frequencies. 

tsGroup ( SeriesName )

Dbank group that the series belongs to.

tsGroupCount  ( ParentGroup )

Number of sub-groups in a group.

tsGroupLabel  ( GroupName )

Label of a group.

tsGroupName ( ParentGroup, N )

Nth  sub-group in the specified parent group. Can be used together with tsGroupCount( ) to determine all the sub-groups in the named parent group.  If you omit the group number, tsGroupName() returns all the sub-groups in the parent group.  However, you need to use Excel's array formulae protocol to call the function in this (see above).

tsInterpolationMethod (SeriesName)

Series' default interpolation method.

tsInternetAddress ( SeriesName )

Address of ASCII file to be retrieved to update series via FTP.  The ASCII file needs to be in Dbank's FREE format.

tsLastDate ( SeriesName )

Last available date in the series (even if value is missing).

tsLinkCount ( SeriesName ) 

Returns the number of link variables associated with the series.

tsLinkName (SeriesName, N)

Returns the name of the Nth link or alias variable associated with the series. This function can be used together with tsLinkCount( ) to enumerate all the link variables associated with the series.

tsLongName ( SeriesName )

Series’ long-name.

tsMakeString ( SeriesName )

Series' Make string field.

tsMemo ( SeriesName )

Series' memo field.

tsName ( SeriesName )

Series’ short-name.

tsRevised ( SeriesName )

Series' revision date and time.

tsSeriesCount  ( GroupName )

Number of time-series in the named group.

tsSeriesNames ( GroupName, N )

Nth series in the named group. Can be used together with tsSeriesCount( ) to enumerate all the series in the indicated group.  If you omit the series number, tsSeriesNames() returns all the series in the named group.  However, you need to use Excel's array formulae protocol to call the function in this (see above).

tsSignificantDigits ( SeriesName )

Series’ significant digits setting.

tsStartDate ( SeriesName )

First date in the series ( or tsDate (SeriesName,1) ).

tsTitle ( SeriesName )

Series’ title.

tsUnits ( SeriesName )

Series’ units of measurement.

tsVal ( SeriesName, DateString )

Value of the series at the indicated date, and any associated footnote entries, which will appear as an Excel cell-note.  

tsVal( ) can perform automatic time series conversion. The last  character of the date, if non-numeric, indicates the desired frequency to work with, irrespective of the actual frequency of the series.  The desired frequency can be derived only if a conversion method has been saved with the series.

The most important of these functions is tsVal ( SeriesName, Date ), which allows an Excel user to dynamically link any cell in an Excel worksheet to any value of a time-series saved in a Dbank database. For example, for the Dbank database "example.mdb" that resides in the sub-directory "c:\program files\dbank32" placing the following function call in a particular cell
=tsVal("c:\program files\dbank32\example[first]bpimr","1985:1")
will result in the value -114544 (the actual value of the quarterly series) being placed in that cell.

You can also retrieve the annual version of the series at 1985 by the call:
=tsVal("c:\program files\dbank32\example[first]bpimr","1985A"),
which will place -118228 (the average of -114544 [1985:1], 118618 [1985:2], 118036 [1985:3], and -121714 [1985:4]) in the cell. Notice that the last letter in the date argument determines the target frequency (which can be any valid frequency that Dbank recognizes). In other words, tsVal( ) performs automatic "behind the scenes" conversion of the (in this case, a quarterly) series to any target frequency supported by Dbank.

"Dynamically linked", or "automated" spreadsheets as they are often called, provide the tools needed to prepare sophisticated management reports that use all the power of Microsoft Excel for formatting time series information, including those that are targeted for the Internet using an XML aware browser. Since dynamic spreadsheets automatically re-read data stored in the Dbank database whenever the spreadsheet recalculates, the embedded tables will reflect the impact of revisions after a full recalculation of the worksheet has occurred.

Since each Dbank database can be up to two gigabytes in size (and virtually unlimited in size if you use Dbank's SQL engine), dynamically-linked spreadsheets also provide a means of sharing very large time series databases online with Excel users. Excel's memory usage is keep to a minimum because only those observations required to recalculate the spreadsheet are actually “pulled” into the spreadsheet by Dbank’s add-in.

Lastly, when combined with Excel 2000's ability to save a spreadsheet in XML format, automated spreadsheets can be published directly to the Internet without any duplicated effort or loss of formatting information.

The add-in's tsVal() function is designed to operate on a single observation or value. If you would rather work with a set of time-series values, the add-in's tsGet ( SeriesName1, ..., SeriesNameN ) function can be used instead to retrieve a matrix of time-series (i.e., sets of time-series, appropriately aligned). In order to do so, users need to first highlight a target range for the time-series matrix within a spreadsheet, then enter a call to tsGet( ) in the upper left-most cell (press F2 after highlighting the range) of this range, and then use Excel's "array-formulae" protocol to retrieve the entire matrix into the selected range.

Array formulas are created in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. The critical requirement here is that the target range is large enough to accept the time-series, which the user is responsible for selecting before entering the function call. Following the example of tsVal(), tsGet() can also convert a time-series to another frequency by specifying a target frequency within the function arguments (see below for a detailed explanation).

Dbank's add-in also contains functions to enumerate all the sub-groups that exist in a parent group, and all the time-series within a group. These include tsGroupCount(), which returns the number of sub-groups within a group; and tsGroupName(), which enables you to retrieve the names of these sub-groups.

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