Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 2
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Removing unnecessary observations

    Hello,

    I have data containing on retail investor trading (see below). The data set consists of trades at a discount broker form 1991 to 1996. In the first column you find the investor id, in the second column the date of transaction, the third column indicates the amount of stocks bought or sold (negative sign means sale of stock) and column 6 is an stock identifier. The other columns are not relevant for my problem.
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input long investor float(dates stocks price volume) str8 cusip long v2 str8 stringdates str1 v4 float v6 str4 v8 str3 v9 float v10 str9 v11 str11 v12 str9 v13 str6 v14 byte v15
2290 13366       100   54.375    5437.5 "06071610" 1263307 "19960805" "B"    55 "BIXN" "BA"  6 "COM" "54.37500" "060716107" "" .
2290 13408       200       25      5000 "17958410" 1518348 "19960916" "B"  80.1 "TTXX" "TB" 25 "COM" "25.00000" "179584107" "" .
2290 13382       200       21      4200 "86707110" 3433611 "19960821" "B" 75.35 "TTZZ" "TB" 25 "COM" "21.00000" "867071102" "" .
2290 13437       500       10      5000 "80850988" 9299931 "19961015" "B"     0 "DABD" "BA"  6 "MFS" "10.00000" "808509889" "" .
3206 11501      1000     2.25      2250 "02365010" 1101005 "19910628" "B"    90 "DDCP" "BA"  6 "COM" "2.25000"  "023650104" "" .
3206 12404     -1000   1.0312   -1031.2 "02365010" 1101005 "19931217" "S"    90 "AZQD" "BA"  6 "COM" "1.03120"  "023650104" "" .
3206 12977      -550   13.875  -7631.25 "96040210" 3814009 "19950713" "S" 91.75 "JWXX" "PC" 25 "COM" "13.87500" "960402105" "" .
3206 13457   257.732     19.4      5000 "08188080" 9212226 "19961104" "B"     0 "JOXX" "WW"  6 "MFF" "19.40000" "081880809" "" .
3206 11610    345.96 21.30001   7368.95 "47102340" 9240020 "19911015" "B"    15 "MFTM" "DE"  6 "MFC" "21.30001" "471023408" "" .
3206 13083  -369.231 29.87999 -11032.62 "47102340" 9240020 "19951027" "S" 66.33 "JWXX" "PC"  6 "MFA" "29.87999" "471023408" "" .
3206 11967   452.489 11.04999      5000 "77957Q10" 9268090 "19921006" "B"    30 "DDDB" "BA"  6 "MFC" "11.04999" "77957Q103" "" .
3206 11609   -122.52 34.82999  -4267.37 "81116510" 9272050 "19911014" "S"    29 "MFTM" "DE"  6 "MFC" "34.82999" "811165109" "" .
3206 11609  -194.999 16.27998  -3174.58 "81622110" 9273205 "19911014" "S"    29 "MFTM" "DE"  6 "MFC" "16.27998" "816221105" "" .
3206 11967   152.486  32.7899      5000 "92190830" 9289572 "19921006" "B"    30 "DDDB" "BA"  6 "MFC" "32.78990" "921908307" "" .
6002 13263       -12   43.875    -526.5 "03189710" 1008404 "19960424" "S"  35.1 "TTXX" "TB" 25 "COM" "43.87500" "031897101" "" .
6002 12177       100     16.5      1650 "02312710" 1098152 "19930504" "B"    55 "AZUD" "BA" 25 "COM" "16.50000" "023127103" "" .
6002 12257      -100    23.75     -2375 "02312710" 1098152 "19930723" "S"    55 "DDBK" "BA" 25 "COM" "23.75000" "023127103" "" .
6002 13439        50    19.75     987.5 "07251010" 1287879 "19961017" "B" 42.11 "TTXX" "TB"  2 "COM" "19.75000" "072510100" "" .
6002 13356       200    4.875       975 "41025210" 2134403 "19960726" "B" 41.92 "TTXX" "TB" 25 "COM" "4.87500"  "410252100" "" .
6002 12075      1000    1.125      1125 "50221010" 2404508 "19930122" "B"    90 "CHRT" "BR" 25 "COM" "1.12500"  "502210107" "" .
6002 12064       200     5.25      1050 "55261810" 2498081 "19930111" "B" 47.85 "CHJL" "BR" 25 "COM" "5.25000"  "552618100" "" .
6002 12528       200      5.5      1100 "55261810" 2498081 "19940420" "B"  48.7 "DDTR" "BA" 25 "COM" "5.50000"  "552618100" "" .
6002 13426       200     2.75       550 "55403410" 2501316 "19961004" "B" 35.42 "TTXX" "TB"  6 "COM" "2.75000"  "554034108" "" .
6002 12263       100   11.625    1162.5 "55917720" 2511500 "19930729" "B" 49.76 "DDPR" "BA" 25 "COM" "11.62500" "559177209" "" .
6002 12498      -100     16.5     -1650 "55917720" 2511500 "19940321" "S"    55 "BIIU" "BA"  2 "COM" "16.50000" "559177209" "" .
6002 12435       100    6.875     687.5 "57679810" 2554418 "19940117" "B" 41.69 "DDTP" "BA"  6 "COM" "6.87500"  "576798102" "" .
6002 12221       200     3.25       650 "59373610" 2602500 "19930617" "B" 41.05 "DDSH" "BA"  6 "COM" "3.25000"  "593736101" "" .
6002 13276      -200    1.625      -325 "59373610" 2602500 "19960507" "S"  35.1 "TTXX" "TB"  6 "COM" "1.62500"  "593736101" "" .
6002 13402       100    15.75      1575 "64121010" 2756430 "19960910" "B"  49.5 "TTCF" "TB"  6 "COM" "15.75000" "641210109" "" .
6002 11947      -100    10.75     -1075 "77310210" 3167145 "19920916" "S" 48.28 "CHNS" "BR" 25 "CEM" "10.75000" "773102108" "" .
6002 12058       100   18.625    1862.5 "81137170" 3256250 "19930105" "B"    55 "BIBC" "BA"  2 "COM" "18.62500" "811371707" "" .
6002 12088      -100    23.75     -2375 "81137170" 3256250 "19930204" "S"    55 "DDCC" "BA"  2 "COM" "23.75000" "811371707" "" .
6002 12092       100    8.625     862.5 "81090510" 3256880 "19930208" "B" 44.66 "DDAD" "BA" 25 "CEM" "8.62500"  "810905109" "" .
6002 13137      -100    11.25     -1125 "81090510" 3256880 "19951220" "S" 44.21 "TTXX" "TB" 25 "CEM" "11.25000" "810905109" "" .
6002 13408       200   4.5625     912.5 "87990540" 3495032 "19960916" "B" 40.96 "TTXX" "TB"  6 "COM" "4.56250"  "879905404" "" .
6002 13460       100     1.75       175 "87990540" 3495032 "19961107" "B"  35.1 "TTXX" "TB"  6 "COM" "1.75000"  "879905404" "" .
6002 13368 -6746.722        1  -6746.72 "31606710" 9230541 "19960807" "S"     0 "AYDY" "BA"  6 "MPM" "1.00000"  "316067107" "" .
6002 11948   -60.136 65.36999  -3931.09 "31618410" 9230555 "19920917" "S"    29 "MFSC" "DE"  6 "PFA" "65.36999" "316184100" "" .
6002 13356    53.433 36.70017      1961 "62838030" 9257430 "19960726" "B"    39 "BJDU" "BA"  6 "MFC" "36.70017" "628380305" "" .
6002 12667   226.655 11.02998      2500 "77064830" 9268407 "19940906" "B"    39 "OZBI" "BA"  6 "MFC" "11.02998" "770648301" "" .
6002 13156   116.279 10.32001      1200 "77064887" 9268408 "19960108" "B"     0 "OZTH" "BA"  6 "MFA" "10.32001" "770648871" "" .
6002 13346    62.814 11.94001       750 "77064887" 9268408 "19960716" "B"     0 "TTXX" "TB"  6 "MFA" "11.94001" "770648871" "" .
7109 12445       100       32      3200 "00157530" 1007205 "19940127" "B"  49.5 "TTXX" "TB"  3 "COM" "32.00000" "001575307" "" .
7109 12514      -100   34.375   -3437.5 "00157530" 1007205 "19940406" "S"  49.5 "TTXX" "TB" 25 "COM" "34.37500" "001575307" "" .
7109 11396       100    22.25      2225 "00190710" 1011850 "19910315" "B"    49 "IROM" "BR"  6 "COM" "22.25000" "001907104" "" .
7109 12403      -100     23.5     -2350 "00190710" 1011850 "19931216" "S"  49.5 "TTVT" "TB"  6 "COM" "23.50000" "001907104" "" .
7109 11553       100    21.75      2175 "01849010" 1076690 "19910819" "B"  49.5 "TTXX" "TB"  3 "COM" "21.75000" "018490102" "" .
7109 12087      -100     22.5     -2250 "01849010" 1076690 "19930203" "S"  49.5 "TTCF" "TB"  3 "COM" "22.50000" "018490102" "" .
7109 11354      -100   24.875   -2487.5 "03673210" 1177583 "19910201" "S"  44.1 "TTSY" "TB"  2 "COM" "24.87500" "036732105" "" .
7109 12380       100    20.75      2075 "03785710" 1180941 "19931123" "B"  49.5 "TTXX" "TB"  6 "COM" "20.75000" "037857109" "" .
7109 12516      -100    25.75     -2575 "03785710" 1180941 "19940408" "S"  49.5 "TTXX" "TB"  6 "COM" "25.75000" "037857109" "" .
7109 13269       100    26.25      2625 "03785710" 1180941 "19960430" "B"  49.5 "TTXX" "TB"  6 "COM" "26.25000" "037857109" "" .
7109 12697       150     16.5      2475 "04890310" 1224517 "19941006" "B" 64.87 "TTXX" "TB" 25 "COM" "16.50000" "048903108" "" .
7109 12228       100   20.875    2087.5 "09367610" 1324410 "19930624" "B"  49.5 "TTXX" "TB"  2 "COM" "20.87500" "093676104" "" .
7109 12472      -100    24.75     -2475 "09367610" 1324410 "19940223" "S"  49.5 "TTQF" "TB" 25 "COM" "24.75000" "093676104" "" .
7109 12134        50     65.5      3275 "10904310" 1356402 "19930322" "B"  49.5 "TTXX" "TB"  3 "COM" "65.50000" "109043109" "" .
7109 12589       -50     72.5     -3625 "10904310" 1356402 "19940620" "S"  49.5 "TTXX" "TB"  3 "COM" "72.50000" "109043109" "" .
7109 12697        50   68.375   3418.75 "10904310" 1356402 "19941006" "B"  49.5 "TTXX" "TB"  3 "COM" "68.37500" "109043109" "" .
7109 13443        50   62.875   3143.75 "12692010" 1397275 "19961021" "B"  49.5 "TTXX" "TB"  3 "COM" "62.87500" "126920107" "" .
7109 11340       100    27.25      2725 "20810810" 1596215 "19910118" "B"  44.1 "TTFK" "TB" 25 "COM" "27.25000" "208108100" "" .
7109 11353      -100    27.25     -2725 "20810810" 1596215 "19910131" "S"  44.1 "TTPS" "TB" 25 "COM" "27.25000" "208108100" "" .
7109 11416       100       24      2400 "20810810" 1596215 "19910404" "B"    49 "IROM" "BR"  3 "COM" "24.00000" "208108100" "" .
7109 12087      -100   23.375   -2337.5 "20810810" 1596215 "19930203" "S"  49.5 "TTCF" "TB" 25 "COM" "23.37500" "208108100" "" .
7109 12362       100   26.125    2612.5 "22237210" 1647318 "19931105" "B"  49.5 "TTXX" "TB"  3 "COM" "26.12500" "222372104" "" .
7109 12514      -100   23.625   -2362.5 "22237210" 1647318 "19940406" "S"  49.5 "TTXX" "TB" 25 "COM" "23.62500" "222372104" "" .
7109 13355       200    13.75      2750 "23281510" 1676215 "19960725" "B" 66.74 "TTBM" "TB"  6 "COM" "13.75000" "232815100" "" .
7109 11926       100       33      3300 "25084710" 1724800 "19920826" "B"  49.5 "TTXX" "TB" 25 "COM" "33.00000" "250847100" "" .
7109 12362        50   30.875   1543.75 "25084710" 1724800 "19931105" "B"  49.5 "TTXX" "TB"  6 "COM" "30.87500" "250847100" "" .
7109 12607      -150   25.125  -3768.75 "25084710" 1724800 "19940708" "S" 72.79 "TTXX" "TB"  2 "COM" "25.12500" "250847100" "" .
7109 11548       100     27.5      2750 "25283630" 1734460 "19910814" "B"  49.5 "TT08" "TB" 25 "COM" "27.50000" "252836309" "" .
7109 12137      -100   12.125   -1212.5 "25283630" 1734460 "19930325" "S" 45.55 "TTJD" "TB" 25 "COM" "12.12500" "252836309" "" .
7109 12095        50   48.375   2418.75 "38131710" 2071401 "19930211" "B"  49.5 "TTXX" "TB"  6 "COM" "48.37500" "381317106" "" .
7109 12163       -50   44.125  -2206.25 "38131710" 2071401 "19930420" "S"  49.5 "TTXM" "TB"  6 "COM" "44.12500" "381317106" "" .
7109 12715       200   13.125      2625 "38526910" 2083780 "19941024" "B" 65.99 "TTXX" "TB" 25 "COM" "13.12500" "385269105" "" .
7109 13464      -300   12.625   -3787.5 "38526910" 2083780 "19961111" "S"  72.9 "TTXX" "TB" 25 "COM" "12.62500" "385269105" "" .
7109 13031       100       44      4400 "40621610" 2127601 "19950905" "B"  49.5 "TTXX" "TB" 25 "COM" "44.00000" "406216101" "" .
7109 12430       100    28.75      2875 "44984210" 2232221 "19940112" "B"  49.5 "TTXX" "TB" 25 "MLP" "28.75000" "449842103" "" .
7109 13464      -100       13     -1300 "44984210" 2232221 "19961111" "S" 46.89 "TTXX" "TB"  3 "MLP" "13.00000" "449842103" "" .
7109 12290       100   40.875    4087.5 "50025510" 2394425 "19930825" "B"  49.5 "TTXX" "TB"  3 "COM" "40.87500" "500255104" "" .
7109 12409      -100   46.625   -4662.5 "50025510" 2394425 "19931222" "S"  49.5 "TTQR" "TB"  3 "COM" "46.62500" "500255104" "" .
7109 12179       100     28.5      2850 "53567810" 2468512 "19930506" "B"  49.5 "TTXX" "TB"  6 "COM" "28.50000" "535678106" "" .
7109 12575      -100     46.5     -4650 "53567810" 2468512 "19940606" "S"  49.5 "TTXX" "TB"  6 "COM" "46.50000" "535678106" "" .
7109 12738        50       51      2550 "53567810" 2468512 "19941116" "B"  49.5 "TTXX" "TB"  6 "COM" "51.00000" "535678106" "" .
7109 12817       -50     51.5     -2575 "53567810" 2468512 "19950203" "S"  49.5 "TTXX" "TB"  6 "COM" "51.50000" "535678106" "" .
7109 13234       100   40.375    4037.5 "53567810" 2468512 "19960326" "B"  49.5 "TTXX" "TB"  6 "COM" "40.37500" "535678106" "" .
7109 11728       100   19.625    1962.5 "59408710" 2603659 "19920210" "B"  49.5 "TTXX" "TB"  6 "COM" "19.62500" "594087108" "" .
7109 12319        50   36.625   1831.25 "59408710" 2603659 "19930923" "B"  49.5 "TTXX" "TB"  6 "COM" "36.62500" "594087108" "" .
7109 12395      -150    32.25   -4837.5 "59408710" 2603659 "19931208" "S" 74.25 "TTVT" "TB"  6 "COM" "32.25000" "594087108" "" .
7109 12282       100     21.5      2150 "72387710" 2983605 "19930817" "B"  49.5 "TTXX" "TB"  6 "COM" "21.50000" "723877106" "" .
7109 12418      -100    18.75     -1875 "72387710" 2983605 "19931231" "S"  49.5 "TTQH" "TB"  6 "COM" "18.75000" "723877106" "" .
7109 11354      -100     9.25      -925 "74838C10" 3071757 "19910201" "S" 36.72 "TTFK" "TB"  6 "COM" "9.25000"  "74838C106" "" .
7109 12249        50    30.75    1537.5 "76340810" 3144386 "19930715" "B"    55 "DDNC" "BA"  6 "COM" "30.75000" "763408101" "" .
7109 12624      -200    13.75     -2750 "76340810" 3144386 "19940725" "S" 66.74 "TTXX" "TB"  6 "COM" "13.75000" "763408101" "" .
7109 12627      -300       15     -4500 "76340810" 3144386 "19940728" "S" 77.13 "TTXX" "TB"  6 "COM" "15.00000" "763408101" "" .
7109 12697       100    20.25      2025 "77136710" 3166007 "19941006" "B"  49.5 "TTXX" "TB" 25 "COM" "20.25000" "771367109" "" .
7109 12472       100    27.25      2725 "80818810" 3241320 "19940223" "B"  49.5 "TTXX" "TB"  6 "COM" "27.25000" "808188106" "" .
7109 12514      -100    27.75     -2775 "80818810" 3241320 "19940406" "S"  49.5 "TTXX" "TB"  6 "COM" "27.75000" "808188106" "" .
7109 11396       100   36.875    3687.5 "86331410" 3421805 "19910315" "B"    49 "IROM" "BR"  2 "COM" "36.87500" "863314100" "" .
7109 12075      -200   19.875     -3975 "86331410" 3421805 "19930122" "S" 74.01 "TTTM" "TB"  2 "COM" "19.87500" "863314100" "" .
7109 12479       100       21      2100 "86693310" 3432082 "19940302" "B"  49.5 "TTXX" "TB"  2 "COM" "21.00000" "866933104" "" .
end
format %td dates
    I now want to remove all positions that were opened prior to 1991. What I am trying to do is to accumulate share positions for each investor-stock over time and if the accumulated numver of a stock's share becomes negative at any point in time, I want to remove the investor-stock from the sample.
    For example, in line 7: investor 3206 sold an amount of 550 stocks with an stock identifier of 96040210, because there is not a corresponding purchase of the stock, one can assume that the stock was purchased before 1991 and therefore I want to remove this observation.
    The same holds for line 9 and 10, there the accumulated share position is negative and therefore I want to drop line 9 and 10.
    I know that I can collapse the data set:
    Code:
    collapse (sum) stocks, by(investor cusip)
    but this does not really solve my issue.

    Maybe someone has an idea how to cope with this problem. Thanks in advance!
    Tags: None
  • #2
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input long investor float(dates stocks price volume) str8 cusip long v2 str8 stringdates str1 v4 float v6 str4 v8 str3 v9 float v10 str9 v11 str11 v12 str9 v13 str6 v14 byte v15
2290 13366       100   54.375    5437.5 "06071610" 1263307 "19960805" "B"    55 "BIXN" "BA"  6 "COM" "54.37500" "060716107" "" .
2290 13408       200       25      5000 "17958410" 1518348 "19960916" "B"  80.1 "TTXX" "TB" 25 "COM" "25.00000" "179584107" "" .
2290 13382       200       21      4200 "86707110" 3433611 "19960821" "B" 75.35 "TTZZ" "TB" 25 "COM" "21.00000" "867071102" "" .
2290 13437       500       10      5000 "80850988" 9299931 "19961015" "B"     0 "DABD" "BA"  6 "MFS" "10.00000" "808509889" "" .
3206 11501      1000     2.25      2250 "02365010" 1101005 "19910628" "B"    90 "DDCP" "BA"  6 "COM" "2.25000"  "023650104" "" .
3206 12404     -1000   1.0312   -1031.2 "02365010" 1101005 "19931217" "S"    90 "AZQD" "BA"  6 "COM" "1.03120"  "023650104" "" .
3206 12977      -550   13.875  -7631.25 "96040210" 3814009 "19950713" "S" 91.75 "JWXX" "PC" 25 "COM" "13.87500" "960402105" "" .
3206 13457   257.732     19.4      5000 "08188080" 9212226 "19961104" "B"     0 "JOXX" "WW"  6 "MFF" "19.40000" "081880809" "" .
3206 11610    345.96 21.30001   7368.95 "47102340" 9240020 "19911015" "B"    15 "MFTM" "DE"  6 "MFC" "21.30001" "471023408" "" .
3206 13083  -369.231 29.87999 -11032.62 "47102340" 9240020 "19951027" "S" 66.33 "JWXX" "PC"  6 "MFA" "29.87999" "471023408" "" .
3206 11967   452.489 11.04999      5000 "77957Q10" 9268090 "19921006" "B"    30 "DDDB" "BA"  6 "MFC" "11.04999" "77957Q103" "" .
3206 11609   -122.52 34.82999  -4267.37 "81116510" 9272050 "19911014" "S"    29 "MFTM" "DE"  6 "MFC" "34.82999" "811165109" "" .
3206 11609  -194.999 16.27998  -3174.58 "81622110" 9273205 "19911014" "S"    29 "MFTM" "DE"  6 "MFC" "16.27998" "816221105" "" .
3206 11967   152.486  32.7899      5000 "92190830" 9289572 "19921006" "B"    30 "DDDB" "BA"  6 "MFC" "32.78990" "921908307" "" .
6002 13263       -12   43.875    -526.5 "03189710" 1008404 "19960424" "S"  35.1 "TTXX" "TB" 25 "COM" "43.87500" "031897101" "" .
6002 12177       100     16.5      1650 "02312710" 1098152 "19930504" "B"    55 "AZUD" "BA" 25 "COM" "16.50000" "023127103" "" .
6002 12257      -100    23.75     -2375 "02312710" 1098152 "19930723" "S"    55 "DDBK" "BA" 25 "COM" "23.75000" "023127103" "" .
6002 13439        50    19.75     987.5 "07251010" 1287879 "19961017" "B" 42.11 "TTXX" "TB"  2 "COM" "19.75000" "072510100" "" .
6002 13356       200    4.875       975 "41025210" 2134403 "19960726" "B" 41.92 "TTXX" "TB" 25 "COM" "4.87500"  "410252100" "" .
6002 12075      1000    1.125      1125 "50221010" 2404508 "19930122" "B"    90 "CHRT" "BR" 25 "COM" "1.12500"  "502210107" "" .
6002 12064       200     5.25      1050 "55261810" 2498081 "19930111" "B" 47.85 "CHJL" "BR" 25 "COM" "5.25000"  "552618100" "" .
6002 12528       200      5.5      1100 "55261810" 2498081 "19940420" "B"  48.7 "DDTR" "BA" 25 "COM" "5.50000"  "552618100" "" .
6002 13426       200     2.75       550 "55403410" 2501316 "19961004" "B" 35.42 "TTXX" "TB"  6 "COM" "2.75000"  "554034108" "" .
6002 12263       100   11.625    1162.5 "55917720" 2511500 "19930729" "B" 49.76 "DDPR" "BA" 25 "COM" "11.62500" "559177209" "" .
6002 12498      -100     16.5     -1650 "55917720" 2511500 "19940321" "S"    55 "BIIU" "BA"  2 "COM" "16.50000" "559177209" "" .
6002 12435       100    6.875     687.5 "57679810" 2554418 "19940117" "B" 41.69 "DDTP" "BA"  6 "COM" "6.87500"  "576798102" "" .
6002 12221       200     3.25       650 "59373610" 2602500 "19930617" "B" 41.05 "DDSH" "BA"  6 "COM" "3.25000"  "593736101" "" .
6002 13276      -200    1.625      -325 "59373610" 2602500 "19960507" "S"  35.1 "TTXX" "TB"  6 "COM" "1.62500"  "593736101" "" .
6002 13402       100    15.75      1575 "64121010" 2756430 "19960910" "B"  49.5 "TTCF" "TB"  6 "COM" "15.75000" "641210109" "" .
6002 11947      -100    10.75     -1075 "77310210" 3167145 "19920916" "S" 48.28 "CHNS" "BR" 25 "CEM" "10.75000" "773102108" "" .
6002 12058       100   18.625    1862.5 "81137170" 3256250 "19930105" "B"    55 "BIBC" "BA"  2 "COM" "18.62500" "811371707" "" .
6002 12088      -100    23.75     -2375 "81137170" 3256250 "19930204" "S"    55 "DDCC" "BA"  2 "COM" "23.75000" "811371707" "" .
6002 12092       100    8.625     862.5 "81090510" 3256880 "19930208" "B" 44.66 "DDAD" "BA" 25 "CEM" "8.62500"  "810905109" "" .
6002 13137      -100    11.25     -1125 "81090510" 3256880 "19951220" "S" 44.21 "TTXX" "TB" 25 "CEM" "11.25000" "810905109" "" .
6002 13408       200   4.5625     912.5 "87990540" 3495032 "19960916" "B" 40.96 "TTXX" "TB"  6 "COM" "4.56250"  "879905404" "" .
6002 13460       100     1.75       175 "87990540" 3495032 "19961107" "B"  35.1 "TTXX" "TB"  6 "COM" "1.75000"  "879905404" "" .
6002 13368 -6746.722        1  -6746.72 "31606710" 9230541 "19960807" "S"     0 "AYDY" "BA"  6 "MPM" "1.00000"  "316067107" "" .
6002 11948   -60.136 65.36999  -3931.09 "31618410" 9230555 "19920917" "S"    29 "MFSC" "DE"  6 "PFA" "65.36999" "316184100" "" .
6002 13356    53.433 36.70017      1961 "62838030" 9257430 "19960726" "B"    39 "BJDU" "BA"  6 "MFC" "36.70017" "628380305" "" .
6002 12667   226.655 11.02998      2500 "77064830" 9268407 "19940906" "B"    39 "OZBI" "BA"  6 "MFC" "11.02998" "770648301" "" .
6002 13156   116.279 10.32001      1200 "77064887" 9268408 "19960108" "B"     0 "OZTH" "BA"  6 "MFA" "10.32001" "770648871" "" .
6002 13346    62.814 11.94001       750 "77064887" 9268408 "19960716" "B"     0 "TTXX" "TB"  6 "MFA" "11.94001" "770648871" "" .
7109 12445       100       32      3200 "00157530" 1007205 "19940127" "B"  49.5 "TTXX" "TB"  3 "COM" "32.00000" "001575307" "" .
7109 12514      -100   34.375   -3437.5 "00157530" 1007205 "19940406" "S"  49.5 "TTXX" "TB" 25 "COM" "34.37500" "001575307" "" .
7109 11396       100    22.25      2225 "00190710" 1011850 "19910315" "B"    49 "IROM" "BR"  6 "COM" "22.25000" "001907104" "" .
7109 12403      -100     23.5     -2350 "00190710" 1011850 "19931216" "S"  49.5 "TTVT" "TB"  6 "COM" "23.50000" "001907104" "" .
7109 11553       100    21.75      2175 "01849010" 1076690 "19910819" "B"  49.5 "TTXX" "TB"  3 "COM" "21.75000" "018490102" "" .
7109 12087      -100     22.5     -2250 "01849010" 1076690 "19930203" "S"  49.5 "TTCF" "TB"  3 "COM" "22.50000" "018490102" "" .
7109 11354      -100   24.875   -2487.5 "03673210" 1177583 "19910201" "S"  44.1 "TTSY" "TB"  2 "COM" "24.87500" "036732105" "" .
7109 12380       100    20.75      2075 "03785710" 1180941 "19931123" "B"  49.5 "TTXX" "TB"  6 "COM" "20.75000" "037857109" "" .
7109 12516      -100    25.75     -2575 "03785710" 1180941 "19940408" "S"  49.5 "TTXX" "TB"  6 "COM" "25.75000" "037857109" "" .
7109 13269       100    26.25      2625 "03785710" 1180941 "19960430" "B"  49.5 "TTXX" "TB"  6 "COM" "26.25000" "037857109" "" .
7109 12697       150     16.5      2475 "04890310" 1224517 "19941006" "B" 64.87 "TTXX" "TB" 25 "COM" "16.50000" "048903108" "" .
7109 12228       100   20.875    2087.5 "09367610" 1324410 "19930624" "B"  49.5 "TTXX" "TB"  2 "COM" "20.87500" "093676104" "" .
7109 12472      -100    24.75     -2475 "09367610" 1324410 "19940223" "S"  49.5 "TTQF" "TB" 25 "COM" "24.75000" "093676104" "" .
7109 12134        50     65.5      3275 "10904310" 1356402 "19930322" "B"  49.5 "TTXX" "TB"  3 "COM" "65.50000" "109043109" "" .
7109 12589       -50     72.5     -3625 "10904310" 1356402 "19940620" "S"  49.5 "TTXX" "TB"  3 "COM" "72.50000" "109043109" "" .
7109 12697        50   68.375   3418.75 "10904310" 1356402 "19941006" "B"  49.5 "TTXX" "TB"  3 "COM" "68.37500" "109043109" "" .
7109 13443        50   62.875   3143.75 "12692010" 1397275 "19961021" "B"  49.5 "TTXX" "TB"  3 "COM" "62.87500" "126920107" "" .
7109 11340       100    27.25      2725 "20810810" 1596215 "19910118" "B"  44.1 "TTFK" "TB" 25 "COM" "27.25000" "208108100" "" .
7109 11353      -100    27.25     -2725 "20810810" 1596215 "19910131" "S"  44.1 "TTPS" "TB" 25 "COM" "27.25000" "208108100" "" .
7109 11416       100       24      2400 "20810810" 1596215 "19910404" "B"    49 "IROM" "BR"  3 "COM" "24.00000" "208108100" "" .
7109 12087      -100   23.375   -2337.5 "20810810" 1596215 "19930203" "S"  49.5 "TTCF" "TB" 25 "COM" "23.37500" "208108100" "" .
7109 12362       100   26.125    2612.5 "22237210" 1647318 "19931105" "B"  49.5 "TTXX" "TB"  3 "COM" "26.12500" "222372104" "" .
7109 12514      -100   23.625   -2362.5 "22237210" 1647318 "19940406" "S"  49.5 "TTXX" "TB" 25 "COM" "23.62500" "222372104" "" .
7109 13355       200    13.75      2750 "23281510" 1676215 "19960725" "B" 66.74 "TTBM" "TB"  6 "COM" "13.75000" "232815100" "" .
7109 11926       100       33      3300 "25084710" 1724800 "19920826" "B"  49.5 "TTXX" "TB" 25 "COM" "33.00000" "250847100" "" .
7109 12362        50   30.875   1543.75 "25084710" 1724800 "19931105" "B"  49.5 "TTXX" "TB"  6 "COM" "30.87500" "250847100" "" .
7109 12607      -150   25.125  -3768.75 "25084710" 1724800 "19940708" "S" 72.79 "TTXX" "TB"  2 "COM" "25.12500" "250847100" "" .
7109 11548       100     27.5      2750 "25283630" 1734460 "19910814" "B"  49.5 "TT08" "TB" 25 "COM" "27.50000" "252836309" "" .
7109 12137      -100   12.125   -1212.5 "25283630" 1734460 "19930325" "S" 45.55 "TTJD" "TB" 25 "COM" "12.12500" "252836309" "" .
7109 12095        50   48.375   2418.75 "38131710" 2071401 "19930211" "B"  49.5 "TTXX" "TB"  6 "COM" "48.37500" "381317106" "" .
7109 12163       -50   44.125  -2206.25 "38131710" 2071401 "19930420" "S"  49.5 "TTXM" "TB"  6 "COM" "44.12500" "381317106" "" .
7109 12715       200   13.125      2625 "38526910" 2083780 "19941024" "B" 65.99 "TTXX" "TB" 25 "COM" "13.12500" "385269105" "" .
7109 13464      -300   12.625   -3787.5 "38526910" 2083780 "19961111" "S"  72.9 "TTXX" "TB" 25 "COM" "12.62500" "385269105" "" .
7109 13031       100       44      4400 "40621610" 2127601 "19950905" "B"  49.5 "TTXX" "TB" 25 "COM" "44.00000" "406216101" "" .
7109 12430       100    28.75      2875 "44984210" 2232221 "19940112" "B"  49.5 "TTXX" "TB" 25 "MLP" "28.75000" "449842103" "" .
7109 13464      -100       13     -1300 "44984210" 2232221 "19961111" "S" 46.89 "TTXX" "TB"  3 "MLP" "13.00000" "449842103" "" .
7109 12290       100   40.875    4087.5 "50025510" 2394425 "19930825" "B"  49.5 "TTXX" "TB"  3 "COM" "40.87500" "500255104" "" .
7109 12409      -100   46.625   -4662.5 "50025510" 2394425 "19931222" "S"  49.5 "TTQR" "TB"  3 "COM" "46.62500" "500255104" "" .
7109 12179       100     28.5      2850 "53567810" 2468512 "19930506" "B"  49.5 "TTXX" "TB"  6 "COM" "28.50000" "535678106" "" .
7109 12575      -100     46.5     -4650 "53567810" 2468512 "19940606" "S"  49.5 "TTXX" "TB"  6 "COM" "46.50000" "535678106" "" .
7109 12738        50       51      2550 "53567810" 2468512 "19941116" "B"  49.5 "TTXX" "TB"  6 "COM" "51.00000" "535678106" "" .
7109 12817       -50     51.5     -2575 "53567810" 2468512 "19950203" "S"  49.5 "TTXX" "TB"  6 "COM" "51.50000" "535678106" "" .
7109 13234       100   40.375    4037.5 "53567810" 2468512 "19960326" "B"  49.5 "TTXX" "TB"  6 "COM" "40.37500" "535678106" "" .
7109 11728       100   19.625    1962.5 "59408710" 2603659 "19920210" "B"  49.5 "TTXX" "TB"  6 "COM" "19.62500" "594087108" "" .
7109 12319        50   36.625   1831.25 "59408710" 2603659 "19930923" "B"  49.5 "TTXX" "TB"  6 "COM" "36.62500" "594087108" "" .
7109 12395      -150    32.25   -4837.5 "59408710" 2603659 "19931208" "S" 74.25 "TTVT" "TB"  6 "COM" "32.25000" "594087108" "" .
7109 12282       100     21.5      2150 "72387710" 2983605 "19930817" "B"  49.5 "TTXX" "TB"  6 "COM" "21.50000" "723877106" "" .
7109 12418      -100    18.75     -1875 "72387710" 2983605 "19931231" "S"  49.5 "TTQH" "TB"  6 "COM" "18.75000" "723877106" "" .
7109 11354      -100     9.25      -925 "74838C10" 3071757 "19910201" "S" 36.72 "TTFK" "TB"  6 "COM" "9.25000"  "74838C106" "" .
7109 12249        50    30.75    1537.5 "76340810" 3144386 "19930715" "B"    55 "DDNC" "BA"  6 "COM" "30.75000" "763408101" "" .
7109 12624      -200    13.75     -2750 "76340810" 3144386 "19940725" "S" 66.74 "TTXX" "TB"  6 "COM" "13.75000" "763408101" "" .
7109 12627      -300       15     -4500 "76340810" 3144386 "19940728" "S" 77.13 "TTXX" "TB"  6 "COM" "15.00000" "763408101" "" .
7109 12697       100    20.25      2025 "77136710" 3166007 "19941006" "B"  49.5 "TTXX" "TB" 25 "COM" "20.25000" "771367109" "" .
7109 12472       100    27.25      2725 "80818810" 3241320 "19940223" "B"  49.5 "TTXX" "TB"  6 "COM" "27.25000" "808188106" "" .
7109 12514      -100    27.75     -2775 "80818810" 3241320 "19940406" "S"  49.5 "TTXX" "TB"  6 "COM" "27.75000" "808188106" "" .
7109 11396       100   36.875    3687.5 "86331410" 3421805 "19910315" "B"    49 "IROM" "BR"  2 "COM" "36.87500" "863314100" "" .
7109 12075      -200   19.875     -3975 "86331410" 3421805 "19930122" "S" 74.01 "TTTM" "TB"  2 "COM" "19.87500" "863314100" "" .
7109 12479       100       21      2100 "86693310" 3432082 "19940302" "B"  49.5 "TTXX" "TB"  2 "COM" "21.00000" "866933104" "" .
end
format %td dates

*ENSURE NO MORE THAN ONE TRANSACTION PER INVESTOR-STOCK-DATE
isid investor cusip dates
bys investor cusip (dates): g netposition= sum(stocks)
g tag= netposition>=0
gsort investor cusip tag -dates
by investor cusip: g tagdate= date[1] if !tag[1]
drop if date<= tagdate & !missing(tagdate)
    Res.:

    Code:
    . sort investor cusip dates

. l investor cusip dates stocks netposition, sepby(investor cusip)

     +------------------------------------------------------+
     | investor      cusip       dates    stocks   netpos~n |
     |------------------------------------------------------|
  1. |     2290   06071610   05aug1996       100        100 |
     |------------------------------------------------------|
  2. |     2290   17958410   16sep1996       200        200 |
     |------------------------------------------------------|
  3. |     2290   80850988   15oct1996       500        500 |
     |------------------------------------------------------|
  4. |     2290   86707110   21aug1996       200        200 |
     |------------------------------------------------------|
  5. |     3206   02365010   28jun1991      1000       1000 |
  6. |     3206   02365010   17dec1993     -1000          0 |
     |------------------------------------------------------|
  7. |     3206   08188080   04nov1996   257.732    257.732 |
     |------------------------------------------------------|
  8. |     3206   77957Q10   06oct1992   452.489    452.489 |
     |------------------------------------------------------|
  9. |     3206   92190830   06oct1992   152.486    152.486 |
     |------------------------------------------------------|
 10. |     6002   02312710   04may1993       100        100 |
 11. |     6002   02312710   23jul1993      -100          0 |
     |------------------------------------------------------|
 12. |     6002   07251010   17oct1996        50         50 |
     |------------------------------------------------------|
 13. |     6002   41025210   26jul1996       200        200 |
     |------------------------------------------------------|
 14. |     6002   50221010   22jan1993      1000       1000 |
     |------------------------------------------------------|
 15. |     6002   55261810   11jan1993       200        200 |
 16. |     6002   55261810   20apr1994       200        400 |
     |------------------------------------------------------|
 17. |     6002   55403410   04oct1996       200        200 |
     |------------------------------------------------------|
 18. |     6002   55917720   29jul1993       100        100 |
 19. |     6002   55917720   21mar1994      -100          0 |
     |------------------------------------------------------|
 20. |     6002   57679810   17jan1994       100        100 |
     |------------------------------------------------------|
 21. |     6002   59373610   17jun1993       200        200 |
 22. |     6002   59373610   07may1996      -200          0 |
     |------------------------------------------------------|
 23. |     6002   62838030   26jul1996    53.433     53.433 |
     |------------------------------------------------------|
 24. |     6002   64121010   10sep1996       100        100 |
     |------------------------------------------------------|
 25. |     6002   77064830   06sep1994   226.655    226.655 |
     |------------------------------------------------------|
 26. |     6002   77064887   08jan1996   116.279    116.279 |
 27. |     6002   77064887   16jul1996    62.814    179.093 |
     |------------------------------------------------------|
 28. |     6002   81090510   08feb1993       100        100 |
 29. |     6002   81090510   20dec1995      -100          0 |
     |------------------------------------------------------|
 30. |     6002   81137170   05jan1993       100        100 |
 31. |     6002   81137170   04feb1993      -100          0 |
     |------------------------------------------------------|
 32. |     6002   87990540   16sep1996       200        200 |
 33. |     6002   87990540   07nov1996       100        300 |
     |------------------------------------------------------|
 34. |     7109   00157530   27jan1994       100        100 |
 35. |     7109   00157530   06apr1994      -100          0 |
     |------------------------------------------------------|
 36. |     7109   00190710   15mar1991       100        100 |
 37. |     7109   00190710   16dec1993      -100          0 |
     |------------------------------------------------------|
 38. |     7109   01849010   19aug1991       100        100 |
 39. |     7109   01849010   03feb1993      -100          0 |
     |------------------------------------------------------|
 40. |     7109   03785710   23nov1993       100        100 |
 41. |     7109   03785710   08apr1994      -100          0 |
 42. |     7109   03785710   30apr1996       100        100 |
     |------------------------------------------------------|
 43. |     7109   04890310   06oct1994       150        150 |
     |------------------------------------------------------|
 44. |     7109   09367610   24jun1993       100        100 |
 45. |     7109   09367610   23feb1994      -100          0 |
     |------------------------------------------------------|
 46. |     7109   10904310   22mar1993        50         50 |
 47. |     7109   10904310   20jun1994       -50          0 |
 48. |     7109   10904310   06oct1994        50         50 |
     |------------------------------------------------------|
 49. |     7109   12692010   21oct1996        50         50 |
     |------------------------------------------------------|
 50. |     7109   20810810   18jan1991       100        100 |
 51. |     7109   20810810   31jan1991      -100          0 |
 52. |     7109   20810810   04apr1991       100        100 |
 53. |     7109   20810810   03feb1993      -100          0 |
     |------------------------------------------------------|
 54. |     7109   22237210   05nov1993       100        100 |
 55. |     7109   22237210   06apr1994      -100          0 |
     |------------------------------------------------------|
 56. |     7109   23281510   25jul1996       200        200 |
     |------------------------------------------------------|
 57. |     7109   25084710   26aug1992       100        100 |
 58. |     7109   25084710   05nov1993        50        150 |
 59. |     7109   25084710   08jul1994      -150          0 |
     |------------------------------------------------------|
 60. |     7109   25283630   14aug1991       100        100 |
 61. |     7109   25283630   25mar1993      -100          0 |
     |------------------------------------------------------|
 62. |     7109   38131710   11feb1993        50         50 |
 63. |     7109   38131710   20apr1993       -50          0 |
     |------------------------------------------------------|
 64. |     7109   40621610   05sep1995       100        100 |
     |------------------------------------------------------|
 65. |     7109   44984210   12jan1994       100        100 |
 66. |     7109   44984210   11nov1996      -100          0 |
     |------------------------------------------------------|
 67. |     7109   50025510   25aug1993       100        100 |
 68. |     7109   50025510   22dec1993      -100          0 |
     |------------------------------------------------------|
 69. |     7109   53567810   06may1993       100        100 |
 70. |     7109   53567810   06jun1994      -100          0 |
 71. |     7109   53567810   16nov1994        50         50 |
 72. |     7109   53567810   03feb1995       -50          0 |
 73. |     7109   53567810   26mar1996       100        100 |
     |------------------------------------------------------|
 74. |     7109   59408710   10feb1992       100        100 |
 75. |     7109   59408710   23sep1993        50        150 |
 76. |     7109   59408710   08dec1993      -150          0 |
     |------------------------------------------------------|
 77. |     7109   72387710   17aug1993       100        100 |
 78. |     7109   72387710   31dec1993      -100          0 |
     |------------------------------------------------------|
 79. |     7109   77136710   06oct1994       100        100 |
     |------------------------------------------------------|
 80. |     7109   80818810   23feb1994       100        100 |
 81. |     7109   80818810   06apr1994      -100          0 |
     |------------------------------------------------------|
 82. |     7109   86693310   02mar1994       100        100 |
     +------------------------------------------------------+

.

    Comment

    • #3
      Thank you for the fast response!
      Can you maybe please explain the last two lines of your code:
      Code:
      by investor cusip: g tagdate= date[1] if !tag[1]
drop if date<= tagdate & !missing(tagdate)
      I dont get what you are doing here and why you are using date.

      Comment

      • #4
        g tag= netposition>=0
        A negative value for "netposition" implies that the investor sold stock that was purchased outside of the observed period. Therefore, I generate a tag variable equal to 1 if netposition is positive or zero and even more importantly, equal to 0 if netposition is negative.


        gsort investor cusip tag -dates
        Here, I identify the latest date at which a negative net position occurred. tag=0 is sorted before tag=1 and -dates sorts date from latest to earliest.


        by investor cusip: g tagdate= date[1] if !tag[1]
        Within each group defined by the variables "investor and cusip", I retrieve the latest date when a negative net position occurred, given that the first observation in such a group has tag=0 (i.e. !tag[1]).


        drop if date<= tagdate & !missing(tagdate)
        Finally, I drop observations that precede the latest date when a negative net position occurred and including that latest negative position. These all happen to occur if and only if "tagdate" is nonmissing.
        Last edited by Andrew Musau; 18 Oct 2022, 06:50.

        Comment

        • #5
          When I use the aboce mentioned code it deletes never the same amount of observations. Every time the number of deleted observations is different. Is there a mistake in the code because I would have assume that the number of deleted obeservations should always be the same when executing the code?

          Comment

          • #6
            I couldn't tell you without a data example that reproduces that inconsistency. My code in #2 starts with the assertion

            isid investor cusip dates
            which if satisfied rules out duplicates in terms of those variables.
            • 1 like

            Comment

            • #7
              I actually get an error message when using
              Code:
              isid investor cusip dates
              It says that the three variables do not uniquely identify the observations
              Last edited by Jana He; 03 Nov 2022, 03:58.

              Comment

              • #8
                Then my code relies on these variables uniquely identifying the observations, and I was explicit on this with

                *ENSURE NO MORE THAN ONE TRANSACTION PER INVESTOR-STOCK-DATE
                What this means is that some investors had multiple transactions involving the same stock on the same day. You therefore need to net out the investor position at each date, and this can be achieved by summing the transactions.

                Comment

                • #9
                  How would I sum the transactions? Because if I have mulitple transactions of the same investor on the same day, I would use the average purchase price of the multiple transactions

                  Comment

                  • #10
                    Something along the lines of

                    Code:
                    collapse (sum) stocks (mean) price, by(investor cusip dates)
                    where you also include other relevant variables in the mix. However, you may want to adjust the mean price according to the number of stocks purchased in a transaction.

                    Comment

                    • #11
                      Thank you! You are correct a weighted purchase prices makes more sense.
                      But it could also be that a sale and purchase appears on the same day. If this happens I do not want to calculate the weighted mean price. How do I solve this issue?

                      Comment

                      • #12
                        Create an example with 3-4 identifiers and repeated transactions and I can illustrate a procedure.

                        Comment

                        • #13
                          Code:
                          * Example generated by -dataex-. For more info, type help dataex
clear
input float investor str10 stringdates float stocks str10 cusip float(price dates)
1 "19960805"  100 "0123"   50 13366
1 "19961005"  -45 "0123"   40 13427
1 "19961005"  -55 "0123"   42 13427
1 "19910108"  200 "0456"  125 11330
1 "19920416"  125 "0789"  200 11794
2 "19930202"  250 "0999"   65 12086
2 "19930202"  100 "0999"   67 12086
2 "19940506" -350 "0999"  100 12544
2 "19950705"  200 "0888"  250 12969
3 "19910408"  100 "0147"  100 11420
3 "19910408" -100 "0147"  105 11420
3 "19930606"  136 "0258"  250 12210
4 "19921105"  125 "0369"  158 11997
4 "19950507" -125 "0369"  200 12910
4 "19961206"  100 "0123"  200 13489
4 "19961212" -145 "0254"  120 13495
4 "19961216"  -50 "0123"  180 13499
5 "19920808" -120 "0258"  200 11908
5 "19940708" -250 "0148" 2500 12607
5 "19910605"   50 "0258"  250 11478
5 "19920607"   30 "0574"  200 11846
5 "19920607"   25 "0574"  205 11846
end
format %td dates
                          I just created a simple sample. I am very grateful for your help!

                          Comment

                          • #14
                            But it could also be that a sale and purchase appears on the same day. If this happens I do not want to calculate the weighted mean price.
                            Code:
                            * Example generated by -dataex-. To install: ssc install dataex
clear
input float investor str10 stringdates float stocks str10 cusip float(price dates)
1 "19960805"  100 "0123"   50 13366
1 "19961005"  -45 "0123"   40 13427
1 "19961005"  -55 "0123"   42 13427
1 "19910108"  200 "0456"  125 11330
1 "19920416"  125 "0789"  200 11794
2 "19930202"  250 "0999"   65 12086
2 "19930202"  100 "0999"   67 12086
2 "19940506" -350 "0999"  100 12544
2 "19950705"  200 "0888"  250 12969
3 "19910408"  100 "0147"  100 11420
3 "19910408" -100 "0147"  105 11420
3 "19930606"  136 "0258"  250 12210
4 "19921105"  125 "0369"  158 11997
4 "19950507" -125 "0369"  200 12910
4 "19961206"  100 "0123"  200 13489
4 "19961212" -145 "0254"  120 13495
4 "19961216"  -50 "0123"  180 13499
5 "19920808" -120 "0258"  200 11908
5 "19940708" -250 "0148" 2500 12607
5 "19910605"   50 "0258"  250 11478
5 "19920607"   30 "0574"  200 11846
5 "19920607"   25 "0574"  205 11846
end

*TAG DAYS WITH BOTH PURCHASES AND SALES
bys investor cusip dates (stocks): gen tag= stocks[1]<0 & stocks[_N]>0
*GEN WEIGHTED PRICE FOR DAYS NOT TAGGED
bys investor cusip dates: egen transactions= total(cond(!tag, price*abs(stocks), .))
bys investor cusip dates: egen units= total(cond(!tag, abs(stocks), .))
bys investor cusip dates: gen newprice= cond(!tag, transactions/units, price)
*LIST
l, sepby(investor cusip dates)
*COLLAPSE 
collapse (sum) stocks (mean) price=newprice, by(investor cusip dates)
*LIST
l, sepby(investor cusip dates)
                            Res.:

                            Code:
                            . l, sepby(investor cusip dates)

     +------------------------------------------------------------------------------------------+
     | investor   string~s   stocks   cusip   price   dates   tag   transa~s   units   newprice |
     |------------------------------------------------------------------------------------------|
  1. |        1   19960805      100    0123      50   13366     0       5000     100         50 |
     |------------------------------------------------------------------------------------------|
  2. |        1   19961005      -55    0123      42   13427     0       4110     100       41.1 |
  3. |        1   19961005      -45    0123      40   13427     0       4110     100       41.1 |
     |------------------------------------------------------------------------------------------|
  4. |        1   19910108      200    0456     125   11330     0      25000     200        125 |
     |------------------------------------------------------------------------------------------|
  5. |        1   19920416      125    0789     200   11794     0      25000     125        200 |
     |------------------------------------------------------------------------------------------|
  6. |        2   19950705      200    0888     250   12969     0      50000     200        250 |
     |------------------------------------------------------------------------------------------|
  7. |        2   19930202      100    0999      67   12086     0      22950     350   65.57143 |
  8. |        2   19930202      250    0999      65   12086     0      22950     350   65.57143 |
     |------------------------------------------------------------------------------------------|
  9. |        2   19940506     -350    0999     100   12544     0      35000     350        100 |
     |------------------------------------------------------------------------------------------|
 10. |        3   19910408     -100    0147     105   11420     1          0       0        105 |
 11. |        3   19910408      100    0147     100   11420     1          0       0        100 |
     |------------------------------------------------------------------------------------------|
 12. |        3   19930606      136    0258     250   12210     0      34000     136        250 |
     |------------------------------------------------------------------------------------------|
 13. |        4   19961206      100    0123     200   13489     0      20000     100        200 |
     |------------------------------------------------------------------------------------------|
 14. |        4   19961216      -50    0123     180   13499     0       9000      50        180 |
     |------------------------------------------------------------------------------------------|
 15. |        4   19961212     -145    0254     120   13495     0      17400     145        120 |
     |------------------------------------------------------------------------------------------|
 16. |        4   19921105      125    0369     158   11997     0      19750     125        158 |
     |------------------------------------------------------------------------------------------|
 17. |        4   19950507     -125    0369     200   12910     0      25000     125        200 |
     |------------------------------------------------------------------------------------------|
 18. |        5   19940708     -250    0148    2500   12607     0     625000     250       2500 |
     |------------------------------------------------------------------------------------------|
 19. |        5   19910605       50    0258     250   11478     0      12500      50        250 |
     |------------------------------------------------------------------------------------------|
 20. |        5   19920808     -120    0258     200   11908     0      24000     120        200 |
     |------------------------------------------------------------------------------------------|
 21. |        5   19920607       25    0574     205   11846     0      11125      55   202.2727 |
 22. |        5   19920607       30    0574     200   11846     0      11125      55   202.2727 |
     +------------------------------------------------------------------------------------------+

