Generate Random Time Series using RANDBETWEEN, but NOT with RANDBETWEEN in Each Cell

Greetings All,

I am trying to figure out a way to generate a random time series in Excel 2010 between two numbers that can vary. Yes, RANDBETWEEN is good for this, but I want a time series to be generated from a single cell start point (i.e., maybe RANDBETWEEN as a random seed generator), so that I don't have a huge amount of cells that just say "=RANDBETWEEN(min,max)", where all of the cells would be reliant on the static values of "min" and "max" in my worksheet. However, the RANDBETWEEN function can be used in the resultant time series column if they are governed by the starting seed value (not just the min/max values) -- I just can't think of how to get this done.

The reason that I want the randomization process to happen in this fashion is that I need to incorporate the Solver to manipulate the RANDBETWEEN value to ultimately produce some distribution parameters across the time series, but I am trying to work around the onboard Solver's 200 variable limit, and I may have to work with time series that are well in excess of 200 periods. If I can just have a time series generated by a single seed, it would be highly advantageous.

Many thanks in advance,

LBF

Hello
RANDBETWEEN only works with whole values >=0. Time values are fractions of 1 so you can use RAND() which takes no arguments but randomizes between 0 and <1. These can be formatted to show as time values.

DBY

Sorry, what I mean by "time series" is in the statistical sense; basically it's just a series of numbers. Dates and times don't actually come into play...

For random Time

=TIME(RANDBETWEEN(0,24),RANDBETWEEN(0,59),RANDBETWEEN(0,59))

shg,

At the risk of being too technical, what I'm trying to do is basically generate random series of financial returns that conform to certain distribution parameters so that I can perform skewed Monte Carlo-style simulations with more realistic randomized distributions. For example, suppose I take the S&P500 over some frequency (say, monthly) and with some length of time (say, 30 years) and calculate the descriptive statistics for the data. Specifically, I'll be interested in MIN, MAX, AVERAGE, STDEV, SKEW, KURT, MEDIAN and SUM for what I'm trying to achieve (Note that I AM NOT interested in generating random numbers from a pure normal distribution; I already know how to do this. Also note that I already know the workaround for RANDBETWEEN to get decimalized results to a certain number of decimal places).

I then want to generate random series of the same length as the original reference series that are constrained as follows:

• each number in the series is not less than X% below the min, nor more than Y% the max, of the original reference series
• the series as a whole has the roughly the same mean, standard deviation, skew and excess kurtosis as the original reference series
• to ensure that the bulk of the distribution is where it is expected, the randomly-generated series should have roughly the same median as the original reference series
• to ensure data magnitude is not that far from expectation, the new series should roughly sum up to the same amount as the original reference column

Because of the mathematical complexities involved and limitations of Excel in generating random series this precisely-defined, I believe that I have developed a unique solution using the Frontline Solver in Excel to effectively achieve the results I'm looking for. I am getting very good results when I am working with series below a count of 200 cells (where each one of the "by changing" cells in the Solver dialog box each houses the =RANDBETWEEN(min/max) formula), but then after that I am limited by the Solver's variable limit of 200. If only one cell was changed that could then cause the simulation series to change, then I could manipulate the single cell and not be in violation of the Solver variable limit.

Ultimately, I plan on creating a function or sub where the Solver will programmatically be called to generate this simulated series, assuming that the user already has a reference to the Solver add-in in Excel.

Thanks very much!

shg,

I spent the better part of last night and this morning exploring your suggestion, and I am intrigued by this approach. I know how to create a CDF (which will obviously be impacted by how I striate the buckets, but that is a separate matter), but I am getting hung up on what the procedure is, in plain language, once I have the CDF of my initial distribution.

Say I have a cumulative value column created now using the histogram function in the Data Analysis toolpak, and my data is binned in 1% increments. I am now getting stuck on how to mathematically perform the transform that is being described in the link (and many others I have searched). Everyone seems to be implementing this sort of approach in R, but there doesn't seem to be anything about this that Excel can't handle (for smaller distributions, anyhow). Any way you could provide detail?

Thanks. Also, while I haven't posted much until recently, I have to say that I have benefited greatly from the solutions you have provided to others as an anonymous chronic Googler of VBA solutions over the years. Many thanks in advance.

LBF