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
Code:
* 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
0 Response to Reshaping Datastream Data
Post a Comment