Data Generation

  • Thread starter Thread starter rvExcelNewTip
  • Start date Start date
R

rvExcelNewTip

This is probabably a mathematical rather than an excel question, but I
give it a try. I want to generate a "random" data series with the
following characteristics:
1. The overal series should have a X% compounded growth rate
2. Each point should be within a Y% interval around the previous
point.
(Y > X)

For instance, the data series should show a 4% compounded growth rate,
but from one point to the next there can be a variation of +/- 12%.

Has somebody a hint as to how to construct such a function?
 
An interesting problem and easy to model in Excel:

In A1 put a starting value, say 100. In B1 we put a random sample from 12%
below A1 to 12% above A1 (that is from 88 to 112). The equation for this is:

=RANDBETWEEN(A1*0.88,A1*1.12)

Row 2 is the next year and in A2 we put:
=B1*1.04
Which is the 4% growth over the previous year's randon sample. A2 is the
new center point, so in B2:

=RANDBETWEEN(A2*0.88,A2*1.12)


We now propagate row 2 down the columns:
Select A2 and B2 and copu them down as far as you like.
 
I used the RandBetween function as suggested by Garry's Student. Th
approach seems logical, but I'm not certain it does meet the criteria.
I generated 20 times the series for 500 integer data points (from A1 t
A501). I used each time a different starting point: A1 varied from 1
to 200 with steps of 10. I then computed the resulting growth rate a
follows:

-=ROUND(100*((A501/A1)^(1/500)-1),2).-

The resulting growth rate was 19 times out of 20 less than 4 (averag
3.62 minimum 2.82 and once 4.32). I know that working with rando
numbers can be tricky, but nevertheless I expected a more evenl
distributed growth rate around an average of 4.

Is the sample to small or is somehow the approach flawed?

I also tested the GrowthSeries function and it showed a ver
interesting behaviour. It's an array formula and I filled a range o
500 cells with it.

-=GrowthSeries(0.04,0.12,10)-

Again I varied the starting point from 10 to 200 and I calculated th
growth rate. The good news is that the average over the 20 sample
calculated by the same formula as above, was ... 4.00. But the serie
showed a strange behaviour. It grew more rapidly than the first (whic
it should), but after some 450 data points it started to decline ver
quickly to attain the "correct" growth rate at the end point.

When I reduced the sample to 450 cells, then the average growth rat
was ... 4.01. But again it surged for about 410 cells and then again i
declined to the correct value! A closer inspection revealed that th
function algorithm does use the cell count to determine some bounds an
probably this skews the results to the target value near the end.

I'm afraid that both suggestions have their problems
 
Hello,

I get at the end 9247677589.34 which is precisely 4.00% as expected:
=200*(1+4%)^450

My algorithm starts to generate random numbers with the full given
flexibility of +/-12% per step - until it reaches boundary conditions
to meet the final preset result.

There are zillion ways to come from 200 to 9247677589.34 in 450 steps
:-) And each of these are "skewed" by the condition "result =
9247677589.34". Now you can decide which "skew" you would fancy.

Would you like the random numbers to be generated with a flexibility
between -4% and +12% (meaning: on average equal to the given compound
growth rate)?

Regards,
Bernd
 
Bernd,

It is just that I expected over 500 data points more fluctuations
around an average growth rate. But the generated values grow steadily
to a maximum (at around 450 at which point the compounded rate was
about 5.8) and then they fell steadily to the 4.0 growth rate at the
end. I expected for random points a more "sawtooth" like pattern with
some more local max-and minima.

The same pattern existed when I limited the data range to 450 data
points: a steady rise for about 410 points and then decline - just
once.

I then extended the series to 1000 data points - same pattern: rises
for about 900 points, then fall. Could it be a chance hit that in each
case the series rises for about 90% of the time?

But then again: the algorithm seems correct, but in practice it is not
entirely satisfactory. A trick played by the random devil?
 
As an afterthougth, I think that the basic question is:

Can I generate a data series such that the values are "randomly" chose
from the interval [-12,+12] but such that their average is 4? (Whic
means that the series is not totally random!)

