xlf EandA series


StockReturn function


A simple code exercise. Time allocation 10 minutes.


0. Preliminary


The EXERCISE - complete the following



Write a custom function to return the value of StockReturn (SkRet abbrv), with arguments for each price and optional Return method, and log being the default (the other you must identify). Use only VBA functions (not WS functions). Any error returns #VALUE!


 

1. Tasks


This is a suggested coding solution


StockReturn function


The task is a VBA based application of stock return formulas covered in session 2 and assignment 1.


Relevant Formulas:


For return at time t \(R_t\), given Price at time t \(P_t\) and t -1 \(P_{t-1}\)



Code 1: the StkRet function interpretation of set task 
Function StkRet(P_t As Double, P_tm1 As Double, Optional RetM As Variant) As Variant
' RetM 0 for log, 1 for Delta

On Error GoTo ErrHandler

    If IsMissing(RetM) Then RetM = 0

    If RetM = 0 Then
        StkRet = Log(P_t / P_tm1)
    ElseIf RetM = 1 Then
        StkRet = (P_t - P_tm1) / P_tm1
    Else
        GoTo ErrHandler
    End If

Exit Function
ErrHandler:
    StkRet = CVErr(xlErrValue)
End Function