Australian Bureau of Statistics

Rate the ABS website
ABS Home
ABS @ Facebook ABS @ Twitter ABS RSS ABS Email notification service
Help - Time Series Spreadsheet User Guide
 

How do I find historical (time series) data?
Is it better to 'Save' or to 'Open' an ABS Time Series Spreadsheet?
What purpose does the 'Index' worksheet serve?
Why are the 'Data' worksheets formatted this way?
What is time series metadata?
What is a 'Named Range'?
How do I find the time series I am looking for in an ABS Time Series Spreadsheet?
How can I make finding a time series easier?
Can I bookmark an ABS Time Series Spreadsheet?
What is the best way to reference (link to) ABS Time Series Spreadsheet data?
What is the best way to graph/chart ABS Time Series Spreadsheet data?


Locating ABS Time Series Spreadsheets

The ABS Web Site is structured so all components of a given statistical release are found grouped together by Product (or catalogue) number. A statistical release contains a number of web pages, eg, Main Features and Explanatory Notes, and, associated file attachments that can be downloaded to a clients computer. These file attachments may include a print-ready Publication in pdf format, Data Cubes in SuperTable or Excel format and ABS Time Series Spreadsheets.

Many Statistical Products include the full range of available file attachments however this is not always the case. Not all products have Time Series spreadsheets associated with them and there is no single point of reference on the ABS Web Site that lists all available Time Series Spreadsheets available for download.

To determine whether Time Series Spreadsheets are available for the subject you are interested in, you must first identify the 'parent' statistical product required. Information on how to identify and locate products you are looking for is found in the 'Navigate to Statistics' Help page on the ABS Web Site. Other web pages that help you in locating the subject matter you are interested in include:


Once you have identified and located the appropriate Statistical Product, click on the 'Details' tab to see if ABS Time Series Spreadsheets are available.

Where ABS Time Series Spreadsheets are available, they are provided in both Excel and compressed Excel (zipped) formats. Zip files are provided to allow for faster download; some ABS Time Series files can be quite large in uncompressed format (> 10 Mb). Although the uncompressed Excel files are substantially larger, they will open automatically within most web browsers on computers that have Microsoft Excel (or Excel Viewer) installed, thereby requiring fewer 'clicks' to get to the information you are seeking.
Back to top

Save or Open?

ABS Time Series Spreadsheets are available from the ABS Web Site. They are accessed via a 'Download Now' link on the 'Details' page of their respective products.

Some ABS Time Series Spreadsheet can be quite large and, accordingly, they are also provided in a Zipped (Excel) format to facilitate faster download. Clicking a 'Download Now' link will result in one of several possible behaviours depending on whether the 'Zip' or 'Excel' option is used, the type of browser being used, the browser options selected, and whether the user has appropriate software available to view or open Excel format files.

Most browsers' default behaviour will automatically open an ABS Time Series Spreadsheet in a browser window when the 'Download Now' link is clicked providing that appropriate 'viewer' software is installed (eg, Microsoft Excel, Excel Viewer, Lotus 123 etc), and, the Excel file type is recognised and registered with the browser application being used.

However, another common behaviour is that the user will be prompted to 'Open' or 'Save' the file. If the user chooses to open the file, it could open either in a browser window, or open in the 'native' application associated with it.

If the 'Zip' option is chosen, the user will be prompted to 'Save' or 'Open' the file. A Zip file must be opened before the ABS Time Series Spreadsheet within can be accessed. Once the Zip file is open, the ABS Time Series Spreadsheet can then be opened directly, or, 'extracted' to a permanent storage device (hard drive etc).

Opening in a Browser Window vs Saving to Disk

When an ABS Time Series Spreadsheet is saved to disk first, and then opened, it will launch within its associated application window, independent of the browser that was used to download it. All features of the file and those of the particular application being used, can be accessed when an ABS Time Series Spreadsheet is opened in 'standalone' mode.

All of the same features are available when the file is opened from within the browser window. There is one significant drawback that appears in some browser set-ups when navigating around the Workbook in a browser window. Each time a link in the ABS Time Series Spreadsheet is clicked (or browser Forward & Back buttons are used), the page is reloaded in the browser window. Depending on browser and/or support infrastructure settings, this may result in the ABS Time Series Spreadsheet actually being downloaded 'many' times while navigating around it.

For more information, please refer to 'Help: Using Time Series Spreadsheets'.
Back to top

The Index Sheet

Metadata is information about data, it appears on the Index sheet of ABS Time Series Spreadsheets. You can use metadata to easily locate the series data you require. Metadata is also repeated on the Data sheet/s where it forms part of a number of data matrices that are conducive to automation, integration and export.

On opening an Time Series File, the user is presented with the File's Index page. The Index page is designed to provide an intuitive, user-friendly interface to the File's underlying data.

Index Sheet Layout

The Index sheet has a consistent, fixed format across all 'publications' as follows:

Rows 1 to 11 are dedicated to 'file header' information and include hyperlinks to related information on the ABS website. All labels, hyperlinks, the ABS logo and column headings are set in dedicated fields and WILL NOT change due to, for example, the length of a table title being extended.

Cell A12 contains an Excel 'freeze pane' anchor; this allows the user to scroll down through the series while leaving all of the header information visible.

Series descriptions (labels) are presented in alphabetical order with the 'components' of the label also being ordered in a consistent manner. This allows the human eye to scroll down through the various 'blocks' of like descriptions that naturally group together which helps the user to easily and quickly locate the required data series.

