VBA: Covariance Matrix 2

Here is the second attempt in programming the covariance matrix. I still have problems with getting the returns and dynamic averages.


Sub COV2M()

    Dim ws As Worksheet
    Set ws = Sheets.Add
        
    ws.Range("A1") = "Covariance Matrix"

    ws.Range("A3") = "Prices:"
    
    'prices
    p = Worksheets("Sheet1").Range("B2:K100")
    
    'counting rows and columns
    r = Worksheets("Sheet1").Range("B2:K100").Rows.Count
    c = Worksheets("Sheet1").Range("B2:K100").Columns.Count
    
    'pasting price to ws
    ws.Range("A5").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(r, c))
    ans.Value = p
    
    'Returns
    ws.Range("M3") = "Returns"
        
    'rows of return
    r = r - 1
    
    ret = Range("M6:V103")
    For i = 1 To r
        For j = 1 To c
            ret(i, j) = p(i + 1, j) / p(i, j) - 1
        Next j
    Next i
    Range("M6:V103") = ret
    
    'getting the averages
    ws.Range("X3") = "Average"
    ave = ws.Range("X6:AG6")
    For j = 1 To c
        s = 0
        For i = 1 To r
            s = ret(i, j) + s
        Next i
        ave(1, j) = s / r
    Next j
    ws.Range("X6:AG6") = ave
    
    'getting the mean Returns
    ws.Range("AI3") = "Mean Returns"
    mret = ws.Range("AI6:AR103")
    For i = 1 To r
        For j = 1 To c
            mret(i, j) = ret(i, j) - ave(1, j)
        Next j
    Next i
    ws.Range("AI6:AR103") = mret
    
    'getting the covariance matrix
    ws.Range("AT3") = "Covariance"
    trans = Application.WorksheetFunction.Transpose(mret)
    covm = Application.WorksheetFunction.MMult(trans, mret)
    For i = 1 To c
        For j = 1 To c
            covm(i, j) = covm(i, j) / (r - 1)
        Next j
    Next i
    
    Range("AT6").Select
    Set ans = ActiveCell.Range(Cells(1, 1), Cells(c, c))
    ans.Value = covm
    
End Sub

VBA: Matrix Operations

Since I still don't know how to let the user specify the "range" in a matrix, but I was eager to do something productive, here's what I came up with:

Given to Matrices, find:

1. Sum
2. Scalar Multiplication
3. Matrix Multiplication
4. Inverse
5. Transpose

I'm having difficulty with Inverse as I am having a runtime error '1004': Unable to get the MInverse property of the WorksheetFunction class. somehow, the help button doesn't work. I do know the code is correct. I just have to get why I am having such errors.


Sub MatrixPractice()
    
    matrixA = Range("A1:J10")
    matrixB = Range("L1:U10")

    
    'Count Rows and Columns
    rA = Range("A1:J10").Rows.Count
    rB = Range("L1:U10").Rows.Count
    
    cA = Range("A1:J10").Columns.Count
    cB = Range("L1:U10").Columns.Count
    
    'checker for same m x n
    If Not rA = rB And cA = cB Then
        MsgBox "can't do operations with the two matrices"
        Exit Sub
    Else
        r = rA
        c = cA
    End If
    
    'Sum
    sum = Range("B14:K23")
    Range("A13") = "Sum"
    For i = 1 To r
        For j = 1 To c
            sum(i, j) = matrixA(i, j) + matrixB(i, j)
        Next j
    Next i
    Range("B14:K23") = sum
    
    'Multiplication
    scalarM = Range("B26:K35")
    Range("A25") = "Scalar Multiplication"
    For i = 1 To r
        For j = 1 To c
            scalarM(i, j) = matrixA(i, j) * matrixB(i, j)
        Next j
    Next i
    Range("B26:K35") = scalarM
    
    'Mmult
    MMult = Application.WorksheetFunction.MMult(matrixA, matrixB)
    Range("A37") = "Matrix Multiplication"
    Range("B38:K47") = MMult
    
    'Inverse have to work on it
    'inv = Application.WorksheetFunction.MInverse(matrixB)
    'Range("A49") = "Matrix B Inverse"
    'Range("B50:K59") = inv

    'Transpose
    tran = Application.WorksheetFunction.Transpose(matrixB)
    Range("A61") = "Matrix B Transpose"
    Range("B62:K71") = tran