If I can generate such a series, I can then use its values as succesiv
multipliers to generate the growth series
 
Hello,

I think that the randomness has to be skewed in that way that the
algorithm should recallibrate the middle point of the random interval
to the current necessary compounded growth rate (which is detemined by
current value, original ending value and count of remaining steps),
meaning:

Function GrowthSeries(dblRate As Double, dblMaxRatePerStep As Double, _
Optional dblStartVal As Double = 1#) As Variant
'Algorithm by: sulprobil http://Reverse("moc.liborplus.www") V0.9
'Returns random data with a compounded growth rate dblRate, with
'a maximal relative change rate per step of dblMaxRatePerStep and
'with a start value of dblStartVal.
'The number of periods is implicitly chosen by the number of selected
'cells which call this function as an array formula (entered with
'CTRL + SHIFT + ENTER).

Dim vR As Variant
Dim lP As Long 'Periods
Dim lrow As Long
Dim lcol As Long
Dim dblCurrVal As Double
Dim dblCurrRate As Double
Dim dblCurrMin As Double
Dim dblCurrMax As Double
Dim dblRelMin As Double
Dim dblRelMax As Double
Dim dblEndVal As Double

Application.Volatile

If TypeName(Application.Caller) <> "Range" Then
GrowthSeries = CVErr(xlErrRef)
Exit Function
End If

If Application.Caller.Rows.Count <> 1 And _
Application.Caller.Columns.Count <> 1 Then
GrowthSeries = CVErr(xlErrValue)
Exit Function
End If

If Abs(dblRate) > dblMaxRatePerStep Then
GrowthSeries = CVErr(xlErrNum)
Exit Function
End If

lP = Application.Caller.Count

ReDim vR(1 To Application.Caller.Rows.Count, _
1 To Application.Caller.Columns.Count)

dblCurrVal = dblStartVal
dblEndVal = dblStartVal * (1# + dblRate) ^ CDbl(lP)
dblCurrMin = dblEndVal / (1# + dblMaxRatePerStep) ^ CDbl(lP)
dblCurrMax = dblEndVal / (1# - dblMaxRatePerStep) ^ CDbl(lP)
For lrow = 1 To UBound(vR, 1)
For lcol = 1 To UBound(vR, 2)
dblCurrRate = (dblEndVal / dblCurrVal) ^ _
(1# / CDbl(lP - lcol * lrow + 1)) - 1#
dblCurrMin = dblCurrMin * (1# + dblMaxRatePerStep)
dblCurrMax = dblCurrMax * (1# - dblMaxRatePerStep)
dblRelMin = (dblCurrMin - dblCurrVal) / dblCurrVal
If dblRelMin < -dblMaxRatePerStep Then
dblRelMin = -dblMaxRatePerStep
End If
dblRelMax = (dblCurrMax - dblCurrVal) / dblCurrVal
If dblRelMax > dblMaxRatePerStep Then
dblRelMax = dblMaxRatePerStep
End If
If dblCurrRate - dblRelMin < dblRelMax - dblCurrRate Then
dblRelMax = 2# * dblCurrRate - dblRelMin
Else
dblRelMin = 2# * dblCurrRate - dblRelMax
End If
dblCurrVal = dblCurrVal * (1# + (dblRelMin + dblRelMax) / _
2# + (Rnd() - 0.5) * (dblRelMax - dblRelMin))
vR(lrow, lcol) = dblCurrVal
Next lcol
Next lrow

GrowthSeries = vR

End Function

HTH,
Bernd
 
Bernd, thank you for your persistence. I tested the function for
different starting values and different numbers of data points and each
time a sawtooth pattern resulted with more up than downs. This is to be
expected as the overall growth is positive. The new function shows the
pattern that I intuitively expected (intuition is mostly wrong in
randomness :rolleyes: )
 
Hello,

Your intuition was right. Compare the two versions and you will see my
corrections :-) And trust my programs when they reach a version number
= V1.00 ...
Click to expand...

Have fun,
Bernd
 
Back
Top