Use for loop to generate growth rate and extrapolate data

[Taz Raihan]

I have a dataset of the following sort:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 date long bns float bns_qrt_growth long(cbna fcib fgb)
"Mar-14" 176535310     . 11294717 35831053 22359818
"Jun-14" 190726980  8.04 10644840 35897166 23619625
"Sep-14" 192644381  1.01  9901093 37192064 23526022
"Dec-14" 194210805   .81 10754557 38620165 24288135
"Mar-15" 186943115 -3.74  9774468 40428726 25382380
"Jun-15" 198691679  6.28 13190912 43019852 26311049
"Sep-15" 214435176  7.92 13174967 44976924 26726495
"Dec-15" 212618211  -.85 13804100 50453410 26766775
"Mar-16" 220099378  3.52 12126162 54608312 29789672
"Jun-16" 244633857 11.15 11136283 51588411 32222677
"Sep-16" 237884451 -2.76 13277994 52869157 33116516
"Dec-16" 239354182   .62 13204974 51947427 33396243
"Mar-17" 243451013  1.71 12286569 54768652 32397767
"Jun-17" 258912892  6.35 14068066 58157548 35323039
"Sep-17" 262534320   1.4 15126217 58218143 39123263
"Dec-17" 259433254 -1.18 15284789 58249010 35861926
end

I calculated the quarterly growth rates of bns and put them under a new variable called bns_qrt. I want to do this for the rest of the variables [cbna, fcib, fgb] using a for loop as I have a lot more variables than these in my actual dataset. That's the first problem. To solve this problem, I was trying the following codes which resulted in an error - "f ambiguous abbreviation":

local banks cbna fcib fgb

local i = 1

foreach f of local banks {

        gen var`i' `f'_qrt_growth = (f - f[_n-1])*100/f[_n-1]

        local ++i

}


The second problem is, after calculating the quarterly growth rates, I want to divide these growth rates by 3 and then extrapolate monthly values for bns. For example, for June 2014, the bns growth rate is 8.04 which divided by 3 is 2.68. Now, for April 2014, the bns will be calculated as 176535310*(1+.0268).

If anybody could give some idea about how to go about it, that would be highly appreciated. I hope I am not asking for too much. Thanks.

[Taz Raihan]

Ok...solved the first problem using the following code. Still thinking about the second problem.

foreach var of varlist bns-fgb {

  generate `var'_qrt_growth = (`var' - `var'[_n-1])*100/`var'[_n-1]

}

[William Lisowski]

Thank you for the clear data example. The sample code below starts with your data and accomplishes what I believe you want, although not following precisely what you describe. In particular, your calculations of growth do not serve your purpose: converting a quarterly growth rate to monthly requires taking the third root (raising to the 1/3 power) rather than dividing by 3. And you don't want to be multiplying the numbers you are working with by 100 for your calculations - you can create a different variable for presentation.

generate datem = monthly(date,"M20Y")
format datem %tmMon-YY
order datem

drop date bns_qrt_growth

tsset datem, monthly delta(3)
foreach var of varlist bns-fgb {
    generate `var'_qg = `var'/L.`var'
    generate `var'_mg = F.`var'_qg^(1/3)
    format `var'_qg `var'_mg %9.4f
    }

tsset datem, monthly delta(1)
tsfill
foreach var of varlist bns-fgb {
    replace `var'_mg = L.`var'_mg if `var'_mg==.
    generate `var'_rate = `var'_mg*100 - 100
    format `var'_rate %9.2f
    generate double `var'_new = `var'
    replace `var'_new = L.`var'_new * `var'_mg if `var'_new==.
    format `var' `var'_new %14.0fc
    }
list datem bns*, clean noobs

. list datem bns*, clean noobs

     datem           bns   bns_qg   bns_mg   bns_rate       bns_new  
    Mar-14   176,535,310        .   1.0261       2.61   176,535,310  
    Apr-14             .        .   1.0261       2.61   181,144,488  
    May-14             .        .   1.0261       2.61   185,874,007  
    Jun-14   190,726,980   1.0804   1.0033       0.33   190,726,980  
    Jul-14             .        .   1.0033       0.33   191,363,986  
    Aug-14             .        .   1.0033       0.33   192,003,121  
    Sep-14   192,644,381   1.0101   1.0027       0.27   192,644,381  
    Oct-14             .        .   1.0027       0.27   193,165,112  
    Nov-14             .        .   1.0027       0.27   193,687,251  
    Dec-14   194,210,805   1.0081   0.9874      -1.26   194,210,805  
    Jan-15             .        .   0.9874      -1.26   191,757,381  
    Feb-15             .        .   0.9874      -1.26   189,334,951  
    Mar-15   186,943,115   0.9626   1.0205       2.05   186,943,115  
    Apr-15             .        .   1.0205       2.05   190,780,007  
    May-15             .        .   1.0205       2.05   194,695,649  
    Jun-15   198,691,679   1.0628   1.0257       2.57   198,691,679  
    Jul-15             .        .   1.0257       2.57   203,806,695  
    Aug-15             .        .   1.0257       2.57   209,053,390  
    Sep-15   214,435,176   1.0792   0.9972      -0.28   214,435,176  
    Oct-15             .        .   0.9972      -0.28   213,827,807  
    Nov-15             .        .   0.9972      -0.28   213,222,158  
    Dec-15   212,618,211   0.9915   1.0116       1.16   212,618,211  
    Jan-16             .        .   1.0116       1.16   215,083,243  
    Feb-16             .        .   1.0116       1.16   217,576,853  
    Mar-16   220,099,378   1.0352   1.0359       3.59   220,099,378  
    Apr-16             .        .   1.0359       3.59   227,991,184  
    May-16             .        .   1.0359       3.59   236,165,957  
    Jun-16   244,633,857   1.1115   0.9907      -0.93   244,633,857  
    Jul-16             .        .   0.9907      -0.93   242,363,036  
    Aug-16             .        .   0.9907      -0.93   240,113,294  
    Sep-16   237,884,451   0.9724   1.0021       0.21   237,884,451  
    Oct-16             .        .   1.0021       0.21   238,373,372  
    Nov-16             .        .   1.0021       0.21   238,863,298  
    Dec-16   239,354,182   1.0062   1.0057       0.57   239,354,182  
    Jan-17             .        .   1.0057       0.57   240,712,079  
    Feb-17             .        .   1.0057       0.57   242,077,680  
    Mar-17   243,451,013   1.0171   1.0207       2.07   243,451,013  
    Apr-17             .        .   1.0207       2.07   248,499,556  
    May-17             .        .   1.0207       2.07   253,652,793  
    Jun-17   258,912,892   1.0635   1.0046       0.46   258,912,892  
    Jul-17             .        .   1.0046       0.46   260,114,460  
    Aug-17             .        .   1.0046       0.46   261,321,603  
    Sep-17   262,534,320   1.0140   0.9960      -0.40   262,534,320  
    Oct-17             .        .   0.9960      -0.40   261,496,543  
    Nov-17             .        .   0.9960      -0.40   260,462,868  
    Dec-17   259,433,254   0.9882   0.9960      -0.40   259,433,254