Troubles with rownumb() in code

[Bernard Njindan]

Hello stata users, I am having troubles with rownumb() in my code. Below is the code and data.

Code:

 version 10.1
drop _all
set memory 512m
set more off
set matsize 800

local outdir "E:\Today\"

cd "E:\Today\"


******************* load macro data into matrix ************************

*import delimited E:\Today\data.txt, clear 

insheet using data.txt, clear

mkmat year
mkmat p
mkmat cpi
mkmat yrret

local myrs = rowsof(yrret)
local rowyrs ""

forvalues i=1/`myrs' {
   local addyr = year[`i',1]
   local rowyrs "`rowyrs' `addyr'"
   }
matrix rownames p = `rowyrs'
matrix rownames cpi = `rowyrs'
matrix rownames yrret = `rowyrs'
global yroffset = year[`myrs',1]-`myrs'
matrix yrs = (1955,1960,1962,1963,1964,1967,1968,1969,1970,1971,1977,1983,1986,1989,1992,1995,1998,2001,2004,2007)
global nyrs = colsof(yrs) 

qui gen f = 0
qui gen w = 0
qui gen lret = . 
qui gen yri = rownumb(yrret,string(year)) 
forvalues i=1/136 {
  quietly replace lret = yrret[yri-`i',1] if yri > `i'  
  qui replace f = f + lret if yri > `i'
  qui replace w = w + 1 if yri > `i'
  } 
qui gen retall = f/w 
drop yri
drop f
drop w
drop lret

Error message using rownumb():

. qui gen yri = rownumb(yrret,string(year)) 
type mismatch
r(109);

Data:

* Example generated by -dataex-. To    install:    ssc    install    dataex
clear
input int year float(p cpi yrret)
1871 4.6916666 12.400645 .3783405
1872  5.029167  12.92396 .3891352
1873  4.801667 12.678156 .3787354
1874      4.57 11.940774 .3827223
1875    4.4475  11.26684 .3947424
1876 4.0608335  10.50566 .3865376
1877  3.135833  10.11715 .3099522
1878  3.383333 8.6423855 .3914814
1879    4.1225 8.5234585 .4836652
1880  5.209167  9.538342 .5461291
1881  6.254167  9.776216 .6397329
1882       5.9 10.307447 .5724017
1883  5.634167  9.562134 .5892165
1884 4.7408333  8.816821 .5377033
1885  4.596667  8.087381 .5683751
1886  5.364167  7.754368 .6917607
1887  5.534167  8.039808 .6883456
1888  5.204167 8.1825285 .6360096
1889  5.323333  7.738511 .6879015
1890  5.269166  7.794014  .676053
1891  5.028333  7.770226 .6471283
1892      5.55  7.270711 .7633366
1893     4.775  7.445147 .6413574
1894  4.386667  6.660196 .6586393
1895     4.525  6.802913 .6651562
1896  4.233333  6.461975 .6551145
1897 4.4508333   6.49369 .6854089
1898    5.0525  6.747412 .7488056
1899  6.288333   7.28657 .8630032
1900    6.1475   7.80987  .787145
1901    7.8425  7.690938 1.019707
1902  8.416667  8.182522 1.028615
1903  7.211667  8.285602 .8703854
1904  7.049167  8.277667 .8515885
1905  8.985833  8.356963 1.075251
1906  9.621667  8.571018 1.122582
1907      7.84  9.070535 .8643371
1908  7.775833  8.737544 .8899336
1909    9.7125  9.435274 1.029382
1910  9.351666  9.768285 .9573499
1911     9.235  9.007118   1.0253
1912     9.535  9.601766 .9930465
1913    8.5075  9.883333 .8607926
1914     7.945 10.016667  .793178
1915     8.305 10.108333 .8215994
1916  9.466667 10.883333 .8698316
1917     8.495    12.825 .6623782
1918  7.539167 15.041667 .5012189
1919    8.7825 17.333332 .5066827
1920    7.9775  20.04167 .3980457
end

Suggestions will be very much appreciated.

[Nick Cox]

I think you need to explain what you're trying to do. That is a fair amount of code for people to read and understand. On the face of it you have a few time series for a century and more and my wild guess is that you have some simple data manipulations to do. So what are they?

[Bernard Njindan]

Thank you, Nick. You are right. I am trying to construct a simple moving average stock returns index (the above) and later a weighted average stock returns with different weighting parameters. I took the code from here, which would have been quite easier to understand if their data were available. Below is the full data.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year float(p cpi yrret)
1871 4.6916666 12.400645 .3783405
1872  5.029167  12.92396 .3891352
1873  4.801667 12.678156 .3787354
1874      4.57 11.940774 .3827223
1875    4.4475  11.26684 .3947424
1876 4.0608335  10.50566 .3865376
1877  3.135833  10.11715 .3099522
1878  3.383333 8.6423855 .3914814
1879    4.1225 8.5234585 .4836652
1880  5.209167  9.538342 .5461291
1881  6.254167  9.776216 .6397329
1882       5.9 10.307447 .5724017
1883  5.634167  9.562134 .5892165
1884 4.7408333  8.816821 .5377033
1885  4.596667  8.087381 .5683751
1886  5.364167  7.754368 .6917607
1887  5.534167  8.039808 .6883456
1888  5.204167 8.1825285 .6360096
1889  5.323333  7.738511 .6879015
1890  5.269166  7.794014  .676053
1891  5.028333  7.770226 .6471283
1892      5.55  7.270711 .7633366
1893     4.775  7.445147 .6413574
1894  4.386667  6.660196 .6586393
1895     4.525  6.802913 .6651562
1896  4.233333  6.461975 .6551145
1897 4.4508333   6.49369 .6854089
1898    5.0525  6.747412 .7488056
1899  6.288333   7.28657 .8630032
1900    6.1475   7.80987  .787145
1901    7.8425  7.690938 1.019707
1902  8.416667  8.182522 1.028615
1903  7.211667  8.285602 .8703854
1904  7.049167  8.277667 .8515885
1905  8.985833  8.356963 1.075251
1906  9.621667  8.571018 1.122582
1907      7.84  9.070535 .8643371
1908  7.775833  8.737544 .8899336
1909    9.7125  9.435274 1.029382
1910  9.351666  9.768285 .9573499
1911     9.235  9.007118   1.0253
1912     9.535  9.601766 .9930465
1913    8.5075  9.883333 .8607926
1914     7.945 10.016667  .793178
1915     8.305 10.108333 .8215994
1916  9.466667 10.883333 .8698316
1917     8.495    12.825 .6623782
1918  7.539167 15.041667 .5012189
1919    8.7825 17.333332 .5066827
1920    7.9775  20.04167 .3980457
1921    6.8575     17.85 .3841737
1922  8.410833     16.75 .5021393
1923  8.573334     17.05 .5028348
1924  9.045834    17.125 .5282239
1925     11.15 17.541668 .6356295
1926 12.586667      17.7 .7111111
1927 15.343333 17.358334 .8839175
1928 19.953333 17.158333 1.162895
1929 26.018333 17.158333 1.516367
1930 21.026667      16.7 1.259082
1931 13.659167 15.208333  .898137
1932  6.928333 13.641667 .5078803
1933  8.958333 12.933333 .6926546
1934  9.844167 13.383333 .7355542
1935 10.599167    13.725 .7722526
1936 15.468333 13.866667 1.115505
1937 15.405833 14.383333 1.071089
1938 11.488333 14.091667 .8152573
1939 12.061667 13.908333 .8672259
1940 11.020833 14.008333 .7867341
1941  9.824166    14.725  .667176
1942  8.673333 16.333332 .5310204
1943 11.504167 17.308332 .6646606
1944 12.468333 17.591667 .7087636
1945 15.155833 17.991667 .8423807
1946 17.080833 19.516666 .8751922
1947 15.165833    22.325 .6793206
1948   15.5325  24.04167 .6460658
1949     15.23  23.80833  .639692
1950      18.4  24.06667 .7645429
1951    22.335  25.95833 .8604173
1952   24.4975     26.55  .922693
1953 24.731667 26.766666 .9239726
1954 29.689167     26.85 1.105742
1955  40.49333    26.775 1.512356
1956  46.62333  27.18333 1.715144
1957     44.38  28.09167 1.579828
1958  46.23833 28.858334 1.602252
1959  57.37917     29.15  1.96841
1960     55.85    29.575 1.888419
1961   66.2725 29.891666 2.217089
1962   62.3825     30.25 2.062231
1963    69.865    30.625 2.281306
1964  81.36833 31.016666 2.623374
1965     88.17 31.508333 2.798307
1966  85.25667 32.458332  2.62665
1967  91.92833 33.358334 2.755783
1968  98.69417 34.783333 2.837398
1969     97.84 36.683334 2.667151
1970     83.22    38.825 2.143464
1971  98.28333  40.49167 2.427248
1972 109.20833  41.81667 2.611598
1973 107.42333      44.4 2.419445
1974   82.5525  49.30833  1.67421
1975  86.15583  53.81667 1.600914
1976 102.02167  56.90833 1.792737
1977     98.21  60.60833 1.620404
1978     96.02  65.23333 1.471947
1979  103.0225    72.575 1.419531
1980 118.78333  82.40833   1.4414
1981 128.04167    90.925 1.408212
1982   119.725      96.5 1.240674
1983   160.425      99.6 1.610693
1984 160.46667 103.88333 1.544682
1985    186.85 107.56667 1.737062
1986 236.35834 109.60833  2.15639
1987 286.84167   113.625 2.524459
1988 265.78333 118.25833 2.247481
1989  322.8333 123.96667 2.604195
1990  334.5875 130.65833 2.560782
1991  376.1775 136.19167 2.762118
1992  415.7442 140.31667 2.962899
1993  451.4067 144.45833 3.124823
1994  460.3292   148.225 3.105611
1995  541.6383 152.38333 3.554446
1996  670.8283    156.85 4.276878
1997  872.6733 160.51666 5.436653
1998 1084.3108 163.00833 6.651874
1999 1326.0608   166.575 7.960743
2000 1427.0074     172.2  8.28692
2001 1192.0782 177.06667  6.73237
2002    995.63   179.875 5.535121
2003  963.6891 183.95833 5.238627
2004 1130.5475 188.88333 5.985427
2005 1207.0608 195.29167 6.180811
2006   1310.67 201.59167 6.501608
2007 1476.6333  207.3424 7.121714
2008 1220.8883  215.3025 5.670572
2009  946.7358   214.537 4.412926
2010 1139.3075  218.0555 5.224851
2011   1268.89 224.93916 5.641037
2012 1379.5634  229.5939  6.00871
2013 1642.5117 232.95708 7.050705
2014 1930.6675 236.73618 8.155355
2015 2061.1992   237.017 8.696419
2016 2091.8442 240.00717 8.715757
2017 2448.2175  245.1196 9.987849
2018   2744.68 251.13863 10.92894
2019   2584.96 251.40276 10.28215
end

[Nick Cox]

Sorry, that is just a series of links on a page so far as I can see. tssmooth is, I guess, the place to start.

This is what I get as a basic plot:

That makes me wonder whether you should be smoothing the logarithms, not the data.

* ssc install multiline
multiline yrret cpi p year , xla(1880(20)2020) xtitle("")

[Bernard Njindan]

Thank you, Nick. Again, you are right about the need to smooth the data. I intend smoothing the data in the finalized work. Pardon me for not being clear enough. For now, I want to be able to figure out why I get the type mismatch r(109) error, when applying the line:

 qui gen yri = rownumb(yrret, string(year))

The code works up until the following lines:

qui gen yri = rownumb(yrret,string(year))  
 forvalues i=1/136 {   quietly replace lret = yrret[yri-`i',1] if yri > `i'     qui replace f = f + lret if yri > `i'   qui replace w = w + 1 if yri > `i'   }  qui gen retall = f/w  drop yri drop f drop w drop lret

Sorry for my previous messy link to the original code. The working link is here.

[Braulio Santos]

As you have a variable named yrret, you have to tell Stata that yrret is a matrix.
The code

qui gen yri = rownumb(matrix(yrret), string(year))

works.

[Nick Cox]

My gut reaction is that you're trying to program something quite simple in a long-winded way. As before, you've not told us what it is.

You may well be in trouble using the same names for matrices and variables and expecting Stata to work out which you intend. That is just a guess.

Sorry, but in this particular case knowing what you want to do is the missing incentive to study your code in detail.

[Sven-Kristjan Bormann]

You probably have to modify the code from

qui gen yri = rownumb(yrret,string(year))

to

qui gen yri = _n

The logic is that the string function -string()- takes a number as its input. The original code just tries to convert the string "string" into a string, while a number was expected.
The original code intends to write the row number for each year into a new variable. If the number of rows is equal to the number of observations in the dataset, then it is enough to create a variable which records the observation number. This is exactly what my change does.
Let me know if I understood something wrong.

[Bernard Njindan]

Thank you, Nick. Sorry for missing these details. I am trying to construct an index by following the formula in the jpeg index attached. The authors provide the code for doing that. However, their data is not available for me to follow. Therefore, I used my data (raw) to see whether I can produce crude results first. Below is the portion of their code that I am interested in (the full code is in the attachment). Thank you, Braulio Santos. Your suggestion works.

**** create simple moving average stock returns ****

qui gen f = 0
qui gen w = 0
qui gen lret = . 
qui gen yri = rownumb(yrret,string(year)) 
forvalues i=1/136 {
  quietly replace lret = yrret[yri-`i',1] if yri > `i'  
  qui replace f = f + lret if yri > `i'
  qui replace w = w + 1 if yri > `i'
  } 
qui gen retall = f/w 
drop yri
drop f
drop w
drop lret


forvalues j=1/4 {
  qui gen f`j' = 0
  qui gen w`j' = 0
}
qui gen lret = . 
qui gen yri = rownumb(yrret,string(year)) 
forvalues i=1/79 {
     qui replace lret = yrret[yri-`i',1]
     qui replace f1 = f1 + lret if age/3 > `i'
     qui replace w1 = w1 + 1 if age/3 > `i'
     qui replace f2 = f2 + lret if (age/3 <= `i' & age*(2/3) > `i')
     qui replace w2 = w2 + 1 if (age/3 <= `i' & age*(2/3) > `i')
     qui replace f3 = f3 + lret if (age*(2/3) <= `i' & age > `i')
     qui replace w3 = w3 + 1 if (age*(2/3) <= `i' & age > `i')
     qui replace f4 = f4 + lret if (age < `i'+21 & age > `i')  
     qui replace w4 = w4 + 1 if (age < `i'+21 & age > `i')
     }
qui gen retma1 = f1/w1 
qui gen retma2 = f2/w2
qui gen retma3 = f3/w3
qui gen retform = f4/w4
drop yri
drop f1 f2 f3 f4
drop w1 w2 w3 w4
drop lret



forvalues j=1/4 {
  qui gen f`j' = 0
  qui gen w`j' = 0
}
qui gen lret = . 
qui gen yri = rownumb(yrret,string(year)) 
forvalues i=1/79 {
     qui replace lret = yrbret[yri-`i',1]
     qui replace f1 = f1 + lret if age/3 > `i'
     qui replace w1 = w1 + 1 if age/3 > `i'
     qui replace f2 = f2 + lret if (age/3 <= `i' & age*(2/3) > `i')
     qui replace w2 = w2 + 1 if (age/3 <= `i' & age*(2/3) > `i')
     qui replace f3 = f3 + lret if (age*(2/3) <= `i' & age > `i')
     qui replace w3 = w3 + 1 if (age*(2/3) <= `i' & age > `i')
     qui replace f4 = f4 + lret if (age < `i'+21 & age > `i')  
     qui replace w4 = w4 + 1 if (age < `i'+21 & age > `i')
     }
qui gen bretma1 = f1/w1 
qui gen bretma2 = f2/w2
qui gen bretma3 = f3/w3
qui gen bretform = f4/w4
drop yri
drop f1 f2 f3 f4
drop w1 w2 w3 w4
drop lret


forvalues j=1/4 {
  qui gen f`j' = 0
  qui gen w`j' = 0
}
qui gen lret = . 
qui gen yri = rownumb(yrret,string(year)) 
forvalues i=1/79 {
     qui replace lret = yrret[yri-`i',1]-yrbret[yri-`i',1] 
     qui replace f1 = f1 + lret if age/3 > `i'
     qui replace w1 = w1 + 1 if age/3 > `i'
     qui replace f2 = f2 + lret if (age/3 <= `i' & age*(2/3) > `i')
     qui replace w2 = w2 + 1 if (age/3 <= `i' & age*(2/3) > `i')
     qui replace f3 = f3 + lret if (age*(2/3) <= `i' & age > `i')
     qui replace w3 = w3 + 1 if (age*(2/3) <= `i' & age > `i')
     qui replace f4 = f4 + lret if (age < `i'+21 & age > `i')  
     qui replace w4 = w4 + 1 if (age < `i'+21 & age > `i')
     }
qui gen dretma1 = f1/w1 
qui gen dretma2 = f2/w2
qui gen dretma3 = f3/w3
qui gen dretform = f4/w4
drop yri
drop f1 f2 f3 f4
drop w1 w2 w3 w4
drop lret



**** create weighted average stock returns with different weighting parameters ****

local k1 1
local k2 1.433
local k3 1.325
local k4 1.166
local k5 1.50
forvalues j=1/5 {
   qui gen f`j' = 0
   qui gen w`j' = 0
   qui gen v`j' = 0
   }
quietly gen lret = . 
quietly gen yri = rownumb(yrret,string(year)) 
forvalues i=1/79 {
     qui replace lret = yrret[yri-`i',1] if age > `i'
     forvalues j=1/5 {
       qui replace f`j' = f`j' + lret*((age-`i')/age)^`k`j'' if age > `i'
       qui replace v`j' = v`j' + (lret^2)*((age-`i')/age)^`k`j'' if age > `i'
       qui replace w`j' = w`j' + ((age-`i')/age)^`k`j'' if age > `i'
     }
}
qui gen retave1 = f1/w1
qui gen retave1433 = f2/w2
qui gen retave1325 = f3/w3
qui gen retave1166 = f4/w4
qui gen retave150 = f5/w5
qui gen ret2ave1 = sqrt(v1/w1-(f1/w1)^2)
qui gen ret2ave1433 = sqrt(v2/w2-(f2/w2)^2)
qui gen ret2ave1325 = sqrt(v3/w3-(f3/w3)^2)
qui gen ret2ave1166 = sqrt(v4/w4-(f4/w4)^2)
qui gen ret2ave150 = sqrt(v5/w5-(f5/w5)^2)

drop f1 f2 f3 f4 f5
drop w1 w2 w3 w4 w5
drop v1 v2 v3 v4
drop lret 
drop yri

[Nick Cox]

I'd define the weights in a new variable and then add the product of weights and past values using a cumulative sum. (The sum of anything over t < time <= T is the cumulative sum to T minus the cumulative sum to t.) No need to loop.

[Bernard Njindan]

Thank you, Sven-Kristjan. Yes, it appears that is what the code does. I will try your suggestion and see whether it is consistent with Braulio's. I will post the outcome here. Many thanks for your contributions.

[Bernard Njindan]

Thank you, Nick, for your helpful suggestions. Your approach seems simpler. I will try that and post any challenges that I encounter.