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...
"NickHK" wrote:
> 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
>
> "Harimau" <(E-Mail Removed)> wrote in message
> news:FE609D73-1262-4B2B-B514-(E-Mail Removed)...
> > 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
> >
> >
> >
>
>
>
Similar Threads