End Sub

VB: Covariance Matrix

This is getting the Covariance Matrix using the Record Macro. It is to analyze how it is being done in VBA.

Sub CovMatrix()
'
' CovMatrix Macro
' Fixed, Relative References is not used
'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Covariance Matrix given Prices"
    
    'Prices
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "1. Get the Prices"
    Range("B6").Select
    Sheets("Sheet1").Select
    Range("K100").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 18
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("VBA").Select
    ActiveSheet.Paste
    Range("N4").Select
    Application.CutCopyMode = False
    
    'Returns
    ActiveCell.FormulaR1C1 = "2. Change it into Returns"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "=RC[-12]/R[-1]C[-12]-1"
    Range("N7").Select
    Selection.Copy
    Range("N8:W104").Select
    ActiveSheet.Paste
    Range("O7:W7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'Averages
    Range("Y4").Select
    ActiveCell.FormulaR1C1 = "3.Get the Averages"
    Range("Y7").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-11]:R[97]C[-11])"
    Range("Y7").Select
    Selection.Copy
    Range("Z7:AH7").Select
    ActiveSheet.Paste
    Range("Y7:AH7").Select
    Application.CutCopyMode = False
    
    'Mean adjusted Return
    Range("AJ4").Select
    ActiveCell.FormulaR1C1 = "4.Mean Adjusted Returns"
    Range("AJ7").Select
    ActiveCell.FormulaR1C1 = "=RC[-22]-R7C[-11]"
    Range("AJ7").Select
    Selection.Copy
    Range("AJ8:AJ104").Select
    ActiveSheet.Paste
    Range("AK7:AS104").Select
    ActiveSheet.Paste
    Range("AU5").Select
    Application.CutCopyMode = False
    
    'covariance Matrix
    Range("AU4").Select
    ActiveCell.FormulaR1C1 = "5. Get the Covariance matrix"
    Range("AU7:BD16").Select
    Selection.FormulaArray = _
        "=MMULT(TRANSPOSE(RC[-11]:R[97]C[-2]),RC[-11]:R[97]C[-2])/(COUNT(RC[-11]:R[97]C[-11])-1)"
    ActiveWindow.ScrollRow = 75
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 72
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 65
    ActiveWindow.ScrollRow = 64
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 17
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
End Sub

Thinking of a dynamic way to program this Covariance. Somehow copy and paste seems to be the easier way. Though when reading the code by itself and applying it using relative references, it doesn't make sense. So I have to go back to arrays and matrices.

MPT and Asset Allocation

Modern Portfolio Theory

It's a good thing the group reported  Modern Portfolio Theory.

Additional Information:

• In the optimization $\text{min } \sigma = w^T V w $, the inputs in the diagonal of the covariance matrix $V$ are variances.
• It is important to ask the questions: How did they defined the data used? What constitutes returns? It is not that simple and we have to be objective. We must based it on empirical data and past research.
• What is the use of the Tangency Portfolio? Why is CAL included in the efficient frontier graph?     

The edited graph are copied in Wiki.


• Every stock's expected return is determined by its beta with the tangent portfolio
• Tangent portfolio by definition has the highest SR
• MPT shows only one method of diversifying: managing risk and return. IRL, there are other ways such as go in and out of the market. Since, CAL connects the risk free rate to the tangency portfolio. The highlighted portion is the combination of the risk-free rate and the tangent portfolio which is found to be a way to diversify with a lower risk and still be in the efficient frontier. 
• Any of the portfolios in the upper portion of the $y^2$ curve can beat the market.
• SML - because everyone is buying the market, risk is how much is the market exposure. 
• Empirical data and results shows that MPT is just as it is, a theory. No one can really define and measure the "global market". Some even tested the capacity of CAPM and it doesn't work.

Exercises - found in MPT.xlsm for more info

1. Optimization $\text{min } \sigma = w^T V w $ in Excel. 

2. Covariance

Ways of getting covariance:

In class, we got the covariance of two stocks using the long way and the short way. Details are in the excel sheet. It contains 10 stock returns with an n of 99.

