Reshaping Datastream Data

[Aleksandar Nogovic]

Hello community.
first of all I would like to thank you for the inclusion in this really instructive and interesting forum.

However, I have a request for which I hope for your help.
Within the framework of a scientific work, I am trying to convert a dataset resulting from a datastream query (Thomson Reuters) into a form suitable for STATA. My goal is to subsequently form momentum portfolios based on this datastream.

I have already been able to read some information from the other topics.
I have also tried the 'reshape' code, but without success.

I would like to perform the following steps on the data set:

1. Delete missing variables (an ERROR or an M as variable name indicate this) completely from the data set. Only companies beginning with the first two ISIN-letters 'DE' are to be examined.

2. Convert the data, which is currently available in a wide format, into an appropriate long format.

3. Some of the companies (starting with DE in the ISIN) have not existed since the beginning of my investigation period (31.01.1985), but were listed on the stock exchange later. The missing values of such companies are marked with an 'NA'.
These 'NA' values should not be taken into account in the later momentum calculation for a certain period of time.

I still have a relatively general question for this purpose:

I knitted the data query via Thomson Reuters in such a way that I had one variable output per Excel table for all companies.
For example, in Table 1, all returns are data for the companies studied. Table 2 shows the market capitalizations of the companies, et cetera.
Can I later combine the STATA adjustments to the variables in each table into one (.dta) file to perform the calculations that go beyond the Momentum method through a common document?

A total of 1206 companies are examined in 396 periods.

Grateful,
Aleks

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date str17 DE000A1EWWW0RIU double DE0006483001RIU str38 M str17 DE0005552004RIU double(DE0008430026RIU DE0005439004RIU) str17 DE0005810055RIU double DE000ENAG999RIU str17 DE0006231004RIU str38 AD str17 NL0012169213RIU
 9162 "NA"  227.72 "$$ER: 2380,NO DATA IN REQUESTED PERIOD" "NA"  457.41 100.38 "NA" 151.13 "NA" "$$ER: 2380,NO DATA IN REQUESTED PERIOD" "NA"
 9190 "NA"  227.07 ""                                       "NA"  450.63  91.23 "NA" 137.93 "NA" ""                                       "NA"
 9219 "NA"  244.18 ""                                       "NA"  448.46 112.58 "NA" 162.84 "NA" ""                                       "NA"
 9251 "NA"  249.89 ""                                       "NA"  541.92  110.5 "NA" 165.43 "NA" ""                                       "NA"
 9282 "NA"  268.94 ""                                       "NA"  600.84 117.76 "NA" 179.05 "NA" ""                                       "NA"
 9310 "NA"  318.37 ""                                       "NA"  812.85  132.3 "NA" 206.11 "NA" ""                                       "NA"
 9343 "NA"  343.77 ""                                       "NA"   765.4  128.9 "NA" 227.43 "NA" ""                                       "NA"
 9373 "NA"  373.31 ""                                       "NA"  875.95 143.61 "NA" 240.41 "NA" ""                                       "NA"
 9404 "NA"  404.92 ""                                       "NA"  859.55 145.18 "NA" 288.62 "NA" ""                                       "NA"
 9435 "NA"  471.43 ""                                       "NA" 1120.21 166.05 "NA" 301.89 "NA" ""                                       "NA"
 9464 "NA"  457.38 ""                                       "NA" 1141.26 162.11 "NA" 309.62 "NA" ""                                       "NA"
 9496 "NA"   496.7 ""                                       "NA" 1603.12  175.2 "NA" 350.14 "NA" ""                                       "NA"
 9527 "NA"  532.92 ""                                       "NA" 1532.97 209.57 "NA" 335.92 "NA" ""                                       "NA"
 9555 "NA"  535.19 ""                                       "NA" 1688.76 244.34 "NA" 365.06 "NA" ""                                       "NA"
 9586 "NA"  575.05 ""                                       "NA" 1884.37 254.82 "NA" 382.69 "NA" ""                                       "NA"
 9616 "NA"  709.26 ""                                       "NA" 2339.66 295.28 "NA" 453.25 "NA" ""                                       "NA"
 9646 "NA"  614.52 ""                                       "NA" 1918.61 274.35 "NA"    355 "NA" ""                                       "NA"
 9677 "NA"  634.09 ""                                       "NA" 2051.52 323.27 "NA"    360 "NA" ""                                       "NA"
 9708 "NA"  661.66 ""                                       "NA" 1732.45 358.57 "NA" 354.29 "NA" ""                                       "NA"
 9737 "NA"  762.82 ""                                       "NA" 2126.21 432.14 "NA" 431.18 "NA" ""                                       "NA"
 9769 "NA"  795.32 ""                                       "NA" 1858.45 411.37 "NA" 424.14 "NA" ""                                       "NA"
 9800 "NA"  788.42 ""                                       "NA"  1863.1  413.1 "NA" 404.18 "NA" ""                                       "NA"
 9828 "NA"  832.13 ""                                       "NA" 2004.87 486.21 "NA" 431.36 "NA" ""                                       "NA"
 9861 "NA"  760.88 ""                                       "NA" 2187.46  473.3 "NA" 464.55 "NA" ""                                       "NA"
 9891 "NA"  718.59 ""                                       "NA" 1820.77 441.11 "NA" 446.67 "NA" ""                                       "NA"
 9919 "NA"  683.18 ""                                       "NA" 1483.71 444.08 "NA" 447.74 "NA" ""                                       "NA"
 9951 "NA"  747.72 ""                                       "NA"  1718.5 479.23 "NA" 446.73 "NA" ""                                       "NA"
 9981 "NA"   796.1 ""                                       "NA" 1510.83 469.11 "NA"  466.7 "NA" ""                                       "NA"
