Caclulate Rolling Statistics with limited use of for loops

[jfay]

I have to loop through the following code several hundred times, so I am trying to determine if there is a faster way to code what i have already written. I do not believe there is another way to execute what i have sritten without using the multiple for loops that I have, but if anyone has any ideas I would love to hear them.
Thanks!

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

I have attached the workbook that I am using that may make it more clear what the code is doing. It is calculating the hurst exponent, a statistic that measures persistence or anti-persistence of time series data. In order to calculate this, the macro needs to calculate statistics such as standard deviation of the data up until each point before the end date. I am looking to see if there is a way to do this other than using a for loop. It is the second for loop that is slowing the program down I believe, but any suggestions as to how to improve the program would be appreciated.
Note: The code as it is now will take about 8ish seconds to run, but I am looking to run this over hundreds of time periods at least and those seconds add up

I realize the code is rather complicated. If anyone would even have another method that I could look into in order to make it run quicker I would appreciate that as well, I do not necessarily need any rewritten code.

Thanks!

[k64]

If you want help, post a sample workbook and an example of what you want to accomplish. Unless we know what you're trying to do, it's hard to tell you how to do it.
Note: "Lengthy Code" is not the best thread title. I almost avoided reading this because of it. I'd title it something like "Optimizing code for [Insert purpose of code]" or "Alternative to loops when [what code does]"

[k64]

Hey, I tried for a while to make the code faster, and succeeded just a little, but I don't know what it's supposed to do, so I can't do much. What I can do is tell you what I was trying to do and you can figure it out.
The section that is the problem is the "b" loop. Writing to cells one by one is slow. What I was trying to do is this:
1. Dim arr as Variant, and arr = Sheets(1).usedrange.value to take the whole sheet into an array
2. Do all the operations in the array
3. Cells(1,1).Resize(ubound(arr,1),ubound(arr,2)).value = arr to paste the array back down.
I was only able to replace that specific loop with my method, which meant that ever iteration of the i loop I was rewriting the entire sheet. It was still 15% faster, but I'm pretty sure if you could get both loops to operate on an array and the paste the result, you would have significant time savings.

[ag273n]

there might be a better option out there... but my suggestion is...


1. import the web data into a sheet(new),
2. then create formulas referenced to that sheet
3. automate the remaining smaller stuff (like maybe paste special values to remove the formulas)
so you have a template that has all the default formulas needed in it, then that template at the end part of the macro will recreate another workbook with the results from the web scrapper so you can reuse it

what made that macro so slow i believe is that excel had to manually calculate the values for each cell through the loop - a long long process...