Announcement

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

  • New user: how to generate variance covariance matrix and to output in excel

    Good morning,

    I am a new user of Stata so thank you in advance for your support.
    I have a time series of last daily prices for some currencies (in excel Currencies Dataset).
    As you can see in the txt file I have uploaded the data in Stata and then I have generated the return and (hopefully) the variances and standard deviations.

    Now, first I need to build the correlation matrix and the variance covariance matrix.
    Second, is there a way to output the standard deviation, the variance-covariance matrix and the correlation matrix in excel?

    Hope to have described well the problem.
    Best regards,
    Alberto Bolzoni
    Attached Files

  • #2
    Alberto:
    see -help correlate-.
    Perhaps user-written programmes, such as -estab- (type -search estab-) or -estout- (type -search estout-)can help you out in exporting your results to spreadsheets.
    As an aside, please do not attach spreadsheets, as most of the regular contributors of this forum (me, too) are not willing to download them, to to the risk of active contents; rather, see the FAQ on how to share what you typed and what Stata gave you back. Thanks.
    Kind regards,
    Carlo
    (Stata 19.0)

    Comment


    • #3
      Thank you Carlo,
      As your recomandation here it is the code using dataex:

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input int date double(EURUSD EURGBP EURCAD USDSAR)
      18759 1.4245999999999999 .87444            1.37148             3.7502
      18760             1.4119 .87171             1.3676             3.7503
      18763             1.4156  .8743            1.38141 3.7500999999999998
      18764             1.4237 .87601            1.38411             3.7502
      18765              1.425 .88132 1.3822999999999999 3.7500999999999998
      18766             1.4309 .88139            1.38466             3.7502
      18767 1.4161000000000001 .87245            1.37904             3.7502
      18770             1.4048 .87135            1.37385             3.7502
      18771               1.41 .87141            1.37724             3.7502
      18772             1.4088 .86569            1.37686             3.7502
      18773 1.4144999999999999 .86226            1.38308 3.7500999999999998
      18774             1.4319 .86732            1.39774 3.7500999999999998
      18777             1.4282  .8669 1.3954900000000001             3.7502
      18778             1.4396 .87529            1.39442             3.7502
      18779             1.4328 .87724            1.40036             3.7502
      18780             1.4491 .88515             1.4143             3.7502
      18781             1.4635 .89104            1.43163             3.7502
      18784             1.4576 .89114            1.42987             3.7502
      18785             1.4691  .8932            1.43176 3.7500999999999998
      18786             1.4583   .889             1.4282 3.7500999999999998
      18787              1.451 .88648            1.41188             3.7502
      18788 1.4346999999999999 .88427 1.4058899999999999 3.7500999999999998
      18791             1.4413 .88012 1.4068399999999999 3.7500999999999998
      18792              1.444 .88212            1.39812             3.7502
      18793             1.4181 .87562            1.38838 3.7500999999999998
      18794 1.4203999999999999 .87909            1.39384 3.7500999999999998
      18795             1.4306 .88373            1.40146 3.7500999999999998
      18798 1.4304000000000001 .88287 1.4015900000000001 3.7500999999999998
      18799             1.4412 .88715            1.39965             3.7502
      18800             1.4357 .89319 1.3979300000000001             3.7502
      end
      format %tdnn/dd/CCYY date
      Hope this could help.

      Kind regards,
      Alberto

      Comment


      • #4
        See -help return list-, -help putexcel- (especially "returned results"), -help tabstat-.
        Code:
        / // Establish Excel file
        putexcel set "c:/temp/YourFileName.xlsx"
        // I'm assuming that 10 rows in Excel is enough space for your matrices.
        corr EURUSD EURGBP EURCAD USDSAR
        putexcel A1 = matrix(r(C)), names
        corr EURUSD EURGBP EURCAD USDSAR, covar
        putexcel A11 = matrix(r(C)), names
        tabstat EURUSD EURGBP EURCAD USDSAR, statistics(sd) save
        putexcel A21 = matrix(r(StatTotal))
        putexcel A21 = matrix(SD), names

        Comment


        • #5
          Originally posted by Mike Lacy View Post
          See -help return list-, -help putexcel- (especially "returned results"), -help tabstat-.
          Code:
          / // Establish Excel file
          putexcel set "c:/temp/YourFileName.xlsx"
          // I'm assuming that 10 rows in Excel is enough space for your matrices.
          corr EURUSD EURGBP EURCAD USDSAR
          putexcel A1 = matrix(r(C)), names
          corr EURUSD EURGBP EURCAD USDSAR, covar
          putexcel A11 = matrix(r(C)), names
          tabstat EURUSD EURGBP EURCAD USDSAR, statistics(sd) save
          putexcel A21 = matrix(r(StatTotal))
          putexcel A21 = matrix(SD), names
          Mike,
          Thank you in advance for your support, the code you sent works perfectly.
          Just a quick one: the last line produce an error saying SD not found, can you help me out with this?

          Comment


          • #6
            I'm sorry, the last line was a mistake, leftover from when I was approaching your problem by first saving returned results in matrices (e.g., one called "SD") rather than using the returned results directly. So, remove the last line, and make the next to last line to be:
            Code:
            putexcel A21 = matrix(r(StatTotal)), names

            Comment


            • #7
              Originally posted by Mike Lacy View Post
              I'm sorry, the last line was a mistake, leftover from when I was approaching your problem by first saving returned results in matrices (e.g., one called "SD") rather than using the returned results directly. So, remove the last line, and make the next to last line to be:
              Code:
              putexcel A21 = matrix(r(StatTotal)), names
              Thank you so much, it is working perfectly.

              Now, what if I want to upload another dataset (mantaining the previous one) with this structure:

              Code:
              * Example generated by -dataex-. To install: ssc install dataex
              clear
              input long(exp_EURUSD exp_EURGBP exp_EURCAD exp_USDSAR)
              100000000 15000000 -36000000 270000000
              end
              First of all, is it possible (I tried to upload from an othor excel file but there is an error saying "no; data in memory would be lost")?

              Comment

              Working...
              X