10010 "NA"  764.86 ""                                       "NA" 1479.36 485.94 "NA" 497.23 "NA" ""                                       "NA"
10042 "NA"  824.76 ""                                       "NA" 1627.24 486.79 "NA" 518.59 "NA" ""                                       "NA"
10073 "NA"  840.44 ""                                       "NA" 1727.01 508.94 "NA"  518.2 "NA" ""                                       "NA"
10104 "NA"  872.57 ""                                       "NA" 1789.58 541.81 "NA" 541.74 "NA" ""                                       "NA"
10134 "NA"  833.14 ""                                       "NA" 1696.76 477.03 "NA" 519.32 "NA" ""                                       "NA"
10164 "NA"  703.08 ""                                       "NA" 1264.08 390.36 "NA" 508.83 "NA" ""                                       "NA"
10195 "NA"  627.86 ""                                       "NA" 1016.76 364.86 "NA" 516.98 "NA" ""                                       "NA"
10226 "NA"  687.97 ""                                       "NA" 1150.39 351.87 "NA" 514.44 "NA" ""                                       "NA"
10255 "NA"  653.45 ""                                       "NA" 1075.51 273.24 "NA" 443.87 "NA" ""                                       "NA"
10286 "NA"  694.36 ""                                       "NA" 1292.95 374.09 "NA" 480.38 "NA" ""                                       "NA"
10317 "NA"  740.79 ""                                       "NA" 1276.39 393.76 "NA" 482.31 "NA" ""                                       "NA"
10346 "NA"  786.01 ""                                       "NA" 1221.33  390.3 "NA" 466.97 "NA" ""                                       "NA"
10378 "NA"  800.59 ""                                       "NA" 1141.64 377.41 "NA" 450.42 "NA" ""                                       "NA"
10408 "NA"  788.95 ""                                       "NA" 1217.73 374.71 "NA" 467.02 "NA" ""                                       "NA"
10437 "NA"  759.26 ""                                       "NA" 1266.09 374.81 "NA" 433.04 "NA" ""                                       "NA"
10470 "NA"  755.97 ""                                       "NA" 1298.38 350.03 "NA" 433.04 "NA" ""                                       "NA"
10500 "NA"  780.55 ""                                       "NA" 1383.08  376.1 "NA" 452.09 "NA" ""                                       "NA"
10531 "NA"  844.65 ""                                       "NA" 1508.54 412.45 "NA" 477.42 "NA" ""                                       "NA"
10561 "NA"  860.59 ""                                       "NA" 1432.27 427.12 "NA"  494.2 "NA" ""                                       "NA"
10591 "NA"   922.9 ""                                       "NA" 1456.79 419.74 "NA"  487.4 "NA" ""                                       "NA"
10623 "NA"  867.98 ""                                       "NA" 1401.16 387.53 "NA" 477.75 "NA" ""                                       "NA"
10651 "NA"  909.37 ""                                       "NA" 1399.28 357.75 "NA" 512.22 "NA" ""                                       "NA"
10682 "NA"   857.2 ""                                       "NA" 1235.76 358.88 "NA" 496.49 "NA" ""                                       "NA"
10710 "NA"  883.66 ""                                       "NA" 1348.31 386.43 "NA" 511.67 "NA" ""                                       "NA"
10743 "NA"  831.86 ""                                       "NA" 1327.51 392.56 "NA" 489.86 "NA" ""                                       "NA"
10773 "NA"  886.26 ""                                       "NA" 1585.89 437.58 "NA" 551.99 "NA" ""                                       "NA"
10804 "NA"  931.84 ""                                       "NA" 1989.65 476.07 "NA" 570.68 "NA" ""                                       "NA"
10835 "NA"  921.95 ""                                       "NA" 1938.15 449.71 "NA" 593.39 "NA" ""                                       "NA"
10864 "NA"  984.84 ""                                       "NA" 2156.51 466.14 "NA" 607.29 "NA" ""                                       "NA"
10896 "NA"  910.18 ""                                       "NA" 1936.26 517.45 "NA" 589.35 "NA" ""                                       "NA"
10926 "NA" 1021.59 ""                                       "NA" 2131.58 516.25 "NA" 663.83 "NA" ""                                       "NA"
10955 "NA" 1175.34 ""                                       "NA"  2518.6 535.72 "NA" 751.61 "NA" ""                                       "NA"
10988 "NA" 1199.57 ""                                       "NA" 2477.49 558.67 "NA" 864.81 "NA" ""                                       "NA"
11016 "NA" 1161.01 ""                                       "NA" 2172.89 480.99 "NA" 869.91 "NA" ""                                       "NA"
11046 "NA" 1362.01 ""                                       "NA"    2394 503.66 "NA" 927.97 "NA" ""                                       "NA"
11077 "NA" 1281.27 ""                                       "NA" 2090.96 481.08 "NA" 888.09 "NA" ""                                       "NA"
11108 "NA" 1351.59 ""                                       "NA" 2265.94 490.98 "NA" 842.08 "NA" ""                                       "NA"
11137 "NA"  1411.1 ""                                       "NA" 2415.28 516.99 "NA" 882.37 "NA" ""                                       "NA"
11169 "NA" 1520.84 ""                                       "NA" 3017.17 560.55 "NA" 897.68 "NA" ""                                       "NA"
11200 "NA" 1282.61 ""                                       "NA" 2613.67 557.28 "NA" 767.74 "NA" ""                                       "NA"
11228 "NA" 1067.44 ""                                       "NA"  2032.5 512.66 "NA" 639.43 "NA" ""                                       "NA"
11261 "NA" 1256.38 ""                                       "NA" 2561.77 438.04 "NA" 695.71 "NA" ""                                       "NA"
11291 "NA" 1257.43 ""                                       "NA" 2686.52 372.58 "NA" 685.84 "NA" ""                                       "NA"
11322 "NA" 1242.99 ""                                       "NA" 2719.82 400.06 "NA" 676.98 "NA" ""                                       "NA"
11353 "NA" 1213.32 ""                                       "NA" 2827.97 429.91 "NA" 715.68 "NA" ""                                       "NA"
11381 "NA" 1248.71 ""                                       "NA" 2900.16 457.82 "NA" 748.72 "NA" ""                                       "NA"
11410 "NA" 1102.34 ""                                       "NA" 2452.07 386.32 "NA" 637.57 "NA" ""                                       "NA"
11442 "NA" 1126.59 ""                                       "NA" 2424.71 355.88 "NA" 664.94 "NA" ""                                       "NA"
11473 "NA" 1191.36 ""                                       "NA" 2699.15  313.1 "NA" 745.69 "NA" ""                                       "NA"
11501 "NA" 1099.24 ""                                       "NA" 2460.45 316.86 "NA" 644.27 "NA" ""                                       "NA"
11534 "NA" 1107.62 ""                                       "NA" 2670.26 325.57 "NA" 686.32 "NA" ""                                       "NA"
11564 "NA" 1140.36 ""                                       "NA" 2481.61 363.26 "NA" 716.73 "NA" ""                                       "NA"
11595 "NA" 1191.38 ""                                       "NA" 2460.94 383.31 "NA" 740.27 "NA" ""                                       "NA"
11626 "NA" 1093.72 ""                                       "NA" 2428.06 378.79 "NA" 733.51 "NA" ""                                       "NA"
11655 "NA" 1071.37 ""                                       "NA" 2505.74 373.12 "NA"  770.9 "NA" ""                                       "NA"
11687 "NA" 1089.52 ""                                       "NA" 2734.07 402.78 "NA" 829.11 "NA" ""                                       "NA"
11718 "NA" 1151.63 ""                                       "NA" 2946.24 411.19 "NA" 803.89 "NA" ""                                       "NA"
11746 "NA" 1223.74 ""                                       "NA" 2946.49 435.14 "NA" 806.29 "NA" ""                                       "NA"
11778 "NA" 1219.76 ""                                       "NA" 2933.37 445.22 "NA" 823.69 "NA" ""                                       "NA"
11808 "NA" 1242.55 ""                                       "NA" 2790.24 489.27 "NA" 835.51 "NA" ""                                       "NA"
11837 "NA" 1342.93 ""                                       "NA" 2824.13 499.34 "NA" 899.31 "NA" ""                                       "NA"
11869 "NA" 1347.59 ""                                       "NA" 2974.62 503.73 "NA"  940.6 "NA" ""                                       "NA"
11900 "NA" 1263.85 ""                                       "NA" 2740.38 521.23 "NA" 924.52 "NA" ""                                       "NA"
11931 "NA" 1323.39 ""                                       "NA" 2729.65 510.91 "NA" 948.16 "NA" ""                                       "NA"
11961 "NA" 1222.76 ""                                       "NA" 2972.14 436.44 "NA" 916.15 "NA" ""                                       "NA"
11991 "NA" 1087.47 ""                                       "NA" 2873.68 357.77 "NA" 810.64 "NA" ""                                       "NA"
12022 "NA" 1080.93 ""                                       "NA" 2769.47 376.37 "NA" 796.25 "NA" ""                                       "NA"
12053 "NA" 1057.55 ""                                       "NA" 2879.97  360.7 "NA" 810.43 "NA" ""                                       "NA"
12082 "NA" 1123.04 ""                                       "NA" 2949.73 385.16 "NA" 828.35 "NA" ""                                       "NA"
12110 "NA" 1160.59 ""                                       "NA" 3173.31    396 "NA" 854.27 "NA" ""                                       "NA"
12143 "NA"  1196.4 ""                                       "NA" 3266.08 389.82 "NA" 856.74 "NA" ""                                       "NA"
12173 "NA" 1114.12 ""                                       "NA" 3433.67 357.21 "NA"  880.8 "NA" ""                                       "NA"
end
format %tdnn/dd/CCYY Date

