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

[Alberto Bolzoni]

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

[Carlo Lazzaro]

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.

[Alberto Bolzoni]

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

* 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

[Mike Lacy]

See -help return list-, -help putexcel- (especially "returned results"), -help tabstat-.

/ // 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

[Alberto Bolzoni]

See -help return list-, -help putexcel- (especially "returned results"), -help tabstat-.

/ // 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?

[Mike Lacy]

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:

putexcel A21 = matrix(r(StatTotal)), names

[Alberto Bolzoni]

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:

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:

* 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")?