Series Hyperlinks


Column E on the Index sheet contains a unique Series Identifier for each data series contained within the File. The Series Identifiers have little relevance in the context of manual (human) navigation; their primary purpose is to facilitate programmatic access to the Series' data. However, the Series Identifiers are also hyperlinks to the underlying data series found in the Data sheet/s. On clicking a Series hyperlink, a 'named range' containing the entire Series and its metadata is automatically selected ready for further manipulation, copy/paste, charting etc.

The Series' ranges are given the 'name' of the corresponding Series Identifier. All Named Ranges are also accessible via the Excel menu item "Edit/Go to" or via the Excel shortcut keys <ALT + G> or <F5>. For more information on ABS Time Series Spreadsheets standard 'Named Ranges' please refer to the
Named Range section below.

Annotations


There may be additional information available to support ABS time series data. This extra information could apply to individual observations, series', a time period, or all series in an ABS Time Series Spreadsheet. These additional data are referred to as Annotations.


Excel 'Cell Comments' (or Cell Notes) have been adopted in ABS Time Series Spreadsheet to provide all relevant annotations (including those formerly provided as footnotes). This means that all annotations can be delivered to the user directly in the relevant cell concerned, and in the case of annotations relating to a particular observation in a series, without interfering with the data itself.

The presence of an annotation (Cell Comment) is indicated by a small red triangle appearing in the top right corner of the cell concerned. When the mouse pointer is hovered over the cell containing a Comment Indicator, the additional information is displayed in a small pop-up.
Back to top

The Data Sheet/s

The "Data" sheets have been designed specifically to facilitate integration of spreadsheets into client desktop and larger systems, particularly Microsoft Office based applications. Data sheets are designed to support the automation and export of the series data and metadata contained within; users should use the 'Index Sheet' for manual navigation (click the Series ID link).

The Data sheet (or sheets) is located 'behind' the Index sheet. All series data and its metadata are located within the Data sheet (or sheets) and are laid out in a consistent, fixed, flat structure conducive to interrogation and manipulation.

The Data sheet has deliberately NOT been designed to be navigated by the human eye; the data series' presented here will not necessarily appear in any particular order or grouping. Columns are kept to a manageable, fixed width which hides much of the Series Description.

Why? There is no need to - each and every series within has its own unique Series Identifier, and, a corresponding 'named range' containing all of the series' data and metadata. The data series should be accessed via the Index sheet hyperlinks when performing 'manual' operations or interrogations.




Back to top

Time Series Metadata

Metadata is 'data about data'. Each time series in an ABS Time Series Spreadsheet has 10 key metadata items associated with it. These metadata items are:

Series Description
Series Type
Series ID
Series Start
Series End
No. Obs
Unit
Data Type
Frequency
Collection Month

TSS metadata items are consistent in both layout and format across all ABS Time Series Spreadsheet available on the ABS web site. The metadata not only describe each series in a consistent manner, but also facilitate navigation and interrogation of the data.

Series Description

Series' descriptions are comprised of one or more Classifications & Parameter Items that have been concatenated to form a meaningful, plain English descriptor for the series. For example, a Labour Force time series may contain three or four Classifications relating to the group of data being displayed; eg, Sex (male or female), State (NSW, VIC etc), Status (employed, unemployed) etc.

Series Descriptions do not uniquely identify a Time Series, and must be used in conjunction with other Series metadata, including the title of the Time Series Spreadsheet, to differentiate between similar data items.

Series' descriptions are not necessarily unique either across or within ABS Time Series outputs.

Series Type

ABS Time Series data are commonly presented in one or more of three distinct Series Types; Original, Seasonally Adjusted or Trend.

Series ID (Series Identifiers)

Each data series presented in an Time Series File has its own unique Series Identifier. This Series ID can be used as a unique and stable "key" for selecting and describing a time series. Two different time series will never share the same ID. In rare cases, the description of a particular time series in a Time Series Workbook may be updated (eg to make it clearer) but if it is conceptually the same series (ie the same data as previously released) then the Series ID will not change in such cases. If a Series ID does change in a Time Series Workbook then it means the series previously released has been replaced by a new series and the data may not be directly comparable.

These properties mean that Series IDs can be a very good way of locating data within a Workbook (or other time series output) either systematically (eg using a "macro") or manually (eg using a "go to" process). They are easier, shorter and more reliable to use than descriptions in this sense. They are also more reliable than "absolute" references (eg "look for the value in eleventh row of the sixth column") because the latter can be invalidated if extra information (eg more time series) are added to a Workbook in future.

Should you have questions about data within the Time Series Workbook, quoting the Series ID will help the ABS know exactly which time series you are seeking more information about.

ABS Series IDs start with a letter (always "A"). This ensures they are suitable for use in various products (eg for naming ranges in Excel) where a purely numeric ID would not be suitable. This is followed by a number unique to each series. The ID is completed with a calculated "check character", which helps to identify whether a quoted ID is valid (eg to pick up cases where two digits may have been transposed by mistake).

Series Start/Series End

The reference period, ie, month and year, of the first and last observations available for the Series.

No. Obs.

The number of observations contained in the Series.

Unit