[William Lisowski]

Welcome to Statalist. I believe the following Stata (note: not STATA) code will start you in a useful direction.

keep Date DE*
ds DE*, has(type string)
local vars `r(varlist)'
foreach v of varlist `vars' {
    rename `v' `v'_s
    quietly generate `v' = cond(`v'_s=="NA",.n,real(`v'_s))
    drop `v'_s
    }
rename (DE*) (val=)
reshape long val, i(Date) j(ISIN) string
rename val returns
sort ISIN Date

I would apply this code to each dataset, changing "returns" to whatever the data represents in that dataset, and then merging the resulting datasets by Date and ISIN.

[Aleksandar Nogovic]

Many thanks for the very helpful code.

I still have a problem with the issued ISIN's.
The ISIN is a combination of letters and numbers consisting of 12 characters. Due to the datastream query, the abbreviation of the variable name (RIU) has been appended to the ISIN's range.

My goal is to shorten the ISIN's to 12 characters so that the "RIU"'s are omitted. This is necessary, so that I can unite the data of the individual Excel tables.

I tried the following code:

. foreach var of varlist * {
  2. renvarlab ISIN , trim(12)
  3. }

But he tells me that no renaming is necessary, because all new names correspond to the old names.

Did I not specify the code correctly?

[William Lisowski]

One small addition to the code in post #2 will reduce the ISIN variable created by the reshape command to the first 12 characters.

keep Date DE*
ds DE*, has(type string)
local vars `r(varlist)'
foreach v of varlist `vars' {
    rename `v' `v'_s
    quietly generate `v' = cond(`v'_s=="NA",.n,real(`v'_s))
    drop `v'_s
    }
rename (DE*) (val=)
reshape long val, i(Date) j(ISIN) string
rename val returns
replace ISIN = substr(ISIN,1,12)
sort ISIN Date

[Aleksandar Nogovic]

Once again I have to thank you very much. The next step I want to take is to build Dezil portfolios based on the last observed values of the respective calendar year for the variable "MV". So I want to compare the variable "MV" across all ISIN's every 31 December and then build the Dezil portfolios by size. Then I want to assign the Dezil portfolios their corresponding returns so that I can calculate the returns of the weighted portfolios. I have already worked with the following code to do this:

 bys ISIN month(`12'): astile gen MV10=MV, nq(10)

