Hello to everyone,
I am kinda new to VBA, and I cannot figure out how to implement a rolling computation. Here's an example of my data:
A C D E F G H
A % theory practice error
0.0052 0.05 -0.0193 -0.0198 2.96E-07 a 0.0000
0.0039 0.10 -0.0135 -0.0136 9.59E-09 b 119.5765
-0.0180 0.15 -0.0101 -0.0103 5.76E-08 c 103.2256
0.0175 0.20 -0.0077 -0.0086 9.61E-07
0.0064 0.25 -0.0058 -0.0061 7.24E-08 SSE 3.04E-05
0.0063 0.30 -0.0043 -0.0044 1.66E-08
0.0028 0.35 -0.0030 -0.0028 4.99E-08
-0.0022 0.40 -0.0019 -0.0015 1.57E-07
-0.0038 0.45 -0.0009 -0.0005 1.70E-07
0.0146 0.50 0.0000 0.0006 3.67E-07
0.0049 0.55 0.0010 0.0026 2.42E-06
0.0058 0.60 0.0022 0.0041 3.93E-06
0.0088 0.65 0.0035 0.0058 5.34E-06
-0.0216 0.70 0.0049 0.0073 5.31E-06
0.0260 0.75 0.0067 0.0088 4.39E-06
0.0025 0.80 0.0089 0.0106 2.82E-06
0.0002 0.85 0.0117 0.0134 2.86E-06
0.0039 0.90 0.0156 0.0166 1.06E-06
-0.0039 0.95 0.0223 0.0220 1.13E-07
In column A, I have some numbers. In columns C, some percentiles. In column D, some values, computed according to the parameters a,b,c. In columns E, I take the x percentile of the data in column A, where the x percentile is contained in column C. In column F, I compute the squared difference between the values in columns D and E, and the sum of such squared differences is reported as SSE. Everything is done because, with the solver, I need to minimize SSE changing a,b,c.
The problem arises because the data in column A are around 500, and I need to compute the possible values for a,b,c 250 times by taking a sample of 250 data at a time.
To better explain, the first simulation should do the following:
- in column E, take the range A1:A250 and compute the values corresponding to the percentiles contained in column C;
- then, activate the solver (minimize SSE, changing a,b,c, constraitns: b>0,c>0), which will change the values for a,b,c;
- copy the values of a,b,c somewhere on the right.
The second simulation should, considering the range A2:A251 instead, perform the same tasks.
I call it fixed-length dynamic array because the array is always 250-data long, but the reference data shift by one element dynamically. In the end, my aim is to have a series of 250 simulations of the values of a,b,c, each computed on a different sample.
I have tried implementing this procedure with a macro, but I could not make the "rolling window" work.
Any help for implementing this procedure via VBA?
Thank you very, very much!!
Cross-posted to:
http://stackoverflow.com/questions/1...-dynamic-array
http://www.mrexcel.com/forum/excel-q...ml#post3421044
http://www.ozgrid.com/forum/showthre...298#post654298
http://social.msdn.microsoft.com/For...d-b5a98d9db603
Bookmarks