My empirical (price-spread) time series data has a nonnormal distribution (see below) and it has no (partial)-autocorrelation. I am worrying about which distribution fits most to my data, but I think there is not a known distribution I could use. Please correct me here if I am wrong. Actually the huge amount of Zero Spreads is worrying me most. My idea was to create some sort of a empirical distrubtion function based on the (2 years) sample and use the distribution for randomness in a monte carlo simulation.

  1. Does my approach makes sense?

  2. Can I do it via Excel or do I need advanced statistical software like R? Because ultimately, I want to derive random price spreads which I can use to conduct a monte carlo in Excel. I already prepared everything around in Excel, but the missing part is a correct distrubution. Currently I am using a triangular distribution.

enter image description here


I initially thought your data might exhibit , but when you look a little more closely, you note that the two bars right next to the high one at zero are also higher than the ones farther out. It looks like the peak at zero is not a peak of strict zeros, but part of a very narrow normal distribution. (Or one with yet additional zero inflation.)

Your data looks really like a : the hump on the right looks nicely normal, you probably have a narrower normal distribution around zero, and then something asymmetric on the left side, with a rather long tail - a flipped gamma might be a possibility to model this, though the tail does look rather long.

In R, you might be able to use the mixtools package, though I don't know whether it can deal with a flipped gamma. Alternatively, you might need to set up your own likelihood function to optimize. Using the empirical distribution in Excel sounds like a possible approach with less work. I don't know of any packages or functionalities implementing mixture distributions in Excel.

Then again, I am a little skeptical that there really is no temporal dynamics in your data. Have you looked at ARCH/GARCH models? Especially for financial series, the dynamics often don't happen so much in the time series itself, but in the variance.

| cite | improve this answer | |
  • $\begingroup$ 1) The price distribition around 0 (in -1 cent steps) is shown here: s12.directupload.net/images/200729/wxnkqil6.png I think it does not look normal right? 2) Regarding the Temporal Dynamics: No I haven't looked into ARCH/GARCH models. Is there a simple way to test for ARCH? 3) Regarding mixture distribution: Sounds indeed very interesting, but I think it's quite challenging to use Excel to do it. I need to first understand the concept. $\endgroup$ – UDE_Student Jul 29 at 9:57
  • $\begingroup$ Ah. This does look like a peak by itself. So it might be useful to model a point mass at zero for one of the mixture components. This is indeed zero inflation (which is a specific kind of mixture, namely one with a point mass at zero), but since the rest of your distribution is a mixture all by itself, it makes the most sense to model all of your data as a three-component mixture. For ARCH/GARCH, there are packages in R - again, I don't know about Excel (but googling might turn something up). $\endgroup$ – Stephan Kolassa Jul 29 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.