The unit of measure applicable to the Series (eg, '000, $m, Percent)

Data Type

Stock, Flow, Derived

Freq.

The frequency of the observations; usually monthly, quarterly or annual.

Collection Month

Refers to series collected at quarterly and lesser frequencies only. Indicates which month in the collection period the data refers to. For example, for a quarterly series the data may be collected in the first, second or third month in the quarter. The 'Collection Month' would therefore be '1', '2' or '3' respectively.

Back to top
Named Ranges

ABS Time Series Spreadsheets contain numerous predefined 'Named Ranges' within them. 'Named Range' is a term that is associated with a cell or group of cells in a spreadsheet that have been assigned a name, or alias, by the user.

By default, Excel assigns names to each cell in a worksheet in accordance with its grid reference coordinate, eg, "A1" or "R1C1" (depending on the Reference style option selected), or in the case of a range of cells, "A1:A5" etc. A unique 'Name' can be assigned to a single cell or any number of cells, as a group, in an Excel workbook. The 'Name Box' in the standard Excel spreadsheet interface displays the name of the range currently selected.

Image: Spreadsheet


Click on the Name Box dropdown to view a list of all Named Ranges present in the Workbook (ie, all worksheets in the spreadsheet). Click on the list and the range is selected and the focus is moved to the selection.
Image: Spreadsheet


Names can be assigned to a cell or range of cells in a number of different ways. Please refer to the Help documentation in Excel or other spreadsheet package for details.

Named Ranges have been applied to ABS Time Series Spreadsheet extensively to facilitate navigation, interrogation and export of time series data. ABS Time Series Spreadsheets use the uniqueness of the Time Series Identifier (see Metadata) by assigning Names that include the Series ID to ranges of cells associated with that particular Series.

Each Time Series in an ABS Time Series Spreadsheet has three different Named Ranges assigned to it; the Named Ranges refer to ranges present in the Data Sheet/s:
  • The first range is assigned with a name that is the same as the actual Series Identifier (eg, A12345R). This range includes all of the series' metadata and all observations for the series. When you click on a Series ID hyperlink on the Index Sheet of an ABS Time Series Spreadsheet, you will be taken straight to the relevant Named Range (ie, the Series metadata and data will be selected) on the appropriate Data Sheet.
  • The second range is assigned with a name that begins with the Series Identifier and has "_Data" appended to it (eg, A12345R_Data). This range contains only the data component (ie, the series' observations) for the Series.
  • The third is a range assigned with a name that begins with the Series Identifier and has "_Latest" appended to it (eg, A12345R_Latest). This range contains only the latest observation for the Series (ie, a Range comprising of a single cell).
Two additional Named Ranges are present in each ABS Time Series Spreadsheet. These relate to the date range encompassed by the Time Series observations included in the spreadsheet. These ranges are "Date_Range" and "Date_Range_Data".
  • "Date_Range" includes all observation time points listed in the 'Data' worksheet/s as well as the series metadata headings present in cells A1 to A10 of the same. The metadata headings do not actually relate to the date range as such, but allow for easy alignment of series' and their respective observation points when copy/pasting a series selected via the Index Sheet hyperlink (see Range 1 above) into another spreadsheet.
  • "Date_Range_Data" includes only the observation points that correspond to the actual data, and should be used in conjunction with series' selected using the 'Range 2' option (see above).

For the casual user, the use of Named Ranges in ABS Time Series Spreadsheets may seem to be an insignificant detail, merely providing the means to provide a link from the Series Identifier on the Index sheet to the actual data. However, the presence of Named Ranges and the structure of the naming conventions applied to those Named Ranges provide an extremely robust schema that facilitates the export and manipulation of ABS Time Series data for 'power users' of Excel. See
'
Referencing Time Series Data' for details.
Back to top

Locating a Time Series

After locating and opening the appropriate ABS Time Series Spreadsheet the Index sheet should be used to identify the Time Series required. The Index Sheet provides a user interface that is designed to enable access to the underlying time series data. Users are presented with a table of time series metadata, uncluttered by the data itself.

Locating Data

The Series Identifiers displayed on the ABS Time Series Spreadsheet Index sheet are also hyperlinks to the data series' ranges, and the Index sheet should be used to identify and locate the required data series 'manually'. However, once a user becomes familiar with the Series Identifiers associated with their data requirements, other functionality built into Excel may be utilised to access the data, ie, to locate and select it.


For example, series can also be accessed via the Excel 'Name' box; a drop-down menu usually located adjacent to the Excel Formula Bar. The Name Box will usually display a single cell reference, eg, "A1" unless an existing Named Range happens to be selected. When clicking on the dropdown, all Named Ranges available in the File, not just the Worksheet currently in the foreground, are displayed in alphabetical order; locate the required Series Identifier (scroll), click it and get taken to the data and/or metadata associated with the selected Named Range.

Similarly, the 'Edit/Go To' menu command (or keyboard shortcuts <F5>, <ALT E G> or <Cntl + G>) can also be used to 'go to' (ie, locate and select) the required Named Range.

Image: Go To
Users that regularly download each issue of an ABS Time Series Spreadsheet, harvesting data from a particular series or several series, it is recommended that once the relevant series has been identified and located in the first instance, that the associated Series ID be noted for future reference. This will allow the user to quickly and easily locate the required time series when future issues are made available.

The Data Sheet/s

The "Data" sheets have been designed specifically to facilitate the integration of the spreadsheet into client workflow systems, particularly Microsoft Office based applications.Data sheets are designed to support automation and export of the series data and metadata contained within; users should use the 'Index Sheet' for manual navigation (click the Series ID link).
Back to top

Bookmarking ABS Time Series Spreadsheets

Bookmarking (adding to 'Favourites') ABS Time Series Spreadsheets is possible using a number of different techniques. When the spreadsheet is opened within a web browser window, users can simply add a bookmark in the standard manner appropriate to the relevant web browser being used (eg, in Internet Explorer use the 'Add to favourites' option). Using this method however, will always take the user to that particular issue of the spreadsheet.

In the past it has not been possible to bookmark the 'latest issue' of a particular Time Series Spreadsheet directly, however, the ABS Website has now been modified to support this functionality. See 'Creating Persistent Bookmarks' (below) for details.

You can bookmark the 'latest' issue of an ABS Statistical Product at the Summary Tab; a bookmark can be created that will take the user to the relevant page of the ABS Web Site where the links to the Time Series Spreadsheet can be located under the 'Details' tab displayed.

To bookmark a 'latest product', create a bookmark manually and use the following syntax:

http://www.abs.gov.au/ausstats/abs@.nsf/mf/<catalogue number>

where <catalogue number> = the ABS Catalogue Number of the required product, eg, http://www.abs.gov.au/ausstats/abs@.nsf/mf/6401.0

Clicking on this bookmark will take the user to the 'Summary' tab for the latest issue of the Product; click on the 'Details' tab to access the Time Series Spreadsheets.

You can also 'bookmark' the required Product directly within Excel, or other applications that support hyperlinking, using the above syntax. For example, in Excel the 'Insert Hyperlink' function can be used as displayed below. From the menu select 'Insert' → 'Hyperlink'.
Image: Insert Hyperlink

Image: Spreadsheet, hyperlink example


Or, the URL can be typed directly into a cell:
Image: Spreadsheet


Alternately, using the Hyperlink worksheet function will achieve a similar result.






When the user clicks on the link, the default web browser will open with the relevant web page on the ABS Web Site displayed.

Creating Persistent Links to the Most Recent Issue of an ABS Time Series Spreadsheet

It is now possible to create hyperlinks (as described above) that will always point to the most recent issue of a particular ABS Time Series Spreadsheet, or to a particular time series (or part thereof) within an ABS Time Series Spreadsheet.

There are two distinct methods available; the first method involves the creation of a URL that refers to a particular file and path within the ABS Website hierarchy. The second method utilises a URL that calls a server-side script that accepts an official ABS Time Series Identifier (Series ID) as an argument; using this method the path and filename of the spreadsheet are not required.

To determine the filename of the ABS Time Series Spreadsheet required, hover your mouse pointer over the 'Free Download' link adjacent to the required spreadsheet (or alternately copy & paste the link/shortcut/address to Notepad or similar). The filename and its extension appear immediately following the "log?openagent&" section of the URL.

eg, http://abs.gov.au/AUSSTATS/abs@archive.nsf/log?openagent&640101.xls&6401.0&Time Series Spreadsheet&11BD859813CC8817CA2573D8001754B4&0&Dec 2007&23.01.2008&Latest

Alternately, download the file - the filename and extension will be displayed when you are prompted to 'Save' or 'Open'.




Method 1

To create links using this method, the filename of the required ABS Time Series Spreadsheet must be known.

To open (or save) the most recent issue of an ABS Time Series Spreadsheet with its Index sheet displayed, use the following pattern:

http://www.ausstats.abs.gov.au/ausstats/<database>/LatestTimeSeries/<filename>/$FILE/<filename.ext>

Where

<database> = the database where the attachment is stored. If the attachment related to a Main Economic Indicator (MEI) it will be stored in the Meisubs.nsf database. All other Time Series Spreadsheet attachments will be stored in the ABS@Archive.nsf database. MEI's are 5206.0, 6401.0, 6202.0, 5302.0, 8731.0, 5676.0, 8750.0, 6416.0, 5609.0, 5368.0, 6457.0, 6354.0, 6345.0, 5671.0, 5625.0, 6427.0, 8501.0, and 9314.0.
<filename> = the base filename (ie, without full stop and file extension) of the ABS Time Series Spreadsheet required (eg, 640101), and,
<filename.ext> = the base filename and file extension of the ABS Time Series Spreadsheet required (eg, 640101.xls)

Eg, the URL "http://www.ausstats.abs.gov.au/ausstats/meisubs.nsf/LatestTimeSeries/640101/$FILE/640101.xls" will download and open (or save) Table 1 of the Time Series Spreadsheets available for Consumer Price Index.


Method 2

To creates links using this method, a (ie, any) Series Identifier (Series ID) present in the required ABS Time Series Spreadsheet must be known.


To open (or save) the most recent issue of an ABS Time Series Spreadsheet with its Index sheet displayed, use the following pattern:

http://www.ausstats.abs.gov.au/ausstats/<Database>/GetTimeSeries?OpenAgent&sid=<Series ID>

Where

<database> = the database where the attachment is stored. If the attachment related to a Main Economic Indicator (MEI) it will be stored in the Meisubs.nsf database. All other Time Series Spreadsheet attachments will be stored in the ABS@Archive.nsf database. MEI's are 5206.0, 6401.0, 6202.0, 5302.0, 8731.0, 5676.0, 8750.0, 6416.0, 5609.0, 5368.0, 6457.0, 6354.0, 6345.0, 5671.0, 5625.0, 6427.0, 8501.0, and 9314.0.
<Series ID> = Any valid Series Identifier appearing in an ABS Time Series Spreadsheet

Eg, the Series ID "A2325846C" refers to the series "Index Numbers ; All groups ; Australia ;" located in Table 1 of the Consumer Price Index Time Series Spreadsheets; the URL "http://www.ausstats.abs.gov.au/ausstats/meisubs.nsf/GetTimeSeries?OpenAgent&sid=A2325846C" will download and open (or save) Table 1 of the Time Series Spreadsheets available for Consumer Price Index.

Please note that the Series Identifier, in this case, is only used as 'a key' to open the Time Series Spreadsheet that that particular series belongs to; any Series Identifier in a given Time Series Spreadsheet can be used to this end. Using a Series Identifier in this context does NOT take the user directly to the actual time series it refers to.

The key advantage to using this method is that the correct ABS Time Series Spreadsheet will always be returned, regardless of its filename and where it may physically reside on the ABS Website.

Warning: when applying this method to the creation of hyperlinks within an Excel spreadsheet, the downloaded file will be automatically named "GetTimeSeries" by Excel. Attempts to download further spreadsheets (or the same spreadsheet again) will cause a 'Duplicate Filename' prompt to be displayed. To reference spreadsheets downloaded in this manner, it is recommended that they be saved to disk and renamed first.

Optional Parameters

Hyperlinks created using either of the methods described above will open the specified spreadsheet on its 'Index' sheet. However, it is possible to go directly to a particular time series by appending a hatch character, then a relevant Range Name, to a URL constructed in either manner shown above. URL patterns are:

http://www.ausstats.abs.gov.au/ausstats/<database>/LatestTimeSeries/<filename>/$FILE/<filename.ext>#<Range Name>
http://www.ausstats.abs.gov.au/ausstats/<database>/GetTimeSeries?OpenAgent&sid=<Series ID>#<Range Name>

ABS Time Series Spreadsheets contain three distinct named ranges that are named in accordance with, and/or correspond to, each ABS Time Series Identifier (see Section XXX above) present in the spreadsheet. It is therefore possible to create URLs that will take the user directly to the:
  • Series data excluding its metadata (append #<Series ID>_Data)
  • Latest observation available for a time series (append #<Series ID>_Latest)
Back to top

Maximising Use of the Index Sheet

ABS Time Series Spreadsheets can contain many individual time series, sometimes several hundred, and identifying the particular series required may appear to be somewhat difficult, at first glance, in these cases. ABS Time Series Spreadsheets have the Series Descriptions appearing on the Index sheet where similar series are grouped together in a hierarchy of contiguous 'blocks'. These blocks provide a visual cues that help reduce the time it takes to locate a series.
Image: Index Sheet


There are several additional techniques, however, that can be used to make the task of locating the desired time series in ABS Time Series Spreadsheets even easier, using standard Excel functionality.

Sorting

The standard Excel 'Sort' function can be used to find data much easier. For example, some ABS Time Spreadsheets do not have the Series Description column alphabetically sorted (which may make locating a particular series difficult), or, users may only be interested in a particular 'Series Type' (eg, Trend) or 'Data Type' (eg, Stock). To sort an Index sheet of an ABS Time Series Spreadsheet, the appropriate range must be selected first; sorting will not work correctly if entire columns are selected.
  • The selection should start in cell A10 and continue down to the last row of series metadata in column L. This can be achieved using a number of techniques; click and drag, or, hold down the Shift key and use the Down and Right keys, or, hold down the Ctrl and Shift keys while using the Down and Right keys. All cells from Row 10 down, and from Columns A to L must be included in the selection before sorting.
  • Next, click on the Excel 'Data' menuitem, then click on 'Sort...' - the Excel 'Sort' dialog box will then be displayed. Click the Radio Button labelled ('My data range has') 'Header Row' - this option allows the user to sort based on the metadata column headings rather than having to identify which column label (A, B, C etc) to use.
Image: Sort Dialogue

Users can also perform sorts on more than one column if required.
Image: Sort Dialogue

Applying Filters

Applying a filter or filters to the Index sheet of an ABS Time Series Spreadsheet is another easy and powerful method to help locate the desired time series. By applying a filter, the user can quickly and easily reduce the number of rows of series metadata visible on-screen. Excel supports two types of filter; an 'AutoFilter' and an 'Advanced Filter'. This section will deal the application of 'AutoFilters' only.

To apply a filter to all metadata appearing on the Index sheet of ABS Time Series Spreadsheets, it is necessary to multi-select a range of cells first. Excel will only apply the filter to non-blank columns including and adjacent to the cell that has been selected in the spreadsheet. For example, if the cursor is placed in cell A12 and the AutoFilter option is applied, the filter will only apply to the contents of Column A; this is because there are blank columns (columns B and C) adjacent to Column A. However, if the cursor is placed in cell D12 (or any cell in Row 12 from Column D to Column L) and an AutoFilter is applied, that column and all adjacent non-blank columns (ie, Columns D to L) will have the filter automatically applied.

To apply an 'AutoFilter':
  • Select cells A11 to L11 (eg, click on cell A11 and drag the cursor across to cell L11, or use the Shift, Ctrl & Right keys as described above)
  • From the Excel 'Data' menu, click on (or hover over) 'Filter', then select 'AutoFilter' - dropdown boxes will then appear on all columns of the Index sheet containing series metadata.
At this point each column of metadata can be filtered by clicking on the dropdown boxes now appearing in row 11 and selecting the filter criteria. However, the usefulness of a filter on a particular column will vary depending on the ABS Time Series Spreadsheet being worked on. For example, if an ABS Time Series Spreadsheet only contains Original (series type) data there is no value in attempting to filter by Series Type. Conversely, if an ABS Time Series Spreadsheet contains Original, Seasonally Adjusted and Trend data, and (for example) you were only interested in Trend data, then setting the filter criteria on Series Type to 'Trend' would limit the rows displayed on the Index Sheet to those items required.

Multiple criteria can be specified on multiple columns to further reduce the rows displayed, once again depending on the content of the particular ABS Time Series Spreadsheet being used. To expand on the above example where Trend data was only required, it may also be possible to also filter by Data Type (eg, just show' Index) or Unit (eg, just show 'Percent').

Filtering Series Descriptions

Individual Series Descriptions tend to be unique in an ABS Time Series Spreadsheet except where that spreadsheet includes data for all three Series Types (Original, Seasonally Adjusted and Trend). Therefore, filtering Series Descriptions as they stand may have little value. However, because Series Descriptions are comprised of individual classifications concatenated with a semi-colon delimiter, it is possible to parse Series Descriptions into their individual classifications and then filter each of them. This can be achieved by using the Excel 'Text to Columns' feature available from the 'Data' menu.

The 'Text to Columns' feature will place each classification into its own columns immediately adjacent to (to the right of) the column being parsed. Columns B and C on the Index sheet do not contain any data, and these can be populated with classifications separated out by parsing. However, if the Series Descriptions contain more than three classifications, additional columns (an extra column for each classification) will need to be inserted between, the text being split (column A) and the next data column (column D), before the 'Text to Columns' feature can be used.

To apply the 'Text to Columns' feature to Series Descriptions:
  • Select all Series Descriptions (ie, from cell A12 downwards)
  • From the 'Data' menu, select 'Text to Columns...' - the 'Convert Text to Columns dialog box will be displayed.
Image: Text to Columns
  • Ensure that the 'Delimited' option is checked, then click 'Next'; a second dialog box is displayed.
Image: Text to Columns
  • In the 'Delimiters' section, ensure that 'Semicolon' is selected and that all other options are unchecked - click 'Next'; a third dialog box is displayed.
Image: Text to Columns
  • Click on the last column header and check the 'Do not import column (skip)' option in the 'Column data format' section of the dialog. Click 'Finish'.
Image: Text to Columns

Series Description components will now appear in separate columns.
  • Now apply an 'Auto Filter' to these columns as described in the previous section


In this example, the classifications 'Labour Force Status', 'Sex' and 'State/Territory' can now be filtered independently thereby providing a powerful method to quickly locate the required time series. For example, if we were looking for the unemployment rate for females in Tasmania:
  • Select 'Unemployment Rate' from the first dropdown list.
Image: Spreadsheet, range selection
Only rows containing 'Unemployment Rate' will now be displayed.
  • Next select 'Tasmania' from the second dropdown....
Image: Spreadsheet, filter selection

Only rows that contain 'Tasmania' AND 'Unemployment Rate' will be displayed.
Image: Spreadsheet, filter example

  • Lastly, select 'Females' from the third dropdown.
Image: Spreadsheet, filter selection

Now only the required series remains displayed
Image: Spreadsheet, filter example

Back to top
Referencing Time Series Data

Named Ranges have been used extensively throughout ABS Time Series Spreadsheet to facilitate referencing Time Series data. Each Time Series appearing in an ABS Time Series Spreadsheet has been 'ranged'. That is to say that each Time Series has had three uniquely named Named Ranges applied to it. Also, each Named Range assigned to a given Time Series shares a common component in its Range Name; the Series 'name' or Series Identifier. See the Named Ranges section of this document for full details.

The key to referencing ABS Time Series data is the Time Series Identifier. Once the appropriate Time Series has been identified and located using the methods described in earlier sections, the associated Time Series Identifier can be used to locate that Time Series quickly, easily and reliably every time you need to access that same Series, regardless of the Issue (eg, Dec 2005, April 2006) of the spreadsheet being interrogated, and regardless of whether the Time Series has physically changed its location within that ABS Time Series Spreadsheet (ie, on a different Worksheet or in a different Column).

This methodology has been used internally in ABS Time Series Spreadsheet to reference each Time Series. The Index sheet Series ID column contains hyperlinks that reference a Named Range that has the same 'name' as the Series ID. For example, if the user clicks on an Index sheet Series ID hyperlink 'A12345', the Range with the same 'name' as the Series ID, ie, 'A12345', is selected.

A similar methodology can be applied by end users that have data models set up that contain or reference ABS Time Series data; once the relevant Series ID is known (see 'Locating a Time Series'), spreadsheet (and other) models may be set up with formulae referencing the appropriate Named Range in an ABS Time Series Spreadsheet.

Linking Using Excel Formulae

Most Excel users will be familiar with the concept of linking where, rather than typing a value in a cell, a reference to another cell is used instead. The reference can point to any cell in the 'current' Worksheet, the 'current' Workbook, or, to any cell in any Excel Workbook that the user has access to. At the basic level, the user can type '=' into a cell, then navigate to, and click on another cell; the value of the second cell is displayed in the first cell.

For example, if cell A5 in a Worksheet contained the value '1000' and the user typed '=' in cell B5 and clicked on cell A5, the value of cell A5 (1000) will be displayed (in cell B5).


Image: Spreadsheet

Image: Spreadsheet


Note the value in the Formula Bar of the second screen shot; it displays the reference while the cell displays the value of the reference.

This concept extends to references in other Worksheets in the Workbook, and to 'external' Workbooks. Note the values displayed in cells and in the Formula Bars of the following examples:
Image: Spreadsheet

Image: Spreadsheet

Linking to a cell in another Worksheet in the same Workbook.
Image: Spreadsheet

Image: Spreadsheet

Linking to a cell in another Workbook.

In the context of ABS Time Series Spreadsheet, please consider the following scenario:


A user maintains a composite spreadsheet model that includes the latest statistics that are available from ABS Time Series Spreadsheet. Rather than typing or copy/pasting the relevant figures from the ABS Time Series Spreadsheet into the model, the user uses links their model to the ABS Time Series Spreadsheet instead.
Image: Spreadsheet



In the example above, an absolute cell reference is used to form the links. That is, the reference points to a specific grid position (ie, cell M357 in the 'Data1' worksheet) in an Excel Workbook. The down side of this approach is that, if the user was expecting to find a particular value in a cell of an external Workbook (eg, an ABS Time Series Spreadsheet) using absolute cell references in their formula, and, it happened that that value was not located in exactly the same grid-reference position that the user was expecting, then unexpected results or an error, may be returned.

All ABS Time Series Spreadsheets are updated on a regular cyclical basis, either monthly, quarterly or annually. With each new issue, extra observations are added to each time series contained within; new rows of data are appended with each release. Using the methodology applied in the above example, when the user wishes to subsequently update the model with April 2005 data, they would be compelled to change the reference in their formula to increment the row-count component by 1, ie, =[6202001.xls]Data1!$M$338.

Using this method introduces an undesirable level of risk in terms of consistently providing the correct figures for the required period. For example, if the formula is not updated, an inaccurate result will be returned. If the user DOES update the reference but the series has moved to a new position (ie, appears in a different column) since the previous issue was released, then data will be returned for an incorrect series altogether with potentially disastrous results.

Referencing Named Ranges in ABS Time Series Spreadsheets

Named Ranges

ABS Time Series Spreadsheets use Excel Named Ranges extensively to reference the data series' within. As mentioned above, each data series has its own unique Series Identifier, and, a corresponding Named Range with the range name being the same as the identifier itself. For example, a series with a Series Identifier of "A4513C" will have all of its data and metadata contained in a range named "A4513C".

By default, the Series hyperlinks visible on the Index sheet of an Time Series File point to their corresponding Named Ranges in the Data sheet. By clicking on a Series hyperlink, the entire corresponding range in the Data sheet is selected, ie, the series data itself and its metadata, ready for manipulation, charting etc.

As all series contained in a given ABS Time Series Spreadsheet may not necessarily have the same Start Date, for those series starting later than the others there will be gaps (blank cells) between the end of the Series metadata (ie, Row 10) and the first observation for the series. This scenario is catered for (ie, the blank cells are disregarded and are NOT selected) through the use of non-contiguous Named Ranges in this case.

As well as containing these 'standard' Named Ranges, Time Series Files are currently provided with two additional 'range sets'. The first 'set' contains only a single range; a range named "Date_Range". This range contains all of the observation points (dates) for all of the series contained within the File. Note: some series may start later or finish earlier than others in the File. The 'Date_Range' range contains File level or 'global' start and end dates.

The second 'set' is similar to the 'standard set', but includes the series data only, not its metadata as well. These ranges are named using the same convention as for the 'standard set', but are differentiated by having "_Data" appended the Series Identifier string. To use the example shown above, a range named "A4513C" will contain all of that series' data and its metadata; a range named "A4513C_Data" only contains the data for the series, and not its metadata as well. 'Data only' ranges have been provided to allow for easy importing of data (only) into other software packages, specifically database applications such as Microsoft Access, where series metadata has already been predefined from previous downloads.


This is why ABS Time Series Spreadsheet contain predefined Named Ranges; so that users can employ the range name as part of the link reference rather than absolute grid-references. When names are used in a link reference it doesn't matter where the range is located in absolute terms; the correct value will be returned regardless.

Each time series in an ABS Time Series Spreadsheet has 3 distinct names (named in accordance with the relevant Series ID) applied to 3 Named Ranges , namely:
  • <Series ID> (eg, A12345)
  • <Series ID>_Data (eg, A12345_Data)
  • <Series ID>_Latest (eg, A12345_Latest)

These names refer to different ranges in the Data(n) worksheets in an ABS Time Series Spreadsheet. These ranges include (respectively):
  • All observations and metadata for the selected time series
  • All observations for the selected time series (no metadata)
  • The latest observation available for the selected time series

ABS Time Series Spreadsheets have been designed to use the 'uniqueness' of the Series Identifier, and, the manner in which Excel enforces the unique naming of ranges in a Workbook by combining the two.

What does this mean?

This means that once the required Series ID is identified, the user has the key to accessing the required time series very easily. An overt example is that the Series IDs appearing in the Index sheet of an ABS Time Series Spreadsheet are hyper-linked to their respective <Series ID> Named Ranges in the Data(n) sheets, ie, click on the Series ID on the Index sheet and get taken to that series, already selected, ready to perform the next operation (eg, copy/paste, chart, etc).

Another key advantage of using names to reference a range of cells is that it doesn't matter where the specified range is in terms of absolute cell references within the specified workbook. When a name is used as the reference in formulae or script, the corresponding data can always be found regardless of its physical location. So if Series are moved around in an ABS Time Series Spreadsheet for whatever reason, required series can always be found by referencing a named range rather than absolute cell references (eg, "A12345" vs "Data1!$AA$315:$AA$330").

Applying this referencing methodology to the previous example (above), the formula would be:
Image: Spreadsheet


The real power of ABS Time Series Spreadsheets is the ability of users to use the strict conventions used in the naming of the Series ID and associated Named Ranges in the area of automation and integration with other systems (as opposed to casual browsing use in a 'manual fashion'). The design of ABS Time Series Spreadsheets provides a number of robust referential 'hooks' that be easily used by qualified application architects or experienced Excel users.

The 'Offset' Function

As indicated above, Named Ranges have been put in place so that users can easily access (a) a full time series including metadata, and (b) a full time series excluding metadata, and (c) the latest observation available for a time series. However, some users may have a need to access a subset of time series data that falls somewhere between the full series and the latest observation, eg, the last nobservations of a given time series.

In this case the Excel 'Offset' function can be used in conjunction with a 'latest' type named range to return the number of observations required.

The Offset function returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Syntax - =OFFSET(reference,rows,cols,height,width)

The following provides an example of how the Offset function can be used to return the latest six observations for a time series, and, their corresponding time points.
Image: Spreadsheet

The formulae that returns these results is as follows:
Image: Spreadsheet
The 'Hyperlink' Function

The 'Hyperlink' function creates a shortcut that opens a document stored on a network server, an intranet, or the Internet. When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file stored at the specified link location. The 'Hyperlink' function can also be used to link named ranges in an Excel Workbook. This functionality may be useful to those users that wish to create, for example, a single reference point (ie, an Excel Workbook) that contains links to all ABS time series that are used on a regular basis.
Syntax - =HYPERLINK(link_location,friendly_name)

For details, please refer to your Excel Help documentation
Back to top
Charting Time Series Data

Creating charts based on ABS Time Series Spreadsheet data can be a relatively simple exercise where only a simple, indicative chart is required. Setting up complex charts containing multiple time series, or charts that the user wishes to be automatically updated when the latest data is downloaded is also relatively simple but requires a little more effort.


Simple Charts

Click on the Series ID hyperlink on the Index sheet of an ABS Time Series Spreadsheet. Next, click on the Chart Wizard toolbar button Picture: Button - a dialog containing the available chart types is displayed:
Image: chart wizard


Select the desired chart type (usually a line chart) and click 'Next'.
Image: Chart wizard data source


At this point, the Data Range will need to be adjusted in order to provide a meaningful chart; delete the text after the = sign and up to and including the comma. In the above example, the Data Range would then read as '=Data1!$H$11:$H$430'. Click 'Next'.
Image: Spreadsheet, chart options


Optionally, you may wish to add titles etc., otherwise, click 'Next'.
Image: Spreadsheet, chart location


Select the location where you wish to display the chart and click 'Finish'.
Image: Spreadsheet chart

A fault with this type of chart is the absence of various labels that identify time periods, units of measure etc. Creating charts that include all relevant details and/or more than one time series is possible, however this requires the user to manually multi-select the appropriate columns (In the Name box, select the first range, and then hold down CTRL and select the other ranges or hold down the <Ctrl> key and drag down each column required for the display). In the following example all time periods and all observations for 'Employed - full-time; Persons' and 'Employed - part-time; Persons' have been selected.

Using the Chart Wizard function as described above, the following chart is created. Note: because, in this case, the series' metadata has NOT been included in the selection, that the series' ranges do not have to be adjusted in Step 2.
Image: Spreadsheet, chart with multiple data sources


On the 'Series' tab of the Step 2 dialog, type in a meaningful label for both series.
Image: Spreadsheet, chart with multiple data sources


In Step 3, add a Chart label and/or Series labels as required. Click on the other Tabs as required to make any further adjustments to the default layout.
Image: Spreadsheet, chart with multiple data sources


In Step 4, select a location for your chart to appear then click 'Finish'
Image: Spreadsheet, chart location

The result will be similar to the following:



Automatic Updating of Charts

It is possible to automate the updating of charts as and when new issues of ABS Time Series Spreadsheets become available and are downloaded to the user's environment. The multi-cell Named Ranges present in ABS Time Series Spreadsheets expand with each new issue so that all observations are always present in these ranges. Therefore, if a chart's series parameters are based on Named Ranges rather than absolute cell references, it follows that the chart should automatically update when a new issue of the source ABS Time Series Spreadsheet is downloaded to the user's 'working directory'.
Image: Spreadsheet, chart with multiple data sources


Back to top

Bookmark and Share. Opens in a new window


Commonwealth of Australia 2014

Unless otherwise noted, content on this website is licensed under a Creative Commons Attribution 2.5 Australia Licence together with any terms, conditions and exclusions as set out in the website Copyright notice. For permission to do anything beyond the scope of this licence and copyright terms contact us.