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:
- Log return: \( R_t (log) = \log \left ( \frac{P_t}{P_{t-1}} \right )\)
- Discrete return: \( R_t (discrete) = \frac{\left (P_t - P_{t-1} \right )} {P_{t-1}} \)
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
- Published: 18 November 2022
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Time (AET)]