PDA

View Full Version : Run-time error 13 - type mismatch- simple price time series calc not working on array



MJS
11-17-2014, 09:25 PM
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

snb
11-18-2014, 01:51 AM
First tell us the dimensions of the arrays.

See also: http://www.snb-vba.eu/VBA_Arrays_en.html

MJS
11-18-2014, 06:32 AM
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.

snb
11-18-2014, 06:57 AM
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

MJS
11-18-2014, 07:27 AM
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

snb
11-18-2014, 09:28 AM
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.