Tuesday, 12 April 2022
Seasonal Adjustment Outlier Thresholds
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
Dim arr ()As Variant
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).ColumnSimilar 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.
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.
|
Thursday, 7 April 2022
Formulas and Definitions of Different Growth Rates Commonly Used by Statistical Offices (NSOs)
- Average annual growth rate (AAGR)
- Compound annual growth rate (CAGR)
- Period-on-previous-period (POP) change (or growth rate)
- Year-on-year (YoY) change (or growth rate)
- Annualised level/value data
- Annualised growth rate (Annualised rate of change)
- Linear approximation of the annualised growth rate
- Annualised semi-annual growth rate
- 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
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
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
|
Subscribe to:
Posts (Atom)
How to Read in 1 or All Excel Sheets into a Pandas DF Using Xlwings?
Photo by Jeff Sheldon on Unsplash One of the advantages of using Xlwings...
-
With the prevalence of PDF files, there is a huge demand to convert the tables in these files into CSV/Excel for easy extraction of the d... -
Photo by Microsoft Edge on Unsplash As Python does not have readily available...