However, I got the following error message:

 genes MV10 invalid name              
st_addvar(): 3300 argument out of range                
fastile(): - function returned error                  
<istmt>: - function returned error

Then, when the Dezil portfolios stand, I want to assign the Dezil portfolios their associated returns so that I can calculate the return on the weighted portfolios. The weighting should be value-based. The return of each of the companies should therefore be in relation to the "MV" of the Dezil portfolio. I used the following code to calculate my returns:

 gen time=_n  
bysort ISIN(Date): gen Return = (RI[_n]-RI[_n-1])/RI[_n-1]

My .dta document looks like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date str15 ISIN double(RI MV) byte _merge float(month time Return)
 9162 "DE0001218063" .n .n 3  1   1 .
 9190 "DE0001218063" .n .n 3  2   2 .
 9219 "DE0001218063" .n .n 3  3   3 .
 9251 "DE0001218063" .n .n 3  4   4 .
 9282 "DE0001218063" .n .n 3  5   5 .
 9310 "DE0001218063" .n .n 3  6   6 .
 9343 "DE0001218063" .n .n 3  7   7 .
 9373 "DE0001218063" .n .n 3  8   8 .
 9404 "DE0001218063" .n .n 3  9   9 .
 9435 "DE0001218063" .n .n 3 10  10 .
 9464 "DE0001218063" .n .n 3 11  11 .
 9496 "DE0001218063" .n .n 3 12  12 .
 9527 "DE0001218063" .n .n 3  1  13 .
 9555 "DE0001218063" .n .n 3  2  14 .
 9586 "DE0001218063" .n .n 3  3  15 .
 9616 "DE0001218063" .n .n 3  4  16 .
 9646 "DE0001218063" .n .n 3  5  17 .
 9677 "DE0001218063" .n .n 3  6  18 .
 9708 "DE0001218063" .n .n 3  7  19 .
 9737 "DE0001218063" .n .n 3  8  20 .
 9769 "DE0001218063" .n .n 3  9  21 .
 9800 "DE0001218063" .n .n 3 10  22 .
 9828 "DE0001218063" .n .n 3 11  23 .
 9861 "DE0001218063" .n .n 3 12  24 .
 9891 "DE0001218063" .n .n 3  1  25 .
 9919 "DE0001218063" .n .n 3  2  26 .
 9951 "DE0001218063" .n .n 3  3  27 .
 9981 "DE0001218063" .n .n 3  4  28 .
