daily to weekly returns

[Moh Bit]

Hi everyone,
I am trying to convert daily returns for Bitcoin to weekly returns (Sunday to Sunday). Would you please advise on this? Below you will find data over one month. in contrast to stock returns Bitcoin market operates 7 days a week and gaps in data are rare...

Thank you !!

Date	Return daily
01/01/2014
02/01/2014	0,007170518
03/01/2014	0,004007075
04/01/2014	0,005102447
05/01/2014	0,01335761
06/01/2014	0,001743769
07/01/2014	-0,023356052
08/01/2014	0,008827473
09/01/2014	0,000161686
10/01/2014	0,004031975
11/01/2014	0,007507082
12/01/2014	-0,009350074
13/01/2014	-0,002610048
14/01/2014	-0,00106158
15/01/2014	0,003928251
16/01/2014	-0,004687846
17/01/2014	-0,003390411
18/01/2014	0,002766558
19/01/2014	0,006018623
20/01/2014	-0,001286642
21/01/2014	0,000548436
22/01/2014	-0,001756461
23/01/2014	-0,001652581
24/01/2014	-0,00515709
25/01/2014	0,006485329
26/01/2014	0,003108574
27/01/2014	-0,011427733
28/01/2014	0,003513585
29/01/2014	0,000635094
30/01/2014	0,001472243

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date double returndaily
19724           .
19725  .007170518
19726  .004007075
19727  .005102447
19728   .01335761
19729  .001743769
19730 -.023356052
19731  .008827473
19732  .000161686
19733  .004031975
19734  .007507082
19735 -.009350074
19736 -.002610048
19737  -.00106158
19738  .003928251
19739 -.004687846
19740 -.003390411
19741  .002766558
19742  .006018623
19743 -.001286642
19744  .000548436
19745 -.001756461
19746 -.001652581
19747  -.00515709
19748  .006485329
19749  .003108574
19750 -.011427733
19751  .003513585
19752  .000635094
19753  .001472243
end
format %td date


//    VERIFY NO GAPS
tsset date
assert !missing(L.date) in 2/L

//    CREATE CUMULATIVE RETURN VARIABLE
gen double cum_return = 1 in 1
replace cum_return = L1.cum_return*(1+returndaily) in 2/L

//    PARTITION DATES INTO 7 DAY WEEKS
gen int week = ceil(_n/7)
by week (date), sort: gen weekly_return = cum_return[_N]/cum_return[1] - 1

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Moh Bit]

Thank you, Clyde. I really appreciate it.
Another quick question if you don't mind: I have a sample for 4 years and not only for one month. I am having troubles in converting your macro to compute weekly returns for the whole sample. I have used dataex as you recommended (please see below). Would you please advise?

Thank you again!!


* Example generated by -dataex-. To install: ssc install dataex
clear
input int date float returndaily
19724 .
19725 4.899204
19726 3.1418285
19727 4.47074
19728 9.292914
19729 -.20617314
19730 -14.051766
19731 6.483681
19732 -.20151676
19733 2.1967304
19734 4.846395
19735 -6.740476
19736 -1.812473
19737 -.3940906
19738 2.358609
19739 -2.990446
19740 -2.1387718
19741 1.2845476
19742 5.277171
19743 .1203741
19744 .6527122
19745 -1.1771247
19746 -.7165883
19747 -2.967901
19748 4.7409816
19749 4.670447
19750 -6.509216
19751 -1.1576581
19752 -.7544374
19753 1.683974
19754 -.27018258
19755 .16708694
19756 1.361357
19757 -2.2918506
19758 -.54564744
19759 -2.416072
19760 -8.734112
19761 -17.521662
19762 -6.975297
19763 1.6494397
19764 -12.403577
19765 -.6318917
19766 -8.622661
19767 -16.256433
19768 -5.406724
19769 -14.19845
19770 -21.32752
19771 -9.62846
19772 7.657878
19773 -11.686073
19774 -84.87646
19775 -.32217675
19776 82.90121
19777 19.3546
19778 -57.80653
19779 -24.801937
19780 147.43945
19781 .5631976
19782 -3.8537595
19783 -2.925966
19784 -2.3287077
19785 19.55593
19786 -.04985038
19787 -.5378172
19788 -1.536887
19789 -4.986057
19790 -1.2268223
19791 2.886319
19792 -2.2710316
19793 -.7006838
19794 1.749058
19795 .9015924
19796 -1.7771778
19797 1.6154656
19798 -.7867048
19799 -2.0824583
19800 -1.2362143
19801 -.6650505
19802 -4.991782
19803 -2.9385695
19804 2.646626
19805 -2.3631856
19806 2.932137
19807 -.8262213
19808 -.07819913
19809 -20.009796
19810 4.7004547
19811 -1.1461803
19812 -6.53674
19812 -.50696826
19813 4.1388574
19814 -8.809282
19815 2.774729
19816 1.8282015
19817 2.7238436
19818 -.20606877
19819 1.4574317
19820 .899955
19821 -1.9959537
19822 -17.314524
end
format %td date

[Clyde Schechter]

Um, something is drastically wrong here. The values of returndaily in your original post appear to be on a completely different scale from what you show in #3. I interpreted returndaily from #1 as follows: something like -0.01 would mean that there was a 1% decrease in value that day. As such, numbers less than -1 are impossible, since -1 would already imply that the asset had lost all of its value.

Your numbers in #3 are obviously quite different, and I do not understand what they are supposed to be, so I'm not going to try to write code to work with them. I can tell you that the code shown in #2 would produce only nonsense as applied to the data in #3. So, what are these numbers? And if your data is a mixture of different things, you need to be able to identify what is what within the data before you can calculate anything from them.

