Help converting time series data to panel data

[Jorrit Reijmers]

Dear Members,

I hope you could help me with the following problem, I have an dataset which contains ETF and index prices. I would like to Transform the dataset from time series to panel data. I can't find an example with the exact same format as my data. My data looks as following:

Date ETF1 ETF2 ETF3 ETF4
01/01/2005 ReturnETF1 RetrurnETF2 Return ETF3 ReturnETF4 .... ETF Return52
02/01/2005 ReturnETF1 RetrurnETF2 Return ETF3 ReturnETF4 .... ETF Return52
03/01/2005 ReturnETF1 RetrurnETF2 Return ETF3 ReturnETF4 .... ETF Return52
..... ReturnETF1 RetrurnETF2 Return ETF3 ReturnETF4 .... ETF Return52
01/01/2020 ReturnETF1 RetrurnETF2 Return ETF3 ReturnETF4 .... ETF Return52

I can't figure out to use the reshape command in this setting. I hope my question is clear and that you could help me. Thank you in advance.

With kind regards,
Jorrit Reijmers

[Wouter Wakker]

reshape long ETF, i(Date) j(no)

[Jorrit Reijmers]

Hello Wouter,

Thank you for your response. I used the following command to rename my ETF's

rename (ReturnSHESPZ-ReturnDJSTOXXPIZ) (PanelReturn=)

Subsequently I Ran

reshape long PanelRetun, i(Date) j(no)

I het the following error code, do you know why it doesn't seem to work?

no xij variables found
You typed something like reshape wide a b, i(i) j(j).
reshape looked for existing variables named a# and b# but could not find any. Remember this picture:

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+

long to wide: reshape wide a b, i(i) j(j) (j existing variable)
wide to long: reshape long a b, i(i) j(j) (j new variable)


With kind regards,
Jorrit

[Nick Cox]

PanelRetun would be the wrong stub if your variables have prefix PanelReturn.

If that is not the answer, please back up, read https://www.statalist.org/forums/help#stata and give us

1. Real, or realistic, data example using dataex, not a schematic example.

2. Code presented readably using CODE delimiters.

[Jorrit Reijmers]

Thank you for your respond. I included an example of my data using

dataex Date SMB5 HML5 RMW5 CMA5 ReturnSHESPZ ReturnF50EPZ ReturnDSXRTPZ ReturnDEUN2PZ ReturnFMSEDPZ Retur
> nDUIM1PZ ReturnDVX5EPZ ReturnDDXETPZ ReturnDDBXEPZ ReturnFBNKEPZ ReturnDIQQSPZ

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Date double(SMB5 HML5 RMW5 CMA5) float(ReturnSHESPZ ReturnF50EPZ ReturnDSXRTPZ ReturnDEUN2PZ ReturnFMSEDPZ ReturnDUIM1PZ ReturnDVX5EPZ ReturnDDXETPZ ReturnDDBXEPZ ReturnFBNKEPZ ReturnDIQQSPZ)
16555 -.36  .12 -.13  .12 . . .          . .           . . . . .          .
16558 -.17  .12  -.1  .22 . . .   .9682503 .   1.0703616 . . . .   1.378554
16559 -.38 -.07 -.02  .04 . . .   .4254386 .   .05713662 . . . . -.21449906
16560 -.67  .03 -.06 -.13 . . .  .58980185 .    .8254268 . . . .  -.1636492
16561 -.15 -.03  .08 -.07 . . .   .7182389 .        -.01 . . . .   .8067478
16562 -.15    0  .01  .07 . . .   .4835032 .   1.2794873 . . . .  .14240032
16565  .33   .2 -.05  .19 . . .  -.3387314 .   -.5039908 . . . .  .09147134
16566  .33  .14  .16 -.03 . . .  -.7702086 .   -.7722241 . . . . -.56937087
16567  .02  .11 -.32  .07 . . . -.54227006 .  -.07655574 . . . .  .24464746
16568 -.18 -.12 -.06 -.19 . . .   .8535058 .    .5212098 . . . .   .6490645
16569 -.14  .04 -.19  .19 . . .  -.1092556 .  -.50792634 . . . . -.06054334
16572  .08 -.04 -.04  .04 . . .  -.0431071 .    .4879264 . . . .  .24246164
16573  .09 -.12  .12 -.17 . . . -.17570013 .   -.4081425 . . . . -.26246163
16574 -.99  .26  .24  .13 . . .   1.830341 .   1.6385702 . . . .  1.0960947
16575 -.04  .28 -.18  .16 . . .  .57441723 .     .706849 . . . .   .6875614
16576 -.09  .28 -.09  .11 . . .  .11940797 .    .2494035 . . . .   .3369644
16579 -.22  .01  .08  .08 . . .  .57027245 .    .6033998 . . . .  .38506225
16580   .3 -.45  .37  .05 . . . -.10647854 .  -.46161366 . . . .       -.01
16581  .23  .17  .04 -.08 . . . -.20323677 .   .50596374 . . . .  -.6527711
16582 -.12  .04 -.06  .09 . . .   .9843947 .    .4072691 . . . .   .8788947
16583  .16  .15 -.22  .03 . . . -.13775474 .    -.234467 . . . .  -.0591763
16586 -.16  .06 -.02 -.15 . . .  .50004256 .    .6937777 . . . .   .5296138
16587  .44  .13 -.13 -.21 . . . -.48808855 .   -.3612697 . . . .  .28311208
16588 -.73 -.08  .11 -.12 . . .  1.6062608 .   1.4192764 . . . .  .18493183
16589  .24  .23  .06  .01 . . . -.07289308 .    .3048617 . . . .       -.01
16590  .38 -.14  .08 -.05 . . .  -.3250601 .  -.57746685 . . . .   .9589998
16593  .37  .12 -.01 -.11 . . .  -.6113628 .   -.5807054 . . . .       -.01
16594 -.45  .14  .03  .07 . . .  1.2518464 .    .6870878 . . . .  .37498605
16595  .39  .09  .08  .07 . . . -.41835615 .   .05313132 . . . .       -.01
16596  .04  .15 -.25 -.02 . . .  .08438415 .   .17915516 . . . .   .8508375
16597 -.07  .17 -.01 -.12 . . .   .4605891 .    .6492395 . . . .       -.01
16600  .12 -.03  .26   .1 . . .   .3648833 .    .4270909 . . . .       -.01
16601 -.03    0  .03  .02 . . .   .2391437 .   -.1658847 . . . .       -.01
16602  .48  .07 -.14 -.14 . . .  -.4151743 .  -.38511765 . . . .   .8434902
16603 -.15   .1 -.19  .01 . . .   .5195138 .   .52100235 . . . .       -.01
16604    0  .35 -.11   .2 . . .   .4858176 .   .54918134 . . . .   .4140289
16607  .22 -.06  .17 -.32 . . .  -.4747568 .   -.3824399 . . . .       -.01
16608 -.16  .07 -.15 -.22 . . .   .5474496 .   .39341405 . . . .       -.01
16609  -.1 -.07 -.01 -.26 . . .   .1134568 .    .1756436 . . . . .037003525
16610  .05  .02  .11 -.04 . . .   .3594586 .   .14444017 . . . .       -.01
16611   .5  .14  .01 -.02 . . .  -1.122496 .   -.6913281 . . . .       -.01
16614  .39  .13   .3  .07 . . .  -.9466287 .  -1.0093837 . . . . -1.1442276
16615 -.37  .21   .3  .14 . . .  1.8853496 .    .7404726 . . . .   .6531953
16616 -.26 -.06 -.09  .34 . . . -1.6547228 .        -.01 . . . .   .3670033
16617  .14  .18  .09  .31 . . . .021284217 .    .8893703 . . . .  1.2055362
16618  -.2  .02  .15  .02 . . .    .706849 .    .3290356 . . . .  -.4290926
16621    0  .02  .37 -.05 . . .   .4238402 .    .7257482 . . . .   .4090926
16622  .21  .01 -.14 -.05 . . . -.31969365 .   -.6842287 . . . .       -.01
16623 -.12  .12  .09 -.06 . . .  .45418155 .    .9082801 . . . .   .3610579
16624  .41 -.09 -.06   .1 . . .  -1.691235 .   -1.824039 . . . .       -.01
16625 -.24   .1  .18    0 . . .  1.7329636 .   1.2540834 . . . .       -.01
16628 -.03  .09 -.25  .04 . . .   .6665093 .   1.2987998 . . . .   .9575266
16629  .22  .07 -.18  .07 . . .  -.4092021 .   -.3431822 . . . .   .3104397
16630 -.21  .07  -.1 -.16 . . .   .9088426 .   .59496254 . . . .       -.01
16631  -.2  .16 -.13 -.08 . . .    .476619 .    .7410926 . . . .       -.01
16632  .09  .06 -.17 -.15 . . . -.07069803 .   -.2197379 . . . .       -.01
16635  .09  .09 -.04 -.03 . . .  .08103324 .  .019989504 . . . .   .6732183
16636 -.42  .24 -.02 -.13 . . .   .8958033 .   -.0999955 . . . .       -.01
16637  .04  .26 -.26  .15 . . .  -.2206214 .   -.4009191 . . . .   .3977016
16638 -.03   .4 -.17 -.25 . . . -.22106597 .    .2307464 . . . .   .1706685
16639  .18  .08  .05 -.08 . . .  -.1610346 .   -.2206214 . . . .    .080212
16642  .01 -.12  .24  .05 . . .   .3521006 .   .05022283 . . . .  -.2356828
16643   .1  .11 -.19  .18 . . .  .02011595 .   .29057133 . . . . -.05519774
16644  .24    0 -.02  .03 . . .  .14044383 .   .22980826 . . . .   .6209169
16645 -.02 -.02  .17  -.1 . . .   .7686802 .    .4678982 . . . .    .482943
16646  .36 -.32  .27  .03 . . .  -.3387019 .        -.01 . . . .  .16865122
16649  .18  .03 -.13 -.15 . . .  .01992668 .  -.27853662 . . . .       -.01
16650 -.27  .04  .11  .01 . . .   .7055666 .    .1989241 . . . .  .25737986
16651  .08 -.05  .28   .1 . . . -.21817853 .   .25797695 . . . .  -.3220127
16652  .59 -.25  .01  .17 . . .   -.997293 .   -.6064825 . . . .   .3465066
16653  .27  .08 -.14 -.17 . . .  -.8250989 .   -.3996303 . . . .  -.9485543
16656  -.2 -.08  .06  .14 . . .   .2926637 .   -.3408771 . . . .       -.01
16657 -.41  .07  .16  .04 . . .  1.2214317 .    .7404162 . . . .       -.01
16658 -.48  .13 -.15 -.04 . . .   .9702544 .    1.267688 . . . .  1.1951027
16659  .18  .07 -.03 -.02 . . . -.33568496 .  -.27607554 . . . . -.27654836
16660  .41    0 -.02  .11 . . . -.48562515 .   -.3658723 . . . . -.05449389
16663  .07  .21 -.07  .05 . . .  -.3084186 . -.069435365 . . . .       -.01
16664  .33  .04 -.27  .02 . . . -.54940796 .   -.8458257 . . . .       -.01
16665 -.01  .14  .01  .15 . . . -.16035336 .   -.3102104 . . . .  -.8142939
16666  .19 -.05 -.18   .1 . . .  -.6136843 .   -.4318144 . . . .  -.8208153
16667 -.49  .11    0  .11 . . .    1.40292 .    .7419963 . . . .       -.01
16670  .22  .17  .04 -.07 . . .  .22852127 .    .6768774 . . . .   .8008153
16671  .44 -.11 -.03  .07 . . . -1.2987934 .   -.9969977 . . . . -.09976661
16672  .16  .01  .15 -.12 . . . -.13073651 .   -.7945544 . . . .       -.01
16673  .53 -.21  .02  .04 . . .  -.9506836 .        -.01 . . . .       -.01
16674  .73 -.14 -.06 -.07 . . .     -.9904 .        -.01 . . . .       -.01
16677 -.31 -.13   .1  .02 . . .   .4201082 .        -.01 . . . . -1.3662595
16678  .29 -.07    0  .01 . . .  -.1326994 .        -.01 . . . .       -.01
16679 -.33 -.06  .27  .06 . . .   .6934744 .        -.01 . . . .   .3988127
16680 -.04 -.34  .22  .15 . . .    .779318 .        -.01 . . . .       -.01
16681  .15 -.12  .07  -.1 . . .  -.4038802 .        -.01 . . . .       -.01
16684  -.3  .17  .04  .01 . . .   .9567749 .        -.01 . . . .       -.01
16685  -.5  .16 -.11  .15 . . .  1.0665654 .        -.01 . . . .   1.430169
16686 -.01  -.2  .03 -.23 . . .  .25734013 .        -.01 . . . .       -.01
16687  .28 -.02 -.08  .02 . . . -.15843405 .        -.01 . . . .  1.4537654
16688 -.01 -.13  .29  .02 . . .   .2866481 .        -.01 . . . .   .2099253
16691  .27  .11 -.22  .01 . . .       -.01 .        -.01 . . . .       -.01
16692  .22 -.03 -.06  .19 . . .  -.8428424 .        -.01 . . . .       -.01
16693 -.18  .09 -.03  .21 . . .   .3180158 .        -.01 . . . .   .1217234
16694  .05 -.02  .19 -.22 . . .       -.01 .        -.01 . . . .       -.01
end
format %tdnn/dd/CCYY Date

rename (ReturnSHESPZ-ReturnDJSTOXXPIZ) (Funds=)

reshape long Funds, i(Date) j(no)

This gives the following error:

variable no contains all missing values
r(498);

The paneldata I would like to create has to look as following:

Date Funds FundsReturn SMB5 HML5  etc.
01/01/2005 Name Value Value Value etc.

Thank you in advance!

[Nick Cox]

A different error! But you need the string option: see the help for reshape.