Export covariance matrix to Excel

[Francois Durant]

Hi,

I would like to export the following covariance matrix to excel:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double(r_EPS r_DGRO r_MGK r_IJH r_VOE r_VOT r_VO r_VBR r_VBK)
20356    -.02385333333333332   -.013421480878763162   -.02335984095427447  -.020722823396279642   -.031949250288350585     -.0317242729854107    -.03221311475409842    -.03043177551625332   -.04435133212111629
20363    .009561404706942984    .016078284213261008   .009541984732824428  -.001372237469305198   .0064339330394375316   .0023258165638588733    .004573558058778744  -.0015285845307244624  -.007528127068166749
20370    .040138364609688254     .03735795454545457    .02848141146817902   .041368337311058066     .04036936190363439     .02683615819209036     .03355534946463202     .05113288426209421    .03942652329749107
20377   .0066512597936981156   .0035014964495998697   .011640730302659023  -.005625390652128636  -.0009103322712789974  -.0007860090391039329  -.0008157272208174282   -.005049033886785086  -.005773857257417793
20384    .020816911964232635    .020740740740740744   .025678294573643467   .004400055873725349    .009567198177676577    .001081612586037359    .006204588129643278    .008197521225724636 -.0054040974350701865
20391    .004641350210970441 -.00007638835841413535   .008384506376948438  .0027814477435505573  -.0016922382671480786    .008938218249680744    .003164300202839761 -.00038718420288458283  -.000162192847295402
20398   .0035279294414111563   .0015278838808250247   .009368778545497097   .013799320435475907    .005198327494632252    .006911993769470483    .006632157877709424    .019269875084729438   .025387298239922095
20405    -.03466979157947596    -.02784134248665143   -.03817148161039555  -.038987688098495135    -.03338954468802697    -.04283090012568894    -.03872730194439975     -.0428462853885617    -.0423524758740706
20412    .032997190621531586     .02788936837975686   .039203860072376355   .030249110320284697    .028262386601535152     .03161616161616157    .030508191240387877    .026203473945409436    .02770559480877346
20419     .00223698623812782   .0037784961585289128 -.0004643064422519588   .014853195164076032    .007352109489876775    .011269950063644478    .009003163273582604    .015475384466582786   .020736216042436893
20426   -.004158291457286412  -.0018973456172837518  -.002090349552897371  -.012661674608577356   -.002357960925219043   -.007658717479497788   -.005225080385852136   -.013810839127535848  -.014094488188976427
20433    -.03773648378727292    -.03428571428571423  -.038752472943093356  -.040402647545504586    -.04355655599324694    -.03688164698995025   -.040565656565656534   -.047324705427854025   -.04145036338950561
20440  -.0029104193888118213  -.0008796844181460085  -.011622276029055615     -.011567753987642   -.006119086843963405   -.005774490933036098   -.005895729807125434   -.010036496350364911 -.0004999166805532601
20447    .025126332905284272    .023385463346454735   .024375306222439917   .026023115504833993    .028771015865498545    .022416955369879792    .025925612132508703    .026728110599078335   .023257752584194786
20454   -.011543394613082495   -.008873456790123472  -.007054884610785474   -.01296493092454844   -.010818275981125535   -.006278652581223935   -.008093153852506432   -.014861360462796819  -.010672097759674154
20461    -.05247837370242216    -.05488516932658622   -.06021194605009633   -.06352282515073208    -.06422338568935422    -.06318323137097581    -.06435767213387732    -.06580945631264554   -.07419301712779966
20468   -.031040731830901034   -.014579901153212633   -.02280881599179909  -.028588947650800983    -.03356956359567329   -.037041002034043397    -.03532657056415731    -.03338029695458977   -.04011384861691723
20475     .00973302491673214    .012287887653598617    .02216102806189349   .013649992109831182    .006432522835456066     .01678710394663692     .01106908956738311    .009193855813432083   .016771682727946647
20482     .01710889130465218    .025185796862097418   .005901218729955019   .023818790379076846    .023264732199923213    .003936146949486108    .012407627041328341     .02744139540051104   .007290622436890523
20489   -.030091743119266164    -.01852597664115978   -.04055605152404021    -.0294229453356649   -.020112429731417858    -.04051404922674798   -.027755249166441367   -.025843425605536336   -.04840314846647964
20496   -.006181422625803887   -.004103405826836332  -.007045061810447974   -.01284662384458719   -.017465578786333563   -.011464245175936333   -.015015293354342428    -.01576201576201578  -.025194903974139624
20503    .030142814312839876    .027606098063452895    .03279785809906296   .034994445326138796     .03438432593745953    .042255138362613305     .03811047332266865      .0319160933799481     .0488637471959427
20510     .01924542770953552    .011226944667201186    .01814646791963696   .026987656214061042    .024962368289011477     .02974661231684484     .02755620014503269    .027213114754098305    .03273200669518315
20517    .026741239977700518     .02339413164155432    .02253341820496494    .04471817842478544     .03695998041855355     .03284365802540068     .03475652787579391     .05096286839025435   .042139384116693615
20524    .011331682359744433     .01123595505617988   .009337649402390439   .005859654137487446    .015106809866635207    .012844416822042622    .014151747655583944    .008604980765337056  .0025920165889062674
20531     .01774770842426892     .01379310344827584   .016652275811027614   .015629440181869972    .009417509591907779    .009715688279811851    .009751176866173595    .014955334738532663    .01111685625646322
20538    -.01729668991190832     -.0128495842781557  -.006187818490657669  -.014339675433687823   -.012439530062197631   -.008913197609642556   -.011072261072261058   -.014438291139240585   -.01704593880507969
20545    .018766393317418258    .017611026033690556   .023806617018679074   .028103044496487175    .019127595054816895    .029943791517629097    .024665375873389954     .02358017258679519    .03598369895083678
20552    -.01245325211088396   -.011286681715575515   -.00894347722394467  -.017256850969835025    -.01934081025406269   -.010121055765032704    -.01487019388761093   -.018723654543672294  -.011131570137261453
20559    .017672626948803997    .016742770167427614    .01275418120563112    .02718269298307231    .020305753296767523    .013131515637529952    .016679176048703195    .031478521478521586    .02268303004655093
20566    .007531969309462928    .008233532934131827 -.0026137578709753933   .008205689277899265    .011895230470090267   .0063322449787276205    .009351160692314007    .011806181053936465   .012827940081105662
20573   -.023818891808078217   -.008166295471418054  -.017272185824895803  -.010919696147585358   -.005538600655589408   -.014157899911513104   -.009670865501828507   -.004307456686130017  -.014626572969439386
20580   -.002058620854045924  -.0022455089820358804 -.0007272727272727549  -.005485839676335536   -.012161854967038047   -.004188690535554021   -.008616445100935476   -.008171505479715385  -.013516875362799649
20587   -.007478437605857681   -.004501125281320367  -.002911208151382762   -.00813624767289531   -.004602462317339679   -.005908863294942306     -.0048009270755732   -.013085199185809911  -.006809011432414157
20594      .0083486479390916   .0022607385079126707  .0024330900243309346   .007438303788668705    .006588833660848377    .005138021358049475    .005739000249521731    .005794539383225333    .01074904782056704
20601     .02192251614247041     .02142857142857144   .024878640776698994    .03001656086116494    .022737712448323427    .026761551568607815    .024644392987098942     .02743872668684701    .03274158432423377
20608  -.0015583864118895853    .002944423997055513  .0005920663114268462   .004689488845715741    .008196721311475454    .003709488481062041    .005649717514124202    .008743584869796633   .012081407605610921
20615  -.0033257773260241008   .0007339449541284248  -.004970414201183452  .0005334400213376843  -.0012250807439581181   -.006613499319198528   -.003451043338683729     .00084793668739404  -.004967152699887761
20622   -.009485771342985398   -.009534286762009462  -.012844909609895318  -.012795734755081745   -.014384478144513895   -.012336009398864353   -.013932511878875767   -.016473689165019297   -.01690821256038654
20629   -.019795113041080867    -.02147352832284346   -.02289156626506031   -.01876729899412679    -.01697024550288494    -.01595955590800951   -.017151257758902274   -.016079632465543708  -.012366912366912293
20636    .027398223554385526    .032917139614074956    .03378545006165239    .02992776057791534    .027506042122223507    .030522816560894544     .02974904437427288    .027918287937743237   .030848329048843177
20643    .010548965422835593    .012454212454212448   .017175572519083943   .013026052104208532    .009184587813619995     .01867057673509283    .014122014202711426    .009084886912084733   .020432788995253737
20650     .01756740167546444    .014471780028943509   .011257035647279477   .015825914935707258     .01920088790233079    .013050570962479604    .015755550250656513    .024946075213354693   .012455656286953193
20657    .006926446545720754   .0049928673323823315   .007305194805194918   .005387861084063512    .008276162474137047    .009389030974708784    .009009009009009052    .004574983987556043   .010978743284279347
20664   -.001437731359069154    -.00354861603974455   .006101070565212399  .0045196280991736636    .002376066529862824    .005337777727728254   .0037267080745340816   .0037344020402586445   .005545286506469492
20671    .005792304509722673   .0014245014245015209   .005491990846681805   .002378197711787994    .000538735050102329   -.003313730981050964  -.0013149752475246556    .003629764065335676   .004212622549019695
20678   .0014273961332785209   .0014224751066856027   .002730996813837154 -.0025649246553381038     .00452293775576138 -.00032779208616259234   .0020137866935170854  -.0030741410488244955 .00030508733124850915
20685    .004189823657716066   .0010653409090909496 -.0012482977757603203  .0035358405657343805    .006968267581475189   .0021547685965898817    .005024348767102154    .006439325231271483   .001296225695768109
20692   -.006544850634236938   -.004966300106420737  -.007840018179752436 -.0017930813581037658   -.005748961992973557   -.002711040478638796   -.004537763421012178   -.002973776696404418 -.0009137983551629954
20699    .007954971239402675    .005704099821746886  .0028630325240494733   .013668968252491124    .009744083949030908    .004874390701162317    .007571660356949623    .012201735357917519   .013490853658536665
20706   -.020694528980919138   -.027295285359801472  -.024780175859312407   -.03260525482747691   -.026299045599151687    -.03087686567164181   -.028448738593666083    -.02652022502009107  -.029104309242686355
20713    .005827316222631064                      0   .015690866510538515  -.004123036649214816   .0008712698758441789    .004331504475887986    .002446724546172078   -.004127682993946091   .005189775367931959
20720    .010728652594036455    .011661807580174937   .008415955729767166    .01971479266609713    .013492927094668062    .014951121334100079    .014172112432091937     .01547388781431341      .020806041457964
20727  -.0036271577280206154   -.005043227665706072 .00011432491139825215  -.002964490558741893    .004616706033927342    .004627006610009394    .004890924617653874   .0037188208616779736  -.005208726504114122
20734   -.007000695951538782   -.006154960173787172    -.0067443987197074  -.011440760131859674   -.010900929785187518   -.014475044647053353   -.013056242274412839   -.012289896981745884  -.014038549096979772
20741   -.007157875529272195      -.010775956284153  -.008861779261134722  -.009546227278671427   -.015018908698001087   -.015069146399618488   -.014951076320939309   -.010338517840805082  -.019087200800431033
20748   .0022179802595580157    .002423197884673436   .008128193218764382   .005347240559809892    .006472136902150104   .0040670088118524425   .0055626191989828575    .005916612739206809  .0014123185562965289
20755   .0044261434203836485  -.0003673769287288027  -.013476157567380645  -.017532339615207747   -.009155313351498674    -.01620214099720313   -.012170064801643798   -.018840180130502683    -.0267962077881376
20762    -.01799585062240671   -.015803013597941923  -.022533566841798094   -.01350086886779849   -.015509844901550947   -.016665032839917684   -.016559999999999946   -.014893218433870394   -.02101280090169873
20769    .042486573735649485    .048170276325616095    .02281414237935973   .057384823848238474     .04558659217877093     .03299770710796533      .0398600829740502     .07644765617571543    .06241776315789477
20776    .015600807399541105    .005343783398646191   .006189419595936017   .027936182482219605     .02126522761273771     .01862574792511091    .019791911131972272    .028089391396519805    .02322161157984364
20783    .014499116004900982    .013819985825655584   .011606313834726092   .021380041139437804    .018729726901747477    .015442918048318424     .01741331696839507    .025002147950854856   .021181632498676235
20790   -.007525570416994563   -.003844809507165307  -.018815970628728782   -.00872696204076655   -.006984387838948157   -.026870684829259278   -.016436703611550984   -.007376362112321839  -.024298096155270767
20797     .03442507937388771    .028771929824561414      .030753040224509   .041802622668226264     .03692594124948276     .03461169702780441    .036335760827903484     .04855598716433035    .04213803052160035
20804    .001766473031589563  -.0010231923601637495  .0009075439591605025  -.014182720718591217    -.01586034912718208   -.008710962839403185   -.013166654338338643    -.01634855440122414  -.018140754771965473
20811   -.006628849456648591   -.004096961420279872  -.002946843477275361 -.0017983455221195477  -.0047638353942833864   -.002617556324203058   -.003522974289783366  .00032749304077293477  -.004303628403947559
20818   -.008344243357720426   -.009256085018855095  -.009776060020461514  -.007086235887581112   -.010184336490477645   -.009560408660605455   -.009854069505039885    -.00965788181371752  -.007824726134585375
20825    .015997949777310054    .010972318339100345    .02548501894156812   .012640619329865752     .01749151147237373     .02365855966688748    .020056218187343423    .011074380165289284   .016523959741625488
20832   -.001910927831899392   -.002296600278603966  .0031344453151237114   .004121125246371604   -.003337041156840917    .004899694924655645   .0007447680047664728 -.00024521824423738055    .01086153391458548
20839   -.004223577749441985  -.0006861063464836904  .0015623256332998614  -.007197240066619128   -.003145292207792231    .002667893284268556   -.000520949616729874   -.007603630120186467  -.009794605657481203
20846    .010713324360699899    .009268795056642623   .012033426183843992    .01258163081900422    .012417302798982177    .012294705936324465    .012062546537602204    .011451639479321145    .01136783051598134
20853    .004303063507282081   .0037414965986395576    .00495431024991746   .006685994911543669    .009349552628933415   .0018127435874195853    .005738669805768108   .0026879530830007194   .008758484782132816
20860    .008451814052514228    .009827177228058256   .012817703768624032    .00799341718584703    .008366533864541725    .013480503030851305    .011046086320409798    .006904955320877405   .011576586354098794
20867     .01841486412369732    .020134228187919392    .01633315305570569   .008513119533527742    .010865270644014309    .007052312087127355    .009188915418565818    .007503025413473115   .010657320649452894
20874     .00897454813721879    .009210526315789511   .006598552575564119  .0014454209065679925   .0038108266562438984    .003457140324439328    .003943217665615224  -.0005605381165918736 -.0026893135173391637
20881     .00828784826693662    .006192959582790133   .008987100866990998   .001962935165406174   .0035043317434050366   -.001855123674911731   .0009283724916089084                      0    .00319330116378099
20888   -.004129804205946245  -.0012957563977973015 -.0008383108037306141  -.015672716796312294   -.010185275002425038    -.00424816355429675    -.00756278538812787   -.020831664129476873  -.016976727735728978
20895    .004398148990937888   .0019461563412261523   .005663345568956542   .011063630509863526   .0069580556644452535     .00871033685894578    .007836089144500385    .014155961050650553    .01503921709721525
20902    -.01739231301813545    -.01877630301068313  -.008864323704244387  -.023506252894858744   -.021313868613138665   -.013305137016477142   -.014551679863042956    -.02912699693400032  -.020842194810718827
20909   .0018297789140115188     .00461893764434182   .007049663299663168   .015178465551998116    .013524264120922827    .008662261118056786    .007889974665218989     .02102551317211004   .018462206776715777
20916    -.00392904881668244   -.003940886699507422  -.001253787483021527  -.006950122649223209   -.005592621664050308   -.004072598494909322   -.004740017236426289    -.01017418199576754  -.010094547522570467
20923   -.007090573012939025   -.011539729640619778  -.008892143529658003  -.015291419161324439   -.011642821904291984   -.009245266245888454   -.010535430798095019   -.015623715155003748  -.009335727109515342
20930     .00539500040955834     .01067378252168113   .012560692421363859    .02162097593023953    .011380652890086858    .018034993270524817    .014366977829638264    .021635619413582854   .021964479884015956
20937    .015068640267228588    .014521452145214445    .02178671948295621   .009003215434083554    .009969400848879586    .016305305834655462    .012797469264505005    .008585445625511015   .011349127535820847
20944    .006140094855891958    .005530253741054057   .008161599673535984   .004519381192421352   .0005863956215793811     .00988639320093661     .00475615816000559  -.0004864207539520719  .0058914293729834125
20951  -.0047319813041412214   -.004852798447104451  .0034405990690144043  -.010901540058833793   -.009181480757960518   -.006440532417346501   -.007983608873816557   -.015816367913050553  -.004671593919955289
20958  -.0010128316294930435   -.003250975292587822 -.0037313432835819923 -.0036738978306507784    -.00463328075709778    .004321521175453759 -.00042731999145361637   -.009230262073512482 -.0033625218914184922
20965    .011699489423778206    .013698630136986242   .020649863346492478   .008604038630377517    .014855897791423196    .011703958691910495    .013537584609903853     .00856762601896524   .012933155268152129
20972    .009704118122044117     .01093951093951105   .014083110185460693   .014275766016713138    .009758953840148335    .015311330384484494     .01223198594024611    .011711340206185581   .015960030532232203
20979  -.0027383077472332177    .000636537237428376  -.014865525672371599   .004291108822519739 -.00028993911278632587    -.00854557640750667    -.00402805750399342    .009456264775413684  -.004439587459872998
20986   .0009988078744724927   .0028625954198473235 -.0003971011615209595  -.002107901783170994   .0032869296210363825                      0    .002161634474583378   -.005006864249374179 -.0030186608122941667
20993    .002578573165672394   .0012686330478908704    .00873969609693113  -.005195250057090641    -.01040662940836383    .005577150583065714   -.003966045087670423   -.006574141709276748   .012455271125791373
21000   -.009153399706777254   -.007918910357934747   -.01486659446687005 -.0017216642754663493   .0016553067185978744   -.011092436974789859   -.004400977995109992    .004166666666666592  -.009515394549038304
21007    .006588255624318217   .0019157088122604955 -.0002998201079352502  .0009198045415350676   .0013609409934869307   .0005098572399727062    .001262980634296789  -.0004068017248392902 -.0006862005077883368
21014     .01181343147458655     .00828553218610585   .021693491952414292   .010280856929527264    .012134744199592273    .014438593511126235    .013945339873861312    .010011395083835292   .012978095172698035
21021   .0018593142453164283   .0022123893805309825   .010176125244618316   .005230244457077814    .005658929599079121    .007116543871399938    .005874628516137911   .0058828269804173565   .007795553145336071
21028 -.00009879121887196586   -.002838221381267734 -.0021309569934133948  -.006277570410586955  -.0031473533619456205  -.0012469864494139636  -.0023361275250790396   -.004085883672488311 -.0025559965023205455
21035   .0032992469953915977     .00600885515496514 -.0014560279557366675  -.005918843549029767   .0006697282816686509   -.010987181621441713   -.004545454545454523   -.004665754967420145  -.012745296378717286
21042    -.00964362209099895   -.009745363093366826  -.010790317876932045   -.02278582469800189    -.01740128119323078   -.019104527857263055    -.01840321018403208   -.026347692556372787  -.019808743169398946
21049 -.00029830498844061733   -.006666666666666694 -.0036360062893082204  -.010896947682933887   -.009535856767539066  -.0018876018876018779  -.0062729066817027515   -.011621150493898943  -.008083623693379767
end
format %tdnn/dd/CCYY date

findname date, not local(ETF)
corr `ETF',cov

Can someone help? Thanks.

[Joro Kolev]

Try the solution proposed in this thread:
https://www.statalist.org/forums/for...elation-matrix

[Francois Durant]

I had seen this post before, but it only imports to Words. I would like to import to excel. I have found the following solution, but I'm missing the row names and columns. Would you be able to help? Thank

findname date, not local(ETF)
corr `ETF',cov
return list
matrix list r(C)
putexcel set "excel.xlsx", modify sheet("VAR_COV_Matrix")
putexcel A1=matrix(r(C))

[Joro Kolev]

I think this is going to do it, no?

 putexcel A1=matrix(r(C)), names

[Francois Durant]

That's great, thanks a lot!