Topic #3: Trendline (Quantitative Analysis)

Our next topic will be Trendline Analysis. In this topic, we will be making a program that would require us to input the size and the data. Then the outpout would be the equation of the trendline. Assuming that there is  no seasons involved. To start, we need to declare all the variables we will be using. Here are the variables:

    Dim x As Single

    Dim y() As Single

    Dim xsum As Single

    Dim ysum As Single

    Dim xsqsum As Single

    Dim xysum As Single

    Dim n As Integer

    Dim b1 As Single

    Dim b0 As Single

    Dim response As Single

Then, we need to put a code in order for us to input the data and the data size.

n = Application.InputBox("Enter number of data.")   

    ReDim y(1 To n) As Single

    For x = 1 To n

        y(x) = Application.InputBox("enter data " & x)

    Next

After the data input, we need to do the necessary solutions to solve for the trendline equation. In my work, i used the normal equation method instead of the conventional way to minimize rounding errors. Here is how normal equation works:

Now, if we solve it manually, you will be using systems of equations involving two variables to determine the value of b₀ and b₁.

Now for solving using visual basic, we need to put the code below:

        xsum = 0

        ysum = 0

        xsq = 0

        xysum = 0

       

    For x = 1 To n

        xsum = xsum + x

        ysum = ysum + y(x)

        xsqsum = xsqsum + x ^ 2

        xysum = xysum + y(x) * x

    Next

    b1 = (ysum * xsum - n * xysum) / (xsum ^ 2 - n * xsqsum)

    b0 = (ysum - b1 * xsum) / n

Now for the output, we need to put this code:

response = MsgBox("the equation is y=" & b1 & "x + " & b0 & ".", vbOKOnly)