[Moh Bit]

These are the daily returns for Bitcoin (in 2 I used data for Ethereum (another cryptocurrency)). I have used the following macro to compute daily returns based on daily prices of Biticon (please see the data with daily prices (close) below):

gen returndaily=100*(log(close[_n])-log(close[_n-1]))

I have multiplied the returns by 100.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date double close float returndaily
19724 815.940002 .
19725 856.909973 4.899204
19726 884.26001 3.1418285
19727 924.690002 4.47074
19728 1014.73999 9.292914
19729 1012.650024 -.20617314
19730 879.900024 -14.051766
19731 938.840027 6.483681
19732 936.950012 -.20151676
19733 957.76001 2.1967304
19734 1005.320007 4.846395
19735 939.789978 -6.740476
19736 922.909973 -1.812473
19737 919.280029 -.3940906
19738 941.219971 2.358609
19739 913.48999 -2.990446
19740 894.159973 -2.1387718
19741 905.719971 1.2845476
19742 954.799988 5.277171
19743 955.950012 .1203741
19744 962.210022 .6527122
19745 950.950012 -1.1771247
19746 944.159973 -.7165883
19747 916.549988 -2.967901
19748 961.049988 4.7409816
19749 1007 4.670447
19750 943.539978 -6.509216
19751 932.679993 -1.1576581
19752 925.669983 -.7544374
19753 941.390015 1.683974
19754 938.849976 -.27018258
19755 940.419983 .16708694
19756 953.309998 1.361357
19757 931.710022 -2.2918506
19758 926.640015 -.54564744
19759 904.52002 -2.416072
19760 828.869995 -8.734112
19761 695.650024 -17.521662
19762 648.780029 -6.975297
19763 659.570007 1.6494397
19764 582.630005 -12.403577
19765 578.960022 -.6318917
19766 531.130005 -8.622661
19767 451.440002 -16.256433
19768 427.679993 -5.406724
19769 371.070007 -14.19845
19770 299.799988 -21.32752
19771 272.279999 -9.62846
19772 293.950012 7.657878
19773 261.529999 -11.686073
19774 111.919998 -84.87646
19775 111.559998 -.32217675
19776 255.589996 82.90121
19777 310.170013 19.3546
19778 174 -57.80653
19779 135.779999 -24.801937
19780 593.140015 147.43945
19781 596.48999 .5631976
19782 573.940002 -3.8537595
19783 557.390015 -2.925966
19784 544.559998 -2.3287077
19785 662.179993 19.55593
19786 661.849976 -.04985038
19787 658.299988 -.5378172
19788 648.26001 -1.536887
19789 616.72998 -4.986057
19790 609.210022 -1.2268223
19791 627.049988 2.886319
19792 612.969971 -2.2710316
19793 608.690002 -.7006838
19794 619.429993 1.749058
19795 625.039978 .9015924
19796 614.030029 -1.7771778
19797 624.030029 1.6154656
19798 619.140015 -.7867048
19799 606.380005 -2.0824583
19800 598.929993 -1.2362143
19801 594.960022 -.6650505
19802 565.98999 -4.991782
19803 549.599976 -2.9385695
19804 564.340027 2.646626
19805 551.159973 -2.3631856
19806 567.559998 2.932137
19807 562.890015 -.8262213
19808 562.450012 -.07819913
19809 460.450012 -20.009796
19810 482.609985 4.7004547
19811 477.109985 -1.1461803
19812 446.920013 -6.53674
19812 444.660004 -.50696826
19813 463.450012 4.1388574
19814 424.369995 -8.809282
19815 436.309998 2.774729
19816 444.359985 1.8282015
19817 456.630005 2.7238436
19818 455.690002 -.20606877
19819 462.380005 1.4574317
19820 466.559998 .899955
19821 457.339996 -1.9959537
19822 384.630005 -17.314524
end
format %tdnn/dd/CCYY date

[Attaullah Shah]

You can also have a look at the ascol program that converts daily prices or returns to weekly, monthly, quarterly, or yearly frequencies.

 ssc install ascol
help ascol

Further, you can read this blog post on how to go about the conversion of log and simple returns to a lower frequency.

[Clyde Schechter]

So if those are the returns multiplied by 100, the code is almost the same:

//    VERIFY NO GAPS
tsset date
assert !missing(L.date) in 2/L

//    CREATE CUMULATIVE RETURN VARIABLE
gen double cum_return = 1 in 1
replace cum_return = L1.cum_return*(1+returndaily/100) in 2/L

//    PARTITION DATES INTO 7 DAY WEEKS
gen int week = ceil(_n/7)
by week (date), sort: gen weekly_return = cum_return[_N]/cum_return[1] - 1

That said, you should also be aware that the formula you used, -gen returndaily=100*(log(close[_n])-log(close[_n-1]))-, is just an approximation, and it is not accurate unless the return is close to zero. Many of your reported returns are well outside the range where this approximation is valid, so your returndaily variable is not correct. The formula you have used is good up to a return of about 10% (or -10%). Beyond that it becomes progressively more inaccurate. For example, if the real return is 20%, that formula gives a result of 18%. And a real return of 30% is given by the formula as 26%. The larger the real return, the worse the error.

I don't really understand why people use this approximate formula in the first place. It's one thing to use an approximation that is quicker or easier to calculate than the exact formula. But this formula is actually much more complicated than the real formula, which is

gen returndaily = close/close[_n-1] - 1

(And if you want to multiply that result by 100 because you prefer that scale, you can do that, too.)

[Moh Bit]

It worked perfectly. Thank you, Clyde! I will also use the exact formula. Thanks for the suggestion!