How to improve efficiency and readability for this code Function…
Question Answered step-by-step How to improve efficiency and readability for this code Function… How to improve efficiency and readability for this codeFunction Bubble_Finder(StockPrices, Dates) Dim var1 As Integer var1 = StockPrices.Cells.Count Dim var2 As Integer var2 = var1 – 1 Dim Momentum() As Double ReDim Momentum(1 To var2, 1 To 1) For i = 1 To var2 Momentum(i, 1) = StockPrices.Cells(i + 1, 1) / StockPrices.Cells(i, 1) – 1 Next i Dim small_lil_nums() As Double ReDim small_lil_nums(1 To 2, 1 To 2) big_step = 3 For i = 1 To var2 Step big_step If Momentum(i, 1) < small_lil_nums(2, 1) Then If Momentum(i, 1) < small_lil_nums(1, 1) Then small_lil_nums(2, 1) = small_lil_nums(1, 1) small_lil_nums(2, 2) = small_lil_nums(1, 2) small_lil_nums(1, 1) = Momentum(i, 1) small_lil_nums(1, 2) = i Else small_lil_nums(2, 1) = Momentum(i, 1) small_lil_nums(2, 2) = i End If End If Next i Dim The_real_small() As Double ReDim The_real_small(1 To 2, 1 To 2) Dim small_set As Integer small_set = big_step For j = 1 To 2 For i = (small_lil_nums(j, 2) - small_set) To (small_lil_nums(j, 2) + small_set) If Momentum(i, 1) < The_real_small(j, 1) Then The_real_small(j, 1) = Momentum(i, 1) The_real_small(j, 2) = i End If Next i Next j Dim deviation As Double Dim how_much_difference As Double how_much_difference = 0 Dim walk_sum As Double For i = 1 To var2 walk_sum = walk_sum + Momentum(i, 1) Next i Dim X_Bar As Double X_Bar = walk_sum / var2 For i = 1 To var2 how_much_difference = how_much_difference + (Momentum(i, 1) - X_Bar) ^ 2 Next i deviation = (how_much_difference / (var2 - 1)) ^ 0.5 Dim Fast_burst As Integer Dim Slow_burst As Integer Dim bubble_go_boom() As Variant ReDim bubble_go_boom(1 To 2, 1 To 1) For j = 1 To 2 For i = (The_real_small(j, 2) - small_set) To (The_real_small(j, 2) + small_set) If Momentum(i, 1) < (X_Bar - 2 * deviation) Then Fast_burst = Fast_burst + 1 ElseIf Momentum(i, 1) < (X_Bar - deviation) Then Slow_burst = Slow_burst + 1 End If Next i Bubble_burst = 2 * Fast_burst + Slow_burst If Bubble_burst > WorksheetFunction.Floor(small_set * 1.5, 1) Then bubble_go_boom(j, 1) = “Large Bubble Burst” ElseIf Bubble_burst > small_set Then bubble_go_boom(j, 1) = “Standard Bubble Burst” ElseIf Bubble_burst > WorksheetFunction.Floor(small_set * 0.5, 1) Then bubble_go_boom(j, 1) = “Partial Bubble Burst” Else bubble_go_boom(j, 1) = “No Bubble/No Burst” End If Fast_burst = 0 Slow_burst = 0 Next j Dim results() As String ReDim results(1 To 2 + 1, 1 To 5) results(1, 1) = “Average Momentum” results(2, 1) = X_Bar results(3, 1) = “” results(1, 2) = “Momentum Std Dev” results(2, 2) = deviation results(3, 2) = “” For i = 3 To 2 + 1 results(i, 1) = “” results(i, 2) = “” Next i results(1, 3) = “Local Min Momentum” results(1, 4) = “Local Min Location” results(1, 5) = “Bubble Burst Found” For i = 1 To 2 results(i + 1, 3) = The_real_small(i, 1) results(i + 1, 4) = The_real_small(i, 2) + 1 results(i + 1, 5) = bubble_go_boom(i, 1) Next i Bubble_Finder = results() End Function Engineering & Technology Computer Science BFC 5936 Share QuestionEmailCopy link Comments (0)
