Disconnected For Next Loop

[GeorgeA]

I'm working to finish up my final project for a financial modeling class. Below is the code for a section. I haven't made many changes lately, but all of a sudden, every time I go to run it, I'm getting an error saying that the next to last "Next" does not have a "For". I've gone over it so many times, I can't begin to understand what I'm missing. I'm thinking I just need a different set of eyes to look at it, as I've been staring at it a lot this last week. Any help or suggestions are much appreciated.

For j = 1 To 4
    cash(j) = 50000
    row30 = RSIrow(30, RSIcol(j) + 1)
    row70 = RSIrow(70, RSIcol(j) + 1)


'if RSI > 70 comes before RSI < 30, start will a long position


    If row70 < row30 Then
        purchase(j) = Range(drange).Offset(1, prcCol(j)).Value * 0.95
        shares(j) = cash(j) / purchase(j)
        cash(j) = 0
    Else
        shares(j) = 0
        purchase(j) = 0
    End If
Next j


For i = 1 To numRows
    balance = 0
    'This is where our 1.005 for each stock we hold should go.
    For x = 1 To 4
        If cash(x) > 0 Then cash(x) = cash(x) * (1 + Range(rfr).Offset(i, 0).Value / 100)
        If margin(x) < 0 Then margin(x) = margin(x) * 1.005    'interest expense
    Next x
      
    For j = 1 To 4
    ' sell if RSI > 70
        If Range(drange).Offset(i, RSIcol(j)).Value > 70 Then
            If shares(j) > 0 Then
                cash(j) = shares(j) * Range(drange).Offset(i, prcCol(j)).Value
                shares(j) = 0
                purchase(j) = 0
                    
            ElseIf cash(j) > 0 And Not prudent Then  ' short stock
                shares(j) = (-2 * cash(j) * 0.96 / Range(drange).Offset(i, prcCol(j)).Value)
                margin(j) = 3 * cash(j) - 2 * 0.04 * cash(j)
                cash(j) = 0
                purchase(j) = Range(drange).Offset(i, prcCol(j)).Value
            End If
        End If
        
    ' buy if RSI < 30
        If Range(drange).Offset(i, RSIcol(j)).Value < 30 Then
            If cash(j) > 0 Then
                If prudent Then
                    shares(j) = cash(j) / Range(drange).Offset(i, prcCol(j)).Value
                    cash(j) = 0
                    purchase(j) = Range(drange).Offset(i, prcCol(j)).Value
                
            ElseIf shares(j) < 0 Then   ' close short position if it exists
                margin(j) = margin(j) - shares(j) * Range(drange).Offset(i, prcCol(j)).Value
                cash(j) = margin(j)
                margin(j) = 0
                shares(j) = 0
                purchase(j) = 0
            End If
        End If
            
        'Code for StopLoss and StopBuy
    If prcCol(j) < (1 + Range(stopBuy).Value) * purchase(j) And shares(j) > 0 Then 'stopbuy
        cash(j) = shares(j) * Range(drange).Offset(i, prcCol(j)).Value
            shares(j) = 0
            purchase(j) = 0
            
    If prcCol(j) > (1 - Range(stopBuy).Value) * purchase(j) And shares(j) < 0 Then 'stoploss
        cash(j) = shares(j) * Range(drange).Offset(i, prcCol(j)).Value
            shares(j) = 0
            purchase(j) = 0
    End If
  End If
  
  balance = balance + cash(j) + shares(j) * Range(drange).Offset(i, prcCol(j)).Value + margin(j)
  
    Next j
    Range(outrange).Offset(i, 0).Value = balance
    
Next i

 

[StephenCrump]

The error message is a little misleading.

The problem is actually a mismatch between your IF / ENDIF's. For example, presumably this snippet should be

If cash(j) > 0 Then
    If prudent Then
        shares(j) = cash(j) / Range(drange).Offset(i, prcCol(j)).Value
        cash(j) = 0
        purchase(j) = Range(drange).Offset(i, prcCol(j)).Value
