I have a dataset with 3.000 observation (price of an asset). I want to study the empirical distribution of the logRet of that time series. How can I do it in Excel? if not possible to do it in Excel, Python? Wolfram?
thanks, Ciao
$\begingroup$
$\endgroup$
- calculate n-1 log returns
- draw a histogram via data -> analytics
-
$\begingroup$ thanks.what is the best way to create the bin column? i.e. how do you best simplify the obs in intervals(bin)? that is I have 3k obs that are logret, how can I create the x axis of the histogram $\endgroup$ – whitefox May 23 '20 at 18:57
-
$\begingroup$ It should be possible to let Excel choose the bin-size, if you want to choose one on your own, the smaller the bins the better $\endgroup$ – simzoor May 23 '20 at 20:01
-
$\begingroup$ Excel is a bit primitive. There are all kinds of rules of thumb that more sophisticated software use to automatically set the bin width. For example one rule is "to set the number of intervals to twice the cube root of the number of observations", no guarantee that this will be good but it could be a starting point for you. $\endgroup$ – noob2 May 24 '20 at 2:23
-
$\begingroup$ thanks! Can you expand a little bit more on the rule: twice cube root...or do you know the name of that rule so that I can dig a bit more? $\endgroup$ – whitefox May 24 '20 at 10:25
-
$\begingroup$ That's called "Rice Rule" or "Rice's Rule" en.wikipedia.org/wiki/Histogram#Rice_Rule $\endgroup$ – noob2 May 24 '20 at 10:44
$\begingroup$
$\endgroup$
You can use histogram in Excel. Either on Insert tab, where you choose Add graph and then histogram (in Excel 2016 and higher), or go to tab Data and Analytics tools (add-ins is needed for that) and choose item Histogram.
answered May 24 '20 at 7:11
