Seasonal Adjustment Outlier Thresholds

A good summary of the seasonal adjustment thresholds for outliers adopted by various organisations: https://1drv.ms/b/s!Aq4mhLQ_aASsaKR4_xJUPnSs74E?e=5LzwG5

VBA Cheat Sheet / Excel Macros Quick Guide

TERM	DESCRIPTION
ARRAY  index starts from 0 by default To amend Redim OR Option Base 1   However when assigning Range to array, array starts from 1. https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm https://bettersolutions.com/vba/arrays/option-base-1.htm
Static Array	Dim arr(0 To 5) As Long (other data types can be used)
Dynamic Array	Dim arr() As Long (other data types can be used)   By default, the array will start at 0. To change, place at top of module: "Option Base 1".   Dim lStudentCount()As Long     lStudentCount = Sheet1.Range("A" & Rows.Count).End(xlUp).Row       ' Create array of correct size     Dim arr() As Long     ReDim arr(1 To lStudentCount)
Preserve the values of a dynamic  array when it is ReDim	If we use ReDim on an existing array, then the array and its contents will be deleted.   To retain the original values, use Preserve. But the starting index value must be the same.   Dim arr() as String ReDim arr(1 To 2)     arr(1) = "Apple"     arr(2) = "Apple"   'It must start at 1. ReDim Preserve arr(1 To 6)
Fill dynamic array with values	Dim arr () As Variant arr = Array("John", "Hazel", "Fred")   Dim arr ()As Variant arr = Range("A1:D2") --> this will work if there is no reference to a worksheet or workbook   arr = Sheets("sheet1").Range("A1:G311").Value2 --> If there is a reference to a worksheet or workbook, you will need to use "Value" or "Value2"   Differences between Text, Value and Value2 a. Range.Text Property b. Range.Value Property c. Range.Value2 Property     a) Doesn't work with array! Returns the value exactly as it is on your screen. If your column width is too narrow and the value in the cell is shown as ###, that’s exactly what Text will return.   b) Returns the value in whatever format it was in Excel and converts it to the VBA equivalent data type. This is usually a fine way to extract the data, unless your data is formatted as currency or a date. Transmitting as a date may be useful, but the currency can cause some issues. The VBA currency data type only carries 4 decimal places, so your cell value may get truncated when converted to VBA.   c) Returns the value independent of format. Eg, a formatted date will be returned as the serial number date value Excel uses.   Adapted from https://wellsr.com/vba/2017/excel/excel-vba-assign-range-to-array/
2-dimensional array	Dim arr(1 To 5, 1 To 2) As Long   The  above is an array with 5 rows & 2 columns.
Calling out an element of an array	Specify the row and column of an array even if it is a n x 1.   Debug.Print arr(1)  --> Will give an error   Debug.Print arr(2,1)  --> correct     Example:   Dim arr() As Variant arr = ActiveSheet.Range("A1:A5").Value2   Dim c As Variant For Each c In arr     Debug.Print c Next c
WORKBOOK
ActiveWorkbook	Refers to the Workbook which is active (the one you see)
ThisWorkbook	Refers to the Workbook that houses the code
Workbooks(n)	n is a index number being in the order that the open Workbooks were opened
Workbooks("aBook.xls")	Refers to ONLY the workbook of that name
WORKSHEET (ONLY WORKSHEET)
Worksheets(n)	n is the index number of a Worksheet from left to right in a workbook
Worksheets("aSheet")	Refers to a Worksheet tab name
SHEET (INCLUDE WORKSHEET AND CHARTSHEET)
ActiveSheet	Refers to the Sheet which is active (the one you see)
Sheets(n)	n is the index number of a Sheet from left to right in a workbook
Sheets("aSheet")	Refers to a Sheet tab name
Directly use the code name without quotation marks	Refers to a Sheet code name Note: The code name remains the same if you change the Sheet tab name or the order of your sheets. So this is the safest way to reference a Sheet.   The code name can be found in the VBA Editor:     Chart6, Sheet1 are examples of  code names while the tab names are in brackets.     Changing the code name:   Select the Sheet and its Properties will show. Click on the box for (Name) and Name to change its code and tab name, respectively.
CHART
ActiveChart	Refers to the Chart sheet / embedded chart in a worksheet which is active (the one you see). If no chart sheet/embedded chart  is active, code will return an error.
Charts(n)	n is the index number of a Chart sheet from left to right in a workbook
Charts("Chart1")	Refers to a Chart sheet tab name
CELL
Cells ()	Cells (row, column) Column is optional. If only Row index is used & index exceeds the number of columns in the specified range, the reference will wrap to successive rows within the range columns. To refer to "A1": Cells(1)   To refer to "B1": Cells(2)   To refer to "A3": Cells(3,1) is the same as Cells(3, "A")   To select all cells in a worksheet: ActiveSheet.Cells.Select
Range ()	Refers to a single cell, adjacent cells, non-adjacent cells, row(s) or column(s)   Examples Range("A1")   Range("A1, B5")   Range("A3:E5")   Range ("3:3")   Range ("3:5")   Range ("A:A")   Range ("A:C") Range("A3:E5") is same as Range (Cells (3,1),Cells (5,5)) Range("PriceList") --> A named range. It is useful to have a named range when columns/rows/cells are added/deleted.   Range("B" & LastCopyRow2 & ":L" & LastCopyRow2), where LastCopyRow2 is a variable   Range("A2:" & Cells(LastUsedRow(i), LastUsedCol(i)).Address)
Rows()	Refers to a row(s)   To refer to all the rows in a worksheet: Activesheet.Rows   To refer to Row 1-3 in a worksheet: Activesheet .Rows("1:3")   To refer to Row 3 in a worksheet: Activesheet .Rows(3)   To refer to all the rows in a specific range: Activesheet.Range("B2:D4").Rows   To refer to Row 3 in a specific range: Activesheet.Range("B2:D4").Rows(3)
Columns()	Refers to a column(s)   To refer to all the columns in a worksheet: Activesheet.Columns   To refer to Column 3 in a worksheet: Activesheet .Columns(3) is the same as Activesheet .Columns("C")   To refer to Column A-C in a worksheet: Activesheet .Columns("A:C")   To refer to all the columns in a specific range: Activesheet.Range("B2:D4").Columns   To refer to Column 3 in a specific range: Activesheet.Range("B2:D4").Columns(3)
Row	Returns the row number. If more than 1 row is referenced, it will return the 1st row number in the specified range.   Returns "3": Activesheet.Range("F3").row
Column	Returns the column number. If more than 1 column is referenced, it will return the 1st column number in the specified range.     Returns "6": Activesheet.Range("F3:H5").column
THE RANGE.END() METHOD To find last used row/column. End() is akin to pressing the Ctrl+Arrow Key .
End(xlUp)	Example 1: Range.End(xlUp) Method1 LastRow1 = Cells(Rows.Count, 1).End(xlUp).Row Rows.Count is the Row number of the last row in the worksheet. Cells(Rows.Count, 1) will move down to the last row in Column A. Next, End(xlUp) will move up to the last used row in Column A. Finally, Row returns the row number of the last used row in Column A.
End(xlDown)	Example 2:   LastRow2= Cells(1, 1).End(xlDown).Row   From A1, move down to the last used row in Column A.
End(xlToLeft)	Example 3:   LastCol3= Cells(1, Columns.Count).End(xlToLeft).Column   Similar to Example 1, except it is to find last used column.
End(xlToRight)	Example 4:   LastCol4= Cells(1, 1).End(xlToRight).Column Similar to Example 2, except it is to find last used column.
HOW TO FIND THE SPECIFIC ROW & COLUMN NO. OF A USED RANGE?
	Example LastCopyRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row   Explanation ActiveSheet.UsedRange.Rows.Count will return the number of used rows in the UsedRange: 6   ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count) refers to the last row in the UsedRange: 6   ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row returns the row No. of the last row in the UsedRange: 16     LastCopyColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