[COLOR=#ff0000][B]    Endif[/B][/COLOR]
ElseIf shares(j) < 0 Then   ' close short position if it exists
    margin(j) = margin(j) - shares(j) * Range(drange).Offset(i, prcCol(j)).Value
    cash(j) = margin(j)
    margin(j) = 0
    shares(j) = 0
    purchase(j) = 0
End If

 

[GeorgeA]

Thank you. This worked perfectly. I have another question regarding this project that I am going to post about separately from this reply. If you have the time, I would love for you to take a look at it as well.

 

[mrmmickle1]

GeorgeA,

Glad you were able to get your issue solved. If you don't mind me asking:

What kind of financial modeling class are you taking? What College? ...........I've never seen VBA incorporated into coursework.

 

[GeorgeA]

Ok, I have had another issue come up with this same project. I'm trying to run the following code which calls the linest function to run some statistical data:

Public Function regress(yrange, capm) As Single


Dim xrange As String
Dim regMtx() As Single
Dim regout
Dim rcnt As Integer, i As Integer, j As Integer, ccnt As Integer




If capm Then ' true -> CAPM
    xrange = "FFF!b2:b66"
Else        ' false -> FF 3 factor
    xrange = "FFF!b2:d66"
End If


regout = Application.LinEst(Range(yrange).Value, Range(xrange).Value, 1, 1)


rcnt = UBound(regout, 1)
ccnt = UBound(regout, 2)
ReDim regMtx(rcnt, ccnt)




For i = 1 To rcnt
    For j = 1 To ccnt
        If i > 2 And j > 2 Then
            regMtx(i, j) = -999
        Else
            regMtx(i, j) = regout(i, j)
        End If
Next j, i


regress = regMtx


End Function

As far as I know, the above is structured fine, the problem is when I go to call it. My professor gave us a method for using it, the code for which is located below:

Dim yrange As Single
Dim regresults As String


yrange = "StatisticalData!E2:E61" ' true for CAPM false for FF 3 factor
regresults = regress(yrange, True)  ' alpha regresults(1,2)
                                                        ' beta regresults(1,1)
yrange = "StatisticalData!E2:E61"   ' idio. risk regresults(4,2)
regresults = regress(yrange, False)
                                                        'FF 3 Factor
yrange = "StatisticalData!F2:F61"   ' alpha regresults(1,4)
regresults = regress(yrange, True)


yrange = "StatisticalData!F2:F61"
regresults = regress(yrange, False)

My issue is that I am getting a mismatch error for my "yrange" variable. I've gotten these before, but it seems like it is never the same fix, and I've done everything that I have used in the past. Please let me know if you would like any additional information, as I know this can appear a bit strange because it is just a portion of the overall project.

 

[GeorgeA]

I'm at the University of Missouri. Our modeling seems to generally be focused on portfolio analysis. The ironic part is that we didn't really start working with it until about half way through the semester, and now we have this pretty substantial project we are working on that is in all honestly, way above our heads. I've haven't spoken to one person in my class who 100% knows what they are doing. But, my partner and I feel like we've gotten fairly close, so now we are just trying to close up some loose ends.

 

[mrmmickle1]

GeorgeA,

Glad to hear it. My career has skyrocketed in the last few years based on my VBA Skills. I have to say that it's very difficult to put this on paper (i.e. a resume) because, people don't really understand what automated VBA solutions are or what ramifications they have on workplace/task efficiency. I find that it's best to demonstrate in one on one interviews. Over the year and a half I have more than doubled my salary based on this skill set alone.... I have to say that while most large companies like to say they have full Business Intelligence / Reporting Analytics Software that automates the work.... this is a far cry from the truth. I work at a fortune 500 that relies heavily on spreadsheet manipulation. Knowing Excel and VBA is worth it's weight in gold.

Keep plugging. All of the hard work will pay off!

 

[GeorgeA]

I appreciate the kind words. When I initially signed up for the class, I had hoped it would have been a bit more foundational and kind of worked us from the ground up. I feel like I'm still missing a lot of the basics considering we just kind of jumped in. But I'm hoping that just having looked at it will help me to some degree if I end up having to work with it in any sort of regular way.

 

[StephenCrump]

This seems like a convoluted way of using LINEST, but presumably done this way for a reason?

Dim yrange As Single

yrange = "StatisticalData!E2:E61" ' true for CAPM false for FF 3 factor

My issue is that I am getting a mismatch error for my "yrange" variable.

You've declared yrange as Single, but then assigned a string: "StatisticalData!E2:E61" is a string in the same way that "qwerty" is a string.

I suggest you do this:

Dim yrange As Range
Dim vResults As Variant

Set yrange = Worksheets("StatisticalData").Range("E2:E61")
vResults = regress(yrange, True)

Public Function regress(yrange As Range, capm As Boolean) As Variant

' ....

End Function

Ideally, all the parameters used by your function should be passed as arguments to the function. That way, you can re-use the function for any set of x and y values, instead of (as at the moment) always having x values based on FFF!b2:b66

 

[StephenCrump]

My career has skyrocketed in the last few years based on my VBA Skills.

Nice!