Users` Manual for the Revision Spreadsheets
Contents
1. E oa ae m a m Revisions to YoY Growth Rates lal Sim gt 15 7 MI In 11 OECD Microsoft Excel template_revisions_analysis_monthly_old_xls x File Edit Yiew Insert Format Tools Data Window Help Type a question For help lt ee Dwell B aAY 4 Bee HT Oo B SL 5 Meer Be Paste Special 2 x Arial 10 gt B Z U Paste C All validation Cc i indica Formulas All except borders oL a ato R a Pied eoe pe t Whol i Yalues Column widths A SEISA TN SRPS Formats Formulas and number Formats country indicator Comments C values and number Formats Relating First 2 3 A o estimate months months ene A SERTE EEN period P later later None Multiply Add Divide Subtract Skip blanks IV Transpose caei Revisions spreadsheet M3_P Z M3_M2 Y1 PY2 P L_P Y2 Y1Y1 M27 4i gt Select destination and press ENTER or choose Paste NUM M 4 Step 2 REPLACING EMPTY CELLS WITH ZERO VALUES If the month on previous month QoQ or year on year growth rate is zero the program leaves it blank This causes some errors in the X_Y worksheets therefore it needs to be fixed In order to do that identify blanks in the middle of the series and fill them with a 0 using CTRL R leave find what blank and put a 0 in the replace with Note that the blank cells after the end of the rel2. SS E Sel E T T 14 Apr 33 e a e nn ss es ee 15 an a ae ee een ee a Eee 16 Jun 33 133 8 1337 133 9 134 6 ne Sess EEE Be Ss Ss E 17 Jul 33 115 7 gt EE EE T ee ey EE ee 18 Aug 39 115 7 eS ae a 19 Sep 93 n57 Es SE See Ge Es 20 Oct 33 15 7 eS RAS hn Ge en eee 21 Nov 33 nsz e U U eee es eee eee ee 22 Dec 33 azo ae eee y ee E eae 23 Jan 00 1216 1220 E 24 Feb 00 121 8 122 3 1228 SESS ae Sa 25 Mar 00 1215 1219 122 5 123 7 Been ee eae eee 26 215 i219 1225 123 8 aaa ee Gee ee 27 zis 1219 122 4 123 6 ae ee Eee eee 281 elon nn 16 3 117 2 N76 112 134 nan 1a 4 12N 4 in 1215 1213 122 4 122 3 1232 R 174 5 125 bo 44 gt bt Leyels MoM Growth Rates ri Revisions to MoM Growth Rates YoY Growth Rates Revisions to YoY Growth Rates P gt Ready NUM Once the Levels worksheet has been filled in properly values in the growth rates and in the revisions to growth rates worksheets will be automatically derived Check that the data in all the worksheets look correct Microsoft Excel template_triangle_monthly_old xls a x File Edit Yiew Insert Format Tools Data Window Help Type a question for helg v amp X DSHS ahay B g na 23 Me B oo BB f lt 7 Fel E Arial 10 B Z U 6337 X f A z F ME H Il Hil ie 4 x N oo os ut IH n aA i ey te Or See SS Ve 2 A EJ o
3. Value and Transpose and click Okay Microsoft Excel template_revisions_analysis_monthly_old xts a x B9 File Edit View Insert Format Tools Data Window Help gt e a question For help a ec Der we em Bay ZBA STi MO A 2 7l wrx BO ae 2 wf Arial 10 B Z U E S 2 Be Sw 68 s8 EE te ee DAS B20 Fe B C D Fe G H 1 J K E mi m E Q R S T 1 Country indica ae Cut 2 Revisions spre 3 country indicator Copy amend A f E Paste Main orp Pe mest A 2 gears Latest absolute asic P Paste Special later estimate zatar of 4 Insert Copied Cells LF Delete p Y2_Y1 Clear Contents v1 Raz Insert Comment Format Cells 16 3 p 17 Pick From List ia Add Watch 2 Hyperlink 27 r 28 29 pe 30 31 0 0 an fate i on bet m 4 gt gt Revisions spreadsheet Summary statistics Mm2pP ms_pP ms_m2 svi P syv2P _p fsyv2_vyif y1_M2 jal gt mand 10 OECD Microsoft Excel template_revisions_analysis_monthly_old_xts x i File Edit View Insert Format Tools Data Window Help e a que e Tna De Bee BAY g BA S O Sxz S ll B 7 Be I 2 fh Arial Pasta SAER ajx gt 18 8 E Aan B20 A Paste H i J kK E PA MI o nl a R ad E ry 1 Country i All validation J Revisions Formulas All except borders country indicator fe values Column widths pe A Formats Formulas and number For
4. or the slope error UD is the disturbance proportion of MSR 1 e the proportion of MSR that is not caused by systematic difference between earlier and later estimates Good preliminary estimates have low values of UM and UR and a high value of UD More detailed information on these statistics can be found in a paper written by Tommaso Di Fonzo available at http www oecd org dataoecd 55 17 35010765 pdf Appendices A and B contain comprehensive technical details on the test statistic 17
5. 001 0 005 0 004 o 1 K Sep 39 0 2 0 2 0 6 02 0 6 0 3 0 0 ER 0 032 0 20 0 321 0 014 0 112 1 o 14 Oct 39 o6 0 7 01 OF 01 0 0 6 woe 0 57 0 02 0 004 0 034 0 002 1 1 15 Nov 99 03 0 2 0 1 0 2 01 0 0 01 Delete 0 06 0 57 0 011 0 006 0 061 o 1 16 Dec 99 0 4 o6 02 0 6 0 2 01 0 0 11 0 06 0 047 0 022 0 013 1 1 17 Jan 00 1 0 0 6 0 4 0 6 0 4 0 2 02 Clear Contents 0 22 0 11 0 193 0 095 0 047 o 1 18 Feb 00 0 3 o5 0 2 05 0 2 0 0 0 4 0 44 0 22 0 018 0 059 0 029 1 1 190 Mar 00 02 0 6 0 4 0 6 0 4 0 2 02 Insert Comment 0 13 0 44 0 138 0 050 0 163 1 1 20 Apr 00 0 3 0 7 0 2 OF 0 2 0 0 od 0 37 0 13 0 038 0 072 0 026 o 1 21 May 00 0 4 oF 0 3 OF 0 3 0 1 0 4 T Format Cells 0 19 0 37 0 081 0 056 0 106 1 1 22 Jun 00 0 2 o5 0 2 o5 0 2 01 03 f 0 29 0 19 0 043 0 059 0 041 1 1 23 Jul 00 0 4 0 2 0 6 0 2 0 6 0 4 0 2 Pick From List 0 21 0 29 0 413 0 135 0 185 o o 24 Aug 00 0 3 oF 0 4 0 7 0 4 01 O 6 Add Watch 0 65 0 21 0 131 0 234 0 075 1 1 25 Sep 00 0 2 0 2 0 0 02 0 0 0 0 0 4 s 0 36 0 65 0 001 0 009 0 016 o 1 26 Oct 00 0 1 0 2 0 2 0 2 0 2 0 0 0 0 Hyperlink 0 02 0 36 0 030 0 004 0 062 o 1 27 Mov 00 0 2 0 2 0 1 03 01 0 0 0 2 emake wi 0 17 0 02 oom 0 012 0 003 o 1 28 Dec 00 0 6 0 5 01 05 01 0 0 01 0 2 0 13 0 10 0 17 0 016 0 012 0 022 1 1 29 Jan 01 0 2 0 9 0 6 0 3 o6 03 01 01 0 58 0 13 0 10 0 333 0 073 0 059 o 1 30 Feb 01 0 5 0 2 0 3 02 03 01 0 6 01 0 23 0 58 0 13 0 082 0
6. 38 t t Hre ae Q12 x Ke F G H l J K L M N o P RESSE Z 4A E 2 United Stal Production 3 1 Levels Find and Replace Find Replace Find what E v Replace with Options gt gt Replace All Replace Find All Find Next Close ia a Book Sheet Name Cell Value Formula id 15 temp Levels Cpe an 46 temp Levels R 17 temp Levels Bee 18 temp Levels T 19 20 21 22 23 24 25 i204 120 7 1215 26 1204 120 7 1215 1219 i204 120 7 1215 1219 2r i ai Z ERE yp 17A I 1131 Wa4 1 190 1 194 in 1715 1719 shin nin _ os as mta Growth Rates YoY Growth Rates Revisions to YoY Growth Rates Sam z i OE E i a M 4 gt ah Levels f MoM Growth Rates Revisions to MoM H ba Ready NUM After this cleaning the worksheet should look as below OECD Microsoft Excel template_triangle_monthly_old_xls x File Edit View Insert Format Tools Data Window Help Type a question For help a eee D g ESA Bay t uBR IY wY 2 21 i Bom e Arial 8 B Z U 2 2 ZE Q12 U 1 OECD ME 2 United St 3 1 Levels 7 f peye EEE es ES o rere ws we Pa Dae oe GEE I s E Feb 33 134 2 E ae ees eee ian Ee eee Ee 13 Mar 33 134 0 133 9 se PSs
7. tel E D A A v DotStatQuery B1 C D E E G H l J K B Lial N gt A Dataset Main Economic Indicators Original 2 release data and revisions Country United States ariable Index of industrial production 5 Frequency Monthly 6 February March pril 1999 May 1999 June 1999 July 1999 ugust Septembe October November December January DAE 1999 1999 1999 r 1999 1999 1999 1999 2000 i i EE 134 2 134 133 8 133 8 133 8 115 7 115 7 115 7 115 7 115 7 116 9 116 9 Ee 133 9 133 8 133 7 133 7 115 6 115 6 115 6 115 6 115 6 117 2 117 2 10 134 134 133 9 115 8 115 8 115 8 115 8 115 8 117 6 117 6 11 J 2 z 134 2 134 6 116 5 116 5 116 5 116 5 116 5 118 1 1181 EEJ 2 2 2 p 135 4 117 116 9 116 8 116 8 116 8 118 4 118 4 13 2 2 F F 117 2 174 1171 1174 A 119 119 14 A F F 7 117 3 117 3 117 3 117 3 119 4 119 4 15 ie g amp z A x 118 4 118 4 118 120 120 16 z a x E 2 x 3 118 5 118 4 120 3 120 4 n z T F x F X F 118 120 3 120 6 18 r 2 Z e a 3 5 z z a 121 121 6 M 4 gt gt I Index of Industrial Production jal gt NUM Ready Step 2 MATCHING REFERENCE PERIODS Identify the first edition of MEI publication e g February 1999 and the reference period e g December 1998 of the downloaded data Find the corresponding MEI edition and reference period in the worksheet Levels of the Revision triangles template Step 3 IMPORTING DOWNLOADED DATA INTO THE TEMPLATE The downloaded data
8. will be used The Levels worksheet and the Revisions to MoM YoY Growth rates worksheets are not going to be used in this process The revision analysis template consists of the followings e REVISION SPREADSHEET each column of this worksheet reports the growth rates for the first published estimates labelled as P estimates published 2 months 3 months 1 year and 2 years later labelled as M2 M3 Y1 and Y2 respectively and the latest estimates labelled as L The periods of reference for each value are given in the first column The only difference for quarterly source data is that estimates published 2 or 3 months later M2 and M3 do not exist therefore the revision spreadsheet for quarterly data will contain estimates published 5 months later M5 along with P Y1 Y2 and L e SUMMARY STATISTICS this worksheet contains the summary statistics for each period compared which are extracted from the summary statistics tables in the X_Y worksheets e X Y The X_Y worksheets evaluate the revision that occurred between period X and Y e g the comparison M2_P is an analysis of the revision between the first published estimates P and those published 2 months later M2 These worksheets contain the intermediate values necessary for the computation of all statistics for each worksheet in fact a summary statistics table a bar chart of revisions between X and Y and a line graph of the time series X and Y are automatically provided 2 2 How
9. 165 0 036 1 1 31 002 Mar 01 0 4 0 4 0 8 0 4 og o6 0 3 0 6 0 81 0 29 0 58 0 657 0 232 0 467 o o AAT e sn oe es E a el lt a E LA ns ns oe Fe eels Ka E E Fo AAO OOOO A m 4 gt HA Revisions spreadsheet Summary statistics M2_P M3_P M3_M2 Y1_P Y2_P Fd e E EE gt Ready NUM Step 7 FINAL CHECK View the summary statistics revision bar chart and line graph of comparison in the X_Y worksheets Microsoft Excel template_revisions_analysis_monthly_old_xls Z a x E File Edit View Insert Format Tools Data Window Help Type a question For help z T A Cees 44Y BA S aa E S 2 l a 7 B T k lt 2 ae z at Y Arial 10 B Z u B 3 BBS a 8 08 E E n DA H5 f A B E D E F G H i J K L mA M O P Q R 1 United States Index of Industrial Production Calculations of summary statistics for the revisions analysis USA_IIP Jan 99 feb 06 Total 7 657 1 631 0 873 Revision L P Jan 99 feb 06 95 sample _36 n 3 0 97 mean absolute revision 0 2 33 mean revision Rbar 0 0 _33 st dev Rbar HAC formula 0 0 100 mean squared revision 0 1 101 relative mean absolute revision 0 5 102 t stat 0 7 103 t crit 2 0 104 Is mean revision significant NO 105 Correlation between L and P 0 8 106 Min Revision 0 8 107 Maz Revision 0 7 108 Range 1 5 109 xL gt P 50 6 110 x Sign L Sign P 88 0 m Variance of L 0 3 n2 ariance of P 0 3 n3 U
10. 3034 0 2193 2 1642 0 2261 1 3839 1 9930 NO 0 9583 6 3 4 3 10 6 51 4 95 9 30 1158 32 7492 3 33 7 11 88 90 t pare P 2 R DIE t P is the preliminary or earlier l tags Bem a ee woe Te var R ramp E tR n h h with SR R Other statistics useful for evaluation of various aspects are 7 Range Max Revision Min Revision m Max Revision value of the highest revision m Min Revision value of the lowest revision m Later gt Earlier the percentage of observations where the later estimate is larger than the earlier estimate 1 e revision is greater than 0 m sign later sign earlier the percentage of observations where the sign of later estimate and the sign of earlier estimate are the same Decomposition of the mean squared revision Mean squared revision measures the variance of revision based on a symmetric and quadratic loss function MSR decomposed and divided by itself gives 1 UM UR UD where 2 S ps UM yr A psi up e MSR MSR MSR and S is the variance of earlier estimates S the variance of the later estimates and p the correlation between them UM is the proportion of MSR due to mean revision not being equal to zero It is thus also known as mean error If we consider a linear regression model of the earlier and later estimates L PP u UR is the proportion of MSR due to the slope coefficient J being different from 1
11. Dec 99 ANN BG NN GG SE BEE pea ye J lan AN no af na a gt oe a gt os 2 a gt no a2 M 4 gt gt t Levels MoM Growth Rates Revisions to MoM Growth Rates Fd YoY Growth Rates Revisions to YoY Growth Rates Ready NUM a OECD Microsoft Excel template_triangle_monthly_old xls x Eile Edit Yiew Insert Format Tools Data Window Help Type a question For help c amp x DEESA BBY ABE STi o a A E 2 Z Me Bw ooo F a 4 m oO 4 le N i ll ist 9 x 8b bs ut IH E 2 A mi a a ho 4 a E LE re es Dec 99 Sa PA Jan 00 OOo 23 Feb 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 baal M o4 o gt h Levels f MoM Growth Rates Revisions to MoM Growth Rates YoY Growth Rates Revisions to YoY Growth Rates add gt Ready NUM 2 Revision analysis template The second part of the process concerns importing data from the Revision triangles template to the Revision analysis template which can be accessed at http www oecd org dataoecd 24 58 36789516 xls for monthly input data and at http www oecd org dataoecd 24 59 36789544 xls for quarterly input data Z1 Structure of the Revision analysis template The Revision analysis template provides the user with summary statistics for the revision analysis using the growth rates calculated in the Revision triangles template Note that now only the growth rates worksheets
12. EE See eee SO aE E SEEE Clear Contents H ne a SA Insert Comment G ass ee hE a E mu P Se SSS SS Pick From List p Aoo oA Hyperink hh gt a E E 27 a a e mM 4 gt hh Index of Industrial Production 4 gt Ready Sum 10971 NUM gt In the downloaded data from the database which has now been copied each column represents the monthly edition of the MEI and the rows are the reference periods for the values published as opposed to the Revision analysis template in which the rows represent the monthly edition of the MEI and the columns the reference periods Since it is inverted the copied data must be transposed when pasted Click on the right hand mouse button and select Paste special Check the boxes Value and Transpose and then click Okay gt Note that if you are pasting a variable with large values e g GDP levels the width of the columns of the worksheet will need to be adjusted in order to be able to see the numbers otherwise they just appear as Remember that it is in the second block that the data should be pasted Microsoft Excel template_triangle_monthly_old_xlts a x Ey File Edit Yiew Insert Format Tools Data Window Help Type a question For help xX D Esa 42a4Y E BSF O Eo l Meee BB Paste Spec lal 2 x Arial 8 B Z U 2322 p A t 5 EE te D A Paste P12 X f E all yalidation A B eee ne E in ae L M N Formulas C All except borders OECD MEI revi
13. EFINING THE SAMPLE PERIOD Change the sample period in the upper right hand corner so that it corresponds to the period under analysis Here the sample period corresponds to the common period between periods X Y Sample period in the summary statistics table automatically changed as well Microsoft Excel template_revisions_analysis_monthly_old xls Ey File Edit View Insert Format Tools Data Window Help D HSA BEY BBY a z se Bm OB I gt 2 chi 10 B2U F339 ws EE Ue O A N12 f A B c D E H I J K E m 1 United States Index of Industrial Production _2 Revisions spreadsheet Whole sample 3 USA_IIP E Main t z LE CEL 2 gears Latest absolute ed ich ang TEONE eres Sa later later estimate value of period P later later P Comparison Sample M2_P Jan 99 dec 06 M3_P Jan33 nov 06 RM3_M2 Jan 399 nov 06 YIP Jan 99 feb 06 Y2_P_ Jan 99 feb 05 L_P_ Jan 99 feb 07 03 0 6 0 3 0 3 0 4 0 8 0 3 0 1 0 5 0 7 0 3 0 2 ac nA na i M 4 gt gt A Revisions spreadsheet Summary statistics M2_P M3_P M3_M2 4 Y1_P 2_P L_P f v2_ 1 4 1_M2 Ready and X Y worksheets will be Y2_Y1 Jan 99 feb 05 YI_M2 Jan 99 feb 06 pe a que help v amp X a gt N Oo Ez Q R S t al Eim ag NUM Step 5 ADJUSTING THE RANGE OF TIME PERIOD Each worksheet is arranged to host the widest range of time period possible Obviously depending on the country chosen and the variable selected for t
14. M x 0 4 Chart to compare earlier P and later L estimates 14 UR x 7 8 First published estimate P Estimate published 1 year later L v mM o4 gt a Revisions spreadsheet Summary statistics _ M2_P 4M3_P M3_M2 Y1_P Y2_PL_P Y2_Y1 Y1 M27 of Ready NUM 3 Description of Summary Statistics 15 A revision is defined as R L P where L is the later estimate and estimate n is the number of observations The following are the relevant formulas for the main statistics t included in the summary statistics tables In order to test whether mean revision is significantly different from zero we perform a t test with the test Statistic where st dev Rbar HAC Formula or the heteroscedasticity and autocorrelation consistent standard mean revision n mean absolute revision mean revision Rbar st dev Rbar HAC formula mean squared revision relative mean absolute revision t stat t crit Is mean revision significant Correlation between L and P Min Revision Max Revision Range L gt P Sign L Sign P Variance of L Variance of P UM UR UD mean absolute revision MAR D3 L P ty LR n n relative mean absolute revision RMAR l mean squared revision MSR n ooo st dev Rbar HAC Formula n t n 2L t n n R deviation of mean revision is defined as the square root of 16 gt L PY R n t 74 1 1871 0
15. R In cell A2 of the Levels worksheet write the name of the country and of the indicator used for the analysis changes made in this worksheet will automatically be applied to all the other worksheets OECD Microsoft Excel template_triangle_monthly_old_xts la x a Fie Edit View Insert Format Tools Data Window Help j r hel F X D Een BAY amp amp Fi o Z im B 5 BB P 2 Arial 8 B Z U E B 18 58 E E 2 A H22 X Ke G EER L M N nM a E C ee E a lee elza 13 i 8 S Ie wW W W W W o wW W W W W o ATT I DE 2A n a l a NAA ioa Fe 17RA 131 134 nan 134 M 4 gt ah Leyels MoM Growth Rates Revisions to MoM Growth Rates YoY Growth Rates Revisions to YoY Growth Rates Ready Step 5 DELETING MISSING VALUES The two dots meaning missing values for the variable chosen need to be erased in order for the summary information at the top of the worksheet to be correct Select the pasted data and use the key Ctrl H to find and replace with blanks Type in the menu Find what and leave the menu Replace with empty Click on Replace All Microsoft Excel template_triangle_monthly_old xls la x File Edit View Insert Format Tools Data Window Help Type a question for help 5 fa es Ce hee Slav amp m S z 2 l W 5 s B I 2 Mee Arial 8 B Z U Bs BS t
16. User manual and pre programmed spreadsheets for performing revision analysis This document describes how to perform revision analysis using pre programmed template spreadsheets based on data extracted from the OECD Main Economic Indicators Original Release Data and Revisions Database Such database contains original data as published in successive monthly editions of the Main Economic Indicators MEI CD Rom from February 1999 for the following 21 key short term economic statistics GDP total constant prices GDP Private consumption expenditure constant prices GDP Government consumption expenditure constant prices GDP Gross fixed capital formation constant prices GDP Exports of goods and services constant prices GDP Imports of goods and services constant prices GDP Total current prices GDP Total implicit price deflator Index of industrial production Retail trade volume Production in construction Composite leading indicator trend restored Composite leading indicator 6 month rate of change annualised Standardised unemployment rate Civilian employment Hourly earnings manufacturing Consumer price index Monetary aggregates broad money International trade in goods exports International trade in goods imports and Balance of payments current account balance All pre programmed template spreadsheets can be downloaded via the links included in this document which describes in detail how to us
17. ating to period must not be filled otherwise this will affect the X Y worksheets calculation Microsoft Excel template_revisions_analysis_monthly_old xls a x File Edit Yiew Insert Format Tools Data Window Help Type a question for help Spo fa 2 8 Dees BAY amp Bee ST Y m Bxr BL5 Mwwm CB ee _ UST A Arial fa 53 HE E D A D50 A B J K L M N o P Q R S i a 1 Country indicator 2 Revisions spreadsheet Whole sample 3 country indicator Relating First 2 3 peepee to estimate months months a SoS Eoi ETDs f period P later later later later estimate ii of at 0 2 0 4 42 0 3 0 3 43 0 5 0 3 P 0 3 0 3 45 0 2 0 5 Pa 0 4 0 5 ar 0 6 0 5 as 0 3 0 6 aa 0 3 0 6 01 0 2 0 3 0 3 s2 Ad 0 6 53 0 6 0 2 54 0 3 0 5 55 0 5 0 2 56 06 06 57 0 2 0 2 s8 0 4 0 4 59 0 4 0 4 60 03 o1 et 03 0 6 Eel nz n1 of M 4 gt AA Revisions spreadsheet Summary statistics M2_P M3_P f M3_M2 Y1_P Y2_P4 L_P 2_Y1 Y1_M2 f tal of gt Ready NUM Step 3 NAMING THE REVISION ANALYSIS TEMPLATE Modify the title country indicator and country code in the upper left hand corner of Revisions spreadsheet accordingly to the country chosen and the 12 OECD variable selected changes to the country code and to the name of variables will automatically be applied to the summary statistics table and to the X_Y worksheets Step 4 D
18. blication on average Particular attention must be paid while inserting data here The Revision triangles template is therefore structured in 5 worksheets e LEVELS this worksheet contains the level estimates of the variable for the period in question The imported data should be put here and the values in the following worksheets will be automatically derived from the data contained in this worksheet e MoM GROWTH RATES this worksheet contains the month on previous month growth rates of the variable based on the level estimates or quarter on previous quarter growth rates in the case of quarterly data this worksheet is pre filled with formulas therefore will be automatically populated after having imported the data into the levels worksheet e REVISIONS TO MoM GROWTH RATES this worksheet calculates the difference between the month on previous month quarterly on previous quarter growth rates for the same reference period in two successive monthly editions of the MEI publication This worksheet is pre filled with formulas therefore will be automatically populated after having imported the data into the levels worksheet e YoY GROWTH RATES this worksheet contains the year on year growth rates of the variable based on the level estimates This worksheet is pre filled with formulas therefore will be automatically populated after having imported the data into the levels worksheet http stats oecd org mei default asp rev 1 http www oe
19. cd org dataoecd 48 20 36888799 pdf e REVISIONS TO YoY GROWTH RATES this worksheet calculates the difference between the year on year growth rates in the same way as described in the MoM growth rates above This worksheet is pre filled with formulas therefore will be automatically populated after having imported the data into the levels worksheet Note Each value in the lower block will appear in bold if it is different from the previous value in the same column this identifies the occurrence of revision made to the data point from one MEI edition to the other OECD 1 2 How to use the Revision triangles template Step 1 SELECT AND DOWNLOAD DATA Choose the country the variable and the period you want to perform revision analysis for and proceed downloading the data e g U S index of industrial production as published in the MEI CD Rom each month from February 1999 to March 2006 monthly frequency from the MEI Original Release Data and Revisions Database In order to analyse the data they need to be saved on the PC as an excel file This user guide explains how to download data in the correct format required The downloaded data should look as shown below Microsoft Excel DotStat_ExcelXP 2003_2007 8 3_17 49 58 367 1 xls x File Edit View Insert Format Tools Data Window Help Type a question For help X D Esaa BRAY amp BE SI O amp HE Sh l a ooo BB I 2 af 2 Arial 8 B Z U 23 BS x t 8 ce
20. e the templates to perform revisions analysis with reference to an example based on the monthly index of industrial production for the United States Please note users must follow the instructions provided below precisely when performing a revisions analysis otherwise it is almost certain that errors will be made i e don t try and take short cuts If users have their own source of historical vintage data this can also be used provided the data is formatted and treated as described below 1 Revision triangles template The first step consists in importing data to the Revision triangles template which can be accessed for monthly data at http www oecd org dataoecd 24 60 36789554 xls or for quarterly data at http www oecd org dataoecd 24 61 36789593 xls Prior to this the desired source data for which one wishes to perform the revisions analysis from must be downloaded in an Excel spreadsheet from the Main Economic Indicators Original Release Data and Revisions Database To download data from this database in the format required for performing revisions analysis as explained in this user guide see Downloading data user guide 1 1 Structure of a Revision triangles template The Revision triangles template contains data for e level estimates e month on previous month growth rates M M 1 1 or quarter on previous quarter growth rates Q Qt 1 1 in the case of quarterly data e year on year growth rates computed eith
21. er as a M y M 12 1 or Q Q 4 1 and e a corresponding revision worksheet for each period analysed monthly quarterly and yearly Each worksheet is divided into two blocks i For monthly source data the first block of six rows of each worksheet gives the reference period and summary values of the comparison i e first published estimates estimates published 2 months later 3 months later one year and 2 years after the first publication and the latest estimates For quarterly source data the values are first published estimates estimates published 5 months later one year and 2 years after the first publication and the latest estimates This block contains pre set formulas so that results are derived automatically from the data previously copied and pasted in the second block ii In the second block of data each row represents the month of publication in the MEI CD Rom as labelled in column A of the excel worksheet and contains the values of the variable that was published for each reference period at that time The data in this block should in theory look triangular i e one additional monthly data point of the series for each successive monthly publication in the MEI However they often take the form of a distorted triangle because of the periods in which more than one or no new data point were published Of course where the input data is of quarterly frequency a new data point is only likely to appear every third month of the MEI pu
22. he analysis this period may vary therefore some rows at the beginning and at the end of the table in each worksheet may be empty hence should be deleted This applies to both the Revision Spreadsheet and the X_Y worksheets Before deleting empty rows Microsoft Excel template_revisions_analysis_monthly_old xts lej xf File Edit View Insert Format Tools Data Window Help z o ed xX D W Sa aav ugm LY Y 2 21 mer L P 2 2 Arial 10 B Z U 22 28 BS x t 8 sete 8 D A A107 X 01 04 2006 A E amp E E D E F G H 1 J K m Mm o P a R g 1 United States Index of Industrial Production me S Calculations of summary statistics for the revisions analysis 3 us4_IIP Jan 33 feb 06 07 0 Q u 13 114 Copy 115 Paste 16 Total 7 657 Paste Special Insert Revision L P nz Delete a E Content 113 OA era Jan 99 feb 06 120 Eormat Cells 121 2 revision 122 Row Height pn Rbar 0 0 i 123 Hide formula 0 0 v 44 T dsheet Z Summary statistics M2_P 4M3_P 4 M3_M2 1_P Y2_P L_P Y2_Y1 4 1_M2 f gt Ready Unhide 13 Sum 4pr 59 NUM and after deleting empty rows Microsoft Excel template_revisions_analysis_monthly_old xlts 3 x File Edit View Insert Format Tools Data Window Help Pro fet XxX Deen 6 Ry t mee So 2l Z MBs QB EA Ms a x x Arial 10 B Z U SSeS x 83 FF n 2 A R97 Ke A 1 United States Inde
23. mats Main Relating PERTEN to es Comments C values and number Formats ae period value of Operation E 4 Fal None Multiply LP Add Divide v2 Y 2 Subtract Y1_PA2 Skip blanks V Transpose 13 26 27 _ 28 a oe ae ee ee we M 4 gt gt I Revisions spreadsheet Summary statistics 4 M2_P M3_P M3_M2 Y1_P v2_ PAL P Y2_Y1 4 Y1_Me2 lal gt Select destination and press ENTER or choose Paste NUM gt COPYING TRANSPOSE DATA INTO REVISION SPREADSHEET LATEST ESTIMATE SEPARATELY Copy and paste the Latest Estimate row in the worksheet MoM QoQ Growth Rates or YoY Growth Rates from the Revision triangles template to the last column of the Revisions Spreadsheet labelled Latest estimate This also needs to be transposed Click on the right hand mouse button and select Paste special Check the boxes Value and Transpose and click Okay Microsoft Excel template_triangle_monthly_old_xls la x Bg Eile Edit View Insert Format Tools Data Window Help e est he amp x De Meal S4 Rwy EA E RA E B 4 2 __ Zell Arial 7 B Z U 22 26 BS x 68 53 t te 8 DA E17 gt IF E10 INDIRECT ADDRESS MAX E12 E177 lt gt PROVV E12 E177 COLUMN E12 E177 4 E F G l J K E M N m E Q DE cut 2 MoM rates of changes Copy E Paste Paste Special Insert Copied Cells Delete 7 Clear Contents wa Insert Comment Pick From List 2 Hyperlink
24. must be copied and pasted to the relevant part of the Levels worksheet in the Revision triangles template Select the values and copy as shown below be careful to copy all the data till the end of the worksheet OECD Microsoft Excel DotStat_ExcelXP 2003_2007 8 3_17 49 58 367 1 xlts y gt Eile Edit wiew Insert Format Tools Data Window Help Type a question For help oso fe es De He SR BBY BREA YT E SL EL Me a ioo BB ee __ si ee Arial 5 B Z U CS Fe 134 2 A Dataset Main Economic i ll il if ih i Sess 68 98 cE te ee DAS E E G H l Indicators Original release data and revisions Country United States ariable Index of industrial production Frequency Monthly Edition February March April 1999 May 1999 June 1999 July 1999 4 amp ugust Septembe October November December January 1999 1999 1999 r 1999 1999 1999 1999 2000 4 134 2 134 133 6 133 86 133 6 sd 4115 7 iso 1163 116 3 1339 133 8 133 7 1337 115 6 115 6 _ se 115 6 117 2 117 2 Gs 134 134 133 9 d J 1158 1176 117 6 se 134 2 134 6 1165 1164 1181 ee S A 135 4 a17 116 9 116 8 116 8 116 8 118 4 118 4 Ee a ee eee cut 1171 1171 1171 T a e eS ee as Ger B 117 3 117 3 117 3 1173 119 4 119 4 EE EE Ea oe p 118 4 120 3 120 4 Se SS SSS Paste Special JS na _ 1203 120 6 ee EE EEE a Insert BET Ty 121 1216 E ae a hhh EEE ESE E
25. os o 22 par ive oe ioe e o os os oe or o os o o o o o pee os mw oo os re Ee 0 6 0 2 os 01 oF o2 07 0 4 0 6 0 2 0 6 0 6 05 0 7 0 4 0 5 Paste Special ee a a a a a E S T a E O S E E E E ana Ee a ee ee ee sy ee ee Sa eee ErTak tah San les See Clear Contents i ee ee ne Rs Se Ge Ge ee ee E T a ee E ees ss ees Fae Paes eee ia Insert Comment oe eo oo o1 oo 1E see ar ER Se ens es ee ee eee es oo oo oo o1 o1 oo 5 E E ee ees ee e eee eee eee eee oo oo oo oo oo oo Pick From List ae a S oo oo oo oo oo oo Hyperlink a es ee ee eee eee ee eee ee eee oo oo oo oo oo oo oo SS ae eae eee aes es ee ee ee ee 23 Feb 00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 ka mM o4 gt bt Levels MoM Growth Rates Revisions to MoM Growth Rates YoY Growth Rates Revisions to YoY Growth Rates lat gt Ready Sum 37 9 NUM gt MATCHING BEGINNING DATES AMONG THE TWO TEMPLATES Find the starting point of the time series in the Revisions spreadsheet of the Revision analysis template It should be the same as the starting point in the Growth rates worksheet In this example it is Jan 99 gt COPYING TRANSPOSE DATA INTO REVISION SPREADSHEET The copied data must be transposed before pasting because also here the rows and columns are inverted Click on the right hand mouse button and select Paste special Check the boxes
26. sions dataset values Column widths Formats Formulas and number formats Comments Yalues and number formats Country indicator Operation None Multiply Add Divide Subtract l Skip blanks V Transpose cot _ e tvermer oT ot ft ft tT ft ft ft NY EAM I A a a PR a E 12 Fabas CTC CC CC CC M ee es Be See ees eee eee ee ee 13 eee a CS GA PR mm Rens Ge Oe ee ee hn a i i i Gane ne Es 14 ee eS ey er Fae a Ean Pee a ees eet eee ees eee ee ee ee ee es ees 15 eT TE LS SS SS ST NS a a ES SE RE NE CE ES CL SS 16 anea ee ee tee Re See Gai eet Areal Geet feet Ee a ee eS en SS EE eee a ooo o o o T T T S S S S es SE EE LE CS SUR E E ABl aS a a a a Gee ee 2 ee ee ee ee 19 Ce SS as ae a a ee ee Es ee ee es es ee en en ee a o e A Sa a es ee ae aa a 21 ee Se a ee ee ee ee ee ee ee e B e E E a es es ee es es ee eee ee eS a Se es Se ee ee a a SS ee ee es ee ee ae Gee ee eee eee ee eee ee 24 e T eS See a Ss eS eS ee ee es ee 25 ee a a a Ea EE a E E a U H e St Ba Ge es ee ee es ee 26 Fe a eS a SS eS ee es ee Ey a a ees es ae ees 27 mao A E a E ET E E a a E en ee ee eS ae 28 dun a M o4 gt gt t Levels f MoM Growth Rates Revisions to MoM Growth Rates YoY Growth Rates Revisions to YoY Growth Rates Jal m Select destination and press ENTER or choose Paste NUM Step 4 NAMING THE INDICATO
27. to be cleared are placed in the first row of the following columns R t 1 R t 2 R t 1 Rbar R t 2 Rbar R t Rbar R t 1 Rbar and R t Rbar R t 2 Rbar For R t 2 R t 2 Rbar and R t Rbar R t 2 Rbar also the second row cells need to be cleared by formulas 14 OECD Microsoft Excel template_revisions_analysis_monthly_old xls m G x File Edit wiew Insert Format Tools Data Window Help pe a question For help amp x Ae F MEZ D Esa 42aY d uB J aov 2 il Me arm I 2 a n i E Fa 2 0 00 E 5 g Arial a 20 STREE ES SS ee Ss 3 oo 0 ES 2S iii D A H5 X A IF C5 AREF Ss ess E ee ee ee EAE G H I J K L m m m E a B Ea _1 United States Index of Industrial Production gt Calculations of summary statistics for the revisions analysis USA_IP Jan 99 feb 06 4 5 Jan 33 0 1 03 0 3 03 03 BEF I BEF 0 31 REF REF 0 097 REF REF 1 0 6 s Feb 39 01 0 2 0 2 03 0 2 ow 03 cut 031 REF 0 029 0 053 7 REF 1 1 7 Ma 39 01 0 4 0 3 0 4 03 01 02 0 17 0 31 0 065 0 044 0 080 1 1 s Apr 33 0 6 03 0 2 03 03 01 0 3 Copy 0 26 017 0 130 0 092 0 062 o 1 3 May 33 02 o5 0 3 05 03 01 0 0 36 0 26 0 100 0 114 0 081 1 1 10 Jun 39 0 2 03 0 2 0 2 0 2 0 0 oJ E Paste 0 32 0 36 0 021 0 046 0 052 1 1 n Jul 33 0 7 o5 0 2 05 0 2 0 0 02 ean 0 15 0 32 0 040 0 029 0 062 o 1 12 Aug 39 0 2 0 3 0 0 0 3 0 0 0 0 0 4 Sste 2beoS 0 20 015 0
28. to use the Revision analysis template Step 1 COPYING SUMMARY TABLES INTO REVISION SPREADSHEET The growth rates calculated in the Revision triangles template will be used in the analysis Copy the summary values 1 e the rows First Published Estimate 2 months later 3 months later 1 year later and 2 years later from the worksheet MoM or QoQ Growth Rates for analysis of revisions to month on previous month growth rates or YoY Growth Rates for analysis of revisions to year on year growth rates into the Revision Template spreadsheet When copying the summary tables the Latest estimate row needs to be copied separately because there is a hidden row not used in this step Be careful to copy all the data till the end of the worksheet A different spreadsheet must be used for the analysis of MoM Growth Rates or QoQ and YoY Growth Rates 9 OECD Microsoft Excel template_triangle_monthly_old_xts Ey Eile Edit view Insert Format Tools Data Window Help spo le ES Oe Mea Gary s BMB Slo Bx 4 2 Mm Bwm B 2 FZ Arial 7 B Z U S Elp cc t 08 ce ce DA Fe MoM Growth Rates lIES E z G H l J K S hw N O P Q R S IE w Vv vv ANa p OECD Miisions dataset United Stindex of Industrial Production gt MoM rates of changes as ites es ee fees ea ser ee es ee ee ee epee ee ae ee eee pa is e oo e oe e o ve o oe o o oe
29. x of Industrial Production z Calculations of summary statistics for the revisions analysis 3 UsS4_IIP Jan 99 feb 06 fl 101 Oct 05 102 Novw 05 103 Dec 05 104 Jan 06 105 Feb 06 106 hMlar 06 i 107 Total 7 657 1 631 0 869 Estimate published 1 ox First published gear later 2 estimate P Revision L P 108 a 103 Summ Statistics USA_IP 110 sample Jan 99 feb 06 111 n 83 0 112 mean absolute revision 0 2 13 mean revision Rbar 0 0 14 st dev Rbar HAC formula 0 0 115 mean squared revision 0 1 116 relative mean absolute revision 0 5 i t stat 0 7 113 t crit 2 0 113 Is mean revision significant NO 120 Correlation between L and P 0 8 121 Min Revision 0 8 122 Maz Revision 0 7 123 Range 1 5 Ready Step 6 DELETING FORMULAS WITH INVALID CELL REFERENCE ERRORS IN X_Y WORKSHEETS The X Y worksheet has some built in formulas allowing the calculation of the summary statistics table The majority of such formulas are taking the previous period as a reference 1 e R t 1 is taking revision at period t and revision at period t 1 therefore an error REF will appear in some cells of the first row of the spreadsheet where the formula is referring to some data that are truly missing 1f the reference period start in January 1999 the R t 1 will not be computed as December 1998 is a missing value The formulas need therefore to be erased so to allow the summary statistics to be computed properly Cells where formulas need
Download Pdf Manuals
Related Search