Announcement

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

  • Multiple linear regression

    Dear Stata experts,

    I'm new to stata and need some help for my analysis. This is my problem:
    I have a dataset with many companies and their peer groups (consisting of other companies) for the years 2014-2016.
    There is also another dataset with share price returns for a longer period for all those companies.

    I need for any company and year (2014-2016) a multiple linear regression.
    For example company Apple has in 2014 a peer group consisting of the companies Alphabet, Microsoft and Facebook.
    I need the beta coefficients for each of the peer companies on the share price returns for the five years befor 2014.

    Return Apple, t = β0 + β1*Return Alphabet, t + β2*Return Microsoft, t + β3*Return Facebook, t ; t ranges from 2009-2013

    So I face different problems at the moment:
    - multiple linear regression with different number of independent variables
    - linking/merging two (big) datasets (each company can be identified by an unique Id in both datasets)
    - incorporating only the share price return of the last five years (range of t not constant)

    I hope the description of the problem is ok.

    Thank you very much for your help,
    Freddy

  • #2
    I doubt anyone will be able to give you specific advice without seeing example data.

    Please post back with examples of both data sets, using the -dataex- command. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

    In selecting example data to show, make sure it represents the problems you are facing. In particular, make sure that the data from the two sets you want to merge together actually include observations that match each other. Make sure that the data reflects the variety of ranges of time and number of peers that you are facing. If you provide too restricted an example, you may be given code that works for your example but fails in your real data.

    When asking for help with code, always show example data. When showing example data, always use -dataex-.

    Comment


    • #3
      Here the example data from the first dataset:

      input str10 cik long grantId str10(peerCIK peerTicker) str80 peerName int fiscalyear byte fiscalmonth
      "0000016732" 372739 "0000040704" "GIS" "GENERAL MILLS INC" 2014 7
      "0000016732" 372739 "0000047111" "HSY" "HERSHEY CO" 2014 7
      "0000016732" 372739 "0000048465" "HRL" "HORMEL FOODS CORP" 2014 7
      "0000016732" 372739 "0000055067" "K" "KELLOGG CO" 2014 7
      "0000016732" 372739 "0000063754" "MKC" "MCCORMICK & CO INC" 2014 7
      "0000016732" 372739 "0000091419" "SJM" "SMUCKER (JM) CO" 2014 7
      "0000016732" 372739 "0000100493" "TSN" "TYSON FOODS INC -CL A" 2014 7
      "0000016732" 372739 "0000718053" "CCH.Z" "CAMPBELL RESOURCES INC" 2014 7
      "0000016732" 372739 "909954" "GMCR" "KEURIG GREEN MOUNTAIN INC" 2014 7
      "0000016732" 372739 "0001103982" "MDLZ" "MONDELEZ INTERNATIONAL INC" 2014 7
      "0000016732" 372739 "0001452575" "MJN" "MEAD JOHNSON NUTRITION CO" 2014 7
      "0000016732" 372739 "0001545158" "KRFT" "KRAFT FOODS GROUP INC" 2014 7
      "0001552000" 426861 "0001483096" "ACMP" "ACCESS MIDSTREAM PARTNERS LP" 2014 12
      "0001591763" 428062 "0001483096" "ACMP" "ACCESS MIDSTREAM PARTNERS LP" 2014 12

      The cik is the identifier for companies. 0000016732 is for CAMPBELL SOUP. In July (fiscalmonth 7) 2014, CAMPBELL SOUP has a peergroup of 12 companies (GENERAL MILLS, HERSHEY...) also identified by their cik. The peergroup of CAMPBELL SOUP for 2014 can also be identified by the grantId 372739.

      The second dataset has too many variables for the dataex command. Therefore I post a smaller example of the data:

      input long cik str18(200801 200802 200803) str17(200804 200805) str18(200806 200807) str17 200808
      1137411 "-.122829" "-.06454408" "-.02971138" ".104287" "-.02389095" "-.2185107" ".03607173" ".06344198"
      66382 "-.01883297" "-.05884946" "-.1763326" "-.05046805" ".06687129" ".003629032" ".05022097" ".07998626"
      16732 "-.1167087" ".02313055" ".05140911" ".03175837" "-.03793103" "-.0005973716" ".09434109" ".01181968"
      1551182 "-.1429603" "-.02373169" "-.01190624" ".102548" ".1068446" "-.1211212" "-.1580558" ".03012387"
      1326428 "-.1322413" ".08297293" "-.1666667" ".1531579" ".06549326" ".09616233" "-.122334" ".023223"
      896878 "-.02910471" "-.1345715" ".01694277" "-.001480933" ".07378569" "-.04799724" "-.008705114" ".1002561"
      1166036 ".00651273" ".03216912" "-.106087" ".1575875" ".02691877" "-.01161952" "-.0550209" ".04205749"

      This dataset has all the monthly share price returns of the last 10 years of all the companies in the first dataset (starting with January 2008/200801).

      So what I need is a multiple linear regression of the monthly share price returns of the focal firms (like CAMPBELL SOUP in the example above) with the monthly share price returns of its peer companies for each fiscalyear (firm and fiscalyear fixed effects) regressed with the returns over the 5 years before the fiscalyear (in the example July 2009 - July 2013 for CAMPBELL SOUP). The size of the peer group is not constant, so the number of independent variables depends on the focal firm and its fiscal year.

      I hope with this example data you can understand my problem.

      Thanks for your help
      Freddy


      Comment


      • #4
        Well, this data requires a lot of management before you can get to any regressions.

        FIrst of all, your second data example is bogus. You cannot possibly have a Stata dataset like that because things like 200801 are not legal variable names. If you try to run that input code you will see that it gives you precisely that kind of error message. I assume this is something that you concocted from a spreadsheet and you attempted to make it look like a -dataex- output. That means you haven't yet imported your data into Stata, which makes it premature to ask for help with Stata code.

        I will start from the assumption that you can and will import that second data set and that the variables will end up with names spr200801 spr200802, etc. With that done, you need to put the two data sets together. This is made harder by the fact that cik is a string variable in one data set and a long integer in another. So they have to be harmonized: strings are better for ID variables, so I will harmonize them both to strings. Next, the share price returns are strings, which is useless for regression. So those have to be converted to numbers. The share price data set needs to be put into long layout. Also, though this is not strictly necessary for what you have described so far, it is likely the case that you will need a real Stata internal format monthly date variable. Finally, putting them together is a tad complex even when the data are all in the right storage types. For matching on the focal firm's CIK there are multiple references to each CIK in both files, so -merge- will not work with this data in any useful way. It requires -joinby-, which pairs up every observation for a given CIK in one firm with all of the corresponding observations in the other. The subsequent matching on the peer firms is, however, a simple 1:1 merge on the combination of the firm id and the date.

        Putting this all together:

        Code:
        clear
        input str10 cik long grantId str10(peerCIK peerTicker) str80 peerName int fiscalyear byte fiscalmonth
        "0000016732" 372739 "0000040704" "GIS" "GENERAL MILLS INC" 2014 7
        "0000016732" 372739 "0000047111" "HSY" "HERSHEY CO" 2014 7
        "0000016732" 372739 "0000048465" "HRL" "HORMEL FOODS CORP" 2014 7
        "0000016732" 372739 "0000055067" "K" "KELLOGG CO" 2014 7
        "0000016732" 372739 "0000063754" "MKC" "MCCORMICK & CO INC" 2014 7
        "0000016732" 372739 "0000091419" "SJM" "SMUCKER (JM) CO" 2014 7
        "0000016732" 372739 "0000100493" "TSN" "TYSON FOODS INC -CL A" 2014 7
        "0000016732" 372739 "0000718053" "CCH.Z" "CAMPBELL RESOURCES INC" 2014 7
        "0000016732" 372739 "909954" "GMCR" "KEURIG GREEN MOUNTAIN INC" 2014 7
        "0000016732" 372739 "0001103982" "MDLZ" "MONDELEZ INTERNATIONAL INC" 2014 7
        "0000016732" 372739 "0001452575" "MJN" "MEAD JOHNSON NUTRITION CO" 2014 7
        "0000016732" 372739 "0001545158" "KRFT" "KRAFT FOODS GROUP INC" 2014 7
        "0001552000" 426861 "0001483096" "ACMP" "ACCESS MIDSTREAM PARTNERS LP" 2014 12
        "0001591763" 428062 "0001483096" "ACMP" "ACCESS MIDSTREAM PARTNERS LP" 2014 12
        end
        tempfile dataset1
        save `dataset1'
        
        clear
        input long cik str18(spr200801 spr200802 spr200803) str17(spr200804 spr200805) str18(spr200806 spr200807) str17 spr200808
        1137411 "-.122829" "-.06454408" "-.02971138" ".104287" "-.02389095" "-.2185107" ".03607173" ".06344198" 
        66382 "-.01883297" "-.05884946" "-.1763326" "-.05046805" ".06687129" ".003629032" ".05022097" ".07998626" 
        16732 "-.1167087" ".02313055" ".05140911" ".03175837" "-.03793103" "-.0005973716" ".09434109" ".01181968" 
        1551182 "-.1429603" "-.02373169" "-.01190624" ".102548" ".1068446" "-.1211212" "-.1580558" ".03012387" 
        1326428 "-.1322413" ".08297293" "-.1666667" ".1531579" ".06549326" ".09616233" "-.122334" ".023223" 
        896878 "-.02910471" "-.1345715" ".01694277" "-.001480933" ".07378569" "-.04799724" "-.008705114" ".1002561" 
        1166036 ".00651273" ".03216912" "-.106087" ".1575875" ".02691877" "-.01161952" "-.0550209" ".04205749" 
        end
        
        reshape long spr, i(cik) j(ym)
        numdate monthly mdate = ym, pattern("YM")
        drop ym
        rename cik firm_id
        tostring firm_id, replace format(%010.0f)
        destring spr, replace
        tempfile shareprices
        save `shareprices'
        
        use `dataset1', clear
        clonevar firm_id = cik
        joinby firm_id using `shareprices'
        rename spr spr_focal
        drop firm_id
        clonevar firm_id = peerCIK
        merge 1:1 firm_id mdat using `shareprices'
        rename spr spr_peer
        Note: -numdate- is written by Nick Cox and is available from SSC. It handles this date conversion more simply than using the official Stata functions.

        Now, with the examples you gave, there aren't actually any matches on the peer firms' CIK values in the two data sets, so no kind of regression would be possible from this example data. Presumably in your real data this is not the case, but you need to check that. When you are putting together data sets where the id variables were handled differently, as here, it is quite possible that there really won't be any mismatches and you will have to go back to the data sources to reconcile the discrepancies in representation of the same firms.

        Be that as it may, it is also not clear what you mean by regressing the focal returns on the peer returns. Do you want a separate regression for each focal firm? A separate regression for each month/year? A single regression for the whole data set? If the last, how do you plan to account for the non-independence of observations?

        Anyway, the above code should be a reasonable starting point for you.

        Comment


        • #5
          Thanks for the hints. I will try to implement them.
          And yes, I did import the spreadsheets, but indeed had problems with the variable names, so manually renamed them in the example.

          What I need is one regression for every focal firm and fiscal year combination.
          So one regression for:
          - CAMPBELL SOUP, 2014
          - CAMPBELL SOUP, 2015
          - CAMPBELL SOUP, 2016
          - APPLE, 2014
          - APPLE, 2015
          ...


          It has to be a multiple linear regression, with all the peers of the focal firm in that specific fiscal year. The period for the regression depends on the fiscal year (and fiscal month) of the focal firm.
          The first data set has a long shape. Should I reshape it to wide? But the number of peers is not constant...

          Thank you for your help.
          Freddy

          Comment


          • #6
            So first adapt the code in #2 to your data. Then with the resulting data set you can do this:

            Code:
            capture program drop one_regression
            program define one_regression
                regress spr_focal spr_peer
                matrix M = r(table)
                gen b = M[1, 1]
                gen se = M[2, 1]
                gen t = M[3,1]
                gen p = M[4, 1]
                gen lb = M[5, 1]
                gen ub = M[6, 1]
                gen n_obs = e(N)
                gen r_square = e(r2)
                exit
            end
            
            runby one_regression, by(cik fiscalyear)
            Note: -runby- is written by Robert Picard and me, and is available from SSC.

            The code shown here will store all of the regression output in new variables in your data set. If your data set is very large, you might want to specify the -status- option in the -runby- command; if you do so Stata will give you a periodic progress report as it works its way through the data. Not also, that in bulk regressions of this kind, it is common that some of the regressions will fail due to insufficient observations. If that arises in your data, Stata will simply skip over that one, generating no output for that particular focal firm and fiscal year, and move on to the next. The total number of failed regressions will be given in a summary report that -runby- displays at the end of all the runs.

            It has to be a multiple linear regression, with all the peers of the focal firm in that specific fiscal year.
            I don't see how it can be. You have said that you are regressing focal return on peer return. With only one predictor in the regression, that is by definition a single regression. Multiple regression means multiple predictor variables; it has nothing to do with the number of observations in the sample.

            The first data set has a long shape. Should I reshape it to wide?
            Definitely not. Nearly all data management and analysis in Stata is easier with long data. Whenever you import a new data set, one of the first things you should do, if it came to you in wide layout, is convert it to long. There are a small number of things that are easier to do with wide layout (certain types of graphs, ttest, ranksum), but you should only use wide layout when doing those things. Long layout is more difficult for human eyes to understand, but it is the natural one for Stata's data model. Stick with long except when you are in one of those uncommon situations where wide is required.

            Comment


            • #7
              Two things are missing. The first thing is the number of observations. The second data set has all monthly returns of the last ten years. But for the regression only the 5 years before the fiscal year (and fiscal month) of the focal firm should be used.
              And secondly, it should be a multiple regression. For the dataex in #3 the regression for CAMPBELL SOUP in 2014 should look like this:

              ReturnCAMPBELL SOUP, 2014,t = β0 + βGENERAL MILLS, 2014*ReturnGENERAL MILLS, 2014,t + βHERSHEY, 2014*ReturnHERSHEY, 2014,t + βHORMEL FOODS, 2014*ReturnHORMEL FOODS, 2014,t + ...

              and t ranging from July 2009- July 2014.

              How can I do this?

              Thank you.
              Freddy

              Comment


              • #8
                Well, the number of observations is not missing. It's there in the variable n_obs. But since these are the wrong regressions, everything needs to be revised. I did not understand what you were looking for. It is much clearer now.

                I think the following will work:

                Code:
                clear
                input str10 cik long grantId str10(peerCIK peerTicker) str80 peerName int fiscalyear byte fiscalmonth
                "0000016732" 372739 "0000040704" "GIS" "GENERAL MILLS INC" 2014 7
                "0000016732" 372739 "0000047111" "HSY" "HERSHEY CO" 2014 7
                "0000016732" 372739 "0000048465" "HRL" "HORMEL FOODS CORP" 2014 7
                "0000016732" 372739 "0000055067" "K" "KELLOGG CO" 2014 7
                "0000016732" 372739 "0000063754" "MKC" "MCCORMICK & CO INC" 2014 7
                "0000016732" 372739 "0000091419" "SJM" "SMUCKER (JM) CO" 2014 7
                "0000016732" 372739 "0000100493" "TSN" "TYSON FOODS INC -CL A" 2014 7
                "0000016732" 372739 "0000718053" "CCH.Z" "CAMPBELL RESOURCES INC" 2014 7
                "0000016732" 372739 "909954" "GMCR" "KEURIG GREEN MOUNTAIN INC" 2014 7
                "0000016732" 372739 "0001103982" "MDLZ" "MONDELEZ INTERNATIONAL INC" 2014 7
                "0000016732" 372739 "0001452575" "MJN" "MEAD JOHNSON NUTRITION CO" 2014 7
                "0000016732" 372739 "0001545158" "KRFT" "KRAFT FOODS GROUP INC" 2014 7
                "0001552000" 426861 "0001483096" "ACMP" "ACCESS MIDSTREAM PARTNERS LP" 2014 12
                "0001591763" 428062 "0001483096" "ACMP" "ACCESS MIDSTREAM PARTNERS LP" 2014 12
                end
                tempfile dataset1
                save `dataset1'
                
                clear
                input long cik str18(spr200801 spr200802 spr200803) str17(spr200804 spr200805) str18(spr200806 spr200807) str17 spr200808
                1137411 "-.122829" "-.06454408" "-.02971138" ".104287" "-.02389095" "-.2185107" ".03607173" ".06344198" 
                66382 "-.01883297" "-.05884946" "-.1763326" "-.05046805" ".06687129" ".003629032" ".05022097" ".07998626" 
                16732 "-.1167087" ".02313055" ".05140911" ".03175837" "-.03793103" "-.0005973716" ".09434109" ".01181968" 
                1551182 "-.1429603" "-.02373169" "-.01190624" ".102548" ".1068446" "-.1211212" "-.1580558" ".03012387" 
                1326428 "-.1322413" ".08297293" "-.1666667" ".1531579" ".06549326" ".09616233" "-.122334" ".023223" 
                896878 "-.02910471" "-.1345715" ".01694277" "-.001480933" ".07378569" "-.04799724" "-.008705114" ".1002561" 
                1166036 ".00651273" ".03216912" "-.106087" ".1575875" ".02691877" "-.01161952" "-.0550209" ".04205749" 
                end
                
                reshape long spr, i(cik) j(ym)
                numdate monthly mdate = ym, pattern("YM")
                drop ym
                rename cik firm_id
                tostring firm_id, replace format(%010.0f)
                destring spr, replace
                tempfile shareprices
                save `shareprices'
                
                use `dataset1', clear
                clonevar firm_id = cik
                joinby firm_id using `shareprices'
                rename spr spr_focal
                drop firm_id
                clonevar firm_id = peerCIK
                merge 1:1 firm_id mdat using `shareprices'
                rename spr spr_peer
                
                //    PREPARE DATA FOR RANGE RUN
                egen flag = tag(cik fiscalyear)
                gen latest = ym(fiscalyear, 7) if flag
                gen earliest = ym(fiscalyear-5, 7) if flag
                
                capture program drop one_regression
                program define one_regression
                    keep cik fiscalyear mdate peerCIK spr_*
                    reshape wide spr_peer, i(cik mdate) j(peerCIK) string
                    regress spr_focal spr_peer*
                    matrix M = r(table)
                    local peer_ciks: colvarlist M
                    local peer_ciks: subinstr local peer_ciks "spr_peer" "", all
                    local i = 1
                    foreach p of local peer_ciks {
                        if substr("`p'", 1, 2) != "o.") {
                            gen b_`p' = M[1, `i']
                            gen se_`p' = M[2, `i']
                            gen t_`p' = M[3, `i']
                            gen p_`p' = M[4, `i']
                            gen lb_`p' = M[5, `i']
                            gen ub_`p' = M[6, `i']
                        }
                        gen rsq = e(r2)
                        gen n_obs = e(N)
                        local ++i
                    }
                    exit
                end
                
                rangerun one_regression, interval(mdate earliest latest) by(cik fiscalyear) ///
                But because the example data sets do not actually have matches for peers, in this example you get no results. Consequently, this code is not tested and may contain errors. If you encounter problems with it in your real data, please post back with -dataex- output for example datasets that do contain matches so that some regressions will actually take place.

                To use this code you need to install -rangerun-, by Robert Picard and Nick Cox, from SSC. Also -rangerun- itself requires that you install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also from SSC.

                Note that in some instances you may get no regression results, or "weird" regression results. The former situation will arise if you have more peers for a given focal firm than there are months worth of non-missing data for the analysis. Remember that any observation (corresponding to a focal cik and fiscal month) that has a missing value on any of the share price returns will be omitted from the analysis. Remember also that a regression cannot be done with fewer observations than predictor variables. Now, Stata will begin to deal with this, because when the number of observations is smaller than the number of predictors, there is necessarily colinearity among the predictors. So Stata will omit as many as needs to to eliminate that colinearity. But it will chose them in a somewhat arbitrary way--from the perspective of model predictions this makes no difference. But it does mean that the coefficients, standard errors, etc., are not really meaningful in this situation. This code will show the results for the non-omitted variables, but interpret them at your own risk. This is what I mean by weird results. It may occasionally happen that there are no observations, or only a single observation, corresponding to a given focal cik and fiscal year. In that case, the regression is skipped altogether.

                The data set at the end will contain one observation per focal cik-fiscal year combination and include the regression results from those.

                Comment


                • #9
                  Thanks for your help.
                  I did run the program for the regressions. But where can I find the results of the regressions or save them in a excel file? I can't find them in my data set.

                  Comment


                  • #10
                    They should be in the data set, with names like b_0000040704, se_0000040704, ... ub_0000040704, rsq, and nobs. If they're not there, then the regressions did not in fact run. That may be due to a problem in your data or to a problem in my code. Please post back, using -dataex-, to show better examples of your data sets. In particular, there must be observations in the two data sets that actually pair up with each other, and there must be enough observations with non-missing data on the variables that the regressions can be done without omitting any of the predictors (that is, the number of months for which every peer and the focal cik have non-missing return values must be greater than the number of peers.) If you show data like that with just a few focal cik's and their corresponding peers, I will re-run it here and either verify that it works, or troubleshoot the code if it doesn't.

                    I will tell you that my experience with these iterated regression projects that produce no results is that the usual cause is just too much missing data leading to regressions failing. But in this case, the code for setting up the regressions is more complicated than the typical case and I may well have gotten it wrong. especially since the data available to me did not allow adequate testing.

                    Comment


                    • #11
                      A company can be a peer for several companies and also for several fiscal years. So how can it be possible to access the coefficients only with the cik of the peer? Shouldn't it be a combination of the focal firm's cik, its fiscal year, and the cik of the peer, like this:
                      - CAMPBELL SOUP, 2014: peer1, peer2, peer3, peer4 ...
                      - CAMPBELL SOUP, 2015: peer1, peer2, peer3, peer4 ...
                      - CAMPBELL SOUP, 2016: peer1, peer2, peer3, peer4 ...
                      - APPLE, 2014: peer1, peer2, peer3, peer4 ...
                      - APPLE, 2015: peer1, peer2, peer3, peer4 ...



                      Here a short data example of my dataset before running the regression:
                      (I had to change your code a little bit, because the month of "latest" and "earliest" is not constantly 7, it depends on "fiscalmonth")

                      clear
                      input long(cik grantId) str10(peerCIK peerTicker) str80 peerName int(Fiscalyearstart Startfiscalyear) byte fiscalmonth str30 Name_focal double spr_focal float mdate str30 Name_peer double spr_peer byte flag float(latest earliest)
                      1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc" -.122829 576 "AAR Corp" -.2227189 1 643 596
                      1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc" -.003097005 697 "AAR Corp" .05213738 0 . .
                      1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc" .04599313 646 "AAR Corp" .06625683 0 . .
                      1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc" -.01227639 659 "AAR Corp" .08388607 0 . .

                      Unfortunately I can't give you the data example you asked, since the second dataset has too many variables (monthly returns of 10 years). Therefore I upload the excel sheet with data for one regression.
                      Attached Files

                      Comment


                      • #12
                        OK. But you've made some changes to the code, so I need to have the code as you are trying to run it. Also, there is something different about this data. For example it no longer has a variable named fiscalyear; perhaps that is the one now called Startfiscalyear. But I can't effectively troubleshoot this when everything is changing. So show example data that is exactly like your real data in terms of variable names, storage types, everything. Make it an excerpt from your real data set, so that you don't inadvertently change anything. There is no such thing as an insignificant detail. And show the code as you have modified it.

                        Also, I do not download Excel spreadsheets from strangers; they can contain active malware, and I can't take that risk. If the data set is too wide for -dataex-, then please use Excel to save it as a CSV file and attach that.

                        Thanks.
                        Last edited by Clyde Schechter; 12 Jul 2018, 14:47.

                        Comment


                        • #13
                          Here the full code. I hope it is not too much, there are many variables, which are irrelevant for the regressions.
                          And also the csv file.

                          Code:
                          clear
                          input long(cik grantId) str10(peerCIK peerTicker) str80 peerName int fiscalyear byte fiscalmonth str12 component long participantID byte CEO long grantdate str11 metric int(Fiscalyearend Fiscalyearstart Startfiscalyear) byte Startfiscalmonth
                          1137411  375968 "0000001750" "AIR"   "AAR CORPORATION"              2014  9 "rsu"       128376 1   19673 "Stock Price" 19996 19631 2013  9
                            66382  467595 "706688"     ""      "AARONS INC."                  2015  5 "rsu"        24427 1   20282 "Stock Price" 20602 20236 2015  5
                            16732  372739 "0000040704" "GIS"   "GENERAL MILLS INC"            2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000047111" "HSY"   "HERSHEY CO"                   2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000048465" "HRL"   "HORMEL FOODS CORP"            2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000055067" "K"     "KELLOGG CO"                   2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000063754" "MKC"   "MCCORMICK & CO INC"           2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000091419" "SJM"   "SMUCKER (JM) CO"              2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000100493" "TSN"   "TYSON FOODS INC  -CL A"       2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0000718053" "CCH.Z" "CAMPBELL RESOURCES INC"       2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "909954"     "GMCR"  "KEURIG GREEN MOUNTAIN INC"    2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0001103982" "MDLZ"  "MONDELEZ INTERNATIONAL INC"   2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0001452575" "MJN"   "MEAD JOHNSON NUTRITION CO"    2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                            16732  372739 "0001545158" "KRFT"  "KRAFT FOODS GROUP INC"        2014  7 "rsu"        48168 1   19632 "Stock Price" 19932 19567 2013  7
                          1551182 1028069 "0001091587" "ABBN"  "ABB Ltd."                     2016 12 "rsu"        45706 1   20507 "Stock Price" 20819 20453 2015 12
                          1567892  481518 "0001551152" "ABBV"  "ABBVIE (NEW)"                 2016  9 "rsu"       204427 1   20457 "Stock Price" 20727 20361 2015  9
                           896878  373572 "0000008670" "ADP"   "AUTOMATIC DATA PROCESSING"    2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000108772" "XRX"   "XEROX CORP"                   2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000356028" "CA"    "CA INC"                       2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000712515" "EA"    "ELECTRONIC ARTS INC"          2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000718877" "ATVI"  "ACTIVISION BLIZZARD INC"      2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000721683" "TSS"   "TOTAL SYSTEM SERVICES INC"    2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000723531" "PAYX"  "PAYCHEX INC"                  2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000749251" "IT"    "GARTNER INC"                  2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000769397" "ADSK"  "AUTODESK INC"                 2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000796343" "ADBE"  "ADOBE SYSTEMS INC"            2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000798354" "FISV"  "FISERV INC"                   2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000813672" "CDNS"  "CADENCE DESIGN SYSTEMS INC"   2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000816761" "TDC"   "TERADATA CORP"                2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000849399" "SYMC"  "SYMANTEC CORP"                2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000877890" "CTXS"  "CITRIX SYSTEMS INC"           2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000883241" "SNPS"  "SYNOPSYS INC"                 2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0000891103" "IAC"   "IAC/INTERACTIVECORP"          2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001002638" "OTEX." "OPEN TEXT CORP"               2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001011006" "YHOO"  "YAHOO INC"                    2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001014473" "VRSN"  "VERISIGN INC"                 2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001058290" "CTSH"  "COGNIZANT TECH SOLUTIONS"     2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001065088" "EBAY"  "EBAY INC"                     2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001086222" "AKAM"  "AKAMAI TECHNOLOGIES INC"      2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001087423" "RHT"   "RED HAT INC"                  2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001101215" "ADS"   "ALLIANCE DATA SYSTEMS CORP"   2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001101239" "EQIX"  "EQUINIX INC"                  2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001102556" "NUAN." "NUANCE COMMUNICATIONS-OLD"    2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001107694" "RAX"   "RACKSPACE HOSTING INC"        2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001108524" "CRM"   "SALESFORCE.COM INC"           2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001123360" "GPN"   "GLOBAL PAYMENTS INC"          2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001124610" "VMW"   "VMWARE INC -CL A"             2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001136893" "FIS"   "FIDELITY NATIONAL INFO SVCS"  2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001141391" "MA"    "MASTERCARD INC"               2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001175454" "FLT"   "FLEETCOR TECHNOLOGIES INC"    2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001271024" "LNKD"  "LINKEDIN CORP"                2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001291080" "FIS.3" "FIDELITY NATL INFO SVC-REDH"  2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001365135" "WU"    "WESTERN UNION CO"             2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001383312" "BR"    "BROADRIDGE FINANCIAL SOLUTNS" 2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                           896878  373572 "0001597033" "SABR"  "SABRE CORP"                   2014  7 "rsu"       125992 1   19928 "Stock Price" 19935 19570 2013  7
                          1166036  400234 "0001483096" "ACMP"  "ACCESS MIDSTREAM PARTNERS LP" 2014 12 "cashShort"  70983 1 2936185 "Stock Price" 20088 19723 2013 12
                          1552000  426861 "0001483096" "ACMP"  "ACCESS MIDSTREAM PARTNERS LP" 2014 12 "unitCash"   92804 1   19783 "Stock Price" 20088 19723 2013 12
                          1591763  428062 "0001483096" "ACMP"  "ACCESS MIDSTREAM PARTNERS LP" 2014 12 "rsu"       238132 1   19876 "Stock Price" 20088 19723 2013 12
                             5272  391935 "0000896159" ""      "ACE LIMITED"                  2014 12 "rsu"       174667 1   19801 "Stock Price" 20088 19723 2013 12
                             5272  391935 "9999007"    ""      "ALLIANZ GROUP"                2014 12 "rsu"       174667 1   19801 "Stock Price" 20088 19723 2013 12
                             5272  437558 "0000896159" ""      "ACE LIMITED"                  2015 12 "rsu"       174667 1   20165 "Stock Price" 20453 20088 2014 12
                           944695  449810 "0000896159" ""      "ACE LIMITED"                  2015 12 "rsu"        40353 1   20145 "Stock Price" 20453 20088 2014 12
                           932628  373842 "0000805022" "BPL"   "BUCKEYE PARTNERS LP"          2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0000880285" "EEP"   "ENBRIDGE ENERGY PRTNRS  -LP"  2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0000909281" "OKS"   "ONEOK PARTNERS -LP"           2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0000922358" "FGP"   "FERRELLGAS PARTNERS  -LP"     2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0000932628" "APU"   "AMERIGAS PARTNERS  -LP"       2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001005210" "SPH"   "SUBURBAN PROPANE PRTNRS  -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001022321" "GEL"   "GENESIS ENERGY  -LP"          2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001061219" "EPD"   "ENTERPRISE PRODS PRTNRS  -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001070423" "PAA"   "PLAINS ALL AMER PIPELNE  -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001075607" "TCP"   "TC PIPELINES LP"              2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001086600" "ARLP"  "ALLIANCE RESOURCE PTNRS  -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001110805" "NS"    "NUSTAR ENERGY LP"             2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001126975" "MMP"   "MAGELLAN MIDSTREAM PRTNRS LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001136352" "CEQP"  "CRESTWOOD EQUITY PARTNERS LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001161154" "ETP"   "ENERGY TRANSFER PARTNERS -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001166036" "MWE"   "MARKWEST ENERGY PARTNERS LP"  2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001171486" "NRP"   "NATURAL RESOURCE PARTNERS LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001173911" "EEQ"   "ENBRIDGE ENERGY MGMT LLC"     2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001176334" "MMLP"  "MARTIN MIDSTREAM PARTNERS LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001223786" "NSH"   "NUSTAR GP HOLDINGS LLC"       2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001276187" "ETE"   "ENERGY TRANSFER EQUITY LP"    2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001283140" "HEP"   "HOLLY ENERGY PARTNERS LP"     2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001336047" "BWP"   "BOARDWALK PIPELINE PRTNRS-LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001338613" "RGP"   "REGENCY ENERGY PARTNERS LP"   2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001340122" "CLMT"  "CALUMET SPECIALTY PRODS  -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001344980" "AHGP"  "ALLIANCE HOLDINGS GP LP"      2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001358831" "LGCY"  "LEGACY RESERVES LP"           2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001373835" "SE"    "SPECTRA ENERGY CORP"          2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001379661" "NGLS"  "TARGA RESOURCES PARTNERS LP"  2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001384072" "VNRSQ" "VANGUARD NATURAL RESOURCES"   2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001407463" "PSE"   "PIONEER SOUTHWEST ENERGY -LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001410838" "EPB"   "EL PASO PIPELINE PARTNERS LP" 2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001481506" "PNG"   "PAA NATURAL GAS STORAGE LP"   2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001483096" "WPZ"   "WILLIAMS PARTNERS LP"         2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001483830" "NKA"   "NISKA GAS STORAGE PARTNERS"   2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           932628  373842 "0001506307" "KMI"   "KINDER MORGAN INC"            2014  9 "rsu"       279012 1   19724 "Stock Price" 19996 19631 2013  9
                           875159  435977 "0000896159" ""      "ACE LTD."                     2015 12 "rsu"        18438 1   20221 "Stock Price" 20453 20088 2014 12
                           874766  446454 "0000896159" ""      "ACE LTD."                     2015 12 "rsu"       174615 1   20150 "Stock Price" 20453 20088 2014 12
                          end
                          format %tdnn/dd/CCYY grantdate
                          format %tdnn/dd/CCYY Fiscalyearend
                          format %tdnn/dd/CCYY Fiscalyearstart
                          ------------------ copy up to and including the previous line ------------------

                          Listed 100 out of 10852 observations
                          Use the count() option to list more

                          . tempfile dataset1

                          . save `dataset1'
                          file /var/folders/sg/y8bgynjd1_v8vgzq2k1qh85m0000gp/T//S_08696.000001 saved

                          .
                          .
                          . import excel "/.../aktienrenditen test.xlsx", sheet("Sheet1") firstrow clear

                          . dataex
                          input statement exceeds linesize limit. Try specifying fewer variables
                          r(1000);

                          . reshape long spr, i(cik) j(ym)
                          (note: j = 20081 20082 20083 20084 20085 20086 20087 20088 20089 20091 20092 20093 20094 20095 20096 20097 2009
                          > 8 20099 20101 20102 20103 20104 20105 20106 20107 20108 20109 20111 20112 20113 20114 20115 20116 20117 20118
                          > 20119 20121 20122 20123 20124 20125 20126 20127 20128 20129 20131 20132 20133 20134 20135 20136 20137 20138
                          > 20139 20141 20142 20143 20144 20145 20146 20147 20148 20149 20151 20152 20153 20154 20155 20156 20157 20158 2
                          > 0159 20161 20162 20163 20164 20165 20166 20167 20168 20169 20171 20172 20173 20174 20175 20176 20177 20178 20
                          > 179 20181 20182 20183 20184 20185 20186 200810 200811 200812 200910 200911 200912 201010 201011 201012 201110
                          > 201111 201112 201210 201211 201212 201310 201311 201312 201410 201411 201412 201510 201511 201512 201610 201
                          > 611 201612 201710 201711 201712)

                          Data wide -> long
                          -----------------------------------------------------------------------------
                          Number of obs. 2197 -> 276822
                          Number of variables 130 -> 6
                          j variable (126 values) -> ym
                          xij variables:
                          spr20081 spr20082 ... spr201712 -> spr
                          -----------------------------------------------------------------------------

                          . numdate monthly mdate = ym, pattern("YM")
                          command numdate is unrecognized
                          r(199);

                          . ssc install numdate
                          checking numdate consistency and verifying not already installed...
                          installing into /.../Library/Application Support/Stata/ado/plus/...
                          installation complete.

                          . numdate monthly mdate = ym, pattern("YM")

                          . drop ym

                          . rename cik firm_id

                          . tostring firm_id, replace format(%010.0f)
                          firm_id was long now str10

                          . destring spr, replace
                          spr: contains nonnumeric characters; no replace

                          . clear

                          .
                          . import excel "/.../aktienrenditen test.xlsx", sheet("Sheet1") firstrow

                          . reshape long spr, i(cik) j(ym)
                          (note: j = 20081 20082 20083 20084 20085 20086 20087 20088 20089 20091 20092 20093 20094 20095 20096 20097 2009
                          > 8 20099 20101 20102 20103 20104 20105 20106 20107 20108 20109 20111 20112 20113 20114 20115 20116 20117 20118
                          > 20119 20121 20122 20123 20124 20125 20126 20127 20128 20129 20131 20132 20133 20134 20135 20136 20137 20138
                          > 20139 20141 20142 20143 20144 20145 20146 20147 20148 20149 20151 20152 20153 20154 20155 20156 20157 20158 2
                          > 0159 20161 20162 20163 20164 20165 20166 20167 20168 20169 20171 20172 20173 20174 20175 20176 20177 20178 20
                          > 179 20181 20182 20183 20184 20185 20186 200810 200811 200812 200910 200911 200912 201010 201011 201012 201110
                          > 201111 201112 201210 201211 201212 201310 201311 201312 201410 201411 201412 201510 201511 201512 201610 201
                          > 611 201612 201710 201711 201712)

                          Data wide -> long
                          -----------------------------------------------------------------------------
                          Number of obs. 2197 -> 276822
                          Number of variables 130 -> 6
                          j variable (126 values) -> ym
                          xij variables:
                          spr20081 spr20082 ... spr201712 -> spr
                          -----------------------------------------------------------------------------

                          . numdate monthly mdate = ym, pattern("YM")

                          . drop ym

                          . rename cik firm_id

                          .
                          . tostring firm_id, replace format(%010.0f)
                          firm_id was long now str10

                          . destring spr, replace
                          spr: contains nonnumeric characters; no replace

                          . destring spr, ignore("-"), gen(intspr)
                          invalid 'gen'
                          r(198);

                          . destring spr, ignore("-") gen(intspr)
                          spr: character - removed; intspr generated as double
                          (27545 missing values generated)

                          . gen tagnegspr = regexm(spr, "^-[. 0-9]")

                          . replace intspr = -intspr if tagnegspr==1
                          (110,762 real changes made)

                          . mdesc
                          command mdesc is unrecognized
                          r(199);

                          . ssc install mdesc
                          checking mdesc consistency and verifying not already installed...
                          installing into /.../Library/Application Support/Stata/ado/plus/...
                          installation complete.

                          . mdesc

                          Variable | Missing Total Percent Missing
                          ----------------+-----------------------------------------------
                          firm_id | 0 276,822 0.00
                          Securities | 0 276,822 0.00
                          BloombergT~r | 0 276,822 0.00
                          Name | 0 276,822 0.00
                          spr | 0 276,822 0.00
                          intspr | 27,545 276,822 9.95
                          mdate | 0 276,822 0.00
                          tagnegspr | 0 276,822 0.00
                          ----------------+-----------------------------------------------

                          . tempfile shareprices

                          . save `shareprices'
                          file /var/folders/sg/y8bgynjd1_v8vgzq2k1qh85m0000gp/T//S_08696.000002 saved

                          . dataex

                          ----------------------- copy starting from the next line -----------------------
                          Code:
                          * Example generated by -dataex-. To install: ssc install dataex
                          clear
                          input str10 firm_id str14 Securities str17 BloombergTicker str30 Name str18 spr double intspr float(mdate tagnegspr)
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.2227189"     -.2227189 576 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1241543"     -.1241543 577 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".05330243"     .05330243 578 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1419142"     -.1419142 579 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1760684"     -.1760684 580 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.2982365"     -.2982365 581 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".27051"           .27051 582 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.07678883"   -.07678883 583 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".04536862"     .04536862 584 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.01466594"   -.01466594 588 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.2712238"     -.2712238 589 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.05143722"   -.05143722 590 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".2017544"       .2017544 591 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02455209"   -.02455209 592 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".09183674"     .09183674 593 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".1919003"       .1919003 594 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1108207"     -.1108207 595 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".2898295"       .2898295 596 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".008268059"   .008268059 600 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02114804"   -.02114804 601 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".09435626"     .09435626 602 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.01772764"   -.01772764 603 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1919606"     -.1919606 604 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1502538"     -.1502538 605 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".003584229"   .003584229 606 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.08571428"   -.08571428 607 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".21484375"     .21484375 608 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02475428"   -.02475428 612 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01866368"     .01866368 613 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01575669"     .01575669 614 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.05779911"   -.05779911 615 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01344086"     .01344086 616 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".0265252"       .0265252 617 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".08568016"     .08568016 618 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1959782"     -.1959782 619 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.2933446"     -.2933446 620 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".109351"         .109351 624 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".04058518"     .04058518 625 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1723356"     -.1723356 626 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.149331"       -.149331 627 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.2200647"     -.2200647 628 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".1186722"       .1186722 629 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".06005442"     .06005442 630 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".0471499"       .0471499 631 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".1034946"       .1034946 632 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01288288"     .01288288 636 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.06790451"   -.06790451 637 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".04667046"     .04667046 638 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02474171"   -.02474171 639 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".1231803"       .1231803 640 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".09571286"     .09571286 641 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".1060733"       .1060733 642 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".03506601"     .03506601 643 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".08927859"     .08927859 644 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.04591607"   -.04591607 648 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".08442777"     .08442777 649 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1020761"     -.1020761 650 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".0009259422" .0009259422 651 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.06177606"   -.06177606 652 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".1341564"       .1341564 653 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02126137"   -.02126137 654 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".02973978"     .02973978 655 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1281588"     -.1281588 656 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".03440581"     .03440581 660 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".02581996"     .02581996 661 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".04421769"     .04421769 662 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.0126098"     -.0126098 663 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02314815"   -.02314815 664 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".0788761"       .0788761 665 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1520784"     -.1520784 666 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.09721706"   -.09721706 667 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.2203041"     -.2203041 668 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1979962"     -.1979962 672 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01332699"     .01332699 673 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".09300141"     .09300141 674 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".0362601"       .0362601 675 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01539101"     .01539101 676 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.0438345"     -.0438345 677 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".03835152"     .03835152 678 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".01862583"     .01862583 679 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".2726534"       .2726534 680 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02981392"   -.02981392 684 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".07596124"     .07596124 685 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02295177"   -.02295177 686 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".07240745"     .07240745 687 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.02917477"   -.02917477 688 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.005151688" -.005151688 689 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".07814518"     .07814518 690 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.03582888"   -.03582888 691 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".04769828"     .04769828 692 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".03188123"     .03188123 696 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".05213738"     .05213738 697 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".03593236"     .03593236 698 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.01666289"   -.01666289 699 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".03210162"     .03210162 700 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".04027747"     .04027747 701 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.03616637"   -.03616637 585 1
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".05941213"     .05941213 586 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" ".08677686"     .08677686 587 0
                          "0000001750" "AIR" "AIR Equity" "AAR Corp" "-.1061987"     -.1061987 597 1
                          end
                          format %tm mdate
                          ------------------ copy up to and including the previous line ------------------

                          Listed 100 out of 276822 observations
                          Use the count() option to list more


                          [/CODE]
                          Attached Files

                          Comment


                          • #14
                            Part 2
                            Code:
                            . use `dataset1', clear
                            
                            .
                            . clonevar firm_id = cik
                            (3 missing values generated)
                            
                            .
                            . tostring firm_id, replace format(%010.0f)
                            firm_id was long now str10
                            
                            . drop if cik == .
                            (3 observations deleted)
                            
                            . joinby firm_id using `shareprices'
                            
                            . rename spr stringspr
                            
                            . rename intspr spr
                            
                            . rename spr spr_focal
                            
                            . rename stringspr stringspr_focal
                            
                            . drop firm_id
                            
                            . drop Securities
                            
                            . drop BloombergTicker
                            
                            . drop stringspr_focal
                            
                            . drop tagnegspr
                            
                            . rename Name Name_focal
                            
                            . clonevar firm_id = peerCIK
                            
                            . joinby firm_id mdate using `shareprices'
                            
                            .
                            . . drop Securities
                            
                            .
                            . . drop BloombergTicker
                            
                            .
                            . . drop tagnegspr
                            
                            .
                            . drop spr
                            
                            . rename intspr spr_peer
                            
                            . drop firm_id
                            
                            . rename Name Name_peer
                            
                            . egen flag = tag(cik fiscalyear)
                            
                            .
                            . gen latest = ym(Startfiscalyear, Startfiscalmonth-1) if flag
                            (1,271,384 missing values generated)
                            
                            . replace latest = ym(Startfiscalyear, Startfiscalmonth-1) if flag & latest = .
                            invalid syntax
                            r(198);
                            
                            .  replace latest = ym(Startfiscalyear if Startfiscalmonth >= 1 else Startfiscalyear-1, Startfiscalmonth-1 if S
                            > tartfiscalmonth >= 1 else 12) if flag
                            1ifStartfiscalmonth invalid name
                            r(198);
                            
                            . replace latest = ym(Startfiscalyear, Startfiscalmonth-1) if flag & latest = "."
                            invalid syntax
                            r(198);
                            
                            . replace latest = ym(Startfiscalyear, Startfiscalmonth-1) if flag & latest == .
                            (0 real changes made)
                            
                            . replace latest = ym(Startfiscalyear-1, 12) if flag & latest = .
                            invalid syntax
                            r(198);
                            
                            . replace latest = ym(Startfiscalyear-1, 12) if flag & latest == .
                            (16 real changes made)
                            
                            . mdesc
                            
                                Variable    |     Missing          Total     Percent Missing
                            ----------------+-----------------------------------------------
                                        cik |           0      1,271,970           0.00
                                    grantId |           0      1,271,970           0.00
                                    peerCIK |           0      1,271,970           0.00
                                 peerTicker |      36,540      1,271,970           2.87
                                   peerName |           0      1,271,970           0.00
                                 fiscalyear |           0      1,271,970           0.00
                                fiscalmonth |           0      1,271,970           0.00
                                  component |           0      1,271,970           0.00
                               participan~D |           0      1,271,970           0.00
                                        CEO |           0      1,271,970           0.00
                                  grantdate |           0      1,271,970           0.00
                                     metric |           0      1,271,970           0.00
                               Fiscalyear~d |           0      1,271,970           0.00
                               Fiscalyear~t |           0      1,271,970           0.00
                               Startfisca~r |           0      1,271,970           0.00
                               Startfisca~h |           0      1,271,970           0.00
                                 Name_focal |           0      1,271,970           0.00
                                  spr_focal |      72,232      1,271,970           5.68
                                      mdate |           0      1,271,970           0.00
                                  Name_peer |           0      1,271,970           0.00
                                   spr_peer |      65,985      1,271,970           5.19
                                       flag |           0      1,271,970           0.00
                                     latest |   1,271,368      1,271,970          99.95
                            ----------------+-----------------------------------------------
                            
                            . gen earliest = ym(fiscalyear-5, Startfiscalmonth) if flag
                            (1,271,368 missing values generated)
                            
                            . mdesc
                            
                                Variable    |     Missing          Total     Percent Missing
                            ----------------+-----------------------------------------------
                                        cik |           0      1,271,970           0.00
                                    grantId |           0      1,271,970           0.00
                                    peerCIK |           0      1,271,970           0.00
                                 peerTicker |      36,540      1,271,970           2.87
                                   peerName |           0      1,271,970           0.00
                                 fiscalyear |           0      1,271,970           0.00
                                fiscalmonth |           0      1,271,970           0.00
                                  component |           0      1,271,970           0.00
                               participan~D |           0      1,271,970           0.00
                                        CEO |           0      1,271,970           0.00
                                  grantdate |           0      1,271,970           0.00
                                     metric |           0      1,271,970           0.00
                               Fiscalyear~d |           0      1,271,970           0.00
                               Fiscalyear~t |           0      1,271,970           0.00
                               Startfisca~r |           0      1,271,970           0.00
                               Startfisca~h |           0      1,271,970           0.00
                                 Name_focal |           0      1,271,970           0.00
                                  spr_focal |      72,232      1,271,970           5.68
                                      mdate |           0      1,271,970           0.00
                                  Name_peer |           0      1,271,970           0.00
                                   spr_peer |      65,985      1,271,970           5.19
                                       flag |           0      1,271,970           0.00
                                     latest |   1,271,368      1,271,970          99.95
                                   earliest |   1,271,368      1,271,970          99.95
                            ----------------+-----------------------------------------------
                            
                            .
                            
                            . ssc install rangerun
                            checking rangerun consistency and verifying not already installed...
                            installing into /.../Library/Application Support/Stata/ado/plus/...
                            installation complete.
                            
                            
                            . capture program drop one_regression
                            
                            . program define one_regression
                              1.     keep cik fiscalyear mdate peerCIK spr_*
                              2.     reshape wide spr_peer, i(cik mdate) j(peerCIK) string
                              3.     regress spr_focal spr_peer*
                              4.     matrix M = r(table)
                              5.     local peer_ciks: colvarlist M
                              6.     local peer_ciks: subinstr local peer_ciks "spr_peer" "", all
                              7.     local i = 1
                              8.     foreach p of local peer_ciks {
                              9.         if substr("`p'", 1, 2) != "o.") {
                             10.             gen b_`p' = M[1, `i']
                             11.             gen se_`p' = M[2, `i']
                             12.             gen t_`p' = M[3, `i']
                             13.             gen p_`p' = M[4, `i']
                             14.             gen lb_`p' = M[5, `i']
                             15.             gen ub_`p' = M[6, `i']
                             16.         }
                             17.         gen rsq = e(r2)
                             18.         gen n_obs = e(N)
                             19.         local ++i
                             20.     }
                             21.     exit
                             22. end
                            
                            .
                            . rangerun one_regression, interval(mdate earliest latest) by(cik fiscalyear) ///
                            This command requires rangestat (from SSC).
                            To install rangestat, click on the link below
                            ssc install rangestat
                            r(199);
                            
                            . ssc install rangestat
                            checking rangestat consistency and verifying not already installed...
                            installing into /.../Library/Application Support/Stata/ado/plus/...
                            installation complete.
                            
                            
                            . capture program drop one_regression
                            
                            . program define one_regression
                              1.     keep cik fiscalyear mdate peerCIK spr_*
                              2.     reshape wide spr_peer, i(cik mdate) j(peerCIK) string
                              3.     regress spr_focal spr_peer*
                              4.     matrix M = r(table)
                              5.     local peer_ciks: colvarlist M
                              6.     local peer_ciks: subinstr local peer_ciks "spr_peer" "", all
                              7.     local i = 1
                              8.     foreach p of local peer_ciks {
                              9.         if substr("`p'", 1, 2) != "o.") {
                             10.             gen b_`p' = M[1, `i']
                             11.             gen se_`p' = M[2, `i']
                             12.             gen t_`p' = M[3, `i']
                             13.             gen p_`p' = M[4, `i']
                             14.             gen lb_`p' = M[5, `i']
                             15.             gen ub_`p' = M[6, `i']
                             16.         }
                             17.         gen rsq = e(r2)
                             18.         gen n_obs = e(N)
                             19.         local ++i
                             20.     }
                             21.     exit
                             22. end
                            
                            .
                            . rangerun one_regression, interval(mdate earliest latest) by(cik fiscalyear)
                              (using rangestat version 1.1.1)
                            .
                            
                            . drop grantdate
                            
                            . drop component
                            
                            . drop participantID
                            
                            . drop CEO
                            
                            . drop metric
                            
                            
                            . dataex
                            input statement exceeds linesize limit. Try specifying fewer variables
                            r(1000);
                            
                            . drop Fiscalyearend
                            
                            . dataex
                            input statement exceeds linesize limit. Try specifying fewer variables
                            r(1000);
                            
                            . drop fiscalmonth
                            
                            . drop fiscalyear
                            
                            . dataex
                            
                            ----------------------- copy starting from the next line -----------------------
                            
                            
                            Code:
                            * Example generated by -dataex-. To install: ssc install dataex
                            clear
                            input long(cik grantId) str10(peerCIK peerTicker) str80 peerName int(Fiscalyearstart Startfiscalyear) byte Startfiscalmonth str30 Name_focal double spr_focal float mdate str30 Name_peer double spr_peer byte flag float(latest earliest)
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     -.122829 576 "AAR Corp"   -.2227189 1 643 596
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"  -.003097005 697 "AAR Corp"   .05213738 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .04599313 646 "AAR Corp"   .06625683 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.01227639 659 "AAR Corp"   .08388607 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.08930408 605 "AAR Corp"   -.1502538 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.0278542 631 "AAR Corp"    .0471499 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02176628 652 "AAR Corp"  -.06177606 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.01230102 622 "AAR Corp"  -.08379328 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.07569707 586 "AAR Corp"   .05941213 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"  -.006107587 678 "AAR Corp"   .03835152 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"  .0004314064 683 "AAR Corp"   -.1040932 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .1749387 591 "AAR Corp"    .2017544 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .1039194 682 "AAR Corp"    .1467205 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .03607173 582 "AAR Corp"      .27051 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02069498 641 "AAR Corp"   .09571286 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02756164 616 "AAR Corp"   .01344086 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.03607061 588 "AAR Corp"  -.01466594 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02906817 667 "AAR Corp"  -.09721706 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     -.227906 585 "AAR Corp"  -.03616637 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .06289405 601 "AAR Corp"  -.02114804 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02501701 695 "AAR Corp"  -.05507455 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .0838572 662 "AAR Corp"   .04421769 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .06344198 583 "AAR Corp"  -.07678883 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02535459 651 "AAR Corp" .0009259422 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .01349432 660 "AAR Corp"   .03440581 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .01379901 690 "AAR Corp"   .07814518 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .01636189 647 "AAR Corp"   -.1028187 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02597913 637 "AAR Corp"  -.06790451 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .08010384 608 "AAR Corp"   .21484375 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .0872783 673 "AAR Corp"   .01332699 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.0290132 627 "AAR Corp"    -.149331 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02025015 629 "AAR Corp"    .1186722 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02917861 626 "AAR Corp"   -.1723356 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc" -.0002371354 681 "AAR Corp"   .02957627 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.01138664 653 "AAR Corp"    .1341564 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .0122056 636 "AAR Corp"   .01288288 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .07585168 606 "AAR Corp"  .003584229 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.0697119 610 "AAR Corp"    .1143376 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .04615385 590 "AAR Corp"  -.05143722 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02058032 701 "AAR Corp"   .04027747 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.06232403 654 "AAR Corp"  -.02126137 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .09691386 595 "AAR Corp"   -.1108207 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"  -.007129276 679 "AAR Corp"   .01862583 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.00259443 692 "AAR Corp"   .04769828 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   .009160805 617 "AAR Corp"    .0265252 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02185205 694 "AAR Corp"   .06916945 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02083938 698 "AAR Corp"   .03593236 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .04551201 624 "AAR Corp"     .109351 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .07197452 657 "AAR Corp"    .1007248 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"  -.001623643 643 "AAR Corp"   .03506601 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.03687783 677 "AAR Corp"   -.0438345 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"  -.003168568 639 "AAR Corp"  -.02474171 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .03741106 693 "AAR Corp"   .03137246 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .03850455 603 "AAR Corp"  -.01772764 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.07918296 619 "AAR Corp"   -.1959782 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02156102 684 "AAR Corp"  -.02981392 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02971138 578 "AAR Corp"   .05330243 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .2333982 691 "AAR Corp"  -.03582888 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   .008078716 663 "AAR Corp"   -.0126098 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.05241407 607 "AAR Corp"  -.08571428 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .05007486 638 "AAR Corp"   .04667046 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.00410013 671 "AAR Corp"   .07043974 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.01870112 664 "AAR Corp"  -.02314815 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .01999462 658 "AAR Corp"  -.03283019 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02472138 630 "AAR Corp"   .06005442 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.09359187 628 "AAR Corp"   -.2200647 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .04558066 620 "AAR Corp"   -.2933446 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.06454408 577 "AAR Corp"   -.1241543 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .03554059 599 "AAR Corp"    .2315112 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02218615 648 "AAR Corp"  -.04591607 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .03924367 611 "AAR Corp"    .1184853 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.03640532 689 "AAR Corp" -.005151688 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .01126288 594 "AAR Corp"    .1919003 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.0837033 666 "AAR Corp"   -.1520784 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.1069865 618 "AAR Corp"   .08568016 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .05499774 655 "AAR Corp"   .02973978 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   .006052142 614 "AAR Corp"   .01575669 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .07313349 634 "AAR Corp"   .01789264 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   .006080046 676 "AAR Corp"   .01539101 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .1131919 592 "AAR Corp"  -.02455209 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"      .104287 579 "AAR Corp"   -.1419142 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.0855676 584 "AAR Corp"   .04536862 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.01713014 699 "AAR Corp"  -.01666289 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .07132565 687 "AAR Corp"   .07240745 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    -.2185107 581 "AAR Corp"   -.2982365 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .05298618 674 "AAR Corp"   .09300141 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .05962854 669 "AAR Corp"    .2000861 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .09668326 649 "AAR Corp"   .08442777 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.02983507 665 "AAR Corp"    .0788761 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.01626591 593 "AAR Corp"   .09183674 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02836627 625 "AAR Corp"   .04058518 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .02903036 645 "AAR Corp"   .07420051 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc" -.0001221747 668 "AAR Corp"   -.2203041 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   .008265402 613 "AAR Corp"   .01866368 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"      .112118 602 "AAR Corp"   .09435626 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"     .1009269 612 "AAR Corp"  -.02475428 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"      .147007 587 "AAR Corp"   .08677686 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   .007766758 680 "AAR Corp"    .2726534 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"    .04003066 700 "AAR Corp"   .03210162 0   .   .
                            1137411 375968 "0000001750" "AIR" "AAR CORPORATION" 19631 2013 9 "Rockwell Collins Inc"   -.09907673 604 "AAR Corp"   -.1919606 0   .   .
                            end
                            format %tdnn/dd/CCYY Fiscalyearstart
                            format %tm mdate
                            ------------------ copy up to and including the previous line ------------------ Listed 100 out of 1271970 observations Use the count() option to list more . tempfile regressiondata . . save `regressiondata' file /var/folders/sg/y8bgynjd1_v8vgzq2k1qh85m0000gp/T//S_08696.000003 saved

                            Comment


                            • #15
                              OK. Thanks for trying.

                              I started with your final -dataex- that I think represents your data after you join and merge the two starting data sets and have the data paired up and ready to regress. Now, that example contains only one cik and one peer cik, so it's not much of a test of the code. So I expanded it to include another cik and another peer cik for a bit more rigorous of a test. Also, the spr's for these additional observations are the original numbers with some random noise added to make sure that one of the peer cik's spr didn't drop out due to colinearity. With that done, I ran the code and found a couple of errors in program one_regression. One was a typo, and the other was a logical error. I have fixed those, and now have code that produces credible results.

                              Due to the Forum software's limitation on the length of a post (which you apparently encountered, too), and the necessarily large size of the example data, I am attaching the code (including the -dataex- output of the test data) as a do-file.
                              panakkal.do

                              Comment

                              Working...
                              X