WHAT IS PASSING ARGUMENTS BY VALUE VS BY REFERENCE?
	Passing Arguments By Value does not change the original variable as only a copy of the variable is passed.   Passing Arguments By Reference changes the original variable as the variable itself is accessed.     A copy of a variable is passed Original variable is changed Ref No Yes Val Yes No   Example for ByRef vs ByVal   Dim i As Long Function Add_ByRef(ByRef i As Long) As Long i = i + 5 Add_ByRef = i End Function   Function Add_ByVal(ByVal i As Long) As Long i = i + 5 Add_ByVal = i End Function   Sub Test_ByRef () i = 2 Debug.Print Add_ByRef(i) ‘Return 7 Debug.Print I ‘Return 2 End Sub   Sub Test_ByVal () i = 2 Debug.Print Add_ByVal(i) ‘Return 7 Debug.Print i ‘Return 2 End Sub

 

 

COMMON ERRORS

1)      Activating a sheet is needed when selecting anything in it. Eg, Sometimes selecting a cell doesn’t work because the sheet has not been activated. At other times, the active sheet could differ from the sheet stated in the code. Hence it is best practice to always activate a sheet. ThisWorkbook.Worksheets("Data").Activate   2)      Ensuring a workbook is opened for formulas to be updated For certain formulas like Indirect and Offset when used to reference another Excel file; that file must be opened for the values to be updated. Offset can usually be substituted with Index which doesn’t require the referenced Excel file to be opened.

 

