Hey Everyone,
I'm not sure if its possible to do what I am asking but here goes.
I've created a function that does a "Cash Calculation" on one set of data.
Function CashCalc(ByVal CurSignal As Integer, ByVal PrevSignal As Integer, ByVal CurShares As Integer, ByVal PrevShares As Integer, ByVal ClosePrice As Double, ByVal PrevCash As Double)
Dim ReturnValue As Double
ReturnValue = PrevCash
If CurSignal = 0 And PrevSignal = 0 Then
ReturnValue = PrevCash
ElseIf CurSignal = PrevSignal Then
ReturnValue = PrevCash
ElseIf CurSignal <> PrevSignal And CurSignal > 0 Then
ReturnValue = ReturnValue - ClosePrice * (CurShares - PrevShares)
ElseIf CurSignal <> PrevSignal And CurSignal = 0 Then
ReturnValue = ReturnValue + ClosePrice * PrevShares
End If
CashCalc = ReturnValue
End Function
Eventually though, I will need to do this calculation for 30+ sets of data. For each row in the spread sheet there would be numerous times when I would need to either add or subtract from the return value.
So my question is this. Can I use an array to do this calculation across the 30+ sets of data. For example, instead of the "CurSignal" being one variable it would be an array. The code would then go through a loop taking the first value of each array, doing the calculations of the "ReturnValue", then move on to the second value of each array and looping over and over until all data sets are analyzed and returning a final value.
I'm thinking of it similar to how the SUM Function works in excel, it doesn't care how many cells I use... it adjusts for the calculation, once it has received the array.
I've attached a sample spread sheet to explain. Currently the function has been used with Data Set 2, imagine if you will I wanted to use this calculation with both data sets, then maybe 5 data sets, then maybe 25.
Thoughts anyone? Am I in over my head?
Bookmarks