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

Sub Button1_Click()
    Range(Cells(4, 8), Cells(Cells(3, 2) - Cells(2, 3), 25)).ClearContents
""""""""""""EXTRACT DATA (needs no editing)
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim EndDate As Date
    Dim StartDate As Date
    Dim Symbol As String
    Dim qurl As String
    Dim nQuery As Name
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    Set DataSheet = ActiveSheet
  
        StartDate = DataSheet.Range("B2").Value
        EndDate = DataSheet.Range("B3").Value
        Symbol = DataSheet.Range("B4").Value
        Cells(7, 3).CurrentRegion.ClearContents
        
'construct the URL for the query

                 
        
'construct the URL for the query
      Dim sp As String
      Dim vix As String
      Dim gspc As String
      
      sp = "spx"
      vix = "vix"
      gspc = "gspc"
      If Symbol = sp Then
       Symbol = gspc
      End If
      
      
      If Symbol = gspc Or Symbol = vix Then
        qurl = "http://ichart.finance.yahoo.com/table.csv?s=%5E" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("C3") & "&ignore" & "=.csv"
        Range("b5") = qurl
      Else
        
        qurl = "http://chart.yahoo.com/table.csv?s=" & Symbol
        qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
            "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
            Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Range("C3") & "&q=q&y=0&z=" & _
            Symbol & "&x=.csv"
        Range("b5") = qurl
      End If
                   
                   
                   
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("C7"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            
            Cells(7, 3).CurrentRegion.TextToColumns Destination:=Cells(7, 3), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, other:=False
            
            Range(Range("C7"), Range("C7").End(xlDown)).NumberFormat = "mmm d/yy"
            Range(Range("D7"), Range("G7").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H7"), Range("H7").End(xlDown)).NumberFormat = "0,000"
            Range(Range("I7"), Range("I7").End(xlDown)).NumberFormat = "0.00"


    With ThisWorkbook
        For Each nQuery In Names
            If IsNumeric(Right(nQuery.Name, 1)) Then
                nQuery.Delete
            End If
        Next nQuery
    End With
'turn calculation back on
    maxd = (Range("b3") - Range("b2")) * 5 / 7
    Range(Cells(7, 3), Cells(maxd, 9)).Select
    Selection.Sort Key1:=Range("C8"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("C1").Select
    Selection.ColumnWidth = 12
    
    'UpdateScale

    Range("B4").Select
"""""""""""""END EXTRACT DATA

''Code that takes a while to run


num = Range("f2")
inc = Range("f3")
Cells(2, 4) = WorksheetFunction.CountA(Range("c:c")) - 2
Cells(8, 11) = Range("g8") / Range("d8") - 1
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim x As Range
Dim y As Variant
Dim z As String
Dim days As Long
days = Range("d2")
If num * inc > days - 10 Or num <= 1 Then
 MsgBox "Number of Points must be > 1 and Increment * Number of Points may not exceed number of days available. Adjust Increment or number of points, or select wider date range.", vbCritical, "Error"
Exit Sub
End If
For h = 9 To days + 6
 Cells(h, 11) = Cells(h, 9) / Cells(h - 1, 9) - 1
Next
go = 8
For i = num - 1 To 0 Step -1

  n = Range("d2") - i * inc - 1
  '  Range("n8") = WorksheetFunction.Average(Range(Cells(8, 16), Cells(n + 7, 16)))
  avg = WorksheetFunction.Average(Range(Cells(8, 11), Cells(n + 6, 11)))
  Range(Cells(8, 12), Cells(n + 6, 12)).Value = Range(Cells(8, 11), Cells(n + 6, 11)).Value
  z = "-"
  y = avg
  Set x = Range(Cells(8, 12), Cells(n + 6, 12))
  Call AddSubDivMulRange(x, y, z)
  Cells(8, 13).Value = Cells(8, 12).Value
  For b = 9 To n + 6
   Cells(b, 13).Value = Cells(b, 12).Value + Cells(b - 1, 13)
  Next
  r = WorksheetFunction.Max(Range(Cells(8, 13), Cells(n, 13))) - WorksheetFunction.Min(Range(Cells(8, 13), Cells(n, 13)))
  std = WorksheetFunction.StDev_P((Range(Cells(8, 11), Cells(n + 6, 11))))
  Cells(go, 18) = r / std
  Cells(go, 15) = n - 1
  Cells(go, 16) = WorksheetFunction.Ln(Cells(go, 15).Value)
  Cells(go, 17) = WorksheetFunction.Ln(r / std)
  go = go + 1
  
  
  

Next
Cells(4, 6) = WorksheetFunction.Slope(Range("q:Q"), Range("p:p"))

    Range("k7") = "r(k)"
    Range("l7") = "x(k)"
    Range("m7") = "Y(k)"
    Range("o7") = "n"
    Range("p7") = "LN(n)"
    Range("q7") = "LN(R/s)"
    Range("r7") = "R/s"
 Cells(2, 9) = Cells(Cells(8, 15) + 7, 3)
 Cells(3, 9) = Cells(days + 5, 3)
 Cells(2, 8) = "First n:"
 Cells(3, 8) = "Last n:"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True

End Sub

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!