• =COVARIANCE.S(array1, array2)
• =COVARIANCE.P(array1, array2)
• =SUM(x-u_x)(y-u_y)/n

Covariance Matrix

It was also analyzed how the covariance came up to be. 

• It is the matrix multiplication of the mean adjusted return transpose by the mean adjusted return then divided the number of returns
• In short: =mmult(MARTranspose, MAR)/N

Somehow, there are some discrepancies on the numbers I computed for the matrix to the QuantProf, the predefined function made my my prof. But it is very small. Later I found out that some defined =mmult(MARTranspose, MAR)/(N-1) to solve for the covariance matrix.

Other references: Quantitative Methods in Finance by Watsham 

Asset Allocation

• Policy Statement should be there in every portfolio.
• Over long periods of time, sizable allocation to equity will improve results.
• Asset allocation determines your return. It is the overall asset allocation that is important.

Other references involve the previous research material used and a ppt presentation made by another group.

Searching for Alpha

Good reference: Searching for Alpha by Ben Warwick

• History of Finance: Review
• Important people that helped in shaping up the Finance world
• We are trying to figure out how to deal with our investments. Are we satisfied with the the allocation? To what degree is the market predictable? 
• Instead of looking for the weights, we look at alpha.
• where alpha is the excess returns, y-intercepts
• beta is the slope.
• Hunt for a better trap
• Multi-factor model 
• Macro-economic Factor Model
• GDP+Analysis => asset allocation
• Industry => asset allocation
• Search for Arbitrage opportunities (buy and sell) and convergence trade (put and call options)
• Search for market anomalies (e.g January effect, small cap effect, low P/B ratios)
• Value vs growth Stocks, underlying stocks Not the company itself
• Contrary strategies
• Risk minimizing modeling (VAR)

Links and Research for the Mock Trading

I'm assigned both GDP and BOP Analysis. First time of hearing BOP so I have to study and apply it.


actual data and indicators, news and report
http://www.tradingeconomics.com/philippines/indicators
http://www.tradingeconomics.com/philippines/news
http://www.tradingeconomics.com/philippines/report

other key stats
http://www.bsp.gov.ph/statistics/statistics_key.asp


balance of Payment analysis for monetary and fiscal policy
http://www.continentaleconomics.com/files/Mueller.BalanceofPaymentsAnalysis.2011.pdf
http://rbidocs.rbi.org.in/rdocs/content/pdfs/L-9.pdf
http://www.imf.org/external/pubs/ft/bop/2002/02-51.pdf

GDP Resources -basic concepts
http://www.investopedia.com/university/releases/gdp.asp#axzz2JXt7ARuz
http://www.investopedia.com/terms/g/gdp.asp#axzz2JXt7ARuz


Better Method to use? -GDP
https://www.google.com.ph/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&cad=rja&ved=0CEwQFjAD&url=http%3A%2F%2Fciteseerx.ist.psu.edu%2Fviewdoc%2Fdownload%3Fdoi%3D10.1.1.202.9191%26rep%3Drep1%26type%3Dpdf&ei=8VkKUdulEKqviQexkoGgDA&usg=AFQjCNHkkzuJg2LGIuEatg9o44agwwWohA&bvm=bv.41642243,d.aGc


"Our main conclusion is that in general linear time series models (ARFC14, which imposes a unit root in a model with constant and 4 lags )can be hardly beaten if they are carefully specified, and therefore still provide a good benchmark for theoretical models of growth and inflation. 
"However, we have also identified some important cases where the adoption of a more complicated benchmark can alter the conclusions of economic analyses about the driving forces of GDP growth and inflation. Therefore, comparing theoretical models also with more sophisticated time series benchmarks can guarantee more robust conclusions."

http://econpapers.repec.org/paper/hhsrbnkwp/0099.htm
-age structure information

http://repec.rwi-essen.de/files/REP_10_177.pdf
"the current practice of performing medium-term economic projections is unsatisfactory from a methodological point of view as the applied methodology has been developed for short-run forecasting and it is questionable whether these methods are useful for the medium term. In particular, currently medium-term projections are mostly based on the neoclassical Solow growth model with an aggregate production function with labour, capital, and exogenous technological progress. It might be argued, however, that for medium-run projections endogenous growth models might be better suited."