10010 "DE0001218063" .n .n 3  5  29 .
10042 "DE0001218063" .n .n 3  6  30 .
10073 "DE0001218063" .n .n 3  7  31 .
10104 "DE0001218063" .n .n 3  8  32 .
10134 "DE0001218063" .n .n 3  9  33 .
10164 "DE0001218063" .n .n 3 10  34 .
10195 "DE0001218063" .n .n 3 11  35 .
10226 "DE0001218063" .n .n 3 12  36 .
10255 "DE0001218063" .n .n 3  1  37 .
10286 "DE0001218063" .n .n 3  2  38 .
10317 "DE0001218063" .n .n 3  3  39 .
10346 "DE0001218063" .n .n 3  4  40 .
10378 "DE0001218063" .n .n 3  5  41 .
10408 "DE0001218063" .n .n 3  6  42 .
10437 "DE0001218063" .n .n 3  7  43 .
10470 "DE0001218063" .n .n 3  8  44 .
10500 "DE0001218063" .n .n 3  9  45 .
10531 "DE0001218063" .n .n 3 10  46 .
10561 "DE0001218063" .n .n 3 11  47 .
10591 "DE0001218063" .n .n 3 12  48 .
10623 "DE0001218063" .n .n 3  1  49 .
10651 "DE0001218063" .n .n 3  2  50 .
10682 "DE0001218063" .n .n 3  3  51 .
10710 "DE0001218063" .n .n 3  4  52 .
10743 "DE0001218063" .n .n 3  5  53 .
10773 "DE0001218063" .n .n 3  6  54 .
10804 "DE0001218063" .n .n 3  7  55 .
10835 "DE0001218063" .n .n 3  8  56 .
10864 "DE0001218063" .n .n 3  9  57 .
10896 "DE0001218063" .n .n 3 10  58 .
10926 "DE0001218063" .n .n 3 11  59 .
10955 "DE0001218063" .n .n 3 12  60 .
10988 "DE0001218063" .n .n 3  1  61 .
11016 "DE0001218063" .n .n 3  2  62 .
11046 "DE0001218063" .n .n 3  3  63 .
11077 "DE0001218063" .n .n 3  4  64 .
11108 "DE0001218063" .n .n 3  5  65 .
11137 "DE0001218063" .n .n 3  6  66 .
11169 "DE0001218063" .n .n 3  7  67 .
11200 "DE0001218063" .n .n 3  8  68 .
11228 "DE0001218063" .n .n 3  9  69 .
11261 "DE0001218063" .n .n 3 10  70 .
11291 "DE0001218063" .n .n 3 11  71 .
11322 "DE0001218063" .n .n 3 12  72 .
11353 "DE0001218063" .n .n 3  1  73 .
11381 "DE0001218063" .n .n 3  2  74 .
11410 "DE0001218063" .n .n 3  3  75 .
11442 "DE0001218063" .n .n 3  4  76 .
11473 "DE0001218063" .n .n 3  5  77 .
11501 "DE0001218063" .n .n 3  6  78 .
11534 "DE0001218063" .n .n 3  7  79 .
11564 "DE0001218063" .n .n 3  8  80 .
11595 "DE0001218063" .n .n 3  9  81 .
11626 "DE0001218063" .n .n 3 10  82 .
11655 "DE0001218063" .n .n 3 11  83 .
11687 "DE0001218063" .n .n 3 12  84 .
11718 "DE0001218063" .n .n 3  1  85 .
11746 "DE0001218063" .n .n 3  2  86 .
11778 "DE0001218063" .n .n 3  3  87 .
11808 "DE0001218063" .n .n 3  4  88 .
11837 "DE0001218063" .n .n 3  5  89 .
11869 "DE0001218063" .n .n 3  6  90 .
11900 "DE0001218063" .n .n 3  7  91 .
11931 "DE0001218063" .n .n 3  8  92 .
11961 "DE0001218063" .n .n 3  9  93 .
11991 "DE0001218063" .n .n 3 10  94 .
12022 "DE0001218063" .n .n 3 11  95 .
12053 "DE0001218063" .n .n 3 12  96 .
12082 "DE0001218063" .n .n 3  1  97 .
12110 "DE0001218063" .n .n 3  2  98 .
12143 "DE0001218063" .n .n 3  3  99 .
12173 "DE0001218063" .n .n 3  4 100 .
end
format %tdnn/dd/CCYY Date
label values _merge _merge
label def _merge 3 "matched (3)", modify

I am very sorry if I should spam, but I am extremely new to the area of Stata and try to get the best possible result in the shortest possible time. Thank you for your efforts.

[William Lisowski]

Your question is no longer concerned with reshaping Datastream data,

Is it about constructing "Decile Portfolios"? If so, entering that phrase into the search box at the top of the page will find much other discussion of that topic on this forum.

If that does not help you, you should post this question as a new topic with an informative title, probably including the phrase decile portfolio, that will draw members with a background that can help you, who may not be following this topic.

A word of warning about the Statalist forum software: you cannot post your new topic by copying from your post #5 and pasting it into a new post. All the things in code blocks will be not helpful, as shown by the few lines in blue below copied from your post #5. You will need to create the code blocks again.

Best wishes. Sorry I cannot go the next step, but it will really work better if you can get help from someone who can better advise on technique, either in a previous topic revealed by the search, or in response to a new topic.

calculate my returns:

Code:
gen time=_n bysort ISIN(Date): gen Return = (RI[_n]-RI[_n-1])/RI[_n-1]
My .dta document looks like this: