Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Nested forvalues loop

    Hello,

    below you find my current code but it does not really work.





    I want to generate the variables buyprice and sellprice which includes the price so I can compare for what price an investor bought a stock and for which price he sold it. First, I want that j goes through the numbers 1 to the last observation in my data set (I want to achieve this by using count). The variable u is a counter variable which counts if the two conditions are fulfilled. Put differently, the variable u increases if the value of the stock is bigger than zero and if in two subsequent lines we have the same investor (my data set is sorted by investor). In the next step another loop starts using the counter variable u as a starting point. Then variable a increases if the conditions are fulfilled and then the two variables buyprice and sellprice are generated including the buy price of a stock and the sell price of the very same stock.

    But if I run the code I got the error message "invalid syntax".
    Maybe someone can help me with this issue.
    Thanks!
    Attached Files
    Last edited by Jana He; 06 Oct 2022, 06:27.

  • #2
    Presumably, count should be `count'

    It also helps to have set trace on for diagnosing error messages.

    Comment


    • #3
      I rerun the code using `count' and the error is still there. Just to test whether this was the mistake I substituted "count" by 100 so that j=1/100 { and so on. Now, I get a different error message: "if not allowed". So, it seems like there is a mistake using "count" and an additional mistake in my if-condition.

      Comment


      • #4
        Justin Niakamal is right, but fixing that just exposes the next problem. My guess is that all statements of the form


        Code:
        local ... if ...
        should be of the form

        Code:
        if ... local ...
        and then in turn the statement starting

        Code:
        gen buyprice[`a']
        is lllegal.

        I'd advise backing up, giving an example of the data and then re-stating the problem. Looping through observations in this way comes naturally if you are more familiar with mainstream programming languages, but it is rarely needed in Stata.

        Comment


        • #5
          Thanks!
          I did as you suggested but there still seems to be a problem with `count'. I still got the error "invalid syntax" after `count'.
          To my data set: I have trading data of individual investors containing the investor identifier, the date of transaction (buy or sell of stocks), the number of stocks sold or bought (negative sign means sold), the price of the stock, the volume (meaning price times #stocks) and an stock identifier. Now, I want to generate new variables to be able to identify when stocks are sold with losses and when with gains. Therefore I coded the nested forvalues loop. I want to generate 2 variables: Variable 1 shows the the buy price and Variable 2 shows the sell price.
          Having this variables, I want to calculate the absolute number of loss and gain sales per month. For example if I want to know the absolute number of gain sales per month I can use an if-condition using Variable 1 and Variable 2 (if Variable 2 > Variable 1 etc.).

          How else can I generate the two variables "buyprice" and "sellprice" if not using "gen"?

          Click image for larger version

Name:	Problem2_Stata.PNG
Views:	1
Size:	51.9 KB
ID:	1684582





          Click image for larger version

Name:	Problem3_Stata.PNG
Views:	1
Size:	3.8 KB
ID:	1684583

          Comment


          • #6
            This is not yet what I asked for as you don't give an example of the data.

            That said, where did you define the local macro count -- because it is not visible to the code when you refer to it? A local macro must be defined in the same block of code.

            Comment


            • #7
              I did not define the local macro count. Should I just add: local count into the code? I am not really familiar with local macros, so sorry for the question.

              Here you see an example of the data:
              Click image for larger version

Name:	Problem4_Stata.PNG
Views:	1
Size:	31.8 KB
ID:	1684596

              Comment


              • #8
                Yes. If you are going to use local macros, you need to define them first.

                Comment


                • #9
                  I added "local count" but if I run the code I still got the same error message: "Invalid syntax".

                  Click image for larger version

Name:	Problem5_Stata.PNG
Views:	1
Size:	53.6 KB
ID:	1684613



                  Where is my mistake?
                  And I still dont know how to create the variable buyprice and sellprice without using gen. Maybe someone can help me with this issue?

                  Comment


                  • #10
                    That is defining it as an empty string, which actually defines and removes it at the same time. Simply, what do you think that Stata is going to do when it sees that definition? How is Stata going to know what you want to be counted?

                    You may mean the number of observations

                    l
                    Code:
                    ocal count = _N 
                    or perhaps something else. There are 42 lines in the file above this that we can't see.

                    This code shows signs that you know how to program in another language and you're trying to translate that thinking into Stata. Good. and that could be made to work, but it is not the best strategy for Stata.

                    Other way round, I don't work with your kind of data, so trying to understand the substance of what you want is just too hard for me to suggest code. You need someone else who knows Stata quite well and who can read your explanation and hold it all in their head.

                    Comment


                    • #11
                      Originally posted by Jana He View Post
                      Thanks!
                      To my data set: I have trading data of individual investors containing the investor identifier, the date of transaction (buy or sell of stocks), the number of stocks sold or bought (negative sign means sold), the price of the stock, the volume (meaning price times #stocks) and an stock identifier. Now, I want to generate new variables to be able to identify when stocks are sold with losses and when with gains. Therefore I coded the nested forvalues loop. I want to generate 2 variables: Variable 1 shows the the buy price and Variable 2 shows the sell price.
                      Having this variables, I want to calculate the absolute number of loss and gain sales per month. For example if I want to know the absolute number of gain sales per month I can use an if-condition using Variable 1 and Variable 2 (if Variable 2 > Variable 1 etc.).
                      I am fairly certain you do not need loops at all for this problem. If you could give a sample of your data, others will be able help you more easily and efficiently. Use the dataex command for providing sample data. Also, when you want to show us your code, please do not take screenshots. Instead, copy the relevant code and paste it here within CODE blocks (use the # button on the toolbar above the text entry box in this forum),

                      Comment


                      • #12
                        Code:
                        local count = _N
                        local a=0
                        local u=0
                        
                        set trace on 
                        
                        forvalues j = 1/`count'{
                             if stocks[`j']>0 & investor[`j'] == investor[`j'+1] {
                                 local u =`j'+1
                             }
                             
                                forvalues i = `u'/`count'{
                                    if investor[`j']==investor[`i'] & cusip[`j']==cusip[`i'] & stocks[`j']==-stocks[`i'] & price[`i']!=0 & price[`i']==price[`j'] {
                                        local a = `a'+1
                                        gen buyprice[`a'] = price[`j']
                                        gen sellprice[`a'] = price [`i']
                                    }
                                }
                        }
                        
                        set trace off
                        That's my code. And I already provided a sample of my data in the previous posts.
                        And thank you Nick! Your suggested code "local count = _N" now works fine but I still don't know how to create the variable buyprice and sellprice...
                        I would appreciate any help!

                        Comment


                        • #13
                          Jana He the sample of data you provided is not usable since we can't copy and paste it into Stata. There is a command called dataex in Stata that allows you to easily create an extract that can be pasted into this forum for others to be able to use it and help you solve your problem. This is what I had asked you to do in #11. The sooner you do this, the sooner others will be able to help you.

                          Comment


                          • #14
                            Here you can see an extract of my data:


                            Code:
                            * Example generated by -dataex-. For more info, type help dataex
                            clear
                            input long investor float(dates stocks price volume) str8(cusip stringdates)
                            2290 13366       100   54.375    5437.5 "06071610" "19960805"
                            2290 13408       200       25      5000 "17958410" "19960916"
                            2290 13382       200       21      4200 "86707110" "19960821"
                            2290 13437       500       10      5000 "80850988" "19961015"
                            3206 11501      1000     2.25      2250 "02365010" "19910628"
                            3206 12404     -1000   1.0312   -1031.2 "02365010" "19931217"
                            3206 12977      -550   13.875  -7631.25 "96040210" "19950713"
                            3206 13457   257.732     19.4      5000 "08188080" "19961104"
                            3206 11610    345.96 21.30001   7368.95 "47102340" "19911015"
                            3206 13083  -369.231 29.87999 -11032.62 "47102340" "19951027"
                            3206 11967   452.489 11.04999      5000 "77957Q10" "19921006"
                            3206 11609   -122.52 34.82999  -4267.37 "81116510" "19911014"
                            3206 11609  -194.999 16.27998  -3174.58 "81622110" "19911014"
                            3206 11967   152.486  32.7899      5000 "92190830" "19921006"
                            6002 13263       -12   43.875    -526.5 "03189710" "19960424"
                            6002 12177       100     16.5      1650 "02312710" "19930504"
                            6002 12257      -100    23.75     -2375 "02312710" "19930723"
                            6002 13439        50    19.75     987.5 "07251010" "19961017"
                            6002 13356       200    4.875       975 "41025210" "19960726"
                            6002 12075      1000    1.125      1125 "50221010" "19930122"
                            6002 12064       200     5.25      1050 "55261810" "19930111"
                            6002 12528       200      5.5      1100 "55261810" "19940420"
                            6002 13426       200     2.75       550 "55403410" "19961004"
                            6002 12263       100   11.625    1162.5 "55917720" "19930729"
                            6002 12498      -100     16.5     -1650 "55917720" "19940321"
                            6002 12435       100    6.875     687.5 "57679810" "19940117"
                            6002 12221       200     3.25       650 "59373610" "19930617"
                            6002 13276      -200    1.625      -325 "59373610" "19960507"
                            6002 13402       100    15.75      1575 "64121010" "19960910"
                            6002 11947      -100    10.75     -1075 "77310210" "19920916"
                            6002 12058       100   18.625    1862.5 "81137170" "19930105"
                            6002 12088      -100    23.75     -2375 "81137170" "19930204"
                            6002 12092       100    8.625     862.5 "81090510" "19930208"
                            6002 13137      -100    11.25     -1125 "81090510" "19951220"
                            6002 13408       200   4.5625     912.5 "87990540" "19960916"
                            6002 13460       100     1.75       175 "87990540" "19961107"
                            6002 13368 -6746.722        1  -6746.72 "31606710" "19960807"
                            6002 11948   -60.136 65.36999  -3931.09 "31618410" "19920917"
                            6002 13352 -5138.506        1  -5138.51 ""         "19960722"
                            6002 13368  -551.012        1   -551.01 ""         "19960807"
                            6002 13356    53.433 36.70017      1961 "62838030" "19960726"
                            6002 13374   388.601     7.72      3000 ""         "19960813"
                            6002 13445   274.348  7.29001      2000 ""         "19961023"
                            6002 12667   226.655 11.02998      2500 "77064830" "19940906"
                            6002 13156   116.279 10.32001      1200 "77064887" "19960108"
                            6002 13346    62.814 11.94001       750 "77064887" "19960716"
                            7109 12445       100       32      3200 "00157530" "19940127"
                            7109 12514      -100   34.375   -3437.5 "00157530" "19940406"
                            7109 11396       100    22.25      2225 "00190710" "19910315"
                            7109 12403      -100     23.5     -2350 "00190710" "19931216"
                            7109 11553       100    21.75      2175 "01849010" "19910819"
                            7109 12087      -100     22.5     -2250 "01849010" "19930203"
                            7109 11354      -100   24.875   -2487.5 "03673210" "19910201"
                            7109 12380       100    20.75      2075 "03785710" "19931123"
                            7109 12516      -100    25.75     -2575 "03785710" "19940408"
                            7109 13269       100    26.25      2625 "03785710" "19960430"
                            7109 12697       150     16.5      2475 "04890310" "19941006"
                            7109 12228       100   20.875    2087.5 "09367610" "19930624"
                            7109 12472      -100    24.75     -2475 "09367610" "19940223"
                            7109 12134        50     65.5      3275 "10904310" "19930322"
                            7109 12589       -50     72.5     -3625 "10904310" "19940620"
                            7109 12697        50   68.375   3418.75 "10904310" "19941006"
                            7109 13443        50   62.875   3143.75 "12692010" "19961021"
                            7109 11340       100    27.25      2725 "20810810" "19910118"
                            7109 11353      -100    27.25     -2725 "20810810" "19910131"
                            7109 11416       100       24      2400 "20810810" "19910404"
                            7109 12087      -100   23.375   -2337.5 "20810810" "19930203"
                            7109 12362       100   26.125    2612.5 "22237210" "19931105"
                            7109 12514      -100   23.625   -2362.5 "22237210" "19940406"
                            7109 13355       200    13.75      2750 "23281510" "19960725"
                            7109 11926       100       33      3300 "25084710" "19920826"
                            7109 12362        50   30.875   1543.75 "25084710" "19931105"
                            7109 12607      -150   25.125  -3768.75 "25084710" "19940708"
                            7109 11548       100     27.5      2750 "25283630" "19910814"
                            7109 12137      -100   12.125   -1212.5 "25283630" "19930325"
                            7109 12095        50   48.375   2418.75 "38131710" "19930211"
                            7109 12163       -50   44.125  -2206.25 "38131710" "19930420"
                            7109 12715       200   13.125      2625 "38526910" "19941024"
                            7109 13464      -300   12.625   -3787.5 "38526910" "19961111"
                            7109 13031       100       44      4400 "40621610" "19950905"
                            7109 12430       100    28.75      2875 "44984210" "19940112"
                            7109 13464      -100       13     -1300 "44984210" "19961111"
                            7109 12290       100   40.875    4087.5 "50025510" "19930825"
                            7109 12409      -100   46.625   -4662.5 "50025510" "19931222"
                            7109 12179       100     28.5      2850 "53567810" "19930506"
                            7109 12575      -100     46.5     -4650 "53567810" "19940606"
                            7109 12738        50       51      2550 "53567810" "19941116"
                            7109 12817       -50     51.5     -2575 "53567810" "19950203"
                            7109 13234       100   40.375    4037.5 "53567810" "19960326"
                            7109 11728       100   19.625    1962.5 "59408710" "19920210"
                            7109 12319        50   36.625   1831.25 "59408710" "19930923"
                            7109 12395      -150    32.25   -4837.5 "59408710" "19931208"
                            7109 12282       100     21.5      2150 "72387710" "19930817"
                            7109 12418      -100    18.75     -1875 "72387710" "19931231"
                            7109 11354      -100     9.25      -925 "74838C10" "19910201"
                            7109 11442       200    13.75      2750 ""         "19910430"
                            7109 12249        50    30.75    1537.5 "76340810" "19930715"
                            7109 12624      -200    13.75     -2750 "76340810" "19940725"
                            7109 12627      -300       15     -4500 "76340810" "19940728"
                            7109 12697       100    20.25      2025 "77136710" "19941006"
                            end
                            format %td dates
                            The first column is an investor identifier, the second column shows the date the investor bought or sold a stock, the third column depicts the number of stocks bought or sold (negative sign), the fourth shows the price of the stock and the fifth the volume. The sixth column is a stock identifier and the last one is the date formatted as string.

                            Comment


                            • #15
                              Jana He thanks, that is very helpful.

                              So now it is time to think about what you actually want, not the process by which you want to get it -- because I think you are approaching at this from the way you would do it in a different language, where you think of the data like a matrix and work on it element by element, or row by row. Stata is not best used like that. Stata thinks of data as primarily being variables (which are the columns) which have observations (the row elements), and Stata commands generally ask to do something to one or more variables (i.e. columns). This is a simple way to think about it; of course Stata can do much more than this...

                              For this reason, I am going to ignore the entire process you tried to explain in #1. You would just not approach the problem that way in Stata. You description of the problem in #5 is more helpful, as well as the data extract and description you provided in #14. Thank you for that. However, I am still not sure what you finally want from the data.

                              Here is one example of something you can do very easily in Stata with this data:

                              Code:
                              gen double gain = - stocks * price
                              collapse (sum) gain, by(investor cusip)
                              The first command creates a new variable that keeps track of the proceeds/revenues from a sale (in which case it will be positive), or the expenditure on a purchase (negative).
                              The second command collapses the dataset, summing over all these revenues and expenditures, effectively giving you the "gains", and does this for each investor-stock combination. It produces one row for each such combination, along with the corresponding net gain.

                              Here is what the output looks like:

                              Code:
                              . li, noobs sepby(investor)
                                +----------------------------------+
                                | investor      cusip         gain |
                                |----------------------------------|
                                |     2290   06071610      -5437.5 |
                                |     2290   17958410        -5000 |
                                |     2290   80850988        -5000 |
                                |     2290   86707110        -4200 |
                                |----------------------------------|
                                |     3206   02365010   -1218.7999 |
                                |     3206   08188080   -5000.0006 |
                                |     3206   47102340    3663.6666 |
                                |     3206   77957Q10   -4999.9989 |
                                |     3206   81116510    4267.3703 |
                                |     3206   81622110    3174.5796 |
                                |     3206   92190830   -5000.0007 |
                                |     3206   96040210      7631.25 |
                                |----------------------------------|
                                |     6002               689.51855 |
                                |     6002   02312710          725 |
                                |     6002   03189710        526.5 |
                                |     6002   07251010       -987.5 |
                                |     6002   31606710    6746.7222 |
                                |     6002   31618410    3931.0897 |
                                |     6002   41025210         -975 |
                                |     6002   50221010        -1125 |
                                |     6002   55261810        -2150 |
                                |     6002   55403410         -550 |
                                |     6002   55917720        487.5 |
                                |     6002   57679810       -687.5 |
                                |     6002   59373610         -325 |
                                |     6002   62838030   -1961.0001 |
                                |     6002   64121010        -1575 |
                                |     6002   77064830        -2500 |
                                |     6002   77064887   -1950.0002 |
                                |     6002   77310210         1075 |
                                |     6002   81090510        262.5 |
                                |     6002   81137170        512.5 |
                                |     6002   87990540      -1087.5 |
                                |----------------------------------|
                                |     7109                   -2750 |
                                |     7109   00157530        237.5 |
                                |     7109   00190710          125 |
                                |     7109   01849010           75 |
                                |     7109   03673210       2487.5 |
                                |     7109   03785710        -2125 |
                                |     7109   04890310        -2475 |
                                |     7109   09367610        387.5 |
                                |     7109   10904310     -3068.75 |
                                |     7109   12692010     -3143.75 |
                                |     7109   20810810        -62.5 |
                                |     7109   22237210         -250 |
                                |     7109   23281510        -2750 |
                                |     7109   25084710        -1075 |
                                |     7109   25283630      -1537.5 |
                                |     7109   38131710       -212.5 |
                                |     7109   38526910       1162.5 |
                                |     7109   40621610        -4400 |
                                |     7109   44984210        -1575 |
                                |     7109   50025510          575 |
                                |     7109   53567810      -2212.5 |
                                |     7109   59408710      1043.75 |
                                |     7109   72387710         -275 |
                                |     7109   74838C10          925 |
                                |     7109   76340810       5712.5 |
                                |     7109   77136710        -2025 |
                                +----------------------------------+
                              Is this what you would like? If not, what would you really like to do? Another thing you could do to help us help you, is to show us a small example of what output you want from Stata, so that we can try and create that using code.
                              Last edited by Hemanshu Kumar; 07 Oct 2022, 10:58.

                              Comment

                              Working...
                              X