View Full Version : Run-time error 13 - type mismatch- simple price time series calc not working on array
Hello all - I feel ridiculous posting this, but I cannot figure out why this formula generates errors.
Ticker1 = an array of stock prices (1 To 2542)
PctChg1 = an array which I hope will ultimately hold the results of (Price_last / Price_prev)-1
i = counter
For i = 2 To UBound(Ticker1)
PctChg1(i, 1) = Ticker1(i, 1) / Ticker1(i - 1, 1) - 1
Next i
...something about the bold/underlined part it does not like.
Thanks in advance - will try to improve my questions going forward.
Aussiebear
11-18-2014, 12:34 AM
Perhaps you could try this
PctChg1(i, 1)= Ticker1(i, 1) / Offset(i, 1, -1, 0) - 1
First tell us the dimensions of the arrays.
See also: http://www.snb-vba.eu/VBA_Arrays_en.html
Aussiebear- it doesn't like the use of Offset with no qualifier, and I am not skilled enough yet to make the necessary adjustments.
Snb- the dimensions for both arrays are (1 to 2542, 1 to 1). I will check out that link now. Thanks.
I haven't had much problem doing other calculations, but trying to calculate percent change for that first day (using i-1 as the row reference) is giving me fits.
sub M_snb()
sn=cells(1).resize(2542)
sp=sn
sp(1,1)=0
For j = 2 To UBound(sn)
if Val(sn(j-1,1))<>0 then sp(j,1)= sn(j, 1)/sn(j-1,1) - 1
Next
end sub
snb - possible to explain in 30 words or less what you did and why? ...teach a man to fish...
this is working well, sorry I should have known to do that. still learning.
For i = 2 To UBound(Ticker1)
If Val(Ticker1(i - 1, 1)) <> 0 Then PctChg1(i, 1) = Ticker1(i, 1) / Ticker1(i - 1, 1) - 1
Next i
But I don't understand the purpose of this part:
sn=cells(1).resize(2542)
sp=sn
sp(1,1)=0
Well, somehow we need an array filled with values:
sn=cells(1).resize(2542) equivalent to sn=range("A1:A2542").Value
sp=sn a copy of variable sn
sp(1,1)=0 sets the first value in array sp to 0, because the formula (x/x-1-1) starts in the second cell in the array.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.