LOST!

[allenD]

I'm trying to access historical stock data from another workbook("sp500.xls") and calculate the percentage change over the last 17 days. the variable curPrice is todays price which is located Workbook("sp500.xls").Sheets("prices") in cell "e3". The variable lookBackPrice is the price 17 days ago which is also located in the same workbook adn sheet but in cell "e20". I have 5000 prices in this workbook in range("e3:e5000"). I want to calculate the percentage change for each price over a 17day period and store the percentage changes in the range("H3:H4937).

This is the code that I wrote to "trying" to accomplish this.
Please show me the correct way to write this and also point out to me what I did wrong so I will understand whats going on.

THANKS!
Allen

Sub sp500()

Range("h3:h5000").Select
Selection.Clear

Dim curPrice
Dim lookBackPrice
Dim percentChg

Workbooks("sp500.xls").Sheets("prices").Range("e3").Value = curPrice
Workbooks("sp500.xls").Sheets("prices").Range("e20").Value = lookBackPrice
percentChg = "= Abs((curPrice - lookBackPrice) / (lookBackPrice))"

Range("h3").Value = percentChg
Range("h3").Select
Selection.AutoFill Destination:=Range("h3:h5000"), Type:=xlFillDefault

End Sub

 

[jindon]

Not sure if this is what you wanted...

Sub sp500()
    Dim a, i As Long b(1 to 5000, 1 To 1), ws As Worksheet

    Set ws = ActiveSheet
    ws.Range("h3:h5000").Clear
    

    a = Workbooks("sp500.xls").Sheets("prices").Range("e3:e5000").Value
    For i = 1 To UBound(a,1) - 17
       b(i,1) = Abs((a(i,1) - a(i+17,1)) / a(i+17,1))
    Next
    
    ws.Range("h3").Resize(i-1).Value = b
    Set ws = Nothing
    End Sub