Download this article at https://1drv.ms/b/s!Aq4mhLQ_aASsaZDGdaKPusJ66BA?e=JV4aIP

Formulas and Definitions of Different Growth Rates Commonly Used by Statistical Offices (NSOs)

 

1. Average annual growth rate (AAGR)       
2. Compound annual growth rate (CAGR)  
3. Period-on-previous-period (POP) change (or growth rate)            
4. Year-on-year (YoY) change (or growth rate)        
5. Annualised level/value data       
6. Annualised growth rate (Annualised rate of change)       
7. Linear approximation of the annualised growth rate       
8. Annualised semi-annual growth rate       
9. References  

Average annual growth rate (AAGR)

Definition

Average annual growth rate (AAGR) shows the average annual growth for n years. OR Average annual growth rate (AAGR) shows the average change between the current year and n years ago;  assuming linear growth, geometric growth or exponential growth.

Formula

GR 1st year  : annual growth rate for 1st year GR nth year : annual growth rate for nth year n :  number of years   P:  level/ value y:  current year y-1:  preceding year   OR   Assuming linear growth,   P:  level/ value y:  current year y-n:  n years ago n :  number of years   For details on geometric growth or exponential growth, refer to OECD Data and Metadata Reporting and Presentation Handbook 2007.

Alternate Names

Average rate of return or growth

Examples in Write-up

The population of Lane County grew 12 percent between 1980 and 1990 or at a rate of 1.2 percent annually.

 

 

Compound annual growth rate (CAGR)

Definition

Compound annual growth rate (CAGR) shows the growth rate required  for a level/value at the beginning period to reach its new level at the ending period.

Formula

P:  level/ value y:  beginning period y+n:  ending period n:  number of years   Note: n is usually years. Other frequencies should be converted to years.

Alternate Names

Average annual (compound) growth rates

Examples in Write-up

NIL

 

 

Period-on-previous-period (POP) change (or growth rate)

Definition

Period-on-previous-period (POP) change (or growth rate) shows the growth between the current period and the preceding period.   POP is usually quarter-on-previous- quarter (QoQ) or month-on-previous- month (MoM).

Formula

OR   (for POP change)   P:  level/ value fth:  current period fth -1:  preceding period

Alternate Names

Period-to-period* growth rates/changes; Period-over-period* growth rates/changes; 1-period* rate of change; Rate of change on the previous period*;   *Can be substituted with month or quarter   For GDP, Japan's NSO terms “Changes from the previous term” for quarterly SA data:

Examples in Write-up

GDP is estimated to have fell by 2.2% in Quarter 2 2020.   The Australian economy contracted by 0.3% in seasonally adjusted chain volume terms in the March quarter 2020.

 

 

Year-on-year (YoY) change (or growth rate)

Definition

Year-on-year (YoY) change (or growth rate) shows the growth between the current period and the corresponding period of the previous year.    Year-on-year change (or growth rate) can be used for monthly, quarterly or annual data.

Formula

OR (for yoy change)   P:  level/ value t:  current period t-12:  preceding 12 months     Example for quarterly data: Q11:  value for 1st Qtr in 2018 Q1:  value for 1st Qtr in 2017   Example for annual data: A11:  value for 2018 A1:  value for 2017

Alternate Names

Year-over-year growth rates; Year-to-year growth rate; Rate of change from the previous year; 12-month rate of change; Through-the-year change; Annual growth rates/change   For GDP, Australia’s NSO (ABS) terms "y-o-y change" solely for annual data: Year -on-year Growth %   For GDP, Australia’s NSO (ABS) terms "through-the-year change" for quarterly data: Through-the-year Growth %   For GDP, Japan's NSO terms “Changes from the previous year” solely for annual data:   For GDP, Japan's NSO terms “Changes from the previous term” for quarterly NSA data:

Examples in Write-up

GDP contracted by 2.2% in Quarter 2  from a year earlier.   GDP contracted by 2.2% over the same period last year.   Based on advance estimates, the economy contracted by 13 per cent on a year-on-year basis in the second quarter of 2020, …   Australia’s NSO (ABS) Through the year GDP was up 1.4%.

 

 

Annualised level/value data

Definition

For a single period: Annualised level/value data shows the level that would be registered if the level for the current period was maintained for a full year.   For multiple periods: Annualised level/value data shows the level that would be registered if the level for the periods were maintained for a full year.

Formula

For a single period: P:  level / value fth:  current period f:  frequency /number of data points in a year   Example for quarterly data: For multiple periods: P:  level / value f:  frequency /number of data points in a year

Alternate Names

NIL

Examples in Write-up

NIL

 

 

Annualised growth rate (Annualised rate of change)

Definition

For period-on-previous period (POP): Annualised growth rate (Annualised rate of change) shows the growth that would be registered if the period-on-previous period (POP) rate of change were maintained for a full year.   POP is usually QoQ or MoM.   Annualised growth rates are often used for seasonally adjusted (SA) data. Users often compute the SA data QoQ or MoM change. Annualising the QoQ or MoM change will allow for comparison on the same magnitude as an annual growth or yoy growth rate.   SA data annualised rate of change is known as seasonally adjusted annualised rate (SAAR).   “Annualised growth rate” is sometimes used to describe the quarterly or monthly growth rate multiplied by 4 or 12. However, this is more appropriately referred to as “Linear approximation of the annualised growth rate” – refer below.   For year-to-date: Annualised growth rate (Annualised rate of change) shows the growth that would be registered if the rate of change between the current period and last period of the previous year was maintained for a full year.

Formula

For period-on-previous period (POP): OR   P:  level/ value fth:  current period fth -1:  preceding period f:  frequency /number of data points in a year   Example for quarterly data:   For year-to-date: OR   P:  level/ value fth:  current period lastfth:  last period of the previous year f:  frequency /number of data points in a year   Example for quarterly data:   Q4:  value for 4th Qtr 2019 Q2:  value for 2nd Qtr 2020

Alternate Names

1-month rate of change, annualized;  etc

Examples in Write-up

Real GDP decreased at an annual rate of xx% in the first Qtr of 2020   On a quarter-on-quarter seasonally-adjusted annualised basis, the economy shrank by 41.2 per cent in the second quarter.

 

 

Linear approximation of the annualised growth rate

Definition

Linear approximation of the annualised growth rate is a quick calculation of the annualised growth rate that show the rate of change that would be measured for a quarter, month or other frequency  if maintained for a full year.

Formula

P:  level / value fth:  current period fth -1:  preceding period f:  frequency /number of data points in a year   Example for quarterly data:

Alternate Names

Sometimes mis-named as Annualised growth rate; 1-month rate of change, annualized

Examples in Write-up

NIL

 

 

Annualised semi-annual growth rate

Definition

Annualised semi-annual growth rates show the growth that would be registered if the rate of change measured with reference to a 6-month period were maintained for a full year.

Formula

OR P:  level/ value t:  current period t-6:  preceding 6 months   Example for quarterly data:   Q1:  value for 1st Qtr Q3:  value for 3rd Qtr   Example for monthly data:   M1:  value for Dec M6:  value for Jun

Alternate Names

NIL

Examples in Write-up

NIL

 

 

References

OECD Data and Metadata Reporting and Presentation Handbook 2007, "Chapter 4. Guidelines for the reporting of different types of data" https://www.oecd.org/sdd/37671574.pdf   https://pages.uoregon.edu/rgp/PPPM613/class8a.htm https://study.com/academy/lesson/population-growth-rate-definition-formula-examples.html http://ocw.jhsph.edu/courses/DemographicMethods/PDFs/Session11Exercise.pdf https://www.investopedia.com/terms/a/aagr.asp https://www.investopedia.com/terms/c/cagr.asp https://www.investopedia.com/terms/a/annualize.asp https://www.dallasfed.org/research/basics/annualizing.aspx https://thesmartmethod.com/a-formula-or-easy-way-to-annualize-data-based-on-month/ https://bizfluent.com/how-8684386-annualize-quarterly-numbers.html https://www.quora.com/Using-an-annual-growth-rate-how-can-I-convert-monthly-revenue-growth-to-quarterly-revenue-growth