. 
. *COLLAPSE 

. 
. collapse (sum) stocks (mean) price=newprice, by(investor cusip dates)

. 
. *LIST

. 
. l, sepby(investor cusip dates)

     +----------------------------------------------+
     | investor   cusip   dates   stocks      price |
     |----------------------------------------------|
  1. |        1    0123   13366      100         50 |
     |----------------------------------------------|
  2. |        1    0123   13427     -100       41.1 |
     |----------------------------------------------|
  3. |        1    0456   11330      200        125 |
     |----------------------------------------------|
  4. |        1    0789   11794      125        200 |
     |----------------------------------------------|
  5. |        2    0888   12969      200        250 |
     |----------------------------------------------|
  6. |        2    0999   12086      350   65.57143 |
     |----------------------------------------------|
  7. |        2    0999   12544     -350        100 |
     |----------------------------------------------|
  8. |        3    0147   11420        0      102.5 |
     |----------------------------------------------|
  9. |        3    0258   12210      136        250 |
     |----------------------------------------------|
 10. |        4    0123   13489      100        200 |
     |----------------------------------------------|
 11. |        4    0123   13499      -50        180 |
     |----------------------------------------------|
 12. |        4    0254   13495     -145        120 |
     |----------------------------------------------|
 13. |        4    0369   11997      125        158 |
     |----------------------------------------------|
 14. |        4    0369   12910     -125        200 |
     |----------------------------------------------|
 15. |        5    0148   12607     -250       2500 |
     |----------------------------------------------|
 16. |        5    0258   11478       50        250 |
     |----------------------------------------------|
 17. |        5    0258   11908     -120        200 |
     |----------------------------------------------|
 18. |        5    0574   11846       55   202.2727 |
     +----------------------------------------------+

.

                            Comment

                            • #15
                              Thank you very much!
                              Could you maybe again explain what you did with the codes
                              Code:
                              total(cond(
                              Last edited by Jana He; 05 Nov 2022, 01:41.

                              Comment

                              Previous 1 2 Next
                              Working...
                              X