"In particular, the five-year projections of real GDP growth, inflation and the unemployment rate are investigated. Finally, we describe some approaches to improve medium-run projections"

"Solow (2000) mentions the transition from fixed to flexible prices."

http://homepage.univie.ac.at/robert.kunst/070107_efc.pdf
- model free forecast
- Model- Based Univariate (ARMA) <= best method by most institutions.
- Model - Based Multivariate (VAR)

Bottomline: I got the data. I can use ARMA or AR for forecasting GDP and I'm still learning BOP.

LP: Simplex Method

Maximization

How lucky my sister was for waking me up at the middle at the night to teach her the simplex method, in the matrix way for her exam in a few hours. How luckier I am, it is one of the class topics we were about to discuss allowing me to recall on some things. 

Here is a simple maximization problem from her book. All we needed to do after the first table is to make the $2$ x $2$ matrix on the upper left an identity matrix and the last $1$ x $2$ matrix on the lower left, zeroes. Remember that these are equations so the "elimination via matrix" is applied.

Minimization

For minimization problems, a little tweaking is needed. Example, if you are given
 \[ \text{min } z = 0.15x_1+0.12x_2\]
with constraints
\[ \begin{split} 60x_1 +60x_2 & \geq 300 \\ 12x_1 + 6x_2 & \geq 36 \\ 10x_1 + 30x_2 & \geq 90 \end{split} \]
then we have the matrix
\[ \begin{bmatrix} 60 & 60 & \vdots & 300 \\ 12 & 6 & \vdots & 36 \\ 10 & 30 & \vdots & 90 \\ \cdots &\cdots & \vdots & \cdots \\ 0.15 & 0.12 & \vdots & 0\\ \end{bmatrix} \]
The transpose of this matrix will give you a maximization problem and the rest of the process is the same as earlier.  

Application - Simplex method Application varies from business to business and are commonly used.

Links for more info is provided below: 
http://pages.intnet.mu/cueboy/education/notes/algebra/simplex.htm
http://college.cengage.com/mathematics/larson/elementary_linear/5e/students/ch08-10/chap_9_3.pdf
http://college.cengage.com/mathematics/larson/elementary_linear/5e/students/ch08-10/chap_9_4.pdf

VBA: Numerical Methods

The best way that I can learn VB is by practice and just get on with it. I never realized that starting something and ending something is harder than the in between process. Good thing it is object-oriented and somehow close to Java. They are almost alike. But not all. Sobrang nangapa ako sa codes and shortcuts. It's been a very long time since I did some programming.

VBA Shorcuts

• Alt-F11 - Activate VBE
• F5 - Execute Module

The formulas are assumed to be correct. If I put on another char like $y$ in the formula, there will be an error. I can't seem to fix this. Here are the codes:

Colorit.bas


Something Fun. Coloring the highlighted Red. Just learning on the buttons.

Sub Red()
'
' Red Macro
'
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Sub NoFill()
'
' NoFill Macro
'
'
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Bisection.bas

This contain some of the functions found in the other methods. i didn't include it anymore in the other methods.


Sub Bisectiontest()
    
    'ASSUME THAT THE FORMULA IS CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("D4")
    a = Range("E6").Value
    b = Range("E7").Value
     
    'checks the inputs
    If Inputx(form, 0, a, b) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    'wrong property of bisection
    ElseIf Px(form, a) * Px(form, b) > 0 Then
        MsgBox "Please change your interval"
        Exit Sub
    Else
        'applying the method
        Call Bisection(form, a, b)
    End If
    
End Sub

Public Function Inputx(form, form2, a, b) As Boolean
    'Missing input
    If form = "" Or form2 = "" Or a = "" Or b = "" Then
        Inputx = False
    'wrong input on interval
    ElseIf a Like "[A-Z,a-z]" Or b Like "[A-Z,a-z]" Then
        Inputx = False
    Else
        Inputx = True
    End If
    
End Function

Public Function Px(f, x)
    'This is to evaluate the x variable only
    
    'replacing x with the value
    f1 = Application.WorksheetFunction.Substitute(f, "x", x)
    
    'evaluate the string
    Px = Evaluate(f1)
    
End Function

