Announcement

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

  • How to stack daily stock price data

    Dear Stata user

    I would like to ask about how to stack daily stock price data.

    I have use command

    "stack TLKMIJEquity HMSPIJEquity"
    but the result is not like I intended, because I lost the variable date.


    The table below is my data
    DATE TLKM IJ Equity HMSP IJ Equity
    04/01/2000 0.089167945 0.048009219
    05/01/2000 -0.063591103 -0.048009219
    06/01/2000 0.029852963 0.016260521
    07/01/2000 0 0
    10/01/2000 0 0
    11/01/2000 0.014598799 -0.016260521
    12/01/2000 -0.014598799 -0.016529302
    13/01/2000 0.004889985 0.016529302
    14/01/2000 0.019324273 0.032260862
    17/01/2000 0.009523882 0.090971778
    18/01/2000 -0.01913934 -0.075223421

    and below is table that I want to generate:
    date company_name stockprice
    04/01/2000 TLKM IJ Equity 0.089167945
    05/01/2000 TLKM IJ Equity -0.063591103
    06/01/2000 TLKM IJ Equity 0.029852963
    07/01/2000 TLKM IJ Equity 0
    10/01/2000 TLKM IJ Equity 0
    11/01/2000 TLKM IJ Equity 0.014598799
    12/01/2000 TLKM IJ Equity -0.014598799
    13/01/2000 TLKM IJ Equity 0.004889985
    14/01/2000 TLKM IJ Equity 0.019324273
    17/01/2000 TLKM IJ Equity 0.009523882
    18/01/2000 TLKM IJ Equity -0.01913934
    04/01/2000 HMSP IJ Equity 0.048009219
    05/01/2000 HMSP IJ Equity -0.048009219
    06/01/2000 HMSP IJ Equity 0.016260521
    07/01/2000 HMSP IJ Equity 0
    10/01/2000 HMSP IJ Equity 0
    11/01/2000 HMSP IJ Equity -0.016260521
    12/01/2000 HMSP IJ Equity -0.016529302
    13/01/2000 HMSP IJ Equity 0.016529302
    14/01/2000 HMSP IJ Equity 0.032260862
    17/01/2000 HMSP IJ Equity 0.090971778
    18/01/2000 HMSP IJ Equity -0.075223421
    Thank you in advance for any help

    Anzas

    Last edited by anzas rustamaji pratama; 25 Jul 2017, 13:17.

  • #2
    This is a job for -reshape-, not -stack-.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float(date tlkmijequity hmspijequity)
    14613  .08916795   .04800922
    14614  -.0635911  -.04800922
    14615  .02985296  .016260521
    14616          0           0
    14619          0           0
    14620   .0145988 -.016260521
    14621  -.0145988 -.016529301
    14622 .004889985  .016529301
    14623 .019324273   .03226086
    14626 .009523882   .09097178
    14627 -.01913934  -.07522342
    end
    format %td date
    
    reshape long @equity, i(date) j(company_name) string
    rename equity stock_price
    -reshape- is one of the most important of Stata's data management commands. Do read the entire manual section on it. It has good explanations of how it works and lots of worked examples. It takes some time to develop an instinct about what goes in i() and what goes in j(), but after you do it a certain number of times, it will suddenly "click" in your brain and become second nature.

    As an aside, in the future, please always post example data using the -dataex- command, as I have done here. Run -ssc install dataex- to get the command, and run -help dataex- to read the simple instructions for using it. The particular data display you used was not, in this instance, particularly difficult to bring into Stata. But often they are. -dataex- makes it possible for those who want to help you to make a completely faithful replica of your Stata example with just a simple copy/paste operation.

    Another aside: because of the way you displayed the data, I cannot tell if your date variable is a Stata internal format date variable or a string variable that looks like a date to human eyes. In my solution, it is the former. It does not actually matter for the purposes of this particular problem. But almost no matter what you want to do beyond this point, you will need date to be a numeric Stata internal format variable. So if it is, in fact, a string (or worse, just a value-labeled numeric variable that displays as a date but is some other kind of number) you need to convert it. See -help datetimes- if you are not familiar with doing that.

    Comment


    • #3
      thanks for the help Clyde! it's work

      Much appreciated

      Comment

      Working...
      X