Visual Basic + Excel Programming Help! - Overclock.net

Jimmy2Shoe · 02-01-07

Hello all,

Seing as it's my last term at business school, I decided to take a class in Financial Modelling. Now I didn't know much of VB getting in, and well... I need some help from a pro.

I want to create a function that will allow you select a series of prices of a stock, and return the variance of the returns/losses.

1st trick: The input, a series in Excel (array in VB speak?) will be prices, and not daily losses returns. So one would have to take the n-th price and devide by previous price n-1. Then you must subtract 1 to the result of that division to get the % variation from one day to the next. I presume that you would have to store these results somewhere.

This is what I have, but it's mostly garbage. Don't worry about the 2nd variable, lambda... that is a "side" function that I must write up, easy enough to do.... I'm not sure that the VAR function in excel lets you use a range as an input.

Code:

Option Explicit
Function volatilite(donnees As Range, lambda)
Dim lambda As Integer
Dim numRow As Integer
numRow = donnees.Rows.Count
dim matrice as double

For i = 1 To numRows
    matrice(i - 1) = Application.WorksheetFunction.Var(donnees)
    Next i


End Function

Would it be best if I gave you the mathimatical formula? It's a little tricky to type out unfortunately... but maybe I can scan a page and put it in JPG.

It's basically the sum (from 1 to i prices) of the cubed prices divided by the number of prices i.

Wait:

		Overclock.net - Overclocking.net > Software, Programming and Coding > Coding and Programming
Visual Basic + Excel Programming Help!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)