Private Sub Bisection(form, a, b)
    
    Static counter As Integer
    xn = (a + b) / 2
    fa = Px(form, a)
    fxn = Px(form, xn)
    fb = Px(form, b)
    
    'stop with 1000 iteration
    counter = counter + 1
    If counter = 1000 Then
       Range("F9").Value = xn
        Exit Sub
    Else
    
    'checker for next iteration
    'assigning new a or b
    
        If fa = 0 Then
            Range("F9").Value = a
        ElseIf fxn = 0 Then
            Range("F9").Value = xn
        ElseIf fb = 0 Then
            Range("F9").Value = b
        ElseIf fa * fxn < 0 Then
            b1 = xn
            Call Bisection(form, a, b1)
        Else
            a1 = xn
            Call Bisection(form, a1, b)
        End If
    End If
End Sub

Newton.bas


Sub Newtontest()
    'ASSUME THAT THE FORMULAS inputed are CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("E5")
    form2 = Range("E6")
    a = Range("F8").Value
     
    'checks the inputs
    If Inputx(form, form2, a, 0) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    'property of newton
    ElseIf Px(form2, a) = 0 Then
        MsgBox "Please change your x. The first derivative is 0."
        Exit Sub
    Else
        'applying the method
        Call Newton(form, form2, a)
    End If
End Sub

Private Sub Newton(form, form2, a)
    
    Static counter As Integer
    
    f = Px(form, a)
    fprime = Px(form2, a)
    
    'stop with 1000 iteration
    counter = counter + 1
    If Not counter = 1000 Then
        'checker for next iteration
        'assigning new xn
        If f = 0 Then
            Range("G10").Value = a
        Else
            xn = a - (f / fprime)
            Call Newton(form, form2, xn)
        End If
    Else
        Range("G10").Value = a
        Exit Sub
    End If
End Sub

Secant.bas


Sub Secanttest()
    'ASSUME THAT THE FORMULAS inputed are CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("E5")
    x1 = Range("F7").Value
    x2 = Range("F8").Value
    
    'checks the inputs
    If Inputx(form, 0, x1, x2) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    'property of secant
    ElseIf Px(form, x1) = Px(form, x2) Then
        MsgBox "f(x1) = f(x2). Please change your points"
        Exit Sub
    Else
        'applying the method
        Call Secant(form, x1, x2)
    End If
End Sub

Private Sub Secant(form, x1, x2)
    
    Static counter As Integer
    
    f1 = Px(form, x1)
    f2 = Px(form, x2)
    
    'stop with 1000 iteration
    counter = counter + 1
    If Not counter = 1000 Then
        'checker for next iteration
        'assigning new xn
        If f1 = 0 Then
            Range("G10").Value = x1
        ElseIf f2 = 0 Then
            Range("G10").Value = x2
        Else
            xn = x2 - f2 * ((x2 - x1) / (f2 - f1))
            Call Secant(form, x2, xn)
        End If
    Else
        Range("G10").Value = x2
        Exit Sub
    End If
End Sub

Fixedpoint.bas


Sub Fixedpointtest()
    'ASSUME THAT THE FORMULAS inputed are CORRECT
    '0nly after 1000 iterations of f(x) = 0
    
    form = Range("E5")
    form2 = Range("E6")
    a = Range("F8").Value
     
    'checks the inputs
    If Inputx(form, form2, a, 0) = False Then
        MsgBox "Please check your inputs"
        Exit Sub
    Else
        'applying the method
        Call Fixedpoint(form, form2, a)
    End If
End Sub

Private Sub Fixedpoint(form, form2, a)
    
    Static counter As Integer
    
    f = Px(form, a)
    g = Px(form2, a)
    
    'stop with 1000 iteration
    counter = counter + 1
    If Not counter = 1000 Then
        'checker for next iteration
        'assigning new xn
        If f = g Then
            Range("G10").Value = a
        Else
            xn = g
            Call Fixedpoint(form, form2, xn)
        End If
    Else
        Range("G10").Value = a
        Exit Sub
    End If
End Sub

I realized how i missed programming. Its so much different that recording it into a macro. Although that helps but you can't define a function or apply my own kinda style in programming. Maybe a mixture of the two can help me this time.