Creating a UDF

[Guest]

Hi all,

I am trying to create a User-Defined-Function to calculate a special type of
statistical variance. The basic gist of it is that it takes four vectors of
values (four different types of prices) and one integer (the number of
trading days) and spits out a single variance value. Similar to the VAR or
VARP function, except that it requires five inputs instead of one range.

I have appended the code that I have so far at the bottom of the post, but I
have a few questions if people don't mind answering them:

1) In the code below, was I correct in having defined the openingPrices,
highPrices, etc As Range? or should have i defined them as arrays As double,
like so:

Function YZVolatility(openingPrices() As Double, _
highPrices() As Double, lowPrices() As Double, _
closingPrices() As Double, numberOfTradingDays As Integer) As Double

Or am I wrong on both counts? The goal is to use the entries from the
vectors to create some new vectors to work on.

2) Are we able to do matrix operations in VBA such as addition, subtraction,
etc?

3) In the section where i'm using the For...Next statement, was that the
correct way to fill in the newly created arrays?

4) Was creating arrays the correct choice in this, considering that I wanted
to use Worksheet functions to do some of calculations? Well... if we can use
matrices in VBA, then I won't have to, since I prefer not to.

5) Is there anything else in that code which could have produced the error?
I can't seem to get it to work. I know it might be easier to just calculate
it manualy in excel using Array formulas, but i'm making this function for my
boss, who doesn't want to waste that much time.

Thank you so much for anyone who helps!

Kind Regards,

Iwan Juwono
--------------------------
Code
--------------------------
Function YZVolatility(openingPrices() As Double, _
highPrices() As Double, lowPrices() As Double, _
closingPrices() As Double, numberOfTradingDays As Integer) As Double
'Calculates the Yang Zhang Open-High-Low-Close Volatility

Dim nOpening As Integer
Dim nHigh As Integer
Dim nLow As Integer
Dim nClose As Integer

Dim sigma2 As Double
Dim sigma02 As Double
Dim sigmac2 As Double
Dim sigmars2 As Double
Dim k As Double
Dim lnOC() As Double
Dim lnCO() As Double
Dim lnHC() As Double
Dim lnHO() As Double
Dim lnLC() As Double
Dim lnLO() As Double
Dim rs() As Double
Dim i As Integer

'Calculate the count variables
nOpening = openingPrices.Count
nHigh = highPrices.Count
nLow = lowPrices.Count
nClose = closingPrices.Count

'Check if all length of all the vectors are the same.
If nOpening = nHigh And nLow = nClose And nOpening = nClose Then
ReDim lnOC(nOpening - 1) As Double
ReDim lnCO(nOpening - 1) As Double
ReDim lnHC(nOpening - 1) As Double
ReDim lnHO(nOpening - 1) As Double
ReDim lnLC(nOpening - 1) As Double
ReDim lnLO(nOpening - 1) As Double
ReDim rs(nOpening - 1) As Double

For i = 1 To noOpening - 1
lnOC(i) = Log(openingPrices(i) / closingPrices(i + 1))
lnCO(i) = Log(closingPrices / openingPrices(i))
rs(i) = Log(highPrices(i) / closingPrices(i)) * Log(highPrices(i) /
openingPrices(i)) _
+ Log(lowPrices(i) / closingPrices(i)) * Log(lowPrices(i) /
openingPrices(i))
Next i

sigma02 = (numberOfTradingDays) * Application.WorksheetFunction.Var(lnOC)

sigmac2 = (numberOfTradingDays) * Application.WorksheetFunction.Var(lnCO)

sigmars2 = (numberOfTradingDays) * Application.WorksheetFunction.VarP(rs)
k = 0.34 / (1 + (nOpening) / (nOpening - 2))

sigma2 = sigma02 + k * sigmac2 + (1 - k) * sigmars2
YZVolatility = sigma2

Else
YZVolatility = 0
End If

End Function

 

[NickHK]

See inline

1 - You are currently requiring arrays of doubles. If you use this on a
worksheet, using a range as the argument(s), Excel will use the .Value of
the range and coerce to double. Hence if any cells cannot be coerced to a
double, an error will error will occur. You will also be able to call the
function within VBA, using variables defined as arrays of doubles

If you change the arguments to Range, then you will have the opportunity to
check any cells for invalid data before you start processing, but then you
cannot call the function with arrays of doubles.

If you check the Object browser/help, you will see that the arguments of
many Excel functions are defined "As Variant". This allows for both
situations above. However, it comes at the price of your code having to
check what was actually passed and acting accordingly.
Depends really then on what you want/need...maybe start with arrays of
doubles and see if there is any need to change it.

2 - There's MMULT and other M*** function for dealing with matrices.
However, do you really mean for dealing arrays ?

3 - Arrays are no Objects (in VBA anyway), so this is not valid
nOpening = openingPrices.Count
Use Ubound(openingPrices)-Lbound(openingPrices)+1

You should also check that all arrays have the same number of dimensions.

As for the correct way in the loop, I suppose so, but only you know the
formula to use.

4 - Up to you...

5 - Get it to compile then post back with specific questions.

NickHK

 

[Guest]

Thanks for that! However, my code still doesn't work and I have more
questions now.

The objective was to take the four columns of data on the spreadsheet and
calculate a single volatility number. However, to do that, I need to actually
create some intermediary columns - usually columns that involve taking one
figure from one of the four original columns and dividing it by a figure from
another (or the same column, but at a different row).

How do I actually go about creating those intermediary columns in the VBA
thing? My attempts in the code above was to create the new columns "lnOC",
"lnOH" and so on. It doesn't seem that I did them correctly...